How to create an Oracle 8i standby database

CREATING A STANDBY DATABASE:[@more@]

Doc ID: Note:70233.1 Type: REFERENCE
Last Revision Date: 04-MAY-2004 Status: PUBLISHED

This document will provide the reader with basic step-by-step instructions on
how to create a Standby Database on Oracle 8i (8.1.x). If you are using Windows,
please also perform appropriate step also shown in this Note. For additional
explanation or information on any of these steps, please see the references
listed at the end of the document.

CREATING A STANDBY DATABASE:
=============================

1) Ensure the Primary Database is in ARCHIVELOG Mode:

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination
Oldest online log sequence x
Current log sequence y
SQL> alter database close;

Database altered.

SQL> alter database archivelog;

Database altered.

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

Modify the Primay database init.ora so that log_archive_start=true and
specify a Archivelog Desintaion (log_archive_dest_1=), thenrestart
the instance. Verify that database is in archive log mode and that automatic
archiving is enabled.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination
Oldest online log sequence x
Next log sequence to archive y
Current log sequence y

2) Create a full Backup of the Primary Database:

You can use an existing backup of the Primary database as long as you have the
archive logs that have been generated since that backup. You may also take a
hot backup as long as you have all archive logs through the end of the backup
of the last tablespace. To create a cold backup do the following:

SQL>
SQL> select name from v$datafile;

NAME
----------------------------------------------------------------------
/export/home/oracle/temp/oracle/data/sys.dbf

SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

Create a backup of all datafiles and online redo logs using an OS command or
utility. A backup of the online redo logs is necessary to facilitate switchover.

Once complete startup the instance:

SQL> startup
ORACLE instance started.

Database mounted.
Database opened.

3) Connect to your Primary Database and create the Stanby Controlfile:

ALTER DATABASE CREATE STANDBY CONTROLFILE AS ;

4) Copy Backup files to the Standby Host:

Copy the backup datafiles, standby controlfile, all available archived redo logs,
and online redo logs from the primary site to the standby site. If you use FTP to
copy the files, please mind to use BinaryMode (bin) for transfering the Files.

If the standby is on a separate site with the same directory structure as the
primary database then you can use the same path names for the standby files as
the primary files. In this way, you do not have to rename the primary datafiles
in the standby control file. If the standby is on the same site as the primary
database, or the standby database is on a separate site with a different
directory structure the you must rename the primary datafiles in the standby
control file after copying them to the standby site. This can be done using
the db_file_name_convert and log_file_name_convert INIT.ORA-parameters or by
manually using the ALTER DATABASE statements.

5) Set the initialization parameters for the primary database:

Primary INIT.ORA:

log_archive_dest_1='LOCATION= log_archive_dest_2='SERVICE=standby reopen=60'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
log_archive_format=%t_%s.dbf
log_archive_start=true

Create the standby INIT.ORA and set the initialization parameters for the
standby database. Depending on your configuration, you may need to set filename
conversion parameters.

Standby INIT.ORA:

log_archive_dest_1='LOCATION='
log_archive_dest_state_1=enable
log_archive_format=%t_%s.dbf (MUST be the same as on Primiary !!)
log_archive_start=true
standby_archive_dest=

NOTE: In the above example db_file_name_convert and log_file_name_convert are
not needed as the directory structure on the two hosts are the same. If the
directory structure is not the same then setting of these parameters is
recommended. Please reference notes 47325.1 and 47343.1 for further
information.

WINDOWS NT/2000: Due to Bug 951554, the names of the locations specified in
these Parameters MUST be in UPPERCASE. Otherwise, you will get an
ORA-01157: cannot identify/lock data file
when you try to recover.
If the directory structure is different you may also have to set
on your Standby INIT.ORA:

control_files=
background_dump_dest=
user_dump_dest=

If you plan to run Primary and Standby database on the same Host, you must set the
LOCK_NAME_SPACE-Parameter in the Standby-INIT.ORA to avoid Memory-conflicts in your
System-Memory.
You can set this Parameter to any up to 8 alphanumeric characters, mostly it is set
to standby or the service/instance name.
Please reference Note 47340.1 for further information.

6) Configure Networking Components:

Create a Net Service Name for the Standby database that the Primary Database is
able to connect to the Standby Database. You may also configure a Listener on
the Standby Host.

Example TNSNAMES.ORA (on Primary Host):

Standby=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
)
(CONNECT_DATA =
(SID = STANDBY)
(SERVER = DEDICATED)
)
)

