Hendry's "Oracle and SQL Server DBA Stuff"

Oracle and SQL Server Database Solutions for DBA's please visit http://hendrydba.com for the latest posts. Thanks

Archive for February, 2011

Install Oracle Grid Agent 11g on IBM Aix 6.1

Posted by Hendry chinnapparaj on February 23, 2011

Install Oracle Grid Agent 11g on IBM Aix 6.1

Oracle 11g Grid Agent connecting to Oracle 10g Grid Control

There are issues installing this agent in Aix 6.1, hence a efix required in unix before install which can be removed after the install as below

UNIX :

zoeprd:/tmp/OraInstall2011-02-23_01-35-04PM> oslevel -g

6.1.0.0

zoeprd:/tmp/OraInstall2011-02-23_01-35-04PM> oslevel -r

6100-06

Install efix:  emgr -e IZ88711SP2.101121.epkg.Z

DBA :

perform oracle product install  – AIX_Grid_Control_agent_download_11_1_0_1_0.zip

UNIX :

remove efix : emgr -r -L IZ88711SP2

To install 11g grid agent you have to use the following options –

 

1. Push Method.

2. Pull Method.

3. Clone agent home

4. Silent install.

Example of silent install.

Download 11g grid control agent software and unzip it from technet.oracle.com –

cd /u01/oracle/stage –

ls -l *.zip

AIX_Grid_Control_agent_download_11_1_0_1_0.zip

unzip AIX_Grid_Control_agent_download_11_1_0_1_0.zip

Creates a new directory aix.

cd /u01/oracle/stage/GA11/aix/response

zoeprd:/u01/oracle/stage/GA11/aix/response> l

total 48

-rwxrwxr-x    1 oracle   dba             489 Dec 23 2009  staticports.ini

-rwxrwxr-x    1 oracle   dba            5922 Feb  9 2010  upgrade_agent.rsp

-rwxrwxr-x    1 oracle   dba           10470 Feb 23 13:33 additional_agent.rsp

edit the file – additional_agent.rsp

Make the following changes- basic changes needed – no uploads to oracle support.

SECURITY_UPDATES_VIA_MYORACLESUPPORT=false

DECLINE_SECURITY_UPDATES=true

INSTALL_UPDATES_SELECTION=”skip”

ORACLE_AGENT_HOME_LOCATION=”/u01/oracle/”

b_silentInstall=true

OMS_HOST=”prdu010.greatwest.com”

OMS_PORT=”4889″

AGENT_REGISTRATION_PASSWORD=”xxxxxxx”

FROM_LOCATION=”/u01/oracle/stage/GA11/aix/agent/stage/products.xml”

cd /u01/oracle/stage/aix/agent

./runInstaller -silent -responseFile /u01/oracle/stage/GA11/aix/response/additional_agent.rsp à check the path for rsp file

Starting Oracle Universal Installer…

Checking Temp space: must be greater than 150 MB.   Actual 1858 MB    Passed

Checking swap space: must be greater than 150 MB.   Actual 8192 MB    Passed

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2011-02-02_08-14-12AM. Please wait …cpsoa1:/u01/oracle/stage/aix/agent> *** Check for updates ***

*** Select Installation Type ***

*** Check Prerequisites ***

*** Specify Oracle Management Service Location ***

*** Customize Ports ***

*** Review ***

—————————————————————————–

Summary

Global Settings

Source: /u01/oracle/stage/aix/agent/stage/../stage/products.xml

Oracle Home: /u01/oracle/agent11g (agent11g1)

Installation Type: Complete

Product Languages

English

Space Requirements

/tmp/ Required 77MB (only as temporary space) : Available 1.59GB

/u01/ Required 2.54GB : Available 37.68GB

New Installations (58 products)

Oracle Management Agent 11.1.0.1.0

Agent Virtualization 11.1.0.1.0

Enterprise Manager Agent 11.1.0.1.0

Enterprise Manager Agent Core Files 11.1.0.1.0

Secure Socket Layer 11.1.0.7.0

Oracle Recovery Manager 11.1.0.7.0

Enterprise Manager Common Files 11.1.0.1.0

Enterprise Manager Common Core Files 11.1.0.1.0

Required Support Files 11.1.0.7.0

Oracle RAC Required Support Files-HAS 11.1.0.7.0

Oracle JDBC/THIN Interfaces 11.1.0.7.0

