数据库版本：
12.2.0.1.0
实验环境：
192.168.0.196 prod04 主数据库（sid cdb)
192.168.0.197 prod05 备数据库（sid cdb)

主备数据库数据文件目录相同。（/u01/app/oracle/oradata/cdb/）

1, 主数据库环境准备

1，开启归档
2，启动监听
3，配置如下参数
LOG_FILE_NAME_CONVERT,db_file_name_conver,standby_file_manage,
4，添加standby log

#开启强制日志和归档
ALTER DATABASE FORCE LOGGING;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

#设置文件位置，文件管理方式
alter system set standby_file_management='AUTO';
alter system set LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/cdb/','/u01/app/oracle/oradata/cdb/'  scope=spfile;
alter system set db_file_name_convert='/u01/app/oracle/oradata/cdb/','/u01/app/oracle/oradata/cdb/'  scope=spfile;

#stantdby redo:(大小必须和redo大小一致)
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/cdb/s0111.log' size 200m;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/cdb/s0222.log' size 200m;
ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/cdb/s0333.log' size 200m;

2,备库环境

1，备库创建密码文件
2，启动监听
3，spfile文件
4，建立目录文件

密码文件：

orapwd file=orapwsborcl password=oracle  format=12

spfile

(fal_server='CDB',log_archive_config='DG_CONFIG=(cdb,sbcdb)',db_unique_name='sbcdb')

*.audit_file_dest='/u01/app/oracle/admin/cdb/adump'
*.audit_trail='DB'
*.compatible='12.2.0'
*.connection_brokers='((TYPE=DEDICATED)(BROKERS=1))','((TYPE=EMON)(BROKERS=1))'# connection_brokers default value
*.control_files='/u01/app/oracle/oradata/cdb/control01.ctl'#Restore Controlfile
*.core_dump_dest='/u01/app/oracle/diag/rdbms/sbcdb/cdb/cdump'
*.db_block_size=8192
*.db_file_name_convert='/u01/app/oracle/oradata/cdb/','/u01/app/oracle/oradata/cdb/'
*.db_name='cdb'
*.db_unique_name='sbcdb'
*.diagnostic_dest='/u01/app/oracle'
*.enable_pluggable_database=TRUE
*.fal_server='CDB'
*.log_archive_config='DG_CONFIG=(cdb,sbcdb)'
*.log_archive_dest_1='location=/u01/app/arc'
*.log_buffer=7456K# log buffer update
*.log_file_name_convert='/u01/app/oracle/oradata/cdb/','/u01/app/oracle/oradata/cdb/'
*.open_cursors=300
*.optimizer_dynamic_sampling=2
*.optimizer_mode='ALL_ROWS'
*.pga_aggregate_target=585M
*.plsql_warnings='DISABLE:ALL'# PL/SQL warnings at init.ora
*.processes=640
*.query_rewrite_enabled='TRUE'
*.remote_login_passwordfile='EXCLUSIVE'
*.result_cache_max_size=9024K
*.sga_target=1760M
*.skip_unusable_indexes=TRUE
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

建立目录：
mkdir -p /u01/app/oracle/admin/cdb/adump
mkdir -p /u01/app/oracle/oradata/cdb

3主备数据库tnsname.ora 配置

[oracle@prod05 admin]$ cat tnsnames.ora 

cdb =
(DESCRIPTION =
    (ADDRESS_LIST =(ADDRESS=(PROTOCOL= TCP)(HOST=prod04)(PORT=1521)))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = cdb))
)

sbcdb =
(DESCRIPTION =
    (ADDRESS_LIST =(ADDRESS=(PROTOCOL = TCP)(HOST=prod05) (PORT=1521)))
    (CONNECT_DATA =
    (UR=A)
    (SERVER = DEDICATED)
    (SERVICE_NAME = sbcdb))
)

4，备库启动到nomount，使用rman 同步数据库

主数据库：cdb
备数据库：sbcdb

rman target sys/oracle@cdb auxiliary sys/oracle@sbcdb

