Data Guard and Oracle Restart, aka relink always !!!

During my early years with PC I love to play "Leisure Suit Larry" (especially part 1 to 3 which has running on CGA or Hercules graphics card). Author of this games, Al Lowe had a good sense of humor and every time you failed your mission or stuck in a place - there was an pop up on the screen "Al says: save early, save often". After today fight with Oracle I should remember a next saying - "Relink after, relink always".

Let me explain what what happen today.

There used to be old Oracle bug in 11.2g which I described here. Basically Oracle Restart was not aware of role changes and could open a database in wrong mode after role switch. I created a workaround for it but I was really waiting for a patch. During a UKOUG 2014 I was speaking with Oracle Data Guard Product Manager - Larry Carpenter - and I learned that patch is ready. I asked @UweHesse for details and I got my numbers back - patch 15986647 and it is included in latest PSU 11.2.0.4.4.

After I got this information I was happy - I just applied this PSU last week, so let's test it.

The first run looked OK - after switchover Oracle Restart displayed proper role and start-up option on new primary

agent/ohasd/oraagent_grid/oraagent_grid.log
2014-12-22 09:02:36.511: [    AGFW][2057]{0:0:44372} Agent received the message: RESOURCE_MODIFY_ATTR[ora.apppre11.db 1 1] ID 4355:617901
2014-12-22 09:02:36.513: [ora.apppre11.db][9207]{0:0:44372} [res_attr_modified] clsn_agent::modify {
2014-12-22 09:02:36.513: [ora.apppre11.db][9207]{0:0:44372} [res_attr_modified] clsn_agent::modify }
2014-12-22 09:02:36.513: [    AGFW][9207]{0:0:44372} Command: res_attr_modified for resource: ora.apppre11.db 1 1 completed with status: SUCCESS
2014-12-22 09:02:36.516: [    AGFW][2057]{0:0:44372} Attribute: ROLE for resource ora.apppre11.db modified to: PRIMARY
2014-12-22 09:02:36.516: [    AGFW][2057]{0:0:44372} Attribute: USR_ORA_OPEN_MODE for resource ora.apppre11.db modified to: open
2014-12-22 09:02:36.516: [    AGFW][2057]{0:0:44372}  config version updated to : 69 for ora.apppre11.db 1 1
2014-12-22 09:02:36.516: [    AGFW][2057]{0:0:44372} Agent sending last reply for: RESOURCE_MODIFY_ATTR[ora.apppre11.db 1 1] ID 4355:617901
2014-12-22 09:02:36.663: [    AGFW][2057]{0:0:44374} Agent received the message: RESOURCE_MODIFY_ATTR[ora.apppre11.apppre_adf.svc 1 1] ID 4355:617914

but only proper role on the new standby.

agent/ohasd/oraagent_grid/oraagent_grid.log

2014-12-22 09:03:10.420: [    AGFW][2057]{0:0:11369} Agent received the message: RESOURCE_MODIFY_ATTR[ora.apppre21.db 1 1] ID 4355:189787
2014-12-22 09:03:10.423: [ora.apppre21.db][2412]{0:0:11369} [res_attr_modified] clsn_agent::modify {
2014-12-22 09:03:10.424: [ora.apppre21.db][2412]{0:0:11369} [res_attr_modified] clsn_agent::modify }
2014-12-22 09:03:10.424: [    AGFW][2412]{0:0:11369} Command: res_attr_modified for resource: ora.apppre21.db 1 1 completed with status: SUCCESS
2014-12-22 09:03:10.424: [    AGFW][2057]{0:0:11369} Attribute: ROLE for resource ora.apppre21.db modified to: PHYSICAL_STANDBY
2014-12-22 09:03:10.424: [    AGFW][2057]{0:0:11369}  config version updated to : 142 for ora.apppre21.db 1 1
2014-12-22 09:03:10.424: [    AGFW][2057]{0:0:11369} Agent sending last reply for: RESOURCE_MODIFY_ATTR[ora.apppre21.db 1 1] ID 4355:189787

Start up option was still open instead of mount. Hmmm, I was sure that both nodes are same but again I reconfigure Data Guard Broker and Oracle Restart - no change - issue was resolved on the one server only.  Then I decided to double check if I have this PSU installed, and I found out that everything was in order in terms of PSU.

