Creating a proper standby usable with dgmgrl as basic as possible



In a earlier series of pointless posts i described how to create some basic setup as simple as possible

I had a simple database here
http://dbaharrison.blogspot.co.uk/2013/12/it-cant-get-much-simpler-than-this.html

A cdb/pdb database here
http://dbaharrison.blogspot.de/2014/01/12c-cdb-with-pdb-as-simple-as-possible.html

I had dataguard here
http://dbaharrison.blogspot.de/2013/12/creating-standby-minimally.html

I wanted to show just how easy some things can be and a lot of the extra stuff is maybe not needed - at least in a very basic setup

I've had to build a properly functioning dataguard this week from scratch that actually worked with dgmgrl - so i decided to try and also do this minimally too.

My previous minimal dataguard post while it did kind of create one - it was a bit of a cop out and not really that useful - this time I'm going to set it up from scratch and have it capable of doing switchover from dgmgrl.

Unfortunately it's not quite as short as i'd hoped - but hopefully a lot shorter than a lot of other examples - some of the output of the commands is a bit lengthy but i think it's useful to show that to refer to as it helps understand what is happening.

Anyway - here we go

In this example i'm using 12.1.0.2 and a database called EIDG (one node has the unique_name EIDG_UK and the other EIDG_DE)

First up we add this entry to the oratab on both servers

EIDG:/oracle/12.1.0.2.1.DB:N:

I then use this to set the environment on each side the oraenv

Now i'm assuming a standard listener on 1521 on both sides - into both sides we need to add these lines to get a static entry for dataguard to use - after this is added restart

SID_LIST_LISTENER = 
    (SID_LIST =
      (SID_DESC =
      (GLOBAL_DBNAME = EIDG)
      (ORACLE_HOME = /oracle/12.1.0.2.1.DB)
      (SID_NAME =  EIDG)
    )
 )

Now in the tnsnames.ora on both sides add these 2 entries

EIDG_UK =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (UR = A)
      (SID = EIDG)
    )
  )

EIDG_DE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (UR = A)
      (SID = EIDG)
    )
  )

That's the networky bits done - now we create the primary database.

create an initEIDG.ora with the following content

db_create_file_dest='/oracle/EIDG/oradata'
DB_NAME='EIDG'
db_recovery_file_dest='/oracle/EIDG/recovery_area'
db_recovery_file_dest_size=10G
DB_UNIQUE_NAME='EIDG_UK'
shared_pool_size=1G
log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=EIDG_UK'
log_archive_dest_state_1='enable'
remote_login_passwordfile='EXCLUSIVE'
FAL_SERVER=EIDG_DE;
FAL_CLIENT=EIDG_UK;
LOG_ARCHIVE_CONFIG='DG_CONFIG=(EIDG_UK,EIDG_DE)'

Now we create the database

SQL> startup nomount
SQL> create spfile from pfile;
SQL> shutdown -- this stage is important to make sure we are running with an spfile
SQL> startup nomount
SQL> create database;
SQL> @?/rdbms/admin/catalog.sql
SQL>@?rdbms/admin/catproc.sql

Now the database is created - a quick few tweaks

SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
SQL> alter database add standby logfile;
SQL> alter database add standby logfile;
SQL> alter user sys identified by oracle;

OK - all looking good - now we need to create a minimal instance on the remote box - this has to use a pfile (not spfile) - or it creates problems with the rman command

initEIDG.ora on this side contains

DB_NAME=EIDG
shared_pool_size=1G
db_create_file_dest=/oracle/EIDG/oradata
db_recovery_file_dest=/oracle/EIDG/recovery_area
db_recovery_file_dest_size=10G

Lets bring up the instance

SQL>startup nomount

OK - now we just need to set the password file correctly and we are ready to run the rman magic

So on both sides in $ORACLE_HOME/dbs run this

orapwd file=orapwEIDG password=oracle entries=5 force=y ignorecase=Y

Right good to go with rman

so we connect to primary and standby (auxiliary)

rman target=sys/oracle@EIDG_UK auxiliary=sys/oracle@EIDG_DE

and run this

run {
allocate channel primaryside type disk;
allocate auxiliary channel standbyside type disk;
duplicate target database for standby from active database
spfile
 parameter_value_convert 'EIDG_UK','EIDG_DE'
 set db_unique_name='EIDG_DE'
 set fal_client='EIDG_DE'
 set fal_server='EIDG_UK'
 set standby_file_management='AUTO'
 set log_archive_config='dg_config=(EIDG_UK,EIDG_DE)'
;
}