Oracle JDBC/OCI Instant Client 11.1.0.7.0

Oracle Globalization Support 11.1.0.7.0

Oracle Net Required Support Files 11.1.0.7.0

SSL Required Support Files for InstantClient 11.1.0.7.0

LDAP Required Support Files 11.1.0.7.0

Oracle Globalization Support 11.1.0.7.0

Perl Interpreter 5.10.0.0.1

Perl Modules 5.10.0.0.1

Expat libraries 2.0.1.0.1

XML Parser for Java 11.1.0.7.0

Precompiler Required Support Files 11.1.0.7.0

RDBMS Required Support Files 11.1.0.7.0

RDBMS Required Support Files for Instant Client 11.1.0.7.0

Parser Generator Required Support Files 11.1.0.7.0

Platform Required Support Files 11.1.0.7.0a

Patch for Oracle Process Management Notification 10.1.3.4.0

Oracle Process Management Notification 10.1.3.0.0

Agent Required Support Files 10.2.0.4.1

XDK Required Support Files 11.1.0.7.0

SQL*Plus Required Support Files 11.1.0.7.0

Secure Socket Layer 11.1.0.7.0

Oracle Core Required Support Files 11.1.0.7.0

Enterprise Manager Agent for Grid Control 11.1.0.1.0

Installation Common Files 11.1.0.7.0

Oracle Configuration Manager 10.3.2.1.0

Oracle Bali Share 11.1.1.2.0

Enterprise Manager Application Server Integrator Plugin — Agent Support 11.1.0.2.0

Oracle Dynamic Monitoring Service Patch 10.1.2.3.0

Oracle Dynamic Monitoring Service 10.1.2.1.0

Enterprise Manager Application Server Plugin — Agent Support 11.1.0.1.0

Enterprise Manager Application Server Plugin — Common Support 11.1.0.1.0

Enterprise Manager Database Plugin — Agent Support 11.1.0.1.0

Enterprise Manager Siebel Plugin — Agent Support 11.1.0.1.0

Provisioning Advisor Framework Common Files For Agent and OMS 11.1.0.1.0

Common component for  Virtualization 11.1.0.1.0

Oracle Notification Service (eONS) 11.2.0.1.0

Enterprise Manager Collaboration Suite Plugin — Agent Support 11.1.0.1.0

Oracle Notification Service 10.1.3.0.0

Oracle Required Support Files 32 bit 11.1.0.7.0

OracleAS HTTP Client 11.1.1.2.0

OracleAS J2EE BULKOPS 11.0.0.0.0

Oracle Wallet Manager 11.1.0.7.0

Oracle Security Developer Tools 11.1.0.7.0

Oracle Universal Installer 11.1.0.8.0

Oracle One-Off Patch Installer 11.1.0.8.0

Installer SDK Component 11.1.0.8.0

Sun JDK 1.6.0.6.0

—————————————————————————–

Installation in progress

…………………………………Installation in progress

……………..

Install successful

Linking in progress

Setup in progress

…..

Setup successful

Warning: The following configuration scripts needs to be executed as the “root” user

/u01/oracle/agent11g/root.sh

To execute the configuration scripts:

1. Open a new  terminal window

2. Login in as “root”

3. Run the scripts

To check progress – cd $AGENT_HOME/cfgtoollogs/cfgfw

cd /u01/oracle/agent11g/cfgtoollogs/cfgfw

tail –f CfmLogger_2011-02-03_08-55-32-AM.log

Final few lines –

Verifying the update…

Inventory check OK: Patch ID 9676134 is registered in Oracle Home inventory with proper meta-data.

Files check OK: Files from Patch ID 9676134 are present in Oracle Home.

The local system has been patched and can be restarted.

UtilSession: N-Apply done.

OPatch succeeded.

Finished one-offs apply…

Deleting the files…

Finished deleting the above files…

Configuration assistant “Agent Oneoff Patch Application” Succeeded

Running Configuration assistant “Agent Configuration Assistant”

Performing free port detection on host=oatu009n2.greatwest.com

Securing the agent

Performing targets discovery and agent configuration

Starting the agent

Configuration assistant “Agent Configuration Assistant” Succeeded

AgentPlugIn:agent configuration finished with status = true

Running Configuration assistant “Agent Add-on Plug-in”

Configuration assistant “Agent Add-on Plug-in” Succeeded

CONTROL C to quit –

 

cd /u01/oracle/agent11g/bin >  ./emctl status agent

Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0

Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.

—————————————————————

Agent Version     : 11.1.0.1.0

OMS Version       : 10.2.0.5.0

Protocol Version  : 10.2.0.5.0

Agent Home        : /u01/oracle/agent11g

Agent binaries    : /u01/oracle/agent11g

Agent Process ID  : 1089764

Parent Process ID : 1581084

Agent URL         : https://uatu024.greatwest.com:3872/emd/main/

Repository URL    : https://prdu010.greatwest.com:1159/em/upload

Started at        : 2011-02-02 10:53:14

Started by user   : ofsax

Last Reload       : 2011-02-02 10:54:59

Last successful upload                       : 2011-02-02 10:55:01

Total Megabytes of XML files uploaded so far :    17.16

Number of XML files pending upload           :        0

Size of XML files pending upload(MB)         :     0.00

Available disk space on upload filesystem    :    49.58%

Last successful heartbeat to OMS             : 2011-02-02 10:55:20

—————————————————————

Agent is Running and Ready

[UAT]ofsax:/u01/ofsax/agent11g/bin>

Get unix team to run root.sh from this area – /u01/oracle/agent11g

Then configure the dbsnmp user in database instances via Grid control.

Login to Grid control – Configure –Next Submit.

Troubleshoot for any install errors under the /tmp logs

zoeprd:/tmp/OraInstall2011-02-23_01-35-04PM> pwd

/tmp/OraInstall2011-02-23_01-35-04PM

zoeprd:/tmp/OraInstall2011-02-23_01-35-04PM> l

-rw——-    1 oracle   dba               0 Feb 23 13:35 oraInstall2011-02-23_01-35-04PM.err

drwxr-xr-x    3 oracle   dba             256 Feb 23 13:35 install

-rw——-    1 oracle   dba             177 Feb 23 13:35 oraInstall2011-02-23_01-35-04PM.out

Posted in EM10g Grid Control | 1 Comment »

ORA-00328 archived log ends at change 29604569404, need later change 29605086441

Posted by Hendry chinnapparaj on February 21, 2011

ORA-00328 archived log ends at change 29604569404, need later change 29605086441

Problem:-

The Dataguard / standby database has these errors when the MRP0 – Recover session was initiated.

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT from session

Sun Feb 20 14:04:25 2011

Media Recovery Log /u03/oradata/flash_recovery_area/ZOEPRD_JS/archivelog/2011_02_20/o1_mf_1_22383_1FJ-7GqbN_.arc

Errors with log /u03/oradata/flash_recovery_area/ZOEPRD_JS/archivelog/2011_02_20/o1_mf_1_22383_1FJ-7GqbN_.arc

MRP0: Background Media Recovery terminated with error 328

Sun Feb 20 14:04:25 2011

Errors in file /u01/oracle/admin/zoeprd/bdump/zoeprd_mrp0_790780.trc:

ORA-00328: archived log ends at change 29604569404, need later change 29605086441

ORA-00334: archived log: ‘/u03/oradata/flash_recovery_area/ZOEPRD_JS/archivelog/2011_02_20/o1_mf_1_22383_1FJ-7GqbN_.arc’

Recovery interrupted!

Sun Feb 20 14:04:26 2011

Errors in file /u01/oracle/admin/zoeprd/bdump/zoeprd_mrp0_790780.trc:

ORA-00328: archived log ends at change 29604569404, need later change 29605086441

ORA-00334: archived log: ‘/u03/oradata/flash_recovery_area/ZOEPRD_JS/archivelog/2011_02_20/o1_mf_1_22383_1FJ-7GqbN_.arc’

Sun Feb 20 14:04:26 2011

MRP0: Background Media Recovery process shutdown (zoeprd)

Cause:-

The issue can be due to many reasons realting to archived logs and SCN having wrong meta entry / data.

Standby Oracle / control file is looking for a specific Archivelog / specific SCN which it thinks is required for Recovery which  is not available in the archived log. Somewhere in the log shipping / transfer

things got distorted inside the log causing issues / .

Even if you copy the logs from primary and register / replace the on standby and apply them – still the standby controlfile cannot recover them

Even if you recreate standby controlfile and recover, still comes up with same errors

So what do you do finally ? try this one as it worked fo rme

Fix:-

12.7 Using RMAN Incremental Backups to Roll Forward a Physical Standby Database

