Database Name: SRPRIM
Instances: srprim1 Node name: 10gnode1
Instances: srprim2 Node name: 10gnode2
Oracle Database Home and ASM Home: /u01/app/oracle/product/10.2.0/db1
Oracle CRS Home: /u01/app/oracle/product/10.2.0/crs
Below shows the status of CRS and the status of the resources.
[oracle@10gnode1 bin]$ cd $ORA_CRS_HOME [oracle@10gnode1 crs]$ cd bin/ [oracle@10gnode1 bin]$ ./crsctl check crs CSS appears healthy CRS appears healthy EVM appears healthy [oracle@10gnode1 bin]$ ./crs_stat -t Name Type Target State Host ---------------------------------------------------------- ora....SM1.asm application ONLINE ONLINE 10gnode1 ora....E1.lsnr application ONLINE ONLINE 10gnode1 ora....de1.gsd application ONLINE ONLINE 10gnode1 ora....de1.ons application ONLINE ONLINE 10gnode1 ora....de1.vip application ONLINE ONLINE 10gnode1 ora....SM2.asm application ONLINE ONLINE 10gnode2 ora....E2.lsnr application ONLINE ONLINE 10gnode2 ora....de2.gsd application ONLINE ONLINE 10gnode2 ora....de2.ons application ONLINE ONLINE 10gnode2 ora....de2.vip application ONLINE ONLINE 10gnode2 ora.srprim.db application ONLINE ONLINE 10gnode1 ora....m1.inst application ONLINE ONLINE 10gnode1 ora....m2.inst application ONLINE ONLINE 10gnode2
Lets check the CRS active and Software version and both are being shown up as 10.2.0.1.0
[oracle@10gnode1 bin]$ ./crsctl query crs activeversion CRS active version on the cluster is [10.2.0.1.0] [oracle@10gnode1 bin]$ ./crsctl query crs softwareversion CRS software version on node [10gnode1] is [10.2.0.1.0]
[oracle@10gnode1 bin]$ srvctl status asm -n 10gnode1 ASM instance +ASM1 is running on node 10gnode1. [oracle@10gnode1 bin]$ srvctl status asm -n 10gnode2 ASM instance +ASM2 is running on node 10gnode2.
Since we are applying the patch in a rolling fashion, shutdown the instance on one node 10gnode1 (srprim1).
[oracle@10gnode1 bin]$ srvctl stop instance -d srprim -i srprim1 [oracle@10gnode1 bin]$ srvctl stop asm -n 10gnode1 [oracle@10gnode1 bin]$ srvctl stop nodeapps -n 10gnode1
Cross verify if all the resources accessing the srprim1 instance are down.
[oracle@10gnode1 bin]$ ./crsctl check crs CSS appears healthy CRS appears healthy EVM appears healthy [oracle@10gnode1 bin]$ ./crs_stat -t Name Type Target State Host ------------------------------------------------------------ ora....SM1.asm application OFFLINE OFFLINE ora....E1.lsnr application OFFLINE OFFLINE ora....de1.gsd application OFFLINE OFFLINE ora....de1.ons application OFFLINE OFFLINE ora....de1.vip application OFFLINE OFFLINE ora....SM2.asm application ONLINE ONLINE 10gnode2 ora....E2.lsnr application ONLINE ONLINE 10gnode2 ora....de2.gsd application ONLINE ONLINE 10gnode2 ora....de2.ons application ONLINE ONLINE 10gnode2 ora....de2.vip application ONLINE ONLINE 10gnode2 ora.srprim.db application ONLINE ONLINE 10gnode1 ora....m1.inst application OFFLINE OFFLINE ora....m2.inst application ONLINE ONLINE 10gnode2
Unzip the patch 6810189 and run the runInstaller from the unzipped directory. Select the 10g CRS HOME path that needs to be upgraded. The patch will be applied on this home.
Once when the patching is done, Oracle prompts to run two scripts as root user:
Script 1: Shutdown the CRS daemons by issuing the following command:
/u01/app/oracle/product/10.2.0/crs/bin/crsctl stop crs
root@10gnode1 ~]# /u01/app/oracle/product/10.2.0/crs/bin/crsctlstop crs Stopping resources. Successfully stopped CRS resources Stopping CSSD. Shutting down CSS daemon. Shutdown request successfully issued.
Script 2: Run the shell script located at:
/u01/app/oracle/product/10.2.0/crs/install/root102.sh
This script will automatically start the CRS daemons on the patched node upon completion.
root@10gnode1 ~]# /u01/app/oracle/product/10.2.0/crs/install/root102.sh Creating pre-patch directory for saving pre-patch clusterware files Completed patching clusterware files to /u01/app/oracle/product/10.2.0/crs Relinking some shared libraries. Relinking of patched files is complete. WARNING: directory '/u01/app/oracle/product/10.2.0' is not owned by root WARNING: directory '/u01/app/oracle/product' is not owned by root WARNING: directory '/u01/app/oracle' is not owned by root WARNING: directory '/u01/app' is not owned by root WARNING: directory '/u01' is not owned by root Preparing to recopy patched init and RC scripts. Recopying init and RC scripts. Startup will be queued to init within 30 seconds. Starting up the CRS daemons. Waiting for the patched CRS daemons to start. This may take a while on some systems. . 10204 patch successfully applied. clscfg: EXISTING configuration version 3 detected. clscfg: version 3 is 10G Release 2. Successfully accumulated necessary OCR keys. Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897. node <nodenumber>: <nodename> <private interconnect name> <hostname> node 1: 10gnode1 10gnode1-priv 10gnode1 Creating OCR keys for user 'root', privgrp 'root'.. Operation successful. clscfg -upgrade completed successfully
[oracle@10gnode1 bin]$ ./crsctl query crs activeversion CRS active version on the cluster is [10.2.0.1.0] [oracle@10gnode1 bin]$ [oracle@10gnode1 bin]$ ./crsctl query crs softwareversion CRS software version on node [10gnode1] is [10.2.0.4.0]
You can see above that the CRS version is still being shown as 10.2.0.1.0. This would be changed to 10.2.0.4.0 only after the scripts mentioned above (Script 1 and Script 2) are run on the second node (10gnode2) as well.
Now, lets proceed to the next node (10gnode2)
Node 2:
shutdown the instance on node 10gnode2 (srprim2).
[oracle@10gnode2 ~]$ srvctl stop nodeapps -n 10gnode2 [oracle@10gnode2 ~]$ cd /u01/app/oracle/product/10.2.0/crs/bin [oracle@10gnode2 bin]$ ./crsctl query crs activeversion CRS active version on the cluster is [10.2.0.1.0] [oracle@10gnode2 bin]$ ./crsctl query crs softwareversion CRS software version on node [10gnode2] is [10.2.0.1.0] [oracle@10gnode2 bin]$ [oracle@10gnode2 bin]$ ./crs_stat -t Name Type Target State Host ------------------------------------------------------------ ora....SM1.asm application ONLINE ONLINE 10gnode1 ora....E1.lsnr application ONLINE ONLINE 10gnode1 ora....de1.gsd application ONLINE ONLINE 10gnode1 ora....de1.ons application ONLINE ONLINE 10gnode1 ora....de1.vip application ONLINE ONLINE 10gnode1 ora....SM2.asm application OFFLINE OFFLINE ora....E2.lsnr application OFFLINE OFFLINE ora....de2.gsd application OFFLINE OFFLINE ora....de2.ons application OFFLINE OFFLINE ora....de2.vip application OFFLINE OFFLINE ora.srprim.db application ONLINE ONLINE 10gnode2 ora....m1.inst application ONLINE ONLINE 10gnode1 ora....m2.inst application OFFLINE OFFLINE
Now, run the 2 scripts on node 10gnode2 that were prompted by Oracle on 10gnode1 to be run after the patch installation.
Script 1:
[root@10gnode2 ~]# /u01/app/oracle/product/10.2.0/crs/bin/crsctl stop crs Stopping resources. Successfully stopped CRS resources Stopping CSSD. Shutting down CSS daemon. Shutdown request successfully issued.
Script 2:
[root@10gnode2 ~]# /u01/app/oracle/product/10.2.0/crs/install/root102.sh Creating pre-patch directory for saving pre-patch clusterware files Completed patching clusterware files to /u01/app/oracle/product/10.2.0/crs Relinking some shared libraries. Relinking of patched files is complete. WARNING: directory '/u01/app/oracle/product/10.2.0' is not owned by root WARNING: directory '/u01/app/oracle/product' is not owned by root WARNING: directory '/u01/app/oracle' is not owned by root WARNING: directory '/u01/app' is not owned by root WARNING: directory '/u01' is not owned by root Preparing to recopy patched init and RC scripts. Recopying init and RC scripts. Startup will be queued to init within 30 seconds. Starting up the CRS daemons. Waiting for the patched CRS daemons to start. This may take a while on some systems. . 10204 patch successfully applied. clscfg: EXISTING configuration version 3 detected. clscfg: version 3 is 10G Release 2. Successfully accumulated necessary OCR keys. Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897. node <nodenumber>: <nodename> <private interconnect name> <hostname> node 2: 10gnode2 10gnode2-priv 10gnode2 Creating OCR keys for user 'root', privgrp 'root'.. Operation successful. clscfg -upgrade completed successfully
Verify whether the CRS version and the software version have been changed to 10.2.0.4.0 on both the nodes.
[oracle@10gnode2 bin]$ ./crsctl query crs activeversion CRS active version on the cluster is [10.2.0.4.0] [oracle@10gnode2 bin]$ ./crsctl query crs softwareversion CRS software version on node [10gnode2] is [10.2.0.4.0] [oracle@10gnode2 bin]$
Now lets cross verify if the CRS version has been updated to 10.2.0.4.0 on node 10gnode1 as well.
[oracle@10gnode1 bin]$ ./crsctl query crs activeversion CRS active version on the cluster is [10.2.0.4.0] [oracle@10gnode1 bin]$ ./crsctl query crs softwareversion CRS software version on node [10gnode1] is [10.2.0.4.0] [oracle@10gnode1 bin]$
Here we go !!
COPYRIGHT
© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.
DISCLAIMER
The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.
Oracle provides an unique feature where the physical standby database can be opened in READ WRITE mode to perform update able transactions. Quite often we have the standby database opened in READ Only mode for reporting purposes and optionally have the active dataguard implemented, but a snapshot standby database can be used to perform both READ and WRITE activities. Most importantly, a snapshot standby database keeps receiving the redo data from the primary database but does not apply them. These redo data received from the primary database would be applied only when the snapshot standby database is converted back to the Physical standby mode. There by the snapshot standby database provides data protection on primary database.
A snapshot standby database will allow you to make use of the data available on the physical standby database (which is a mirrored copy of the primary database). This allows the users to test the application on a standby database which has the primary data before implementing it in the Real production environment. When a physical standby database is converted to a snapshot standby database, a guaranteed restore point is automatically created. Once when the updateable transactions are completed for testing purposes on the snapshot standby database and when you are converting back the snapshot standby to physical standby, oracle flashbacks to the restore point that was created earlier which means that the transactions that were made when the standby database was opened in READ WRITE mode will be flushed out.
The only requirement to have the snapshot standby is that FRA (Flash Recovery Area) must be configured on physical standby database. It is not necessary to have flashback enabled. Below are the steps on how to convert a physical standby database to a snapshot standby database and viceversa.
Oracle Database version: 11.2.0.3 Enterprise Edition
Primary database: SRPRIM
Details with respect to the primary database:
SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance; STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE ------------ ---------------- ---------------- -------------------- OPEN srprim PRIMARY READ WRITE SQL> select thread#,max(sequence#) from v$archived_log group by thread#; THREAD# MAX(SEQUENCE#) ---------- -------------- 1 206
Standby database Details:
Oracle database version: 11.2.0.3 Enterprise Edition
Standby database name: SRPS
Details with respect to the physical standby database:
SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance; STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE ------------ ---------------- ---------------- ---------------- OPEN srps PHYSICAL STANDBY READ ONLY WITH APPLY SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#; THREAD# MAX(SEQUENCE#) ---------- -------------- 1 206 SQL> SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ NO
You can observe that the standby database is in sync with the primary database. Below outcome shows that the Flash Recovery Area is configured on the physical standby database.
SQL> show parameter db_recovery_file_dest NAME TYPE VALUE --------------------------- ----------- ------------- db_recovery_file_dest string +FRA_NEW db_recovery_file_dest_size big integer 4122M SQL>
Step 1: Cancel the Managed Recovery Process (MRP) on the physical standby database, shut it down and place it in Mount mode.
SQL> alter database recover managed standby database cancel; Database altered. SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE instance started. Total System Global Area 1269366784 bytes Fixed Size 2227984 bytes Variable Size 805306608 bytes Database Buffers 452984832 bytes Redo Buffers 8847360 bytes Database mounted.
Step 2: Once the standby database is mounted, convert the Physical standby database to snapshot standby database.
SQL> alter database convert to snapshot standby; Database altered.
Step 3: You can now open the snapshot standby database and check its mode.
SQL> alter database open; Database altered. SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance; STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE ----------- --------------- ---------------- ------------------ OPEN srps SNAPSHOT STANDBY READ WRITE
Small Test on the snapshot standby database.
1. Create a user called “SNAPTEST”
2. Create a table called “TEST” whose owner is “SNAPTEST” and insert some records in it. You can also update some of the records as well.
SQL> create user snaptest identified by oracle; User created. SQL> grant connect,resource to snaptest; Grant succeeded. SQL> conn snaptest/oracle@srps Connected. SQL> SQL> create table test(code number, name char(20)); Table created. SQL> insert into test values (100,'ARUN'); 1 row created. SQL> insert into test values(200,'SHIVU'); 1 row created. SQL> commit; Commit complete. SQL> select * from test; CODE NAME ---------- -------------------- 100 ARUN 200 SHIVU SQL> update snaptest.test set code=500 where name='ARUN'; 1 row updated. SQL> commit; Commit complete. SQL> select * from snaptest.test; CODE NAME ---------- -------------------- 500 ARUN 200 SHIVU
In the mean time, you can also see that the redo data from the primary database is received by the snapshot standby database but would not be applied.
On primary database the latest sequence generated is 208 and that on the standby database, the RFS process is idle for sequence 209.
Primary:
SQL> select thread#,max(sequence#) from v$archived_log group by thread#; THREAD# MAX(SEQUENCE#) ---------- -------------- 1 208
Standby:
SQL> select process,status,sequence# from v$managed_standby; PROCESS STATUS SEQUENCE# --------- ------------ ---------- ARCH CLOSING 1 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 RFS IDLE 0 RFS IDLE 209 RFS IDLE 0 7 rows selected.
Steps on converting back a snapshot standby database to physical standby database.
Step 1: Shut down the snapshot standby database and open it in Mount mode.
SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE instance started. Total System Global Area 1269366784 bytes Fixed Size 2227984 bytes Variable Size 805306608 bytes Database Buffers 452984832 bytes Redo Buffers 8847360 bytes Database mounted. SQL>
Step 2: Convert the snapshot standby database to physical standby database.
SQL> alter database convert to physical standby; Database altered.
Step 3: Once done, bounce the physical standby database and start the Managed Recovery Process (MRP) on it.
SQL> shut immediate ORA-01507: database not mounted ORACLE instance shut down. SQL> startup ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance ORACLE instance started. Total System Global Area 1269366784 bytes Fixed Size 2227984 bytes Variable Size 805306608 bytes Database Buffers 452984832 bytes Redo Buffers 8847360 bytes Database mounted. Database opened. SQL> select status,instance_name,database_role,open_mode from v$database,v$Instance; STATUS INSTANCE_NAME DATABASE_ROLE OPEN_MODE ------------ -------------- ---------------- ---------------- OPEN srps PHYSICAL STANDBY READ ONLY SQL> alter database recover managed standby database disconnectfrom session; Database altered. SQL> select process,status,sequence# from v$managed_standby; PROCESS STATUS SEQUENCE# --------- ------------ ---------- ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 RFS IDLE 0 RFS IDLE 0 RFS IDLE 0 MRP0 WAIT_FOR_LOG 213 8 rows selected.
Crosscheck whether the physical standby database is in sync with the primary database.
On Primary database:
SQL> select thread#,max(sequence#) from v$archived_log group bythread#; THREAD# MAX(SEQUENCE#) ---------- -------------- 1 212
On Standby database:
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#; THREAD# MAX(SEQUENCE#) ---------- -------------- 1 212
You can see below that the transactions that were carried out when the standby database is opened in READ WRITE mode are flushed out after it was converted back to physical standby database mode.
SQL> select * from snaptest.test; select * from snaptest.test * ERROR at line 1: ORA-00942: table or view does not exist SQL> select username,account_status from dba_users where username='SNAPTEST'; no rows selected
Here you go !!
COPYRIGHT
© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.
DISCLAIMER
The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.
When you plan of having a using the User Managed Backup method to backup the tablespace, you do the following:
1. Place the tablespace in “begin backup” mode.
2. Use OS commands to copy the datafiles of the tablespace to the disk.
3. Finally remove the tablespace from the backup mode (“end backup”).
But what exactly happens internally at the database level is what makes us curious to know about. The first thing that happens is that a checkpoint occurs and then the datafile header of the tablespace that would be placed in backup mode would be frozen. This means that when the tablespace is in backup mode and when a checkpoint occurs, the checkpoint SCN information would not be updated to the datafile header of
this tablespace.
Being said that the datafile header would be frozen, it does not mean that the DBWn process has no work on the datafile. Oracle behaves in a normal way with DBWn process writing the dirty blocks to the datafile from the database buffer. Oracle does not update the header but increments the Backup Checkpoint SCN of the datafile that is in backup mode.
Most DBAs assume that since the header is frozen, the dirty blocks would not be written to the datafile instead recorded in the redo log buffer and onwards written to the log file due to which there is a huge redo log information generated. Also, many assume that these archives would be used to write the transactions to the datafile of the tablespace once the tablespace is removed from the backup mode. This assumption is false. DBWn as usual keeps writing the modified blocks to the datafile irrespective of tablespace being in backup mode. The reason for Oracle to freeze the datafile header is that the checkpoint SCN at which the header was frozen marks the SCN from which the
recovery needs to be peformed when the datafiles that were backed up (using OS copy) will be restored.
It is true that there would be a good amount of redo information generated when the tablespace is in backup mode but it is not due to the above said assumption. When you place the tablespace in backup mode, Oracle copies the modified block as a whole image in the Redo logs and not just as a change vector. There by leading to increase in the archivelog generation. When the tablespace is normal mode (no backup mode), Oracle only writes the changed bytes (as change vector) to the redo log files.
You might be aware that the OS block is smaller in size when compared to the database block. The reason for copying the entire block when the tablespace is in backup mode is because when you are using the OS copy command to copy the datafile, the datafile is copied by reading the OS blocks and there can be cases where the DBWr process might be writing the modified database block to the datafile (OS block) at the same time. There by the datafile copy might have OS blocks before the DBWn made changes and OS blocks after the DBWn made changes. This is called as fractured blocks. At the time of recovery, the fractured blocks would be resolved by replacing the full image of the block from the archivelogs.
Once the tablespace is removed from the backup mode, the datafile header is updated with the latest Checkpoint SCN.
Here we go !!
COPYRIGHT
© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.
DISCLAIMER
The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.
The most common error I’ve come across users posting in OTN forums when you are restoring a controlfile from autobackup is “RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece”
This error comes up when you either do not have the controlfile autobackup to restore the controlfile or the controlfile autobackups are
placed in a different location other than the Flash Recovery Area. By default, RMAN looks for the controlfile autobackup at location
“$ORACLE_HOME/dbs” if Flash Recovery Area (FRA) is not configured. If FRA is configured, then RMAN looks for the controlfile autobackup in
the FRA location. Here is an example I would like to simulate where the controlfile autobackup is taken to a non-default location.
Database Name: SRPRIM
Here you can see below that I am taking the controlfile autobackup to location ‘/u01/autobackup/’.
[oracle@ora1-1 ~]$ rman target sys/oracle@srprim Recovery Manager: Release 11.2.0.3.0 - Production on Mon Jan 1411:39:09 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: SRPRIM (DBID=216679430) RMAN> show all; using target database control file instead of recovery catalog RMAN configuration parameters for database with db_unique_name SRPRIM are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO'/u01/autobackup/%F'; CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; #default CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT'OPTIMIZE FOR LOAD TRUE ; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0.3/db1/dbs/snapcf_srprim.f'; # default
Now, let me try to simulate the issue. For demonstrative purpose, I’ve shutdown the database, dropped the controlfile and started the
instance in nomount mode. Now, lets try to restore the controlfile from autobackup.
[oracle@ora1-1 ~]$ rman target sys/oracle@srprim Recovery Manager: Release 11.2.0.3.0 - Production on Mon Jan 14 11:58:30 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: SRPRIM (not mounted) RMAN> set dbid=216679430 executing command: SET DBID RMAN> restore controlfile from autobackup; Starting restore at 14-JAN-13 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=24 device type=DISK recovery area destination: +FRA database name (or database unique name) used for search: SRPRIM channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130114 channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130113 channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130112 channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130111 channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130110 channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130109 channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130108 channel ORA_DISK_1: no AUTOBACKUP in 7 days found RMAN-00571: =================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ======= RMAN-00571: =================================================== RMAN-03002: failure of restore command at 01/14/2013 11:59:10 RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece
You can see above that RMAN threw the error “RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece” as it tried to search the autobackup in FRA as I had configured FRA for this instance. This can be seen from below set of RMAN configuration where the “CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F'” is not pointing to the specific location, instead its pointing to the default location.
RMAN> show all; RMAN configuration parameters for database with db_unique_name SRPRIM are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO'%F'; # default CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; #default CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT'OPTIMIZE FOR LOAD TRUE ; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
To overcome this, we need to explicitly set the controlfile autobackup location to the location where the autobackups are placed. Below is what I’ve done and then restored the controlfile successfully.
RMAN> set controlfile autobackup format for device type disk to'/u01/autobackup/%F'; executing command: SET CONTROLFILE AUTOBACKUP FORMAT RMAN> restore controlfile from autobackup; Starting restore at 14-JAN-13 using channel ORA_DISK_1 recovery area destination: +FRA database name (or database unique name) used for search: SRPRIM channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area channel ORA_DISK_1: looking for AUTOBACKUP on day: 20130114 channel ORA_DISK_1: AUTOBACKUP found: /u01/autobackup/c-216679430-20130114-00 channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/autobackup/c-216679430-20130114-00 channel ORA_DISK_1: control file restore from AUTOBACKUP complete output file name=+DATA/srprim/controlfile/current.264.804686387 output file name=+FRA/srprim/controlfile/current.256.804686393 Finished restore at 14-JAN-13 RMAN> alter database mount; database mounted released channel: ORA_DISK_1
Here you go !!
COPYRIGHT
© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.
DISCLAIMER
The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.
Here is a 2012 annual report of my blog..
Here’s an excerpt:
4,329 films were submitted to the 2012 Cannes Film Festival. This blog had 28,000 views in 2012. If each view were a film, this blog would power 6 Film Festivals
Click here to see the complete report.
COPYRIGHT
© Shivananda Rao P, 2012, 2013, 2014. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.
DISCLAIMER
The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.
When you are performing a PITR (Point In Time Recovery), you might have undergone the error “RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time”. The reason that RMAN throws this error is when you are trying to perform a PITR of the database to a time before the database was last opened with RESETLOGS. In other words, the time you speicify in the “until time” clause of RMAN is the time that the database was in its previous incarnation and not the CURRENT incarnation.
Here is an example, that I would like to demonstrate with.
Database Name: srprim Database Version: 11.2.0.3
Step1 : I restore the SPFILE from the backup that was taken on 1st Dec location “/u01/backup/1_dec”
[oracle@ora1-1 1_dec]$ ls -lrth total 1.1G -rw-r----- 1 oracle oinstall 1.1G Dec 1 11:28 SRPRIM_inc0_20121201_05nrp04l_1_1.bak -rw-r----- 1 oracle oinstall 9.7M Dec 1 11:28 SRPRIM_inc0_20121201_06nrp06c_1_1.bak [oracle@ora1-1 1_dec]$ pwd /u01/backup/1_dec [oracle@ora1-1 dbs]$ rman target sys/oracle@srprim Recovery Manager: Release 11.2.0.3.0 - Production on Sat Dec 1 20:22:12 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database (not started) RMAN> startup force nomount; startup failed: ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0.3/db1/dbs/initsrprim.ora' starting Oracle instance without parameter file for retrieval of spfile Oracle instance started Total System Global Area 158662656 bytes Fixed Size 2226456 bytes Variable Size 92276456 bytes Database Buffers 58720256 bytes Redo Buffers 5439488 bytes RMAN> restore spfile from '/u01/backup/1_dec/SRPRIM_inc0_20121201_06nrp06c_1_1.bak'; Starting restore at 01-DEC-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=19 device type=DISK channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/backup/1_dec/SRPRIM_inc0_20121201_06nrp06c_1_1.bak channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete Finished restore at 01-DEC-12
Step 2: Let me start my database in nomount stage with the SPFILE that I restored in the previous step. Later I would restore the controlfile from the same above mentioned backup piece and mount the database.
RMAN> shutdown immediate Oracle instance shut down RMAN> startup nomount; connected to target database (not started) Oracle instance started Total System Global Area 1269366784 bytes Fixed Size 2227984 bytes Variable Size 771752176 bytes Database Buffers 486539264 bytes Redo Buffers 8847360 bytes RMAN> restore controlfile from '/u01/backup/1_dec/SRPRIM_inc0_20121201_06nrp06c_1_1.bak'; Starting restore at 01-DEC-12 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=25 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:15 output file name=+DATA/srprim/controlfile/current.264.800915029 output file name=+FRA/srprim/controlfile/current.256.800915035 Finished restore at 01-DEC-12 RMAN> alter database mount; database mounted released channel: ORA_DISK_1
Step 3: The next step is to restore and recover my database until time “1st Dec 2012 – 9:00 AM” as per my requirement. I try to perform it as below:
RMAN> run
2> {
3> set until time "to_date('01-12-2012:09:00:00','DD-MM-YYYY:HH24:MI:SS')";
4> restore database;
5> recover database;
6> }
When I ran the above commands, RMAN threw an error saying “RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time”.
executing command: SET until clause Starting restore at 01-DEC-12 Starting implicit crosscheck backup at 01-DEC-12 RMAN-00571: =================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ======= RMAN-00571: =================================================== RMAN-03002: failure of restore command at 12/01/2012 20:26:41 RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time
Ok, RMAN is fair enough and I’ve to accept what it is saying. Now, let me check the incarnations of the database and determine why RMAN threw the above error. With my database mounted, I run the “list incarnation” command at RMAN prompt.
[oracle@ora1-1 dbs]$ export NLS_DATE_FORMAT='DD-MM-YYYY HH24:MI:SS' [oracle@ora1-1 dbs]$ rman target sys/oracle@srprim Recovery Manager: Release 11.2.0.3.0 - Production on Sat Dec 1 20:27:37 2012 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: SRPRIM (DBID=216679430, not open) RMAN> list incarnation; using target database control file instead of recovery catalog List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 1 1 SRPRIM 216679430 PARENT 1 17-09-2011 09:46:04 2 2 SRPRIM 216679430 PARENT 995548 30-11-2012 19:13:17 3 3 SRPRIM 216679430 CURRENT 1040914 01-12-2012 11:19:53
From the above outcome, I can draw a conclusion that the current incarnation of the database has started from the time “1st Dec 2012 11:19 AM” and the time until which I tried to perform the PITR was “1st Dec 2012 9:00 AM”. It is obvious that RMAN has reported the error and could not perform the restoration.
So, to fulfill my requirement, I need to reset the database incarnation, in other words I need to set it to the incarnation where in the Point of time I’am looking out for recovery fits in. To do so, I need to have the backup of the database of the incarnation to which I would be setting. In my case, I had to reset the database incarnation# 2 as this incarnation began at “30th Nov 2012 19:13” which would fit in the time I was looking out (1st Dec 2012 9:00 AM) for recovery. To do this, I would follow
RMAN> shutdown immediate database dismounted Oracle instance shut down RMAN> startup nomount; connected to target database (not started) Oracle instance started Total System Global Area 1269366784 bytes Fixed Size 2227984 bytes Variable Size 771752176 bytes Database Buffers 486539264 bytes Redo Buffers 8847360 bytes [oracle@ora1-1 30_nov]$ pwd /u01/backup/30_nov [oracle@ora1-1 30_nov]$ ls -lrth total 1.2G -rw-r----- 1 oracle oinstall 27M Nov 30 19:26 SRPRIM_inc0_20121130_01nrkjeu_1_1.bak -rw-r----- 1 oracle oinstall 1.1G Nov 30 19:27 SRPRIM_inc0_20121130_02nrkjf1_1_1.bak -rw-r----- 1 oracle oinstall 9.4M Nov 30 19:27 SRPRIM_inc0_20121130_03nrkjgo_1_1.bak -rw-r----- 1 oracle oinstall 3.0K Nov 30 19:27 SRPRIM_inc0_20121130_04nrkjgu_1_1.bak -rw-r----- 1 oracle oinstall 27M Nov 30 19:27 SRPRIM_inc0_20121130_05nrkjh4_1_1.bak -rw-r----- 1 oracle oinstall 9.4M Nov 30 19:27 SRPRIM_inc0_20121130_06nrkjhb_1_1.bak -rw-r----- 1 oracle oinstall 96K Nov 30 19:27 SRPRIM_inc0_20121130_07nrkjhe_1_1.bak
Restore the controlfile from the backup that belongs to the incarnation that I would be changing to (incarnation #2)
RMAN> restore controlfile from '/u01/backup/30_nov/SRPRIM_inc0_20121130_06nrkjhb_1_1.bak'; Starting restore at 01-12-2012 20:32:21 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=24 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/srprim/controlfile/current.264.800915029 output file name=+FRA/srprim/controlfile/current.256.800915035 Finished restore at 01-12-2012 20:32:24 RMAN> alter database mount; database mounted released channel: ORA_DISK_1
Once the database is mounted, I would set the incarnation of the database to #2.
RMAN> reset database to incarnation 2; database reset to incarnation 2
Now, upon checking the the list of incarnations that my contorlfile is aware of, it would show as below
RMAN> list incarnation; List of Database Incarnations DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 1 1 SRPRIM 216679430 PARENT 1 17-09-2011 09:46:04 2 2 SRPRIM 216679430 CURRENT 995548 30-11-2012 19:13:17
We can see that the database CURRENT incarnation is set to incarnation# 2. Now, I need to catalog the backups that I had taken up with the backup of this controlfile. Note that I cannot use the backup that I had taken on 1st Dec. I’ll have to look out for the backups that fall under the incarnation that was newly set above.
RMAN> catalog start with '/u01/backup/30_nov/'; Starting implicit crosscheck backup at 01-12-2012 20:33:15 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=24 device type=DISK Crosschecked 5 objects Finished implicit crosscheck backup at 01-12-2012 20:33:16 Starting implicit crosscheck copy at 01-12-2012 20:33:16 using channel ORA_DISK_1 Finished implicit crosscheck copy at 01-12-2012 20:33:16 searching for all files in the recovery area cataloging files... no files cataloged searching for all files that match the pattern /u01/backup/30_nov/ List of Files Unknown to the Database ===================================== File Name: /u01/backup/30_nov/SRPRIM_inc0_20121130_02nrkjf1_1_1.bak File Name: /u01/backup/30_nov/SRPRIM_inc0_20121130_04nrkjgu_1_1.bak File Name: /u01/backup/30_nov/SRPRIM_inc0_20121130_07nrkjhe_1_1.bak File Name: /u01/backup/30_nov/SRPRIM_inc0_20121130_01nrkjeu_1_1.bak File Name: /u01/backup/30_nov/SRPRIM_inc0_20121130_05nrkjh4_1_1.bak File Name: /u01/backup/30_nov/SRPRIM_inc0_20121130_06nrkjhb_1_1.bak File Name: /u01/backup/30_nov/SRPRIM_inc0_20121130_03nrkjgo_1_1.bak Do you really want to catalog the above files (enter YES or NO)? YES cataloging files... cataloging done List of Cataloged Files ======================= File Name: /u01/backup/30_nov/SRPRIM_inc0_20121130_02nrkjf1_1_1.bak File Name: /u01/backup/30_nov/SRPRIM_inc0_20121130_04nrkjgu_1_1.bak File Name: /u01/backup/30_nov/SRPRIM_inc0_20121130_07nrkjhe_1_1.bak File Name: /u01/backup/30_nov/SRPRIM_inc0_20121130_01nrkjeu_1_1.bak File Name: /u01/backup/30_nov/SRPRIM_inc0_20121130_05nrkjh4_1_1.bak File Name: /u01/backup/30_nov/SRPRIM_inc0_20121130_06nrkjhb_1_1.bak File Name: /u01/backup/30_nov/SRPRIM_inc0_20121130_03nrkjgo_1_1.bak
Once I catalog the backups, let me try to perform the Point in time recovery until time “1st Dec 2012 9:00 AM”.
RMAN> run
2> {
3> set until time "to_date('01-12-2012:09:00:00','DD-MM-YYYY:HH24:MI:SS')";
4> restore database;
5> recover database;
6> }
executing command: SET until clause
Starting restore at 01-12-2012 20:34:37
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/srprim/datafile/system.265.800737881
channel ORA_DISK_1: restoring datafile 00002 to +DATA/srprim/datafile/sysaux.259.800737883
channel ORA_DISK_1: restoring datafile 00003 to +DATA/srprim/datafile/undotbs1.258.800737885
channel ORA_DISK_1: restoring datafile 00004 to +DATA/srprim/datafile/users.257.800737887
channel ORA_DISK_1: restoring datafile 00005 to +DATA/srprim/datafile/example.264.800738043
channel ORA_DISK_1: reading from backup piece /u01/backup/30_nov/SRPRIM_inc0_20121130_02nrkjf1_1_1.bak
channel ORA_DISK_1: piece handle=/u01/backup/30_nov/SRPRIM_inc0_20121130_02nrkjf1_1_1.bak tag=TAG20121130T192625
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:45
Finished restore at 01-12-2012 20:36:22
Starting recover at 01-12-2012 20:36:23
using channel ORA_DISK_1
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=4
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=5
channel ORA_DISK_1: reading from backup piece /u01/backup/30_nov/SRPRIM_inc0_20121130_05nrkjh4_1_1.bak
channel ORA_DISK_1: piece handle=/u01/backup/30_nov/SRPRIM_inc0_20121130_05nrkjh4_1_1.bak tag=TAG20121130T192731
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
archived log file name=+FRA/srprim/archivelog/2012_12_01/thread_1_seq_4.259.800915785 thread=1 sequence=4
channel default: deleting archived log(s)
archived log file name=+FRA/srprim/archivelog/2012_12_01/thread_1_seq_4.259.800915785 RECID=4 STAMP=800915788
archived log file name=+FRA/srprim/archivelog/2012_12_01/thread_1_seq_5.258.800915789 thread=1 sequence=5
channel default: deleting archived log(s)
archived log file name=+FRA/srprim/archivelog/2012_12_01/thread_1_seq_5.258.800915789 RECID=5 STAMP=800915791
unable to find archived log
archived log thread=1 sequence=6
RMAN-00571: ===================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =======
RMAN-00571: ===================================================
RMAN-03002: failure of recover command at 12/01/2012 20:36:39
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 6 and starting SCN of 1040949
Ok, so RMAN has recovered until the last sequence that was generated till time “1st Dec 2012 9:00 AM”. Now, let me open the database with RESETLOGS.
RMAN> alter database open resetlogs; database opened
Here you go !! We were successfully able to perform the Point in Time Recovery to the time that was beyond the CURRENT incarnation of the database.
COPYRIGHT
© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.
DISCLAIMER
The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.
I had a situation where the ASM instance was started using the pfile. A better option for me was to start it with SPFILE rather than with PFILE. So, when I tried to create an SPFILE from the PFILE, Oracle threw an error “ORA-29786: SIHA attribute GET failed with error [Attribute ‘SPFILE’ sts[200] lsts[0]]“.
[oracle@ora1-1 ~]$ export ORACLE_SID=+ASM [oracle@ora1-1 ~]$ export ORACLE_HOME=$ORA_CRS_HOME [oracle@ora1-1 ~]$ sqlplus / as sysasm SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 16 15:08:12 2012 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 Automatic Storage Management option SQL> select status,instance_name from v$instance; STATUS INSTANCE_NAME -------- --------------- STARTED +ASM SQL> select name,state from v$asm_diskgroup; NAME STATE ------ ----------- FRA MOUNTED DATA MOUNTED SQL> show parameter spfile NAME TYPE VALUE ------ ------- ----------- spfile string SQL> create spfile from pfile; create spfile from pfile * ERROR at line 1: ORA-29786: SIHA attribute GET failed with error [Attribute 'SPFILE' sts[200]lsts[0]] SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Automatic Storage Management option
The very next thing is to check if ASM (resource ora.asm) is registered with CRS.
[oracle@ora1-1 ~]$ srvctl config asm PRCR-1001 : Resource ora.asm does not exist
Here it is !! The resource ora.asm does not exist and which is why Oracle is unaware of the ASM instance that was started through the PFILE and not allowing to perform any operations on it.
Now, lets add ASM through SRVCTL utility and check its configuration.
[oracle@ora1-1 ~]$ srvctl add asm [oracle@ora1-1 ~]$ srvctl config asm ASM home: /u01/app/oracle/product/11.2.0.3/grid ASM listener was not found PRCA-1032 : ASM listener LISTENER does not exist Spfile: ASM diskgroup discovery string: /dev/oracleasm/disks/
This newly registered ASM would not be running and will have to be started through SRVCTL
[oracle@ora1-1 ~]$ srvctl status asm ASM is not running. [oracle@ora1-1 ~]$ srvctl start asm [oracle@ora1-1 ~]$ srvctl status asm ASM is running on ora1-1
You can still see from the above configuration, that the newly added ASM is not using SPFILE. So, now lets connect to the ASM instance at SQL level, create an SPFILE and start the ASM instance with the newly created SPFILE.
[oracle@ora1-1 ~]$ sqlplus / as sysasm SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 16 15:10:35 2012 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 Automatic Storage Management option SQL> show parameter spfile NAME TYPE VALUE ------ -------- -------------------- spfile string SQL> SQL> create spfile from pfile; File created. SQL> alter diskgroup DATA dismount; Diskgroup altered. SQL> alter diskgroup FRA dismount; Diskgroup altered. SQL> shut immediate ORA-15100: invalid or missing diskgroup name ORA-15100: invalid or missing diskgroup name ASM instance shutdown SQL> startup ASM instance started Total System Global Area 283930624 bytes Fixed Size 2227664 bytes Variable Size 256537136 bytes ASM Cache 25165824 bytes ASM diskgroups mounted ASM diskgroups volume enabled SQL> SQL> show parameter spfile NAME TYPE VALUE ------- ------- -------------------------------------------------------- spfile string /u01/app/oracle/product/11.2.0.3/grid/dbs/spfile+ASM.ora SQL> SQL> select name,state from v$asm_diskgroup; NAME STATE ----- --------- DATA MOUNTED FRA MOUNTED
Finally, lets check the configuration of this ASM instance through SRVCTL
[oracle@ora1-1 ~]$ srvctl config asm ASM home: /u01/app/oracle/product/11.2.0.3/grid ASM listener was not found PRCA-1032 : ASM listener LISTENER does not exist Spfile: /u01/app/oracle/product/11.2.0.3/grid/dbs/spfile+ASM.ora ASM diskgroup discovery string: /dev/oracleasm/disks/
Here you go !!
COPYRIGHT
© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.
DISCLAIMER
The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.
While applying a patch through “opatch”, the prerequisite check might fail with error “CheckActiveFilesAndExecutables” failed.
Here is an example of this issue and how it can be resolved. When I tried applying the patch to my database home, the prerequisite check failed and barked out saying that some of the executables were active. It also recommended that these files needs to be modified by OPatch but are currently being used by some processes.
[oracle@dev OPatch]$ ./opatch apply /opt/12827726 Oracle Interim Patch Installer version 11.2.0.3.0 Copyright (c) 2012, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/11.2.0.2/db1 Central Inventory : /u01/home/oraInventory from : /u01/app/oracle/product/11.2.0.2/db1/oraInst.loc OPatch version : 11.2.0.3.0 OUI version : 11.2.0.2.0 Log file location : /u01/app/oracle/product/11.2.0.2/db1/cfgtoollogs/opatch/12827726_Sep_18_2012_12_08_38/apply2012-09-18_12-08-38PM_1.log Applying interim patch '12827726' to OH '/u01/app/oracle/product/11.2.0.2/db1' Verifying environment and performing prerequisite checks... Prerequisite check "CheckActiveFilesAndExecutables" failed. The details are: Following executables are active : /u01/app/oracle/product/11.2.0.2/db1/bin/oracle Log file location: /u01/app/oracle/product/11.2.0.2/db1/cfgtoollogs/opatch/12827726_Sep_18_2012_12_08_38/apply2012-09-18_12-08-38PM_1.log Recommended actions: OPatch needs to modify files which are being used by some processes. OPatch failed with error code 41
Though the database and the listener were brought down before applying the patch, OPatch gave out this error. I tried to find out which process was using the executable “$ORACLE_HOME/bin/oracle” using the “/sbin/fuser” command.
[oracle@dev OPatch]$ /sbin/fuser /u01/app/oracle/product/11.2.0.2/db1/bin/oracle /u01/app/oracle/product/11.2.0.2/db1/bin/oracle 4685e 4708e 4712e 4723e 4727e
So there were 4 processes with PID 4685, 4708, 4712, 4723 and 4727 which were using the executable “$ORACLE_HOME/bin/oracle”. A much deeper investigation revealed that these were the shadow processes which I had to kill them before applying the patch.
[oracle@dev OPatch]$ [oracle@dev OPatch]$ ps -ef | grep 4685 oracle 4685 1 0 09:53 ? 00:00:00 oraclesrpt (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 11731 11362 0 12:15 pts/1 00:00:00 grep 4685 [oracle@dev OPatch]$ [oracle@dev OPatch]$ [oracle@dev OPatch]$ ps -ef | grep 4708 oracle 4708 1 0 09:53 ? 00:00:00 oraclesrpt (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 11749 11362 0 12:16 pts/1 00:00:00 grep 4708 [oracle@dev OPatch]$ [oracle@dev OPatch]$ ps -ef | grep 4712 oracle 4712 1 0 09:53 ? 00:00:00 oraclesrpt (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 11751 11362 0 12:16 pts/1 00:00:00 grep 4712 [oracle@dev OPatch]$ [oracle@dev OPatch]$ ps -ef | grep 4723 oracle 4723 1 0 09:53 ? 00:00:00 oraclesrpt (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 11768 11362 0 12:16 pts/1 00:00:00 grep 4723 [oracle@dev OPatch]$ [oracle@dev OPatch]$ ps -ef | grep 4727 oracle 4727 1 0 09:53 ? 00:00:00 oraclesrpt (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) oracle 11770 11362 0 12:16 pts/1 00:00:00 grep 4727 [oracle@dev OPatch]$ [oracle@dev OPatch]$ kill -9 4685 [oracle@dev OPatch]$ kill -9 4708 [oracle@dev OPatch]$ kill -9 4712 [oracle@dev OPatch]$ kill -9 4723 [oracle@dev OPatch]$ kill -9 4727
Now, lets try to apply the patch through OPatch and lets see if it succeeds.
[oracle@dev ~]$ export PATH=$PATH:/u01/app/oracle/product/11.2.0.2/db1/OPatch [oracle@dev ~]$ opatch apply /opt/12827726/ Oracle Interim Patch Installer version 11.2.0.3.0 Copyright (c) 2012, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/11.2.0.2/db1 Central Inventory : /u01/home/oraInventory from : /u01/app/oracle/product/11.2.0.2/db1/oraInst.loc OPatch version : 11.2.0.3.0 OUI version : 11.2.0.2.0 Log file location : /u01/app/oracle/product/11.2.0.2/db1/cfgtoollogs/opatch/12827726_Sep_18_2012_12_17_40/apply2012-09-18_12-17-39PM_1.log Applying interim patch '12827726' to OH '/u01/app/oracle/product/11.2.0.2/db1' Verifying environment and performing prerequisite checks... All checks passed. Provide your email address to be informed of security issues, install and initiate Oracle Configuration Manager. Easier for you if you use your My Oracle Support Email address/User Name. Visit http://www.oracle.com/support/policies.html for details. Email address/User Name: You have not provided an email address for notification of security issues. Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: Y Please shutdown Oracle instances running out of this ORACLE_HOME on the local system. (Oracle Home = '/u01/app/oracle/product/11.2.0.2/db1') Is the local system ready for patching? [y|n] y User Responded with: Y Backing up files... Patching component oracle.rdbms.rsf, 11.2.0.2.0... Patching component oracle.rdbms, 11.2.0.2.0... Patching component oracle.sysman.console.db, 11.2.0.2.0... Patching component oracle.sysman.oms.core, 10.2.0.4.3... Patching component oracle.ldap.rsf, 11.2.0.2.0... Patching component oracle.rdbms.dv, 11.2.0.2.0... Patching component oracle.rdbms.dbscripts, 11.2.0.2.0... Patching component oracle.sysman.plugin.db.main.repository, 11.2.0.2.0... Patching component oracle.rdbms.rman, 11.2.0.2.0... Patching component oracle.sdo.locator, 11.2.0.2.0... Verifying the update... Patch 12827726 successfully applied Log file location: /u01/app/oracle/product/11.2.0.2/db1/cfgtoollogs/opatch/12827726_Sep_18_2012_12_17_40/apply2012-09-18_12-17-39PM_1.log OPatch succeeded.
And yes, opatch succeeded in applying the patch successfully to the database home.
Here we go !!
COPYRIGHT
© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.
DISCLAIMER
The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.
Primary Database: srprim
Standby database: srps
Primary Database Server: dev
Standby Database Server: uat
Database version: 11.2.0.2
I have my physical standby database in sync with the primary database with the logs being applied in the form of real time apply. As you might be aware, for the “Real Time Apply” implementation, we need to make sure that we have created the Standby Redo Logs on the standby database with the size same as that of the Online Redo logs. The Standby Redo Logs also needs to be created on the primary database, but this is not mandatory. The Standby Redo Logs would not be used on the Primary database until there is a switchover operation performed and the primary database starts behaving as a standby database. Here is a snippet of the size of the redo logs (Online and Standby Redo logs) on the primary and standby database.
Primary Database:
[oracle@dev ~]$ sqlplus sys/oracle@srprim as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Sat Sep 29 16:32:37 2012 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, DataMining and Real Application Testing options SQL> select status,instance_name,database_role from v$database,v$instance; STATUS INSTANCE_NAME DATABASE_ROLE ------ ------------- -------------- OPEN srprim PRIMARY SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$log group by group#; GROUP# Size in MB ------- ---------- 1 50 2 50 3 50 SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#; GROUP# Size in MB ------- ---------- 6 50 4 50 5 50 7 50
Standby Database:
[oracle@uat ~]$ sqlplus sys/oracle@srps as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Sat Sep 29 16:47:11 2012 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, DataMining and Real Application Testing options SQL> select status,instance_name,database_role from v$database,v$Instance; STATUS INSTANCE_NAME DATABASE_ROLE ------- -------------- ---------------- MOUNTED srps PHYSICAL STANDBY SQL> select group#, sum(bytes/1024/1024)"Size in MB" from v$loggroup by group#; GROUP# Size in MB ------ ------------- 1 50 2 50 3 50 SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#; GROUP# Size in MB ------ ---------- 4 50 5 50 6 50 7 50
Check if the parameter standby_file_management is set to AUTO or MANUAL on standby database. If it is not set to MANUAL, then set it.
SQL> show parameter standby_file_management NAME TYPE VALUE ----------------------- ------- ------- standby_file_management string AUTO SQL> SQL> SQL> alter system set standby_file_management=manual; System altered. SQL> show parameter standby_file_management NAME TYPE VALUE ----------------------- ------- ------- standby_file_management string MANUAL
On the primary database, check the status of the Online Redo Logs and resize them by dropping the INACTIVE redo logs and re-creating them with the new size.
Primary:
SQL> select group#,status from v$log; GROUP# STATUS ------ ------------------- 1 INACTIVE 2 INACTIVE 3 CURRENT
Here you can see that Online Redo Log groups 1 and 2 are INACTIVE. Hence we can drop them and re-create with the new size.
SQL> alter database drop logfile group 1; Database altered. SQL> alter database add logfile group 1 size 100M; Database altered. SQL> select group#,status from v$log; GROUP# STATUS ------ ------- 1 UNUSED 2 INACTIVE 3 CURRENT SQL> alter database drop logfile group 2; Database altered. SQL> alter database add logfile group 2 size 100M; Database altered. SQL> SQL> select group#,status from v$log; GROUP# STATUS ------ ------- 1 UNUSED 2 UNUSED 3 CURRENT
Now that Online Redo Log Groups 1 and 2 are resized and the status of Group 3 is CURRENT, switch logfiles manually until Group 3 becomes INACTIVE
After a couple of log switches, we can check the Status of Group 3
SQL> select group#,status from v$log; GROUP# STATUS ------ ------- 1 ACTIVE 2 CURRENT 3 INACTIVE
Now that Group 3 is INACTIVE, we can drop it and re-create it with the new size.
SQL> alter database drop logfile group 3; Database altered. SQL> alter database add logfile group 3 size 100M; Database altered.
Now, we have resized all the Online Redo Logs on the Primary Database from 50M to 100M.
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$log group by group#; GROUP# size in MB ------ ------------- 1 100 2 100 3 100
Moving on to the Standby Redo Logs on the Primary Database:
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#; GROUP# size in MB ------ ------------- 6 50 4 50 5 50 7 50 SQL> select group#,status from v$standby_log; GROUP# STATUS ------ ------------ 4 UNASSIGNED 5 UNASSIGNED 6 UNASSIGNED 7 UNASSIGNED
The status of the Standby Redo Logs (SRL) on the Primary database would be UNASSIGNED as they would be used only when the primary database starts behaving as a Standby (Switchover)
We can easily drop the UNASSIGNED SRLs and re-create them with the new size.
SQL> alter database drop standby logfile group 4; Database altered. SQL> alter database add standby logfile group 4 size 100M; Database altered.
Repeat this for the remaining SRLs on the primary database and we can see below that all the SRL on the Primary database have been resized.
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#; GROUP# size in MB ------ ------------- 6 100 4 100 5 100 7 100
Moving on to the standby database:
Standby:
SQL> select group#,status from v$log; GROUP# STATUS ------ ------------------- 1 CURRENT 3 CLEARING 2 CLEARING
Lets try to drop Online Redo Log Group 2 as Group 1 is CURRENT status and it cannot be dropped.
SQL> alter database drop logfile group 2; alter database drop logfile group 2 * ERROR at line 1: ORA-01156: recovery or flashback in progress may need access to files
Here above, we faced ORA-01156 error, which is self-explainatory. The recovery (MRP process) on the standby database is active and it needs to be cancelled before we drop any Redo log groups.
SQL> alter database recover managed standby database cancel; Database altered.
Since the status of Group 2 is still clearing, lets clear it manually before dropping the group.
SQL> alter database clear logfile group 2; Database altered. SQL> alter database drop logfile group 2; Database altered. SQL> alter database add logfile group 2 size 100M; Database altered. SQL> select group#,status from v$log; GROUP# STATUS ------ ------------------- 1 CURRENT 3 CLEARING 2 UNUSED
The same is the case with Group 3 whose status is CLEARING. We’ll clear it manually, drop it and create with the new size.
SQL> alter database clear logfile group 3; Database altered. SQL> alter database drop logfile group 3; Database altered. SQL> alter database add logfile group 3 size 100M; Database altered.
To drop online redo log group 1 that is in status CURRENT, generate an archive on the primary database by switching logfile, and then clear the CURRENT online redo log group on the standby database, drop it and re-create it with the required size as done for groups 2 and 3 earlier.
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$log group by group#; GROUP# size in MB ------ ------------- 1 100 2 100 3 100
Resizing Standby Redo Logs on standby database:
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#; GROUP# size in MB ------ -------------- 4 50 5 50 6 50 7 50 SQL> select group#,status from v$standby_log; GROUP# STATUS ------ ------------- 4 ACTIVE 5 UNASSIGNED 6 UNASSIGNED 7 UNASSIGNED
Clear the SRL group whose status is ACTIVE as done earlier, drop the group and re-create them with the size same as that of the Online Redo Logs.
For the SRL groups whose status is UNASSIGNED, just drop the group and recreate them with the size same as that of the Online Redo Logs.
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$log group by group#; GROUP# size in MB ------ ------------ 1 100 2 100 3 100 SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#; GROUP# size in MB ------ ------------- 4 100 5 100 6 100 7 100
Once all the Standby Redo Logs and Online Redo Logs have been resize on both Primary and standby database, set the STANDBY_FILE_MANAGEMENT to AUTO on the standby database and start the recovery (MRP) on standby database.
SQL> alter system set standby_file_management=auto; System altered. SQL> alter database recover managed standby database disconnectfrom session using current logfile; Database altered. SQL> select process,status,sequence# from v$managed_standby; PROCESS STATUS SEQUENCE# ------- -------------- ------------- ARCH CONNECTED 0 ARCH CLOSING 66 ARCH CONNECTED 0 ARCH CLOSING 63 RFS IDLE 0 RFS IDLE 0 MRP0 WAIT_FOR_LOG 71 RFS IDLE 71 RFS IDLE 0 9 rows selected.
Primary:
SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 70
Standby:
SQL> select max(sequence#) from v$archived_log where applied='YES'; MAX(SEQUENCE#) -------------- 70
Here we go !!
COPYRIGHT
© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.
DISCLAIMER
The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.
Here is a brief explanation on how to apply PSU (Patch Set Update) in a dataguard environment
In this demo, I am applying PSU 11.2.0.2.4 on the Primary and standby databases.
Primary database Server : dev
Standby database Server : uat
Primary database : srprim
Standby database : srps
Primary Server:
[oracle@dev ~]$ sqlplus sys/oracle@srprim as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Tue Sep 18 10:43:50 2012 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, DataMining and Real Application Testing options SQL> select status,instance_name,database_role from v$database,v$instance; STATUS INSTANCE_NAME DATABASE_ROLE ------ ------------- --------------- OPEN srprim PRIMARY SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 10
Standby Server:
[oracle@uat ~]$ sqlplus sys/oracle@srps as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Tue Sep 18 10:46:35 2012 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, DataMining and Real Application Testing options SQL> select status,instance_name,database_role from v$database,v$instance; STATUS INSTANCE_NAME DATABASE_ROLE -------- -------------- ---------------------- MOUNTED srps PHYSICAL STANDBY SQL> select max(sequence#) from v$archived_log where applied='YES'; MAX(SEQUENCE#) -------------- 10
Step 1:
Disable the log shipping from primary database to the standby database by setting the log_archive_dest_state_2 to “defer” on the primary database. Here log_archive_dest_state_2 is deferred because parameter log_archive_dest_2 is set on my primary database to point to the Standby Database.
SQL> alter system set log_archive_dest_state_2=defer; System altered.
Step 2:
On the standby database cancel the Managed Recovery Process.
SQL> alter database recover managed standby database cancel; Database altered.
Step 3:
PSU (Patch Set Update)/CPU (Critical Patch Update)/ Patch Set patches always needs to be applied first on the standby database and then on the primary database. In order to apply it on the standby database, shutdown the standby database and also the listener running on the standby server.
SQL> shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. [oracle@uat ~]$ lsnrctl stop [oracle@uat ~]$ ps -ef | grep tns oracle 6958 5107 0 10:52 pts/1 00:00:00 grep tns [oracle@uat ~]$ [oracle@uat ~]$ ps -ef | grep pmon oracle 4788 1 0 09:56 ? 00:00:00 asm_pmon_+ASM oracle 6960 5107 0 10:52 pts/1 00:00:00 grep pmon
Step 4:
Now apply the PSU on the standby database.
[oracle@uat ~]$ export PATH=$PATH:/u01/app/oracle/product/11.2.0.2/db_1/OPatch [oracle@uat ~]$ opatch version OPatch Version: 11.2.0.3.0 OPatch succeeded. [oracle@uat ~]$ <span style="color: #ff6600;">opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /opt/12827726</span> Oracle Interim Patch Installer version 11.2.0.3.0 Copyright (c) 2012, Oracle Corporation. All rights reserved. PREREQ session Oracle Home : /u01/app/oracle/product/11.2.0.2/db_1 Central Inventory : /u01/home/oraInventory from : /u01/app/oracle/product/11.2.0.2/db_1/oraInst.loc OPatch version : 11.2.0.3.0 OUI version : 11.2.0.2.0 Log file location : /u01/app/oracle/product/11.2.0.2/db_1/cfgtoollogs/opatch/opatch2012-09-18_11-11-40AM_1.log Invoking prereq "checkconflictagainstohwithdetail" Prereq "checkConflictAgainstOHWithDetail" passed. OPatch succeeded. [oracle@uat ~]$ opatch apply /opt/12827726/ Oracle Interim Patch Installer version 11.2.0.3.0 Copyright (c) 2012, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/11.2.0.2/db_1 Central Inventory : /u01/home/oraInventory from : /u01/app/oracle/product/11.2.0.2/db_1/oraInst.loc OPatch version : 11.2.0.3.0 OUI version : 11.2.0.2.0 Log file location : /u01/app/oracle/product/11.2.0.2/db_1/cfgtoollogs/opatch/12827726_Sep_18_2012_11_12_36/apply2012-09-18_11-12-35AM_1.log Applying interim patch '12827726' to OH '/u01/app/oracle/product/11.2.0.2/db_1' Verifying environment and performing prerequisite checks... All checks passed. Provide your email address to be informed of security issues, install and initiate Oracle Configuration Manager. Easier for you if you use your My Oracle Support Email address/User Name. Visit http://www.oracle.com/support/policies.html for details. Email address/User Name: You have not provided an email address for notification of security issues. Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: Y Please shutdown Oracle instances running out of this ORACLE_HOME on the local system. (Oracle Home = '/u01/app/oracle/product/11.2.0.2/db_1') Is the local system ready for patching? [y|n] y User Responded with: Y Backing up files... Patching component oracle.rdbms.rsf, 11.2.0.2.0... Patching component oracle.rdbms, 11.2.0.2.0... Patching component oracle.sysman.console.db, 11.2.0.2.0... Patching component oracle.sysman.oms.core, 10.2.0.4.3... Patching component oracle.ldap.rsf, 11.2.0.2.0... Patching component oracle.rdbms.dv, 11.2.0.2.0... Patching component oracle.rdbms.dbscripts, 11.2.0.2.0... Patching component oracle.sysman.plugin.db.main.repository, 11.2.0.2.0... Patching component oracle.rdbms.rman, 11.2.0.2.0... Patching component oracle.sdo.locator, 11.2.0.2.0... Verifying the update... Patch 12827726 successfully applied Log file location: /u01/app/oracle/product/11.2.0.2/db_1/cfgtoollogs/opatch/12827726_Sep_18_2012_11_12_36/apply2012-09-18_11-12-35AM_1.log OPatch succeeded.
Step 5:
Once the patch has been applied on the standby database, start the listener and the standby database.
[oracle@uat ~]$ lsnrctl start [oracle@uat ~]$ sqlplus sys/oracle@srps as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Tue Sep 18 11:40:02 2012 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to an idle instance. SQL> startup mount ORACLE instance started. Total System Global Area 684785664 bytes Fixed Size 2229640 bytes Variable Size 197134968 bytes Database Buffers 482344960 bytes Redo Buffers 3076096 bytes Database mounted.
Note: Do not run any patching scripts on the standby database (Example: catbundle.sql). We are done with the patching on the standby database. Now lets move to the primary database.
Step 6:
Shutdown the Primary database and stop the listener running on the primary database server.
<pre> [oracle@dev ~]$ sqlplus sys/oracle@srprim as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Tue Sep 18 11:48:26 2012 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, DataMining and Real Application Testing options SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, DataMining and Real Application Testing options [oracle@dev ~]$ lsnrctl stop [oracle@dev ~]$ ps -ef | grep pmon oracle 4618 1 0 09:53 ? 00:00:00 asm_pmon_+ASM oracle 10233 4998 0 11:50 pts/1 00:00:00 grep pmon [oracle@dev ~]$ [oracle@dev ~]$ ps -ef | grep tns oracle 10237 4998 0 11:50 pts/1 00:00:00 grep tns
Step 7:
Now apply the PSU patch on the Primary database.
[oracle@dev ~]$ export PATH=$PATH:/u01/app/oracle/product/11.2.0.2/db1/OPatch [oracle@dev ~]$ opatch version OPatch Version: 11.2.0.3.0 OPatch succeeded. [oracle@dev ~]$ opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /opt/12827726/ Oracle Interim Patch Installer version 11.2.0.3.0 Copyright (c) 2012, Oracle Corporation. All rights reserved. PREREQ session Oracle Home : /u01/app/oracle/product/11.2.0.2/db1 Central Inventory : /u01/home/oraInventory from : /u01/app/oracle/product/11.2.0.2/db1/oraInst.loc OPatch version : 11.2.0.3.0 OUI version : 11.2.0.2.0 Log file location : /u01/app/oracle/product/11.2.0.2/db1/cfgtoollogs/opatch/opatch2012-09-18_11-56-11AM_1.log Invoking prereq "checkconflictagainstohwithdetail" Prereq "checkConflictAgainstOHWithDetail" passed. OPatch succeeded. [oracle@dev ~]$ export PATH=$PATH:/u01/app/oracle/product/11.2.0.2/db1/OPatch [oracle@dev ~]$ opatch apply /opt/12827726/ Oracle Interim Patch Installer version 11.2.0.3.0 Copyright (c) 2012, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/11.2.0.2/db1 Central Inventory : /u01/home/oraInventory from : /u01/app/oracle/product/11.2.0.2/db1/oraInst.loc OPatch version : 11.2.0.3.0 OUI version : 11.2.0.2.0 Log file location : /u01/app/oracle/product/11.2.0.2/db1/cfgtoollogs/opatch/12827726_Sep_18_2012_12_17_40/apply2012-09-18_12-17-39PM_1.log Applying interim patch '12827726' to OH '/u01/app/oracle/product/11.2.0.2/db1' Verifying environment and performing prerequisite checks... All checks passed. Provide your email address to be informed of security issues, install and initiate Oracle Configuration Manager. Easier for you if you use your My Oracle Support Email address/User Name. Visit http://www.oracle.com/support/policies.html for details. Email address/User Name: You have not provided an email address for notification of security issues. Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: Y Please shutdown Oracle instances running out of this ORACLE_HOME on the local system. (Oracle Home = '/u01/app/oracle/product/11.2.0.2/db1') Is the local system ready for patching? [y|n] y User Responded with: Y Backing up files... Patching component oracle.rdbms.rsf, 11.2.0.2.0... Patching component oracle.rdbms, 11.2.0.2.0... Patching component oracle.sysman.console.db, 11.2.0.2.0... Patching component oracle.sysman.oms.core, 10.2.0.4.3... Patching component oracle.ldap.rsf, 11.2.0.2.0... Patching component oracle.rdbms.dv, 11.2.0.2.0... Patching component oracle.rdbms.dbscripts, 11.2.0.2.0... Patching component oracle.sysman.plugin.db.main.repository, 11.2.0.2.0... Patching component oracle.rdbms.rman, 11.2.0.2.0... Patching component oracle.sdo.locator, 11.2.0.2.0... Verifying the update... Patch 12827726 successfully applied Log file location: /u01/app/oracle/product/11.2.0.2/db1/cfgtoollogs/opatch/12827726_Sep_18_2012_12_17_40/apply2012-09-18_12-17-39PM_1.log OPatch succeeded.
Step 8:
Start the listener on the primary database server and also start the Primary database.
[oracle@dev ~]$ lsnrctl start [oracle@dev ~]$ sqlplus sys/oracle@srprim as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Tue Sep 18 12:28:35 2012 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 684785664 bytes Fixed Size 2229640 bytes Variable Size 222300792 bytes Database Buffers 457179136 bytes Redo Buffers 3076096 bytes Database mounted. Database opened. SQL>
Step 9:
Now enable log shipping on the primary database by setting the log_archive_dest_state_2 to “enable”. As I said earlier, parameter log_archive_dest_2 on my primary database is set to point to the standby database.
SQL> alter system set log_archive_dest_state_2=enable; System altered.
Step 10:
Start the Managed Recovery Process (MRP) on the standby database.
[oracle@uat ~]$ sqlplus sys/oracle@srps as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Tue Sep 18 12:33:03 2012 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, DataMining and Real Application Testing options SQL> alter database recover managed standby database disconnect; Database altered. SQL> select process,status,sequence# from v$managed_standby; PROCESS STATUS SEQUENCE# ------- ------------- ----------- ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 RFS IDLE 0 RFS IDLE 13 RFS IDLE 0 RFS IDLE 0 MRP0 WAIT_FOR_LOG 13 9 rows selected.
Step 11:
On the primary database, run the patching scripts like “catbundle.sql” in this case.
The script run generates archives and these archives would be shipped and applied to the standby database. So, there is no requriement to run the patching scripts on the standby database.
[oracle@dev ~]$ sqlplus sys/oracle@srprim as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Tue Sep 18 12:31:52 2012 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, DataMining and Real Application Testing options SQL> @?/rdbms/admin/catbundle.sql psu apply SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 14
Step 12:
Check if the PSU applied shows up in the primary database by querying the registry$history or dba_registry_history view.
SQL> select * from registry$history order by action_time desc; ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS BUNDLE_SER ------------------------------ ---------- ---------- --------------- ---------- ------------------------- ---------- 18-SEP-12 12.32.44.728562 PM APPLY SERVER 11.2.0.2 4 PSU 11.2.0.2.4 PSU
Step 13:
Make sure that the latest archive applied on the standby database is the latest archive generated on the primary database. You can see below that the latest archive sequence applied on the standby database is sequence 14 and the latest sequence generated on the primary database too is 14. Now, check if the PSU applied shows up in the standby database by querying the registry$history or dba_registry_history view.
[oracle@uat ~]$ sqlplus sys/oracle@srps as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Tue Sep 18 12:44:25 2012 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, DataMining and Real Application Testing options SQL> select max(sequence#) from v$archived_log where applied='YES'; MAX(SEQUENCE#) -------------- 14 SQL> select * from registry$history order by action_time desc; ACTION_TIME ACTION NAMESPACE VERSION ID COMMENTS BUNDLE_SER ----------------------------------- ---------- ---------- ---------- ---------- ------------------------- ---------- 18-SEP-12 12.32.44.728562 PM APPLY SERVER 11.2.0.2 4 PSU 11.2.0.2.4 PSU
We can see that the PSU is applied successfully on both Primary and standby databases.
Here we go !!
COPYRIGHT
© Shivananda Rao P, 2012 to 2018. Unauthorized use and/or duplication of this material without express and written permission from this blog’s author and/or owner is strictly prohibited. Excerpts and links may be used, provided that full and clear credit is given to Shivananda Rao and http://www.shivanandarao-oracle.com with appropriate and specific direction to the original content.
DISCLAIMER
The views expressed here are my own and do not necessarily reflect the views of any other individual, business entity, or organization. The views expressed by visitors on this blog are theirs solely and may not reflect mine.
Shivananda Rao 