Friday, March 4, 2016

Cluvfy returns "Unsuccessful" for most commands; trace has exectask.sh: "cannot execute" / "permission denied", or scp: "not found" [ID 549667.1]


________________________________________
       
In this Document
  Symptoms
     Examples:
  Changes
  Cause
     Cause #1 - Wrong permissions on exectask
     Cause #2 - scp in wrong location
  Solution
     Solution #1 - Adjust permissions on exectask.
     Solution #2 - Create a symlink to scp in the expected location.
     Scalability RAC Community
  References
________________________________________


Applies to:
Oracle Server - Standard Edition - Version: 10.2.0.1 to 11.2.0.2 - Release: 10.2 to 11.2
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.2.0.2   [Release: 10.2 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 19-Oct-2010***
Symptoms

Cluster is functioning perfectly, but running the Cluster Verification Utility (cluvfy) with any commands always returns "unsuccessful" with no further details, even with -verbose switch.
Examples:
1.
#cluvfy comp admprv -n mynode1,mynode2 -verbose -o crs_inst
Verifying administrative privileges
Checking user equivalence...
Check: User equivalence for user "root"
  Node Name                             Comment
  ------------------------------------  ------------------------
  mynode1                               passed
  mynode2                               passed
Result: User equivalence check passed for user "root".
Verification of administrative privileges was unsuccessful on all the nodes.

2.
#cluvfy comp nodecon -n mynode1,mynode2 -verbose
Verifying node connectivity
Verification of node connectivity was unsuccessful on all the nodes.

3.
#cluvfy comp crs -n mynode1,mynode2
Verifying CRS integrity
Verification of CRS integrity was unsuccessful on all the nodes.
4.
 ./runcluvfy.sh stage -post crsinst -n mynode1,mynode2 -verbose
Performing post-checks for cluster services setup

Checking node reachability...

Check: Node reachability from node "mynode1"
Destination                          Node Reachable?
------------------------------------ ------------------------
mynode1                              yes
mynode2                              yes
Result: Node reachability check passed from node "mynode1".


Checking user equivalence...

Check: User equivalence for user "oracle"
Node Name                        Comment
-------------------------------- ------------------------
mynode1                         passed
mynode2                         passed
Result: User equivalence check passed for user "oracle".

Post-check for cluster services setup was unsuccessful on all the nodes.
Changes
The issue can occur before, during or after installation.  After installation, the triggering event can be the application of a patch, or a change in the OS environment such as permissions change.  In some cases on 10.2 and 11.1 the issue was simply not noticed until after installation.
Cause
There are a couple of possible causes for this reason. To check for which cause you may be encountering, trace cvu and examine cvutrace.log .

For instructions on tracing cluvfy, please see this note:
Note 316817.1 - CLUSTER VERIFICATION UTILITY FAQ
under the section "How do I turn on tracing?"

If using runcluvfy.sh, see this note for tracing instructions:
Note 986822.1 - How to Collect CVU Trace / Debug Output Generated by RUNCLUVFY.SH
Cause #1 - Wrong permissions on exectask
After turning CVU tracing on, the following message can be seen in the cvutrace.log :
ksh: /tmp/CVU_10.2.0.1.0.1_dba/exectask.sh: cannot execute
Or, in an 11.2 install, in which the installer runs cluvfy, the installActionsDATE.log shows "exectask.sh: Execute permission denied", for example:
sh: /var/tmp/CVU_11.2.0.2.0_grid/exectask.sh: Execute permission denied.
Cause:
a) For already installed clusterware: Some CRS patches may change the permissions on $CRS_HOME/cv/remenv/exectask* .  If $CRS_HOME/cv/remenv/exectask* is not executable, then cluvfy will fail.
b) Previous runs of cvu can leave /tmp/CVU* or /var/tmp/CVU* directories in place; the permissions of exectask  must be correct in this location too, or cluvfy will fail.
Cause #2 - scp in wrong location
After turning CVU tracing on, the following message can be seen in the cvutrace.log :
exec exception:/usr/local/bin/scp: not found;
Cause: scp is not located in /usr/local/bin/scp but in a different place on the server.
Solution
Use either solution 1 or solution 2, depending on which of the above causes applies to your situation.
Solution #1 - Adjust permissions on exectask.

Steps:
1. Check the permissions with:
ls -l $CRS_HOME/cv/remenv/exectask*

2. Ensure that the files are owned by
oracle:oinstall
AND
permissions are 744 or 755

3.  Check for the existence of a /tmp/CVU* or /var/tmp/CVU* directory created by previous, failed run of cluvfy.  If the directory exists and exectask* exists in this directory, then make the same change to the permissions on these exectask* files as well.
Solution #2 - Create a symlink to scp in the expected location.

Steps:
1. Find scp
2. As root user, create a symlink to /usr/local/bin

eg. if scp is located in /usr/bin, create a symlink as follows:
# ln -s /usr/bin/scp /usr/local/bin/scp

Thursday, June 25, 2015

Error connecting ASM ORA-15055: unable to connect to ASM instance Fatal NI connect error 12547 ORA-12547: TNS:lost contact

