Upgrade an Oracle 12c database using transient logical standby

Upgrading an Oracle 12c database using transient logical standby database.

An Oracle Data Guard standby database keeps itself synchronized with the primary using the redo logs received from it. It can take over the primary’s role when the usual primary goes down or needs to be down for maintenance. How does one upgrade to a higher version in such a HA configuration ?

Upgrading using the transient logical standby.

Reference : http://www.oracle.com/au/products/database/maa-wp-11g-transientlogicalrollingu-1-131927.pdf

Let us apply this technique to and upgrade an Oracle 12c database.

Consider the case of an Oracle 12.1.0.1 database being upgraded to Oracle 12.1.0.2. To start with we have an Oracle container database named DB1, which is in a data guard configuration that uses a physical standby database. The database version is 12.1.0.1 and it is using asynchronous redo transport.

SQL> select * from v$dataguard_config;

DB_UNIQUE_NAME  PARENT_DBUN          DEST_ROLE         CURRENT_SCN     CON_ID

--------------- -------------------- ----------------- ----------- ----------

DB1PR           NONE                 PRIMARY DATABASE       411484          0

DB1SB           DB1PR                PHYSICAL STANDBY       390255          0

1. First a guaranteed restore point is created on primary. This restore point will be needed later on, when the primary site gets upgraded.

SQL> create restore point before_upgrade guarantee flashback database;

Restore point created.

2. Stop managed recovery on the standby.

SQL> recover managed standby database cancel;

Media recovery complete.

SQL>

3. Start a log miner build on primary (Redo transport remains on)

SQL> execute dbms_logstdby.build();

PL/SQL procedure successfully completed.

SQL>

4. Convert the standby database to a logical standby.

SQL> alter database recover to logical standby keep identity;

Database altered.

5. Start SQL apply on the new logical standby

6. Wait for log miner build redo to be applied on primary (until v$logstdby_state.state

should becomes IDLE).

SQL> select session_id, state from v$logstdby_state;

SESSION_ID STATE

---------- ----------------------------------------------------------------

         1 IDLE

7. Defer redo transport to the standby site from  the primary

SQL> alter system set log_archive_dest_state_2='DEFER';

System altered.

8. On the standby stop the SQL Apply process.

SQL> alter database stop logical standby apply;

Database altered.

9. Shut down the logical standby database

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

10. On the standby site, set the ORACLE_HOME and PATH to point to the new Oracle home software. Its assumed here the the instance being upgraded is registered (for instance, in /etc/oratab) under the old Oracle home.

11. On the standby site, Start up the dbua from the new Oracle home.

12.  Select the Upgrade Oracle Database option and click next on the OUI screen that comes up.

13. The list of databases registered are shown, per Oracle home (values are read from the /etc/oraortab file). Pick the DB1SB database and click next.

14.  Set the recompilation and audit file destination options as desired in the GUI.  The upgrade process may take 1-2 hours for the upgrade to complete.

  

15.  Start log transport on primary.

SQL> alter system set log_archive_dest_state_2='ENABLE'; 

System altered.

16. Start SQL apply on the upgraded standby, wait for it to get synchronized with primary.

SQL> alter database start logical standby apply immediate;

Database altered.

17.  Query v$database.current_scn and verify that standby apply process has applied logs past that scn.

Primary:

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

     440984

Standby:

SQL> select applied_scn, latest_scn, mining_scn from v$logstdby_progress;

APPLIED_SCN LATEST_SCN MINING_SCN

----------- ---------- ----------

     441019     441024     441020

SQL> /

18.  At this point, the standby has caught up to the primary (It falls behind when it was being upgraded because redo transport and sql apply was off)

19.  Perform a switchover so that the upgraded standby can become the primary.

Checking status on primary:

SQL> select database_role, switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS

---------------- --------------------

PRIMARY          TO STANDBY

Initiating the switchover from primary:

SQL> alter database commit to switchover to logical standby;

Database altered.

Completing the switchover on standby:

Checking its status:

SQL> select database_role, switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS

---------------- --------------------

LOGICAL STANDBY  TO PRIMARY

Completing the switchover:

SQL> alter database start logical standby apply immediate;

Database altered.

         

20.  The original primary (DB1PR) is now a logical standby and running on older version. Flash the primary back to the starting point of the upgrade operation and shut it down.

SQL> shut immediate;

