top of page
Search

Data Grud

  • shiva ram
  • Oct 12, 2024
  • 5 min read

Steps to configure 11G Physical Standby Database


Oracle rdbms software is installed with one database(proddb) following is details


Primary DB_NMAE = proddb

Primary DB_UNIQUE_NAME=proddb IP - 172.168.1.7



On Physical standby only rdbms software (std will be my standby database)


Standby DB_NAME=proddb

Standby DB_UNIQUE_NAME=std IP 172.168.1.8


Required Parameters


DB_NAME -Must be same on Primary and on all standby

DB_UNIQUE_name -Must be different on Primary and all standby

LOG_ARCHIVE_CONFIG -This parameter includes db_unique_name which are the part of Datagurd configuration

LOG_ARCHIVE_DEST_n -Define local and remote archive log file location

LOG_ARCHIVE_DEST_STATE_n -Define state of archiving (ENABLE or DIFER)

REMOTE_LOGIN_PASSWORDFILE -Must be in EXCLUSIVE Mode

FAL_SERVER -Use for archivelog gap resolution (required only in physical standby server)

DB_FILE_NAME_CONVERT -required when directory structure is different datafile

LOG_FILE_NAME_CONVERT -required when directory structure is different logfile

STANDBY_FILE_MANAGEMENT -Keep auto to create file automatically on standby


Steps:


Perform following on Primary database


Make sure database is in archivelog mode


if note use following command to change to archive mode


SHUTDOWN IMMEDIATE

STARTUP MOUNT

ALTER DTAABASE ARCHIVELOG

ALTER DATABASE OPEN


SQL> select log_mode from v$database;


LOG_MODE

------------

ARCHIVELOG


Make sure database is in force logging mode


SQL> select force_logging from v$database;


FOR

---

NO


SQL> alter database force logging;


Database altered.


SQL> select force_logging from v$database;


FOR

---

YES



Verify db_name and db_UNIQUE_NAME of primary databsae


SQL> show parameter db_unique_name


NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_unique_name string proddb






Verify db_name and db_UNIQUE_NAME of primary databsae


SQL> show parameter db_name;


NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_name string proddb




Make DB_unique_name to be part of dataguard.(std service we will create


SQL> alter system set log_archive_config='DG_CONFIG=(proddb,std)';


System altered.


Set archivelog destinations

SQL> alter system set log_archive_dest_2='service=std valid_for=(online_logfiles,primary_role) db_unique_name=std';

SQL> show parameter log_archive_config


NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_config string DG_CONFIG=(proddb,std)

SQL>


SQL> alter system set log_archive_dest_state_2=enable;


System altered.


Set remote login password to exclusive


SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;


System altered.


SQL> show parameter remote_login;


NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

remote_login_passwordfile string EXCLUSIVE



set fal server and file name convert parameter incase if director structure is different in primary and standby database


SQL> alter system set fal_server=std;


System altered.


SQL> alter system set db_file_name_convert='std','proddb' scope=spfile;


System altered.


SQL> alter system set log_file_name_convert='std','proddb' scope=spfile;


System altered.


SQL> alter system set standby_file_management=AUTO;


System altered.



Now configure required service (proddb and std)


netmgr



Now backup primary database using rman


rman target=/

RMAN> backup database plus archivelog;


now create standby controlfile and pfile


SQL> alter database create standby controlfile as '/tmp/stdcontrol.ctl';


Database altered.


SQL> create pfile='/tmp/initstd.ora' from spfile;


File created.


Modify initstd.ora file


1)Change db_unique_name

2)change fal_server

3)change log_archive_dest_n

4)change location of controlfile



Create appropriate directory on physical standby and copy backupset archivelog, pfile,standby

controlfile and password file to physical standby database


mkdir -p /u01/app/oracle/admin/std/adump

mkdir -p /u01/app/oracle/oradata/std/

mkdir -p /u01/app/oracle/flash_recovery_area/std/


# standby controlfile to all location

scp stdcontrol.ctl oracle@172.168.1.8:/u01/app/oracle/oradata/

cp stdcontrol101.ctl /u01/app/oracle/flash_recovery_area/std/

cd /u01/app/oracle/flash_recovery_area/std/

mv stdcontrol101.ctl stdcontrol102.ctl



#Archivelogs and backups

scp -r PRODDB oracle@172.168.1.8:/u01/app/oracle/flash_recovery_area/a

