Guenadi N Jilevski's Oracle BLOG

Create a physical standby database from cold backup (W2K) Oracle 9i back in 2004

Create a physical standby database from cold backup (W2K) back in (2004)

1. Ensure that the primary database is in archivelog mode.
2. Enable Forced Logging:

ALTER DATABASE FORCE LOGGING;

1. Increase the number for MAXLOGFILES (> 5) on the primary database if you plan to use standby redo log files.
2. Perform a cold backup of the primary database:

SELECT name FROM v$datafile

UNION ALL

SELECT member FROM v$logfile

UNION ALL

SELECT name FROM v$controlfile;

NAME

—————————————–

D:\ORACLE\ORADATA\DEMO\SYSTEM01.DBF

D:\ORACLE\ORADATA\DEMO\UNDOTBS01.DBF

D:\ORACLE\ORADATA\DEMO\EXAMPLE01.DBF

D:\ORACLE\ORADATA\DEMO\INDX01.DBF

D:\ORACLE\ORADATA\DEMO\TOOLS01.DBF

D:\ORACLE\ORADATA\DEMO\USERS01.DBF

D:\ORACLE\ORADATA\DEMO\OEM_REPOSITORY.DBF

D:\ORACLE\ORADATA\DEMO\LOGSTDBY.ORA

D:\ORACLE\ORADATA\DEMO\REDO01.LOG

D:\ORACLE\ORADATA\DEMO\REDO02.LOG

D:\ORACLE\ORADATA\DEMO\REDO03.LOG

D:\ORACLE\ORADATA\DEMO\CONTROL01.CTL

D:\ORACLE\ORADATA\DEMO\CONTROL02.CTL

D:\ORACLE\ORADATA\DEMO\CONTROL03.CTL

1. Create a controlfile for the standby database:

ALTER DATABASE CREATE STANDBY CONTROLFILE

AS ‘D:\ORACLE\ORADATA\DEMOP\SBCONTROL.CTL’;

1. Copy the backup files to the standby location. Copy controlfiles from the standby controlfile.
2. Set the initialization parameters for the standby datase:

*.db_filename_convert=(‘D:\oracle\oradata\demo’,’D:\oracle\oradata\demop’)

*.log_file_name_convert=(‘D:\oracle\oradata\demo’,’D:\oracle\oradata\demop’)

*.standby_file_management=AUTO

*.remote_archive_enable=TRUE

*.lock_name_space=demop (only if on the same host)

Change the instance name (not the database name) if standby is on the same host:

*.instance_name=’demop’

1. Create a windows service:

oradim –NEW –SID demop

1. Configure Listener for primary and standby database, create net service names in tnsnames.ora.
2. Enable Dead Connection Dedection on the standby system:

SQLNET.EXPIRE_TIME=2

1. Startup physical standby database:

STARTUP NOMOUNT;

ALTER DATABASE MOUNT STANDBY DATABASE;

1. Rename the datafiles and redo logfiles.
2. Create standby redo log file, if necessary. It is recommended to create standby redo logs also on the primary database for switchover. Size and number must be the same as redo log files on the primary.

ALTER DATABASE

ADD STANDBY LOGFILE

‘d:\oracle\oradata\demop\sblog1.dbf’

SIZE 102400k;

1. Initiate Log Apply Service:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

1. Enable archiving from the primary to the physical standby database:

ALTER SYSTEM SET log_archive_dest_2=’SERVICE=demop’ SCOPE=BOTH;

ALTER SYSTEM SWITCH LOGFILE;

1. Verifying the physical standby:

Primary:

SELECT sequence#, first_time, next_time

FROM v$archived_log ORDER BY sequence#;

SEQUENCE# FIRST_TIM NEXT_TIME

———- ——— ———

35 28-JAN-04 28-JAN-04

35 28-JAN-04 28-JAN-04

36 28-JAN-04 28-JAN-04

36 28-JAN-04 28-JAN-04

37 28-JAN-04 28-JAN-04

37 28-JAN-04 28-JAN-04

ALTER SYSTEM SWITCH LOGFILE;

SELECT sequence#, first_time, next_time

FROM v$archived_log ORDER BY sequence#;

SEQUENCE# FIRST_TIM NEXT_TIME

———- ——— ———

35 28-JAN-04 28-JAN-04

35 28-JAN-04 28-JAN-04

36 28-JAN-04 28-JAN-04

36 28-JAN-04 28-JAN-04

37 28-JAN-04 28-JAN-04

37 28-JAN-04 28-JAN-04

38 28-JAN-04 28-JAN-04

38 28-JAN-04 28-JAN-04

Standby:

SELECT sequence#, applied

FROM v$archived_log

ORDER BY sequence#;

SEQUENCE# APP

———- —

35 NO

36 YES

37 YES

38 YES

January 6, 2011 Posted by gjilevski | oracle | Leave a comment