TNS-12547: TNS:lost contact
    ns secondary err code: 12560
    nt main err code: 517
ORA-15055: unable to connect to ASM instance
ORA-12547: TNS:lost contact
TNS-12545: Connect failed because target host or object does not exist


Cause:

$GRID_HOME/bin/oracle or $ORACLE_HOME/bin/oracle permission has been changed

ls -al $GRID_HOME/bin/oracle
-rwxr-x--x 1 grid oinstall 200678464 Jun 28 14:54 oracle 

ls -al $ORACLE_HOME/bin/oracle
-rwxr-x--x 1 oracle asmadmin 228886191 Jun 28 15:41 oracle

Solution:

Change permissions as below 

cd $GRID_HOME/bin
chmod 6751 oracle

cd $ORACLE_HOME/bin
chmod 6751 oracle

Once it looks as below the problem is solved...

ls -l $GRID_HOME/bin/oracle
-rwsr-s--x 1 grid oinstall 203974257 Jun 29 09:30 oracle

ls -l $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 oracle oinstall 232399431 Jun 29 13:47 oracle

Monday, June 15, 2015

ORA-02097: parameter cannot be modified because specified value is invalid

Alert log Entry

ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00068: invalid value 4000 for parameter parallel_max_servers, must be between 0 and 3600
CKPT (ospid: 3277292): terminating the instance due to error 2097
Mon Jun 15 12:30:13 

Cause 1
It might be caused by the current system level change on the number of CPU which will shut down all the instances on the same server.

Solution

Just start the instances no need to worry oracle will take care of itself.

Cause 2

Related to parallel_max_servers wich is calculated 

PARALLEL_THREADS_PER_CPU * CPU_COUNT * concurrent_parallel_users * 5

and its value should range between 0 and 300.

Solution

Modify its value reasonably using alter system.

Saturday, June 13, 2015

Sysman password changing in oracle 11g

We may need to change the passwords of different users including sysman as a security measure or for other reason.But after changing the sysman password the enterprise manager console may not work, because we need to update the password using the following setps

step 1. Stop the dbconsole

export ORACLE_UNQNAME=DBNAME
emctl stop dbconsole

step 2. connect as sysdba and change the passowrd of sysman as required

SQL> alter user sysman idntified by XXXXX;

step 3. Update the password

emctl setpasswd dbconlole

step 4. Start the dbconsole

export ORACLE_UNQNAME=DBNAME
emctl start dbconsole


Now the enterprise manager works properly

Friday, April 24, 2015

ORA-17628: Oracle error 19505 returned by remote Oracle server


RMAN error

released channel: t1
released channel: t2
released channel: t3
released channel: t4
released channel: t5
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 03/01/2015 09:19:11
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on t1 channel at 03/01/2015 09:19:11
ORA-17628: Oracle error 19505 returned by remote Oracle server



CAUSE

1. Shortage of space in the destination directory which will be accompanied by alertlog enter similar to the following:

ORA-19505: failed to identify file "+data"
ORA-17502: ksfdcre:4 Failed to create file +data
ORA-15041: diskgroup "DATA" space exhausted

Solution

Check the destination directory for data and archive files specified by DB_CREATE_FILE_DEST and DB_RECOVERY_FILE_DEST parameters respectively. Then make space in the specified diskgroup as manifested in the alert log ifle as exhausted,either by adding disks to it or deleting unwanted files from it.


2.Missing destination directory for DB_CREATE_FILE_DEST and DB_RECOVERY_FILE_DEST

Solution

Specify appropriate valid destinations for DB_CREATE_FILE_DEST and DB_RECOVERY_FILE_DEST to match with DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT.

Remember sometimes this error requires us to create destinations manually

for instance:
mkidr ARCHIVELOG/ CONTROLFILE/ DATAFILE/ ONLINELOG/ PARAMETER/ TEMPFILE

Thursday, April 16, 2015

ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

Reference Note 748251.1

Possible Cause 

ISSUE IS LIKELY CAUSED BY 
Bug 5689290 - VIEW V$RMAN_BACKUP_JOB_DETAILS SLOW CAUSING DATABASE CONSOLE LOGIN TIMEOUTS
which identified an issue with excessive temp space usage by emagent.

Bug 5689290 was closed as a duplicate of
Bug 5466436 - VIEW V$RMAN_BACKUP_JOB_DETAILS SLOW CAUSING DATABASE CONSOLE LOGIN TIMEOUTS
which was subsequently closed as a duplicate of:
Bug 8434467 - SLOW PERFORMANCE FOR QUERY ON V$RMAN_BACKUP_JOB_DETAILS

Solution

1.  Upgrade to any fixed version, e.g.
12.1 (Future Release)
11.2.0.2 (Server Patch Set)
11.2.0.1 Patch 4 on Windows Platforms
11.1.0.7 Patch 37 on Windows Platforms
10.2.0.5 Patch 4 on Windows Platforms
10.2.0.4 Patch 41 on Windows Platforms
- OR -

2. Apply Patch 8434467 as available for the your actual platform and Oracle version.
- OR -