startup mount;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

ORACLE instance started.

Total System Global Area  835104768 bytes

Fixed Size                  2293880 bytes

Variable Size             230690696 bytes

Database Buffers          595591168 bytes

Redo Buffers                6529024 bytes

Database mounted.

SQL>

SQL> flashback database to restore point before_upgrade;

Flashback complete.

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL>

21. The next step is to mount the original primary (DB1PR) up from the new oracle home. Set ORACLE_HOME and PATH accordingly first.

SQL> startup mount

ORACLE instance started.

Total System Global Area  838860800 bytes

Fixed Size                  2929936 bytes

Variable Size             234883824 bytes

Database Buffers          591396864 bytes

Redo Buffers                9650176 bytes

Database mounted.

22.  Convert the original primary (DB1PR) to a physical standby

SQL> alter database convert to physical standby;

Database altered.

SQL>

23.  Shut down, mount and start Managed Recovery process on the original primary (DB1PR). It will get upgraded when it applies the redo from the former standby (DB1SB)

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

startup mount

ORACLE instance shut down.

SQL> ORACLE instance started.

Total System Global Area  838860800 bytes

Fixed Size                  2929936 bytes

Variable Size             234883824 bytes

Database Buffers          591396864 bytes

Redo Buffers                9650176 bytes

Database mounted.

SQL> recover managed standby database disconnect;

Media recovery complete.

SQL>

Checking archive log sequence on former standby (DB1SB)

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     40

Next log sequence to archive   41

Current log sequence           41

    

24.  Monitor the progress of the catch-up by checking the alert logs on the former primary (DB1PR)

Media Recovery Log /u01/app/oracle/oradata/arc3/1_37_894717897.dbf

Mon Nov 02 15:52:45 2015

Media Recovery Log /u01/app/oracle/oradata/arc3/1_38_894717897.dbf

Mon Nov 02 15:52:47 2015

Media Recovery Log /u01/app/oracle/oradata/arc3/1_39_894717897.dbf

Mon Nov 02 15:52:47 2015

Media Recovery Log /u01/app/oracle/oradata/arc3/1_40_894717897.dbf

Resize operation completed for file# 2, old size 266816K, new size 267840K

Media Recovery Waiting for thread 1 sequence 41 (in transit)

Mon Nov 02 15:52:48 2015

Recovery of Online Redo Log: Thread 1 Group 4 Seq 41 Reading mem 0

  Mem# 0: /u01/app/oracle/oradata/db1pr/sby2.ora

25.  After the standby is caught up, it has been fully upgraded. Next steps is to drop the restore point created to clean up the flashback logs.

SQL> drop restore point before_upgrade;

Restore point dropped.

26.  Optionally you can execute another switchover to restore the site roles or plan to upgrade the COMPATIBLE parameter.

Configuring an Oracle Database 12c standby database for tuning it with statspack

Oracle Database 11g introduced Oracle Active Data Guard. With Active Data Guard, the physical standby database can also be opened for read-only access.

Such a standby, which has been opened in Active Data Guard mode keeps itself continuously updated with changes from the primary -  and at the same time - it can support read-only uses like generation of reports.

With Active Data Guard, the standby, which was in the nomount mode is now able to support reporting needs. It helps makes fuller use of the standby server’s computing resources while also relieving the primary reporting workload. All this, while still preserving the data-protection objectives of Data Guard.

 

When you have a reporting application workload that issues SQL against the standby database, it will have some response time and throughput expectations, which raises the question of tuning the workload.

The standby database is doing media recovery. It is read-only and does not collect AWR snapshots for the workload running against it. Therefore, the AWR based tuning approach is not available for a standby database.

Oracle provides a statspack based mechanism for tuning such databases.

Let us explore this technique.

First you have to install the regular statspack related schema.

Then, after ensuring the standby is in open and in read only mode, configure the STDBYPERF schema, which will store the snapshot data from standby.

The primary will be the repository of the snapshots created on the standby, and it will pull the snapshot information from the standby through a database link that gets created as a part of the statspack configuration set up. Also, because its statspack, the snapshot creation is manual.

References:

http://support.oracle.com/epmos/faces/DocumentDisplay?id=454848.1

Pre-requisite :

Standby database must be open READ-ONLY during this process.

First task, is to run spcreate.sql --- create the ‘normal’ statspack schema.