Recovery Manager: Release 12.2.0.1.0 - Production on Wed Aug 29 14:47:36 2018

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

connected to target database: CDB (DBID=2100050565)
connected to auxiliary database: CDB (not mounted)

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER   NOFILENAMECHECK;

Starting Duplicate Db at 29-AUG-18
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=621 device type=DISK
current log archived

contents of Memory Script:
{
   backup as copy reuse
   passwordfile auxiliary format  '/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapwcdb'   ;
}
executing Memory Script

Starting backup at 29-AUG-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=254 device type=DISK
Finished backup at 29-AUG-18

contents of Memory Script:
{
   restore clone from service  'cdb' standby controlfile;
}
executing Memory Script

Starting restore at 29-AUG-18
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdb
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/cdb/control01.ctl
Finished restore at 29-AUG-18

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to 
 "/u01/app/oracle/oradata/cdb/temp01.dbf";
   set newname for tempfile  2 to 
 "/u01/app/oracle/oradata/cdb/pdbseed/temp012018-08-06_11-44-49-136-AM.dbf";
   set newname for tempfile  3 to 
 "/u01/app/oracle/oradata/cdb/pdb01/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to 
 "/u01/app/oracle/oradata/cdb/system01.dbf";
   set newname for datafile  3 to 
 "/u01/app/oracle/oradata/cdb/sysaux01.dbf";
   set newname for datafile  4 to 
 "/u01/app/oracle/oradata/cdb/undotbs01.dbf";
   set newname for datafile  5 to 
 "/u01/app/oracle/oradata/cdb/pdbseed/system01.dbf";
   set newname for datafile  6 to 
 "/u01/app/oracle/oradata/cdb/pdbseed/sysaux01.dbf";
   set newname for datafile  7 to 
 "/u01/app/oracle/oradata/cdb/users01.dbf";
   set newname for datafile  8 to 
 "/u01/app/oracle/oradata/cdb/pdbseed/undotbs01.dbf";
   set newname for datafile  9 to 
 "/u01/app/oracle/oradata/cdb/pdb01/system01.dbf";
   set newname for datafile  10 to 
 "/u01/app/oracle/oradata/cdb/pdb01/sysaux01.dbf";
   set newname for datafile  11 to 
 "/u01/app/oracle/oradata/cdb/pdb01/undotbs01.dbf";
   set newname for datafile  12 to 
 "/u01/app/oracle/oradata/cdb/pdb01/users01.dbf";
   restore
   from  nonsparse   from service 
 'cdb'   clone database
   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/cdb/temp01.dbf in control file
renamed tempfile 2 to /u01/app/oracle/oradata/cdb/pdbseed/temp012018-08-06_11-44-49-136-AM.dbf in control file
renamed tempfile 3 to /u01/app/oracle/oradata/cdb/pdb01/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 29-AUG-18
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdb
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/cdb/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdb
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/cdb/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdb
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/cdb/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdb
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/cdb/pdbseed/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdb
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/cdb/pdbseed/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdb
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/cdb/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdb
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/cdb/pdbseed/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdb
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/cdb/pdb01/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdb
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/cdb/pdb01/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdb
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00011 to /u01/app/oracle/oradata/cdb/pdb01/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cdb
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00012 to /u01/app/oracle/oradata/cdb/pdb01/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 29-AUG-18

sql statement: alter system archive log current
current log archived

contents of Memory Script:
{
   restore clone force from service  'cdb' 
           archivelog from scn  1459337;
   switch clone datafile all;
}
executing Memory Script

