12cR1 RAC Posts -- 8g : Switchover from RAC Primary to SingleInstance Standby

Continuing this series of posts where I have a SingleInstance/FileSystem Standby database for a RAC/ASM database ...


Checking the status of the configuration :

DGMGRL> show configuration;

Configuration - rac

  Protection Mode: MaxPerformance
  Members:
  rac  - Primary database
    stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 28 seconds ago)

DGMGRL>


SQL> select thread#, max(sequence#)
  2  from v$archived_log
  3  group by thread#
  4  order by 1
  5  /

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1             66
         2             36

SQL> alter system archive log current;

System altered.


RFS[2]: Selected log 8 for thread 2 sequence 38 dbid 2519807290 branch 931825279
Mon Apr 10 23:03:17 2017
Archived Log entry 39 added for thread 2 sequence 37 ID 0x96312536 dest 1:
Mon Apr 10 23:03:18 2017
Media Recovery Waiting for thread 2 sequence 38 (in transit)
Mon Apr 10 23:03:18 2017
Recovery of Online Redo Log: Thread 2 Group 8 Seq 38 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/STBY/onlinelog/group_8.300.937936389
  Mem# 1: /u01/app/oracle/fast_recovery_area/STBY/onlinelog/group_8.306.937936389
RFS[1]: Selected log 6 for thread 1 sequence 68 dbid 2519807290 branch 931825279
Mon Apr 10 23:03:23 2017
Archived Log entry 40 added for thread 1 sequence 67 ID 0x96312536 dest 1:
Mon Apr 10 23:03:23 2017
Media Recovery Waiting for thread 1 sequence 68 (in transit)
Mon Apr 10 23:03:23 2017
Recovery of Online Redo Log: Thread 1 Group 6 Seq 68 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/STBY/onlinelog/group_6.298.937936361
  Mem# 1: /u01/app/oracle/fast_recovery_area/STBY/onlinelog/group_6.304.937936363

The dgmgrl status above is from node1 (collabn1) of the RAC (Primary)database.
The query on v$archived_log is from node2 (collabn2), instance RAC2 of the RAC (Primary) database.
The listing of messages in the alert log are from the Standby database.

Let me add some rows to the data I have in the PDB in the RAC database.

[oracle@collabn1 ~]$ sqlplus hemant/hemant@PDB

SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 10 23:06:31 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Mon Apr 03 2017 22:49:41 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> select count(*) from my_data;

  COUNT(*)
----------
       100

SQL> insert into my_data select rownum+100, to_char(rownum+100)
  2  from dual
  3  connect by level < 201;

200 rows created.

SQL> select count(*) from my_data;

  COUNT(*)
----------
       300

SQL> commit;

Commit complete.

SQL>

Let me try a SWITCHOVER now.

DGMGRL> show configuration;

Configuration - rac

  Protection Mode: MaxPerformance
  Members:
  rac  - Primary database
    stby - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 102 seconds ago)

DGMGRL> switchover to stby;
Performing switchover NOW, please wait...
Operation requires a connection to instance "STBY" on database "stby"
Connecting to instance "STBY"...
Connected as SYSDBA.
New primary database "stby" is opening...
Oracle Clusterware is restarting database "rac" ...
Switchover succeeded, new primary is "stby"
DGMGRL>

Querying on the new "standby" that is RAC :