http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/scenarios.htm#CIHIAADC

In cases where a physical standby database is far behind the primary database, an RMAN incremental backup can be used to roll the standby database forward faster than redo log apply. In this procedure, the RMAN BACKUP INCREMENTAL FROM SCN command is used to create an incremental backup on the primary database that starts at the current SCN of the standby and is used to roll forward the standby database.

Note:

The steps in this section can also be used to resolve problems if a physical standby database has lost or corrupted archived redo data or has an unresolveable archive gap.

1.     On the standby database, stop the managed recovery process (MRP):

2.         SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

3.     On the standby database, find the SCN which will be used for the incremental backup at the primary database:

4.         SQL> SELECT CURRENT_SCN FROM V$DATABASE;

SQL> column current_scn format 999999999999999
SQL> SELECT CURRENT_SCN FROM V$DATABASE;

     CURRENT_SCN
----------------
     29615710642

5.     In RMAN, connect to the primary database and create an incremental backup from the SCN derived in the previous step:

6.         RMAN> BACKUP INCREMENTAL FROM SCN <SCN from previous step>
7.         DATABASE FORMAT '/u03/oradata/backup/ForStandby_%U' tag 'FORSTANDBY';

Note:

RMAN does not consider the incremental backup as part of a backup strategy at the source database. Hence:

o        The backup is not suitable for use in a normal RECOVER DATABASE operation at the source database

o        The backup is not cataloged at the source database

o        The backup sets produced by this command are written to the /dbs location by default, even if the flash recovery area or some other backup destination is defined as the default for disk backups.

o        You must create this incremental backup on disk for it to be useful. When you move the incremental backup to the standby database, you must catalog it at the standby as described in Oracle Database Backup and Recovery Advanced User’s Guide. Backups on tape cannot be cataloged.

Rman>

Run

{

ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT  ‘/u03/oradata/backup/ForStandby_%U’;

      BACKUP INCREMENTAL FROM SCN 29615710642 DATABASE FORMAT '/u03/oradata/backup/ForStandby_%U' tag 'FORSTANDBY';

}

input datafile fno=00646 name=/u04/oradata/zoeprd/CB151_01.DBF

input datafile fno=00657 name=/u02/oradata/zoeprd/CBIDX156_01.DBF

input datafile fno=00668 name=/u02/oradata/zoeprd/CSH067_01.DBF

channel disk1: starting piece 1 at 20-02-2011 12:44:34

channel disk1: finished piece 1 at 20-02-2011 12:48:59

piece handle=/u03/oradata/backup/ForStandby_58m5501i_1_1 tag=FORSTANDBY comment=NONE

channel disk1: backup set complete, elapsed time: 00:04:25

Finished backup at 20-02-2011 12:48:59

Starting Control File and SPFILE Autobackup at 20-02-2011 12:49:00

piece handle=/u03/oradata/flash_recovery_area/ZOEPRD_FC/autobackup/2011_02_20/o1_mf_s_743604540_1FJwU-jRQ_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 20-02-2011 12:49:02

released channel: disk1

8.     Transfer all backup sets created on the primary system to the standby system (note that there may be more than one backup file created). For example:

9.         SCP /tmp/ForStandby_* standby:/tmp
 
ftp drpu009n1
/u03/oradata/backup /u03/oradata/backup

10. Connect to the standby database as the RMAN target, and catalog all incremental backup pieces:

11.    RMAN> CATALOG START WITH '/tmp/ForStandby';

RMAN> catalog start with '/u03/oradata/backup';

searching for all files that match the pattern /u03/oradata/backup

List of Files Unknown to the Database
=====================================
File Name: /u03/oradata/backup/ForStandby_4sm54tbs_1_1
File Name: /u03/oradata/backup/ForStandby_4tm54tnb_1_1
File Name: /u03/oradata/backup/ForStandby_4um54u0u_1_1
File Name: /u03/oradata/backup/ForStandby_51m54ub5_1_1
File Name: /u03/oradata/backup/ForStandby_52m54ugm_1_1
File Name: /u03/oradata/backup/ForStandby_53m54uod_1_1
File Name: /u03/oradata/backup/ForStandby_54m54v05_1_1
File Name: /u03/oradata/backup/ForStandby_55m54v86_1_1
File Name: /u03/oradata/backup/ForStandby_56m54vft_1_1
File Name: /u03/oradata/backup/ForStandby_57m54vnu_1_1
File Name: /u03/oradata/backup/ForStandby_58m5501i_1_1