-bash-3.2$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc
OPatch version    : 11.2.0.3.6
OUI version       : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2014-12-22_15-08-59PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2014-12-22_15-08-59PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.4.0
There are 1 product(s) installed in this Oracle Home.


Interim patches (3) :

Patch  18235390     : applied on Mon Dec 22 14:54:02 GMT 2014
Unique Patch ID:  18349043
   Created on 4 Dec 2014, 18:50:09 hrs PST8PDT
   Bugs fixed:
     18235390

Patch  19121549     : applied on Mon Nov 17 14:06:54 GMT 2014
Unique Patch ID:  18060349
Patch description:  "OCW Patch Set Update : 11.2.0.4.4 (19121549)"
   Created on 7 Oct 2014, 03:38:04 hrs PST8PDT
   Bugs fixed:
     18328800, 18691572, 14525998, 18187697, 18348155, 17516024, 17387214
     17750548, 17617807, 17551223, 14671408, 14207615, 18272135, 18180541
     17292250, 17378618, 17500165, 18875012, 18464784, 17065496, 18848125
     13991403, 17955615, 14693336, 17273020, 17238586, 17089344, 17405605
     17531342, 17155238, 17159489, 18053580, 16543190, 17039197, 16317771
     17947785, 10052729, 16281493, 18346135, 17481314, 18199185, 18399991
     18024089, 18428146, 18352845, 18352846, 17391726, 18414137, 17001914
     17927970, 14378120, 16346413, 17305100, 15832129, 15986647, 16901346
     18068871, 17985714, 18536826, 16206997, 18752378, 16876500, 16429265
     18343490, 18336452, 16613232, 17273003, 19276791, 12928658, 18226143
     17172091, 18229842, 18053631, 16867761, 18231837, 15869775, 17483479
     18729166, 17405302, 15920201, 18709496

Patch  19121551     : applied on Mon Nov 17 14:05:34 GMT 2014
Unique Patch ID:  17971200
Patch description:  "Database Patch Set Update : 11.2.0.4.4 (19121551)"
   Created on 9 Oct 2014, 13:06:55 hrs PST8PDT
Sub-patch  18522509; "Database Patch Set Update : 11.2.0.4.3 (18522509)"
Sub-patch  18031668; "Database Patch Set Update : 11.2.0.4.2 (18031668)"
Sub-patch  17478514; "Database Patch Set Update : 11.2.0.4.1 (17478514)"
   Bugs fixed:
     16929165, 16220077, 17235750, 17468141, 18191164, 17006183, 16315398
     17501491, 13955826, 17288409, 12905058, 17446237, 17375354, 16992075
     16855292, 17050888, 17546973, 18554871, 17726838, 17614134, 9756271
     18673325, 17227277, 17232014, 13853126, 17545847, 17390160, 18096714
     12747740, 17016369, 17786518, 19271443, 10136473, 16785708, 17311728
     18018515, 16268425, 17610798, 18280813, 17082359, 17783588, 14245531
     18094246, 13866822, 17477958, 16943711, 18673304, 18031668, 19463897
     19463893, 19211724, 13498382, 16450169, 17397545, 17786278, 17767676
     14458214, 19289642, 17622427, 17824637, 17716305, 16399083, 18744139
     14852021, 19727057, 17242746, 17174582, 18277454, 13645875, 14084247
     17551709, 17393683, 17614227, 17705023, 17883081, 16042673, 18996843
     16285691, 17393915, 16228604, 17655634, 17596908, 17600719, 16180763
     17754782, 17323222, 18264060, 17325413, 17343514, 17865671, 16613964
     17811447, 16069901, 17390431, 16494615, 16043574, 17006570, 17027426
     14602788, 17080436, 18673342, 17186905, 17011832, 17394950, 13944971
     17752121, 17284817, 17811456, 17238511, 17239687, 17042658, 14764829
     17602269, 17672719, 17891946, 17205719, 18262334, 16721594, 17071721
     14565184, 17265217, 17389192, 17761775, 16360112, 17982555, 17842825
     19121551, 16837842, 18139690, 17313525, 18203837, 18203838, 18203835
     18436307, 17546761, 17721717, 17344412, 17787259, 16472716, 17346671
     17588480, 13364795, 14657740, 11733603, 17811438, 19466309, 17040527
     17037130, 17088068, 17612828, 18180390, 17449815, 19458377, 19554106
     18973907, 18230522, 19544839, 17811429, 16863422, 17237521, 17951233
     17752995, 16392068, 17437634, 14338435, 13609098, 17332800, 18199537
     17465741, 17441661, 18522509, 18061914, 14133975, 17570240, 16692232
     18247991, 18328509, 16956380, 17587063, 19049453, 16618694, 17586955
     18009564, 14285317, 16542886, 17341326, 17571306, 17036973, 18641419
     16524926, 17297939, 17478145, 17040764, 17299889, 16912439, 15861775
     14054676, 13951456, 17267114, 16850630, 17648596, 14010183, 17296856
     17478514, 16875449, 18681862, 14829250, 17385178, 17443671, 16731148
     16314254, 17165204, 15979965, 19584068, 17381384, 17892268, 16198143



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

