建Oracle19c dataguard with CDB/PDB

实验环境 Lab environment:

Primary db_name: orcl
Primary db_unique_name: orcl
Primary sid: orcl
Primary PDB: p1
Primary server name: c81

Standby db_name: orcl
Standby db_unique_name: orcl_c82
Standby SID: orcl
Standby PDB: p1
Primary server name: c82

Oracle version: 19.3.0.0.0
OS version: CentOS 8.4

prep work
-- make sure force loging is on

SQL> alter database force logging;
Database altered.
SQL> select name,force_logging,log_mode from v$database;

NAME FORCE_LOGGING LOG_MODE
--------- --------------------------------------- ------------
ORCL YES ARCHIVELOG

-- create logfiles for standby database

SQL> select GROUP#,MEMBER from v$logfile;

GROUP# MEMBER

---------- ------------------------------------------

3 /u01/app/oracle/oradata/ORCL/redo03.log

2 /u01/app/oracle/oradata/ORCL/redo02.log

1 /u01/app/oracle/oradata/ORCL/redo01.log

SQL> alter database add standby logfile '/u01/app/oracle/oradata/ORCL/stby_redo01.log' size 209715200;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/ORCL/stby_redo02.log' size 209715200;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/ORCL/stby_redo03.log' size 209715200;

Database altered.

SQL> select GROUP#,MEMBER from v$logfile;

GROUP# MEMBER

---------- ---------------------------------------------

3 /u01/app/oracle/oradata/ORCL/redo03.log

2 /u01/app/oracle/oradata/ORCL/redo02.log

1 /u01/app/oracle/oradata/ORCL/redo01.log

4 /u01/app/oracle/oradata/ORCL/stby_redo01.log

5 /u01/app/oracle/oradata/ORCL/stby_redo02.log

6 /u01/app/oracle/oradata/ORCL/stby_redo03.log

6 rows selected.

-- examine the listener.ora on both servers and make sure they have started

[oracle@c81 admin]$ cat listener.ora 

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = orcl)

(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)

(SID_NAME = orcl)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = c81)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

)

[oracle@c81 admin]$ ssh c82 cat `pwd`/listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = orcl)

(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)

(SID_NAME = orcl)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = c82)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

)

-- define TNS for both databases

[oracle@c81 admin]$ cat tnsnames.ora 

ORCL_c81 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = c81)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL)))

ORCL_c82 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = c82)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL)))

[oracle@c81 admin]$ ssh c82 cat `pwd`/tnsnames.ora

ORCL_C82 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = c82)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))

ORCL_C81 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = c81)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))

-- test connections on both servers

On Primary:
[oracle@c81 ~]$ sqlplus sys/oracle@orcl_c81 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 17 18:05:14 2021

Version 19.3.0.0.0

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

Connected to:

Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> quit

Disconnected from Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@c81 ~]$ sqlplus sys/oracle@orcl_c82 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 17 18:05:21 2021

Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> 

###
On Standby
[oracle@c82 ~]$ sqlplus sys/oracle@orcl_c81 as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 17 18:08:01 2021

Version 19.3.0.0.0

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

Connected to:

Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> quit

Disconnected from Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@c82 ~]$ sqlplus sys/oracle@orcl_c82 as sysdba 

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 17 18:08:09 2021

Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> 

-- change certain parameters in Primary

1* alter system set log_archive_dest_2='service=orcl_c82 async valid_for=(all_logfiles,primary_role) db_unique_name=orcl_c82'

SQL> /

System altered.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

SQL> alter system set log_archive_config='dg_config=(orcl,orcl_c82)';

System altered.

-- create pfile and ship to standby server

SQL> create pfile='/tmp/initorcl.ora' from spfile;

File created.

SQL> quit

Disconnected from Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@c81 ~]$ scp /tmp/initorcl.ora c82:/tmp/.

initorcl.ora 100% 1347 1.0MB/s 00:00

**Build the standby**
-- edit the initorcl.ora file

orcl.__data_transfer_cache_size=0

orcl.__db_cache_size=721420288

orcl.__inmemory_ext_roarea=0

orcl.__inmemory_ext_rwarea=0

orcl.__java_pool_size=0

orcl.__large_pool_size=16777216

orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

orcl.__pga_aggregate_target=67108864

orcl.__sga_target=1107296256

orcl.__shared_io_pool_size=50331648

orcl.__shared_pool_size=285212672

orcl.__streams_pool_size=16777216

orcl.__unified_pga_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'

*.audit_trail='db'

*.compatible='19.0.0'

*.control_files='/u01/app/oracle/oradata/ORCL/control01.ctl','/u01/app/oracle/fast_recovery_area/ORCL/control02.ctl'

*.db_block_size=8192

*.db_name='orcl'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=20485760000

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.enable_pluggable_database=true

*.log_archive_config='dg_config=(orcl,orcl_c82)'

*.log_archive_format='%t_%s_%r.dbf'

*.nls_language='AMERICAN'

*.nls_territory='AMERICA'

*.open_cursors=300

*.pga_aggregate_target=50m

*.processes=300

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=1046m

*.standby_file_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.log_archive_dest_2='service=orcl_c81 async valid_for=(all_logfiles,primary_role) db_unique_name=orcl'

db_unique_name='orcl_c82'

-- make necessary dirs

mkdir /u01/app/oracle/admin/orcl/adump

mkdir /u01/app/oracle/oradata/ORCL

mkdir /u01/app/oracle/fast_recovery_area/ORCL

-- startup nonmount

[oracle@c82 ~]$ echo "startup nomount pfile=/tmp/initorcl.ora" | sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 17 19:38:58 2021

Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> ORACLE instance started.

Total System Global Area 1107296064 bytes

Fixed Size 9133888 bytes

Variable Size 318767104 bytes

Database Buffers 771751936 bytes

Redo Buffers 7643136 bytes

SQL> Disconnected from Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

-- connect rman and run duplicate command

[oracle@c82 ~]$ rman target sys/oracle@orcl_c81 auxiliary sys/oracle@orcl_c82

Recovery Manager: Release 19.0.0.0.0 - Production on Tue Aug 17 19:40:16 2021

Version 19.3.0.0.0

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

connected to target database: ORCL (DBID=1608027085)

