oracle 11g 活动数据库复制 DG部署

规划：
主库：db_name=dbking               db_unique_name=dbkingpri               
备库：db_name=dbking               db_unique_name=dbkingstd




1、修改dbking库为归档模式
2、修改dbking库为force logging模式
修改后结果如下：
SQL> select name,open_mode,log_mode,force_logging from v$database;


NAME    OPEN_MODE LOG_MODE      FORCE_LOG
--------------------------- ------------------------------------------------------------ ------------------------------------ ---------
DBKING    READ WRITE ARCHIVELOG      YES




3、配置tnsnames.ora文件


# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.


DBKINGPRI =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.40)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dbking)
    )
  )


DBKINGSTD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.41)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = dbking)
    )
  )


4、设置备库静态监听模式
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.41)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )


SID_LIST_LISTENER =
        (SID_LIST =
          (SID_DESC =
             (GLOBAL_DBNAME = dbking)
                (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)
             (SID_NAME = dbking)
          )
        )


ADR_BASE_LISTENER = /u01/app/oracle


5、创建主库slf文件：


SQL> alter database add standby logfile '/u01/app/oracle/oradata/dbking/srdo01.log' size 52428800;


Database altered.


SQL> alter database add standby logfile '/u01/app/oracle/oradata/dbking/srdo02.log' size 52428800;


Database altered.


SQL> alter database add standby logfile '/u01/app/oracle/oradata/dbking/srdo03.log' size 52428800;


Database altered.


SQL> alter database add standby logfile '/u01/app/oracle/oradata/dbking/srdo04.log' size 52428800;


Database altered.


SQL> select member from v$logfile;


MEMBER
------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/dbking/redo03.log
/u01/app/oracle/oradata/dbking/redo02.log
/u01/app/oracle/oradata/dbking/redo01.log
/u01/app/oracle/oradata/dbking/srdo01.log
/u01/app/oracle/oradata/dbking/srdo02.log
/u01/app/oracle/oradata/dbking/srdo03.log
/u01/app/oracle/oradata/dbking/srdo04.log


7 rows selected.


SQL> 


6、主库配置参数


SQL> show parameter db_name


NAME     TYPE       VALUE
------------------------------------ --------------------------------- ------------------------------
db_name     string       dbking


SQL> alter system set db_unique_name=dbkingpri scope=spfile;


System altered.


SQL> alter system set log_archive_config='dg_config=(dbkingpri,dbkingstd)';


System altered.


SQL> alter system set standby_file_management=auto;


System altered.


SQL> alter system set fal_client=dbkingstd;


System altered.


SQL> alter system set fal_server=dbkingpri;


System altered.


SQL> alter system set log_archive_dest_2='service=dbkingstd lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=dbkingstd';


System altered.


7、准备备库密码文件


[oracle@kaifai dbs]$ scp 192.168.100.40:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwdbking .


8、准备备库参数文件


[oracle@kaifai dbs]$ cat initdbing.ora
db_name=dbking
db_unique_name=dbkingstd


9、启动备库到nomount状态


[oracle@kaifai dbs]$ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.4.0 Production on Fri Jul 8 14:10:49 2016


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


Connected to an idle instance.


SQL> startup nomount
ORACLE instance started.


Total System Global Area  263090176 bytes
Fixed Size    2252256 bytes
Variable Size  205521440 bytes
Database Buffers   50331648 bytes
Redo Buffers    4984832 bytes
SQL>  


10、rman连接主备库


[oracle@kaifai admin]$ rman target sys/oracle@dbkingpri auxiliary sys/oracle@dbkingstd


Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jul 8 14:16:02 2016


Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


connected to target database: DBKING (DBID=3207597140)
connected to auxiliary database: DBKING (not mounted)


RMAN> 


11、创建相应目录


$mkdir -p /data/oradata/dbking
[oracle@kaifai admin]$ pwd
/u01/app/oracle/admin
[oracle@kaifai admin]$ mkdir dbking
[oracle@kaifai admin]$ cd dbking/
[oracle@kaifai dbking]$ mkdir adump
[oracle@kaifai dbking]$ mkdir cdump
[oracle@kaifai dbking]$ mkdir dpdump




12、以下脚本复制数据库


run{
duplicate target database 
for standby 
from active database
spfile
set db_unique_name='dbkingstd'
set log_file_name_convert '/u01/app/oracle/oradata/dbking/','/data/oradata/dbking/'
set db_file_name_convert '/u01/app/oracle/oradata/dbking/','/data/oradata/dbking/'
set control_files='/data/oradata/dbking/control.ctl'
set log_archive_dest_2='service=dbkingpri lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=dbkingpri'
set fal_client='dbkingstd'
set fal_server='dbkingpri'
;
}


13、启动应用


alter database recover managed standby database disconnect from session;


14、测试
create tablespace efmis datafile '/u01/app/oracle/oradata/dbking/efmis01.dbf' size 200m;


15、active dataguard 


1）查看当前备库运行状态


SQL> select instance_name,status from v$instance;


INSTANCE_NAME STATUS
------------------------------------------------ ------------------------------------
dbking MOUNTED


2）进入到只读状态


SQL> alter database recover managed standby database cancel;


Database altered.


SQL> alter database open;


Database altered.


SQL> select open_mode from v$database;


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


3）启动应用日志


SQL> alter database recover managed standby database disconnect from session;