scp initstd.ora oracle@172.168.1.8:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/


#remote login password file

scp orapwproddb oracle@172.168.1.8:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/

cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs/

mv orapwproddb orapwstd

On physical standby server

create service on physical standby database and update /etc/oratab file

netmgr


update /etc/oratab (std:/u01/app/oracle/product/11.2.0/dbhome_1:N)

vi /etc/oratab std:/u01/app/oracle/product/11.2.0/dbhome_1:N

Now start listener on both server

lsnrctl start

restore backup on standby

sqlplus '/as sysdba';


SQL*Plus: Release 11.2.0.1.0 Production on Fri Mar 13 02:36:17 2015


Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to an idle instance.


SQL> create spfile from pfile='/tmp/initstd.ora';


File created.


Restore backupfile


rman target=/

startup mount

Restore database;


RMAN> startup mount;


Oracle instance started

database mounted


Total System Global Area 1121554432 bytes


Fixed Size 1336008 bytes

Variable Size 738200888 bytes

Database Buffers 369098752 bytes

Redo Buffers 12918784 bytes


RMAN> restore database;


Starting restore at 13-MAR-15

Starting implicit crosscheck backup at 13-MAR-15

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=25 device type=DISK

Crosschecked 10 objects

Finished implicit crosscheck backup at 13-MAR-15


Starting implicit crosscheck copy at 13-MAR-15

using channel ORA_DISK_1

Finished implicit crosscheck copy at 13-MAR-15


searching for all files in the recovery area

cataloging files...

no files cataloged


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 /u01/app/oracle/oradata/std/system01.dbf

channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/std/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/std/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/std/users01.dbf

channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/std/example01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/PRODDB/backupset/2015_03_13/o1_mf_nnndf_TAG20150313T013515_bj3wcwb9_.bkp

channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/PRODDB/backupset/2015_03_13/o1_mf_nnndf_TAG20150313T013515_bj3wcwb9_.bkp tag=TAG20150313T013515

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:02:34

Finished restore at 13-MAR-15


RMAN>



Create standby redologs files on primary and standby database


SQL> alter database add STANDBY logfile

2 ('/u01/app/oracle/oradata/std/standby_redo01.log') size 50m;


Database altered.


SQL> alter database add standby logfile

2 ('/u01/app/oracle/oradata/std/standby_redo02.log') size 50m;


Database altered.


SQL> alter database add standby logfile

2 ('/u01/app/oracle/oradata/std/standby_redo03.log') size 50m;


Database altered.


SQL> alter database add standby logfile

2 ('/u01/app/oracle/oradata/std/standby_redo04.log') size 50m;


Database altered.



SQL> select member from v$logfile where type='STANDBY';


MEMBER

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/std/standby_redo01.log

/u01/app/oracle/oradata/std/standby_redo02.log

/u01/app/oracle/oradata/std/standby_redo03.log

/u01/app/oracle/oradata/std/standby_redo04.log


SQL> select member from v$logfile;


MEMBER

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/std/redo03.log

/u01/app/oracle/oradata/std/redo02.log

/u01/app/oracle/oradata/std/redo01.log

/u01/app/oracle/oradata/std/standby_redo01.log

/u01/app/oracle/oradata/std/standby_redo02.log

/u01/app/oracle/oradata/std/standby_redo03.log

/u01/app/oracle/oradata/std/standby_redo04.log


7 rows selected.





on Primary side


SQL> alter database add standby logfile

2 ('/u01/app/oracle/oradata/proddb/standby_redo01.log') size 50m;


Database altered.


SQL> alter database add standby logfile

2 ('/u01/app/oracle/oradata/proddb/standby_redo02.log') size 50m;


Database altered.


SQL> alter database add standby logfile

2 ('/u01/app/oracle/oradata/proddb/standby_redo03.log') size 50m;


Database altered.


SQL> alter database add standby logfile

2 ('/u01/app/oracle/oradata/proddb/standby_redo04.log') size 50m;


Database altered.


SQL> select member from v$logfile where type='STANDBY';


MEMBER

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/proddb/standby_redo02.log

/u01/app/oracle/oradata/proddb/standby_redo01.log

/u01/app/oracle/oradata/proddb/standby_redo03.log

/u01/app/oracle/oradata/proddb/standby_redo04.log



SQL> select member from v$logfile;