Connect to the database as SYS and run $ORACLE_HOME/rdbms/admin/perfstat.sql.

That script will:

1. Create the PERFSTAT user
2. Create the supporting tables – named STATS$%.
3. Install the STATSPACK package

But when run against a container database, this script has a bug. Due to the bug, the script attempts to create a common user without the C## prefix, resulting in this error.

ORA-65096: invalid common user or role name

After googling, I could find a workaround for that one, which leads to successful creation of the PERFSTAT user as a common user – without the C## prefix – by setting an _oracle_script=true.

I found the workaround here. 

https://jonathanlewis.wordpress.com/2013/07/04/12c-statspack-hack/

When executed finally, the script asked for a password, tablespace, and a temp tablespace for the perfstat schema.

I accepted the defaults (password=oracle, tablespace=SYSAUX, temp tablespace=TEMPCDB)

The next step is to run sbcreate.sql. This script will create the user named STDBYPERF, which will be the actual repository for the standby snapshots, but reside in the primary.

About sbcreate.sql

Like spcreate, this script also needs a ‘tweak’ to enable the creation of the STDBYPERF user without the C## prefix. This script is similar to spcreate.

1. It creates the STDBYPERF schema  and its tables.
2. It will install the statspack package – standby version.
3. Additonally, it will execute sbaddins.sql, which will add the standby instance to the statspack being configured on the primary.

About sbaddins.sql

This script’s job is to add a standby instance to the Statspack configuration. A Statspack configuration initially has 0 standby databases. It should be run as the STDBYPERF user.

It will create a database link which connects to the standby. Performance data from the standby will be fetched across this link and saved in the primary.

The name of the database link is stdby_link_xxxxxx where xxxxx=tns alias of standby.

Having added the standby database to the configuration with sbaddins, we can take snapshots.

To take a snapshot run this at SQL AS the STBYPERF user.

SQL > EXECUTE STATSPACK_<DB_UNIQUE_NAME>_<INSTANCE_NAME>.snap

That’s because the STDBYPERF statspack packge is named in that manner. You need at least 2 snapshots to generate a statspack report.

 

Please see below:

SQL> execute statspack_cdb1_dr_cdb1_dr.snap;

BEGIN statspack_cdb1_dr_cdb1_dr.snap; END;

*

ERROR at line 1:

ORA-28113: policy predicate has error

ORA-02063: preceding line from STDBY_LINK_CDB1_DR

ORA-06512: at "STDBYPERF.STATSPACK_CDB1_DR_CDB1_DR", line 3562

ORA-06512: at "STDBYPERF.STATSPACK_CDB1_DR_CDB1_DR", line 5412

ORA-06512: at "STDBYPERF.STATSPACK_CDB1_DR_CDB1_DR", line 101

ORA-06512: at line 1

There was an error when executing the snapshot extraction function for the 2^nd snapshot:

Note that this error is being reported in the primary from which you connected and issued the snapshot request. However, the problem actually was encountered in the standby – which is where the the snapshot-taking will occur.

You will not find the trace files relating to this error on the primary. I was at a puzzled, until I poked into the statspack package source and realized that is doing snapshot activity against the standby.

Then, I examined the standby trace file CDB1_DR_ora_5525.trc, and sure enough, found the error:

Error information for ORA-28113:

Logon user     : PERFSTAT

Table/View     : SYS.V_$RULE_SET

VPD Policy name    : CON_ID

Policy function: SYS.CON_ID

RLS view  :

SELECT  "OWNER","NAME","CPU_TIME","ELAPSED_TIME","FIRST_LOAD_TIME","LAST_LOAD_TIME","LAST_LOADING_TIME","SHARABLE_MEM","RELOADS","INVALIDATIONS","EVALUATIONS","FIRST_HIT_EVALUATIONS","SIMPLE_RULES_ONLY_EVALUATIONS","SQL_FREE_EVALUATIONS","SQL_EXECUTIONS","CONDITIONS_PROCESSED","TRUE_RULES","MAYBE_RULES","VARIABLE_VALUE_FUNCTION_CALLS","VARIABLE_METHOD_FUNCTION_CALLS","EVALUATION_FUNCTION_CALLS","RESULT_CACHE_HITS","IS_RESULT_CACHE","RESULT_CACHE_ELEMENTS","CON_ID" FROM "SYS"."V_$RULE_SET"   "V_$RULE_SET" WHERE (con_id IN (0, 1) )

