 |
 |
*****************************************************************************
* Description: Knowledge of setting up Oracle physical standby via DataGuard
* Date: 02:46 PM EST, 12/26/2017
*****************************************************************************
<1> Pre-work:
|
|__ o. Setup 2 Windows 2012 R2 x86-64 m3-xlarge instances on AWS, and open port HTTP/HTTPS/RDP/ICMP/ORACLE_DB_1521 in security group.
o. Enable ActiveX and suppress security limitation on IE internet option.
o. Download and install Oracle RDMBS Database Software version 12.2.0.1 Enterprise Edition under AWS Windows instance default user "Administratior".
o. Ping each other via both public and private IP.
o. Create OS user "app" without adding into any groups for 12c ORACLE_SOFTWARE and ORACLE_HOME installation only.
o. Create OS user "oracle", and addding into "Administratior" and "ORADBA" group for admin purpose.
o. Adding new inbound rule of port 1521 on Windows intergrated firewall.
o. Create DATABASE on primary server.
o. Create LISTENER on standby server.
|
|__ a) Ensure that DR database registered with local listener [ SQL> alter database set local_listener=''; ]
b) Value of "local_listener" within primary database pfile is "LISTENER_EMERALIT", which needs to be modified to "LISTENER" accordingly.
<2> Backup primary database with archive logs and standby dedicated controlfile:
|
|__ RMAN> run {
backup database format 'C:\oracle\backup\bkp_%U';
backup archivelog all format 'C:\oracle\backup\arc_%U';
backup current controlfile for standby format='C:\oracle\backup\%d_%t_%s_%p_%U_stby_ctl_full.ctl';
}
<3> Create pfile from spfile of primary database. And, transferring all the backup to standby server:
|
|__ SQL> create pfile='c:\oracle\backup\pfile_emeralit.ora' from spfile;
|
|__ o. For parameter "fast_recovery_area", you need to create the physical folder manually on standby server in advanced after moving the pfile to standby server.
|
|__ o. For parameter "control_files", you need to modify the value to expected location on standby server for control file restore in next step.
<4> Switch primary database into force logging mode:
|
|__ SQL> ALTER DATABASE force logging;
|
|__ SQL> SELECT force_logging FROM v$database;
<5> On standby server, create Oracle database Windows service:
|
|__ CMD> ORADIM -new -sid emeralit -SRVC OracleServiceEmeralit -STARTMODE auto -SRVCSTART system -PFILE C:\oracle\PFILE_EMERALIT.ORA
Enter password for Oracle service user: The password of user "app", which owns Oracle Software Installtion in pre-work section. New feature in Oracle 12c.
Instance created.
<6> Start standby database in nomount mode:
|
|__ CMD> set oracle_sid=emeralit
|
|__ SQL> startup nomount pfile='C:\oracle\PFILE_EMERALIT.ORA';
<7> Restore control file on standby server. Location is indicated in pfile:
|
|__ RMAN> restore standby controlfile from 'C:\oracle\EMERALIT_963626694_3_1_03SMVHM6_1_1_STBY_CTL_FULL.CTL';
<8> Swithc standby database into "standby mount" mode:
|
|__ SQL> alter database mount standby database;
<9> Catalog backup on standby server:
|
|__ RMAN> catalog start with 'C:\oracle';
<10> Restore database on standby server:
|
|__ RMAN> run {
# The data file location is different between primary and standby
set newname for datafile 1 to 'C:\APP\APP\ORADATA\EMERALIT\SYSTEM01.DBF';
set newname for datafile 3 to 'C:\APP\APP\ORADATA\EMERALIT\SYSAUX01.DBF';
set newname for datafile 5 to 'C:\APP\APP\ORADATA\EMERALIT\UNDOTBS01.DBF';
set newname for datafile 7 to 'C:\APP\APP\ORADATA\EMERALIT\USERS01.DBF';
# The temp file location is different between primary and standby
set newname for tempfile 1 to 'C:\APP\APP\ORADATA\EMERALIT\TEMP01.DBF';
restore database;
# Renaming data and temp file name registered in database
switch datafile all;
switch tempfile all;
}
<11> Recover database on standby server:
|
|__ RMAN> run {
recover database;
}
<12> Switch redo log name:
|
|__ SQL> ALTER DATABASE RENAME FILE 'C:\APP\OINSTALL\ORADATA\EMERALIT\REDO01.LOG' to 'C:\APP\APP\ORADATA\EMERALIT\REDO01.LOG';
ALTER DATABASE RENAME FILE 'C:\APP\OINSTALL\ORADATA\EMERALIT\REDO02.LOG' to 'C:\APP\APP\ORADATA\EMERALIT\REDO02.LOG';
ALTER DATABASE RENAME FILE 'C:\APP\OINSTALL\ORADATA\EMERALIT\REDO03.LOG' to 'C:\APP\APP\ORADATA\EMERALIT\REDO03.LOG';
<13> Config tnsname.ora entries, and verify TNS names between primary/standby via tnsping:
|
|__ CMD> tnsping standby_DB --> Windows intergrated firewall needs to configure allow port 1521 open, if TNS timeout error show up.
tnsping primary_DB --> Windows intergrated firewall needs to configure allow port 1521 open, if TNS timeout error show up.
<14> Indicating remote archive log shipping location in primary db:
|
|__ SQL> alter system set log_archive_dest_9='SERVICE=emeralit ARCH ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=emeralit';
SQL> alter system set log_archive_dest_state_9='enable' scope=both sid='*';
<15> Verify following parameters value assigned with TNS name in standby database:
|
|__ SQL> alter system set fal_client='emeralit';
| alter system set fal_server='emeralit';
|
|__ SQL> show parameter fal;
NAME TYPE VALUE
------------------- ----------- -------------
fal_client string emeralit
fal_server string emeralit
<16> Copy database password file to $ORACLE_HOME/database folder on standby server.
<17> Start MRP in standby DB:
|
|__ SQL> alter database recover managed standby database disconnect from session; ----> Start MRP
SQL> alter database recover managed standby database cancel; ----> Stop MRP.
SQL> alter database open; ----> If MRP stop, the standby database can be altered to open for readonly purpose.
<18> Check shipped and applied archived log sequence in standby database:
|
|__ o. Below queries are used for comparing max sequence number between primary and standby database to ensure DG working.
|
|__ SQL> select thread#, max(sequence#) "Last Seq Generated" from v$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;
|
|__ SQL> select thread#, max(sequence#) "Last Seq Received" from v$archived_log val, v$database vdb where val.resetlogs_change#=vdb.resetlogs_change# group by thread# order by 1;
|
|__ SQL> select thread#, max(sequence#) "Last Seq Applied" from v$archived_log val, v$database vdb
where val.resetlogs_change#=vdb.resetlogs_change# and applied='YES' group by thread# order by 1;
<19> Checking Dataguard process status:
|
|__ SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;
PROCESS STATUS
--------- ------------
ARCH CLOSING
RFS IDLE
MRP0 WAIT_FOR_LOG
Reference:
|
|__ o. https://docs.oracle.com/cd/B19306_01/server.102/b14239/concepts.htm#g1049956
|
|__ o. https://oracle-base.com/articles/11g/data-guard-setup-11gr2
Your Comments