[转]Oracle DataGuard学习、实验笔记

环境：WIN2003 SP1+ORACLE 9.2.0.1.0

主机名：mis-0738 mis-0739

IP:192.1.1.29 192.1.1.30

sid:test test

 

 

1、查看数据库是否工作在force_logging状态

SQL> select force_logging from v$database;

如状态为no，更改为yes

Sql>alter database force logging

2、查看并更改数据库为archivelogging模式

Sql>archive log list

如为非归档模式，更改：

Sql> alter system set log_archive_start=true;

Sql>alter system set log_archive_dest_1=’d:oracleoradatatestarchive(注：正式环境一定要将archivelog放于不同磁盘，以提高安全性和速度)

Sql>shutdown immediate;

Sql>startup mount;

Sql>alter database archivelog;

Sql>archive log list;

Sql>alter database open;

3、创建备用库的控制文件

Sql>alter database create standby controlfile as ‘d:bakstdctl.ctl’;

4、拷贝文件

Sql>shutdown immediate

将d:oracleoradatatest下所有所有文件、d:bakstdctl.ctl复制到mis-0739d:oracleoradatatest目录

5、在mis-0739建立备用库实例名：

cmd>orapwd file=d:oracleora92databasePWDtest.ora password=xxxxx entries=10

cmd>oradim –new –sid test –startmode manual

在备用库建立相应相应目录，如bdump、cdump、udump、archive、stdarchive，跟主库目录结构一样

6、编辑主库和备用库的listener.ora和tnsname.ora：

主库的listernet.ora最终如下：

# LISTENER.ORA Network Configuration File: d:oracleora92networkadminlistener.ora

 

# Generated by Oracle configuration tools.

 

 

 

LISTENER =

 

(DESCRIPTION_LIST =

 

(DESCRIPTION =

 

(ADDRESS_LIST =

 

(ADDRESS = (PROTOCOL = TCP)(HOST = mis-0738)(PORT = 1521))

 

)

 

)

 

)

 

 

 

SID_LIST_LISTENER =

 

(SID_LIST =

 

(SID_DESC =

 

(SID_NAME = PLSExtProc)

 

(ORACLE_HOME = d:oracleora92)

 

(PROGRAM = extproc)

 

)

 

(SID_DESC =

 

(GLOBAL_DBNAME = test)

 

(ORACLE_HOME = d:oracleora92)

 

(SID_NAME = test)

 

)

 

)

 

主库的tnsnames.ora最终如下：

 

# TNSNAMES.ORA Network Configuration File: d:oracleora92networkadmintnsnames.ora

 

# Generated by Oracle configuration tools.

 

 

 

TEST =

 

(DESCRIPTION =

 

(ADDRESS_LIST =

 

(ADDRESS = (PROTOCOL = TCP)(HOST = mis-0738)(PORT = 1521))

 

)

 

(CONNECT_DATA =

 

(SERVER = DEDICATED)

 

(SERVICE_NAME = test)

 

)

 

)

 

 

 

TESTST=

 

(DESCRIPTION =

 

(ADDRESS_LIST =

 

(ADDRESS = (PROTOCOL = TCP)(HOST = mis-0739)(PORT = 1521))

 

)

 

(CONNECT_DATA =

 

(SID = test)

 

(SERVER = DEDICATED)

 

)

 

)

 

 

 

备用库的listener.ora如下：

 

# LISTENER.ORA Network Configuration File: d:oracleora92networkadminlistener.ora

# Generated by Oracle configuration tools.

 

 

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = mis-0739)(PORT = 1521))

)

)

)

 

 

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = d:oracleora92)

(PROGRAM = extproc)

)

(SID_DESC =

(GLOBAL_DBNAME = test)

(ORACLE_HOME = d:oracleora92)

(SID_NAME = test)

)

)

备库的tnsnames.ora如下：

# TNSNAMES.ORA Network Configuration File: d:oracleora92networkadmintnsnames.ora