3. Use the following workaround to reduce the temp usage by emagent / dbsnmp:
exec dbms_stats.DELETE_TABLE_STATS('SYS','X$KCCRSR'); ==> deletes the statistics on the fixed object
exec dbms_stats.LOCK_TABLE_STATS('SYS','X$KCCRSR'); ==> lock that object so that statistics will not be collected in future
alter system flush shared_pool;  ==> cannot be skipped

Tuesday, March 10, 2015

ORA-01580: error creating control backup

ERROR

Soon after launching the duplication or restore rman tries to restore the controlfile before it mounts the auxiliary database,which is not possible due to the rman configuration for snapshot controlfile which we can see with 
RMAN> show all;

or specifically

RMAN>show snopshot controlfile name;

Therefore the rman session hangs for sometime and throws the following error

RMAN-00571: ======================================================
RMAN-00569: ======== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ======================================================
RMAN-03002: failure of show command at 02/01/2015 01:35:50
RMAN-03014: implicit resync of recovery catalog failed
RMAN-03009: failure of full resync command on default channel at 11/08/2011 01:35:50
ORA-01580: error creating control backup file /u01/snapcf_ggdb1.f


SOLUTION

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME CLEAR;

sqlplus "/ as sysdba"
sys@db1> EXECUTE SYS.DBMS_BACKUP_RESTORE.CFILESETSNAPSHOTNAME('/u01/app/oracle/product/11.2/db/dbs/snapcf_db1.f');
PL/SQL procedure successfully completed.

And then restart the duplication or restore.

Tuesday, February 10, 2015

ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE

ORA-29861: domain index is marked LOADING/FAILED/UNUSABLE

Cause: 

An attempt has been made to access a domain index that is being built or is marked failed by an unsuccessful DDL or is marked unusable by a DDL operation.

Solution:

alter index indexname rebuild; 

Saturday, February 7, 2015

ORA-01450: maximum key length (3215) exceeded

SQL> ALTER INDEX TESTUSR.FBNK_ACCOUNT#HIS_207 REBUILD ONLINE PARALLEL 140 NOLOGGING;

                                                          *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded

Cause of the Problem

Consider the total length of the index which cannot exceed a certain value.The  DB_BLOCK_SIZE value takes the responsibility.
If 2K block size then maximum index key length=758
If 4K block size then maximum index key length=1578
If 8K block size then maximum index key length=3218
If 16K block size then maximum index key length=6498

How the maximum index key length is measured 

Maximum index key length=Total index length (Sum of width of all indexed column+the number of indexed columns)
+ Length of the key(2 bytes)+ROWID(6 bytes)+the length of the rowid(1 byte)

The index key size is limited by the value of db_block_size, because a key value may not span multiple blocks. So, based on the size of the block size of index depends. In fact, it is required that any index block must contain at least TWO index entries per block.

So we can say that the maximum key length for an index will be less than half of
the DB_BLOCK_SIZE. But we know that in a block there also needed space for PCTFREE, INITRANS and space for block overhead(Block Header,ROW Directory, Table Directory, etc). After considering these bytes the actual space that can be used for the Index key is actually just over 1/3 of the DB_BLOCK_SIZE.

Solution of the Problem

The causes already indicates what might be the solutions. Solution may be,
1)Increase your database block size. Create a tablespace with bigger block size and create index on that tablespace.

2)If you have index on multiple columns then you can split index to single or 2 columns so that size does not extended over it can handle.

3)Rebuild the index without online clause. That is 
ALTER INDEX index_name REBUILD;

Tuesday, January 27, 2015

ORA-27300: OS System Dependent Operation: Fork Failed With Status: 12

ALERT LOG ENTRY
Process startup failed, error stack:
Errors in file /u01/app/oracle/diag/rdbms/cbelive/PRODDB2/trace/PRODDB2_psp0_5898502.trc:
ORA-27300: OS system dependent operation:fork failed with status: 12
ORA-27301: OS failure message: Not enough space
ORA-27302: failure occurred at: skgpspawn3
====================CONTENT OF TRACE FILE======================
Trace file /u01/app/oracle/diag/rdbms/cbelive/PRODDB2/trace/PRODDB2_psp0_5898502.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/11.2.0.3/oracle
System name:    AIX
Node name:      t24db2
Release:        1
Version:        6
Machine:        00C941664C00
Instance name: PRODDB2
Redo thread mounted by this instance: 2
Oracle process number: 3
Unix process pid: 5898502, image: oracle@t24db2 (PSP0)

*** 2015-01-27 13:30:39.519
*** SESSION ID:(379.1) 2015-01-27 13:30:39.519
*** CLIENT ID:() 2015-01-27 13:30:39.519
*** SERVICE NAME:(SYS$BACKGROUND) 2015-01-27 13:30:39.519
*** MODULE NAME:() 2015-01-27 13:30:39.519
*** ACTION NAME:() 2015-01-27 13:30:39.519

Process startup failed, error stack:
ORA-27300: OS system dependent operation:fork failed with status: 12
ORA-27301: OS failure message: Not enough space
ORA-27302: failure occurred at: skgpspawn3

