Oracle-SNAPSHOT STANDBY测试

--现因为服务器年限等原因需要进行服务器切换：

--步骤：
--1.新服务器安装操作系统、创建数据库。---- 目录保持与原服务器一致。
--2.新服务器部署应用程序。  --  拷贝原服务器的文件。  修改与ip相关的信息 和 连接数据库的信息（服务名和ip）
--3.搭建一个测试应用服务器。
--4.新服务器与原服务器之间搭建DG。
--5.新服务器DG切换到快照读写模式。   --  这个时间尽可能短。
--6.重新切换新服务器DG为备库模式并开启应用保持同步。
--7.业务空闲期进行DG切换。 
--	切换之后不想修改客户端和应用的IP地址的话可以停止同步，关闭数据库，修改新服务器IP为原服务器IP。
--	并且增加与原服务器相同的服务名。
/*************************以下为个人测试环境进行快照adg测试的步骤***********************************************************************/	


--首先来看官方文档的描述：（11.2.0.4 -- https://docs.oracle.com/cd/E11882_01/server.112/e41134/manage_ps.htm#SBYDB4803 ）

--1.Converting a Physical Standby Database into a Snapshot Standby Database
--Perform the following steps to convert a physical standby database into a snapshot standby database:
--1.1.Stop Redo Apply, if it is active.
--1.2.Ensure that the database is mounted, but not open.
--1.3.Ensure that a fast recovery area has been configured. It is not necessary for flashback database to be enabled.
--1.4.Issue the following SQL statement to perform the conversion:
SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
--2.Using a Snapshot Standby Database
--snapshot standby database can be opened in read-write mode and is fully updatable.
--A snapshot standby database has the following characteristics:
--A snapshot standby database cannot be the target of a switchover or failover. 
--A snapshot standby database must first be converted back into a physical standby database before performing a role transition to it.
--A snapshot standby database cannot be the only standby database in a Maximum Protection Data Guard configuration.
3.Converting a Snapshot Standby Database into a Physical Standby Database
--On an Oracle Real Applications Cluster (Oracle RAC) database, shut down all but one instance.
--Ensure that the database is mounted, but not open.
--Issue the following SQL statement to perform the conversion:
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
--The database is dismounted after conversion and must be restarted.
--Redo data received while the database was a snapshot standby database will be automatically applied when Redo Apply is started.
--A snapshot standby database must be opened at least once in read-write mode before it can be converted into a physical standby database.








/********************需要注意的点******************************************************/
--看完上面的步骤，总结需要注意的点如下：

--1.主库需要注意归档保留时间，不然等切回到物理备库应用归档的时候归档不在造成GAP。
--2.测试的时间尽可能短，因为DG毕竟是作为一个容灾来使用的。
--3.快照备库不能是最大保护模式下的唯一的一个备库。

/***************问题： 发现文档只是写了如何转换，没有创建还原点等操作，那快照期间修改的数据怎么办 ？ *****************************/
测试：
创建还原点。 
进行转换。
写入数据。
然后直接从快照数据库转到备库。  看有没有问题。 修改的数据是不是自动消失。
如果有问题，闪回到还原点。 然后转换到备库，开启应用。



/************************实际测试流程如下************************************/

--1.验证同步状态
主库：
SQL> select database_role,open_mode from v$database;

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

SQL>
SQL> select * from scott.dept;

    DEPTNO DNAME	  LOC
---------- -------------- -------------
	70 oracle	  mysql
	10 ACCOUNTING	  NEW YORK
	20 RESEARCH	  DALLAS
	30 SALES	  CHICAGO
	45 OPERATIONS	  BOSTON

SQL>
备库：
SQL> select database_role,open_mode from v$database;

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

SQL>
SQL> select * from scott.dept;

    DEPTNO DNAME	  LOC
---------- -------------- -------------
	70 oracle	  mysql
	10 ACCOUNTING	  NEW YORK
	20 RESEARCH	  DALLAS
	30 SALES	  CHICAGO
	45 OPERATIONS	  BOSTON

SQL> 

主库

SQL> delete scott.dept where deptno=70;
1 row deleted.
SQL> commit;
Commit complete.
SQL>

备库：
SQL> select * from scott.dept;
    DEPTNO DNAME	  LOC
---------- -------------- -------------
	10 ACCOUNTING	  NEW YORK
	20 RESEARCH	  		DALLAS
	30 SALES	  		CHICAGO
	45 OPERATIONS	 	 BOSTON
SQL>



2.备库创建还原点

SELECT NAME FROM V$RESTORE_POINT;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
CREATE RESTORE POINT SNAP_START_GRP GUARANTEE FLASHBACK DATABASE;

3.转换备库为快照adg

ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;

Database altered.

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> alter database open;

Database altered.

SQL> 


写入数据：
SQL> select database_role,open_mode from v$database;

DATABASE_ROLE	 OPEN_MODE
---------------- --------------------
SNAPSHOT STANDBY READ WRITE

SQL>


insert into scott.dept values(1,'test','test');
insert into scott.dept values(2,'test','test');
insert into scott.dept values(3,'test','test');
insert into scott.dept values(4,'test','test');
insert into scott.dept values(5,'test','test');
commit;

SQL> select * from scott.dept;

    DEPTNO DNAME	  LOC
---------- -------------- -------------
	10 ACCOUNTING	  NEW YORK
	20 RESEARCH	  DALLAS
	30 SALES	  CHICAGO
	45 OPERATIONS	  BOSTON
	 1 test 	  test
	 2 test 	  test
	 3 test 	  test
	 4 test 	  test
	 5 test 	  test

9 rows selected.

SQL>




4.转换快照备库为物理备库

ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

SQL> shutdown immediate;
SQL> startup mount;
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SQL> 
SQL> shutdown immediate
SQL> startup
SQL> select database_role,open_mode from v$database;

DATABASE_ROLE	 OPEN_MODE
---------------- --------------------
PHYSICAL STANDBY READ ONLY

SQL>


---检查刚才插入的数据是否还在
SQL> select *  from scott.dept;

    DEPTNO DNAME	  LOC
---------- -------------- -------------
	10 ACCOUNTING	  NEW YORK
	20 RESEARCH	  DALLAS
	30 SALES	  CHICAGO
	45 OPERATIONS	  BOSTON

SQL>

--发现快照期间写的数据没有了。

--开启日志应用检查同步状态
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE NODELAY DISCONNECT FROM SESSION;

Database altered.

SQL>


--主库写入数据
SQL> insert into scott.dept values(60,'oracle','mysql');

1 row created.

SQL> commit;

Commit complete.

SQL> select *from scott.dept;

    DEPTNO DNAME	  LOC
---------- -------------- -------------
	10 ACCOUNTING	  NEW YORK
	20 RESEARCH	  DALLAS
	30 SALES	  CHICAGO
	45 OPERATIONS	  BOSTON
	60 oracle	  mysql

SQL> 

--备库检查

SQL> select *from scott.dept;

    DEPTNO DNAME	  LOC
---------- -------------- -------------
	10 ACCOUNTING	  NEW YORK
	20 RESEARCH	  DALLAS
	30 SALES	  CHICAGO
	45 OPERATIONS	  BOSTON
	60 oracle	  mysql

SQL> 


--备库删除还原点：


select name from v$restore_point;
recover managed standby database cancel;
shutdown immediate;
startup mount;
DROP RESTORE POINT SNAP_START_GRP;
alter database open;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE NODELAY DISCONNECT FROM SESSION;