DATAGUARD手記（READONLY驗證REAL-TIME DML）（五）

http://zhouwf0726.itpub.net/post/9689/395349
http://zhouwf0726.itpub.net/post/9689/395350
http://zhouwf0726.itpub.net/post/9689/395437
http://zhouwf0726.itpub.net/post/9689/401874
http://zhouwf0726.itpub.net/post/9689/403401

Open Database read only to verify transmission redolog in Real-Time model

--只讀方式開啟資料庫來驗證時時應用日誌模式

一、READ ONLY開啟資料庫

--主庫

oracle:db-tfusn2cn-a-r1.amazon.com: /opt/app/oracle> sqlplus "/as sysdba"

SQL*Plus: Release 10.1.0.4.0 - Production on Thu Oct 4 14:09:11 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options

SYS.YUDONG>alter system switch logfile;

System altered.

SYS.YUDONG>conn zwf/zwf
Connected.

1 row selected.

ZWF.YUDONG>create table t_1004(id number);

Table created.

ZWF.YUDONG>insert into t_1004 values(100);

1 row created.

ZWF.YUDONG>commit;

Commit complete.

--從庫

SYS.YUDONG>SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG;

MAX(SEQUENCE#)
--------------
962

1 row selected.

.>alter database recover managed standby database cancel;

Database altered.

.>alter database open read only;

Database altered.

.>CONN ZWF/ZWF
Connected.

1 row selected.

ZWF.YUDONG>SELECT TNAME FROM TAB; --沒有時時建立t_1004

TNAME
------------------------------
TEST
T1
T2
T
ABC

5 rows selected.

ZWF.YUDONG>CONN /AS SYSDBA
Connected.

1 row selected.

SYS.YUDONG>alter database recover managed standby database disconnect from session;

Database altered.

--主庫

SYS.YUDONG>ALTER SYSTEM SWITCH LOGFILE;

System altered.

SYS.YUDONG>SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG;

MAX(SEQUENCE#)
--------------
963

1 row selected.

--從庫

SYS.YUDONG>SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG;

MAX(SEQUENCE#)
--------------
963

1 row selected.

SYS.YUDONG>alter database recover managed standby database cancel;

Database altered.

SYS.YUDONG>alter database open read only;

Database altered.

SYS.YUDONG>CONN ZWF/ZWF
Connected.

1 row selected.

ZWF.YUDONG>SELECT TNAME FROM TAB; --切換日誌後建立該表

TNAME
------------------------------
TEST
T1
T2
T
ABC
T_1004

6 rows selected.

ZWF.YUDONG>SELECT * FROM T_1004;

ID
----------
100

1 row selected.

二、切換到最到保護模式

--登入從庫：

SYS.YUDONG>alter database recover managed standby database cancel;

Database altered.

SYS.YUDONG>alter database add standby logfile group 5('/opt/app/oracle/standbylog/standlog5') size 134217728;

Database altered.

SYS.YUDONG>alter database add standby logfile group 6('/opt/app/oracle/standbylog/standlog6') size 134217728;

Database altered.

SYS.YUDONG>alter database add standby logfile group 7('/opt/app/oracle/standbylog/standlog7') size 134217728;

Database altered.

SYS.YUDONG>alter database add standby logfile group 8('/opt/app/oracle/standbylog/standlog8') size 134217728;

Database altered.

SYS.YUDONG>alter database add standby logfile group 9('/opt/app/oracle/standbylog/standlog9') size 134217728;

Database altered.

SYS.YUDONG>SELECT * FROM V$STANDBY_LOG;

GROUP# DBID THREAD# SEQUENCE# BYTES USED ARC STATUS FIRST_CHANGE#
---------- ---------------------------------------- ---------- ---------- ---------- ---------- --- ---------- -------------
FIRST_TIME LAST_CHANGE# LAST_TIME
------------------- ------------ -------------------
5 UNASSIGNED 0 0 134217728 512 YES UNASSIGNED 0
0

6 UNASSIGNED 0 0 134217728 512 YES UNASSIGNED 0
0

7 UNASSIGNED 0 0 134217728 512 YES UNASSIGNED 0
0

8 UNASSIGNED 0 0 134217728 512 YES UNASSIGNED 0
0

9 UNASSIGNED 0 0 134217728 512 YES UNASSIGNED 0
0

5 rows selected.

--登入主庫：

SYS.YUDONG>select protection_mode from v$database;

PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE

1 row selected.

SYS.YUDONG>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS.YUDONG>STARTUP MOUNT;
ORACLE instance started.

Total System Global Area 490733568 bytes
Fixed Size 779456 bytes
Variable Size 406985536 bytes
Database Buffers 81920000 bytes
Redo Buffers 1048576 bytes
Database mounted.

SYS.YUDONG>ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;

Database altered.

SYS.YUDONG>ALTER DATABASE OPEN;

Database altered.

SYS.YUDONG>SELECT PROTECTION_MODE FROM V$DATABASE;

PROTECTION_MODE
--------------------
MAXIMUM PROTECTION

1 row selected.

--登入到從庫：

SYS.YUDONG>alter database recover managed standby database disconnect from session;

Database altered.

SYS.YUDONG>SELECT PROCESS FROM V$MANAGED_STANDBY;

PROCESS
---------
ARCH
ARCH
RFS
RFS
RFS
MRP0

6 rows selected.

SYS.YUDONG>SELECT GROUP#,DBID,STATUS FROM V$STANDBY_LOG;

GROUP# DBID STATUS
---------- ---------------------------------------- ----------
5 404134968 ACTIVE
6 UNASSIGNED UNASSIGNED
7 UNASSIGNED UNASSIGNED
8 UNASSIGNED UNASSIGNED
9 UNASSIGNED UNASSIGNED

5 rows selected.

三、啟動時時應用日誌模式

SYS.YUDONG>recover managed standby database cancel;
Media recovery complete.
SYS.YUDONG>recover managed standby database using current logfile DISCONNECT;

Media recovery complete.

--登入到主庫：

SYS.YUDONG>conn zwf/zwf
Connected.

1 row selected.

ZWF.YUDONG>create table test_real_time(id number);

Table created.

--登入到從庫：只讀開啟資料庫發現TEST_REAL_TIME

SYS.YUDONG>recover managed standby database cancel;
Media recovery complete.
SYS.YUDONG>alter database open read only;

Database altered.

SYS.YUDONG>conn zwf/zwf
Connected.

1 row selected.

ZWF.YUDONG>select tname from tab;

TNAME
------------------------------
BIN$O/W3ny81x1jgQAgKK+JqLw==$0
TEST
TEST_REAL_TIME
T1
T2
T
ABC
T_1004

8 rows selected.

ZWF.YUDONG>SELECT GROUP#,DBID,STATUS FROM V$STANDBY_LOG;

GROUP# DBID STATUS
---------- ---------------------------------------- ----------
5 UNASSIGNED UNASSIGNED
6 404134968 ACTIVE
7 UNASSIGNED UNASSIGNED
8 UNASSIGNED UNASSIGNED
9 UNASSIGNED UNASSIGNED

5 rows selected.

--登入到其他從庫：沒有發現TEST_REAL_TIME

SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database open read only;

Database altered.

SQL> conn zwf/zwf
Connected.
SQL> select tname from tab;

TNAME
------------------------------
BIN$O/W3ny81x1jgQAgKK+JqLw==$0
TEST
T1
T2
T
ABC
T_1004

7 rows selected.

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

DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
---------------- -------------------- --------------------
PHYSICAL STANDBY RESYNCHRONIZATION RESYNCHRONIZATION

SQL> select sequence#,applied from v$archived_log;

SEQUENCE# APP
---------- ---
................................................

1078 YES
1079 YES
1080 YES
1081 YES
1082 YES

225 rows selected.

--登入主庫：

SYS.YUDONG>shutdown immediate;Database closed.
Database dismounted.
ORACLE instance shut down.

SYS.YUDONG>startup mount
ORACLE instance started.

Total System Global Area 490733568 bytes
Fixed Size 779456 bytes
Variable Size 406985536 bytes
Database Buffers 81920000 bytes
Redo Buffers 1048576 bytes
Database mounted.

SYS.YUDONG>alter database set standby database to maximize performance;

Database altered.

SYS.YUDONG>alter database open;

Database altered.

--登入其他從庫

SQL> select database_role,protection_mode,protection_level from v$database
SQL> /

DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
---------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

SQL> select sequence#,applied from v$archived_log;

SEQUENCE# APP
---------- ---
................................................

1177 YES
1178 YES
1179 YES
1180 YES
1181 YES
1182 YES

325 rows selected.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/756652/viewspace-242473/，如需轉載，請註明出處，否則將追究法律責任。