OPatch succeeded.

But on one server I was testing patch for OEM (18235390) to allow it to monitor tablespaces without ORA-1000 errors.Is it possible that my OEM patch add anything special ?

I was thinking about that for a while but I was not sure. So I did simple test - installed this patch on standby server and ... yes - Oracle Restart issue has been fixed. 

 

Strange, very strange - what this patch did that Data Guard was fixed ? Well it did one important thing - it relinked oracle binaries again.

 

Lucky enough I got one more pair with latest PSU ready for test.I run test with PSU installed and bug was still there. OK - so let's relink - and it was it
 

2014-12-22 14:33:40.942: [    AGFW][2057]{0:0:12827} Agent received the message: RESOURCE_MODIFY_ATTR[ora.apppre21.db 1 1] ID 4355:199223
2014-12-22 14:33:40.944: [ora.apppre21.db][9494]{0:0:12827} [res_attr_modified] clsn_agent::modify {
2014-12-22 14:33:40.945: [ora.apppre21.db][9494]{0:0:12827} [res_attr_modified] clsn_agent::modify }
2014-12-22 14:33:40.945: [    AGFW][9494]{0:0:12827} Command: res_attr_modified for resource: ora.apppre21.db 1 1 completed with status: SUCCESS
2014-12-22 14:33:40.946: [    AGFW][2057]{0:0:12827} Attribute: ROLE for resource ora.apppre21.db modified to: PHYSICAL_STANDBY
2014-12-22 14:33:40.946: [    AGFW][2057]{0:0:12827} Attribute: USR_ORA_OPEN_MODE for resource ora.apppre21.db modified to: mount
2014-12-22 14:33:40.946: [    AGFW][2057]{0:0:12827}  config version updated to : 153 for ora.apppre21.db 1 1
2014-12-22 14:33:40.946: [    AGFW][2057]{0:0:12827} Agent sending last reply for: RESOURCE_MODIFY_ATTR[ora.apppre21.db 1 1] ID 4355:199223

 

This is quite strange and a little bit scary. I have installed all PSU using opatch auto from Grid Infrastructure Home and there was no errors but relinking Oracle Home actually fix a problem which was included in the patch. 

 

So remember "Relink after (patch), relink always"

 

regards,

Marcin

DataGuard – Far Sync – part 2 - Data Guard Broker

Oracle introduced Far Sync Data Guard configuration which I described briefly in this post. Now is time for part two and using Data Guard Broker to add Far Sync instance.
Assuming that you have basic Data Guard Broker configuration ready (as described in - How to quickly build standby database and setup DataGuard configuration using Oracle 12c) adding new Far Sync instance is quite easy task.

First we have to setup a new instance which will be used as Far Sync for our primary database.

Instance parameter file - as a copy of primary database configuration (not necessary)

*.audit_file_dest='/home/oracle/admin/test12c/adump'
*.audit_trail='db'
*.compatible='12.1.0.0.0'
*.control_files='/u01/app/oracle/oradata/test12c/control01.ctl','/u01/app/oracle/oradata/test12c/control02.ctl','/u01/app/oracle/oradata/test12c/control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='test12c'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=test12cXDB)'
*.log_archive_dest_1='location=/u01/app/oracle/oradata/test12c/arch'
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=450m
*.undo_tablespace='UNDOTBS1'
# for far sync
*.db_unique_name='test12c_far_sync'
*.LOG_FILE_NAME_CONVERT='test','test'
*.local_listener='(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.60)(PORT = 1522)))'

There are three parameters which have to be changed:

• db_unique_name 
• local_listener - new instance has to be registered in proper listener
• log_file_name_convert - it is useful to have even with dummy values so Oracle can recreate standby logs automatically