Example LISTENER.ORA (on Standby Host):

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = STANDBY.world)
(ORACLE_HOME = $ORACLE_HOME)
(SID_NAME = Standby)
)
)

7) WINDOWS NT/2000 only: Create a Windows Service for the new Standby Database

Before you startup the Standby instance, you have to create and start a
Windows Service for this Standby Database. You can create this Service
using the ORADIM-utility:

C:>ORADIM -NEW -SID -INTPWD -MAXUSERS n

Please see Note 61621.1 for datails about ORADIM.

8) Start the Standby Instance and mount the Standby Database:

sqlplus /nolog
SQL*Plus: Release 8.1.7.0.0 - Production on Mi Okt 9 16:07:31 2002

(c) Copyright 2000 Oracle Corporation. All rights reserved.

SQL> connect internal

Connected to an idle instance.

SQL> startup nomount pfile=
ORACLE instance started.

SQL> alter database mount standby database;

Database altered.

SQL>

9) Adjust Filenames if necessary:

If necessary, manually change the names of the primary datafiles and
redo logs in the standby control file for all files not automatically
renamed using DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT as noted
in step 5. Datafile names can be changed on the standby at a mounted
state with the following syntax:

SQL> alter database rename file
2 'old path/filename'
3 to
4 'new path/filename';

10) Start Listener:

Perform a LSNRCTL STOP and LSNRCTL START on Standby Site to make the
Changes take effect.

11) Bounce Primary Database for Change of INIT.ORA-Parameters in 5) to
take effect.

12) Verify that automatic archiving is occurring:

On the Primary database switch a log and verfy that it has been shipped
properly using the v$archive_dest view.

SQL> alter system switch logfile;

System altered.

SQL> select status, error from v$archive_dest where dest_id=2;

STATUS ERROR
--------- -------------------------------------------------------
VALID

SQL>

13) Optionally place Standby Database in Managed Recovery:

SQL> recover managed standby database;

Your session will now just sit there. Logs will be applied as soon as they
are transfered from the Primary Instance (see appropriate entries in the
ALERT.LOG).

14) Stop the Managed Recovery:

Open a new Session and connect to the Standby Database:

SQL> recover managed standby database cancel;

Media Recovery canceled.

Optionally, you can now open the Database in READ-ONLY Mode:

SQL> alter database open read only;

Database altered.


SOME NOTES:
===========

- To keep the standby current, manually copy archived redo logs
as they are generated on the primary, then apply them to the standby.
(This can be accomplished best by a batch job).

- Any physical changes that occur on the primary must be accomplished
on the standby as well. New datafiles are added to the standby
controlfile automatically (via redo logs). If the standby database can't
find a datafile, recovery will stop with an ORA-1157 . Use
ALTER DATABASE CREATE DATAFILE
or physically copy the datafile to the standby system.

- To maintain (only) a subset of the primary database, ALTER...OFFLINE
unneeded datafiles on the standby system; Oracle will just maintain
the online files. Offlined files are not recovered.

- You can add or drop log files on the primary without affecting the
standby.

- If an additional thread is enabled on the primary (using MTS or OPS)
you must recreate the standby controlfile (see (2) above).

- If ALTER DATABASE CLEAR UNARCHIVED LOGFILE is performed
on the primary, the standby is rendered invalid. This
also occurs if ALTER DATABASE OPEN RESETLOGS is done. This
is because the standby doesn't then have the archivelogs it needs
to continue.

- If you do an ALTER DATABASE CREATE CONTROLFILE on the primary,
you must also create a new controlfile for the standby.
First, shut down the standby. Then do an ALTER DATABASE ARCHIVE LOG CURRENT
so the standby will be current on redo. (Then copy the files over to
the standby site). Then, at the standby site, STARTUP NOMOUNT,
then ALTER DATABASE MOUNT STANDBY DATABASE. Finally,
RECOVER STANDBY DATABASE.

REFERENCES
==========

Oracle8i Standby Database Concepts and Administration Release 2(8.1.6) PartNumber A76995-01
Oracle8i Administrator's Guide Release 2 (8.1.6) for Windows NT, Part Number A73008-01
Oracle8i Documentation Addendum Release 3 (8.1.7) Part Number A85455-01
Oracle8i Reference Release 2 (8.1.6) Part Number A76961-01
Oracle8i SQL Reference Release 3 (8.1.7) Part Number A85397-01
Note 61621.1: WINNT/WIN2000: Recreating Oracle Services and Instances from the Command Line

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7567263/viewspace-976965/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7567263/viewspace-976965/