The Complex Oracle Data Guard in less than 15 easy steps. Create a physical standby database in less than an hour(well it’s technically possible to do it in 8 minutes, if you don’t include the time it takes to copy your backup from primary to standby ).
1.edit the spfile of primary
alter system set log_archive_dest_2=’service=nkstdby’
alter system set log_archive_dest_state_2=enable
2.edit pfile for standby :
–change the location for standby control file if necessary
*.standby_archive_dest=/u01/standby/archive create dir
*.log_archive_format=nk_%t_%s.dbf
*.log_archive_start=true
*.log_archive_dest_1=’LOCATION=/u01/standby/archlocal’ create dir
*.log_archive_dest_2=’service=nk’
*.log_archive_dest_state_2=enable
*.remote_archive_enable=true
*.fal_server=nk
*.fal_client=nkstdby
3.edit the tns file on primary to add service for standby
nkstdby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.31.46)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = nk)
)
)
4.tnsping standby
5.configure format /location etc for rman bkp:
go to rman:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/u01/nk/%F’;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/u01/nk/nk_full_%s%D%M%Y’;
6.backup incremental level 0 database include current controlfile for standby;
7.scp the rman backup on the standby:
scp -r * oracle@172.16.31.46:/u01/nk/ –ensure the same path for rman backup on the standby
scp initfile and orapwd to the standby
scp initnk1.ora orapwnk oracle@172.16.31.46:/Oracle/app/oracle/product/9.2.0/dbs/
8.On standby:
9.create same dir for rman backup on the standby machine
create location for control file multiplexing(ensure dirs exists)
make dir on standby machine bdump,cdump,udump and archive
10.On standby add tns entry for primary
nk =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.31.47)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = nk)
)
)
tnsping on both primary and standby
11.add the entry in the listener in the standby:
(SID_DESC =
(GLOBAL_DBNAME = nk)
(ORACLE_HOME = /Oracle/app/oracle/product/9.2.0)
(SID_NAME =nk)
lsnrctl stop and start
12.sql prompt:
SQL> create spfile from pfile;
SQL> startup nomount
SQL> exit
13.Go to rman :
RMAN> connect auxiliary /
RMAN> connect target sys/sys@nk;
***switch logfile on the primary to avoid error
RMAN> duplicate target database for standby dorecover nofilenamecheck;
RMAN> exit
14.go to the sql on standby:
alter database recover managed standby database disconnect from session;
select process,status from v$managed_standby;
select count(*) from v$archived_log where applied=’NO’;
select sequence#,first_time,next_time,applied from v$archived_log order by sequence#;
And Voila 🙂