Listener configuration

SID_LIST_LISTENER_DG =
 (SID_LIST =
   (SID_DESC=
       (ORACLE_HOME= /u01/app/oracle/product/12.1.0/db1)
       (SID_NAME=test12c)
   )
   (SID_DESC =
      (SID_NAME = test12c)
      (GLOBAL_DBNAME = test12c_far_sync_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db1)
   )
 )


LISTENER_DG =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROCDG1))
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.60)(PORT = 1522))
   )
 )

tnsnames file - entry test12c_far_sync has to be added on primary and standby server as well

test12c_prim =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.45)(PORT = 1522))
   )
 (CONNECT_DATA =
   (SID = test12c)
   (SERVER=dedicated)
 )
)

test12c_std =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.79)(PORT = 1522))
   )
 (CONNECT_DATA =
   (SID = test12c)
   (SERVER=dedicated)
 )
)


test12c_far_sync =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.60)(PORT = 1522))
   )
 (CONNECT_DATA =
   (SID = test12c)
   (SERVER=dedicated)
 )
)

When instance parameter file and network configuration is ready we need to create Far Sync control file on primary server.

[oracle@ora12c dbs]$ rlwrap sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 26 23:21:59 2013

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


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

SQL> ALTER DATABASE CREATE FAR SYNC INSTANCE CONTROLFILE AS '/tmp/farsync.ctl';

Database altered.

Copy it to Far Sync server

[oracle@ora12c dbs]$ scp /tmp/farsync.ctl 192.168.1.60:/u01/app/oracle/oradata/test12c/control01.ctl
oracle@192.168.1.60's password:
farsync.ctl                                                                                                                  100%   10MB  10.3MB/s   00:00
[oracle@ora12c dbs]$ scp /tmp/farsync.ctl 192.168.1.60:/u01/app/oracle/oradata/test12c/control02.ctl
oracle@192.168.1.60's password:
farsync.ctl                                                                                                                  100%   10MB  10.3MB/s   00:01
[oracle@ora12c dbs]$ scp /tmp/farsync.ctl 192.168.1.60:/u01/app/oracle/oradata/test12c/control03.ctl
oracle@192.168.1.60's password:
farsync.ctl                                                                                                                  100%   10MB  10.3MB/s   00:00
[oracle@ora12c dbs]$

Now Far Sync instance can be started and new standby logs could be created. Make sure if Data Guard Broker has been started as well.

[oracle@oraclese dbs]$ rlwrap sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 26 23:24:27 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> create spfile from pfile;
File created.

SQL> startup mount
ORACLE instance started.

Total System Global Area  471830528 bytes
Fixed Size                  2289688 bytes
Variable Size             293605352 bytes
Database Buffers          167772160 bytes
Redo Buffers                8163328 bytes
Database mounted.

SQL> alter system set dg_broker_start = true;
System altered.

SQL> alter database add standby logfile ('/u01/app/oracle/oradata/test12c/standby01.log') size 50M reuse;
Database altered.

SQL> alter database add standby logfile ('/u01/app/oracle/oradata/test12c/standby02.log') size 50M reuse;
Database altered.

SQL> alter database add standby logfile ('/u01/app/oracle/oradata/test12c/standby03.log') size 50M reuse;
Database altered.

Like if previous post about Data Guard configuration - Broker will be used to do all configuration work. You should to connect to Broker from primary database. This is how current configuration looks like:

[oracle@ora12c ~]$ rlwrap dgmgrl /
DGMGRL for Linux: Version 12.1.0.1.0 - 64bit Production

Copyright (c) 2000, 2012, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.
DGMGRL> connect /
Connected as SYSDG.
DGMGRL>  show configuration verbose;

Configuration - fsc

  Protection Mode: MaxPerformance
  Databases:
  test12c_prim - Primary database
    test12c_std - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Adding Far Sync

DGMGRL> add far_sync test12c_far_sync as connect identifier is 'test12c_far_sync';
far sync instance "test12c_far_sync" added
DGMGRL>  show configuration verbose;

Configuration - fsc

  Protection Mode: MaxPerformance
  Databases:
  test12c_prim - Primary database
    test12c_std - Physical standby database
    test12c_far_sync  - Far Sync (disabled)

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Now new rules for redo log transport have to be configured