Database altered.


SQL> select open_mode from v$database;


OPEN_MODE
------------------------------------------------------------
READ ONLY WITH APPLY


备库以只读方式应用日志。


4）测试：
略


16、修改主库为最大保护模式：


1）查看当前主库运行模式


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


DB_UNIQUE_NAME       PROTECTION_MODE    PROTECTION_LEVEL
------------------------------ ------------------------------------------------------------ ------------------------------------------------------------
DBKINGPRI       MAXIMUM PERFORMANCE    MAXIMUM PERFORMANCE


2）设置主库为最大保护模式


SQL> alter database set standby database to maximize protection;
alter database set standby database to maximize protection
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any instance




SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.


Total System Global Area  784998400 bytes
Fixed Size    2257352 bytes
Variable Size  499125816 bytes
Database Buffers  276824064 bytes
Redo Buffers    6791168 bytes
Database mounted.
SQL> alter database set standby database to maximize protection;


Database altered.


SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 10000
Session ID: 191 Serial number: 3


3）修改log_archive_dest_2参数


SQL>alter system set log_archive_dest_2='service=dbkingstd lgwr sync valid_for=(online_logfiles,primary_role) db_unique_name=dbkingstd';


SQL> conn / as sysdba


Connected.


SQL> alter database recover managed standby database cancel;


Database altered.


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


Database altered.


现在已经是最大保护模式。


[oracle@kaifai ~]$ sqlplus / as sysdba


SQL*Plus: Release 11.2.0.4.0 Production on Tue Jul 12 11:15:30 2016


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




Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> shutdown immediate
ORA-01154: database busy. Open, close, mount, and dismount not allowed now
SQL> 


说明：最大保护模式下，备库不能先于主库关闭。如果只有一个最大保护模式的备库，则不能直接转换成快照数据库。


17、快照standby：首先关闭mrp进程，并且不能在只读及事实恢复的情况下进行转换。


1）更改主库为最大性能模式


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.


Total System Global Area  784998400 bytes
Fixed Size    2257352 bytes
Variable Size  499125816 bytes
Database Buffers  276824064 bytes
Redo Buffers    6791168 bytes
Database mounted.
SQL> alter database set standby database to maximize performance;


Database altered.


SQL> alter database open;


Database altered.


2）备库在只读恢复模式，不能进行转换


SQL> select open_mode from v$database;


OPEN_MODE
------------------------------------------------------------
READ ONLY WITH APPLY


SQL> alter database convert to snapshot standby;
alter database convert to snapshot standby
*
ERROR at line 1:
ORA-38784: Cannot create restore point 'SNAPSHOT_STANDBY_REQUIRED_07/12/2016
16:24:52'.
ORA-01153: an incompatible media recovery is active


3）关闭数据库，启动到mount状态，进行转换，然后再次关闭数据库，启动数据库至open状态：


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.


Total System Global Area  784998400 bytes
Fixed Size    2257352 bytes
Variable Size  515903032 bytes
Database Buffers  260046848 bytes
Redo Buffers    6791168 bytes
Database mounted.
SQL> select open_mode from v$database;


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


SQL> alter database convert to snapshot standby;


Database altered.


SQL> shutdown immediate
ORA-01109: database not open




Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.


Total System Global Area  784998400 bytes
Fixed Size    2257352 bytes
Variable Size  515903032 bytes
Database Buffers  260046848 bytes
Redo Buffers    6791168 bytes
Database mounted.
Database opened.
SQL> select open_mode from v$database;


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


SQL> 


4）备库开始可以对外提供读写模式了，主库创建用户及生成测试表：


SQL> create user t2 identified by oracle account unlock;


User created.


SQL> grant dba to t2;


Grant succeeded.


SQL> create table t2.emp as select * from scott.emp;


Table created.


5）备库查询步骤4创建的测试用户及表，查不到结果


SQL> conn t2/oracle
ERROR:
ORA-01017: invalid username/password; logon denied




Warning: You are no longer connected to ORACLE.
SQL> conn / as sysdba
Connected.
SQL> select * from t2.emp;
select * from t2.emp
                 *
ERROR at line 1:
ORA-00942: table or view does not exist


6）测试备库读写功能


SQL> create user t3 identified by oracle account unlock;


User created.


SQL> grant dba to t3;


Grant succeeded.


SQL> create table t3.emp as select * from scott.emp;


Table created.


18、snapshot standby数据库转换回物理数据库


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.


Total System Global Area  784998400 bytes
Fixed Size    2257352 bytes
Variable Size  515903032 bytes
Database Buffers  260046848 bytes
Redo Buffers    6791168 bytes
Database mounted.
SQL> alter database convert to physical standby;


Database altered.


SQL> shutdown immediate
ORA-01507: database not mounted




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


Total System Global Area  784998400 bytes
Fixed Size    2257352 bytes
Variable Size  515903032 bytes
Database Buffers  260046848 bytes
Redo Buffers    6791168 bytes
Database mounted.
Database opened.
SQL> alter database recover managed standby database disconnect from session;


Database altered.


SQL> conn t2/oracle
Connected.
SQL> select * from emp;
      .......
     14 rows selected.
SQL> conn t3/oracle
ERROR:
ORA-01017: invalid username/password; logon denied




Warning: You are no longer connected to ORACLE.
SQL> 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29612373/viewspace-2122393/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29612373/viewspace-2122393/