Failover is a one way process where your primary database goes down due to some reasons and to get back the production live without any loss, you convert your existing Physical Standby database to start behaving as Primary database.
I have my primary database as PRPRIM and standby database as PSSTBY
Primary Database: PRPRIM
Standby Database: PSSTBY
Primary Database Server:
SQL> select status,instance_name,database_role from v$database,v$instance; STATUS INSTANCE_NAME DATABASE_ROLE ------ ------------- ----------------------- OPEN prprim PRIMARY SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- TO STANDBY
Standby Database Server:
SQL> select status,instance_name,database_role from v$database,v$instance; STATUS INSTANCE_NAME DATABASE_ROLE ------- ------------- ----------------------- MOUNTED psstby PHYSICAL STANDBY
Now to simulate the failover, I bring down the primary database PRPRIM
Primary:
SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. SQL>
On the standby database (PSSTBY) perform the below steps:
STEP 1:
Cancel the Managed Recovery Process
SQL> alter database recover managed standby database cancel; Database altered.
Step 2:
Inform the standby database that the recovery is finished forever.
SQL> alter database recover managed standby database finish; Database altered.
STEP 3:
Switchover the standby database to Primary role.
SQL> alter database commit to switchover to primary with session shutdown; Database altered. SQL> select status,instance_name from v$instance; STATUS INSTANCE_NAME -------- -------------------- MOUNTED psstby SQL> alter database open; Database altered. SQL> select status,instance_name,database_role from v$database,v$instance; STATUS INSTANCE_NAME DATABASE_ROLE ------- --------------- ----------------- OPEN psstby PRIMARY
Here above, you can see that the instance PSSTBY which was in the standby role earlier, has now been converted to behave as
Primary. Now, since PSSTBY is Primary database, there is no standby database available for it. A new standby database will have to be created for PSSTBY.
If flashback was enabled on both PRPRIM and PSSTBY instances, then now we can get back PRPRIM instance to behave as Standby database for PSSTBY which is behaving as Primary database.
Here are the steps to bring back old primary (PRPRIM) as standby database:
On the new Primary instance (PSSTBY):
SQL> select status,instance_name,database_role from v$database,v$instance; STATUS INSTANCE_NAME DATABASE_ROLE -------- ------------- ---------------------- OPEN psstby PRIMARY SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ YES
STEP 1:
Note down the SCN on the new primary database (PSSTBY) at which it started behaving as the Primary database:
SQL> select standby_became_primary_scn from v$database; STANDBY_BECAME_PRIMARY_SCN -------------------------- 2023466
The SCN at which PSSTBY started behaving as Primary database is 2023466
STEP 2:
Now mount the old primary (PRPRIM) database:
[oracle@dev ~]$ sqlplus sys/oracle@prprim as sysdba SQL*Plus: Release 11.2.0.2.0 Production on Sat Aug 25 21:02:23 2012 Copyright (c) 1982, 2010, Oracle. All rights reserved. Connected to an idle instance. SQL> startup mount; ORACLE instance started. Total System Global Area 910266368 bytes Fixed Size 2231808 bytes Variable Size 851444224 bytes Database Buffers 50331648 bytes Redo Buffers 6258688 bytes Database mounted. SQL> SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ YES
STEP 3:
Flashback the old primary (PRPRIM) database to the SCN at which PSSTBY became primary database.
SQL> flashback database to scn 2023466; Flashback complete.
STEP 4:
Now convert the old primary (PRPRIM) to behave as Standby database for PSSTBY (new primary database)
SQL> alter database convert to physical standby; Database altered. SQL> shutdown immediate ORA-01507: database not mounted ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 910266368 bytes Fixed Size 2231808 bytes Variable Size 851444224 bytes Database Buffers 50331648 bytes Redo Buffers 6258688 bytes Database mounted. SQL>
STEP 5:
Start the Managed Recovery Process (MRP) on the new standby database(PRPRIM) and check if MRP is started or not.
SQL> alter database recover managed standby database disconnect from session using current logfile; Database altered. SQL> select status,instance_name,database_role from v$database,v$instance; STATUS INSTANCE_NAME DATABASE_ROLE ------- --------------- -------------------- MOUNTED prprim PHYSICAL STANDBY 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
Here above, we can see that MRP is not present under the Process column which in turn means that MRP has not been started.
Let us check the alert log file of my new standby database (PRPRIM)
Sat Aug 25 21:09:59 2012 Serial Media Recovery started Managed Standby Recovery starting Real Time Apply Waiting for all non-current ORLs to be archived... All non-current ORLs have been archived. Media Recovery Log +FRA/prprim/archivelog/2012_08_25/thread_1_seq_165.423.792277639 Identified End-Of-Redo (failover) for thread 1 sequence 165 at SCN 0x0.1ee02c Resetting standby activation ID 0 (0x0) Incomplete Recovery applied until change 2023468 time 08/25/2012 21:00:03 MRP0: Background Media Recovery applied all available redo. Recovery will be restarted once new redo branch is registered Errors in file /u01/app/oracle/diag/rdbms/prprim/prprim/trace/prprim_mrp0_7388.trc: ORA-19906: recovery target incarnation changed during recovery Managed Standby Recovery not using Real Time Apply< Recovery interrupted!
It says that MRP has been cancelled (Recovery interrupted). For this, we need to start the MRP using the keyword “Through All Switchover“.
SQL> select process,status,sequence# from v$managed_standby; PROCESS STATUS SEQUENCE# --------- ------------ ---------- ARCH CLOSING 3 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 RFS IDLE 0 RFS IDLE 0 RFS IDLE 4 RFS IDLE 0 8 rows selected. SQL> alter database recover managed standby database through all switchover disconnect from session using current logfile; Database altered. SQL> select process,status,sequence# from v$managed_standby; PROCESS STATUS SEQUENCE# --------- ------------ ---------- ARCH CLOSING 3 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CONNECTED 0 RFS IDLE 0 RFS IDLE 0 RFS IDLE 4 RFS IDLE 0 MRP0 APPLYING_LOG 1 9 rows selected.
Here above, we can see that MRP has been started on the new standby database (PRPRIM) and MRP is applying log sequence 1.
In 11g, when we perform Flashback operation, the log sequence would get started from sequence 1 on both Primary and Standby database just as when the database would be opened with RESETLOGS.
On the new Primary database (PSSTBY) perform a few log switches and check if they are getting applied on the new standby
database (PRPRIM).
PSSTBY:
SQL> alter system switch logfile; System altered. SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 4 Next log sequence to archive 6 Current log sequence 6
PRPRIM:
SQL> select process,status,sequence# from v$managed_standby; PROCESS STATUS SEQUENCE# --------- ----------- ---------- ARCH CLOSING 3 ARCH CONNECTED 0 ARCH CONNECTED 0 ARCH CLOSING 5 RFS IDLE 0 RFS IDLE 0 RFS IDLE 6 RFS IDLE 0 MRP0 APPLYING_LOG 6 9 rows selected.
So, the log sequence 6 generated on the PSSTBY is getting applied to PRPRIM.
On PSSTBY, check the switchover status.
SQL> select status,instance_name,database_role,switchover_status from v$database,v$instance; STATUS INSTANCE_NAME DATABASE_ROLE SWITCHOVER_STATUS -------- ---------------- ------------- ------------------------ OPEN psstby PRIMARY TO STANDBY
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.
ORA-15173 error normally comes when the required directory is unavailable under the ASM diskgroup.
I came across this error while restoring the SPFILE to directory +DATA. The database had crashed and had to be restored completely. The database name was SRPRIMT and the directory named SRPRIMT was unavailable under the Diskgroup +DATA where I was trying to restore the database.
RMAN> restore spfile from '+FRA/SRPRIMT/AUTOBACKUP/2012_07_10/s_788290484.315.788290487'; Starting restore at 10-JUL-12 using channel ORA_DISK_1 channel ORA_DISK_1: restoring spfile from AUTOBACKUP +FRA/SRPRIMT/AUTOBACKUP/2012_07_10/s_788290484.315.788290487 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of restore command at 07/10/2012 17:57:17 ORA-19870: error while restoring backup piece +FRA/srprimt/autobackup/2012_07_10/s_788290484.315.788290487 ORA-32015: unable to restore SPFILE ORA-17502: ksfdcre:4 Failed to create file +DATA/srprimt/spfilesrprimt.ora ORA-15173: entry 'srprimt' does not exist in directory '/'
In order to overcome this issue, I had to create a directory called SRPRIMT under diskgroup DATA through the ASM instance.
Connect to the ASM instance and create the directory SRPRIMT as shown below.
SQL> alter diskgroup DATA add directory '+DATA/srprimt'; Diskgroup altered.
Now, I tried to restore the spfile and was able to do it without any issues.
RMAN> restore spfile from '+FRA/SRPRIMT/AUTOBACKUP/2012_07_10/s_788290484.315.788290487'; Starting restore at 10-JUL-12 using channel ORA_DISK_1 channel ORA_DISK_1: restoring spfile from AUTOBACKUP +FRA/SRPRIMT/AUTOBACKUP/2012_07_10/s_788290484.315.788290487 channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete Finished restore at 10-JUL-12 RMAN> exit
Creation of the specified directory at the required location (under Diskgroup) resolved the issue.
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.
In Cluster environment, when you check the status of the CRS (Cluster Ready Service) you may find the error as CRS-4535 Cannot communicate with Cluster Ready Services as shown below.
[root@rac1 bin]# ./crsctl check crs CRS-4638: Oracle High Availability Services is online CRS-4535: Cannot communicate with Cluster Ready Services CRS-4530: Communications failure contacting Cluster Synchronization Services daemon CRS-4534: Cannot communicate with Event Manager
This mainly occurs for two reasons:
1. Check if the nodes are able to ping each other in terms of respective IPs (Public, Private and Virtual IP).
2. Check if the Grid owner has the permission on the ASM disks on the node where you faced the error.
In my case, GRID owner was user Oracle. The connectivity between the nodes using their Public, Private and Virtual IPs were perfect and was able to ping each other using the above said IPs.
So, the issue laid with the permission of the ASM disks for the Grid Owner (username Oracle)
This is what I found with the permissions for the ASM disks. The disks were owned by ROOT and ORACLE had no permissions on it.
[root@rac1 bin]# cd /dev/oracleasm/disks [root@rac1 disks]# ls -lrt total 0 brw------- 1 root root 8, 17 May 6 10:16 DISK1 brw------- 1 root root 8, 33 May 6 10:16 DISK2 brw------- 1 root root 8, 49 May 6 10:16 DISK3 brw------- 1 root root 8, 65 May 6 10:16 DISK4 brw------- 1 root root 8, 81 May 6 10:16 DISK5 [root@rac1 bin]# ps -ef | grep css root 3784 1 0 10:17 ? 00:00:01 /u01/app/grid/11.2.0/bin/cssdmonitor root 3801 1 0 10:17 ? 00:00:01 /u01/app/grid/11.2.0/bin/cssdagent root 4189 4107 0 10:26 pts/1 00:00:00 grep css
Now, change the owner of these disks to ORACLE as shown below and also provide appropriate permission for the ORACLE user to read/write these disks.
[root@rac1 disks]# chown -R oracle:dba /dev/oracleasm/disks [root@rac1 disks]# chmod -R 777 /dev/oracleasm/disks [root@rac1 disks]# ls -lrt total 0 brwxrwxrwx 1 oracle dba 8, 17 May 6 10:16 DISK1 brwxrwxrwx 1 oracle dba 8, 33 May 6 10:16 DISK2 brwxrwxrwx 1 oracle dba 8, 49 May 6 10:16 DISK3 brwxrwxrwx 1 oracle dba 8, 65 May 6 10:16 DISK4 brwxrwxrwx 1 oracle dba 8, 81 May 6 10:16 DISK5
Once you have assigned the permission, start the cluster services as the ROOT user.
Change to your $GRID_HOME/bin directory (in my case, $GRID_HOME was /u01/app/oracle/product/11.2.0/grid) and start the cluster services using the CRSCTL utility as shown below.
[root@rac1 bin]# ./crsctl start cluster CRS-2672: Attempting to start 'ora.cssd' on 'rac1' CRS-2676: Start of 'ora.cssd' on 'rac1' succeeded CRS-2672: Attempting to start 'ora.ctssd' on 'rac1' CRS-2676: Start of 'ora.ctssd' on 'rac1' succeeded CRS-2679: Attempting to clean 'ora.asm' on 'rac1' CRS-2681: Clean of 'ora.asm' on 'rac1' succeeded CRS-2672: Attempting to start 'ora.asm' on 'rac1' CRS-2676: Start of 'ora.asm' on 'rac1' succeeded CRS-2672: Attempting to start 'ora.crsd' on 'rac1' CRS-2676: Start of 'ora.crsd' on 'rac1' succeeded
Check the CSS service status:
[root@rac1 bin]# ps -ef | grep css root 3784 1 0 10:17 ? 00:00:01 /u01/app/grid/11.2.0/bin/cssdmonitor root 4372 1 0 10:30 ? 00:00:01 /u01/app/grid/11.2.0/bin/cssdagent oracle 4387 1 1 10:30 ? 00:00:02 /u01/app/grid/11.2.0/bin/ocssd.bin root 5347 4107 0 10:33 pts/1 00:00:00 grep css
Now check if CRS (Cluster Ready Service) is online or not:
[root@rac1 bin]# ./crsctl check crs CRS-4638: Oracle High Availability Services is online CRS-4537: Cluster Ready Services is online CRS-4529: Cluster Synchronization Services is online CRS-4533: Event Manager is online [root@rac1 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.
Pre-requisite:
In Oracle 11gR2, ASM is configured by installing Grid Infrastructure. The localconfig file is no more available in the Oracle Home bin directory from 11gR2 version. This requires Grid Infrastructure to be installed.
Database Name: DEV
You can download the document for migrating database from Non ASM (local file system) to ASM from here.
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.
In Oracle 10g version or earlier, while duplicating a database using RMAN, we had to connect to the Target database along with the Auxiliary Database. In oracle 11g, there is a new feature available, where in the duplication from the Target Database to the Auxiliary Database can be done using RMAN without connecting to the Target database or to the Catalog Database. Only thing what is required the full backup of the Target database. Below is the details on how to go ahead with duplicating the database without connecting to the Target Database or to the Auxiliary Database.
Target Database : myprim
Target Database Server : dev
Auxiliary Database : testdb
Auxiliary Database Server : uat
Step 1:
Take the incremental level 0 backup of the Target database using RMAN.
In my case, I had the backup of my target database (myprim) taken at the location “/u02/bkp”
[oracle@dev bkp]$ pwd /u02/bkp [oracle@dev bkp]$ ls -lrt total 1076968 -rw-r--r-- 1 oracle oinstall 75 Apr 14 21:59 dbid.txt -rw-r----- 1 oracle oinstall 53648384 Apr 25 10:04 MYPRIM_inc0_06n9a795_1_1.bak -rw-r----- 1 oracle oinstall 984506368 Apr 25 10:06 MYPRIM_inc0_07n9a79l_1_1.bak -rw-r----- 1 oracle oinstall 9830400 Apr 25 10:06 MYPRIM_inc0_08n9a7cb_1_1.bak -rw-r----- 1 oracle oinstall 37376 Apr 25 10:06 MYPRIM_inc0_09n9a7cg_1_1.bak -rw-r----- 1 oracle oinstall 53684224 Apr 25 10:06 MYPRIM_inc0_0an9a7ci_1_1.bak
Step 2:
Copy these backup pieces from the Target server (location /u02/bkp) to the auxiliary server (location /u03/backup)
Also, copy the pfile (initmyprim.ora) of the Target database to the Auxiliary server.
Copying Backup Pieces:
[oracle@dev bkp]$ scp *.bak uat:/u03/backup oracle@uat's password: MYPRIM_inc0_06n9a795_1_1.bak 100% 51MB 3.4MB/s 00:15 MYPRIM_inc0_07n9a79l_1_1.bak 100% 939MB 1.8MB/s 08:29 MYPRIM_inc0_08n9a7cb_1_1.bak 100% 9600KB 4.7MB/s 00:02 MYPRIM_inc0_09n9a7cg_1_1.bak 100% 37KB 36.5KB/s 00:00 MYPRIM_inc0_0an9a7ci_1_1.bak 100% 51MB 5.1MB/s 00:10 [oracle@dev bkp]$
Copying pfile of Target Database to Auxiliary Server:
[oracle@dev dbs]$ scp initmyprim.ora uat:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/ oracle@uat's password: initmyprim.ora 100% 921 0.9KB/s 00:01 [oracle@dev dbs]$
Step 3:
On the Auxiliary server, edit the pfile that was copied earlier to the desired entries (dump locations, control file location, datafile locations, if using ASM then specify the desired disk group) and rename it to the desired instance name file (init<SID>.ora). Below is the sample I had it done.
inittestdb.ora
testdb.__db_cache_size=297795584 testdb.__java_pool_size=4194304 testdb.__large_pool_size=4194304 testdb.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment testdb.__pga_aggregate_target=167772160 testdb.__sga_target=494927872 testdb.__shared_io_pool_size=0 testdb.__shared_pool_size=176160768 testdb.__streams_pool_size=4194304 *.audit_file_dest='/u01/app/oracle/admin/testdb/adump' # *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='+DATA' *.db_block_size=8192 *.db_create_file_dest='+DATA' *.db_domain='' *.db_name='testdb' *.db_recovery_file_dest='+ARCH' *.db_recovery_file_dest_size=1073741824 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=testdbXDB)' *.log_archive_format='%t_%s_%r.dbf' *.open_cursors=300 *.pga_aggregate_target=164626432 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=494927872 *.undo_tablespace='UNDOTBS1'
Step 4:
Create a password file for the Auxiliary Database using the ORAPWD utility.
orapwd file=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwtestdb password=<mypassword>
Step 5:
Start the auxiliary instance using the modified by pfile.
[oracle@uat ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 [oracle@uat ~]$ export ORACLE_SID=testdb [oracle@uat ~]$ sqlplus sys/oracle as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 25 10:46:24 2012 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount pfile=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/inittestdb.ora ORACLE instance started. Total System Global Area 492707840 bytes Fixed Size 2214656 bytes Variable Size 188744960 bytes Database Buffers 297795584 bytes Redo Buffers 3952640 bytes SQL>
Step 6:
Connect the auxiliary instance through RMAN and start the duplication.
The duplication is done by specifying the location of the backup pieces. The command to be used is DUPLICATE DATABASE TO ‘<auxiliary dbname>’ BACKUP LOCATION ‘<location of the backup pieces on the auxiliary server>’
[oracle@uat ~]$ rman auxiliary /
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Apr 25 10:48:36 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to auxiliary database: TESTDB (not mounted)
RMAN> duplicate database to 'testdb' backup location '/u03/backup';
Starting Duplicate Db at 26-APR-12
contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 492707840 bytes
Fixed Size 2214656 bytes
Variable Size 155190528 bytes
Database Buffers 331350016 bytes
Redo Buffers 3952640 bytes
contents of Memory Script:
{
sql clone "alter system set control_files =
''+DATA/testdb/controlfile/current.293.781612663'' comment=
''Set by RMAN'' scope=spfile";
sql clone "alter system set db_name =
''MYPRIM'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''TESTDB'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile from '/u03/backup/MYPRIM_inc0_08n9a7cb_1_1.bak';
alter clone database mount;
}
executing Memory Script
sql statement: alter system set control_files = ''+DATA/testdb/controlfile/current.293.781612663'' comment= ''Set by RMAN'' scope=spfile
sql statement: alter system set db_name = ''MYPRIM'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''TESTDB'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 492707840 bytes
Fixed Size 2214656 bytes
Variable Size 155190528 bytes
Database Buffers 331350016 bytes
Redo Buffers 3952640 bytes
Starting restore at 26-APR-12
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=23 device type=DISK
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:09
output file name=+DATA/testdb/controlfile/current.293.781612663
Finished restore at 26-APR-12
database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=23 device type=DISK
contents of Memory Script:
{
set until scn 1015172;
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
restore
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 26-APR-12
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00002 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00003 to +DATA
channel ORA_AUX_DISK_1: restoring datafile 00004 to +DATA
channel ORA_AUX_DISK_1: reading from backup piece /u03/backup/MYPRIM_inc0_07n9a79l_1_1.bak
channel ORA_AUX_DISK_1: piece handle=/u03/backup/MYPRIM_inc0_07n9a79l_1_1.bak tag=TAG20120425T100437
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:17
Finished restore at 26-APR-12
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=781612862 file name=+DATA/testdb/datafile/system.292.781612727
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=781612862 file name=+DATA/testdb/datafile/sysaux.291.781612729
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=781612862 file name=+DATA/testdb/datafile/undotbs1.290.781612731
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=781612862 file name=+DATA/testdb/datafile/users.289.781612731
contents of Memory Script:
{
set until scn 1015172;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 26-APR-12
using channel ORA_AUX_DISK_1
starting media recovery
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=10
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=11
channel ORA_AUX_DISK_1: reading from backup piece /u03/backup/MYPRIM_inc0_0an9a7ci_1_1.bak
channel ORA_AUX_DISK_1: piece handle=/u03/backup/MYPRIM_inc0_0an9a7ci_1_1.bak tag=TAG20120425T100610
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=+ARCH/testdb/archivelog/2012_04_26/thread_1_seq_10.1075.781612867 thread=1 sequence=10
channel clone_default: deleting archived log(s)
archived log file name=+ARCH/testdb/archivelog/2012_04_26/thread_1_seq_10.1075.781612867 RECID=1 STAMP=781612868
archived log file name=+ARCH/testdb/archivelog/2012_04_26/thread_1_seq_11.1076.781612869 thread=1 sequence=11
channel clone_default: deleting archived log(s)
archived log file name=+ARCH/testdb/archivelog/2012_04_26/thread_1_seq_11.1076.781612869 RECID=2 STAMP=781612868
media recovery complete, elapsed time: 00:00:03
Finished recover at 26-APR-12
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set db_name =
''TESTDB'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 492707840 bytes
Fixed Size 2214656 bytes
Variable Size 155190528 bytes
Database Buffers 331350016 bytes
Redo Buffers 3952640 bytes
sql statement: alter system set db_name = ''TESTDB'' comment= ''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 492707840 bytes
Fixed Size 2214656 bytes
Variable Size 155190528 bytes
Database Buffers 331350016 bytes
Redo Buffers 3952640 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TESTDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 50 M ,
GROUP 2 SIZE 50 M ,
GROUP 3 SIZE 50 M
DATAFILE
'+DATA/testdb/datafile/system.292.781612727'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
catalog clone datafilecopy "+DATA/testdb/datafile/sysaux.291.781612729",
"+DATA/testdb/datafile/undotbs1.290.781612731",
"+DATA/testdb/datafile/users.289.781612731";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +DATA in control file
cataloged datafile copy
datafile copy file name=+DATA/testdb/datafile/sysaux.291.781612729 RECID=1 STAMP=781612964
cataloged datafile copy
datafile copy file name=+DATA/testdb/datafile/undotbs1.290.781612731 RECID=2 STAMP=781612964
cataloged datafile copy
datafile copy file name=+DATA/testdb/datafile/users.289.781612731 RECID=3 STAMP=781612964
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=781612964 file name=+DATA/testdb/datafile/sysaux.291.781612729
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=781612964 file name=+DATA/testdb/datafile/undotbs1.290.781612731
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=781612964 file name=+DATA/testdb/datafile/users.289.781612731
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 26-APR-12
RMAN>
Step 7:
Connect to the newly created database.
[oracle@uat ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 26 10:44:36 2012 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> select status,instance_name from v$instance; STATUS INSTANCE_NAME ------------ ---------------- OPEN testdb SQL> select name from v$database; NAME --------- TESTDB
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.
A switchover is a role reversal between the primary database and one of its standby databases. A switchover guarantees no data loss. This is typically done for planned maintenance of the primary system. During a switchover, the primary database transitions to a standby role, and the standby database transitions to the primary role.
You can download the document for manual switchover of physical standby database in 11gR2 from here.
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.
Lets say you have the incremental level 0 backup and also the archivelog backup of the Primary database (which in other terms you can call the Production Database) and you need to restore it or create a new database (Test Database) on a new host. In this case you can go with RMAN duplication method which is simple and no complexities invloved, but to go with RMAN duplication method you need to connect to the Primary database as the target database and the new database (Test database) as the Auxiliary database and optionally a catalog database. Say, if you are not permitted to connect to the Primary database and given only its backup to create the duplication, then you can go with the below method. This method is applicable to 10g and also to 11g, but there is new feature in 11g which allows you do it in a different method which I would be describing it in my next post (I’ll be posting the link here )
Here, you need to note down that the Database Name on the new host (test database) should be same as that of the Primary database. You can change it to the desired name using the NEWID utility once the database is restored and recovered. Also you need to have the DBID of the Primary database.
Step 1:
I have the incremetal level 0 and also the archivelog backup of the Primary database.
[oracle@uat bkp]$ pwd /u02/bkp [oracle@uat bkp]$ ls -lrt total 1043604 -rw-r----- 1 oracle oinstall 1006632960 Apr 14 22:26 MYPRIM_inc0_02n8dugg_1_1.bak -rw-r----- 1 oracle oinstall 12288 Apr 14 22:26 MYPRIM_inc0_04n8dujn_1_1.bak -rw-r----- 1 oracle oinstall 9830400 Apr 14 22:26 MYPRIM_inc0_03n8dujg_1_1.bak -rw-r----- 1 oracle oinstall 51111936 Apr 14 22:26 MYPRIM_inc0_05n8dujq_1_1.bak [oracle@uat bkp]$
Step 2:
Now in the new host, add the entries for the new database to the listener.ora and also to the tnsnames.ora file.
Also, you need to create a password file using ORAPWD utility for the new database.
orapwd file=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwmyprim password=<password>
Step 3:
Connect to the new database as the Target using RMAN in the new host.
[oracle@uat ~]$ rman target sys/<password>@myprim Recovery Manager: Release 11.2.0.1.0 - Production on Sun Apr 1515:40:10 2012 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database (not started)
Step 4:
Set the dbid of the Primary DB that was noted down earlierin the RMAN prompt.
RMAN> set dbid=3322337342; executing command: SET DBID
Step 5:
Start the database forcefully in nomount stage.
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/dbhome_1/dbs/initmyprim.ora' starting Oracle instance without parameter file for retrieval of spfile Oracle instance started Total System Global Area 158662656 bytes Fixed Size 2211448 bytes Variable Size 88080776 bytes Database Buffers 62914560 bytes Redo Buffers 5455872 bytes
Step 6:
Restore the Spfile to PFILE using the backup piece.
RMAN> restore spfile to pfile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initmyprim.ora' from '/u02/bkp/MYPRIM_inc0_03n8dujg_1_1.bak'; Starting restore at 15-APR-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 /u02/bkp/MYPRIM_inc0_03n8dujg_1_1.bak channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete Finished restore at 15-APR-12
Step 7: (Optional)
Make changes to the pfile (control_files,db_create_file_dest,FRA) if the ASM diskgroups are different than they are on the Primary db server. If the diskgroups are same, then no need to make any changes to the pfile. Just need to start the instance in nomount mode using the pfile restored in the previous step. In my case, the asm diskgroups were different from the primary db server. CONTROL_FILES & DATAFILES (db_create_file_dest) are stored in +DATA and flash recovery area is in +ARCH.
Step 8:
Shutdown the Database and start it in nomount stage using the PFILE that was modified/created in the previous step.
RMAN> shutdown immediate Oracle instance shut down RMAN> startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initmyprim.ora'; connected to target database (not started) Oracle instance started Total System Global Area 492707840 bytes Fixed Size 2214656 bytes Variable Size 188744960 bytes Database Buffers 297795584 bytes Redo Buffers 3952640 bytes
Step 9:
Now restore the controlfile from the backup piece and mount the database. The controlfile would be restored to the location depending on the parameter CONTROL_FILES in the PFILE.
RMAN> restore controlfile from '/u02/bkp/MYPRIM_inc0_03n8dujg_1_1.bak'; Starting restore at 15-APR-12 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=23 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:07 output file name=+DATA/myprim/controlfile/current.293.780681475 Finished restore at 15-APR-12 RMAN> alter database mount; database mounted released channel: ORA_DISK_1
Step 10: (Optional)
If the backup pieces are stored in a different location than they were taken on the primary server, then the pieces need to be cataloged. If they are copied to the similar directory structure, then this step can be ignored.
In my case I had taken the backup to the location “/u02/bkp” in the primary server and created the similar direcotry on the auxiliary server as well (“/u02/bkp”) and copied the backups.
RMAN>catalog backuppiece '<backup piece name>';
or
RMAN>catalog start with '<backup piece starting name>';
Step 11:
From the sql prompt, notedown the file# & name of the datafiles if you are using filesystem, as you may have to use the file# accordingly to the file name if you want them to be restored to a different location than the primary.
In my case, I ignore it as I have added the parameter db_create_file_dest pointing to the desired disk group as I mentioned earlier.
[oracle@uat ~]$ sqlplus sys/<password>@myprim as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Sun Apr 15 16:56:13 2012 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, DataMining and Real Application Testing options SQL> select status,instance_name from v$instance; STATUS INSTANCE_NAME -------- ------------------- MOUNTED myprim SQL> select file#,name from v$datafile; FILE# NAME ----- --------------------------------------------------------- 1 +DATA_NEW/myprim/datafile/system.268.780583459 2 +DATA_NEW/myprim/datafile/sysaux.267.780583471 3 +DATA_NEW/myprim/datafile/undotbs1.269.780583481 4 +DATA_NEW/myprim/datafile/users.270.780583481
Step 12: (For Filesystem datafiles)
If you want the datafiles to be restored to a different location (in case of filesystem) than that of the primary, then specify the “set newname for datafile” clause as shown below.
RMAN> run{
2> set newname for datafile 1 to '<desired location>system01.dbf';
3> set newname for datafile 2 to '<desired location>sysaux.dbf';
4> set newname for datafile 3 to '<desired location>undotbs1.dbf';
5> set newname for datafile 4 to '<desired location>users.dbf';
6> RESTORE DATABASE;
7> SWITCH DATAFILE ALL;
8> RECOVER DATABASE;
9> }
Step 13:
If you are on ASM, then you can exclude the above step and restore and recover the database.
RMAN> restore database; Starting restore at 15-APR-12 Starting implicit crosscheck backup at 15-APR-12 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=24 device type=DISK Crosschecked 2 objects Finished implicit crosscheck backup at 15-APR-12 Starting implicit crosscheck copy at 15-APR-12 using channel ORA_DISK_1 Finished implicit crosscheck copy at 15-APR-12 searching for all files in the recovery area cataloging files... no files cataloged 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_NEW/myprim/datafile/system.268.780583459 channel ORA_DISK_1: restoring datafile 00002 to +DATA_NEW/myprim/datafile/sysaux.267.780583471 channel ORA_DISK_1: restoring datafile 00003 to +DATA_NEW/myprim/datafile/undotbs1.269.780583481 channel ORA_DISK_1: restoring datafile 00004 to +DATA_NEW/myprim/datafile/users.270.780583481 channel ORA_DISK_1: reading from backup piece /u02/bkp/MYPRIM_inc0_02n8dugg_1_1.bak channel ORA_DISK_1: piece handle=/u02/bkp/MYPRIM_inc0_02n8dugg_1_1.bak tag=TAG20120414T164256 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:02:27 Finished restore at 15-APR-12 RMAN> recover database; Starting recover at 15-APR-12 using channel ORA_DISK_1 starting media recovery 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 04/15/2012 16:06:07 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 6 and starting SCN of 1002495
Step 14:
Open the database with resetlogs:
RMAN> alter database open resetlogs; Database opened RMAN> SQL> select status,instance_name from v$Instance; STATUS INSTANCE_NAME ------- -------------------- OPEN myprim
Step 15:
Check the location of the datafiles and the redo log files if they have been restored in the right ASM diskgroups.
SQL> select file_name from dba_data_files; FILE_NAME ------------------------------------------------------- +DATA/myprim/datafile/users.289.780681797 +DATA/myprim/datafile/undotbs1.290.780681795 +DATA/myprim/datafile/sysaux.291.780681795 +DATA/myprim/datafile/system.292.780681791 SQL> select group#,member from v$logfile; GROUP# MEMBER ------ -------------------------------------------------------- 3 +DATA/myprim/onlinelog/group_3.292.780686001 3 +ARCH/myprim/onlinelog/group_3.1075.780686005 2 +DATA/myprim/onlinelog/group_2.291.780685995 2 +ARCH/myprim/onlinelog/group_2.1076.780685999 1 +DATA/myprim/onlinelog/group_1.290.780685987 1 +ARCH/myprim/onlinelog/group_1.1080.780685991 6 rows selected.
You can change the database name to the desired name (TESTDB) using the NEWID utility by following the below steps
Step 16:
Shutdown the newly created database and mount it.
[oracle@uat ~]$ sqlplus sys/<password>@myprim as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Tue Apr 17 10:37:28 2012 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.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> startup mount ORACLE instance started. Total System Global Area 492707840 bytes Fixed Size 2214656 bytes Variable Size 188744960 bytes Database Buffers 297795584 bytes Redo Buffers 3952640 bytes Database mounted.
Step 17:
Now use the NEWID utility to change the database name. Use the parameters “dbname” and “setname” parameters while using the NEWID utility.
[oracle@uat ~]$ nid target=sys/<password>@myprim dbname=testdb setname=YES DBNEWID: Release 11.2.0.1.0 - Production on Tue Apr 17 10:39:462012 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to database MYPRIM (DBID=2564150375) Connected to server version 11.2.0 Control Files in database: +DATA/myprim/controlfile/current.285.780684875 Change database name of database MYPRIM to TESTDB? (Y/[N]) => Y Proceeding with operation Changing database name from MYPRIM to TESTDB Control File +DATA/myprim/controlfile/current.285.780684875 - modified Datafile +DATA/myprim/datafile/system.286.78068527 - wrote new name Datafile +DATA/myprim/datafile/sysaux.287.78068527 - wrote new name Datafile +DATA/myprim/datafile/undotbs1.288.78068527 - wrote new name Datafile +DATA/myprim/datafile/users.289.78068527 - wrote new name Datafile +DATA/myprim/tempfile/temp.293.78068601 - wrote new name Control File +DATA/myprim/controlfile/current.285.780684875 - wrote new name Instance shut down Database name changed to TESTDB. Modify parameter file and generate a new password file before restarting. Succesfully changed database name. DBNEWID - Completed succesfully.
Step 18:
[oracle@uat ~]$ sqlplus sys/<password>@myprim as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Tue Apr 17 10:43:40 2012 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 492707840 bytes Fixed Size 2214656 bytes Variable Size 188744960 bytes Database Buffers 297795584 bytes Redo Buffers 3952640 bytes Database mounted. Database opened. SQL> select status,instance_name,name from v$database,v$instance; STATUS INSTANCE_NAME NAME -------- -------------- -------- OPEN myprim TESTDB
Step 19 (Optional):
Now the database name here is changed to TESTDB. If you want to change the instance name as well, then you can rename your INIT<SID>.ora file to INIT<DESIRED-SID>.ora but remember you need to recreate the password file using ORAPWD utility.
orapwd file=<$ORACLE_HOME/dbs/orapw<SID>> password=<passsword>
If you are changing the instance name (SID), note that you will have to make changes in the tnsnames.ora and listener.ora file of the newly created database server by replacing the old SID to the new one, if they are making use of the old SID.
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.
Upon adding a datafile to a tablespace or upon creating a tablespace which again requires you to add datafile on the Primary database, the MRP on the Physical standby database might terminate as soon as the redo information from the primary ships to the standby database. This happens mainly because of the initialization parameter STANDBY_FILE_MANAGEMENT being set to MANUAL in the Standby Database pfile/spfile.
STANDBY_FILE_MANAGEMENT enables or disables automatic standby file management. When automatic standby file management is enabled, operating system file additions and deletions on the primary database are replicated on the standby database.
Note: Setting STANDBY_FILE_MANAGEMENT to AUTO causes Oracle to automatically create files on the standby database and, in some cases, overwrite existing files. Care must be taken when setting STANDBY_FILE_MANAGEMENT and DB_FILE_NAME_CONVERTso that existing standby files will not be accidentally overwritten.
Here is a scenario where you try to create a tablespace on the Primary database and the initialization parameter STANDBY_FILE_MANAGEMENT is set to MANUAL on the Physiacl Standby Database.
Primary database : sspm
Physical Standby database: sssb
On the Primary database:
I create a tablespace called DUMMY and cross verify if its successfully created or not.
SQL> create tablespace dummy datafile size 10m; Tablespace created. SQL> select d.name "DATAFILE",ts.name "TABLESPACE" from v$datafile d,v$tablespace ts where d.ts#=ts.ts#; DATAFILE TABLESPACE ------------------------------------------------ -------------------- +DATA_NEW/sspm/datafile/system.256.778803539 SYSTEM +DATA_NEW/sspm/datafile/sysaux.257.778803541 SYSAUX +DATA_NEW/sspm/datafile/undotbs1.258.778803541 UNDOTBS1 +DATA_NEW/sspm/datafile/users.259.778803543 USERS +DATA_NEW/sspm/datafile/sample.266.779367821 SAMPLE +DATA_NEW/sspm/datafile/dummy.267.779368093 DUMMY 6 rows selected.
Later, I perform the log switch to generate an archive which would be shipped to the Standby database.
SQL> alter system switch logfile; System altered. SQL> select thread#,max(sequence#) from v$archived_log group bythread#; THREAD# MAX(SEQUENCE#) ------- ---------------- 1 1013
On the Standby Database:
I check the maximum sequence# that is applied on the Standby Database.
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#; THREAD# MAX(SEQUENCE#) ------- ---------------- 1 1012
So, it is clear the log sequence# 1013 generated at Primary is not applied to the Standby Database. So, when I check the reason for this log not getting applied in the Standby database alert log file, was able to discover the below message.
[oracle@uat trace]$ pwd /u01/app/oracle/diag/rdbms/sssb/sssb/trace [oracle@uat trace]$ tail -30 alert_sssb.log Sat Mar 31 11:07:39 2012 Media Recovery Log +ARCH/sssb/archivelog/2012_03_31/thread_1_seq_1013.1072.779367799 File #6 added to control file as 'UNNAMED00006' because the parameter STANDBY_FILE_MANAGEMENT is set to MANUAL The file should be manually created to continue. Errors with log +ARCH/sssb/archivelog/2012_03_31/thread_1_seq_1013.1072.779367799 MRP0: Background Media Recovery terminated with error 1274 Errors in file /u01/app/oracle/diag/rdbms/sssb/sssb/trace/sssb_mrp0_23320.trc: ORA-01274: cannot add datafile '+DATA_NEW/sspm/datafile/dummy.267.779368093' - file could not be created Recovery interrupted! Recovery stopped due to failure in applying recovery marker (opcode 17.30). Datafiles are recovered to a consistent state at change 1001582 but controlfile could be ahead of datafiles. RFS[3]: Opened log for thread 1 sequence 1014 dbid 1624493265 branch 778803733 Errors in file /u01/app/oracle/diag/rdbms/sssb/sssb/trace/sssb_mrp0_23320.trc: ORA-01274: cannot add datafile '+DATA_NEW/sspm/datafile/dummy.267.779368093' - file could not be created MRP0: Background Media Recovery process shutdown (sssb)
I cross verify to check if the STANDBY_FILE_MANAGEMENT is set to MANUAL on the standby database.
SQL> show parameter standby_file_management NAME TYPE VALUE ------------------------ --------- ------------ standby_file_management string MANUAL
So, here are the steps I followed to have the datafile get created on the Standby Database.
Step 1: Get the file# and name from the Primary database and check what is the file name that is created on the Standby Database. It would be of the name as UNNAMED at the location “$ORACLE_HOME/dbs” location on LINUX and on windows it would be created at the location “%ORACLE_HOME%\database”
Primary Database:
SQL> select file#,name from v$datafile; FILE# NAME ----- ------------------------------------------------- 1 +DATA_NEW/sspm/datafile/system.256.778803539 2 +DATA_NEW/sspm/datafile/sysaux.257.778803541 3 +DATA_NEW/sspm/datafile/undotbs1.258.778803541 4 +DATA_NEW/sspm/datafile/users.259.778803543 5 +DATA_NEW/sspm/datafile/sample.266.779367821 6 +DATA_NEW/sspm/datafile/dummy.267.779368093 6 rows selected.
Standby Database:
SQL> select file#,name from v$datafile; FILE# NAME ----- ----------------------------------------------------------- 1 +DATA/sssb/datafile/system.274.778865099 2 +DATA/sssb/datafile/sysaux.275.778865193 3 +DATA/sssb/datafile/undotbs1.276.778865259 4 +DATA/sssb/datafile/users.277.778865273 5 +DATA/sssb/datafile/sample.284.779367805 6 /u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00006 6 rows selected.
Here you can notice that file# 6 on the Standby database is created at the location $ORACLE_HOME/dbs with the name as UNNAMED rather than getting created at the specified location (Diskgroup “+DATA”).
Step 2:
On the Standby database, I recreate the unnamed datafile using the “alter database create datafile” option as shown below.
Standby Database:
SQL> alter database create datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00006' as NEW; Database altered.
The syntax of the above statement is as follows “alter database create datafile ” as ‘;”
If its an OMF or on ASM, then the syntax would be as “alter database create datafile ” as NEW;”
After executing the above command, Oracle creates the datafile giving its own name as here OMF is being used.
SQL> select file#,name from v$datafile; FILE# NAME ----- --------------------------------------------- 1 +DATA/sssb/datafile/system.274.778865099 2 +DATA/sssb/datafile/sysaux.275.778865193 3 +DATA/sssb/datafile/undotbs1.276.778865259 4 +DATA/sssb/datafile/users.277.778865273 5 +DATA/sssb/datafile/sample.284.779367805 6 +DATA/sssb/datafile/dummy.285.779368485 6 rows selected. SQL> select d.name "DATAFILE",ts.name "TABLESPACE" from v$datafile d,v$tablespace ts where d.ts#=ts.ts#; DATAFILE TABLESPACE --------------------------------------------- ------------- +DATA/sssb/datafile/system.274.778865099 SYSTEM +DATA/sssb/datafile/sysaux.275.778865193 SYSAUX +DATA/sssb/datafile/undotbs1.276.778865259 UNDOTBS1 +DATA/sssb/datafile/users.277.778865273 USERS +DATA/sssb/datafile/sample.284.779367805 SAMPLE +DATA/sssb/datafile/dummy.285.779368485 DUMMY 6 rows selected.
Step 3: Set the parameter STANDBY_FILE_MANAGEMENT to AUTO in the standby database and start the MRP.
Standby Database:
SQL> alter system set standby_file_management=auto; System altered. SQL> alter database recover managed standby database disconnectfrom session; Database altered.
Now, the archives from the Primary would be applied on the standby database.
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.
There would be scenarios where the standby database lags far behind from the primary database leading to Archive Gap. It could be due to one of the following reasons
1. Might be due to the network outage between the primary and the standby database leading to the archive gaps. Data guard would be able to detect the archive gaps automatically and can fetch the missing logs as soon as the connection is re-established.
2. It could also be due to archive logs getting missed out on the primary database or the archives getting corrupted and there would be no valid backups.
In such cases where the standby lags far behind from the primary database, incremental backups can be used as one of the methods to roll forward the physical standby database to have it in sync with the primary database.
Oracle Database version : 11.2.0.1.0 My Oracle Database is using ASM.
Primary database : sspm Standby database : sssb
Primary Host : dev Standby Host : uat
The maximum archivelog sequence generated on the Primary Database is 1005.
SQL> select status,instance_name,database_role from v$database,v$instance; STATUS INSTANCE_NAME DATABASE_ROLE ------------ ---------------- ---------------- OPEN sspm PRIMARY SQL> select thread#,max(sequence#) from v$archived_log group bythread#; THREAD# MAX(SEQUENCE#) ------- ---------------- 1 1005
On the standby database, the maximum archivelog sequence that is applied is sequence 865.
SQL> select status,instance_name,database_role from v$database,v$instance; STATUS INSTANCE_NAME DATABASE_ROLE ------------ ---------------- ---------------- MOUNTED sssb PHYSICAL STANDBY SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#; THREAD# MAX(SEQUENCE#) ------- ---------------- 1 865
The standby database is lagging behind the primary database by around 140 archives (1005 – 865).
When I investigated the alert log file of the Primary database to find out the reason for the logs not getting applied on the standby database, I got to see the below error message.
Sun Mar 25 15:40:23 2012 Errors in file /u01/app/oracle/diag/rdbms/sspm/sspm/trace/sspm_arc2_18816.trc: ORA-00308: cannot open archived log '+FRA/sspm/archivelog/2012_03_25/thread_1_seq_866.1117.778865785' ORA-17503: ksfdopn:2 Failed to open file +FRA/sspm/archivelog/2012_03_25/thread_1_seq_866.1117.778865785 ORA-15012: ASM file '+FRA/sspm/archivelog/2012_03_25/thread_1_seq_866.1117.778865785' does not exist
So the problem was here. The archivelog sequence 866 was missing and was unavailable at the FRA site. There were few more archives missing on the FRA and nor did I had the backup to restore them on the standby database. My option was to go with Roll Forwarding the Standby Database using Incremental Backups. Below are the steps on how to roll forward the physical standby database.
Step 1: Take a note of the Current SCN of the Physical Standby Database.
Standby Database:
SQL> select current_scn from v$database; CURRENT_SCN ----------- 991247
Note down the CURRENT_SCN value of the standby database (991247) to proceed further.
Step 2 : Cancel the Managed Recovery Process on the Standby database.
Standby Database:
SQL>alter database recover managed standby database cancel;
Step 3: On the Primary database, take the incremental SCN backup from the SCN that is currently recorded on the standby database (991247)
Connect to the primary database and take the incremental SCN backup.
Primary Database:
[oracle@dev ~]$ rman target sys/oracle@sspm Recovery Manager: Release 11.2.0.1.0 - Production on Sun Mar 2515:44:45 2012 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: SSPM (DBID=1624493265) RMAN> backup incremental from scn 991247 database format '/u02/bkp/stnd_backp_%U.bak'; Starting backup at 25-MAR-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=39 device type=DISK backup will be obsolete on date 01-APR-12 archived logs will not be kept or backed up channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00001 name=+DATA_NEW/sspm/datafile/system.256.778803539 input datafile file number=00002 name=+DATA_NEW/sspm/datafile/sysaux.257.778803541 input datafile file number=00003 name=+DATA_NEW/sspm/datafile/undotbs1.258.778803541 input datafile file number=00004 name=+DATA_NEW/sspm/datafile/users.259.778803543 channel ORA_DISK_1: starting piece 1 at 25-MAR-12 channel ORA_DISK_1: finished piece 1 at 25-MAR-12 piece handle=/u02/bkp/stnd_backp_10n6p3nl_1_1.bak tag=TAG20120325T154639 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45 using channel ORA_DISK_1 backup will be obsolete on date 01-APR-12 archived logs will not be kept or backed up channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set channel ORA_DISK_1: starting piece 1 at 25-MAR-12 channel ORA_DISK_1: finished piece 1 at 25-MAR-12 piece handle=/u02/bkp/stnd_backp_11n6p3p4_1_1.bak tag=TAG20120325T154639 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 25-MAR-12
Step 4: Take the standby controlfile backup of the Primary database controlfile.
Connect to the Primary database and create the standby controlfile backup.
Primary Database :
RMAN> backup current controlfile for standby format '/u02/stnd_%U.ctl'; Starting backup at 25-MAR-12 using channel ORA_DISK_1 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including standby control file in backup set channel ORA_DISK_1: starting piece 1 at 25-MAR-12 channel ORA_DISK_1: finished piece 1 at 25-MAR-12 piece handle=/u02/stnd_12n6p3qt_1_1.ctl tag=TAG20120325T154845 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 25-MAR-12
Step 5: Transfer the backups from the Primary Server to the Standby Server.
Primary Database :
[oracle@dev bkp]$ pwd /u02/bkp [oracle@dev bkp]$ ls -lrt total 13576 -rw-r----- 1 oracle oinstall 540672 Mar 25 15:47 stnd_backp_10n6p3nl_1_1.bak -rw-r----- 1 oracle oinstall 13336576 Mar 25 15:47 stnd_backp_11n6p3p4_1_1.bak [oracle@dev bkp]$ scp stnd* uat:/u02/bkp oracle@uat's password: stnd_backp_10n6p3nl_1_1.bak 100% 528KB 528.0KB/s 00:00 stnd_backp_11n6p3p4_1_1.bak 100% 13MB 6.4MB/s 00:02 [oracle@dev bkp]$ cd /u02 [oracle@dev u02]$ ls -lrt stnd* -rw-r----- 1 oracle oinstall 13336576 Mar 25 15:48 stnd_12n6p3qt_1_1.ctl [oracle@dev u02]$ scp stnd* uat:/u02 oracle@uat's password: stnd_12n6p3qt_1_1.ctl 100% 13MB 12.7MB/s 00:01 [oracle@dev u02]$
Step 6: On the standby server, connect the Standby Database through RMAN and catalog the copied incremental backups so that the Controlfile of the Standby Database would be aware of these incremental backups.
I had the incremental backuppieces copied to the location ‘/u02/bkp‘ on the standby server.
Standby Database:
[oracle@uat ~]$ rman target sys/mydbpwd@sssb Recovery Manager: Release 11.2.0.1.0 - Production on Sun Mar 2515:51:02 2012 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: SSPM (DBID=1624493265, not open) RMAN> catalog start with '/u02/bkp'; Starting implicit crosscheck backup at 25-MAR-12 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=37 device type=DISK Crosschecked 15 objects Finished implicit crosscheck backup at 25-MAR-12 Starting implicit crosscheck copy at 25-MAR-12 using channel ORA_DISK_1 Crosschecked 2 objects Finished implicit crosscheck copy at 25-MAR-12 searching for all files in the recovery area cataloging files... File Name: +arch/SSSB/ARCHIVELOG/2012_03_25/thread_1_seq_200.453.778846881 File Name: +arch/SSSB/ARCHIVELOG/2012_03_25/thread_1_seq_201.454.778846881 File Name: +arch/SSSB/ARCHIVELOG/2012_03_25/thread_1_seq_202.455.778846881 File Name: +arch/SSSB/ARCHIVELOG/2012_03_25/thread_1_seq_203.456.778846881 File Name: +arch/SSSB/ARCHIVELOG/2012_03_25/thread_1_seq_137.457.778846881 File Name: +arch/SSSB/ARCHIVELOG/2012_03_25/thread_1_seq_204.458.778846881 File Name: +arch/SSSB/ARCHIVELOG/2012_03_25/thread_1_seq_205.459.778846883 . . . . File Name: +arch/SSSB/ARCHIVELOG/2012_03_25/thread_1_seq_856.947.778861691 File Name: +arch/SSSB/ARCHIVELOG/2012_03_25/thread_1_seq_858.949.778861709 File Name: +arch/SSSB/ARCHIVELOG/2012_03_25/thread_1_seq_857.950.778861719 searching for all files that match the pattern /u02/bkp List of Files Unknown to the Database ===================================== File Name: /u02/bkp/stnd_backp_10n6p3nl_1_1.bak File Name: /u02/bkp/stnd_backp_11n6p3p4_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: /u02/bkp/stnd_backp_10n6p3nl_1_1.bak File Name: /u02/bkp/stnd_backp_11n6p3p4_1_1.bak
Step 7: Recover the standby database with the cataloged incremental backup pieces.
RMAN> recover database noredo; Starting recover at 25-MAR-12 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=32 device type=DISK channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set destination for restore of datafile 00001: +DATA/sssb/datafile/system.274.778865099 destination for restore of datafile 00002: +DATA/sssb/datafile/sysaux.275.778865193 destination for restore of datafile 00003: +DATA/sssb/datafile/undotbs1.276.778865259 destination for restore of datafile 00004: +DATA/sssb/datafile/users.277.778865273 channel ORA_DISK_1: reading from backup piece /u02/bkp/stnd_backp_10n6p3nl_1_1.bak channel ORA_DISK_1: piece handle=/u02/bkp/stnd_backp_10n6p3nl_1_1.bak tag=TAG20120325T154639 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished recover at 25-MAR-12
Step 8 : Shutdown the physical standby database, start it in nomount stage and restore the standby controlfile backup that we had taken from the primary database.
Standby Database:
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 659730432 bytes Fixed Size 2216264 bytes Variable Size 398462648 bytes Database Buffers 255852544 bytes Redo Buffers 3198976 bytes RMAN> restore standby controlfile from '/u02/stnd_12n6p3qt_1_1.ctl'; Starting restore at 25-MAR-12 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=21 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:04 output file name=+DATA/sssb/controlfile/current.273.778864875 Finished restore at 25-MAR-12
Step 9: Shutdown the standby database and mount the standby database, so that the standby database would be mounted with the new controlfile that was restored in the previous step.
Standby Database:
RMAN> <strong>shutdown immediate Oracle instance shut down RMAN> startup mount connected to target database (not started) Oracle instance started database mounted Total System Global Area 659730432 bytes Fixed Size 2216264 bytes Variable Size 398462648 bytes Database Buffers 255852544 bytes Redo Buffers 3198976 bytes
Step 10: If the datafile location of the primary and standby databases are different, then you need to follow this step. If not, then proceed with Step 11.
The datafiles of my primary database are residing on the Diskgroup +DATA_NEW on the primary server and the datafiles on the standby database are residing on the Diskgroup +DATA on the standby server, the datafiles location are different.
Since, I have restored the standby controlfile backuppiece of my primary database on the standby database (Step 7) and mounted the standby database, the standby database controlfile would now have the locations of the datafiles recorded as available in the Primary database. So, we need to make the standby controlfile understand that the datafiles location of the standby database are different from that of the Primary database. For this, you need to catalog the datafile location of the standby database to its controlfile as shown below.
Connect the standby database through RMAN and catalog the location of its datafiles and later switch them.
Standby Database:
RMAN> catalog start with '+DATA/SSSB/DATAFILE'; searching for all files that match the pattern +DATA/SSSB/DATAFILE List of Files Unknown to the Database ===================================== File Name: +data/SSSB/DATAFILE/SYSTEM.274.778865099 File Name: +data/SSSB/DATAFILE/SYSAUX.275.778865193 File Name: +data/SSSB/DATAFILE/UNDOTBS1.276.778865259 File Name: +data/SSSB/DATAFILE/USERS.277.778865273 Do you really want to catalog the above files (enter YES or NO)? YES cataloging files... cataloging done List of Cataloged Files ======================= File Name: +data/SSSB/DATAFILE/SYSTEM.274.778865099 File Name: +data/SSSB/DATAFILE/SYSAUX.275.778865193 File Name: +data/SSSB/DATAFILE/UNDOTBS1.276.778865259 File Name: +data/SSSB/DATAFILE/USERS.277.778865273 RMAN> switch database to copy; datafile 1 switched to datafile copy "+DATA/sssb/datafile/system.274.778865099" datafile 2 switched to datafile copy "+DATA/sssb/datafile/sysaux.275.778865193" datafile 3 switched to datafile copy "+DATA/sssb/datafile/undotbs1.276.778865259" datafile 4 switched to datafile copy "+DATA/sssb/datafile/users.277.778865273" RMAN>
Step 11: If the datafile locations of the primary and the standby databases are same, then there is no necessity to perform the catalogging operation as done in the previous step.
On the standby database, start the Managed Recovery Process.
Standby Database:
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 1010 RFS IDLE 0 MRP0 WAIT_FOR_LOG 0 9 rows selected.
Step 12: On the Primary database, check the Maximum Archivelog Sequence generated.
Primary Database:
SQL> select thread#,max(sequence#) from v$archived_log group bythread#; THREAD# MAX(SEQUENCE#) ------- ---------------- 1 1009
Step 13: Check the maximum archivelog sequence that is applied on the Physical standby database.
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#; THREAD# MAX(SEQUENCE#) ------- ---------------- 1 1009
So, here we can see from Steps 12 and 13 that the maximum archivelog sequence generated on the Primary database is sequence# 1009 and that applied on the Physical Standby Database is also 1009 which means that the Standby database is in sync with the Primary Database. You can check it out by generating an archive sequence on the Primary database and check if its shipped and applied on the standby database.
Primary Database:
SQL> alter system switch logfile; System altered. SQL> select thread#,max(sequence#) from v$archived_log group bythread#; THREAD# MAX(SEQUENCE#) ------- ---------------- 1 1010
Standby Database:
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#; THREAD# MAX(SEQUENCE#) ------- ---------------- 1 1010
Now standby database is in sync with the Primary Database.
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.
The main advantage of setting up dataguard in 11g is that the database can be opened in Read-Only mode allowing the Users to access the physical standby database for fetching reports and on the same time the physical standby database can be in recovery mode. In other words, the physical standby database would be in recovery mode and hand in hand the standby database can be used for reporting purposes.
The read-only physical standby database can be used to offload query from the primary database. Users can use select statements and complex queries against this this database and thereby decreasing the load on the primary database. While the standby is open read-only, the following operations are disallowed
- Any Data Manipulation Language (DML) except for select statements
- Any Data Definition Language (DDL)
- Access of local sequences
- DMLs on local temporary tables
Steps on how to setup the active dataguard:
Once you setup the physical standby database as described in https://shivanandarao.wordpress.com/2012/03/10/creating-physical-standby-database-on-oracle-11g/ follow the below steps to setup the active dataguard.
Step 1:
Check the status of the Primary database and the latest sequence generated in the primary database.
SQL> select status,instance_name,database_role from v$instance,v$database; STATUS INSTANCE_NAME DATABASE_ROLE ------------ ---------------- ---------------- OPEN prim PRIMARY SQL> select max(sequence#) from v$archived_log; MAX(SEQUENCE#) -------------- 40
Step 2:
Check the status of the physical standby database and the latest sequence applied on the physcial standby database.
SQL> select status,instance_name,database_role from v$database,v$instance; STATUS INSTANCE_NAME DATABASE_ROLE -------- ------------- --------------------- MOUNTED stnd PHYSICAL STANDBY SQL> select max(sequence#) from v$archived_log where applied='YES'; MAX(SEQUENCE#) -------------- 40
Step 3:
Check if the Managed Recovery Process (MRP) is active on the physcial standby database.
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 41 RFS IDLE 0 RFS IDLE 0 RFS IDLE 0 MRP0 WAIT_FOR_LOG 41 9 rows selected.
Here, MRP is active. The PROCESS Column above shows that MRP is active and is waiting for the log sequence 41.
Step 4:
Cancel the MRP on the physical standby database and open the standby database. The standby database would be opened in the READ-ONLY Mode.
SQL> alter database recover managed standby database cancel; Database altered. 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 stnd PHYSICAL STANDBY READ ONLY
Step 5:
Now start the MRP on the physical standby database.
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 41 RFS IDLE 0 RFS IDLE 0 RFS IDLE 0 MRP0 WAIT_FOR_LOG 41 9 rows selected.
Here, you can see that the MRP is active and is waiting for the log sequence 41 and also the physical standby database is opened in READ-ONLY mode which would allow users to use the physical standby database for fetching reports.
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.
Shivananda Rao 