output below (little long i'm afraid - catch up with the story again below this....)

Recovery Manager: Release 12.1.0.2.0 - Production on Thu Apr 2 22:03:30 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: EIDG (DBID=2674745765)
connected to auxiliary database: EIDG (not mounted)

RMAN> run {
2> allocate channel primaryside type disk;
3> allocate auxiliary channel standbyside type disk;
4> duplicate target database for standby from active database
5> spfile
6>  parameter_value_convert 'EIDG_UK','EIDG_DE'
7>  set db_unique_name='EIDG_DE'
8>  set fal_client='EIDG_DE'
9>  set fal_server='EIDG_UK'
10>  set standby_file_management='AUTO'
11>  set log_archive_config='dg_config=(EIDG_UK,EIDG_DE)'
12> ;
}13>

using target database control file instead of recovery catalog
allocated channel: primaryside
channel primaryside: SID=7 device type=DISK

allocated channel: standbyside
channel standbyside: SID=74 device type=DISK

Starting Duplicate Db at 02-APR-15

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/oracle/12.1.0.2.1.DB/dbs/orapwEIDG' auxiliary format
 '/oracle/12.1.0.2.1.DB/dbs/orapwEIDG'   ;
   restore clone from service  'EIDG_UK' spfile to
 '/oracle/12.1.0.2.1.DB/dbs/spfileEIDG.ora';
   sql clone "alter system set spfile= ''/oracle/12.1.0.2.1.DB/dbs/spfileEIDG.ora''";
}
executing Memory Script

Starting backup at 02-APR-15
Finished backup at 02-APR-15

Starting restore at 02-APR-15

channel standbyside: starting datafile backup set restore
channel standbyside: using network backup set from service EIDG_UK
channel standbyside: restoring SPFILE
output file name=/oracle/12.1.0.2.1.DB/dbs/spfileEIDG.ora
channel standbyside: restore complete, elapsed time: 00:00:01
Finished restore at 02-APR-15

sql statement: alter system set spfile= ''/oracle/12.1.0.2.1.DB/dbs/spfileEIDG.ora''

contents of Memory Script:
{
   sql clone "alter system set  control_files =
 ''/oracle/EIDG/oradata/EIDG_DE/controlfile/o1_mf_bkvb6ok3_.ctl'', ''/oracle/EIDG/recovery_area/EIDG_DE/controlfile/o1_mf_bkvb6oo9_.ctl'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_dest_1 =
 ''location=use_db_recovery_file_dest valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=EIDG_DE'' comment=
 '''' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''EIDG_DE'' comment=
 '''' scope=spfile";
   sql clone "alter system set  fal_client =
 ''EIDG_DE'' comment=
 '''' scope=spfile";
   sql clone "alter system set  fal_server =
 ''EIDG_UK'' comment=
 '''' scope=spfile";
   sql clone "alter system set  standby_file_management =
 ''AUTO'' comment=
 '''' scope=spfile";
   sql clone "alter system set  log_archive_config =
 ''dg_config=(EIDG_UK,EIDG_DE)'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set  control_files =  ''/oracle/EIDG/oradata/EIDG_DE/controlfile/o1_mf_bkvb6ok3_.ctl'', 

''/oracle/EIDG/recovery_area/EIDG_DE/controlfile/o1_mf_bkvb6oo9_.ctl'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_dest_1 =  ''location=use_db_recovery_file_dest valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=EIDG_DE'' 

comment= '''' scope=spfile

sql statement: alter system set  db_unique_name =  ''EIDG_DE'' comment= '''' scope=spfile

sql statement: alter system set  fal_client =  ''EIDG_DE'' comment= '''' scope=spfile

sql statement: alter system set  fal_server =  ''EIDG_UK'' comment= '''' scope=spfile

sql statement: alter system set  standby_file_management =  ''AUTO'' comment= '''' scope=spfile

sql statement: alter system set  log_archive_config =  ''dg_config=(EIDG_UK,EIDG_DE)'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1174405120 bytes

Fixed Size                     3710064 bytes
Variable Size               1107297168 bytes
Database Buffers              50331648 bytes
Redo Buffers                  13066240 bytes
allocated channel: standbyside
channel standbyside: SID=74 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  control_files =
  ''/oracle/EIDG/oradata/EIDG_DE/controlfile/o1_mf_bkvb6ok3_.ctl'', ''/oracle/EIDG/recovery_area/EIDG_DE/controlfile/o1_mf_bkvb6oo9_.ctl'' comment=
 ''Set by RMAN'' scope=spfile";
   restore clone from service  'EIDG_UK' standby controlfile;
}
executing Memory Script

sql statement: alter system set  control_files =   ''/oracle/EIDG/oradata/EIDG_DE/controlfile/o1_mf_bkvb6ok3_.ctl'', 

''/oracle/EIDG/recovery_area/EIDG_DE/controlfile/o1_mf_bkvb6oo9_.ctl'' comment= ''Set by RMAN'' scope=spfile