*** 2015-01-27 13:32:50.138
Process startup failed, error stack:
ORA-27300: OS system dependent operation:fork failed with status: 12
ORA-27301: OS failure message: Not enough space
ORA-27302: failure occurred at: skgpspawn3

*** 2015-01-27 13:33:24.157
Process startup failed, error stack:
ORA-27300: OS system dependent operation:fork failed with status: 12
ORA-27301: OS failure message: Not enough space
ORA-27302: failure occurred at: skgpspawn3
~
===========================================================

CAUSE


Insufficient paging space defined for the system program using excessive amount of paging space.

SOLUTION

Increase the OS paging space.

For AIX, the next APAR is advised:

Fix pack for: IZ19670: CANNOT INCREASE PAGING SPACE UNDER LOAD APPLIES TO AIX 6100-01

Please note the specific patches below:
  5300-09 - use AIX APAR IZ19504
  6100-01 - use AIX APAR IZ19670
  6100-02 - use AIX APAR IZ20052

For more information, please visit:
https://www-304.ibm.com/support/docview.wss?uid=isg1IZ19670

ORA-19809 LIMIT EXCEEDED FOR RECOVERY FILES

We encounter this error when the archivelog destination (specified by DB_RECOVERY_FILE_DEST parameter ) is full.
Solution
1. When we have time and resource consider adding disks to the diskgroup upon which the archive log destination resides and then increase the parameter DB_RECOVERY_FILE_DEST_SIZE parameter.
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=XXXG SCOPE=BOTH;
2.If the above solution doesn't work, use rman to delete the archive log which have been backed up to make some space in the destination.
export ORACLE_SID=TESTDB
rman target /
RMAN> delete noprompt archivelog all completed before 'sysdate - 1';
The above command deletes archivelogs which have been completed 24 hours ago if you have archivelog backup in last 24 hours then you are good to go otherwise change the sysdate - to larger number of days.
3. Consider configuring rman archive log deletion and retention policies.

Wednesday, December 31, 2014

11g DATAGURARD BROKER DGMGRL CONFIGURATION STEPS

Enterprise Manager Grid Control GUI or Command line interface can be used to configure Guard Broker. The following demonstration uses the command line version.
ASSUMPTION
There exists a function physical standby database ( i.e  The redo log shipping/transport is happening and the transported logs are being applied to make the standby database in sync with the production.)
Primary Database: PROD
Standby Database: PRODSTDBY

STEP 1. ON BOTH THE PRIMARY AND STANDBY DATABASES START THE DATAGUARD BROKER PROCESS

SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;
System altered.

STEP 2. EDIT THE listener.ora ON BOTH THE PRIMARY AND STANDBY NODES TO CONTAIN THE STATIC ENTRY FOR DBMGRL

Ensure that the GLOBAL_DBNAME is set to db_unique_name_DGMGRL.db_domain
SID_LIST_LISTENER =
  (SID_LIST =
 (SID_DESC =
        (GLOBAL_DBNAME = PROD_dgmgrl)
        (ORACLE_HOME = /u01/oracle/product/11.2.0.3/db_1)
            (SID_NAME = PROD)
        )
    )

STEP 3. CREATE THE CONFIGURATION

PROD:/u01/oracle$ dgmgrl
DGMGRL for IBM/AIX RISC System/6000: Version 11.1.0.7.0 - 64bit Production

Copyright (c) 2000, 2005, Oracle. All rights reserved.


Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys/xxxxxxx
Connected.
DGMGRL> CREATE CONFIGURATION 'ORIGINDR'
> AS
> PRIMARY DATABASE IS 'PROD'
> CONNECT IDENTIFIER IS 'PRODSTDBY'
> ;
Configuration "ORIGINDR" created with primary database "PROD"

STEP 4. ADD THE STANDBY DATABASE TO THE CONFIGURATION

DGMGRL> ADD DATABASE
> 'PRODSTDBY'
> AS
> CONNECT IDENTIFIER IS 'PRODSTDBY'
> ;
Database "PRODSTDBY" added

DGMGRL> SHOW CONFIGURATION

Configuration

  Name:                ORIGINDR
  Enabled:             NO
  Protection Mode:     MaxAvailability
  Databases:
    PROD - Primary database
    PRODSTDBY - Physical standby database

Fast-Start Failover: DISABLED


Current status for "ORIGINDR":

DISABLED

STEP 5. ENABLE THE CONFIGURATION

DGMGRL> ENABLE CONFIGURATION
Enabled.
DGMGRL> SHOW CONFIGURATION
Configuration
  Name:                ORIGINDR
  Enabled:             YES
  Protection Mode:     MaxAvailability
  Databases:
    PROD - Primary database
    PRODSTDBY - Physical standby database

Fast-Start Failover: DISABLED


Current status for "ORIGINDR":

SUCCESS

Saturday, December 20, 2014

ORA-15000: command disallowed by current instance type


ORA-15000: command disallowed by current instance type

