So there are heaps of articles out there on how to do this, so this one is for my benefit because I've run through it a few times and I know it works. If it doesn't work using this, it means that there is an issue with the environment or infrastructure.
This is pretty much copy and paste.
Primary
copy+paste this into a sqlplus session as sysdba
ALTER DATABASE FORCE LOGGING;
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,ORCL_STDBY)';
alter system set log_archive_dest_2='SERVICE=ORCL_STDBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL_STDBY';
alter system set LOG_ARCHIVE_DEST_STATE_2=DEFER;
alter system set fal_server=ORCL_STDBY;
alter system set fal_client=ORCL;
alter system set DB_FILE_NAME_CONVERT='ORCL_STDBY','ORCL' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='ORCL_STDBY','ORCL' scope=spfile;
alter system set STANDBY_FILE_MANAGEMENT=AUTO;
Find the size of the redo logs and create the same number and size for standby
SELECT GROUP#,BYTES FROM V$LOG;
GROUP# BYTES
---------- ----------
1 52428800
3 52428800
2 52428800
Check to see if standby logs have been created
SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
If so, skip the next step.
If not, add them:
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50M;
create pfile='/home/oracle/initORCL_STDBY.ora' from spfile;
cat the created file and copy and paste it into the standby, then edit it.
Standby
edit the init.ora
*.control_files='+DATA','+RECO','+ARCH'
*.log_archive_dest_2='SERVICE=ORCL LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL';
*.db_unique_name='ORCL_STDBY'
*.log_file_name_convert='ORCL','ORCL_STDBY'
*.db_file_name_convert='ORCL','ORCL_STDBY'
*.fal_client='ORCL_STDBY'
*.fal_server='ORCL'
*.log_archive_config='DG_CONFIG=(ORCL,ORCL_STDBY)'
standby listener (could be in GRID HOME, could be in ORACLE_HOME, could be defined by TNS_ADMIN)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL_STDBY)
(SID_NAME = ORCL)
(ORACLE_HOME = /oracle/ORCL/121)
)
)
lsnrctl reload
Needs to be 'UNKNOWN', not 'BLOCKED'
tnsnames.ora on both hosts (could be in GRID HOME, could be in ORACLE_HOME, could be defined by TNS_ADMIN, may need .WORLD, may not)
ORCL.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = SAP.WORLD)
(PROTOCOL = TCP)
(HOST = oraorcl01)
(PORT = 1521)
)
)
(CONNECT_DATA =
(SID = ORCL)
(GLOBAL_NAME = ORCL.WORLD)
(UR = A)
)
)
ORCL_STDBY.WORLD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(COMMUNITY = SAP.WORLD)
(PROTOCOL = TCP)
(HOST = oraorcl02)
(PORT = 1521)
)
)
(CONNECT_DATA =
(SID = ORCL)
(GLOBAL_NAME = ORCL.WORLD)
(UR = A)
)
)
Check you can tnsping - if not, they probably haven't opened the ports.
TNS-12535: TNS:operation timed out = ports blocked
startup nomount pfile='initORCL_STDBY.ora'
rman script
DIR=/home/oracle/scripts
LOG=$DIR/duplicate.log
rman target sys@ORCL/xxxxx auxiliary sys@ORCL_STDBY/xxxxx << EOF > $LOG
RUN
{
allocate channel dev1 type disk;
allocate channel dev2 type disk;
allocate channel dev3 type disk;
allocate channel dev4 type disk;
allocate channel dev5 type disk;
ALLOCATE auxiliary CHANNEL CH01 TYPE DISK;
ALLOCATE auxiliary CHANNEL CH02 TYPE DISK;
ALLOCATE auxiliary CHANNEL CH03 TYPE DISK;
ALLOCATE auxiliary CHANNEL CH04 TYPE DISK;
ALLOCATE auxiliary CHANNEL CH05 TYPE DISK;
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE;
}
EOF
check standby db_unique_name = ORCL_STDBY
shutdown immediate
sqlplus / as sysdba
create spfile='+DATA/ORCL_STDBY/spfileORCL.ora' from pfile;
exit
mv initORCL.ora initORCL.ora_old
vi initORCL.ora
spfile=('+DATA/ORCL_STDBY/spfileORCL.ora')
sqlplus / as sysdba
startup mount
On primary
alter system set log_archive_dest_state_2=enable;
On standby
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
tail the standby alert log
tail -f /oracle/ORCL/saptrace/diag/rdbms/ORCL_stdby/ORCL/trace/alert_ORCL.log
On the Primary
alter system switch logfile;
/
/
Logs should be applied to the standby.
Check the gap:
select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax
from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) al,
(select thread# thrd, max(sequence#) lhmax
from v$log_history
where first_time=(select max(first_time) from v$log_history)
group by thread#) lh
where al.thrd = lh.thrd;
Numbers should match
Thread Last Seq Received Last Seq Applied
---------- ----------------- ----------------
1 7197 7197
There is a SAP process called BRARCHIVE which backs up and deletes logs, so you may have missing logs that have gone from the primary
If there is a gap in logs, restore them like this:
rman target = /
SET DECRYPTION IDENTIFIED BY 'xxxxx';
run {
allocate channel sbt_1 device type 'SBT_TAPE'
parms 'SBT_LIBRARY=/oracle/ORCL/121/lib/libosbws.so, ENV=(OSB_WS_PFILE=/oracle/ORCL/121/dbs/osbwsORCL.ora)';
restore archivelog from logseq=7187 until logseq=7194 ;
}
Setting up the broker
on both primary and standby - assuming it's a new configuration. If not, there is an Oracle note - Step By Step How to Recreate Dataguard Broker Configuration (Doc ID 808783.1)
alter system set dg_broker_start=true;
alter system set log_archive_dest_2='' scope=both;
show parameter broker
remove / move any broker config files
dg_broker_config_file1 string /oracle/ORCL/121/dbs/dr1ORCL.dat
dg_broker_config_file2 string /oracle/ORCL/121/dbs/dr2ORCL.dat
exit
dgmgrl /
create configuration 'ORCL_DG' as primary database is 'ORCL' connect identifier is 'ORCL';
ADD DATABASE 'ORCL_STDBY' AS CONNECT IDENTIFIER IS ORCL_STDBY MAINTAINED AS PHYSICAL;
enable configuration;
show configuration;
Should show "SUCCESS" at the bottom
Tail the standby alert log and switch some primary log files, check the gap.
Set up failover connections
exec DBMS_SERVICE.CREATE_SERVICE (service_name => 'ORCL_AUTO',network_name => 'ORCL_AUTO',failover_method => 'BASIC',failover_type => 'SELECT',failover_retries => 180,failover_delay => 1);
PL/SQL procedure successfully completed.
CREATE OR REPLACE TRIGGER manage_dgservice
after startup on database
DECLARE
role VARCHAR(30);
BEGIN
SELECT DATABASE_ROLE INTO role FROM V$DATABASE;
IF role = 'PRIMARY' THEN
DBMS_SERVICE.START_SERVICE('ORCL_AUTO');
END IF;
END;
/
client connection - give to the application
ORCL_AUTO=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oraorcl01)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = oraorcl02)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL_AUTO)
)
)
