快照备库 oracle 原理,oracle 快照数据库跟物理备库的转换

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 功能很强大