Do you really want to catalog the above files (enter YES or NO)? yes

cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u03/oradata/backup/ForStandby_4sm54tbs_1_1
File Name: /u03/oradata/backup/ForStandby_4tm54tnb_1_1
File Name: /u03/oradata/backup/ForStandby_4um54u0u_1_1
File Name: /u03/oradata/backup/ForStandby_51m54ub5_1_1
File Name: /u03/oradata/backup/ForStandby_52m54ugm_1_1
File Name: /u03/oradata/backup/ForStandby_53m54uod_1_1
File Name: /u03/oradata/backup/ForStandby_54m54v05_1_1
File Name: /u03/oradata/backup/ForStandby_55m54v86_1_1
File Name: /u03/oradata/backup/ForStandby_56m54vft_1_1
File Name: /u03/oradata/backup/ForStandby_57m54vnu_1_1
File Name: /u03/oradata/backup/ForStandby_58m5501i_1_1

12. Recover the standby database with the cataloged incremental backup:

13.    RMAN> RECOVER DATABASE NOREDO;

Generic Note

------------------------

Use RMAN> catalog backup piece and try the recovery. And when you do the recovery allocate the disk in RMAN as you have copied / ftp’ed these files to the disk

You might encounter these errors because the primary and standby archived logGs location are different. Either create the same directories as in primary or catalog these files

destination for restore of datafile 00647: /u02/oradata/zoeprd/CBIDX151_01.DBF
destination for restore of datafile 00658: /u04/oradata/zoeprd/CB157_01.DBF
channel ORA_DISK_2: reading from backup piece /u03/oradata/flash_recovery_area/ZOEPRD_FC/backupset/2011_02_20/o1_mf_nnnd1_TAG20110219T233049_1FJG22WSN_.bkp
ORA-19870: error reading backup piece /u03/oradata/flash_recovery_area/ZOEPRD_FC/backupset/2011_02_20/o1_mf_nnnd1_TAG20110219T233049_1FJG22WSN_.bkp
ORA-19505: failed to identify file "/u03/oradata/flash_recovery_area/ZOEPRD_FC/backupset/2011_02_20/o1_mf_nnnd1_TAG20110219T233049_1FJG22WSN_.bkp"
ORA-17503: ksfdopn:4 Failed to open file /u03/oradata/flash_recovery_area/ZOEPRD_FC/backupset/2011_02_20/o1_mf_nnnd1_TAG20110219T233049_1FJG22WSN_.bk
failover to copy on device type SBT_TAPE
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/20/2011 13:20:20
ORA-19870: error reading backup piece 3pm53j82_1_2
ORA-19507: failed to retrieve sequential file, handle="3pm53j82_1_2", parms=""
ORA-27029: skgfrtrv: sbtrestore returned error
ORA-19511: Error received from media manager layer, error text:
   ANS1302E (RC2)    No objects on server match query

RMAN> RECOVER DATABASE NOREDO;

Starting recover at 20-02-2011 13:25:01

using channel ORA_SBT_TAPE_1
using channel ORA_SBT_TAPE_2
using channel ORA_DISK_1
using channel ORA_DISK_2
datafile 269 not processed because file is read-only
datafile 270 not processed because file is read-only
datafile 271 not processed because file is read-only
…
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/20/2011 13:26:29
ORA-27191: sbtinfo2 returned error
Additional information: 2

The above error shows – recover is not done and skips the read only datafiles as the SCN are still the same

14. In RMAN, connect to the primary database and create a standby control file backup:

15.    RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/tmp/ForStandbyCTRL.bck';

Run
{

ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT ‘/u03/oradata/backup/sttbyctl.bck’;

backup current controlfile for standby format '/u03/oradata/backup/stbyctl.bck';
}

allocated channel: disk1
channel disk1: sid=634 devtype=DISK

Starting backup at 20-02-2011 13:42:19
channel disk1: starting compressed full datafile backupset
channel disk1: specifying datafile(s) in backupset
including standby control file in backupset
channel disk1: starting piece 1 at 20-02-2011 13:42:20
channel disk1: finished piece 1 at 20-02-2011 13:42:23
piece handle=/u03/oradata/backup/stbyctl.bck tag=TAG20110220T134219 comment=NONE
channel disk1: backup set complete, elapsed time: 00:00:03
Finished backup at 20-02-2011 13:42:23

