Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA BLOG

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.

  1. 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
  1. Identifies software version on primary and standby
  2. Verifies flashback database is enabled on both primary and standby
  3. Verifies available restore points
  4. Verifies that DG broker is disabled.
  5. Stops media recovery on RACDSTB
  6. Creates restore point PRU_0000_0001 on both databases
  7. Creates a backup of the control files on both databases
  8. Starts media recovery, synchronize the RACSTB and shutdown media recovery
  9. Builds dbms_logstdby.build on database RACD
  10. Convert the physical standby into transient logical standby
  11. Starts logical standby on database RACDSTB
  12. waits until logminer dictionary has fully loaded
  13. Waits for apply lag on RACDSTB to fall below 30 seconds
  14. Database RACDSTB is now ready to be upgraded. This script has left the
  15. database RACDSTB is open in case you want to perform any further tasks before upgrading the database.
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
  1. Verify the present RDBMS version on both databases, present GRP, DG broker disabled, flashback database enabled etc.
  2. Waits for RACDSTB to catch up
  3. Starts logical standby on database RACDSTB
  4. Waits for v$dataguard_stats view to initialize and waits for apply lag on RACDSTB to fall below 30 seconds
  5. Switches RACD to become a logical standby
  6. DACD is now a logical standby
  7. Waits for standby RACDSTB to process end-of-redo from primary
  8. Switches RACDSTB to become the new primary
  9. RACDSTB is now the new primary
  10. RACD is a RAC database. Before this script can continue, you must manually reduce the RAC to a single instance.
  11. Verifies instance RACD1 is the only active instance
  12. Shuts down database RACD
  13. Mounts database RACD
  14. Flashes back database RACD to restore point PRU_0000_0001
  15. Converts RACD into physical standby
  16. Shuts down database RACD
  17. 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.
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
  1. Verifies DB versions, flashback, Flashback restore points, disabled DG broker, prior running history etc.
  2. Starts media recovery on RACD
  3. Switchover so that RACD become a primary and RACDSTB become a primary
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.

  1. 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.

  1. Enable force logging on the primary database RACD by running

    SQL> alter database force logging;

  2. Make sure that the source primary database is running in archive log mode
  3. 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]$

  1. 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>

  2. 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>

  3. 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)

    )

    )

  1. 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)

    )

    )

  2. 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=’*’;

  3. 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’

  4. 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]$

  5. 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>

  6. 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>

  7. 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;

    }

  8. Identify the control file and include them in the text parameter file.
  9. 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’;

  10. 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

  11. 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>

  12. 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>

  13. 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.

  14. Test another switchover to get back to the RACD primary and RACDSTB physical standby and verify that redo is applied on the standby database.
  1. 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.

  1. 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.

  1. 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:

  1. Database Rolling Upgrade Using Transient Logical Standby: Oracle Data Guard 11g
  2. Oracle® Data Guard Concepts and Administration 11g Release 2 (11.2) E25608-03
  3. 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:

  1. Database Rolling Upgrade Using Transient Logical Standby: Oracle Data Guard 11g
  2. Oracle® Data Guard Concepts and Administration 11g Release 2 (11.2) E25608-03
  3. Database Rolling Upgrades Made Easy by Using a Data Guard Physical Standby Database Oracle Maximum Availability Architecture White Paper October 2011

July 3, 2012 - Posted by | oracle

2 Comments »

  1. 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 ?

    Comment by PK | November 17, 2012 | Reply

    • Hi,

      I upgraded GI first. After that I performed the database upgrade as described.

      Regards,

      Comment by gjilevski | November 17, 2012 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 772 other followers

%d bloggers like this: