This instruction is created for Oracle on Windows, it also applies to Unix/Linux installations. The difference will be mainly the folder path locations.
Master Instance: ProdServer:1521/DB1
Target Active Standby Instance: StandbyServer:1521/DB1STANDBY (new installation)
Preparation:
Run below query to check if ARCHIVING LOG, FORCE LOGGING is enabled on the Primary database, if not enable them.
select FORCE_LOGGING,log_mode from v$database;On Standby Server Install Oracle, recommend to choice “Software Only” option, since we will be creating this database as standby of the Primary.
- Create Standby Control file, PFILE, and PWD files for use to setup Standby server using below command on the Master Instance:
alter database create standby controlfile as 'C:\TEMP\standby.ctl';- Create PFILE from master server for Standby server
create pfile='C:\TEMP\initDB1STANDBY.ora' from spfile;- Copy the Standby Control file standby.ctl and Oracle Init file initDB1STANDBY.ora to the standby server
- Copy production PWD file “PWDDB1.ora” to the standby server as “PWDDB1STANDBY.ora” (This will allow you to use same sys credentials for the standby server), or use below command to create PWD file for the standby instance if you prefer to have different credentials for Standby instance, oracle PWD file naming conversion is PWD[SID].ora)
orapwd file=<fname> password=<password> entries=<users> force=<y/n>
ignorecase=<y/n> nosysdba=<y/n>- Edit tnsnames.ora on both master and standby server to setup TNS cross reference entries:
- On Master Server, add a TNS entry in tnsnames.ora to point Standby Server (StandbyServer:1521, SID: DB1STANDBY)
#################
TO_DB1STANDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = StandbyServer)(PORT = 1521))
(CONNECT_DATA =
(SID = DB1STANDBY)
)
)
#################
- On Standby server, setup a TNS Alias in tnsnames.ora file to point Primary Server (ProdServer, SID: DB1)
#################
TO_PRODDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ProdServer)(PORT = 1521))
(CONNECT_DATA =
(SID = DB1)
)
)
#################
- Create Standby Instance using the PFILE, Standby Control File and PWD file from Primary that were created from Primary Instance.
- Create data and log folder structure to match Primary server (this is optional, but preferred to simplify the setup and future maintenance)
mkdir F:\DBDATA\ORADATA\DB1\DATAFILE
mkdir G:\DBLog\ArcLogs
mkdir G:\DBLog\Recovery_Area
- On StandbyServer, listener.ora and tnsnames.ora files should be located in %ORACLE_HOME%\network\admin like below
##### listener.ora ######
DB1STANDBY =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = StandbyServer)(PORT = 1521))
)
)
SID_LIST_DB1STANDBY =
(SID_DESC =
(SID_NAME = DB1STANDBY)
(ORACLE_HOME = D:\ORACLE\product\12.2.0\dbhome_1)
)
)
##### tnsnames.ora ######
DB1STANDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = StandbyServer)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DB1STANDBY)
)
)
LISTENER_DB1STANDBY =
(ADDRESS = (PROTOCOL = TCP)(HOST = StandbyServer)(PORT = 1521))
# TNS reference to Primary Server
TO_PRODDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ProdServer)(PORT = 1521))
(CONNECT_DATA =
(SID = DB1)
)
)- Update the PFILE that was created from the master server, update information to match the settings of Standby Server, make sure to keep the “db_name” same as Master DB (for this sample case, the database name is: DB1), and “db_unique_name” should be changed to a unique name, we use DB1STANDBY in this sample case
*._compression_compatibility='12.2.0.1.0'
*._diag_adr_trace_dest='D:\ORACLE\diag\rdbms\db1standby\trace'
*.control_files='E:\DBDATA\ORADATA\DB1\STANDBY.CTL'
*.db_name='db1'
*.db_unique_name='db1standby'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=db1standby)'
*.local_listener='LISTENER_DB1STANDBY'
*.log_archive_dest_1='LOCATION=F:\DBLog\ArcLogs\DB1'
*.log_archive_dest_2='SERVICE=TO_PRODDB1 lgwr async noaffirm reopen=3'- Create the DB1STANDBY Instance and register the Service on StandbyServer with below command:
oradim -NEW -SID DB1STANDBY -STARTMODE MANUAL -PFILE D:\ORACLE\product\12.2.0\dbhome_1\database\initDB1STANDBY.oraThis will create a Oracle Service on the StandbyServer with Instance name OracleServiceDB1Standby:
- Start up DB1Standby Listener Service with below command
lsnrctl start DB1Standby- Confirm Service is up running the TNS Alias works both way
Login as sys to confirm both Primary and Standby Server as working
SQLPLUS sys as sysdba
Ping TSN alias between Primary and Standby
On primary, run command:
tsnping TO_DB1STANDBYOn Standby, run command:
tsnping TO_PRODDB1Confirm the TNS names are pingable like below:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = StandbyServer)(PORT = 1521)) (CONNECT_DATA = (SID = DB1STANDBY)))
OK (10 msec)
Add DB1STANDBY as the an Arch Log destination with command, for this case, we are using LOG_ARCHIVE_DEST_2, if you DEST_2 is already in use, use a different one that’s available:
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=TO_DB1STANDBY lgwr async noaffirm reopen=3'- Start up the Standby Instance in NOMOUNT state
On Standby server:
SQLPLUS sys/**PASS** as sysdba
STARTUP NOMOUNT;
- Then start replication on PRIMARY with RMAN
rman target sys/**PASWORD** auxiliary sys/**PASWORD**@TO_DB1STANDBY
This should successfully connect to Master database and using Standby database as Auxiliary. (Standby must be in “Not Mounted” state)
Run below command in RMAN:
duplicate target database for standby from active database nofilenamecheck;Once the duplicate completed, go to Standby Sever, connect as sys from SQLPLUS, and run below command:
SQLPLUS sys/**Password** as sysdba
alter database recover managed standby database cancel; (stop recover)
alter database open; (open database for read only)
alter database recover managed standby database disconnect;
/* This will switch the DB1STANDBY to an Active Standby */