Starting Control File and SPFILE Autobackup at 20-02-2011 13:42:24
piece handle=/u03/oradata/flash_recovery_area/ZOEPRD_FC/autobackup/2011_02_20/o1_mf_s_743607761_1FJzUyGbV_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 20-02-2011 13:42:43
released channel: disk1

16. Copy the standby control file backup to the standby system. For example:

17.    SCP /tmp/ForStandbyCTRL.bck standby:/tmp

:: No such file or directory
ftp> lcd /u03/oradata/backup
Local directory now /u03/oradata/backup
ftp> cd /u03/oradata/backup
250 CWD command successful.
ftp> binary
200 Type set to I.
ftp> put stbyctl.bck
200 PORT command successful.
150 Opening data connection for stbyctl.bck.
226 Transfer complete.
3260416 bytes sent in 0.144 seconds (2.211e+04 Kbytes/s)
local: stbyctl.bck remote: stbyctl.bck

18. Shut down the standby database and startup nomount:

19.    RMAN> SHUTDOWN;
20.    RMAN> STARTUP NOMOUNT;

21. In RMAN, connect to standby database and restore the standby control file:

22.    RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/ForStandbyCTRL.bck';

connected to target database: zoeprd (not mounted)
connected to recovery catalog database

RMAN> run
2> {
3> ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/u03/oradata/backup/sttbyctl.bck';
4> RESTORE STANDBY CONTROLFILE FROM '/u03/oradata/backup/stbyctl.bck';
5> }

allocated channel: disk1
channel disk1: sid=816 devtype=DISK

Starting restore at 20-02-2011 13:48:13

channel disk1: restoring control file
channel disk1: restore complete, elapsed time: 00:00:04
output filename=/u02/oradata/zoeprd/control01.ctl
output filename=/u03/oradata/zoeprd/control02.ctl
output filename=/u04/oradata/zoeprd/control03.ctl
Finished restore at 20-02-2011 13:48:22
released channel: disk1

23. Shut down the standby database and startup mount:

24.    RMAN> SHUTDOWN;
25.    RMAN> STARTUP MOUNT;

26. If the primary and standby database data file directories are identical, skip to step 13. If the primary and standby database data file directories are different, then in RMAN, connect to the standby database, catalog the standby data files, and switch the standby database to use the just-cataloged data files. For example:

27.    RMAN> CATALOG START WITH '+DATA_1/CHICAGO/DATAFILE/'; 
28.    RMAN> SWITCH DATABASE TO COPY;

29. If the primary and standby database redo log directories are identical, skip to step 14. Otherwise, on the standby database, use an OS utility or the asmcmd utility (if it is an ASM-managed database) to remove all online and standby redo logs from the standby directories and ensure that the LOG_FILE_NAME_CONVERT parameter is properly defined to translate log directory paths. For example, LOG_FILE_NAME_CONVERT='/BOSTON/','/CHICAGO/'.

30. On the standby database, clear all standby redo log groups (there may be more than 3):

31.    SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;
32.    SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;
33.    SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;

SQL> column member format a50
SQL> l
  1* select * from v$logfile
SQL> /

    GROUP# STATUS  TYPE    MEMBER                                             IS_
---------- ------- ------- -------------------------------------------------- ---
         9         STANDBY /u03/oradata/zoeprd/stdby_redo_09.log              NO
        10         STANDBY /u03/oradata/zoeprd/stdby_redo_10.log              NO
        11         STANDBY /u03/oradata/zoeprd/stdby_redo_11.log              NO
         4         ONLINE  /u02/oradata/zoeprd/redo_04a.log                   NO
         4         ONLINE  /u03/oradata/zoeprd/redo_04b.log                   NO
         5         ONLINE  /u02/oradata/zoeprd/redo_05a.log                   NO
         5         ONLINE  /u03/oradata/zoeprd/redo_05b.log                   NO
        12         STANDBY /u03/oradata/zoeprd/stdby_redo_12.log              NO
        13         STANDBY /u03/oradata/zoeprd/stdby_redo_13.log              NO
         6         ONLINE  /u02/oradata/zoeprd/redo_06a.log                   NO
         6         ONLINE  /u03/oradata/zoeprd/redo_06b.log                   NO
         7         ONLINE  /u02/oradata/zoeprd/redo_07a.log                   NO
         7         ONLINE  /u03/oradata/zoeprd/redo_07b.log                   NO

