Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA 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 | oracle

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: