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 !

.
.