MEMBER

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/proddb/redo03.log

/u01/app/oracle/oradata/proddb/redo02.log

/u01/app/oracle/oradata/proddb/redo01.log

/u01/app/oracle/oradata/proddb/standby_redo02.log

/u01/app/oracle/oradata/proddb/standby_redo01.log

/u01/app/oracle/oradata/proddb/standby_redo03.log

/u01/app/oracle/oradata/proddb/standby_redo04.log



now start redo apply process on standby


SQL> alter database recover managed standby database disconnect from session;


Database altered.


Following command is use to stop redo apply process


alter database recover managed standby database cancel


on the primary server, check the latest archived redo log and force a log switch


SQL> alter session set nls_date_format='DD-MON-YYY HH24:MI:SS';


Session altered.


SQL> select sequence#,first_time,next_time from v$archived_log ORDER by sequence#;


SEQUENCE# FIRST_TIME NEXT_TIME

---------- ------------------- -------------------

4 07-MAR-015 14:38:58 07-MAR-015 15:08:52

5 07-MAR-015 15:08:52 07-MAR-015 15:10:29

6 07-MAR-015 15:10:29 07-MAR-015 17:47:00

7 07-MAR-015 17:47:00 07-MAR-015 23:27:08

8 07-MAR-015 23:27:08 08-MAR-015 01:20:11

9 08-MAR-015 01:20:11 08-MAR-015 01:20:30

10 08-MAR-015 01:20:30 08-MAR-015 01:22:36

11 08-MAR-015 01:22:36 08-MAR-015 01:22:51

12 08-MAR-015 01:22:51 08-MAR-015 18:01:00

13 08-MAR-015 18:01:00 08-MAR-015 19:30:36

14 08-MAR-015 19:30:36 09-MAR-015 19:32:51


SEQUENCE# FIRST_TIME NEXT_TIME

---------- ------------------- -------------------

15 09-MAR-015 19:32:51 09-MAR-015 21:49:28

16 09-MAR-015 21:49:28 09-MAR-015 21:49:31

17 09-MAR-015 21:49:31 09-MAR-015 21:49:33

18 09-MAR-015 21:49:33 09-MAR-015 21:51:39

19 09-MAR-015 21:51:39 11-MAR-015 21:53:30

20 11-MAR-015 21:53:30 13-MAR-015 00:49:28

21 13-MAR-015 00:49:28 13-MAR-015 01:08:34

22 13-MAR-015 01:08:34 13-MAR-015 01:10:05

23 13-MAR-015 01:10:05 13-MAR-015 01:34:48

24 13-MAR-015 01:34:48 13-MAR-015 01:36:47


21 rows selected.



SQL> alter system switch logfile;




SQL> select sequence#, first_time,next_time,applied from v$archived_log

2 ORDER BY sequence#;


SEQUENCE# FIRST_TIME NEXT_TIME APPLIED

---------- ------------------- ------------------- ---------

24 13-MAR-015 01:34:48 13-MAR-015 01:36:47 YES

25 13-MAR-015 01:36:47 13-MAR-015 03:58:27 YES

26 13-MAR-015 03:58:27 13-MAR-015 04:02:33 YES


SQL> desc v$database


SQL> select name,open_mode,database_role,db_unique_name,protection_mode from v$database;


NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME

--------- -------------------- ---------------- ------------------------ ------

PROTECTION_MODE

--------------------

PRODDB MOUNTED PHYSICAL STANDBY std

MAXIMUM PERFORMANCE



SQL> select name,open_mode,database_role,db_unique_name,protection_mode from v$database;


NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME

--------- -------------------- ---------------- ------------------------------

PROTECTION_MODE

--------------------

PRODDB READ WRITE PRIMARY proddb

MAXIMUM PERFORMANCE


SQL> SELECT protection_mode FROM v$database;


PROTECTION_MODE

--------------------

MAXIMUM PERFORMANCE


Recent Posts

See All
BCV_readwrite

select open_mode from v$database; spool rp.st select 'alter tablespace ' || tablespace_name || ' add tempfile ' || '''' || file_name...

 
 
 
CDR_PURGING

alter table INCMS.CIN_T_CCN_CDR drop partition TKT_2014_FEB26 update global indexes; alter table INCMS.CIN_T_CCN_CDR drop partition...

 
 
 

Comments

CONTACT US

Join our mailing list

bottom of page