DGMGRL> edit database 'test12c_prim' set property redoroutes='(local : test12c_far_sync sync)';
Property "redoroutes" updated
DGMGRL> edit far_sync 'test12c_far_sync' set property redoroutes='(test12c_prim : test12c_std)';
Property "redoroutes" updated

Above configuration is sending redo data from primary database to far sync instance using SYNC mode and redo data from far sync instance to standby database using ASYNC mode. As primary and far sync instance are close one to each other you can have no data loss mode without additional network synchronization overhead.
Redo logs routing rules are described in Oracle documentation.

Enabling and checking configuration

DGMGRL> enable far_sync test12c_far_sync;
Enabled.
DGMGRL> show configuration verbose;

Configuration - fsc

  Protection Mode: MaxPerformance
  Databases:
  test12c_prim - Primary database
    test12c_far_sync  - Far Sync
      test12c_std - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

If everything is OK we can switch protection mode from Maximum Performance to Maximum Availability to get all advantages of our new configured Far Sync instance.

DGMGRL> edit configuration set protection mode as MaxAvailability;
Succeeded.
DGMGRL> show configuration verbose;

Configuration - fsc

  Protection Mode: MaxAvailability
  Databases:
  test12c_prim - Primary database
    test12c_far_sync  - Far Sync
      test12c_std - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

Now all is done. Happy testing. 
Marcin

How to quickly build standby database and setup DataGuard configuration using Oracle 12c

With every release of Oracle database there are new possibilities to build a required solution (ex. standby database) faster and with fewer steps. This is not official guide but rather a mix of Oracle 12c functionality used to build DataGuard configuration using RMAN and DataGuard Broker.

Assumptions:

• There is a database which will be used as primary.
• New listener will be added to support standby traffic

Steps to run on primary database

Listener configuration

LISTENER_DG =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROCDG1))
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.45)(PORT = 1522))
   )
 )

SID_LIST_LISTENER_DG =
 (SID_LIST =
   (SID_DESC=
       (ORACLE_HOME= /u01/app/oracle/product/12.1.0/db1)
       (SID_NAME=test12c)
   )
   (SID_DESC =
      (SID_NAME = test12c)
      (GLOBAL_DBNAME = test12c_prim_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db1)
   )
 )

tnsnames entries

test12c_std =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.79)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SID = test12c)
      (SERVER = dedicated)
    )
  )


test12c_prim =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.45)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SID = test12c)
      (SERVER = dedicated)
    )
  )

New parameters for database

[oracle@ora12c ~]$ rlwrap sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 26 21:02:14 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

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

SQL> alter system set local_listener='(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.45)(PORT = 1522)))';
System altered.

SQL> alter system set db_unique_name='test12c_prim' scope=spfile;
System altered.

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

Total System Global Area  471830528 bytes
Fixed Size                  2289688 bytes
Variable Size             255856616 bytes
Database Buffers          209715200 bytes
Redo Buffers                3969024 bytes
Database mounted.
Database opened.

New control file for standby

SQL> alter database create standby controlfile as '/tmp/standby.ctl' reuse;
Database altered.

Copy standby control file to standby server

[oracle@ora12c ~]$ scp -i /home/oracle/oracle_standby.key  /tmp/standby.ctl     oracle@10.0.1.79:/u01/app/oracle/oradata/test12c/control01.ctl
standby.ctl                                                                                                                       100%   10MB 438.0KB/s   00:24
[oracle@ora12c ~]$ scp -i /home/oracle/oracle_standby.key  /tmp/standby.ctl     oracle@10.0.1.79:/u01/app/oracle/oradata/test12c/control02.ctl
standby.ctl                                                                                                                       100%   10MB 438.0KB/s   00:24
[oracle@ora12c ~]$ scp -i /home/oracle/oracle_standby.key  /tmp/standby.ctl     oracle@10.0.1.79:/u01/app/oracle/oradata/test12c/control03.ctl
standby.ctl                                                                                                                       100%   10MB 438.0KB/s   00:24

Steps to run on new standby database

Database parameter file - it could be a copy of primary database file with two changes - db_unique_name and local_listener

*.audit_file_dest='/home/oracle/admin/test12c/adump'
*.audit_trail='db'
*.compatible='12.1.0.0.0'
*.control_files='/u01/app/oracle/oradata/test12c/control01.ctl','/u01/app/oracle/oradata/test12c/control02.ctl','/u01/app/oracle/oradata/test12c/control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='test12c'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=test12cXDB)'
*.log_archive_dest_1='location=/u01/app/oracle/oradata/test12c/arch'
*.open_cursors=300
*.pga_aggregate_target=150m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=450m
*.undo_tablespace='UNDOTBS1'
# changes for standby
*.db_unique_name='test12c_std'
*.local_listener='(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.79)(PORT = 1522)))'

