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
Post a Comment