[oracle@collabn1 ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 10 23:36:12 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> select open_mode, database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
MOUNTED              PHYSICAL STANDBY

SQL> select instance_name, host_name from gv$instance;

INSTANCE_NAME
----------------
HOST_NAME
----------------------------------------------------------------
RAC1
collabn1.racattack

RAC2
collabn2.racattack


SQL>

While, the old Standby :

[oracle@oem132 trace]$ sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 10 23:37:34 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select open_mode, databas_role from v$database;
select open_mode, databas_role from v$database
                  *
ERROR at line 1:
ORA-00904: "DATABAS_ROLE": invalid identifier


SQL> select open_mode,  database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ WRITE           PRIMARY

SQL> select instance_name, host_name from gv$instance;

INSTANCE_NAME
----------------
HOST_NAME
----------------------------------------------------------------
STBY
oem132.racattack


SQL>

So, host "oem132.racattack" now has the Primary database in Read Write mode. The instance name is STBY because I chose that to be the instance name through the testing.  (I could have chosen BOSTON and CHICAGO as is used in some Oracle examples / documentation).

Let me verify my data on STBY :

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size                  2929936 bytes
Variable Size             230689520 bytes
Database Buffers          599785472 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.
SQL> alter pluggable database pdb open;

Pluggable database altered.

SQL> connect hemant/hemant@PDB
Connected.
SQL> select count(*) from my_data;

  COUNT(*)
----------
       300

SQL>

The rows inserted at the RAC Primary are now in the Pluggable Database PDB at STBY.

.
.
.

12cR1 RAC Posts -- 8f : Accessing data in the PDB in the Standby

Apparently, the error :
< br />

alter pluggable database pdb open
*
ERROR at line 1:
ORA-44309: unknown failure
ORA-44777: Pluggable database service cannot be started.

in my previous post was a spurious error.

I am able to access the PDB in the Standby.  Thus :

[oracle@oem132 ~]$ . oraenv
ORACLE_SID = [STBY] ? STBY
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oem132 ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 9 15:57:27 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size                  2929936 bytes
Variable Size             230689520 bytes
Database Buffers          599785472 bytes
Redo Buffers                5455872 bytes
SQL> alter database mount standby database;

Database altered.

SQL> rem  here I notice the alert log showing :
SQL> rem ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT  NODELAY
SQL> rem Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT  NODELAY
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> rem I cancel the automatic recovery as I do not want to run Active Data Guard
SQL> alter database open read only;

Database altered.

SQL> alter pluggable database pdb open;
alter pluggable database pdb open
*
ERROR at line 1:
ORA-44309: unknown failure
ORA-44777: Pluggable database service cannot be started.


SQL>
SQL> select con_id, name, open_mode, open_Time from v$pdbs;

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
OPEN_TIME
---------------------------------------------------------------------------
         2 PDB$SEED                       READ ONLY
09-APR-17 04.00.48.232 PM +08:00

         3 PDB                            READ ONLY
09-APR-17 04.00.59.105 PM +08:00


SQL>
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@oem132 ~]$ sqlplus hemant/hemant@PDB

SQL*Plus: Release 12.1.0.2.0 Production on Sun Apr 9 16:02:02 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Mon Apr 03 2017 22:49:41 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------
MY_DATA

SQL> select count(*) from my_data;

  COUNT(*)
----------
       100

SQL> show con_id;

CON_ID
------------------------------
3
SQL>

Thus, the PDB on the Standby *does* OPEN READ ONLY and I can see the data that had been populated from the Primary.
.
.
.

12cR1 RAC Posts -- 8e : Redo Shipping and Apply (RAC to nonRAC)

Continuing the series of posts on the RAC/ASM to SingleInstance/FileSystem  DataGuard configuration ...

With both instances RAC1 and RAC2 running, I have redo shipping from both instances to the single instance STBY.
(In my test server with 3 VMs, it takes a number of minutes to have all 3 instances running and communicating with each other ... so there are spurious ORA and TNS errors until the 3 instances have stabilized. {in fact, it would be best to start the Standby before starting the RAC Primary instances}. I am posting messages after the stabilization).

Thus, on RAC 1 :

Mon Apr 03 22:43:51 2017
Archived Log entry 97 added for thread 1 sequence 59 ID 0x96312536 dest 1:
Mon Apr 03 22:43:52 2017
TT00: Standby redo logfile selected for thread 1 sequence 60 for destination LOG_ARCHIVE_DEST_2

And on RAC 2:

Mon Apr 03 22:44:12 2017
Thread 2 cannot allocate new log, sequence 32
Checkpoint not complete
  Current log# 3 seq# 31 mem# 0: +DATA/RAC/ONLINELOG/group_3.290.931826413
  Current log# 3 seq# 31 mem# 1: +FRA/RAC/ONLINELOG/group_3.259.931826417
Mon Apr 03 22:44:19 2017
Thread 2 advanced to log sequence 32 (LGWR switch)
  Current log# 4 seq# 32 mem# 0: +DATA/RAC/ONLINELOG/group_4.291.931826417
  Current log# 4 seq# 32 mem# 1: +FRA/RAC/ONLINELOG/group_4.260.931826421
Mon Apr 03 22:44:20 2017
Archived Log entry 99 added for thread 2 sequence 31 ID 0x96312536 dest 1:
Mon Apr 03 22:44:21 2017
TT00: Standby redo logfile selected for thread 2 sequence 32 for destination LOG_ARCHIVE_DEST_2

And on STBY :