Listener configuration

LISTENER_DG =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROCDG1))
     (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.79)(PORT = 1522))
   )
 )

SID_LIST_LISTENER_DG =
 (SID_LIST =
   (SID_DESC=
       (ORACLE_HOME= /u01/app/oracle/product/12.1.0/db1)
       (SID_NAME=test12c)
   )
   (SID_DESC =
      (SID_NAME = test12c)
      (GLOBAL_DBNAME = test12c_std_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/db1)
   )
 )

tnsnames entries

test12c_std =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.1.79)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SID = test12c)
      (SERVER = dedicated)
    )
  )


test12c_prim =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.45)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SID = test12c)
      (SERVER = dedicated)
    )
  )

In next step we have to check if all datafile and configuration directory are in place on standby machine and startup instance in mount mode.

Starting up instance
 

[oracle@ip-10-0-1-79 dbs]$ rlwrap sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 26 17:39:48 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> create spfile from pfile;
File created.

SQL> startup mount
ORACLE instance started.

Total System Global Area  471830528 bytes
Fixed Size                  2289688 bytes
Variable Size             255856616 bytes
Database Buffers          209715200 bytes
Redo Buffers                3969024 bytes
Database mounted.
SQL>

RMAN restore over network is an Oracle 12c feature. Yes there was duplicate from active database in 11g but this one is much easier to run and required fewer configuration steps. To restore Oracle data files on standby server we need to configure only instance in mount mode and add tns alias to primary database.

Starting RMAN and restoring data files 

[oracle@ip-10-0-1-79 dbs]$ rlwrap rman target /
Recovery Manager: Release 12.1.0.1.0 - Production on Fri Jul 26 16:18:57 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
connected to target database: TEST12C (DBID=1424547711, not open)

RMAN> restore database from service test12c_prim using compressed backupset;
Starting restore at 26-JUL-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service test12c_prim
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/test12c/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:08:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service test12c_prim
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/test12c/sysaux01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:08:55
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service test12c_prim
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/test12c/undotbs01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:01:25
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service test12c_prim
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/test12c/users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service test12c_prim
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/test12c/soe.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:46:45
Finished restore at 26-JUL-13

RMAN>

At that stage we have primary and standby database almost ready. 

Step to run on primary and standby database

Starting DataGuard Broker and create standby log files
 

SQL> alter system set dg_broker_start = true;
System altered.

SQL> alter database add standby logfile ('/u01/app/oracle/oradata/test12c/standby01.log') size 50M reuse;
Database altered.

SQL> alter database add standby logfile ('/u01/app/oracle/oradata/test12c/standby02.log') size 50M reuse;
Database altered.

SQL> alter database add standby logfile ('/u01/app/oracle/oradata/test12c/standby03.log') size 50M reuse;
Database altered.

Steps to run on primary database

Now is time for DataGuard Broker to work for us. 

Adding primary database

[oracle@ora12c ~]$ rlwrap dgmgrl /
DGMGRL for Linux: Version 12.1.0.1.0 - 64bit Production

Copyright (c) 2000, 2012, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.
DGMGRL> connect /
Connected as SYSDG.
DGMGRL> create configuration FSC as primary database is test12c_prim connect identifier is 'test12c_prim';
Configuration "fsc" created with primary database "test12c_prim"

Adding standby database and displaying configuration

DGMGRL> add database test12c_std as connect identifier is 'test12c_std' maintained as physical;
Database "test12c_std" added
DGMGRL> show configuration verbose;

Configuration - fsc

  Protection Mode: MaxPerformance
  Databases:
  test12c_prim - Primary database
    test12c_std - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

Enabling configuration and checking DataGuard status

DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration verbose;

Configuration - fsc

  Protection Mode: MaxPerformance
  Databases:
  test12c_prim - Primary database
    test12c_std - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    TraceLevel                      = 'USER'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

And our DataGuard configuration is up and running. Now we can change default configuration to one which meet our requirements. I next post I will add Far Sync instance and switch configuration to Maximum Availability.

regards,
Marcin