13 rows selected.

SQL> alter database clear logfile group 9;

Database altered.

SQL> alter database clear logfile group 10;

Database altered.

SQL> alter database clear logfile group 11;

Database altered.

SQL> alter database clear logfile group 12;

Database altered.

SQL> alter database clear logfile group 13;

Database altered.

34. On the standby database, restart Flashback Database:

35.    SQL> ALTER DATABASE FLASHBACK OFF; 
36.    SQL> ALTER DATABASE FLASHBACK ON;

37. On the standby database, restart MRP:

38.    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT from session;

Also recreate the block_change_tracking file if it’s used as it may be disabled.

Posted in Data Guard and DR | Leave a Comment »

ORA-10567: Redo is inconsistent with data block (file# 124, block# 214081)

Posted by Hendry chinnapparaj on February 21, 2011

 

ORA-10567: Redo is inconsistent with data block (file# 124, block# 214081)

Problem

———–

The following errors occurs in standby database – seen in oracle alert log

Completed: ALTER DATABASE RECOVER  managed standby database using current logfile disconnect

Sat Feb 19 08:19:13 2011

Errors in file /u01/oracle/admin/zoeprd/bdump/zoeprd_p000_1470606.trc:

ORA-00600: internal error code, arguments: [3020], [124], [214081], [520307777], [], [], [], []

ORA-10567: Redo is inconsistent with data block (file# 124, block# 214081)

ORA-10564: tablespace ZOE_LM_INDEX_1M

ORA-01110: data file 124: ‘/u02/oradata/zoeprd/ZOE_LM_INDEX_1M_01.DBF’

ORA-10561: block type ‘TRANSACTION MANAGED INDEX BLOCK’, data object# 65407

Sat Feb 19 08:19:13 2011

Errors in file /u01/oracle/admin/zoeprd/bdump/zoeprd_p002_757812.trc:

ORA-00600: internal error code, arguments: [3020], [2], [43149], [8431757], [], [], [], []

ORA-10567: Redo is inconsistent with data block (file# 2, block# 43149)

ORA-10564: tablespace UNDOTBS1

ORA-01110: data file 2: ‘/u02/oradata/zoeprd/undotbs01.dbf’

ORA-10560: block type ‘KTU UNDO BLOCK’

Sat Feb 19 08:19:13 2011

Errors in file /u01/oracle/admin/zoeprd/bdump/zoeprd_p001_1200318.trc:

ORA-00600: internal error code, arguments: [3020], [466], [15128], [1954560792], [], [], [], []

ORA-10567: Redo is inconsistent with data block (file# 466, block# 15128)

ORA-10564: tablespace DOCIDX021

ORA-01110: data file 466: ‘/u02/oradata/zoeprd/DOCIDX021_01.DBF’

ORA-10561: block type ‘TRANSACTION MANAGED INDEX BLOCK’, data object# 139733

Sat Feb 19 08:19:15 2011

Errors in file /u01/oracle/admin/zoeprd/bdump/zoeprd_p000_1470606.trc:

ORA-00600: internal error code, arguments: [3020], [124], [214081], [520307777], [], [], [], []

ORA-10567: Redo is inconsistent with data block (file# 124, block# 214081)

ORA-10564: tablespace ZOE_LM_INDEX_1M

ORA-01110: data file 124: ‘/u02/oradata/zoeprd/ZOE_LM_INDEX_1M_01.DBF’

ORA-10561: block type ‘TRANSACTION MANAGED INDEX BLOCK’, data object# 65407

Sat Feb 19 08:19:15 2011

Errors in file /u01/oracle/admin/zoeprd/bdump/zoeprd_p002_757812.trc:

ORA-00600: internal error code, arguments: [3020], [2], [43149], [8431757], [], [], [], []

ORA-10567: Redo is inconsistent with data block (file# 2, block# 43149)

ORA-10564: tablespace UNDOTBS1

ORA-01110: data file 2: ‘/u02/oradata/zoeprd/undotbs01.dbf’

ORA-10560: block type ‘KTU UNDO BLOCK’

Sat Feb 19 08:19:15 2011

Errors in file /u01/oracle/admin/zoeprd/bdump/zoeprd_p001_1200318.trc:

ORA-00600: internal error code, arguments: [3020], [466], [15128], [1954560792], [], [], [], []

ORA-10567: Redo is inconsistent with data block (file# 466, block# 15128)

ORA-10564: tablespace DOCIDX021

ORA-01110: data file 466: ‘/u02/oradata/zoeprd/DOCIDX021_01.DBF’

ORA-10561: block type ‘TRANSACTION MANAGED INDEX BLOCK’, data object# 139733

Sat Feb 19 08:19:16 2011

MRP0: Background Media Recovery terminated with error 12801

Sat Feb 19 08:19:16 2011

 

Cause:-

———

The Redo when shipped across network or somewhere along the way had some issues which caused the inconsistency, hence not able to syncup with the standby datafiles.

 

 

Fix:-

——–

 

Get the files from the primary and syncup the standby as below

 

There are 3 files affected

File numbers are 466, 124 and 2

1) You have to find out the tablespace name related to this datafiles

‘/u02/oradata/zoeprd/DOCIDX021_01.DBF’ ==> File 466
‘/u02/oradata/zoeprd/ZOE_LM_INDEX_1M_01.DBF’ ==> File 124
‘/u02/oradata/zoeprd/undotbs01.dbf’ ===> File 2

From primary sql> select tablespace_name from dba_data_files where file_name = ‘/u02/oradata/zoeprd/DOCIDX021_01.DBF’ ;
do the same for rest of 2 files also..

2) From primary sql> ALTER tablespace <tablespacename> begin backup;

3) Copy (SCP/FTP) the datafile file from PRIMARY to STANDBY

 

 

ftp <stby server name>

4) From primary sql> ALTER tablespace <tablespacename> end backup;

5) Start the recovery in standby
recover managed standby database using current logfile disconnect;
6) monitor the alert log if any issues

 

 

 

 

 

 

 

 

 

 

 

 

 

Posted in Data Guard and DR | 1 Comment »

ORA-29283: invalid file operation ORA-06512: at “SYS.UTL_FILE”, line 488

Posted by Hendry chinnapparaj on February 4, 2011

Problem

————–

When doing export form datapump

ORA-29283: invalid file operation
ORA-06512: at “SYS.UTL_FILE”, line 488

Solution

————–

create the unix directory and the data pump directory in oracle

DROP DIRECTORY DATA_PUMP_DIR;

 

CREATE OR REPLACE DIRECTORY

DATA_PUMP_DIR AS

‘/u01/oracle/admin/msoa1_js/dpdump/’;

 

 

GRANT READ, WRITE ON DIRECTORY SYS.DATA_PUMP_DIR TO dp WITH GRANT OPTION;

 

GRANT READ, WRITE ON DIRECTORY SYS.DATA_PUMP_DIR TO EXP_FULL_DATABASE;

 

GRANT READ, WRITE ON DIRECTORY SYS.DATA_PUMP_DIR TO IMP_FULL_DATABASE;

 

Posted in ORA- ERRORS relating to ORACLE DATABASE | Leave a Comment »

EXP-00056: ORACLE error 1017 encountered ORA-01017: invalid username/password; logon denied

Posted by Hendry chinnapparaj on February 4, 2011

Problem

————–

The OS Authentication fails due to the missing user OPS$ORACLE

Cpsoa1:/u01/oracle> exp /

Export: Release 10.2.0.4.0 – Production on Tue Jan 11 13:52:43 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

EXP-00056: ORACLE error 1017 encountered

ORA-01017: invalid username/password; logon denied

Username:

Password:

Solution

————

create user and grant permission for OS Authentication

CREATE USER OPS$ORACLE

IDENTIFIED EXTERNALLY

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TEMP

PROFILE DEFAULT

ACCOUNT UNLOCK;

— 4 Roles for OPS$ORACLE

–GRANT DATAPUMP_EXP_FULL_DATABASE TO OPS$ORACLE;

GRANT IMP_FULL_DATABASE TO OPS$ORACLE;

–GRANT DATAPUMP_IMP_FULL_DATABASE TO OPS$ORACLE;

GRANT EXP_FULL_DATABASE TO OPS$ORACLE;

ALTER USER OPS$ORACLE DEFAULT ROLE ALL;

— 1 Tablespace Quota for OPS$ORACLE

ALTER USER OPS$ORACLE QUOTA UNLIMITED ON USERS;

Grant create session to OPS$ORACLE;

Posted in ORA- ERRORS relating to ORACLE DATABASE | Leave a Comment »