# Generated by Oracle configuration tools.

 

 

TEST =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = mis-0738)(PORT = 1521))

)

(CONNECT_DATA =

(SID = test)

(SERVER = DEDICATED)

)

)

 

 

TESTST=

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = mis-0739)(PORT = 1521))

)

(CONNECT_DATA =

(SID = test)

(SERVER = DEDICATED)

)

)

启用备用库的lsnrctl服务

cmd>lsnrctl start

在主库和备用库分别用tnsping test tnsping testst，应返回能够TSNPING正常结果。

7、调整从库的inittest.ora参数，最终结果如下：

*.aq_tm_processes=1

*.background_dump_dest='d:oracleadmintestbdump'

*.compatible='9.2.0.0.0'

*.control_files='d:oracleoradatateststdctl.ctl'

*.core_dump_dest='d:oracleadmintestcdump'

*.db_16k_cache_size=0

*.db_block_size=8192

*.db_cache_size=125829120

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='test'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'

*.fal_server='test'

*.fal_client='testst'

*.fast_start_mttr_target=300

*.hash_join_enabled=TRUE

*.instance_name='test'

*.java_pool_size=33554432

*.job_queue_processes=10

*.large_pool_size=8388608

*.log_archive_dest_1='LOCATION=d:oracleoradatatestarchive'

*.log_archive_dest_2='service=test mandatory reopen=60'

*.log_archive_format='%t_%s.dbf'

*.log_archive_start=true

*.log_archive_dest_state_1=enable

*.log_archive_dest_state_2=enable

remote_archive_enable=true

*.open_cursors=300

*.pga_aggregate_target=25165824

*.processes=150

*.query_rewrite_enabled='FALSE'

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_max_size=250677736

*.shared_pool_size=50331648

*.sort_area_size=524288

*.sql_trace=TRUE

*.standby_archive_dest='d:oracleoradatateststdarchive'

*.standby_file_management='AUTO'

*.star_transformation_enabled='FALSE'

*.timed_statistics=TRUE

*.undo_management='AUTO'

*.undo_retention=10800

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='d:oracleadmintestudump'

8、在mis-0739上启用备用库

Sql>startup nomount

Sql>alter database mount standby database

Sql>alter database recover managed standby database disconnect from session;

Sql>alter database force logging;

9、在主数据库d:oracleoradatatest建立stdarchive目录，调整主数据库相应参数：

SQL>alter system set log_archive_dest_2='service=testst mandatory reopen=60'

 

Sql>alter system set log_archive_dest_state_2=enable

 

Sql>alter system set remote_archive_enable=true

 

Sql>alter system set fal_server='testst'

 

Sql>alter system set fal_client='test'

 

Sql>alter system set standby_archive_dest='d:oracleoradatateststdarchive'

 

Sql>alter system set standby_file_management='AUTO'

 

Sql>create pfile from spfile

 

最终主数据库pfile内容如下：

 

*.aq_tm_processes=1

 

*.background_dump_dest='d:oracleadmintestbdump'

 

*.compatible='9.2.0.0.0'

 

*.control_files='d:oracleoradatatestCONTROL01.CTL','d:oracleoradatatestCONTROL02.CTL'

 

*.core_dump_dest='d:oracleadmintestcdump'

 

*.db_16k_cache_size=0

 

*.db_block_size=8192

 

*.db_cache_size=125829120

 

*.db_domain=''

 

*.db_file_multiblock_read_count=16

 

*.db_name='test'

 

*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'

 

*.fal_server='testst'

 

*.fal_client='test'

 

*.fast_start_mttr_target=300

 

*.hash_join_enabled=TRUE

 

*.instance_name='test'

 

*.java_pool_size=33554432

 

*.job_queue_processes=10

 

*.large_pool_size=8388608

 

*.log_archive_dest_1='LOCATION=d:oracleoradatatestarchive'

 

*.log_archive_dest_2='service=testst mandatory reopen=60'

 

