激活STANDBY做测试

最新推荐文章于 2023-07-09 10:17:08 发布

henrybai

最新推荐文章于 2023-07-09 10:17:08 发布

阅读量2.4k

点赞数

文章标签： archive database sql system parameters oracle

激活STANDBY做测试。

实现步骤：

1. 检查主从是否同步：

主库

ORACLE_SID=dgedb ORACLE_BASE=/u01/app/oracle ORACLE_HOME=/u01/app/oracle/product/ 10.2.0 /db_1 ORA_CRS_HOME=/u01/app/oracle/product/10.2.0/crs_1

SQL> archive log list

Database log mode Archive Mode Automatic

archival Enabled Archive destination /u01/app/oracle/oradata/dgedb/archive/

Oldest online log sequence 27

Next log sequence to archive 29

Current log sequence 29

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE OPEN_MODE ---------------- ---------- PRIMARY READ WRITE

从库

ORACLE_SID=dgfdb

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=/u01/app/oracle/product/ 10.2.0 /db_1

ORA_CRS_HOME=/u01/app/oracle/product/ 10.2.0 /crs_1

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/app/oracle/oradata/dgfdb/archive/

Oldest online log sequence 29

Next log sequence to archive 0

Current log sequence 29

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE OPEN_MODE

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

PHYSICAL STANDBY MOUNTED

2.检查日志应用情况

主库

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/app/oracle/oradata/dgedb/archive/

Oldest online log sequence 31

Next log sequence to archive 33

Current log sequence 33

从库

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/app/oracle/oradata/dgfdb/archive/

Oldest online log sequence 31

Next log sequence to archive 0

Current log sequence 33

3. 在从库上启用闪回库功能

SQL> alter system set db_recovery_file_dest_size= 2g scope=both; System altered.

SQL> alter system set db_recovery_file_dest='/u01/app/oracle/flashback' scope=both;

System altered.

SQL> show parameters db_recovery NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u01/app/oracle/flashback db_recovery_file_dest_size big integer 2G

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database flashback on;

Database altered.

4. 停止从库上的日志应用

SQL> alter database recover managed standby database cancel;

Database altered.

5. 建立一个恢复点

SQL> create restore point Before_App_Test guarantee flashback database;

Restore point created.

6. 归档主库日志

SQL> alter system archive log current;

System altered.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/app/oracle/oradata/dgedb/archive/

Oldest online log sequence 34

Next log sequence to archive 36

Current log sequence 36

7. 设置日志延迟传送

SQL> show parameters log_archive_dest_state_2

NAME TYPE VALUE

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

log_archive_dest_state_2 string ENABLE

SQL> show parameters log_archive_dest_2

NAME TYPE VALUE

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

log_archive_dest_2 string SERVICE=dgfdb LGWR ASYNC

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=dgfdb

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=DEFER;

System altered.

SQL> show parameters log_archive_dest_state_2

NAME TYPE VALUE

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

log_archive_dest_state_2 string DEFER

8. 激活从库

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;

Database altered.

9. 启动从库到MOUNT状态

SQL> STARTUP MOUNT FORCE ;

ORACLE instance started.

Total System Global Area 218103808 bytes

Fixed Size 1260984 bytes

Variable Size 134218312 bytes

Database Buffers 79691776 bytes

Redo Buffers 2932736 bytes

Database mounted.

10. 让从库在最大性能模式下运行

SQL> alter database set standby database to maximize performance;

Database altered.

11. 打开从库

SQL> ALTER DATABASE OPEN;

Database altered.

12. 开始测试

SQL> connect avargas/oracle

Connected.

SQL> create table myusers as select * from dba_users;

Table created.

SQL> insert into myusers select * from myusers;

22 rows created.

SQL> /

44 rows created.

SQL> /

88 rows created.

SQL> /

176 rows created.

SQL> /

352 rows created.

SQL> commit;

Commit complete.

SQL> select username from myusers where username like 'AVAR%';

USERNAME

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

AVARGAS

…

AVARGAS

32 rows selected.

13. 完成测试恢复从库身份，接收主库日志。

• 打开从库到MOUNT。

• 闪回数据库到恢复点。

• 恢复到从库身份接收日志。

14. 打开从库到MOUNT模式

SQL> startup mount force;

ORACLE instance started.

Total System Global Area 218103808 bytes

Fixed Size 1260984 bytes

Variable Size 142606920 bytes

Database Buffers 71303168 bytes

Redo Buffers 2932736 bytes

