oracle 快照数据库和物理备库的转换

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> select open_mode,database_role from v$database;

OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ ONLY WITH APPLY PHYSICAL STANDBY
首先停止redo apply进程;
SQL> alter database recover managed standby database cancel;

Database altered.
然后关闭数据库，并启动数据库到mount状态;
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 534462464 bytes
Fixed Size 2228200 bytes
Variable Size 423624728 bytes
Database Buffers 100663296 bytes
Redo Buffers 7946240 bytes
Database mounted.
然后转换到 快照数据库;

SQL> alter database convert to snapshot standby;

Database altered.

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
SNAPSHOT STANDBY

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> alter database open;

Database altered.

数据库状态为read write，此时可以在数据库上做各种实验

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
6378976

SQL> /

CURRENT_SCN
-----------
6378982

SQL> select primary_db_unique_name,db_unique_name,database_role from v$database;

PRIMARY_DB_UNIQUE_NAME DB_UNIQUE_NAME DATABASE_ROLE
------------------------------ ------------------------------ ----------------
DREAM DREAM01 SNAPSHOT STANDBY

SQL>

数据库自己创建了1个还原点，用于还原
SQL> select name,storage_size from v$restore_point;
rows will be truncated

NAME
--------------------------------------------------------------------------------
SNAPSHOT_STANDBY_REQUIRED_06/19/2012 11:35:11

SQL>

代表SNAPSHOT_STANDBY_REQUIRED_06/19/2012 11:35:11这个还原点开启数据库闪回功能

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY

下面我们再把数据库转换为physical standby

SQL> alter database convert to physical standby;

alter database convert to physical standby
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any instance

首先关闭快照数据库并重新启动到mount状态;
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 534462464 bytes
Fixed Size 2228200 bytes
Variable Size 423624728 bytes
Database Buffers 100663296 bytes
Redo Buffers 7946240 bytes
Database mounted.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY

转换数据库到物理备库;

SQL> alter database convert to physical standby;

Database altered.

这时数据库是nomount状态;

SQL> select primary_db_unique_name,db_unique_name,database_role from v$database;
select primary_db_unique_name,db_unique_name,database_role from v$database
*
ERROR at line 1:
ORA-01507: database not mounted

SQL> select status from v$instance;

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

我们手动启动到mount状态是不行的；

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

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

我们只能先关闭数据库，再从新打开;

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 534462464 bytes
Fixed Size 2228200 bytes
Variable Size 423624728 bytes
Database Buffers 100663296 bytes
Redo Buffers 7946240 bytes
Database mounted.

检查数据库角色

SQL> select primary_db_unique_name,db_unique_name,database_role from v$database;

PRIMARY_DB_UNIQUE_NAME DB_UNIQUE_NAME DATABASE_ROLE
------------------------------ ------------------------------ ----------------
DREAM DREAM01 PHYSICAL STANDBY

转换成功，还原点已经被删除。

SQL> select name from v$restore_point;

NAME
--------------------------------------------------------------------------------------------------------------------------------

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select open_mode,database_role from v$database;

OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ ONLY WITH APPLY PHYSICAL STANDBY

SQL>

11.2.0.2G 功能很强大