As the error itself suggests we are not using the right instance type. It occurs when we try to adminitrer the ASM storage system from RDBMS instance such as adding disks,dropping disks,etc
Solution
Connect to the ASM instance and execute the command connecting 'as sysasm'
for instance
$export ORACLE_SID=+ASM
$sqlplus / as sysasm
SQL>ALTER DISKGROUP DATA ADD DISK '/dev/rhdisk391' REBALANCE POWER 11;

Thursday, December 4, 2014

REGISTERING A DATABASE WITH GRID CLUSTER


After database duplication some times you may not be able to stop,start or see the status of the database using srvctl utility. 
$ srvctl stop database -d TESTDB
PRCD-1120 : The resource for database TESTDB could not be found.
PRCR-1001 : Resource ora.TESTDB.db does not exist
Despite the above errors the database is up and running.
$ ps -fe|grep pmon
grid      3960     1  0 15:57 ?        00:00:00 asm_pmon_+ASM
oracle    4179     1  0 16:33 ?        00:00:00 ora_pmon_TESTDB
oracle    4284  3702  0 16:36 pts/1    00:00:00 grep pmon
$ echo $ORACLE_SID
TESTDB

$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg           
               ONLINE  ONLINE       dbsrvr1
               ONLINE  ONLINE       dbsrvr2   
ora.LISTENER.lsnr   
               ONLINE  ONLINE       dbsrvr1
            ONLINE  ONLINE       dbsrvr2
ora.asm       
               ONLINE  ONLINE       dbsrvr1                Started
            ONLINE  ONLINE       dbsrvr2                Started
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.proddb.db        
      1        ONLINE  ONLINE       dbsrvr1                open
     2        ONLINE  ONLINE       dbsrvr2                open
Please NOTE that there is no entry for TESTDB (  ora.testdb.db  )

SOLUTION
Register the database as follows
$ echo $ORACLE_HOME
/u01/app/oracle/11.2.0.3
$ srvctl add database -d TESTDB -o /u01/app/oracle/11.2.0.3
Try to bounce the database using the srvctl utility and check its status using crsctl now you should see an entry for TESTDB.
$ srvctl stop database -d TESTDB -o immediate
PRCC-1016 : TESTDB was already stopped
$ srvctl start database -d TESTDB
$ srvctl stop database -d TESTDB -o immediate
$ srvctl start database -d TESTDB
$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg           
                ONLINE  ONLINE       dbsrvr1
                ONLINE  ONLINE       dbsrvr2   
ora.LISTENER.lsnr   
                ONLINE  ONLINE       dbsrvr1
             ONLINE  ONLINE       dbsrvr2
ora.asm       
                ONLINE  ONLINE       dbsrvr1                Started
             ONLINE  ONLINE       dbsrvr2                Started
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.proddb.db        
      1        ONLINE  ONLINE       dbsrvr1                open
     2        ONLINE  ONLINE       dbsrvr2                open
ora.testdb.db        
      1        ONLINE  ONLINE       dbsrvr1                open
     2        ONLINE  ONLINE       dbsrvr2                open

Tuesday, November 18, 2014

STEP BY STEP GUIDE FOR CREATION OF SINGLE NODE STANDBY FROM 2 NODE RAC



  • CRETE A PASSWORD FILE FROM RAC NODE 1 AND COPY IT TO ORACLE_HOME/dbs DIRECTORY TO NODE2 AND DISASTER RECOVERY SITE
PROD1$ cd $ORACLE_HOME/dbs
PROD1$orapwd file=orapwCATDB1 entries=5 force=y
PROD1$scp orapwCATDB1 oracle@PROD2:$ORACLE_HOME/dbs
PROD1$scp orapwCATDB1 oracle@STDBY:$ORACLE_HOME/dbs

                          ON SECOND NODE
PROD2$cd $ORACLE_HOME/dbs
PROD2$mv orapwCATDB1 orapwCATDB2

                          ON STANDBY NODE
STDBY$cd $ORACLE_HOME/dbs
STDBY$mv orapwCATDB1 orapwCATDBSTDBY
  • MODIFY THE tnsnames.ora (found in $ORACLE_HOME/network/admin directories in each server) FILE  TO CONTAIN THE FOLLOWING ENTRIES

CATDB1 =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = PROD1)(PORT = 1521))
         (CONNECT_DATA =
            (SERVER = DEDICATED)
              (SERVICE_NAME = CATDB)
            (SID = CATDB1)
       )
  )