Database mounted.

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/app/oracle/oradata/dgfdb/archive/

Oldest online log sequence 1

Next log sequence to archive 2

Current log sequence 2

15. 闪回从库到恢复点

SQL> flashback database to restore point before_app_test;

flashback complete.

16. 恢复从库身份，准备接收日志。

SQL> alter database convert to physical standby;

Database altered.

17. 重启从库

SQL> STARTUP MOUNT FORCE ;

ORACLE instance started.

Total System Global Area 218103808 bytes

Fixed Size 1260984 bytes

Variable Size 150995528 bytes

Database Buffers 62914560 bytes

Redo Buffers 2932736 bytes

Database mounted.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/app/oracle/oradata/dgfdb/archive/

Oldest online log sequence 1

Next log sequence to archive 2

Current log sequence 2

18.接收日志

SQL> alter database recover managed standby database disconnect;

Database altered.

Thu Dec 27 17:30:17 2007 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT Thu Dec 27 17:30:17 2007 Attempt to start background Managed Standby Recovery process (dgfdb) MRP0 started with pid=28, OS id=27288

Thu Dec 27 17:30:18 2007 MRP0: Background Managed Standby Recovery process started (dgfdb) Managed Standby Recovery not using Real Time Apply parallel recovery started with 2 processes Clearing online redo logfile 1 /u01/app/oracle/oradata/dgfdb/redo01.log Clearing online log 1 of thread 1 sequence number 2 Thu Dec 27 17:30:24 2007 Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT Thu Dec 27 17:30:27 2007 Clearing online redo logfile 1 complete Clearing online redo logfile 2 /u01/app/oracle/oradata/dgfdb/redo02.log Clearing online log 2 of thread 1 sequence number 1 Clearing online redo logfile 2 complete Media Recovery Waiting for thread 1 sequence 35

19. 在主库上设置传送日志到从库

SQL> alter system set log_archive_dest_state_2=enable;

System altered.

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE OPEN_MODE

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

PRIMARY READ WRITE

SQL> alter system switch logfile;

System altered.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/app/oracle/oradata/dgedb/archive/

Oldest online log sequence 35

Next log sequence to archive 37

Current log sequence 37

20. 检查日志应用情况

SQL> select database_role, open_mode from v$database;

DATABASE_ROLE OPEN_MODE

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

PHYSICAL STANDBY MOUNTED

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u01/app/oracle/oradata/dgfdb/archive/

Oldest online log sequence 0

Next log sequence to archive 0

Current log sequence 37

在从库的日志上应该看到如下内容

Thu Dec 27 17:34:28 2007 Using STANDBY_ARCHIVE_DEST parameter default value as /u01/app/oracle/oradata/dgfdb/archive/ Redo Shipping Client Connected as PUBLIC -- Connected User is Valid RFS[1]: Assigned to RFS process 1352 RFS[1]: Identified database type as 'physical standby' Primary database is in MAXIMUM PERFORMANCE mode Thu Dec 27 17:34:28 2007 RFS LogMiner: Client disabled from further notification Primary database is in MAXIMUM PERFORMANCE mode RFS[1]: Successfully opened standby log 4: '/u01/app/oracle/oradata/dgfdb/SRL01.log' Thu Dec 27 17:34:32 2007 Fetching gap sequence in thread 1, gap sequence 35-35 Thu Dec 27 17:34:33 2007 Redo Shipping Client Connected as PUBLIC -- Connected User is Valid RFS[2]: Assigned to RFS process 1486 RFS[2]: Identified database type as 'physical standby' RFS[2]: Archived Log: '/u01/app/oracle/oradata/dgfdb/archive/1_35_633452428.dbf'

Thu Dec 27 17:34:39 2007 Redo Shipping Client Connected as PUBLIC -- Connected User is Valid RFS[3]: Assigned to RFS process 1686 RFS[3]: Identified database type as 'physical standby' Thu Dec 27 17:34:39 2007 Redo Shipping Client Connected as PUBLIC -- Connected User is Valid RFS[4]: Assigned to RFS process 1688 RFS[4]: Identified database type as 'physical standby' RFS[4]: Archived Log: '/u01/app/oracle/oradata/dgfdb/archive/1_36_633452428.dbf' Thu Dec 27 17:35:03 2007 Media Recovery Log /u01/app/oracle/oradata/dgfdb/archive/1_35_633452428.dbf Media Recovery Waiting for thread 1 sequence 37 (in transit)