create a physical standby db

最新推荐文章于 2024-11-06 20:49:14 发布

cilan8565

最新推荐文章于 2024-11-06 20:49:14 发布

阅读量89

点赞数

文章标签：数据库网络操作系统

create a physical standby database steps:

1.physical standby 是将primary database的archielog apply 到standby database,从而完成primary database 和 standby database的数据一致性

2.standby 有以下先决条件必须满足:

2.1 oracle的版本必须一致

2.2 操作系统必须一致(版本没有要求)

2.3 primary database 必须运行在archive mode下

2.4 数据库文件结构和名称必须一致,如果不一致,则必须在standby machine 做链接文件

2.5 primary database必须 force logging mode

3.下面以primary 和standby 分部在不同host上为例:

3.1 确认primary 运行在archive mode下:

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination C:oracleora92databasearchiveORCLC

Oldest online log sequence 9

Next log sequence to archive 11

Current log sequence 11

3.2 Enable database in FORCE LOGGING mode

在primary上运行下面命令:

SQL> alter database force logging;

Database altered.

3.3 将primary databae 的 datafile+logfile copy 到standby(目录结构以及文件名称必须保持一致,如果不一致,也可以在standby machine 做链接文件)

必须将primary database shutdown 然后copy

SQL> shutdown immediate;

copy datafile and redo logfile to standby database

3.4 restart primary database

SQL> startup;

3.5 在primary database运行如下命令生成controlfile for standby databae:

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'D:oracleoradatastbycontrol_sb01.ctl';

文件名称必须区别于primary controlfile,而且必须在最后一次全备份以后创建standby controlfile.

3.6 Create pfile from for standby database from the primary database

在primary database上运行如下命令:

SQL> CREATE PFILE='C:oracleora92databaseinitstby.ora' from spfile;

copy 到standby host

3.7 修改上步生成的pfile,需要修改如下参数:

db_name - Not modified. The same name as the primary database.

compatible - Not modified. The same as the primary database, 9.2.0.0.0.

control_files - Specify the path name and filename for the standby control file.

log_archive_start - Not modified. The same as the setting for the primary database, TRUE

standby_archive_dest - Specify the location of the archived redo logs that will be received from the primary database.

log_archive_dest_1 - Specify the location where the redo logs are to be archived on the standby system. (If a switchover occurs and this

instance becomes the primary database, then this parameter will specify the location where the online redo logs will be archived.)

standby_file_management - Set to AUTO.

remote_archive_enable - Set to TRUE.

Also change the values of the parameters background_dump_dest, core_dump_dest and user_dump_dest to specify location of the standby database.

3.8 如果是windows操作系统,还必须做如下步骤生成 windows service

在standby host上运行:

C:>oradim -new -sid stby -intpwd stby -startmode manual

3.9 配置primary和standby数据库的listener.ora & tnsnames.ora 如下:

primary如下:

listener.ora:

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = primary_host)(PORT = 1521))

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = D:oracleora92)

(PROGRAM = extproc)

(SID_DESC =

(GLOBAL_DBNAME = orcl)

(ORACLE_HOME = D:oracleora92)

(SID_NAME = orcl)

)

tnsnames.ora:

primary =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = primary_host)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

standby =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = standby_host)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

standby如下:

listener.ora:

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = standby_host)(PORT = 1521))

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = D:oracleora92)

(PROGRAM = extproc)

(SID_DESC =

(GLOBAL_DBNAME = orcl)

(ORACLE_HOME = D:oracleora92)

(SID_NAME = orcl)

)

tnsnames.ora:

standby =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = standby_host)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

primary =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = primary_host)(PORT = 1521))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

)

Restart the listeners using LSNRCTL utility.

% lsnrctl stop

% lsnrctl start

3.10 启动standby database:

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup pfile='C:oracleora92databaseinitstby.ora' nomount;

ORACLE instance started.

Total System Global Area 135338868 bytes

Fixed Size 453492 bytes

Variable Size 109051904 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

SQL> alter database mount standby database;

Database altered.

3.11 config primary archive log can archiving to standby site:

在primary database上修改如下参数:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby' SCOPE=BOTH;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;

System altered.

3.12 Initiate Log apply services

在standby database上运行如下命令:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL> alter database open read only;

alter database open read only

ERROR at line 1:

ORA-01154: database busy. Open, close, mount, and dismount not allowed now

SQL> recover managed standby database cancel;

Media recovery complete.

在primary database上运行:

SQL> alter system switch logfile;

Database altered.

在standby database上运行:

SQL> alter database open read only;

Database altered.

3.13 Verifying the Standby Database :

在standby database上运行:

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME

2> FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIME NEXT_TIME

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

14 25-APR-05 16:50:34 25-APR-02 16:50:42

15 25-APR-05 16:50:42 25-APR-02 16:50:47

16 25-APR-05 16:50:47 25-APR-02 16:51:52

在primary database上运行:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

在standby database上运行:

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME

2> FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIME NEXT_TIME

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

14 25-APR-05 16:50:34 25-APR-02 16:50:42

15 25-APR-05 16:50:42 25-APR-02 16:50:47

16 25-APR-05 16:50:47 25-APR-02 16:51:52

17 25-APR-05 16:51:52 25-APR-02 17:34:00

在primary database上运行:

SQL> create table test as selct * from dba_objects;

SQL> alter system switch logfile;

在standby database上运行:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL> recover managed standby database cancel;

Media recovery complete.

SQL> alter database open read only;

Database altered.

SQL> desc test;

Name Null? Type

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

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

OWNER VARCHAR2(30)

OBJECT_NAME VARCHAR2(128)

SUBOBJECT_NAME VARCHAR2(30)

OBJECT_ID NUMBER

DATA_OBJECT_ID NUMBER

OBJECT_TYPE VARCHAR2(18)

CREATED DATE

LAST_DDL_TIME DATE

TIMESTAMP VARCHAR2(19)

STATUS VARCHAR2(7)

TEMPORARY VARCHAR2(1)

GENERATED VARCHAR2(1)

SECONDARY VARCHAR2(1)

ok,表已经apply到standby database.

[@more@]

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

转载于:http://blog.itpub.net/75730/viewspace-1000878/