Using Physical Standby with transient Logical Standby (SQL Apply) for near zero downtime upgrade of two node Oracle RAC database from 11.2.0.2 to 11.2.0.3
Using Physical Standby with transient Logical Standby (SQL Apply) for near zero downtime upgrade of two node Oracle RAC database from 11.2.0.2 to 11.2.0.3
In the article you will have a look at an example of using a physical standby database for a near zero downtime upgrade of a two node Oracle RAC database from Oracle 11.2.0.2 to Oracle 11.2.0.3. The cluster configuration is described here. The approach discussed in the article is available from Oracle 11gR1 onwards only and is similar to using a logical standby database for near zero downtime migration discussed here. However, the approach involving a physical standby database starts from an existing physical database that in turn is transitioned temporarily into a transient logical standby database only for upgrade and at the end you have upgraded primary and physical standby databases. For the testing both the primary and standby databases share the same 11.2.0.3 cluster. Furthermore, My Oracle Support Note 949322.1 provides a script physru that will be used that significantly simplifies the upgrade. Also a transient logical standby approach performs only one database migration on the standby site and on the next switchover synchronizes the newly converted physical database(former primary) with the upgraded primary(former standby) whereas the logical standby database performs two upgrades on the logical standby database. Look at the official Oracle documentation here for the enhanced clauses options added in Oracle 11gR1 to the ALTER DATABASE RECOVER TO LOGICAL STANDBY KEEP IDENTITY; that allows a physical standby database to be used for a rolling upgrade by converting it into a logical standby database that keeps the same DB_NAME and DBID as the primary. Oracle documentation states:
Such a logical standby database can only participate in one switchover operation, and thus should only be created in the context of a rolling upgrade with a physical standby database.
I the article you will look at the following topics:
- Overview of using a physical standby database for a transient logical standby database rolling upgrade
- Build a physical standby database
- Prepare and execute migration from 11.2.0.2 to 11.2.0.3 using physru script on two node Oracle RAC database
- Recovery in case of errors
Earlier I have upgraded the cluster to 11.2.0.3 and installed Oracle RDBMS 11.2.0.3 binaries on each node. In the article a physical standby database will be build and used for the upgrade. Each node has a separate non-shared $OH for the GI and RDBMS versions installed. The table below summarizes some environment details used in the article.
Node1 | Node2 | |
Linux hostname | raclinux1 | raclinux2 |
11.2.0.1 $OH | /u01/app/oracle/product/11.2.0/db_1 | |
11.2.0.3 $OH | /u01/app/oracle/product/11.2.0/db_3 | |
Production SID | RACD1 | RACD2 |
Logical Standby SID | RACDSTB1 | RACDSTB2 |
RACD is an 11.2.0.2 database and a physical standby database RACSTB will be built and used for upgrade to 11.2.0.3. Cluster provides a shared pool of storage that consists of DATA and DATADG disk groups. DATADG hosts RACD files and DATA hosts RACDSBT files. The upgrade process goes through the following conceptual stages illustrated in the next section.
- Overview of using a physical standby database for a transient logical standby database rolling upgrade
The following outlines the steps required to use a physical standby to perform a rolling database upgrade:
- Enable flashback database on both primary and standby database and create guaranteed restore points on both primary and standby database.
- Convert the primary database to a transient logical standby database using the standard procedure, as described here, except that replace the call to ALTER DATABASE RECOVER TO LOGICAL STANDBY DBNAME; with ALTER DATABASE RECOVER TO LOGICAL STANDBY KEEP IDENTITY;
- Stop SQL apply on the standby, upgrade the standby database and synch with the primary
- Perform a switchover so that the upgraded logical standby becomes a new primary database.
- Flashback the former primary to the restore point and convert it to a physical standby database.
- Mount the physical standby database from the upgrade $OH and synch with the primary. NOTE that this is the way it gets upgraded.
- Switchover back to the original configuration
Oracle provided script, Note 949322.1, physru will be used to automate and control the upgrade process. The stages are illustrated in the table below.
Stage | Primary Site | Standby site | Production | Major Activity (Details can be seen is psysru output), both databases refers to both RACD and RACDSTB |
Before running physru | RACD 11.2.0.2 | Physical standby RACDSTB 11.2.0.2 | RACD | RACDSTB is running managed recovery |
1st run | RACD 11.2.0.2 | Physical standby RACDSTB 11.2.0.2 | RACD | 1st run physru |
1st run physru running |
RACD 11.2.0.2 | Logical standby RACDSTB 11.2.0.2 | RACD |
|
1st run completed | RACD 11.2.0.2 | Logical standby RACDSTB 11.2.0.2 | RACD | |
Standby database upgrade( dbua and/or manual) | RACD 11.2.0.2 | Logical standby RACDSTB 11.2.0.2 -> 11.2.0.3 | RACD | Run an upgrade of RACSTB. Once the upgrade is complete, the database must be opened in READ WRITE mode before the script can be called to resume the rolling upgrade. |
Test phase | RACD 11.2.0.2 | Logical standby RACDSTB 11.2.0.3 | RACD | Conduct a thorough testing on the upgraded standby database. |
2nd run physru | RACD 11.2.0.2 | Logical standby RACDSTB 11.2.0.3 | RACD | 2nd run |
2nd run physru running | RACD 11.2.0.2 | Logical standby RACDSTB 11.2.0.3 | RACD->RACDSTB |
|
RACDSTB 11.2.0.3 | Physical standby RACD 11.2.0.2 | RACDSTB | ||
3rd run physru | RACDSTB 11.2.0.3 | Physical standby RACD 11.2.0.2 | RACDSTB | Mount the RACD from 11.2.0.3 $OH |
3rd run physru running | RACDSTB 11.2.0.3 | Physical standby RACD 11.2.0.2 | RACDSTB->RACD |
|
end | RACD 11.2.0.3 | Physical standby RACDSTB 11.2.0.3 | RACD |
Both RACD and RACDSTB are two node RAC databases. Only is certain case CLUSTER_DATABASE parameter is set to FALSE. The article aim at documenting steps involved in a near zero downtime upgrade using logical standby and can be used for 11g releases, i.e. upgrade from 11.1.0.X or 11.2.0.X to 11.2.0.X+1 as Oracle has not changed the interface for creating and managing standby databases from 11.1 to 11.2 significantly. There are some data type restrictions that are not subject to coverage here but can be examined in detail and worked around using the Oracle documentation listed in the reference section.
-
Build a physical standby database
In this section you will look at the detailed steps to build a physical standby database (for sake of testing in the same cluster). For information related to building 11.2 standby databases look here or follow the approach used in here. Look in the Appendix for the detail configuration or command output.
-
Enable force logging on the primary database RACD by running
SQL> alter database force logging;
- Make sure that the source primary database is running in archive log mode
-
Create password file
On Node 1
[oracle@raclinux1 dbs]$ cat sh.sh
rm orapwRACDSTB orapwRACDSTB1
orapwd file=orapwRACDSTB1 entries=100 password=sys1 ignorecase=y
orapwd file=orapwRACDSTB entries=100 password=sys1 ignorecase=y
rm orapwRACD1 orapwRACD
orapwd file=orapwRACD1 entries=100 password=sys1 ignorecase=y
orapwd file=orapwRACD entries=100 password=sys1 ignorecase=y
[oracle@raclinux1 dbs]$ ./sh.sh
rm: cannot remove `orapwRACD’: No such file or directory
[oracle@raclinux1 dbs]$
On Node 2
[oracle@raclinux2 dbs]$ cat sh.sh
rm orapwRACDSTB orapwRACDSTB2
orapwd file=orapwRACDSTB2 entries=100 password=sys1 ignorecase=y
orapwd file=orapwRACDSTB entries=100 password=sys1 ignorecase=y
rm orapwRACD2 orapwRACD
orapwd file=orapwRACD2 entries=100 password=sys1 ignorecase=y
orapwd file=orapwRACD entries=100 password=sys1 ignorecase=y
[oracle@raclinux2 dbs]$ ./sh.sh
[oracle@raclinux2 dbs]$
-
Make sure that you can login as sysdba
SQL> connect sys/sys1@sracd as sysdba
Connected to an idle instance.
SQL>
SQL> connect sys/sys1@pracd as sysdba
Connected.
SQL>
SQL> connect sys/sys1@racd as sysdba
Connected.
SQL>
-
Configure standby redo log on the primary database
Oracle documentation states that you need to have n+1 standby redo logs if there are n redo log groups per thread.
In my case I have two threads with two groups each. I will create three groups of standby redo log for each thread.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
———- ———- ———- ———- ———- ———- — —————- ————- ——— ———— ———
1 1 5 52428800 512 2 YES INACTIVE 1053870 28-JUN-12 1104131 28-JUN-12
2 1 6 52428800 512 2 NO CURRENT 1104131 28-JUN-12 2.8147E+14
3 2 1 52428800 512 2 YES INACTIVE 1060619 28-JUN-12 1084093 28-JUN-12
4 2 2 52428800 512 2 NO CURRENT 1084093 28-JUN-12 2.8147E+14
SQL>
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
———- ——- ——- ————————————————– —
2 ONLINE +DATA/racd/onlinelog/group_2.1090.787106247 NO
2 ONLINE +DATADG/racd/onlinelog/group_2.902.787106251 YES
1 ONLINE +DATA/racd/onlinelog/group_1.1089.787106233 NO
1 ONLINE +DATADG/racd/onlinelog/group_1.901.787106239 YES
3 ONLINE +DATA/racd/onlinelog/group_3.1094.787107109 NO
3 ONLINE +DATADG/racd/onlinelog/group_3.903.787107117 YES
4 ONLINE +DATA/racd/onlinelog/group_4.1095.787107123 NO
4 ONLINE +DATADG/racd/onlinelog/group_4.904.787107127 YES
8 rows selected.
SQL>
The following standby redo logs that are created.
alter database add standby logfile thread 1 group 5 size 52428800;
alter database add standby logfile thread 1 group 6 size 52428800;
alter database add standby logfile thread 1 group 7 size 52428800;
alter database add standby logfile thread 2 group 8 size 52428800;
alter database add standby logfile thread 2 group 9 size 52428800;
alter database add standby logfile thread 2 group 10 size 52428800;
SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
GROUP# THREAD# SEQUENCE# ARC STATUS
———- ———- ———- — ———-
5 1 0 YES UNASSIGNED
6 1 0 YES UNASSIGNED
7 1 0 YES UNASSIGNED
8 2 0 YES UNASSIGNED
9 2 0 YES UNASSIGNED
10 2 0 YES UNASSIGNED
6 rows selected.
SQL>
-
Configure tnsnames.ora aliases.
Here RACD and RACDSTB will be used for redo shipment. Pay attention to the PRACD and SRACD that will be used to startup and shutdown a database using a password file authentication and during the upgrade with physru script.
RACD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RACD)
)
)
RACDSTB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RACDSTB)
)
)
PRACD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = raclinux1-vip)(PORT = 1521))
)
(CONNECT_DATA =
(SID = RACD1)
)
)
SRACD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = raclinux1-vip)(PORT = 1521))
)
(CONNECT_DATA =
(SID = RACDSTB1)
)
)
-
Set a static listener.ora
In grid home make the following static listener entries on each node. Look in the Appendix for my configuration. For example for node1:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = RAC)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RAC1)
)
(SID_DESC =
(GLOBAL_DBNAME = RAC_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RAC1)
)
(SID_DESC =
(GLOBAL_DBNAME = RACDSTB)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RACDSTB1)
)
(SID_DESC =
(GLOBAL_DBNAME = RACDSTB_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RACDSTB1)
)
)
SID_LIST_LISTENER_SCAN1 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = RAC)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RAC)
)
(SID_DESC =
(GLOBAL_DBNAME = RAC_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RAC)
)
(SID_DESC =
(GLOBAL_DBNAME = RACDSTB)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RACDSTB)
)
(SID_DESC =
(GLOBAL_DBNAME = RACDSTB_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RACDSTB)
)
)
-
Modify the init parameters on RACD primary database.
alter system set LOG_ARCHIVE_CONFIG=’DG_CONFIG=(RACD,RACDSTB)’ scope=both sid=’*’;
alter system set LOG_ARCHIVE_DEST_1=’LOCATION=+DATADG/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=RACD’ scope=both sid=’*’;
alter system set LOG_ARCHIVE_DEST_2=’SERVICE=RACDSTB LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RACDSTB’ scope=both sid=’*’;
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both sid=’*’;
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both sid=’*’;
alter system set LOG_ARCHIVE_MAX_PROCESSES=30 scope=both sid=’*’;
alter system set FAL_SERVER=RACDSTB scope=both sid=’*’;
alter system set FAL_CLIENT=RACD scope=both sid=’*’;
alter system set DB_FILE_NAME_CONVERT=’+DATADG/RACDSTB’,’+DATA/RACD’,’+DATA/RACDSTB’,’+DATADG/RACD’ scope=spfile sid=’*’;
alter system set LOG_FILE_NAME_CONVERT=’+DATADG/RACDSTB’,’+DATA/RACD’,’+DATA/RACDSTB’,’+DATADG/RACD’ scope=spfile sid=’*’;
alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both sid=’*’;
alter system set cluster_database=false scope=spfile sid=’*’;
-
Prepare the text parameter file for RACDSTB
Copy the text parameter file of the RACD primary database into /tmp/i.ora and modify the following parameters. Look at the Appendix for the full content of the file. Remove the control_files parameter.
DB_NAME=RACD
DB_UNIQUE_NAME=RACDSTB
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(RACD,RACDSTB)’
DB_FILE_NAME_CONVERT=’+DATA/RACD’,’+DATADG/RACDSTB’,’+DATADG/RACD’,’+DATA/RACDSTB’
LOG_FILE_NAME_CONVERT=’+DATA/RACD’,’+DATADG/RACDSTB’,’+DATADG/RACD’,’+DATA/RACDSTB’
LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
LOG_ARCHIVE_DEST_1=’LOCATION=+DATA/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=RACDSTB’
LOG_ARCHIVE_DEST_2=’SERVICE=RACD LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RACD’
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=RACD
FAL_CLIENT=RACDSTB
*.db_create_file_dest=’+DATADG’
-
Create the following directories on each node.
mkdir /u01/app/oracle/admin/RACDSTB
[oracle@raclinux1 RACDSTB]$ pwd
/u01/app/oracle/admin/RACDSTB
[oracle@raclinux1 RACDSTB]$
[oracle@raclinux1 RACDSTB]$ mkdir adump dpdump hdump pfile
[oracle@raclinux1 RACDSTB]$
-
Verify connection to the primary and standby using the aliases PRACD and SRACD
SQL> connect sys/sys1@pracd as sysdba
Connected.
SQL> connect sys/sys1@sracd as sysdba
Connected to an idle instance.
SQL>
-
Start the auxiliary instance
[oracle@raclinux1 dbs]$ sqlplus sys/sys1@sracd as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Sat Jun 30 19:32:11 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile=’/tmp/i.ora’;
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2228072 bytes
Variable Size 423624856 bytes
Database Buffers 92274688 bytes
Redo Buffers 3809280 bytes
SQL>
-
Run the cloning script. Refer to the Appendix for the output. Ignore the error at the end as this is going to be a physical standby database and will synch it.
[oracle@raclinux1 dbs]$ rman target sys/sys1@pracd auxiliary sys/sys1@sracd
Recovery Manager: Release 11.2.0.2.0 – Production on Sat Jun 30 19:32:59 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: RACD (DBID=1643674760)
connected to auxiliary database: RACD (not mounted)
RMAN>
run {
allocate channel tst type disk;
allocate channel tst1 type disk;
allocate auxiliary channel tststby type disk;
sql ‘alter system archive log current’;
duplicate target database for standby from active database DORECOVER;
}
- Identify the control file and include them in the text parameter file.
-
Make an spfile
[oracle@raclinux1 dbs]$ cat initRACDSTB1.ora
SPFILE=’+DATA/RACDSTB/spfileRACDSTB.ora’
[oracle@raclinux1 dbs]$
[oracle@raclinux2 dbs]$ cat initRACD2.ora
SPFILE=’+DATA/RACD/spfileRACD.ora’
[oracle@raclinux2 dbs]$
SQL> create SPFILE=’+DATA/RACDSTB/spfileRACDSTB.ora’ from pfile=’/tmp/i.ora’;
-
Register the database.
srvctl add database -d RACDSTB -o /u01/app/oracle/product/11.2.0/db_1 -p +DATA/RACDSTB/spfileRACDSTB.ora -r physical_standby -y automatic
srvctl add instance -d RACDSTB -i RACDSTB2 -n raclinux2
srvctl add instance -d RACDSTB -i RACDSTB1 -n raclinux1
-
Start the managed recovery on the standby.
On the standby
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL>
-
Verify the physical standby database is created successfully and receiving and applying the redo from the primary. Perform a few redo log archival operations on the primary and make sure that the redo is applied on the standby.
On primary
SQL> select name, PROTECTION_MODE, PROTECTION_LEVEL, OPEN_MODE, DATABASE_ROLE from v$database;
NAME PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE
——— ——————– ——————– ——————–
DATABASE_ROLE
—————-
RACD MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE READ WRITE
PRIMARY
SQL>
On standby
SQL> select name, PROTECTION_MODE, PROTECTION_LEVEL, OPEN_MODE, DATABASE_ROLE from v$database;
NAME PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE
——— ——————– ——————– ——————–
DATABASE_ROLE
—————-
RACD MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE MOUNTED
PHYSICAL STANDBY
SQL>
On primary
SQL> select ‘Instance’||thread#||’: Last Applied=’||max(sequence#)||’ (resetlogs_change#=’||resetlogs_change#||’)’ from v$archived_log where applied = (select decode(database_role, ‘PRIMARY’, ‘NO’, ‘YES’) from v$database) and thread# in (select thread# from gv$instance) and resetlogs_change# = (select resetlogs_change# from v$database) group by thread#, resetlogs_change# order by thread#;
‘INSTANCE’||THREAD#||’:LASTAPPLIED=’||MAX(SEQUENCE#)||'(RESETLOGS_CHANGE#=’||RES
——————————————————————————–
Instance1: Last Applied=16 (resetlogs_change#=972274)
Instance2: Last Applied=13 (resetlogs_change#=972274)
SQL>
On standby
select ‘Instance’||thread#||’: Last Applied=’||max(sequence#)||’ (resetlogs_change#=’||resetlogs_change#||’)’ from v$archived_log where applied = (select decode(database_role, ‘PRIMARY’, ‘NO’, ‘YES’) from v$database) and thread# in (select thread# from gv$instance) and resetlogs_change# = (select resetlogs_change# from v$database) group by thread#, resetlogs_change# order by thread#;
‘INSTANCE’||THREAD#||’:LASTAPPLIED=’||MAX(SEQUENCE#)||'(RESETLOGS_CHANGE#=’||RES
——————————————————————————–
Instance1: Last Applied=15 (resetlogs_change#=972274)
SQL>
SQL> select sequence#, applied from v$archived_log order by sequence#;
SEQUENCE# APPLIED
———- ———
4 YES
5 YES
6 YES
7 YES
8 YES
8 YES
9 YES
9 YES
10 YES
10 YES
11 YES
SEQUENCE# APPLIED
———- ———
11 YES
12 YES
12 YES
13 YES
13 YES
14 YES
15 YES
16 IN-MEMORY
19 rows selected.
SQL>
COLUMN NAME FORMAT A18
COLUMN VALUE FORMAT A16
COLUMN TIME_COMPUTED FORMAT A24
SELECT * FROM V$DATAGUARD_STATS;SQL> SQL> SQL>
NAME VALUE UNIT TIME_COMPUTED DATUM_TIME
—————— —————- —————————— ———————— ——————————
transport lag +00 00:00:00 day(2) to second(0) interval 07/01/2012 00:45:37 07/01/2012 00:45:36
apply lag +00 00:00:00 day(2) to second(0) interval 07/01/2012 00:45:37 07/01/2012 00:45:36
apply finish time +00 00:00:00.000 day(2) to second(3) interval 07/01/2012 00:45:37
estimated startup 27 second 07/01/2012 00:45:37
time
SQL>
-
Test that switchover works and after switchover the new standby operates successfully and receives and applies the redo log from primary.
On the primary
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
——————–
TO STANDBY
SQL>
SQL> alter database commit to switchover to physical standby with session shutdown;
Database altered.
SQL>
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2228072 bytes
Variable Size 406847640 bytes
Database Buffers 109051904 bytes
Redo Buffers 3809280 bytes
Database mounted.
SQL>
On the standby.
SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
——————–
TO PRIMARY
SQL>
SQL> alter database commit to switchover to primary with session shutdown;
Database altered.
SQL> alter database open;
Database altered.
SQL> select name, PROTECTION_MODE, PROTECTION_LEVEL, OPEN_MODE, DATABASE_ROLE from v$database;
NAME PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE DATABASE_ROLE
—————— ——————– ——————– ——————– —————-
RACD MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE READ WRITE PRIMARY
SQL>
On primary
SQL> select name, PROTECTION_MODE, PROTECTION_LEVEL, OPEN_MODE, DATABASE_ROLE from v$database;
NAME PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE
——— ——————– ——————– ——————–
DATABASE_ROLE
—————-
RACD MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE MOUNTED
PHYSICAL STANDBY
SQL>
On the standby
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL>
Repeat the procedure in the preceding section by performing archival on the primary and monitoring the redo apply on the standby.
-
Test another switchover to get back to the RACD primary and RACDSTB physical standby and verify that redo is applied on the standby database.
- Upgrade
3.1 Make sure that you have flashback database feature enabled on both primary and physical standby database.
On the standby
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database flashback on;
Database altered.
SQL>
On the primary
SQL> startup mount;
ORACLE instance started.
Total System Global Area 521936896 bytes
Fixed Size 2228072 bytes
Variable Size 406847640 bytes
Database Buffers 109051904 bytes
Redo Buffers 3809280 bytes
Database mounted.
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
SQL>
Download the physru script from My Oracle Support Note 949322.1 and make it executable by the software owner that installed Oracle RDBMS binaries. Calling phusru without an argument provides help and names the arguments which are self explanatory.
oracle@raclinux1 ~]$ /u01/physru
Usage: physru <username> <password> <primary_tns> <standby_tns>
<primary_name> <standby_name> <upgrade_version>
Purpose:
Perform a rolling upgrade between a primary and physical standby database.
This script simplifies a physical standby rolling upgrade. While numerous
steps have been automated, this script must be called at least three times
in order to complete a rolling upgrade. When this script reaches a point
where user intervention is required, it outputs a message indicating what
is expected of the user. Once the user action is complete, this script can
be called to resume the rolling upgrade. In the event of an error, a user
can take corrective action, and simply call this script again to resume the
rolling upgrade. In the event one wishes to abandon the rolling upgrade, and
revert the configuration back to its pre-upgrade state, this script creates
guaranteed flashback database restore points on both the primary and standby
databases, and backs up each databases’ associated control file. The names
of the restore points and backup control files are output to the console and
logfile when they are initially created.
When this script is called, it assumes all databases to be either mounted or
open. It requires flashback database to be enabled on both the primary and
standby instances. RAC configurations are permitted but there is limited
automation provided by the script. At specific points it may become
necessary to manually shutdown/startup instances and change init.ora
parameter values. When appropriate, the script will output when these
requirements are expected of the user. RAC configurations are also required
to define static tns services since this script expects a given tns service
name to contact the same instance on successive calls.
Arguments:
<username> = dba username
<primary_tns> = tns service name to primary
<standby_tns> = tns service name to physical standby
<primary_name> = db_unique_name of primary (required on RAC)
<standby_name> = db_unique_name of standby (required on RAC)
<upgrade_version> = target rdbms version
Example:
physru sys hq_tnspri hq_tnsstb hq_primary hq_standby 11.2.0.2.0
NOTE: This script performs role transitions, and it is not necessary to
adjust the tns and db name arguments to their respective database roles
on successive calls. That is, the arguments must remain the same from
first-invocation to completion.
ERROR: 0 of the 6 required parameters have been specified
[oracle@raclinux1 ~]$
3.2 First physru run
Here the arguments are as follows
- sys – Oracle sys user
- pracd – tnsnames alias for the primary database
- sracd – tnsnames alias for the standby database
- racd – db_unique_name for the primary database
- racdstb – db_unique_name for the standby database
/u01/physru sys pracd sracd racd racdstb 11.2.0.3
[oracle@raclinux1 ~]$ /u01/physru sys pracd sracd racd racdstb 11.2.0.3
Please enter the sysdba password:
### Initialize script to either start over or resume execution
Jul 01 01:22:39 2012 [0-1] Identifying rdbms software version
Jul 01 01:22:39 2012 [0-1] database racd is at version 11.2.0.2.0
Jul 01 01:22:39 2012 [0-1] database racdstb is at version 11.2.0.2.0
Jul 01 01:22:40 2012 [0-1] verifying flashback database is enabled at RACD and RACDSTB
Jul 01 01:22:40 2012 [0-1] verifying available flashback restore points
Jul 01 01:22:40 2012 [0-1] verifying DG Broker is disabled
Jul 01 01:22:40 2012 [0-1] looking up prior execution history
Jul 01 01:22:40 2012 [0-1] purging script execution state from database RACD
Jul 01 01:22:41 2012 [0-1] purging script execution state from database RACDSTB
Jul 01 01:22:41 2012 [0-1] starting new execution of script
### Stage 1: Backup user environment in case rolling upgrade is aborted
Jul 01 01:22:41 2012 [1-1] stopping media recovery on RACDSTB
Jul 01 01:22:42 2012 [1-1] creating restore point PRU_0000_0001 on database RACDSTB
Jul 01 01:22:44 2012 [1-1] backing up current control file on RACDSTB
Jul 01 01:22:47 2012 [1-1] created backup control file /u01/app/oracle/product/11.2.0/db_1/dbs/PRU_0001_RACDSTB_f.f
Jul 01 01:22:47 2012 [1-1] creating restore point PRU_0000_0001 on database RACD
Jul 01 01:22:47 2012 [1-1] backing up current control file on RACD
Jul 01 01:22:49 2012 [1-1] created backup control file /u01/app/oracle/product/11.2.0/db_1/dbs/PRU_0001_RACD_f.f
NOTE: Restore point PRU_0000_0001 and backup control file PRU_0001_RACDSTB_f.f
can be used to restore RACDSTB back to its original state as a
physical standby, in case the rolling upgrade operation needs to be aborted
prior to the first switchover done in Stage 4.
### Stage 2: Create transient logical standby from existing physical standby
Jul 01 01:22:50 2012 [2-1] verifying RAC is disabled at RACDSTB
Jul 01 01:22:50 2012 [2-1] verifying database roles
Jul 01 01:22:50 2012 [2-1] verifying physical standby is mounted
Jul 01 01:22:50 2012 [2-1] verifying database protection mode
Jul 01 01:22:50 2012 [2-1] verifying transient logical standby datatype support
WARN: Objects have been identified on the primary database which will not be
replicated on the transient logical standby. The complete list of
objects and their associated unsupported datatypes can be found in the
dba_logstdby_unsupported view. For convenience, this script has written
the contents of this view to a file – physru_unsupported.log.
Various options exist to deal with these objects such as:
– disabling applications that modify these objects
– manually resolving these objects after the upgrade
– extending support to these objects (see metalink note: 559353.1)
If you need time to review these options, you should enter ‘n’ to exit
the script. Otherwise, you should enter ‘y’ to continue with the
rolling upgrade.
Are you ready to proceed with the rolling upgrade? (y/n): y
Jul 01 01:23:16 2012 [2-1] continuing
Jul 01 01:23:16 2012 [2-2] starting media recovery on RACDSTB
Jul 01 01:23:22 2012 [2-2] confirming media recovery is running
Jul 01 01:23:24 2012 [2-2] waiting for v$dataguard_stats view to initialize
Jul 01 01:23:25 2012 [2-2] waiting for apply lag on RACDSTB to fall below 30 seconds
Jul 01 01:23:56 2012 [2-2] apply lag is now less than 30 seconds
Jul 01 01:23:56 2012 [2-2] stopping media recovery on RACDSTB
Jul 01 01:23:57 2012 [2-2] executing dbms_logstdby.build on database RACD
Jul 01 01:24:41 2012 [2-2] converting physical standby into transient logical standby
Jul 01 01:24:58 2012 [2-3] opening database RACDSTB
Jul 01 01:25:20 2012 [2-4] configuring transient logical standby parameters for rolling upgrade
Jul 01 01:25:22 2012 [2-4] starting logical standby on database RACDSTB
Jul 01 01:25:44 2012 [2-4] waiting until logminer dictionary has fully loaded
Jul 01 01:27:02 2012 [2-4] dictionary load 03% complete
Jul 01 01:27:12 2012 [2-4] dictionary load 17% complete
Jul 01 01:27:23 2012 [2-4] dictionary load 29% complete
Jul 01 01:27:33 2012 [2-4] dictionary load 36% complete
Jul 01 01:27:44 2012 [2-4] dictionary load 40% complete
Jul 01 01:27:54 2012 [2-4] dictionary load 55% complete
Jul 01 01:28:07 2012 [2-4] dictionary load 62% complete
Jul 01 01:28:37 2012 [2-4] dictionary load 65% complete
Jul 01 01:28:47 2012 [2-4] dictionary load 70% complete
Jul 01 01:28:58 2012 [2-4] dictionary load 75% complete
Jul 01 01:29:38 2012 [2-4] dictionary load 99% complete
Jul 01 01:29:48 2012 [2-4] dictionary load is complete
Jul 01 01:29:51 2012 [2-4] waiting for v$dataguard_stats view to initialize
Jul 01 01:30:18 2012 [2-4] waiting for apply lag on RACDSTB to fall below 30 seconds
Jul 01 01:30:19 2012 [2-4] apply lag is now less than 30 seconds
NOTE: Database RACDSTB is now ready to be upgraded. This script has left the
database open in case you want to perform any further tasks before
upgrading the database. Once the upgrade is complete, the database must
opened in READ WRITE mode before this script can be called to resume the
rolling upgrade.
NOTE: If RACDSTB was previously a RAC database that was disabled, it may be
reverted back to a RAC database upon completion of the rdbms upgrade.
This can be accomplished by performing the following steps:
1) On instance RACDSTB1, set the cluster_database parameter to TRUE.
eg: SQL> alter system set cluster_database=true scope=spfile;
2) Shutdown instance RACDSTB1.
eg: SQL> shutdown abort;
3) Startup and open all instances for database RACDSTB.
eg: srvctl start database -d RACDSTB
[oracle@raclinux1 ~]$
3.3 Prepare and perform migration on RACDSTB
Follow the recommendations by issuing the following commands from sqlplus on instance RACDSTB1.
alter system set cluster_database=true scope=spfile sid=’*’;
shutdown abort;
Start all RACDSTB instances
[oracle@raclinux1 ~]$ srvctl start database -d RACDSTB
[oracle@raclinux1 ~]$
Invoke dbua from 11.2.0.3 $OH
Make sure all components are successfully upgraded. Useful note is ‘How To Diagnose Components With NON VALID Status In DBA_REGISTRY After an Upgrade’ [ID 753041.1]. Re-running catupgrd.sql also sometimes resolves incomplete upgrade issues. At the end I have all valid and I can proceed further.
@utlu112s.sql
Database opened.
SQL> SQL>
.
Oracle Database 11.2 Post-Upgrade Status Tool 07-01-2012 08:01:50
.
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
.
Oracle Server
. VALID 11.2.0.3.0 00:48:33
JServer JAVA Virtual Machine
. VALID 11.2.0.3.0 00:00:00
Oracle Real Application Clusters
. VALID 11.2.0.3.0 00:00:00
Oracle Workspace Manager
. VALID 11.2.0.3.0 00:00:00
OLAP Analytic Workspace
. VALID 11.2.0.3.0 00:00:00
OLAP Catalog
. VALID 11.2.0.3.0 00:02:08
Oracle OLAP API
. VALID 11.2.0.3.0 00:00:00
Oracle Enterprise Manager
. VALID 11.2.0.3.0 00:00:00
Oracle XDK
. VALID 11.2.0.3.0 00:00:00
Oracle Text
. VALID 11.2.0.3.0 00:00:00
Oracle XML Database
. VALID 11.2.0.3.0 00:00:00
Oracle Database Java Packages
. VALID 11.2.0.3.0 00:00:00
Oracle Multimedia
. VALID 11.2.0.3.0 00:00:00
Spatial
. VALID 11.2.0.3.0 00:00:00
Oracle Expression Filter
. VALID 11.2.0.3.0 00:00:00
Oracle Rules Manager
. VALID 11.2.0.3.0 00:00:00
Oracle Application Express
. VALID 3.2.1.00.12
Gathering Statistics
. 00:09:29
Total Upgrade Time: 01:00:44
PL/SQL procedure successfully completed.
SQL> SQL>
Copy password files and init file from 11.2.0.2 $OH to 11.2.0.3 $OH
[oracle@raclinux1 dbs]$ cp initRACD1.ora /u01/app/oracle/product/11.2.0/db_3/dbs/initRACD1.ora
[oracle@raclinux1 dbs]$ cp initRACDSTB1.ora /u01/app/oracle/product/11.2.0/db_3/dbs/initRACDSTB1.ora
[oracle@raclinux1 dbs]$ cp sh.sh /u01/app/oracle/product/11.2.0/db_3/dbs/sh.sh
[oracle@raclinux1 dbs]$
[oracle@raclinux2 dbs]$ cp initRACD2.ora /u01/app/oracle/product/11.2.0/db_3/dbs/initRACD2.ora
[oracle@raclinux2 dbs]$ cp initRACDSTB2.ora /u01/app/oracle/product/11.2.0/db_3/dbs/initRACDSTB2.ora
[oracle@raclinux2 dbs]$ cp sh.sh /u01/app/oracle/product/11.2.0/db_3/dbs/sh.sh
[oracle@raclinux2 dbs]$
Modify listener to 11.2.0.3 $OH for RACDSTB and reload the listener. Add tnsnames.ora entries to the 11.2.0.3 $OH from 11.2.0.2 $OH
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = RAC)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RAC1)
)
(SID_DESC =
(GLOBAL_DBNAME = RAC_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RAC1)
)
(SID_DESC =
(GLOBAL_DBNAME = RACDSTB)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_3)
(SID_NAME = RACDSTB1)
)
(SID_DESC =
(GLOBAL_DBNAME = RACDSTB_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_3)
(SID_NAME = RACDSTB1)
)
)
SID_LIST_LISTENER_SCAN1 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = RAC)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RAC)
)
(SID_DESC =
(GLOBAL_DBNAME = RAC_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RAC)
)
(SID_DESC =
(GLOBAL_DBNAME = RACDSTB)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_3)
(SID_NAME = RACDSTB)
)
(SID_DESC =
(GLOBAL_DBNAME = RACDSTB_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_3)
(SID_NAME = RACDSTB)
)
)
3.2 Second physru run
From the new $OH start the script. In case you wonder why I am re-running it I discovered an error in RACDSTB alert log that crashed the SQL Apply processes. I had to bounce RACDSTB in order to increase the memory_max_target and memory_target,by default all are the same. Note that physru script can be re-run and offers options either to resume from wherever it left off or to continue the script from scratch. I opted to resume.
- If you face a particular problem assess the situation and make a decision and how to proceed.
/u01/physru sys pracd sracd racd racdstb 11.2.0.3
[oracle@raclinux1 admin]$ /u01/physru sys pracd sracd racd racdstb 11.2.0.3.0
Please enter the sysdba password:
### Initialize script to either start over or resume execution
Jul 01 08:34:33 2012 [0-1] Identifying rdbms software version
Jul 01 08:34:33 2012 [0-1] database racd is at version 11.2.0.2.0
Jul 01 08:34:33 2012 [0-1] database racdstb is at version 11.2.0.3.0
Jul 01 08:34:34 2012 [0-1] verifying flashback database is enabled at RACD and RACDSTB
Jul 01 08:34:35 2012 [0-1] verifying available flashback restore points
Jul 01 08:34:35 2012 [0-1] verifying DG Broker is disabled
Jul 01 08:34:35 2012 [0-1] looking up prior execution history
Jul 01 08:34:35 2012 [0-1] last completed stage [2-4] using script version 0001
Jul 01 08:34:35 2012 [0-1] resuming execution of script
### Stage 3: Validate upgraded transient logical standby
Jul 01 08:34:35 2012 [3-1] database RACDSTB is no longer in OPEN MIGRATE mode
Jul 01 08:34:35 2012 [3-1] database RACDSTB is at version 11.2.0.3.0
### Stage 4: Switch the transient logical standby to be the new primary
Jul 01 08:34:36 2012 [4-1] waiting for RACDSTB to catch up (this could take a while)
Jul 01 08:34:37 2012 [4-1] starting logical standby on database RACDSTB
Jul 01 08:34:45 2012 [4-1] waiting for v$dataguard_stats view to initialize
Jul 01 08:34:46 2012 [4-1] waiting for apply lag on RACDSTB to fall below 30 seconds
Jul 01 08:35:17 2012 [4-1] current apply lag: 22468
Jul 01 08:35:47 2012 [4-1] current apply lag: 22498
Jul 01 08:36:17 2012 [4-1] current apply lag: 22529
Jul 01 08:36:47 2012 [4-1] current apply lag: 22558
Jul 01 08:37:17 2012 [4-1] current apply lag: 22588
Jul 01 08:37:48 2012 [4-1] current apply lag: 22619
Jul 01 08:38:18 2012 [4-1] current apply lag: 22649
Jul 01 08:38:48 2012 [4-1] current apply lag: 22679
Jul 01 08:39:18 2012 [4-1] current apply lag: 22710
Jul 01 08:39:49 2012 [4-1] current apply lag: 22739
Jul 01 08:40:19 2012 [4-1] current apply lag: 22770
Jul 01 08:40:49 2012 [4-1] current apply lag: 22800
Jul 01 08:41:19 2012 [4-1] current apply lag: 22831
Jul 01 08:41:49 2012 [4-1] current apply lag: 22861
Jul 01 08:42:20 2012 [4-1] current apply lag: 22891
Jul 01 08:42:50 2012 [4-1] current apply lag: 22921
Jul 01 08:43:20 2012 [4-1] current apply lag: 22951
Jul 01 08:43:50 2012 [4-1] current apply lag: 22981
Jul 01 08:44:20 2012 [4-1] current apply lag: 23012
Jul 01 08:44:50 2012 [4-1] current apply lag: 23041
Jul 01 08:45:20 2012 [4-1] current apply lag: 23072
Jul 01 08:45:51 2012 [4-1] current apply lag: 23102
Jul 01 08:46:21 2012 [4-1] current apply lag: 23132
Jul 01 08:46:51 2012 [4-1] current apply lag: 23162
Jul 01 08:47:21 2012 [4-1] current apply lag: 23190
Jul 01 08:47:51 2012 [4-1] current apply lag: 23222
Jul 01 08:48:22 2012 [4-1] current apply lag: 23253
Jul 01 08:48:52 2012 [4-1] current apply lag: 23283
Jul 01 08:49:23 2012 [4-1] current apply lag: 23314
Jul 01 08:49:53 2012 [4-1] current apply lag: 23344
Jul 01 08:50:23 2012 [4-1] current apply lag: 23375
Jul 01 08:50:53 2012 [4-1] current apply lag: 23404
Jul 01 08:51:23 2012 [4-1] current apply lag: 23435
Jul 01 08:51:53 2012 [4-1] current apply lag: 23465
Jul 01 08:52:24 2012 [4-1] current apply lag: 23495
Jul 01 08:52:54 2012 [4-1] current apply lag: 23525
Jul 01 08:53:25 2012 [4-1] current apply lag: 23555
Jul 01 08:53:56 2012 [4-1] current apply lag: 23586
Jul 01 08:54:26 2012 [4-1] current apply lag: 23616
Jul 01 08:54:26 2012 [4-1] current apply lag: 23616
Jul 01 08:54:56 2012 [4-1] current apply lag: 23648
Jul 01 08:55:26 2012 [4-1] current apply lag: 23678
Jul 01 08:55:57 2012 [4-1] current apply lag: 23707
Jul 01 08:56:27 2012 [4-1] current apply lag: 23738
Jul 01 08:57:05 2012 [4-1] current apply lag: 23768
Jul 01 08:57:35 2012 [4-1] current apply lag: 23807
Jul 01 08:58:06 2012 [4-1] current apply lag: 23837
Jul 01 08:58:36 2012 [4-1] current apply lag: 23867
Jul 01 08:59:06 2012 [4-1] current apply lag: 23897
Jul 01 08:59:36 2012 [4-1] current apply lag: 23928
Jul 01 09:00:07 2012 [4-1] current apply lag: 23958
Jul 01 09:00:38 2012 [4-1] current apply lag: 23988
Jul 01 09:01:08 2012 [4-1] current apply lag: 24019
Jul 01 09:01:38 2012 [4-1] current apply lag: 24051
Jul 01 09:02:09 2012 [4-1] current apply lag: 24081
Jul 01 09:02:39 2012 [4-1] current apply lag: 24110
Jul 01 09:03:09 2012 [4-1] current apply lag: 24141
Jul 01 09:03:39 2012 [4-1] current apply lag: 24170
Jul 01 09:04:10 2012 [4-1] current apply lag: 24201
Jul 01 09:04:40 2012 [4-1] current apply lag: 24231
Jul 01 09:05:10 2012 [4-1] ERROR: timed out after 30 minutes of inactivity
[oracle@raclinux1 admin]$
Re-run and select to resume from where the last operation left off. When prompted execute the suggested commands and press y in order to continue.
[oracle@raclinux1 ~]$ /u01/physru sys pracd sracd racd racdstb 11.2.0.3.0
Please enter the sysdba password:
### Initialize script to either start over or resume execution
Jul 01 09:48:30 2012 [0-1] Identifying rdbms software version
Jul 01 09:48:31 2012 [0-1] database racd is at version 11.2.0.2.0
Jul 01 09:48:31 2012 [0-1] database racdstb is at version 11.2.0.3.0
Jul 01 09:48:32 2012 [0-1] verifying flashback database is enabled at RACD and RACDSTB
Jul 01 09:48:32 2012 [0-1] verifying available flashback restore points
Jul 01 09:48:32 2012 [0-1] verifying DG Broker is disabled
Jul 01 09:48:32 2012 [0-1] looking up prior execution history
Jul 01 09:48:33 2012 [0-1] last completed stage [3-1] using script version 0001
WARN: The last execution of this script either exited in error or at the
user’s request. At this point, there are three available options:
1) resume the rolling upgrade where the last execution left off
2) restart the script from scratch
3) exit the script
Option (2) assumes the user has restored the primary and physical
standby back to the original configuration as required by this script.
Enter your selection (1/2/3): 1
Jul 01 09:48:43 2012 [0-1] resuming execution of script
### Stage 4: Switch the transient logical standby to be the new primary
Jul 01 09:48:44 2012 [4-1] waiting for RACDSTB to catch up (this could take a while)
Jul 01 09:48:48 2012 [4-1] starting logical standby on database RACDSTB
Jul 01 09:49:03 2012 [4-1] waiting for v$dataguard_stats view to initialize
Jul 01 09:49:04 2012 [4-1] waiting for apply lag on RACDSTB to fall below 30 seconds
Jul 01 09:49:39 2012 [4-1] current apply lag: 26929
Jul 01 09:50:25 2012 [4-1] current apply lag: 13661
Jul 01 09:51:08 2012 [4-1] current apply lag: 13704
Jul 01 09:51:48 2012 [4-1] current apply lag: 13704
Jul 01 09:52:22 2012 [4-1] current apply lag: 13742
Jul 01 09:52:56 2012 [4-1] current apply lag: 13775
Jul 01 09:53:27 2012 [4-1] current apply lag: 1343
Jul 01 09:53:57 2012 [4-1] apply lag is now less than 30 seconds
Jul 01 09:53:58 2012 [4-2] switching RACD to become a logical standby
Jul 01 09:54:36 2012 [4-2] RACD is now a logical standby
Jul 01 09:54:38 2012 [4-3] waiting for standby RACDSTB to process end-of-redo from primary
Jul 01 09:54:38 2012 [4-4] switching RACDSTB to become the new primary
Jul 01 09:55:44 2012 [4-4] RACDSTB is now the new primary
### Stage 5: Flashback former primary to pre-upgrade restore point and convert to physical
Jul 01 09:55:52 2012 [5-1] verifying instance RACD1 is the only active instance
WARN: RACD is a RAC database. Before this script can continue, you
must manually reduce the RAC to a single instance. This can be
accomplished with the following step:
1) Shutdown all instances other than instance RACD1.
eg: srvctl stop instance -d RACD -i RACD2 -o abort
Once these steps have been performed, enter ‘y’ to continue the script.
If desired, you may enter ‘n’ to exit the script to perform the required
steps, and recall the script to resume from this point.
Are you ready to continue? (y/n): y
Jul 01 10:02:20 2012 [5-1] continuing
Jul 01 10:02:20 2012 [5-1] verifying instance RACD1 is the only active instance
Jul 01 10:02:21 2012 [5-1] shutting down database RACD
Jul 01 10:03:05 2012 [5-1] mounting database RACD
Jul 01 10:04:03 2012 [5-2] flashing back database RACD to restore point PRU_0000_0001
Jul 01 10:04:41 2012 [5-3] converting RACD into physical standby
Jul 01 10:04:44 2012 [5-4] shutting down database RACD
NOTE: Database RACD has been shutdown, and is now ready to be started
using the newer version Oracle binary. This script requires the
database to be mounted (on all active instances, if RAC) before calling
this script to resume the rolling upgrade.
NOTE: Database RACD is no longer limited to single instance operation since
the database has been successfully converted into a physical standby.
For increased availability, Oracle recommends starting all instances in
the RAC on the newer binary by performing the following step:
1) Startup and mount all instances for database RACD
eg: srvctl start database -d RACD -o mount
[oracle@raclinux1 ~]$
Now RACDSTB has become a primary database and RACD is converted to a physical database. Need to mount the RACD database from the new 11.2.0.3 $OH.
From the OLD 11.2.0.2 $OH execute
srvctl remove database -d RACD
From the NEW 11.2.0.3 $OH
srvctl add database -d RACD -o /u01/app/oracle/product/11.2.0/db_3 -p +DATA/RACD/spfileRACD.ora -r physical_standby -y automatic
srvctl add instance -d RACD -i RACD2 -n raclinux2
srvctl add instance -d RACD -i RACD1 -n raclinux1
Startup and mount all RACD instances
srvctl start database -d RACD -o mount
Modify the listeners to reflect the 11.2.0.3 $OH for RACD database
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = RAC)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_3)
(SID_NAME = RAC1)
)
(SID_DESC =
(GLOBAL_DBNAME = RAC_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_3)
(SID_NAME = RAC1)
)
(SID_DESC =
(GLOBAL_DBNAME = RACDSTB)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_3)
(SID_NAME = RACDSTB1)
)
(SID_DESC =
(GLOBAL_DBNAME = RACDSTB_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_3)
(SID_NAME = RACDSTB1)
)
)
SID_LIST_LISTENER_SCAN1 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = RAC)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_3)
(SID_NAME = RAC)
)
(SID_DESC =
(GLOBAL_DBNAME = RAC_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_3)
(SID_NAME = RAC)
)
(SID_DESC =
(GLOBAL_DBNAME = RACDSTB)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_3)
(SID_NAME = RACDSTB)
)
(SID_DESC =
(GLOBAL_DBNAME = RACDSTB_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_3)
(SID_NAME = RACDSTB)
)
)
3.5 Third execution of physru script
[oracle@raclinux1 dbs]$ /u01/physru sys pracd sracd racd racdstb 11.2.0.3.0
Please enter the sysdba password:
### Initialize script to either start over or resume execution
Jul 01 10:23:07 2012 [0-1] Identifying rdbms software version
Jul 01 10:23:07 2012 [0-1] database racd is at version 11.2.0.3.0
Jul 01 10:23:08 2012 [0-1] database racdstb is at version 11.2.0.3.0
Jul 01 10:23:22 2012 [0-1] verifying flashback database is enabled at RACD and RACDSTB
Jul 01 10:23:30 2012 [0-1] verifying available flashback restore points
Jul 01 10:23:39 2012 [0-1] verifying DG Broker is disabled
Jul 01 10:23:41 2012 [0-1] looking up prior execution history
Jul 01 10:23:54 2012 [0-1] last completed stage [5-4] using script version 0001
Jul 01 10:23:54 2012 [0-1] resuming execution of script
### Stage 6: Run media recovery through upgrade redo
Jul 01 10:24:14 2012 [6-1] upgrade redo region identified as scn range [1201071, 2247592]
Jul 01 10:24:35 2012 [6-1] starting media recovery on RACD
Jul 01 10:25:19 2012 [6-1] confirming media recovery is running
Jul 01 10:25:20 2012 [6-1] waiting for media recovery to initialize v$recovery_progress
Jul 01 10:27:12 2012 [6-1] monitoring media recovery’s progress
Jul 01 10:27:18 2012 [6-2] last applied scn 1183723 is approaching upgrade redo start scn 1201071
Jul 01 10:27:35 2012 [6-2] last applied scn 1185398 is approaching upgrade redo start scn 1201071
Jul 01 10:27:50 2012 [6-2] last applied scn 1187346 is approaching upgrade redo start scn 1201071
Jul 01 10:28:06 2012 [6-2] last applied scn 1188023 is approaching upgrade redo start scn 1201071
Jul 01 10:28:21 2012 [6-2] last applied scn 1188541 is approaching upgrade redo start scn 1201071
Jul 01 10:28:36 2012 [6-2] last applied scn 1191010 is approaching upgrade redo start scn 1201071
Jul 01 10:28:51 2012 [6-2] last applied scn 1192835 is approaching upgrade redo start scn 1201071
Jul 01 10:29:06 2012 [6-2] last applied scn 1195814 is approaching upgrade redo start scn 1201071
Jul 01 10:30:08 2012 [6-3] recovery of upgrade redo at 01% – estimated complete at Jul 01 13:27:47
Jul 01 10:30:24 2012 [6-3] recovery of upgrade redo at 02% – estimated complete at Jul 01 12:20:07
Jul 01 10:30:39 2012 [6-3] recovery of upgrade redo at 04% – estimated complete at Jul 01 11:36:21
Jul 01 10:30:54 2012 [6-3] recovery of upgrade redo at 06% – estimated complete at Jul 01 11:21:42
Jul 01 10:31:09 2012 [6-3] recovery of upgrade redo at 07% – estimated complete at Jul 01 11:18:12
Jul 01 10:31:24 2012 [6-3] recovery of upgrade redo at 08% – estimated complete at Jul 01 11:14:59
Jul 01 10:31:40 2012 [6-3] recovery of upgrade redo at 09% – estimated complete at Jul 01 11:13:58
Jul 01 10:31:55 2012 [6-3] recovery of upgrade redo at 10% – estimated complete at Jul 01 11:12:51
Jul 01 10:32:10 2012 [6-3] recovery of upgrade redo at 11% – estimated complete at Jul 01 11:10:54
Jul 01 10:32:25 2012 [6-3] recovery of upgrade redo at 12% – estimated complete at Jul 01 11:06:50
Jul 01 10:32:40 2012 [6-3] recovery of upgrade redo at 13% – estimated complete at Jul 01 11:08:26
Jul 01 10:33:56 2012 [6-3] recovery of upgrade redo at 14% – estimated complete at Jul 01 11:13:42
Jul 01 10:34:27 2012 [6-3] recovery of upgrade redo at 15% – estimated complete at Jul 01 11:12:59
Jul 01 10:34:42 2012 [6-3] recovery of upgrade redo at 16% – estimated complete at Jul 01 11:13:13
Jul 01 10:35:12 2012 [6-3] recovery of upgrade redo at 18% – estimated complete at Jul 01 11:10:48
Jul 01 10:35:42 2012 [6-3] recovery of upgrade redo at 19% – estimated complete at Jul 01 11:11:01
Jul 01 10:36:27 2012 [6-3] recovery of upgrade redo at 20% – estimated complete at Jul 01 11:12:50
Jul 01 10:36:58 2012 [6-3] recovery of upgrade redo at 21% – estimated complete at Jul 01 11:12:49
Jul 01 10:37:13 2012 [6-3] recovery of upgrade redo at 23% – estimated complete at Jul 01 11:09:50
Jul 01 10:37:28 2012 [6-3] recovery of upgrade redo at 27% – estimated complete at Jul 01 11:04:28
Jul 01 10:38:14 2012 [6-3] recovery of upgrade redo at 32% – estimated complete at Jul 01 11:01:36
Jul 01 10:38:59 2012 [6-3] recovery of upgrade redo at 33% – estimated complete at Jul 01 11:02:29
Jul 01 10:39:29 2012 [6-3] recovery of upgrade redo at 34% – estimated complete at Jul 01 11:03:05
Jul 01 10:39:59 2012 [6-3] recovery of upgrade redo at 36% – estimated complete at Jul 01 11:02:14
Jul 01 10:40:29 2012 [6-3] recovery of upgrade redo at 37% – estimated complete at Jul 01 11:02:43
Jul 01 10:41:00 2012 [6-3] recovery of upgrade redo at 38% – estimated complete at Jul 01 11:03:13
Jul 01 10:42:00 2012 [6-3] recovery of upgrade redo at 39% – estimated complete at Jul 01 11:04:48
Jul 01 10:42:46 2012 [6-3] recovery of upgrade redo at 40% – estimated complete at Jul 01 11:05:43
Jul 01 10:43:16 2012 [6-3] recovery of upgrade redo at 41% – estimated complete at Jul 01 11:05:47
Jul 01 10:43:31 2012 [6-3] recovery of upgrade redo at 43% – estimated complete at Jul 01 11:04:57
Jul 01 10:43:31 2012 [6-3] recovery of upgrade redo at 43% – estimated complete at Jul 01 11:04:57
Jul 01 10:44:16 2012 [6-3] recovery of upgrade redo at 45% – estimated complete at Jul 01 11:05:03
Jul 01 10:44:47 2012 [6-3] recovery of upgrade redo at 46% – estimated complete at Jul 01 11:04:44
Jul 01 10:45:03 2012 [6-3] recovery of upgrade redo at 47% – estimated complete at Jul 01 11:04:38
Jul 01 10:45:18 2012 [6-3] recovery of upgrade redo at 48% – estimated complete at Jul 01 11:04:23
Jul 01 10:46:03 2012 [6-3] recovery of upgrade redo at 49% – estimated complete at Jul 01 11:04:57
Jul 01 10:46:19 2012 [6-3] recovery of upgrade redo at 50% – estimated complete at Jul 01 11:05:14
Jul 01 10:47:04 2012 [6-3] recovery of upgrade redo at 51% – estimated complete at Jul 01 11:05:57
Jul 01 10:47:19 2012 [6-3] recovery of upgrade redo at 52% – estimated complete at Jul 01 11:05:34
Jul 01 10:47:49 2012 [6-3] recovery of upgrade redo at 53% – estimated complete at Jul 01 11:06:03
Jul 01 10:48:35 2012 [6-3] recovery of upgrade redo at 54% – estimated complete at Jul 01 11:06:15
Jul 01 10:49:20 2012 [6-3] recovery of upgrade redo at 55% – estimated complete at Jul 01 11:06:45
Jul 01 10:49:35 2012 [6-3] recovery of upgrade redo at 57% – estimated complete at Jul 01 11:06:04
Jul 01 10:49:50 2012 [6-3] recovery of upgrade redo at 58% – estimated complete at Jul 01 11:05:49
Jul 01 10:50:05 2012 [6-3] recovery of upgrade redo at 59% – estimated complete at Jul 01 11:05:49
Jul 01 10:50:51 2012 [6-3] recovery of upgrade redo at 60% – estimated complete at Jul 01 11:06:11
Jul 01 10:51:06 2012 [6-3] recovery of upgrade redo at 62% – estimated complete at Jul 01 11:05:36
Jul 01 10:51:21 2012 [6-3] recovery of upgrade redo at 63% – estimated complete at Jul 01 11:05:06
Jul 01 10:51:36 2012 [6-3] recovery of upgrade redo at 64% – estimated complete at Jul 01 11:05:06
Jul 01 10:51:51 2012 [6-3] recovery of upgrade redo at 65% – estimated complete at Jul 01 11:04:51
Jul 01 10:52:21 2012 [6-3] recovery of upgrade redo at 67% – estimated complete at Jul 01 11:04:37
Jul 01 10:52:52 2012 [6-3] recovery of upgrade redo at 68% – estimated complete at Jul 01 11:04:32
Jul 01 10:53:07 2012 [6-3] recovery of upgrade redo at 69% – estimated complete at Jul 01 11:04:46
Jul 01 10:53:37 2012 [6-3] recovery of upgrade redo at 70% – estimated complete at Jul 01 11:04:32
Jul 01 10:53:52 2012 [6-3] recovery of upgrade redo at 72% – estimated complete at Jul 01 11:03:55
Jul 01 10:54:22 2012 [6-3] recovery of upgrade redo at 74% – estimated complete at Jul 01 11:03:42
Jul 01 10:54:37 2012 [6-3] recovery of upgrade redo at 76% – estimated complete at Jul 01 11:03:02
Jul 01 10:55:08 2012 [6-3] recovery of upgrade redo at 80% – estimated complete at Jul 01 11:01:49
Jul 01 10:55:25 2012 [6-3] recovery of upgrade redo at 81% – estimated complete at Jul 01 11:01:40
Jul 01 10:55:40 2012 [6-3] recovery of upgrade redo at 82% – estimated complete at Jul 01 11:01:32
Jul 01 10:55:55 2012 [6-3] recovery of upgrade redo at 83% – estimated complete at Jul 01 11:01:31
Jul 01 10:56:10 2012 [6-3] recovery of upgrade redo at 85% – estimated complete at Jul 01 11:01:11
Jul 01 10:56:41 2012 [6-3] recovery of upgrade redo at 87% – estimated complete at Jul 01 11:00:46
Jul 01 10:57:11 2012 [6-3] recovery of upgrade redo at 88% – estimated complete at Jul 01 11:01:12
Jul 01 10:57:26 2012 [6-3] recovery of upgrade redo at 89% – estimated complete at Jul 01 11:00:57
Jul 01 10:57:56 2012 [6-3] recovery of upgrade redo at 90% – estimated complete at Jul 01 11:01:13
Jul 01 10:58:11 2012 [6-3] recovery of upgrade redo at 91% – estimated complete at Jul 01 11:01:04
Jul 01 10:58:26 2012 [6-3] recovery of upgrade redo at 92% – estimated complete at Jul 01 11:01:06
Jul 01 10:59:11 2012 [6-3] recovery of upgrade redo at 94% – estimated complete at Jul 01 11:01:13
Jul 01 11:00:12 2012 [6-3] recovery of upgrade redo at 95% – estimated complete at Jul 01 11:01:49
Jul 01 11:00:42 2012 [6-3] recovery of upgrade redo at 96% – estimated complete at Jul 01 11:01:49
Jul 01 11:01:12 2012 [6-3] recovery of upgrade redo at 97% – estimated complete at Jul 01 11:02:10
Jul 01 11:02:42 2012 [6-3] recovery of upgrade redo at 98% – estimated complete at Jul 01 11:03:25
Jul 01 11:04:14 2012 [6-3] recovery of upgrade redo at 99% – estimated complete at Jul 01 11:04:14
Jul 01 11:04:32 2012 [6-4] media recovery has finished recovering through upgrade
### Stage 7: Switch back to the original roles prior to the rolling upgrade
NOTE: At this point, you have the option to perform a switchover
which will restore RACD back to a primary database and
RACDSTB back to a physical standby database. If you answer ‘n’
to the question below, RACD will remain a physical standby
database and RACDSTB will remain a primary database.
Do you want to perform a switchover? (y/n): y
Jul 01 11:10:15 2012 [7-1] continuing
Jul 01 11:10:15 2012 [7-2] verifying instance RACDSTB1 is the only active instance
WARN: RACDSTB is a RAC database. Before this script can continue, you
must manually reduce the RAC to a single instance. This can be
accomplished with the following step:
1) Shutdown all instances other than instance RACDSTB1.
eg: srvctl stop instance -d RACDSTB -i RACDSTB2
Once these steps have been performed, enter ‘y’ to continue the script.
If desired, you may enter ‘n’ to exit the script to perform the required
steps, and recall the script to resume from this point.
Are you ready to continue? (y/n): y
Jul 01 11:11:34 2012 [7-2] continuing
Jul 01 11:11:34 2012 [7-2] verifying instance RACDSTB1 is the only active instance
Jul 01 11:11:36 2012 [7-2] waiting for v$dataguard_stats view to initialize
Jul 01 11:11:37 2012 [7-2] waiting for apply lag on RACD to fall below 30 seconds
Jul 01 11:12:09 2012 [7-2] apply lag is now less than 30 seconds
Jul 01 11:12:10 2012 [7-3] switching RACDSTB to become a physical standby
Jul 01 11:12:23 2012 [7-3] RACDSTB is now a physical standby
Jul 01 11:12:23 2012 [7-3] shutting down database RACDSTB
Jul 01 11:12:24 2012 [7-3] mounting database RACDSTB
Jul 01 11:13:07 2012 [7-4] waiting for standby RACD to process end-of-redo from primary
Jul 01 11:13:10 2012 [7-5] switching RACD to become the new primary
Jul 01 11:13:11 2012 [7-5] RACD is now the new primary
Jul 01 11:13:11 2012 [7-5] opening database RACD
Jul 01 11:13:55 2012 [7-6] starting media recovery on RACDSTB
Jul 01 11:14:21 2012 [7-6] confirming media recovery is running
NOTE: Database RACD has completed the switchover to the primary role, but
instance RACD1 is the only open instance. For increased availability,
Oracle recommends opening the remaining active instances which are
currently in mounted mode by performing the following steps:
1) Shutdown all instances other than instance RACD1.
eg: srvctl stop instance -d RACD -i RACD2
2) Startup and open all inactive instances for database RACD.
eg: srvctl start database -d RACD
NOTE: Database RACDSTB is no longer limited to single instance operation since
it has completed the switchover to the physical standby role. For
increased availability, Oracle recommends starting the inactive
instances in the RAC by performing the following step:
1) Startup and mount inactive instances for database RACDSTB
eg: srvctl start database -d RACDSTB -o mount
### Stage 8: Statistics
script start time: 01-Jul-12 01:22:44
script finish time: 01-Jul-12 11:16:01
total script execution time: +00 09:53:17
wait time for user upgrade: +00 07:04:15
active script execution time: +00 02:49:02
transient logical creation start time: 01-Jul-12 01:23:16
transient logical creation finish time: 01-Jul-12 01:24:58
primary to logical switchover start time: 01-Jul-12 09:53:57
logical to primary switchover finish time: 01-Jul-12 09:55:46
primary services offline for: +00 00:01:49
total time former primary in physical role: +00 01:05:18
time to reach upgrade redo: +00 00:02:06
time to recover upgrade redo: +00 00:35:09
primary to physical switchover start time: 01-Jul-12 11:10:15
physical to primary switchover finish time: 01-Jul-12 11:13:54
primary services offline for: +00 00:03:39
SUCCESS: The physical rolling upgrade is complete
[oracle@raclinux1 dbs]$
Result is as follows:
On the Primary database:
SQL> select * from v$active_instances;
INST_NUMBER
———–
INST_NAME
——————————————————————————————————————————————————————————————————–
1
raclinux1.gj.com:RACD1
2
raclinux2.gj.com:RACD2
SQL> select * from v$instance;
INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT LOGINS SHU
————— —————- —————————————————————- —————– ——— ———— — ———- ——- ————— ———- —
DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
—————– —————— ——— —
1 RACD1 raclinux1.gj.com 11.2.0.3.0 01-JUL-12 OPEN YES 1 STARTED ALLOWED NO
ACTIVE PRIMARY_INSTANCE NORMAL NO
SQL>
On Standby database:
SQL> select name, PROTECTION_MODE, PROTECTION_LEVEL, OPEN_MODE, DATABASE_ROLE from v$database;
NAME PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE DATABASE_ROLE
——— ——————– ——————– ——————– —————-
RACD MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE MOUNTED PHYSICAL STANDBY
SQL> select * from v$active_instances;
INST_NUMBER
———–
INST_NAME
——————————————————————————————————————————————————————————————————–
1
raclinux1.gj.com:RACDSTB1
2
raclinux2.gj.com:RACDSTB2
SQL> select * from v$instance;
INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT LOGINS SHU
————— —————- —————————————————————- —————– ——— ———— — ———- ——- ————— ———- —
DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
—————– —————— ——— —
1 RACDSTB1 raclinux1.gj.com 11.2.0.3.0 01-JUL-12 MOUNTED YES 1 STARTED ALLOWED NO
ACTIVE PRIMARY_INSTANCE NORMAL NO
SQL>
3.6 This conclude the near zero downtime upgrade.
- Recovery in case of errors
The physru script creates a guaranteed restore points (GRP) and copies of the control file for both primary and physical standby database. This is sufficient at any time to flashback the logical standby database to the point when it was a physical standby database. In order to restore the standby database to the original primary database the action items are
- Restore the control file
- Flashback the database to the GRP
- Convert the database to physical standby
- Mount the physical standby and start media recovery to synch with the primary database.
References:
- Database Rolling Upgrade Using Transient Logical Standby: Oracle Data Guard 11g
- Oracle® Data Guard Concepts and Administration 11g Release 2 (11.2) E25608-03
- Database Rolling Upgrades Made Easy by Using a Data Guard Physical Standby Database Oracle Maximum Availability Architecture White Paper October 2011
Appendix:
6. Configure tnsnames.ora alliases
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
RAC =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RAC)
)
)
RACDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RACDB)
)
)
RSTB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RSTB)
)
)
RACD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RACD)
)
)
RACDSTB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = RACDSTB)
)
)
PRACD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = raclinux1-vip)(PORT = 1521))
)
(CONNECT_DATA =
(SID = RACD1)
)
)
SRACD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = raclinux1-vip)(PORT = 1521))
)
(CONNECT_DATA =
(SID = RACDSTB1)
)
)
[oracle@raclinux1 admin]$
7. listener.ora
Node 1
[grid@raclinux1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/11.2.0.3/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1 = ON
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = RACSTB)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_3)
(SID_NAME = RACSTB1)
)
(SID_DESC =
(GLOBAL_DBNAME = RAC10G)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_3)
(SID_NAME = RAC10G1)
)
(SID_DESC =
(GLOBAL_DBNAME = RACSTB_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_3)
(SID_NAME = RACSTB1)
)
(SID_DESC =
(GLOBAL_DBNAME = RAC10G_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_3)
(SID_NAME = RAC10G1)
)
(SID_DESC =
(GLOBAL_DBNAME = RACD)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RACD1)
)
(SID_DESC =
(GLOBAL_DBNAME = RACD_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RACD1)
)
(SID_DESC =
(GLOBAL_DBNAME = RAC)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RAC1)
)
(SID_DESC =
(GLOBAL_DBNAME = RAC_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RAC1)
)
(SID_DESC =
(GLOBAL_DBNAME = RACDSTB)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RACDSTB1)
)
(SID_DESC =
(GLOBAL_DBNAME = RACDSTB_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RACDSTB1)
)
(SID_DESC =
(GLOBAL_DBNAME = RSTB)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RSTB1)
)
(SID_DESC =
(GLOBAL_DBNAME = RSTB_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RSTB1)
)
)
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_HA = ON
SID_LIST_LISTENER_SCAN1 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = RAC10G)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_3)
(SID_NAME = RAC10G)
)
(SID_DESC =
(GLOBAL_DBNAME = RAC10G_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_3)
(SID_NAME = RAC10G)
)
(SID_DESC =
(GLOBAL_DBNAME = RACSTB)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_3)
(SID_NAME = RACSTB)
)
(SID_DESC =
(GLOBAL_DBNAME = RACSTB_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_3)
(SID_NAME = RACSTB)
)
(SID_DESC =
(GLOBAL_DBNAME = RACD)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RACD)
)
(SID_DESC =
(GLOBAL_DBNAME = RACD_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RACD)
)
(SID_DESC =
(GLOBAL_DBNAME = RAC)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RAC)
)
(SID_DESC =
(GLOBAL_DBNAME = RAC_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RAC)
)
(SID_DESC =
(GLOBAL_DBNAME = RACDSTB)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RACDSTB)
)
(SID_DESC =
(GLOBAL_DBNAME = RACDSTB_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RACDSTB)
)
(SID_DESC =
(GLOBAL_DBNAME = RSTB)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RSTB)
)
(SID_DESC =
(GLOBAL_DBNAME = RSTB_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RSTB)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))
)
ADR_BASE_LISTENER = /u01/app/grid
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON
LISTENER_SCAN1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN1))
)
ADR_BASE_LISTENER_SCAN1 = /u01/app/grid
[grid@raclinux1 admin]$
Node 2
[grid@raclinux2 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/11.2.0.3/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1 = ON
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = RAC10G)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_3)
(SID_NAME = RAC10G2)
)
(SID_DESC =
(GLOBAL_DBNAME = RAC10G_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_3)
(SID_NAME = RAC10G2)
)
(SID_DESC =
(GLOBAL_DBNAME = RACSTB)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_3)
(SID_NAME = RACSTB2)
)
(SID_DESC =
(GLOBAL_DBNAME = RACSTB_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_3)
(SID_NAME = RACSTB2)
)
(SID_DESC =
(GLOBAL_DBNAME = RACD)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RACD2)
)
(SID_DESC =
(GLOBAL_DBNAME = RACD_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RACD2)
)
(SID_DESC =
(GLOBAL_DBNAME = RAC)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RAC2)
)
(SID_DESC =
(GLOBAL_DBNAME = RAC_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RAC2)
)
(SID_DESC =
(GLOBAL_DBNAME = RACDSTB)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RACDSTB2)
)
(SID_DESC =
(GLOBAL_DBNAME = RACDSTB_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RACDSTB2)
)
(SID_DESC =
(GLOBAL_DBNAME = RSTB)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RSTB2)
)
(SID_DESC =
(GLOBAL_DBNAME = RSTB_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RSTB2)
)
)
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_HA = ON
SID_LIST_LISTENER_SCAN1 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = RAC10G)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_3)
(SID_NAME = RAC10G)
)
(SID_DESC =
(GLOBAL_DBNAME = RAC10G_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_3)
(SID_NAME = RAC10G)
)
(SID_DESC =
(GLOBAL_DBNAME = RACSTB)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_3)
(SID_NAME = RACSTB)
)
(SID_DESC =
(GLOBAL_DBNAME = RACSTB_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_3)
(SID_NAME = RACSTB)
)
(SID_DESC =
(GLOBAL_DBNAME = RACD)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RACD)
)
(SID_DESC =
(GLOBAL_DBNAME = RACD_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RACD)
)
(SID_DESC =
(GLOBAL_DBNAME = RAC)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RAC)
)
(SID_DESC =
(GLOBAL_DBNAME = RAC_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RAC)
)
(SID_DESC =
(GLOBAL_DBNAME = RACDSTB)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RACDSTB)
)
(SID_DESC =
(GLOBAL_DBNAME = RACDSTB_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RACDSTB)
)
(SID_DESC =
(GLOBAL_DBNAME = RSTB)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RSTB)
)
(SID_DESC =
(GLOBAL_DBNAME = RSTB_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = RSTB)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))
)
ADR_BASE_LISTENER = /u01/app/grid
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON
LISTENER_SCAN1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN1))
)
ADR_BASE_LISTENER_SCAN1 = /u01/app/grid
[grid@raclinux2 admin]$
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
[oracle@raclinux1 dbs]$ rman target sys/sys1@pracd auxiliary sys/sys1@sracd
Recovery Manager: Release 11.2.0.2.0 – Production on Sat Jun 30 23:46:53 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: RACD (DBID=1643830466)
connected to auxiliary database: RACD (not mounted)
RMAN> run {
allocate channel tst type disk;
allocate channel tst1 type disk;
allocate auxiliary channel tststby type disk;
sql ‘alter system archive log current’;
duplicate target database for standby from active database DORECOVER;
}
142> 3> 4> 5> 6> 7>
using target database control file instead of recovery catalog
allocated channel: tst
channel tst: SID=92 instance=RACD1 device type=DISK
allocated channel: tst1
channel tst1: SID=93 instance=RACD1 device type=DISK
allocated channel: tststby
channel tststby: SID=29 device type=DISK
sql statement: alter system archive log current
Starting Duplicate Db at 30-JUN-12
contents of Memory Script:
{
backup as copy reuse
targetfile ‘/u01/app/oracle/product/11.2.0/db_1/dbs/orapwRACD1’ auxiliary format
‘/u01/app/oracle/product/11.2.0/db_1/dbs/orapwRACDSTB1’ ;
}
executing Memory Script
Starting backup at 30-JUN-12
Finished backup at 30-JUN-12
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format ‘+DATADG/racdstb/controlfile/current.929.787362489’;
restore clone controlfile to ‘+DATADG/racdstb/controlfile/current.930.787362489’ from
‘+DATADG/racdstb/controlfile/current.929.787362489’;
sql clone “create spfile from memory”;
shutdown clone immediate;
startup clone nomount;
sql clone “alter system set control_files =
”+DATADG/racdstb/controlfile/current.929.787362489”, ”+DATADG/racdstb/controlfile/current.930.787362489” comment=
”Set by RMAN” scope=spfile”;
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Starting backup at 30-JUN-12
channel tst: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_RACD1.f tag=TAG20120630T234809 RECID=2 STAMP=787362491
channel tst: datafile copy complete, elapsed time: 00:00:07
Finished backup at 30-JUN-12
Starting restore at 30-JUN-12
channel tststby: copied control file copy
Finished restore at 30-JUN-12
sql statement: create spfile from memory
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 521936896 bytes
Fixed Size 2228072 bytes
Variable Size 427819160 bytes
Database Buffers 88080384 bytes
Redo Buffers 3809280 bytes
allocated channel: tststby
channel tststby: SID=30 device type=DISK
sql statement: alter system set control_files = ”+DATADG/racdstb/controlfile/current.929.787362489”, ”+DATADG/racdstb/controlfile/current.930.787362489” comment= ”Set by RMAN” scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 521936896 bytes
Fixed Size 2228072 bytes
Variable Size 427819160 bytes
Database Buffers 88080384 bytes
Redo Buffers 3809280 bytes
allocated channel: tststby
channel tststby: SID=30 device type=DISK
contents of Memory Script:
{
sql clone ‘alter database mount standby database’;
}
executing Memory Script
sql statement: alter database mount standby database
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.
contents of Memory Script:
{
set newname for tempfile 1 to
“+datadg”;
switch clone tempfile all;
set newname for datafile 1 to
“+datadg”;
set newname for datafile 2 to
“+datadg”;
set newname for datafile 3 to
“+datadg”;
set newname for datafile 4 to
“+datadg”;
set newname for datafile 5 to
“+datadg”;
set newname for datafile 6 to
“+datadg”;
backup as copy reuse
datafile 1 auxiliary format
“+datadg” datafile
2 auxiliary format
“+datadg” datafile
3 auxiliary format
“+datadg” datafile
4 auxiliary format
“+datadg” datafile
5 auxiliary format
“+datadg” datafile
6 auxiliary format
“+datadg” ;
sql ‘alter system archive log current’;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +datadg in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 30-JUN-12
channel tst: starting datafile copy
input datafile file number=00001 name=+DATA/racd/datafile/system.1138.787360643
channel tst1: starting datafile copy
input datafile file number=00002 name=+DATA/racd/datafile/sysaux.1093.787360643
output file name=+DATADG/racdstb/datafile/sysaux.932.787362557 tag=TAG20120630T234915
channel tst1: datafile copy complete, elapsed time: 00:02:45
channel tst1: starting datafile copy
input datafile file number=00005 name=+DATA/racd/datafile/example.1145.787360931
output file name=+DATADG/racdstb/datafile/system.931.787362557 tag=TAG20120630T234915
channel tst: datafile copy complete, elapsed time: 00:03:27
channel tst: starting datafile copy
input datafile file number=00003 name=+DATA/racd/datafile/undotbs1.1096.787360645
output file name=+DATADG/racdstb/datafile/undotbs1.919.787362773 tag=TAG20120630T234915
channel tst: datafile copy complete, elapsed time: 00:00:45
channel tst: starting datafile copy
input datafile file number=00006 name=+DATA/racd/datafile/undotbs2.1146.787361501
output file name=+DATADG/racdstb/datafile/example.918.787362743 tag=TAG20120630T234915
channel tst1: datafile copy complete, elapsed time: 00:01:25
channel tst1: starting datafile copy
input datafile file number=00004 name=+DATA/racd/datafile/users.1139.787360645
output file name=+DATADG/racdstb/datafile/undotbs2.949.787362821 tag=TAG20120630T234915
channel tst: datafile copy complete, elapsed time: 00:00:09
output file name=+DATADG/racdstb/datafile/users.950.787362827 tag=TAG20120630T234915
channel tst1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 30-JUN-12
sql statement: alter system archive log current
contents of Memory Script:
{
backup as copy reuse
archivelog like “+DATADG/racd/archivelog/2012_06_30/thread_2_seq_3.928.787361817” auxiliary format
“+DATA” archivelog like
“+DATADG/racd/archivelog/2012_06_30/thread_1_seq_8.916.787362617” auxiliary format
“+DATA” archivelog like
“+DATADG/racd/archivelog/2012_06_30/thread_2_seq_4.917.787361951” auxiliary format
“+DATA” archivelog like
“+DATADG/racd/archivelog/2012_06_30/thread_1_seq_9.957.787362831” auxiliary format
“+DATA” archivelog like
“+DATADG/racd/archivelog/2012_06_30/thread_2_seq_5.958.787362157” auxiliary format
“+DATA” ;
catalog clone recovery area;
switch clone datafile all;
}
executing Memory Script
Starting backup at 30-JUN-12
channel tst: starting archived log copy
input archived log thread=2 sequence=3 RECID=5 STAMP=787361817
channel tst1: starting archived log copy
input archived log thread=1 sequence=8 RECID=6 STAMP=787362634
output file name=+DATA/racdstb/archivelog/2012_06_30/thread_2_seq_3.272.787362835 RECID=0 STAMP=0
channel tst: archived log copy complete, elapsed time: 00:00:02
channel tst: starting archived log copy
input archived log thread=2 sequence=4 RECID=7 STAMP=787361976
output file name=+DATA/racdstb/archivelog/2012_06_30/thread_2_seq_4.1122.787362837 RECID=0 STAMP=0
channel tst: archived log copy complete, elapsed time: 00:00:01
channel tst: starting archived log copy
input archived log thread=1 sequence=9 RECID=8 STAMP=787362831
output file name=+DATA/racdstb/archivelog/2012_06_30/thread_1_seq_8.1091.787362835 RECID=0 STAMP=0
channel tst1: archived log copy complete, elapsed time: 00:00:03
channel tst1: starting archived log copy
input archived log thread=2 sequence=5 RECID=9 STAMP=787362156
output file name=+DATA/racdstb/archivelog/2012_06_30/thread_1_seq_9.1089.787362837 RECID=0 STAMP=0
channel tst: archived log copy complete, elapsed time: 00:00:01
output file name=+DATA/racdstb/archivelog/2012_06_30/thread_2_seq_5.1095.787362837 RECID=0 STAMP=0
channel tst1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 30-JUN-12
searching for all files in the recovery area
List of Files Unknown to the Database
=====================================
File Name: +datadg/RACDSTB/DATAFILE/SYSTEM.931.787362557
File Name: +datadg/RACDSTB/DATAFILE/SYSAUX.932.787362557
File Name: +datadg/RACDSTB/DATAFILE/EXAMPLE.918.787362743
File Name: +datadg/RACDSTB/DATAFILE/UNDOTBS1.919.787362773
File Name: +datadg/RACDSTB/DATAFILE/UNDOTBS2.949.787362821
File Name: +datadg/RACDSTB/DATAFILE/USERS.950.787362827
cataloging files…
cataloging done
List of Cataloged Files
=======================
File Name: +datadg/RACDSTB/DATAFILE/SYSTEM.931.787362557
File Name: +datadg/RACDSTB/DATAFILE/SYSAUX.932.787362557
File Name: +datadg/RACDSTB/DATAFILE/EXAMPLE.918.787362743
File Name: +datadg/RACDSTB/DATAFILE/UNDOTBS1.919.787362773
File Name: +datadg/RACDSTB/DATAFILE/UNDOTBS2.949.787362821
File Name: +datadg/RACDSTB/DATAFILE/USERS.950.787362827
List of files in Recovery Area not managed by the database
==========================================================
File Name: +DATADG/racdstb/controlfile/current.929.787362489
RMAN-07527: Reason: File was not created using DB_RECOVERY_FILE_DEST initialization parameter
number of files not managed by recovery area is 1, totaling 17.63MB
datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=787362840 file name=+DATADG/racdstb/datafile/system.931.787362557
datafile 2 switched to datafile copy
input datafile copy RECID=9 STAMP=787362840 file name=+DATADG/racdstb/datafile/sysaux.932.787362557
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=787362840 file name=+DATADG/racdstb/datafile/undotbs1.919.787362773
datafile 4 switched to datafile copy
input datafile copy RECID=11 STAMP=787362840 file name=+DATADG/racdstb/datafile/users.950.787362827
datafile 5 switched to datafile copy
input datafile copy RECID=12 STAMP=787362840 file name=+DATADG/racdstb/datafile/example.918.787362743
datafile 6 switched to datafile copy
input datafile copy RECID=13 STAMP=787362840 file name=+DATADG/racdstb/datafile/undotbs2.949.787362821
contents of Memory Script:
{
set until scn 1067167;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 30-JUN-12
starting media recovery
unable to find archived log
archived log thread=1 sequence=0
released channel: tst
released channel: tst1
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: ‘+DATADG/racdstb/datafile/system.931.787362557’
released channel: tststby
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 06/30/2012 23:54:04
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 0 and starting SCN of 1066397
RMAN>
RMAN>
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
[root@raclinux1 ~]# cat /tmp/i.ora
RACDSTB1.__db_cache_size=92274688
RACDSTB2.__db_cache_size=100663296
RACDSTB1.__java_pool_size=4194304
RACDSTB2.__java_pool_size=4194304
RACDSTB1.__large_pool_size=4194304
RACDSTB2.__large_pool_size=4194304
RACDSTB1.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
RACDSTB2.__oracle_base=’/u01/app/oracle’#ORACLE_BASE set from environment
RACDSTB2.__pga_aggregate_target=197132288
RACDSTB1.__pga_aggregate_target=209715200
RACDSTB2.__sga_target=327155712
RACDSTB1.__sga_target=314572800
RACDSTB1.__shared_io_pool_size=0
RACDSTB2.__shared_io_pool_size=0
RACDSTB1.__shared_pool_size=205520896
RACDSTB2.__shared_pool_size=209715200
RACDSTB1.__streams_pool_size=0
RACDSTB2.__streams_pool_size=0
*.audit_file_dest=’/u01/app/oracle/admin/RACDSTB/adump’
*.audit_trail=’db’
*.control_files=’+DATADG/racdstb/controlfile/current.929.787362489′,’+DATADG/racdstb/controlfile/current.930.787362489′
*.cluster_database=false
*.compatible=’11.2.0.0.0′
*.db_block_size=8192
*.db_create_file_dest=’+DATADG’
*.db_domain=”
#*.db_file_name_convert=’+DATA/’,’+DATADG/’
*.DB_FILE_NAME_CONVERT=’+DATA/RACD’,’+DATADG/RACDSTB’,’+DATADG/RACD’,’+DATA/RACDSTB’
*.LOG_FILE_NAME_CONVERT=’+DATA/RACD’,’+DATADG/RACDSTB’,’+DATADG/RACD’,’+DATA/RACDSTB’
*.db_name=’RACD’
*.db_unique_name=’RACDSTB’
*.db_recovery_file_dest=’+DATADG’
*.db_recovery_file_dest_size=41976594432
*.diagnostic_dest=’/u01/app/oracle’
*.dispatchers='(PROTOCOL=TCP) (SERVICE=RACDXDB)’
*.fal_client=’RACDSTB’
*.fal_server=’RACD’
RACDSTB1.instance_number=1
RACDSTB2.instance_number=2
*.log_archive_config=’DG_CONFIG=(RACD,RACDSTB)’
*.log_archive_dest_1=’LOCATION=+DATA/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=RACDSTB’
*.log_archive_dest_2=’SERVICE=RACD LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RACD’
*.log_archive_dest_state_1=’ENABLE’
*.log_archive_dest_state_2=’ENABLE’
*.log_archive_max_processes=30
#*.log_file_name_convert=’+DATADG/’,’+DATA/’
*.memory_target=524288000
*.open_cursors=300
*.processes=500
*.remote_listener=’rac-scan:1521′
*.remote_login_passwordfile=’exclusive’
*.sessions=555
*.standby_file_management=’AUTO’
RACDSTB2.thread=2
RACDSTB1.thread=1
RACDSTB1.undo_tablespace=’UNDOTBS1′
RACDSTB2.undo_tablespace=’UNDOTBS2′
[root@raclinux1 ~]#
References:
- Database Rolling Upgrade Using Transient Logical Standby: Oracle Data Guard 11g
- Oracle® Data Guard Concepts and Administration 11g Release 2 (11.2) E25608-03
- Database Rolling Upgrades Made Easy by Using a Data Guard Physical Standby Database Oracle Maximum Availability Architecture White Paper October 2011
2 Comments »
Leave a Reply to PK Cancel reply
-
Archives
- February 2017 (1)
- November 2016 (1)
- October 2016 (1)
- May 2016 (2)
- March 2016 (3)
- December 2014 (2)
- July 2014 (1)
- June 2014 (6)
- May 2014 (5)
- February 2014 (1)
- December 2012 (2)
- November 2012 (8)
-
Categories
-
RSS
Entries RSS
Comments RSS
Hi Guenadi, excellent article. What was the version of GI during this upgrade ?
Was GI also upgraded during this excercise or was it already at the higher version, namely 11.2.0.3 ?
Hi,
I upgraded GI first. After that I performed the database upgrade as described.
Regards,