CATDB2 =    

     (DESCRIPTION =        

         (ADDRESS = (PROTOCOL = TCP)(HOST = PROD2)(PORT = 1521))

           (CONNECT_DATA = 
             (SERVER = DEDICATED)              
               (SERVICE_NAME = CATDB)           
            (SID = CATDB2)   
    )  
)
CATDBSTDBY =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = STDBY)(PORT = 1521))
         (CONNECT_DATA =
            (SERVER = DEDICATED)
              (SERVICE_NAME = CATDBSTDBY)
       )
  )
     

    • TAKE BACKUP OF THE PRODUCTION DATABASE.

    PROD1$export ORACLE_SID=CATDB1
    PROD1$rman target /

    Recovery Manager: Release 11.2.0.3.0 - Production on Tue Nov 18 11:12:24 2014

    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


    connected to target database: CATDB (not mounted)


    RMAN>
    run {
    allocate channel c1 type disk;
    allocate channel c2 type disk;
    allocate channel c3 type disk;
    allocate channel c4 type disk;
    allocate channel c5 type disk;
    allocate channel c6 type disk;
    allocate channel c7 type disk;
    allocate channel c8 type disk;
    allocate channel c9 type disk;
    allocate channel c10 type disk;
    backup as compressed backupset database format '/u03/backup/rman/CATDB/CATDB_Primary_databkp_standby_%U';
    backup as compressed backupset archivelog all format '/u03/backup/rman/CATDB/CATDB_Primary_archive_%U.arc';
    backup current controlfile for standby format '/u03/backup/rman/CATDB/CATDB_standbycontrol.ctl';
    }

    • MOVE THE BACKUP TO THE DISASTER RECOVERY SITE
    PROD1$cd /u03/backup/rman/CATDB
    PROD1$ls
    CATDB_Primary_databkp_standby_adpnto9u_1_1
    CATDB_Primary_databkp_standby_aepnto9v_1_1
    CATDB_Primary_databkp_standby_a8pnto9r_1_1
    CATDB_Primary_databkp_standby_afpnto9v_1_1
    CATDB_Primary_databkp_standby_a9pnto9s_1_1
    CATDB_Primary_databkp_standby_agpntoa0_1_1
    CATDB_Primary_databkp_standby_aapnto9s_1_1
    CATDB_Primary_databkp_standby_abpnto9t_1_1
    CATDB_Primary_databkp_standby_acpnto9t_1_1
    CATDB_Primary_archive_ahpntof2_1_1.arc
    CATDB_Primary_archive_ajpntof2_1_1.arc
    CATDB_Primary_archive_aipntof2_1_1.arc
    CATDB_Primary_archive_aopntof3_1_1.arc
    CATDB_Primary_archive_ampntof3_1_1.arc
    CATDB_Primary_archive_anpntof3_1_1.arc
    CATDB_Primary_archive_alpntof3_1_1.arc
    CATDB_Primary_archive_akpntof3_1_1.arc
    CATDB_Primary_archive_appntof3_1_1.arc
    CATDB_standbycontrol.ctl


    PROD1$scp * oracle@STDBY:$PWD
    • CHECK THAT ALL THE FILES ARRIVED ON THE INTENDED STANDBY SERVER
    STDBY$cd /u03/backup/rman/CATDB
    STDBY$ls
    CATDB_Primary_databkp_standby_adpnto9u_1_1
    CATDB_Primary_databkp_standby_aepnto9v_1_1
    CATDB_Primary_databkp_standby_a8pnto9r_1_1
    CATDB_Primary_databkp_standby_afpnto9v_1_1
    CATDB_Primary_databkp_standby_a9pnto9s_1_1
    CATDB_Primary_databkp_standby_agpntoa0_1_1
    CATDB_Primary_databkp_standby_aapnto9s_1_1
    CATDB_Primary_databkp_standby_abpnto9t_1_1
    CATDB_Primary_databkp_standby_acpnto9t_1_1
    CATDB_Primary_archive_ahpntof2_1_1.arc
    CATDB_Primary_archive_ajpntof2_1_1.arc
    CATDB_Primary_archive_aipntof2_1_1.arc
    CATDB_Primary_archive_aopntof3_1_1.arc
    CATDB_Primary_archive_ampntof3_1_1.arc
    CATDB_Primary_archive_anpntof3_1_1.arc
    CATDB_Primary_archive_alpntof3_1_1.arc
    CATDB_Primary_archive_akpntof3_1_1.arc
    CATDB_Primary_archive_appntof3_1_1.arc
    CATDB_standbycontrol.ctl

    • INITIATE THE RESTORE AND RECOVER PROCESS

    STDBY$rman target /

    Recovery Manager: Release 11.2.0.3.0 - Production on Tue Nov 18 11:12:51 2014

    Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


    connected to target database: CATDB (not mounted)


    RMAN> set dbid=2344484010

    executing command: SET DBID

    RMAN> restore standby controlfile from '/u03/backup/rman/CATDB/CATDB_standbycontrol.ctl';

    Starting restore at 11/18/14 11:14:19
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=3907 device type=DISK

    channel ORA_DISK_1: restoring control file

    channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
    output file name=+DATA/catdbstdby/controlfile/control01.ctl
    output file name=+ARCHIVE/catdbstdby/controlfile/control02.ctl
    Finished restore at 11/18/14 11:14:24

    RMAN> sql 'alter database mount';

    sql statement: alter database mount
    released channel: ORA_DISK_1

    RMAN> run {
    2> allocate channel c1 type disk;
    3> allocate channel c2 type disk;
    4> allocate channel c3 type disk;
    5> allocate channel c4 type disk;
    6> allocate channel c5 type disk;
    7> allocate channel c6 type disk;
    8> allocate channel c7 type disk;
    9> allocate channel c8 type disk;
    10> allocate channel c9 type disk;
    11> allocate channel c10 type disk;
    12> restore database;
    13> }

    allocated channel: c1
    channel c1: SID=5167 device type=DISK

    allocated channel: c2

    channel c2: SID=5419 device type=DISK

    allocated channel: c3

    channel c3: SID=5545 device type=DISK

    allocated channel: c4

    channel c4: SID=5671 device type=DISK

    allocated channel: c5

    channel c5: SID=5797 device type=DISK

    allocated channel: c6

    channel c6: SID=5923 device type=DISK

    allocated channel: c7

    channel c7: SID=6049 device type=DISK

    allocated channel: c8

    channel c8: SID=6175 device type=DISK

    allocated channel: c9

    channel c9: SID=6301 device type=DISK

    allocated channel: c10

    channel c10: SID=6427 device type=DISK

    Starting restore at 11/18/14 11:15:33

    Starting implicit crosscheck backup at 11/18/14 11:15:33
    Crosschecked 18 objects
    Crosschecked 18 objects
    Finished implicit crosscheck backup at 11/18/14 11:15:39

    Starting implicit crosscheck copy at 11/18/14 11:15:39

    Crosschecked 2 objects
    Finished implicit crosscheck copy at 11/18/14 11:15:42

    searching for all files in the recovery area

    cataloging files...
    no files cataloged

    channel c1: starting datafile backup set restore

    channel c1: specifying datafile(s) to restore from backup set
    channel c1: restoring datafile 00001 to +REDO/catdb/datafile/system.260.798798557
    channel c1: restoring datafile 00007 to +DATA/catdbstdby/datafile/t24distindex.483.800907503
    channel c1: reading from backup piece /u03/backup/rman/CATDB/CATDB_Primary_databkp_standby_aapnto9s_1_1
    channel c2: starting datafile backup set restore
    channel c2: specifying datafile(s) to restore from backup set
    channel c2: restoring datafile 00002 to +REDO/catdb/datafile/sysaux.261.798798559
    channel c2: restoring datafile 00012 to +DATA/catdbstdby/datafile/t24distindex.286.800907661
    channel c2: reading from backup piece /u03/backup/rman/CATDB/CATDB_Primary_databkp_standby_aepnto9v_1_1
    channel c3: starting datafile backup set restore
    channel c3: specifying datafile(s) to restore from backup set
    channel c3: restoring datafile 00004 to +REDO/catdb/datafile/users.263.798798559
    channel c3: restoring datafile 00009 to +DATA/catdbstdby/datafile/t24distdata.346.800907611
    channel c3: reading from backup piece /u03/backup/rman/CATDB/CATDB_Primary_databkp_standby_adpnto9u_1_1
    channel c4: starting datafile backup set restore
    channel c4: specifying datafile(s) to restore from backup set
    channel c4: restoring datafile 00003 to +REDO/catdb/datafile/undotbs1.262.798798559
    channel c4: restoring datafile 00008 to +DATA/catdbstdby/datafile/t24distdata.479.800907597
    channel c4: reading from backup piece /u03/backup/rman/CATDB/CATDB_Primary_databkp_standby_abpnto9t_1_1
    channel c5: starting datafile backup set restore
    channel c5: specifying datafile(s) to restore from backup set
    channel c5: restoring datafile 00005 to +ARCHIVE/catdb/datafile/users.5407.800376881
    channel c5: restoring datafile 00010 to +DATA/catdbstdby/datafile/t24distdata.288.800907625
    channel c5: reading from backup piece /u03/backup/rman/CATDB/CATDB_Primary_databkp_standby_acpnto9t_1_1
    channel c6: starting datafile backup set restore
    channel c6: specifying datafile(s) to restore from backup set
    channel c6: restoring datafile 00006 to +DATA/catdbstdby/datafile/t24distdata.295.800907475
    channel c6: restoring datafile 00014 to +DATA/catdbstdby/datafile/undotbs2.285.831499529
    channel c6: reading from backup piece /u03/backup/rman/CATDB/CATDB_Primary_databkp_standby_a8pnto9r_1_1
    channel c7: starting datafile backup set restore
    channel c7: specifying datafile(s) to restore from backup set
    channel c7: restoring datafile 00011 to +DATA/catdbstdby/datafile/t24distdata.287.800907639
    channel c7: restoring datafile 00013 to +DATA/catdbstdby/datafile/t24distindex.358.800907675
    channel c7: reading from backup piece /u03/backup/rman/CATDB/CATDB_Primary_databkp_standby_a9pnto9s_1_1
    channel c2: piece handle=/u03/backup/rman/CATDB/CATDB_Primary_databkp_standby_aepnto9v_1_1 tag=TAG20141118T110010
    channel c2: restored backup piece 1
    channel c2: restore complete, elapsed time: 00:00:57
    channel c1: piece handle=/u03/backup/rman/CATDB/CATDB_Primary_databkp_standby_aapnto9s_1_1 tag=TAG20141118T110010
    channel c1: restored backup piece 1
    channel c1: restore complete, elapsed time: 00:01:18
    channel c3: piece handle=/u03/backup/rman/CATDB/CATDB_Primary_databkp_standby_adpnto9u_1_1 tag=TAG20141118T110010
    channel c3: restored backup piece 1
    channel c3: restore complete, elapsed time: 00:02:18
    channel c4: piece handle=/u03/backup/rman/CATDB/CATDB_Primary_databkp_standby_abpnto9t_1_1 tag=TAG20141118T110010
    channel c4: restored backup piece 1
    channel c4: restore complete, elapsed time: 00:02:28
    channel c5: piece handle=/u03/backup/rman/CATDB/CATDB_Primary_databkp_standby_acpnto9t_1_1 tag=TAG20141118T110010
    channel c5: restored backup piece 1
    channel c5: restore complete, elapsed time: 00:03:47
    channel c7: piece handle=/u03/backup/rman/CATDB/CATDB_Primary_databkp_standby_a9pnto9s_1_1 tag=TAG20141118T110010
    channel c7: restored backup piece 1
    channel c7: restore complete, elapsed time: 00:04:06
    channel c6: piece handle=/u03/backup/rman/CATDB/CATDB_Primary_databkp_standby_a8pnto9r_1_1 tag=TAG20141118T110010
    channel c6: restored backup piece 1
    channel c6: restore complete, elapsed time: 00:04:27
    Finished restore at 11/18/14 11:20:13
    released channel: c1
    released channel: c2
    released channel: c3
    released channel: c4
    released channel: c5
    released channel: c6
    released channel: c7
    released channel: c8
    released channel: c9
    released channel: c10

    RMAN> list backup of archivelog all;

    If the last sequence number listed is n, choose n+1 for complete recovery. In my case the last sequence number listed by the above command is 8291
    therefore 8292 is chosen for recovery.

    RMAN> recover database until sequence 8292;

    Starting recover at 11/18/14 11:22:47

    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=5167 device type=DISK

    starting media recovery


    channel ORA_DISK_1: starting archived log restore to default destination

    channel ORA_DISK_1: restoring archived log
    archived log thread=1 sequence=8291
    channel ORA_DISK_1: reading from backup piece /u03/backup/rman/CATDB/CATDB_Primary_archive_aopntof3_1_1.arc
    channel ORA_DISK_1: piece handle=/u03/backup/rman/CATDB/CATDB_Primary_archive_aopntof3_1_1.arc tag=TAG20141118T110258
    channel ORA_DISK_1: restored backup piece 1
    channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
    archived log file name=+ARCHIVE/catdbstdby/archivelog/2014_11_18/thread_1_seq_8291.2837.863954589 thread=1 sequence=8291
    channel ORA_DISK_1: starting archived log restore to default destination
    channel ORA_DISK_1: restoring archived log
    archived log thread=2 sequence=2686
    channel ORA_DISK_1: reading from backup piece /u03/backup/rman/CATDB/CATDB_Primary_archive_appntof3_1_1.arc
    channel ORA_DISK_1: piece handle=/u03/backup/rman/CATDB/CATDB_Primary_archive_appntof3_1_1.arc tag=TAG20141118T110258
    channel ORA_DISK_1: restored backup piece 1
    channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
    archived log file name=+ARCHIVE/catdbstdby/archivelog/2014_11_18/thread_2_seq_2686.3707.863954589 thread=2 sequence=2686
    channel default: deleting archived log(s)
    archived log file name=+ARCHIVE/catdbstdby/archivelog/2014_11_18/thread_1_seq_8291.2837.863954589 RECID=1 STAMP=863954588
    unable to find archived log
    archived log thread=1 sequence=8292
    Oracle Error:
    ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
    ORA-01152: file 1 was not restored from a sufficiently old backup
    ORA-01110: data file 1: '+DATA/catdbstdby/datafile/system.1827.863954149'

    RMAN-00571: =======================================================

    RMAN-00569: ========= ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ======================================================
    RMAN-03002: failure of recover command at 11/18/2014 11:23:11
    RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 8292 and starting SCN of 22535896918

    RMAN> exit


    Recovery Manager complete.

    Since we have commanded oracle to recover from a sequence which is not included in the backup, the above error is expected.

    STDBY$sqlplus / as sysdba

    SQL*Plus: Release 11.2.0.3.0 Production on Tue Nov 18 11:24:01 2014

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



    Connected to:

    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    and Real Application Testing options

    SQL> select name,open_mode,database_role from v$database;

    NAME      OPEN_MODE            DATABASE_ROLE
    ---------      --------------------            ------------------------
    CATDB     MOUNTED               PHYSICAL STANDBY

    SQL> ALTER DATABASE RECOVER  managed standby database disconnect using current logfile nodelay noparallel ;

    Database altered.

    SQL>


    Check the alert log while you are making some logswitches from both primary instances, mrp process should apply the logfiles.

    To check the time gap between the primary and the standby you can use the following query on the standby


    SQL>select NAME,VALUE 
    from 
    v$dataguard_stats 
    where name = 'apply lag';
    NAME                                                             VALUE
    -------------------------                                       ----------------------------
    apply lag                                                      +00 00:06:34