DGMGRL> migrate pluggable database <PDBNAME1> to container <dest-cdb-name> using '/<path>/<DEST_SID>/<PDBNAME1.xml>' connect as <USERNAME>@"<dest-cdb-connect-identifer>";
-or-
DGMGRL> migrate pluggable database <PDBNAME1> to container <dest-cdb-name> using '/<path>/<DEST_SID>/<PDBNAME1.xml>':
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB12C READ WRITE NO 4 PDB3 READ WRITE NO
sys@ORA12C> select database_role from v$database;
DATABASE_ROLE ---------------- PRIMARY
生成pdb的xml文件
1 2 3 4 5 6 7 8 9 10 11
sys@ORA12C> alter session set container=pdb3;
Session altered.
BEGIN DBMS_PDB.DESCRIBE( pdb_descr_file => '/u01/app/oracle/oradata/pdb3.xml'); END; /
PL/SQL procedure successfully completed.
在备库机器上连接到源库的dg broker
1 2 3
DGMGRL> connect sys/oracle@ora12c_dg Connected to "ORA12C_DG" Connected as SYSDBA.
DGMGRL> migrate pluggable database immediate pdb3 to container cdb1 using '/u01/app/oracle/oradata/pdb3.xml'; Username: sys@cdb1 Password: Connected to "cdb1" Connected as SYSDBA.
Beginning migration of pluggable database PDB3. Source multitenant container database is ORA12C_DG. Destination multitenant container database is cdb1.
Connected to "ora12c" Closing pluggable database PDB3 on all instances of multitenant container database ora12c. Pluggable database PDB3 on database ORA12C_DG lags its primary database, ora12c, by 2129 seconds. Continuing with migration of pluggable database PDB3 to multitenant container database cdb1. Stopping Redo Apply services on source multitenant container database ORA12C_DG. Pluggable database description will be written to /u01/app/oracle/oradata/pdb3.xml. Closing pluggable database PDB3 on all instances of multitenant container database ORA12C_DG. Disabling media recovery for pluggable database PDB3. Restarting redo apply services on source multitenant container database ORA12C_DG. Creating pluggable database PDB3 on multitenant container database cdb1. Opening pluggable database PDB3 on all instances of multitenant container database cdb1. Unplugging pluggable database PDB3 from multitenant container database ora12c. Pluggable database description will be written to /tmp/ora_tfils2PLmU.xml. Dropping pluggable database PDB3 from multitenant container database ora12c. Migration of pluggable database PDB3 completed. Succeeded.
-- 检查目标端cdb的pdb状态 sys@CDB1> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 MOUNTED 4 PDB3 READ WRITE NO
-- 日志能看到并没有文件的拷贝动作 Completed: create pluggable database PDB3 using '/u01/app/oracle/oradata/pdb3.xml' nocopy standbys=none tempfile reuse alter pluggable database PDB3 open instances=all PDB3(4):Autotune of undo retention is turned on.
DGMGRL> migrate pluggable database <PDBNAME1> to container <dest-cdb-name> using '/<path>/<PDBNAME1>/<PDBNAME1.xml>' connect as <USERNAME>@"<dest-cdb-connect-identifer>"; -or- DGMGRL> migrate pluggable database <PDBNAME1> to container <dest-cdb-name> using '/<path>/<PDBNAME1>/<PDBNAME1.xml>': # This will prompt for user name and password
在Rman中执行duplicate database for standby from active database命令时，只会在备库创建一个单实例，你需要手动设置rac参数包括INSTANCE_NUMBER和INSTANCE_NAME等，在另一个主机上启用第二个实例，然后添加新创建的数据库到OCR中
PARAMETER_NAME TYPE VALUE ------------------------------------------------------------ ----------- --------------------------------------------------------------------------- log_archive_dest_1 string
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=BOTH;
# 注意参数大小写敏感: ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(rac,stbrac)' SCOPE=BOTH SID='*'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_2= 'SERVICE=stbrac ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stbrac' SCOPE=BOTH SID='*';
alter system set log_archive_dest_state_1 = ENABLE SCOPE=BOTH SID='*'; alter system set log_archive_dest_state_2 = ENABLE SCOPE=BOTH SID='*';
# 应该返回EXCLUSIVE show parameter REMOTE_LOGIN_PASSWORDFILE
# FAL = fetch archive log ALTER SYSTEM SET FAL_SERVER='stbrac' SCOPE=BOTH SID='*'; ALTER SYSTEM SET FAL_CLIENT='rac' SCOPE=BOTH SID='*';
# 如果主备库文件目录一致，则不用设置 show parameter DB_FILE_NAME_CONVERT show parameter STANDBY_FILE_MANAGEMENT ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO' SCOPE=BOTH SID='*';
# 设置ARCn进程最大值 ( default 4): ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=8 SCOPE=BOTH SID='*';
主库开启数据库闪回
1 2
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET =2880 scope=BOTH; ALTER DATABASE FLASHBACK ON;
run { allocate channel c1 type disk; allocate channel c2 type disk; allocate auxiliary channel stbc1 type disk; DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE SPFILE set 'db_unique_name'='stbrac' set instance_name='stbrac1' set instance_number='1' set db_create_online_log_dest_1='+FRA' set db_create_online_log_dest_2='+DATA' set db_recovery_file_dest='+FRA' set audit_file_dest='/u01/app/oracle/admin/stbrac/adump' set core_dump_dest='/u01/app/oracle/admin/stbrac/cdump' nofilenamecheck dorecover; }
set linesize 150 set pagesize 100 select 'alter database drop standby logfile member ''' || member || ''';' from v$logfile where type='STANDBY' and member like '+DATA%';
'ALTERDATABASEDROPSTANDBYLOGFILEMEMBER'''||MEMBER||''';' ------------------------------------------------------------------------------------------------------------------------------------------------------ alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_5.278.1005926909'; alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_6.277.1005926915'; alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_7.276.1005926921'; alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_8.275.1005926925'; alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_9.274.1005926931'; alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_10.273.1005926937'; alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_11.272.1005926943'; alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_12.271.1005926947'; alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_13.314.1005926953'; alter database drop standby logfile member '+DATA/STBRAC/ONLINELOG/group_14.315.1005926959';
-- 确认删除完毕 sys@STBRAC1> SELECT GROUP#, MEMBER FROM V$LOGFILE WHERE TYPE='STANDBY' ORDER BY 1,2;
ALTER SYSTEM SET FAL_SERVER=rac SCOPE=BOTH SID='*'; ALTER SYSTEM SET FAL_CLIENT=stbrac SCOPE=BOTH SID='*'; ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(rac,stbrac)' SID='*'; ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SID='*'; ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=rac ASYNC DB_UNIQUE_NAME=rac VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)' SCOPE=BOTH SID='*';
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string +DATA/STBRAC/PARAMETERFILE/spfilestbrac.ora
修改spfile中的INSTANCE_NAME和INSTANCE_NUMBER参数
1 2 3 4
ALTER SYSTEM SET INSTANCE_NUMBER=1 SCOPE=SPFILE SID='stbrac1'; ALTER SYSTEM SET INSTANCE_NUMBER=2 SCOPE=SPFILE SID='stbrac2'; ALTER SYSTEM SET INSTANCE_NAME='stbrac1' SCOPE=SPFILE SID='stbrac1'; ALTER SYSTEM SET INSTANCE_NAME='stbrac2' SCOPE=SPFILE SID='stbrac2';
alter database recover managed standby database using current logfile disconnect from session;
检查每个节点的dg配置情况
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
select inst_id, database_role, db_unique_name instance, open_mode, protection_mode, protection_level from gv$database;
INST_ID DATABASE_ROLE INSTANCE OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL ---------- ---------------- ------------------------------ -------------------- -------------------- -------------------- 1 PHYSICAL STANDBY stbrac READ ONLY WITH APPLY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE 2 PHYSICAL STANDBY stbrac READ ONLY WITH APPLY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
select inst_id, database_mode, recovery_mode, protection_mode from gv$archive_dest_status where dest_name ='LOG_ARCHIVE_DEST_1';
INST_ID DATABASE_MODE RECOVERY_MODE PROTECTION_MODE ---------- --------------- ----------------------- -------------------- 2 OPEN_READ-ONLY MANAGED REAL TIME APPLY MAXIMUM PERFORMANCE 1 OPEN_READ-ONLY MANAGED REAL TIME APPLY MAXIMUM PERFORMANCE
检查redo传输服务
1 2 3 4 5 6 7 8 9 10 11
alter session set nls_date_format ='yyyy-mm-dd hh24:mi:ss'; select inst_id, sequence#, applied, first_time, next_time from gv$archived_log order by 2,1,4;
-- 主库建表 create table test as select * from dba_tables where 1=2;
set linesize 300 col start_time format a20 col item format a20 select to_char(start_time, 'yyyy-mm-dd hh24:mi:ss') start_time, item , sofar, units from v$recovery_progress where item in ('Active Apply Rate', 'Average Apply Rate', 'Redo Applied');
START_TIME ITEM SOFAR UNITS -------------------- -------------------- ---------- -------------------------------- 2019-04-18 17:26:33 Active Apply Rate 1148 KB/sec 2019-04-18 17:26:33 Average Apply Rate 58 KB/sec 2019-04-18 17:26:33 Redo Applied 22 Megabytes
col name for a13 col value for a13 col unit for a30 set lines 132 select name, value, unit, time_computed from v$dataguard_stats where name in ('transport lag', 'apply lag');
NAME VALUE UNIT TIME_COMPUTED ------------- ------------- ------------------------------ ------------------------------ transport lag +00 00:00:00 day(2) to second(0) interval 04/18/2019 17:33:57 apply lag +00 00:00:00 day(2) to second(0) interval 04/18/2019 17:33:57
优化配置
配置主库归档日志删除策略
1 2
rman target / CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
启用备库的闪回
1 2 3 4
alter system set db_flashback_retention_target =2880; alter database recover managed standby database cancel; alter database flashback on; alter database recover managed standby database using current logfile disconnect from session;
配置dg broker
查看系统现有dg broker配置
1 2 3 4 5 6 7
sys@RAC1> show parameter dg_broker
PARAMETER_NAME TYPE VALUE ------------------------------------------------------------ ----------- ------------------------------------------------------------------------------------- dg_broker_config_file1 string /u01/app/oracle/product/12.2.0/dbhome_1/dbs/dr1rac.dat dg_broker_config_file2 string /u01/app/oracle/product/12.2.0/dbhome_1/dbs/dr2rac.dat dg_broker_start boolean FALSE
rac环境中dgbroker的配置文件要存放在共享目录上，在ASM上新建一个目录
1 2 3
su - grid ASMCMD>cd +data/rac/ ASMCMD>mkdir DGCONFIG
停掉备库apply
1
alter database recover managed standby database cancel;
修改主库参数
1 2 3 4
alter system set LOG_ARCHIVE_DEST_2='' scope=both; ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='+DATA/RAC/DGCONFIG/dr1rac.dat' SCOPE=spfile sid='*'; ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='+DATA/RAC/DGCONFIG/dr2rac.dat' SCOPE=spfile sid='*'; alter system set dg_broker_start=true scope=both sid='*';
同时也修改备库
1 2 3 4 5 6 7 8
su - grid ASMCMD>cd +data/stbrac/ ASMCMD>mkdir DGCONFIG
alter system set LOG_ARCHIVE_DEST_2='' scope=both; ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='+DATA/STBRAC/DGCONFIG/dr1stbrac.dat' SCOPE=spfile sid='*'; ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='+DATA/STBRAC/DGCONFIG/dr2stbrac.dat' SCOPE=spfile sid='*'; alter system set dg_broker_start=true scope=both sid='*';
DGMGRL> switchover to 'stbrac'; Performing switchover NOW, please wait... Operation requires a connection to database "stbrac" Connecting ... Connected to "stbrac" Connected as SYSDBA. New primary database "stbrac" is opening... Oracle Clusterware is restarting database "rac" ... Connected to "rac" Connected to "rac" Switchover succeeded, new primary is "stbrac" DGMGRL> show configuration;
--重新swithover回来 DGMGRL> switchover to 'rac'; Performing switchover NOW, please wait... Operation requires a connection to database "rac" Connecting ... Connected to "rac" Connected as SYSDBA. New primary database "rac" is opening... Oracle Clusterware is restarting database "stbrac" ... Switchover succeeded, new primary is "rac"