Starting restore at 02-APR-15

channel standbyside: starting datafile backup set restore
channel standbyside: using network backup set from service EIDG_UK
channel standbyside: restoring control file
channel standbyside: restore complete, elapsed time: 00:00:01
output file name=/oracle/EIDG/oradata/EIDG_DE/controlfile/o1_mf_bkvb6ok3_.ctl
output file name=/oracle/EIDG/recovery_area/EIDG_DE/controlfile/o1_mf_bkvb6oo9_.ctl
Finished restore at 02-APR-15

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for clone datafile  1 to new;
   set newname for clone datafile  2 to new;
   set newname for clone datafile  3 to new;
   restore
   from service  'EIDG_UK'   clone database
   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 02-APR-15

channel standbyside: starting datafile backup set restore
channel standbyside: using network backup set from service EIDG_UK
channel standbyside: specifying datafile(s) to restore from backup set
channel standbyside: restoring datafile 00001 to /oracle/EIDG/oradata/EIDG_DE/datafile/o1_mf_system_%u_.dbf
channel standbyside: restore complete, elapsed time: 00:00:15
channel standbyside: starting datafile backup set restore
channel standbyside: using network backup set from service EIDG_UK
channel standbyside: specifying datafile(s) to restore from backup set
channel standbyside: restoring datafile 00002 to /oracle/EIDG/oradata/EIDG_DE/datafile/o1_mf_sysaux_%u_.dbf
channel standbyside: restore complete, elapsed time: 00:00:15
channel standbyside: starting datafile backup set restore
channel standbyside: using network backup set from service EIDG_UK
channel standbyside: specifying datafile(s) to restore from backup set
channel standbyside: restoring datafile 00003 to /oracle/EIDG/oradata/EIDG_DE/datafile/o1_mf_sys_undo_%u_.dbf
channel standbyside: restore complete, elapsed time: 00:00:15
Finished restore at 02-APR-15

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=876002697 file name=/oracle/EIDG/oradata/EIDG_DE/datafile/o1_mf_system_bkvcpdct_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=5 STAMP=876002697 file name=/oracle/EIDG/oradata/EIDG_DE/datafile/o1_mf_sysaux_bkvcpvmw_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=876002697 file name=/oracle/EIDG/oradata/EIDG_DE/datafile/o1_mf_sys_undo_bkvcqbvq_.dbf
Finished Duplicate Db at 02-APR-15
released channel: primaryside
released channel: standbyside

RMAN>


Pretty neat?

Nearly there then

Next we add a service to be used by the clients when they connect to ensure they are routed to the correct 'live' host and trigger that from a database startup trigger.

begin 
dbms_service.create_service( service_name => 'EIDG_DG', 
network_name => 'EIDG_DG', 
failover_method => 'BASIC', 
failover_type => 'SELECT', 
failover_retries => 180, 
failover_delay => 1); 
end; 

create or replace trigger manage_service after startup on database 
declare 
role varchar2(40); 
begin 
select database_role into role from v$database; 
if role = 'PRIMARY' then 
dbms_service.start_service('EIDG_DG'); 
dbms_system.add_parameter_value('service_names','EIDG_DG','MEMORY');
else 
dbms_service.stop_service('EIDG_DG'); 
end if; 
end; 

Now 2 more tweaks (on both sides here)

 alter system set dg_broker_start=TRUE;
alter system set log_archive_dest_2='';

Right all ready for dg broker - lets do that

dgmgrl
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle
Connected as SYSDG.
DGMGRL> create configuration 'EIDG_DG' as primary database is 'EIDG_UK' connect identifier is EIDG_UK;
Configuration "EIDG_DG" created with primary database "EIDG_UK"
DGMGRL> add database 'EIDG_DE' as connect identifier is EIDG_DE maintained as physical;
Database "EIDG_DE" added
DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration

Configuration - EIDG_DG

  Protection Mode: MaxPerformance
  Members:
  EIDG_UK - Primary database
    EIDG_DE - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 2 seconds ago)

DGMGRL>


All set up and ready to switchover......

So we switch

DGMGRL> switchover to 'EIDG_UK';
Performing switchover NOW, please wait...
Operation requires a connection to instance "EIDG" on database "EIDG_UK"
Connecting to instance "EIDG"...
Connected as SYSDBA.
New primary database "EIDG_UK" is opening...
Oracle Clusterware is restarting database "EIDG_DE" ...


And well we hit at 12.1.0.2 bug... the db is not registered in oracle restart (the cut down clusterware) but oracle tries to start it via srvctl commands - and just hangs.

But anyway trust me it does work - either if you remove clusterware or set up the database with oracle restart

So there you go - dataguard with dgmgrl about as simple as you can get


Comments