*.log_archive_format='%t_%s.dbf'

 

*.log_archive_start=true

 

*.log_archive_dest_state_1=enable

 

*.log_archive_dest_state_2=enable

 

remote_archive_enable=true

 

*.open_cursors=300

 

*.pga_aggregate_target=25165824

 

*.processes=150

 

*.query_rewrite_enabled='FALSE'

 

*.remote_login_passwordfile='EXCLUSIVE'

 

*.sga_max_size=250677736

 

*.shared_pool_size=50331648

 

*.sort_area_size=524288

 

*.sql_trace=TRUE

 

*.standby_archive_dest='d:oracleoradatateststdarchive'

 

*.standby_file_management='AUTO'

 

*.star_transformation_enabled='FALSE'

 

*.timed_statistics=TRUE

 

*.undo_management='AUTO'

 

*.undo_retention=10800

 

*.undo_tablespace='UNDOTBS1'

 

*.user_dump_dest='d:oracleadmintestudump'

 

10、测试

在主数据库上执行

Sql>create table test ( a int);

Sql>insert into table test values(1);

Sql>commit;

Sql>alter system switch logfile;

观察主库的alter.ora日志，看是否正常写入归档日志文件和testst目标

观察d:oracleoradatatestarchive里应该有归档日志。

观察从库的alter.ora日志

观察从库的d:oracleoradatastdarcive里是否有跟主库archive目录下一样的归档日志。

11、主、备数据切换

1)将现主数据库切换到备用数据库

Sql> alter database commit to switchover to physical standby with session shutdown;

Sql>shutdown immediate;

Sql>startup nomount;

Sql> alter database mount standby database;

SQL> select name,protection_mode,database_role from v$database;

NAME PROTECTION_MODE DATABASE_ROLE

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

TEST MAXIMUM PERFORMANCE PHYSICAL STANDBY

Sql>alter database recover managed standby database disconnect from session;

2)打开mis-0739备用数据库

SQL> alter database commit to switchover to primary;

Sql>startup;

SQL> select name,protection_mode,database_role from v$database;

 

 

NAME PROTECTION_MODE DATABASE_ROL

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

TEST MAXIMUM PERFORMANCE PRIMARY

SQL> select * from test

 

 

A

----------

1

Sql>insert into test values(2);

Sql>commit;

Sql>alter system switch logfile;

观察mis-0739主库的alter.ora日志，看是否正常写入归档日志文件和testst目标

观察d:oracleoradatatestarchive里应该有归档日志。

观察mis-0738从库的alter.ora日志

观察mis-07338从库的d:oracleoradatastdarcive里是否有跟主库archive目录下一样的归档日志。

在mis-0738上将从库上切换回read only状态。

SQL> select * from t;

select * from t

*

ERROR at line 1:

ORA-01219: database not open: queries allowed on fixed tables/views only

SQL> alter database recover managed standby database cancel;

SQL>alter database open read only;

SQL> select * from test;

 

 

A

----------

1

2

可以看到现mis-0738的从数据库已经有相应纪录。

Sql>alter database recover managed standby database disconnect from session;

12、切换回mis-0738主数据库，将mis-0739换回备用数据库

1)在mis-0739主数据库上：

SQL> alter database commit to switchover to physical standby;

SQL> shutdown immediate;

SQL> startup nomount;

ORACLE

 

Total System Global Area 252779340 bytes

Fixed Size 453452 bytes

Variable Size 125829120 bytes

Database Buffers 125829120 bytes

Redo Buffers 667648 bytes

SQL> alter database mount standby database;

SQL> alter database recover managed standby database disconnect from session;

2)在mis-0738从库上执行以下操作：

SQL> alter database recover managed standby database disconnect from session;

 

Database altered.

 

SQL> alter database commit to switchover to primary;

 

Database altered.

 

SQL> shutdown immediate;

 

SQL>startup;

这样就完成了主、备用数据库的自由切换。