Physical Standby Database 切換到 Snapshot Standby Database

快照資料庫主要應用於測試一些將要應用與主庫的操作。

主庫資訊：

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS    PRIMARY_DB_UNIQUE_NAME         PROTECTION_MODE
--------- ------------------------------ ---------------- -------------------- ------------------------------ --------------------
PHYPRIMA  phyprimary                     PRIMARY          TO STANDBY           phystandby                     MAXIMUM PERFORMANCE

備庫資訊：

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS    PRIMARY_DB_UNIQUE_NAME         PROTECTION_MODE
--------- ------------------------------ ---------------- -------------------- ------------------------------ --------------------
PHYPRIMA  phystandby                     PHYSICAL STANDBY NOT ALLOWED          phyprimary                     MAXIMUM PERFORMANCE

實驗步驟：

1 重啟備庫到mount階段(備庫操作)

 SQL> shutdown immediate

3 切換物理備庫到快照備庫，並檢視備庫狀態

SQL> alter database convert to snapshot standby;  

Database altered.

SQL> select open_mode,database_role,switchover_status from v$database;

OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
-------------------- ---------------- --------------------
READ WRITE           SNAPSHOT STANDBY NOT ALLOWED

4 開啟快照備庫 新增一些測試資料庫

主庫操作：

在主庫建立測試表

SQL> create table user01.test04 as select * from dba_users;

Table created.

切換日誌，

SQL> alter system switch logfile;

System altered.

在備庫日誌檔案中可以看到，備庫依然在接受日誌

ARC1: Becoming the 'no SRL' ARCH
Archived Log entry 179 added for thread 1 sequence 72 ID 0x136827ea dest 1:
RFS[4]: Selected log 8 for thread 1 sequence 73 dbid 324097933 branch 855859489
Mon Aug 25 19:03:33 2014

但並沒有應用到備庫上，備庫上不存在主庫新建的表

SQL> desc user01.test04;
ERROR:
ORA-04043: object user01.test04 does not exist

在備庫新建測試表

create user02 identified by oracle;

grant resource,connect to user02;

create table user02.test01 as select * from dba_users;

SQL> select username,account_status from user02.test01;

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
MGMT_VIEW                      OPEN
SYS                            OPEN
SYSTEM                         OPEN
DBSNMP                         OPEN
SYSMAN                         OPEN
USER02                         OPEN
OUTLN                          EXPIRED & LOCKED
FLOWS_FILES                    EXPIRED & LOCKED
MDSYS                          EXPIRED & LOCKED
ORDSYS                         EXPIRED & LOCKED
EXFSYS                         EXPIRED & LOCKED

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
WMSYS                          EXPIRED & LOCKED
APPQOSSYS                      EXPIRED & LOCKED
APEX_030200                    EXPIRED & LOCKED
OWBSYS_AUDIT                   EXPIRED & LOCKED
ORDDATA                        EXPIRED & LOCKED
CTXSYS                         EXPIRED & LOCKED
ANONYMOUS                      EXPIRED & LOCKED
XDB                            EXPIRED & LOCKED
ORDPLUGINS                     EXPIRED & LOCKED
OWBSYS                         EXPIRED & LOCKED
SI_INFORMTN_SCHEMA             EXPIRED & LOCKED

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
OLAPSYS                        EXPIRED & LOCKED
SCOTT                          EXPIRED & LOCKED
ORACLE_OCM                     EXPIRED & LOCKED
XS$NULL                        EXPIRED & LOCKED
BI                             EXPIRED & LOCKED
PM                             EXPIRED & LOCKED
MDDATA                         EXPIRED & LOCKED
IX                             EXPIRED & LOCKED
SH                             EXPIRED & LOCKED
DIP                            EXPIRED & LOCKED
OE                             EXPIRED & LOCKED

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
APEX_PUBLIC_USER               EXPIRED & LOCKED
HR                             EXPIRED & LOCKED
SPATIAL_CSW_ADMIN_USR          EXPIRED & LOCKED
SPATIAL_WFS_ADMIN_USR          EXPIRED & LOCKED

37 rows selected.

5 重新切換快照資料庫到物理備庫。

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  422670336 bytes

Fixed Size                  1336960 bytes

Variable Size             343935360 bytes

Database Buffers           71303168 bytes

Redo Buffers                6094848 bytes

Database mounted.

SQL> alter database convert to physical standby;  

Database altered.

6 重新啟動備庫，檢視備庫測試資料

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01507: database not mounted


SQL> select status from v$instance;

STATUS
------------
STARTED

SQL> alter database mount
  2  ;
alter database mount
*
ERROR at line 1:
ORA-00750: database has been previously mounted and dismounted


SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  422670336 bytes
Fixed Size                  1336960 bytes
Variable Size             343935360 bytes
Database Buffers           71303168 bytes
Redo Buffers                6094848 bytes
Database mounted.
SQL> alter database recover managed standby database;

Database altered.

SQL> alter database open;

Database altered.

之前新建的使用者user02已經不存在

SQL> conn /as sysdba
Connected.
SQL> select username from dba_users where username='USER02';   

no rows selected

在物理備庫上檢視之前在主庫建立的表，發現已經自動應用到了備庫上

SQL> desc user01.test04;
Name                                                                                                              Null?    Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
USERNAME                                                                                                          NOT NULL VARCHAR2(30)
USER_ID                                                                                                           NOT NULL NUMBER
PASSWORD                                                                                                                   VARCHAR2(30)
ACCOUNT_STATUS                                                                                                    NOT NULL VARCHAR2(32)
LOCK_DATE                                                                                                                  DATE
EXPIRY_DATE                                                                                                                DATE
DEFAULT_TABLESPACE                                                                                                NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE                                                                                              NOT NULL VARCHAR2(30)
CREATED                                                                                                           NOT NULL DATE
PROFILE                                                                                                           NOT NULL VARCHAR2(30)
INITIAL_RSRC_CONSUMER_GROUP                                                                                                VARCHAR2(30)
EXTERNAL_NAME                                                                                                              VARCHAR2(4000)
PASSWORD_VERSIONS                                                                                                          VARCHAR2(8)
EDITIONS_ENABLED                                                                                                           VARCHAR2(1)
AUTHENTICATION_TYPE