connected to auxiliary database: ORCL (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 17-AUG-21

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=22 device type=DISK

contents of Memory Script:

{

backup as copy reuse

passwordfile auxiliary format '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/orapworcl' ;

}

executing Memory Script

Starting backup at 17-AUG-21

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=50 device type=DISK

Finished backup at 17-AUG-21

duplicating Online logs to Oracle Managed File (OMF) location

contents of Memory Script:

{

restore clone from service 'orcl_c81' standby controlfile;

}

executing Memory Script

Starting restore at 17-AUG-21

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 orcl_c81

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/ORCL/control01.ctl

output file name=/u01/app/oracle/fast_recovery_area/ORCL/control02.ctl

Finished restore at 17-AUG-21

contents of Memory Script:

{

sql clone 'alter database mount standby database';

}

executing Memory Script

sql statement: alter database mount standby database

RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/ORCL/system01.dbf conflicts with a file used by the target database

RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/ORCL/sysaux01.dbf conflicts with a file used by the target database

RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/ORCL/undotbs01.dbf conflicts with a file used by the target database

RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf conflicts with a file used by the target database

RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf conflicts with a file used by the target database

RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/ORCL/users01.dbf conflicts with a file used by the target database

RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf conflicts with a file used by the target database

RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/ORCL/p1/system01.dbf conflicts with a file used by the target database

RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/ORCL/p1/sysaux01.dbf conflicts with a file used by the target database

RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/ORCL/p1/undotbs01.dbf conflicts with a file used by the target database

RMAN-05158: WARNING: auxiliary (datafile) file name /u01/app/oracle/oradata/ORCL/p1/users01.dbf conflicts with a file used by the target database

RMAN-05158: WARNING: auxiliary (tempfile) file name /u01/app/oracle/oradata/ORCL/temp01.dbf conflicts with a file used by the target database

RMAN-05158: WARNING: auxiliary (tempfile) file name /u01/app/oracle/oradata/ORCL/pdbseed/temp012021-08-10_20-33-47-011-PM.dbf conflicts with a file used by the target database

RMAN-05158: WARNING: auxiliary (tempfile) file name /u01/app/oracle/oradata/ORCL/p1/temp01.dbf conflicts with a file used by the target database

contents of Memory Script:

{

set newname for tempfile 1 to

"/u01/app/oracle/oradata/ORCL/temp01.dbf";

set newname for tempfile 2 to

"/u01/app/oracle/oradata/ORCL/pdbseed/temp012021-08-10_20-33-47-011-PM.dbf";

set newname for tempfile 4 to

"/u01/app/oracle/oradata/ORCL/p1/temp01.dbf";

switch clone tempfile all;

set newname for datafile 1 to

"/u01/app/oracle/oradata/ORCL/system01.dbf";

set newname for datafile 3 to

"/u01/app/oracle/oradata/ORCL/sysaux01.dbf";

set newname for datafile 4 to

"/u01/app/oracle/oradata/ORCL/undotbs01.dbf";

set newname for datafile 5 to

"/u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf";

set newname for datafile 6 to

"/u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf";

set newname for datafile 7 to

"/u01/app/oracle/oradata/ORCL/users01.dbf";

set newname for datafile 8 to

"/u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf";

set newname for datafile 17 to

"/u01/app/oracle/oradata/ORCL/p1/system01.dbf";

set newname for datafile 18 to

"/u01/app/oracle/oradata/ORCL/p1/sysaux01.dbf";

set newname for datafile 19 to

"/u01/app/oracle/oradata/ORCL/p1/undotbs01.dbf";

set newname for datafile 20 to

"/u01/app/oracle/oradata/ORCL/p1/users01.dbf";

restore

from nonsparse from service

'orcl_c81' 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/ORCL/temp01.dbf in control file

renamed tempfile 2 to /u01/app/oracle/oradata/ORCL/pdbseed/temp012021-08-10_20-33-47-011-PM.dbf in control file

renamed tempfile 4 to /u01/app/oracle/oradata/ORCL/p1/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 17-AUG-21

using channel ORA_AUX_DISK_1

skipping datafile 1; already restored to SCN 3255780

skipping datafile 3; already restored to SCN 3255780

skipping datafile 4; already restored to SCN 3255780

skipping datafile 5; already restored to SCN 2155216

skipping datafile 6; already restored to SCN 2155216

skipping datafile 7; already restored to SCN 3255780

skipping datafile 8; already restored to SCN 2155216

skipping datafile 17; already restored to SCN 2893504

skipping datafile 18; already restored to SCN 2893504

skipping datafile 19; already restored to SCN 2893504

skipping datafile 20; already restored to SCN 2893504

restore not done; all files read only, offline, excluded, or already restored

Finished restore at 17-AUG-21

sql statement: alter system archive log current

contents of Memory Script:

{

switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=4 STAMP=1080848663 file name=/u01/app/oracle/oradata/ORCL/system01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=5 STAMP=1080848663 file name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=6 STAMP=1080848663 file name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=7 STAMP=1080848663 file name=/u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=8 STAMP=1080848663 file name=/u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf

datafile 7 switched to datafile copy

input datafile copy RECID=9 STAMP=1080848663 file name=/u01/app/oracle/oradata/ORCL/users01.dbf

datafile 8 switched to datafile copy

input datafile copy RECID=10 STAMP=1080848663 file name=/u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf

datafile 17 switched to datafile copy

input datafile copy RECID=11 STAMP=1080848663 file name=/u01/app/oracle/oradata/ORCL/p1/system01.dbf

datafile 18 switched to datafile copy

input datafile copy RECID=12 STAMP=1080848663 file name=/u01/app/oracle/oradata/ORCL/p1/sysaux01.dbf

datafile 19 switched to datafile copy

input datafile copy RECID=13 STAMP=1080848663 file name=/u01/app/oracle/oradata/ORCL/p1/undotbs01.dbf

datafile 20 switched to datafile copy

input datafile copy RECID=14 STAMP=1080848663 file name=/u01/app/oracle/oradata/ORCL/p1/users01.dbf

Finished Duplicate Db at 17-AUG-21

-- put standby to managed revover mode

SQL> alter database recover managed standby database disconnect;

Database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

2 PDB$SEED MOUNTED

4 P1 MOUNTED