Mon Apr 03 22:43:13 2017
Media Recovery Waiting for thread 2 sequence 31 (in transit)
RFS[2]: Selected log 5 for thread 1 sequence 60 dbid 2519807290 branch 931825279
Mon Apr 03 22:43:52 2017
Archived Log entry 25 added for thread 1 sequence 59 ID 0x96312536 dest 1:
Mon Apr 03 22:44:20 2017
Archived Log entry 26 added for thread 2 sequence 31 rlc 931825279 ID 0x96312536 dest 2:
Mon Apr 03 22:44:20 2017
Media Recovery Log /u01/app/oracle/fast_recovery_area/STBY/archivelog/2017_04_03/o1_mf_2_31_dg4qr2qw_.arc
RFS[1]: Selected log 7 for thread 2 sequence 32 dbid 2519807290 branch 931825279
Mon Apr 03 22:44:21 2017
Media Recovery Log /u01/app/oracle/fast_recovery_area/STBY/archivelog/2017_04_03/o1_mf_1_59_dg4qx70d_.arc
Resize operation completed for file# 3, old size 768000K, new size 778240K
Media Recovery Waiting for thread 1 sequence 60 (in transit)
Mon Apr 03 22:44:24 2017
Recovery of Online Redo Log: Thread 1 Group 5 Seq 60 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/STBY/onlinelog/group_5.292.937936339
  Mem# 1: /u01/app/oracle/fast_recovery_area/STBY/onlinelog/group_5.303.937936343

Thus, Thread1 (instance RAC1) is currently at Sequence#60, Thread2 (instanc RAC2) is currently at Sequence=32.  The STBY alert log shows that it is receiving Redo for both Threads.

Let me login to the PDB in the RAC database and create some data.

[oracle@collabn1 ~]$ tnsping PDB

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 03-APR-2017 22:49:34

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = collabn-cluster-scan.racattack)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = PDB.racattack)))
OK (0 msec)
[oracle@collabn1 ~]$ sqlplus hemant/hemant@PDB

SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 3 22:49:39 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Mon Apr 03 2017 22:48:56 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> show con_id

CON_ID
------------------------------
3
SQL> create table my_data (id_col number, data_col varchar2(15));

Table created.

SQL> insert into my_data select rownum, 'Row:' || to_char(rownum)
  2  from dual connect by level < 101;

100 rows created.

SQL> commit;

Commit complete.

SQL>

So, user HEMANT in the Pluggable Database PDB has a table with 100 rows.

I open the Standby Database Read Only.

[oracle@oem132 ~]$ . oraenv
ORACLE_SID = [oracle] ? STBY
The Oracle base has been set to /u01/app/oracle
[oracle@oem132 ~]$ sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 3 22:53:05 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL> alter pluggable database pdb open;
alter pluggable database pdb open
*
ERROR at line 1:
ORA-44309: unknown failure
ORA-44777: Pluggable database service cannot be started.


SQL> alter pluggable database pdb open;
alter pluggable database pdb open
*
ERROR at line 1:
ORA-65019: pluggable database PDB already open

SQL> alter pluggable database pdb close;

Pluggable database altered.

So, there is an error opening the PDB in the Standby database.  I can't find a note about it on MoS.  This will need more research. Let my try with a Common User in CDB$ROOT.
UPDATE : This PDB Error (numbers 44309 and 44777) is spurious and misleading.  The PDB does OPEN READ ONLY.  See my next post.

Restart the Standby.

SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@oem132 ~]$ sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 3 23:14:54 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter user-name: / as sysdba
Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  838860800 bytes
Fixed Size                  2929936 bytes
Variable Size             230689520 bytes
Database Buffers          599785472 bytes
Redo Buffers                5455872 bytes
SQL> alter database mount standby database;

Database altered.

SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.
SQL>

Create a Common User with data in the RAC Primary.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
[oracle@collabn1 ~]$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 3 23:17:00 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> create user C##HKCCOMMON identified by hemant;

User created.

SQL> grant connect, resource, dba to c##HKCCOMMON;

Grant succeeded.

SQL> connect C##HKCCOMMON/hemant
Connected.
SQL> create table root_my_table (id_col number, data_col varchar2(15));

Table created.

SQL> insert into root_my_table select rownum, 'AA' || to_char(rownum)
  2  from dual  connect by level < 101;

100 rows created.

SQL> commit;

Commit complete.

SQL>

Now, open the Standby Read Only and verify the ROOT_MY_TABLE.

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> alter database open read only;

Database altered.

SQL> connect c##HKCCOMMON/hemant
Connected.
SQL> select count(*) from root_my_table;

  COUNT(*)
----------
       100

SQL>

Yes, the CDB ROOT user and data have gone over to the Standby !

.
.