PRIMARY DBNAME: DBTST UNIQUE NAME: DBTST HOST : DEVPRMRY
STANDBY DBNAME: DBTST UNIQUE NAME: DBSTY HOST : DEVSTDBY
PRIMARY
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u02/oradata/DBTST/system01.dbf
/u02/oradata/DBTST/sysaux01.dbf
/u02/oradata/DBTST/undotbs01.dbf
/u02/oradata/DBTST/users01.dbf
SQL> select name from v$controlfile ;
NAME
--------------------------------------------------------------------------------
/u02/oradata/DBTST/control01.ctl
/u03/fast_recovery_area/DBTST/control02.ctl
SQL> column SUBSTR(MEMBER,1,60) format A50
select group#, status, type, substr(member,1, 60) from v$logfile order by group#, member;
SQL>
GROUP# STATUS TYPE SUBSTR(MEMBER,1,60)
---------- ------- ------- --------------------------------------------------
1 ONLINE /u02/oradata/DBTST/redo01.log
2 ONLINE /u02/oradata/DBTST/redo02.log
3 ONLINE /u02/oradata/DBTST/redo03.log
SQL> SELECT log_mode FROM v$database;
LOG_MODE
------------
ARCHIVELOG
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string DBTST
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string DBTST
SQL>
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(DBTST,DBSTY)';
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=DBSTY NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DBSTY';
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;
System altered.
SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
System altered.
In addition to the previous setting, it is recommended to make sure the primary is ready to switch roles to become a standby. For that to work properly we need to set the following parameters. Adjust the *_CONVERT parameters to account for your filename and path differences between the servers.
SQL> ALTER SYSTEM SET FAL_SERVER=DBSTY;
System altered.
SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT='DBSTY','DBTST' SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='DBSTY','DBTST' SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
System altered.
TNSNAMES.ORA****
DBTST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DEVPRMRY.fcicanada.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DBTST)
)
)
DBSTY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DEVSTDBY.fcicanada.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DBTST)
)
)
BACKUP PRIMARY DATABASE
-----------------------
run
{
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET;
allocate channel c1 DEVICE TYPE DISK MAXPIECESIZE = 5G;
backup full format "/home/oracle/%d_DB_%u_%s_%p" database plus archivelog ;
copy current controlfile to "/home/oracle/STDBY_control.ctl";
}
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/home/oracle/DBTST.ctl';
Database altered.
SQL> CREATE PFILE='/home/oracle/initDBSTY.ora' from spfile;
File created.
CREATE PASSWORD FILE
--------------------
[oracle@DEVPRMRY dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwDBTST password=oracle
******scp $ORACLE_HOME/dbs/orapwDBTST oracle@192.168.1.171:$ORACLE_HOME/dbs/orapwDBSTY
restart database standby
Amend the PFILE making the entries relevant for the standby database
---------------------------------------------------------------------
*.db_unique_name='DBSTY'
*.fal_server='DBTST'
*.log_archive_dest_2='SERVICE=DBTST ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DBTST'
Create the necessary directories on the standby server *****
[oracle@DEVSTDBY ~]$ mkdir -p /u01/app/oracle/admin/DBTST/adump
[oracle@DEVSTDBY ~]$ mkdir -p /u02/oradata/DBTST/
[oracle@DEVSTDBY ~]$ mkdir -p /u03/fast_recovery_area
[oracle@DEVSTDBY ~]$ mkdir -p /u03/fast_recovery_area/DBTST/
# Standby controlfile to all locations.
[oracle@DEVPRMRY ~]$ scp /home/oracle/DBTST.ctl oracle@192.168.1.171:/u02/oradata/DBTST/control01.ctl
STDBY_control.ctl 100% 9520KB 9.3MB/s 00:00
[oracle@DEVPRMRY ~]$ scp /home/oracle/DBTST.ctl oracle@192.168.1.171:/u03/flash_recovery_area/DBTST/control02.ctl
STDBY_control.ctl 100% 9520KB 9.3MB/s 00:00
# Archivelogs and backups
[oracle@DEVPRMRY ~]$ scp DBTST_DB_01qm8m5e_1_1 oracle@192.168.1.171:/u03/flash_recovery_area/DBTST/
DBTST_DB_01qm8m5e_1_1 100% 268MB 29.8MB/s 00:09
[oracle@DEVPRMRY ~]$ scp DBTST_DB_02qm8m75_2_1 oracle@192.168.1.171:/u03/fast_recovery_area/DBTST/
DBTST_DB_02qm8m75_2_1 100% 1072KB 1.1MB/s 00:00
# Parameter file.
[oracle@DEVPRMRY ~]$ !
initDBTY.ora 100% 969 1.0KB/s 00:00
# Remote login password file.
[oracle@DEVPRMRY ~]$ scp $ORACLE_HOME/dbs/orapwDBTST oracle@192.168.1.171:$ORACLE_HOME/dbs/
orapwDBTST 100% 1536 1.5KB/s 00:00
[oracle@DEVSTDBY ~]$ export ORACLE_SID=DBSTY
[oracle@DEVSTDBY ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Nov 14 16:52:43 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> CREATE SPFILE FROM PFILE='$ORACLE_HOME/dbs/initDBSTY.ora';
File created.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 768294912 bytes
Fixed Size 2257192 bytes
Variable Size 503320280 bytes
Database Buffers 260046848 bytes
Redo Buffers 2670592 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
[oracle@DEVSTDBY ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sat Nov 14 16:54:02 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DBTST (DBID=3112393672, not open)
RMAN> restore database;
Starting restore at 14-NOV-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u02/oradata/DBTST/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u02/oradata/DBTST/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u02/oradata/DBTST/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u02/oradata/DBTST/users01.dbf
channel ORA_DISK_1: reading from backup piece /u03/fast_recovery_area/DBTST/DBTST_DB_01qm8m5e_1_1
channel ORA_DISK_1: piece handle=/u03/fast_recovery_area/DBTST/DBTST_DB_01qm8m5e_1_1 tag=TAG20151114T163910
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:19
Finished restore at 14-NOV-15
RMAN>recover database;
sql>
Create online redo logs for the standby
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
ALTER DATABASE ADD LOGFILE ('/u02/oradata/DBTST/redo01.log') SIZE 50M;
ALTER DATABASE ADD LOGFILE ('/u02/oradata/DBTST/redo02.log') SIZE 50M;
ALTER DATABASE ADD LOGFILE ('/u02/oradata/DBTST/redo03.log') SIZE 50M;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
In addition to the online redo logs, you should create standby redo logs on both the standby and the primary database
ALTER DATABASE ADD STANDBY LOGFILE ('/u02/oradata/DBTST/standby_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u02/oradata/DBTST/standby_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u02/oradata/DBTST/standby_redo03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u02/oradata/DBTST/standby_redo04.log') SIZE 50M;
Start Apply Process
-------------------
Start the apply process on standby server.
>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
If you need to cancel the apply process, issue the following command.
>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE ADD STANDBY LOGFILE ('/u02/oradata/DBTST/standby_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u02/oradata/DBTST/standby_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u02/oradata/DBTST/standby_redo03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u02/oradata/DBTST/standby_redo04.log') SIZE 50M;
rm /u02/oradata/DBTST/standby_redo01.log
rm /u02/oradata/DBTST/standby_redo02.log
rm /u02/oradata/DBTST/standby_redo03.log
rm /u02/oradata/DBTST/standby_redo04.log
NOTES:
check trace files
check alertlog
check listenerlog
above example:
*****************************************************************
.BASH_PROFILE -DBSTY
-------------
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
export ORACLE_HOSTNAME=DEVSTDBY.FCICANADA.COM
export ORACLE_UNQNAME=DBSTY
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_SID=DBSTY
PATH=/usr/sbin:$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib;
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;
alias cd2b='cd $ORACLE_BASE'
alias cd2h='cd $ORACLE_HOME'
alias tns='cd $ORACLE_HOME/network/admin'
alias envo='env | grep ORACLE'
umask 022
.BASH_PROFILE -DBTST
-------------
[oracle@DEVPRMRY ~]$ more .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
export ORACLE_HOSTNAME=DEVPRMRY.FCICANADA.COM
export ORACLE_UNQNAME=DBTST
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_SID=DBTST
PATH=/usr/sbin:$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib;
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;
alias cd2b='cd $ORACLE_BASE'
alias cd2h='cd $ORACLE_HOME'
alias tns='cd $ORACLE_HOME/network/admin'
alias envo='env | grep ORACLE'
umask 022
*********************************************************************
listener.ora
-------------
[oracle@DEVPRMRY dbs]$ more /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = DBTST)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = DBTST)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DEVPRMRY.fcicanada.com)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@DEVPRMRY dbs]$
[oracle@DEVSTDBY DBTST]$ more /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
INBOUND_CONNECT_TIMEOUT_LISTENER = 0
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = DBSTY)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = DBTST)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DEVSTDBY.fcicanada.com)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
[oracle@DEVSTDBY DBTST]$
---------------------------------------------------------------------------------
TNSNAMES.ORA
-------------
[oracle@DEVSTDBY DBTST]$ more /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
DBTST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DEVPRMRY.fcicanada.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DBTST)
)
)
DBSTY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DEVSTDBY.fcicanada.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DBTST)
)
)
[oracle@DEVSTDBY DBTST]$
[oracle@DEVPRMRY dbs]$ more /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
DBTST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DEVPRMRY.fcicanada.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DBTST)
)
)
DBSTY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DEVSTDBY.fcicanada.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DBTST)
)
)
[oracle@DEVPRMRY dbs]$
------------------------------------------------------------------------------------------------------
PROTECTION MODE
---------------
SELECT protection_mode FROM v$database;
PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE
SQL>
-- Maximum Availability.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;
-- Maximum Performance.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=db11g_stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DB11G_STBY';
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
-- Maximum Protection.
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=DBSTY AFFIRM SYNC mandatory REOPEN=5 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DBSTY';
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;
ALTER DATABASE OPEN;
*****************DGMGRL SETUP STEPS ******************************************
------------------------------------------------------------------------------
1. PRIMARY
SQL> alter system set dg_broker_config_file1='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr1dbtst.dat' sid='*';
System altered.
SQL> alter system set dg_broker_config_file2='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr2dbtst.dat' sid='*';
System altered.
SQL> alter system set dg_broker_start=true sid='*';
System altered.
Add to primary listener
(SID_DESC =
(GLOBAL_DBNAME = DBTST_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = DBTST)
)
2 . standby
SQL> alter system set dg_broker_start=false sid='*';
System altered.
SQL> alter system set dg_broker_config_file1='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr1dbsty.dat' sid='*';
System altered.
SQL> alter system set dg_broker_config_file2='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/dr2dbsty.dat' sid='*';
System altered.
SQL> alter system set dg_broker_start=true sid='*';
System altered.
SQL>
Add to standby listener
(SID_DESC =
(GLOBAL_DBNAME = DBSTY_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = DBTST)
)
3. Create the broker configuration including a profile for the primary database.
DGMGRL> CREATE CONFIGURATION 'DGCONFIG' AS Primary Database is 'DBTST' Connect identifier is DBTST;
Configuration "DGCONFIG" created with primary database "DBTST"
DGMGRL> SHOW CONFIGURATION;
Configuration - DGCONFIG
Protection Mode: MaxPerformance
Databases:
DBTST - Primary database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
DGMGRL> show database DBTST
Object "dbtst" was not found
DGMGRL> show database 'DBTST'
Database - DBTST
Role: PRIMARY
Intended State: OFFLINE
Instance(s):
DBTST
Database Status:
DISABLED
DGMGRL>
4. DGMGRL> add database 'DBSTY' as connect identifier is DBSTY;
Database "DBSTY" added
DGMGRL> ENABLE CONFIGURATION;
Enabled.
DGMGRL> SHOW CONFIGURATION;
Configuration - DGCONFIG
Protection Mode: MaxPerformance
Databases:
DBTST - Primary database
Warning: ORA-16789: standby redo logs not configured
DBSTY - Physical standby database
Warning: ORA-16789: standby redo logs not configured
Fast-Start Failover: DISABLED
Configuration Status:
WARNING
DGMGRL>
----------------------------------------------------
Warning: ORA-16789: standby redo logs not configured
----------------------------------------------------
add standby redo logs to primary
ALTER DATABASE ADD STANDBY LOGFILE ('/u02/oradata/DBTST/standby_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u02/oradata/DBTST/standby_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u02/oradata/DBTST/standby_redo03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u02/oradata/DBTST/standby_redo04.log') SIZE 50M;