Starting restore at 29-AUG-18
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service cdb
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=29
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service cdb
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=30
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service cdb
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=31
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 29-AUG-18

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=985445350 file name=/u01/app/oracle/oradata/cdb/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=985445350 file name=/u01/app/oracle/oradata/cdb/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=985445350 file name=/u01/app/oracle/oradata/cdb/undotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=985445350 file name=/u01/app/oracle/oradata/cdb/pdbseed/system01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=8 STAMP=985445350 file name=/u01/app/oracle/oradata/cdb/pdbseed/sysaux01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=9 STAMP=985445350 file name=/u01/app/oracle/oradata/cdb/users01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=10 STAMP=985445350 file name=/u01/app/oracle/oradata/cdb/pdbseed/undotbs01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=11 STAMP=985445350 file name=/u01/app/oracle/oradata/cdb/pdb01/system01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=12 STAMP=985445350 file name=/u01/app/oracle/oradata/cdb/pdb01/sysaux01.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=13 STAMP=985445350 file name=/u01/app/oracle/oradata/cdb/pdb01/undotbs01.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=14 STAMP=985445350 file name=/u01/app/oracle/oradata/cdb/pdb01/users01.dbf

contents of Memory Script:
{
   set until scn  1459516;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 29-AUG-18
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 29 is already on disk as file /u01/app/arc/1_29_983447047.dbf
archived log for thread 1 with sequence 30 is already on disk as file /u01/app/arc/1_30_983447047.dbf
archived log for thread 1 with sequence 31 is already on disk as file /u01/app/arc/1_31_983447047.dbf
archived log file name=/u01/app/arc/1_29_983447047.dbf thread=1 sequence=29
archived log file name=/u01/app/arc/1_30_983447047.dbf thread=1 sequence=30
archived log file name=/u01/app/arc/1_31_983447047.dbf thread=1 sequence=31
media recovery complete, elapsed time: 00:00:01
Finished recover at 29-AUG-18
Finished Duplicate Db at 29-AUG-18

RMAN>

5,主库开启日志传输

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(cdb,sbcdb)';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=sbcdb ASYNC  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sbcdb';

6，备库接受并应用日志

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(cdb,sbcdb)';
启动日志应用
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
关闭日志应用
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

7检查同步状态

日志同步状态：

SELECT MAX(SEQUENCE#), THREAD# FROM V$ARCHIVED_LOG -
WHERE RESETLOGS_CHANGE# = (SELECT MAX(RESETLOGS_CHANGE#) FROM V$ARCHIVED_LOG) -
GROUP BY THREAD#;


SELECT DESTINATION, STATUS, ARCHIVED_THREAD#, ARCHIVED_SEQ# -
FROM V$ARCHIVE_DEST_STATUS -
WHERE STATUS <> 'DEFERRED' AND STATUS <> 'INACTIVE';

8 ENABLED_PDBS_ON_STANDBY参数

启用这个参数意味着，可以指定部分PDB，复制到备库中。

The new ENABLED_PDBS_ON_STANDBY initialization parameter enables you to specify a subset of pluggable databases (PDBs) for replication on a physical standby of a multitenant container database (CDB). In releases prior to Oracle Database 12c Release 2 (12.2.0.1), you had to specify either all PDBs or none.
Parameter enabled_pdbs_on_standby and STANDBYS Option With Data Guard Subset Standby (文档 ID 2417018.1)

9 temp 表空间问题

temp 并不会复制到备及

处理方法：
alter tablespace temp add tempfile '/u01/app/oracle/oradata/cdb/pdb01/temp02.dbf' size 50m;
alter tablespace temp drop tempfile '/u01/app/oracle/oradata/cdb/pdb01/temp01.dbf';

10 pdb数据库创建删除命令

set linesize 100;
col name format a50;
select con_id,guid,name from V$CONTAINERS;
drop pluggable database pdb02  including datafiles;
create pluggable database pdb02 admin user pdbadmin identified by admin FILE_NAME_CONVERT=('pdbseed','pdb02');

cdb 数据库搭建dataguard

1, 主数据库环境准备

2,备库环境

3主备数据库tnsname.ora 配置

4，备库启动到nomount，使用rman 同步数据库

5,主库开启日志传输

6，备库接受并应用日志

7检查同步状态

8 ENABLED_PDBS_ON_STANDBY参数

9 temp 表空间问题

10 pdb数据库创建删除命令

热门文章

最新文章

相关课程

相关电子书

相关实验场景