ORA-00604: error occurred at recursive SQL level 3

ORA-01000: maximum open cursors exceeded

-------------------------------------------------------------

*** 2015-03-13 18:24:35.058

Then I checked the open_cursors, it was very low:

SQL> show parameter open_cursors

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

open_cursors                         integer     50

After fixing it, the snapshots got captured. The snapshots are shipped to the primary and stored there via a dblink created for this purpose. The dblink is created by sbaddins.sql script

SQL> select db_link, username,host,created from user_db_links;

DB_LINK                             USERNAME             HOST            CREATED

----------------------------------- -------------------- --------------- -----------

STDBY_LINK_CDB1_DR                  PERFSTAT             CDB1_DR         13-Mar-15 5

 Having created the snapshots, you can generate a statspack report.

 Below is a sample invocation

SQL> @?/rdbms/admin/sbreport.sql;

Instances in this Statspack schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Unique Name                 Instance Name

------------------------------ ----------------

CDB1_DR                        CDB1_DR

Enter the DATABASE UNIQUE NAME of the standby database to report

Enter value for db_unique_name: CDB1_DR

You entered: CDB1_DR

Enter the INSTANCE NAME of the standby database instance to report

Enter value for inst_name: CDB1_DR

You entered: CDB1_DR

Specify the number of days of snapshots to choose from

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Entering the number of days (n) will result in the most recent

(n) days of snapshots being listed.  Pressing <return> without

specifying a number lists all completed snapshots.

Listing all Completed Snapshots

                                          Snap

Instance       Snap Id   Snap Started    Level Comment

------------ --------- ----------------- ----- --------------------

CDB1_DR              1 13 Mar 2015 17:21     5

                     8 13 Mar 2015 18:23     5

                    12 13 Mar 2015 19:04     5

                    13 13 Mar 2015 19:04     5

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 12

Begin Snapshot Id specified: 12

Enter value for end_snap: 13

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is sb_CDB1_DR_CDB1_DR_12_13.  To use this name,

press <return> to continue, otherwise enter an alternative.

Enter value for report_name:

Below are the partial contents statspack report… quite similar to awr.

STATSPACK Statistics Report for Physical Standby

Database

~~~~~~~~

DB Unique Name                 Instance     Startup Time    Release     RAC

------------------------------ ------------ --------------- ----------- ---

CDB1_DR                        CDB1_DR      13-Mar-15 14:22 12.1.0.2.0  NO

Host  Name:   labomsb.example. Num CPUs:    2        Phys Memory (MB):    1,955

~~~~

Snapshot       Snap Id     Snap Time      Sessions Curs/Sess Comment

~~~~~~~~    ---------- ------------------ -------- --------- -------------------

Begin Snap:         12 13-Mar-15 19:04:33       32        .8

  End Snap:         13 13-Mar-15 19:04:47       32       1.7

   Elapsed:                0.23 (mins)

Cache Sizes            Begin        End

~~~~~~~~~~~       ---------- ----------

    Buffer Cache:       564M              Std Block Size:         8K

     Shared Pool:       216M                  Log Buffer:     5,064K

Load Profile                   Total         Per Second

~~~~~~~~~~~~       ------------------  -----------------

      DB time(s):                6.7                0.5

       DB CPU(s):                1.3                0.1

 Redo MB applied:                1.3                0.1

   Logical reads:            4,714.0              336.7

  Physical reads:              264.0               18.9

 Physical writes:              254.0               18.1

      User calls:              167.0               11.9

          Parses:              750.0               53.6

     Hard parses:              145.0               10.4

W/A MB processed:               13.4                1.0

          Logons:                0.0                0.0

        Executes:            1,958.0              139.9

       Rollbacks:                0.0                0.0

Instance Efficiency Indicators

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

            Buffer Nowait %:  100.00       Redo NoWait %:

            Buffer  Hit   %:   96.41  Optimal W/A Exec %:  100.00

            Library Hit   %:   78.20        Soft Parse %:   80.67

         Execute to Parse %:   61.70         Latch Hit %:  100.00

Parse CPU to Parse Elapsd %:   84.62     % Non-Parse CPU:   51.47

 Shared Pool Statistics        Begin   End

                               ------  ------

             Memory Usage %:   77.72   85.74

 

Thanks for reading.