PDB clone at Primary side

jnrjian

已于 2023-12-11 20:13:04 修改

阅读量135

点赞数

文章标签： oracle

于 2023-12-11 01:13:01 首次发布

本文链接：https://blog.csdn.net/jnrjian/article/details/134917538

版权

GOAL

This Document explains the precautionary steps and troubleshooting methods if any PDB clone at Primary side.

SOLUTION

NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data
from the Oracle sample schema(s), Public Documentation delivered with an Oracle database product or other training material.
Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.

For the purposes of this document, the following fictitious environment is used as an example to describe the procedure:

Dbname :boston,chicago
pdbname :- pdbboston,pdbchicago

CASE- I : Primary and Standby have same directory structure

Create the same directory which is mentioned in file_name_convert of PDB clone command.

For example,

CREATE PLUGGABLE DATABASE pdbboston
ADMIN USER <username> IDENTIFIED BY <password>
FILE_NAME_CONVERT = ('/oradata/boston/pdbseed/','/oradata/pdbboston/pdbseed/');

Make sure /oradata/pdbboston/pdbseed/' exist on both primary and standby.

CASE - II : Primary and Standby have different file directory

1. Create new directory

Ex,

On Standby if new PDB restored to /oradata/pdbchicago/pdbseed/

2. Add new directory change in db_file_name_convert.

db_file_name_convert='<other directory changes>','<pdb directory changes>'

Example,

db_file_name_convert='boston','chicago','pdbboston','pdbchicago'

CASE - III

If datafile resides in ASM copy datafile's using ASMCMD.

The copied directory should be,

<db_create_file_dest>/<db_unique_name>/<GUID>/datafile

Get the conid using,

select con_id,pdb,name from cdb_services;

Findout GUID using,

SQL>SELECT guid FROM V$CONTAINERS WHERE con_id=3;

CASE - IV

On Standby If the directory structure doen't exist or db_file_name_convert not defined then MRP crash with ORA-01111.

-----Standby Alert log "/alert_chicago.log"-------------

MRP0: Background Media Recovery terminated with error 1111
Errors in file /u01/app/oracle/diag/rdbms/chicago/chicago/trace/chicago_mrp0_12200.trc:
ORA-01111: name for data file 13 is unknown - rename to correct file
ORA-01110: data file 13: '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/UNNAMED00013'
ORA-01157: cannot identify/lock data file 13 - see DBWR trace file
ORA-01111: name for data file 13 is unknown - rename to correct file
ORA-01110: data file 13: '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/UNNAMED00013'
Managed Standby Recovery not using Real Time Apply

SOLUTION
----------------

Verify the datafile name on primary.

select file#,name from v$datafile; <------------both primary and standby

On Standby rename to correct value.

SQL>alter database rename file '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/UNNAMED00012' to '/oradata/pdbchicago/pdbseed/system01.dbf';
SQL>alter database rename file '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/UNNAMED00013' to '/oradata/pdbchicago/pdbseed/sysaux01.dbf';

or else do RMAN restore.

RMAN>run
{
set newname for datafile 13 to '/oradata/pdbchicago/pdbseed/sysaux01.dbf';
restore datafile 13;
}

Start MRP,

SQL>alter database recover managed standby database disconnect;

----------------------- PDB级恢复datafile--------------------------

Opening a newly plugged-in PDB in a standby database running active dataguard can return ORA-65011 despite the fact that it was plugged in and opened in the primary with no errors. The ‘show pdbs’ command in the standby will list the new PDB, but will not allow it to open. This occurs despite TDE tablespaces, RAC (Real Application Cluster) and/or ASM (Automatic Storage Management).

Standby site:

Result of 'show pdbs' command before PDB plugin at Primary site
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY NO
         3 PDB1                           READ ONLY NO

Result of 'show pdbs' command after PDB plugged-in at Primary site
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY NO
         3 PDB1                           READ ONLY NO
         4 PLUGIN_PDB                     MOUNTED
SQL>
SQL> alter pluggable database PLUGIN_PDB open;
alter pluggable database PLUGIN_PDB open
*
ERROR at line 1:
ORA-65011: Pluggable database PLUGIN_PDB does not exist.

CHANGES

NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample schema(s), Public Documentation delivered with an Oracle database product or other training material. Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.

For the purposes of this document, the following fictitious environment is used as an example to describe the procedure:

Database Name: ORCL2, ORCLA2

Pluggable Database Names: PDB1, PLUGIN_PDB

Disk Group Name: +DATA

Performed a PDB plugin at Primary site without placing datafiles in appropriate ways at Standby site.

CAUSE

Copying datafiles from source location to Standby site has failed with some reasons then a plugged-in PDB having no datafiles was created in Standby site. This situation easily occurs if neither standby_pdb_source_file_dblink nor standby_pdb_source_file_directory is specified. Datafiles pre-copy to a directory in DB_CREATE_FILE_DEST at Standby site was not performed unintentionally.

Create pluggable database PLUGIN_DB using XML file completes with no error but these errors are reported in alert log in Standby site.

PR00 (PID:20592): MRP0: Background Media Recovery terminated with error 1274
2020-02-07T04:32:50.721922+00:00
Errors in file /u05/app/database/diag/rdbms/orcl2/ORCL2/trace/ORCL2_pr00_20592.trc:
ORA-01274: cannot add data file that was originally created as '+DATA/ORCL2/9DF3D723E05E4C3BE0530A10C40AEF8F/DATAFILE/system.313.1031718769'
ORA-01565: error in identifying file '+DATA'
ORA-17503: ksfdopn:2 Failed to open file +DATA
ORA-15045: ASM file name '+DATA' is not in reference form
PR00 (PID:20592): Managed Standby Recovery not using Real Time Apply
Recovery interrupted!

Standby site:
SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY NO
         3 PDB1                           READ ONLY NO
         4 PLUGIN_PDB                     MOUNTED

These errors result in no datafiles associated to CON_ID = 4 (the plugged-in PDB).

SQL> select con_id,file#,name from v$datafile order by con_id,file#;

    CON_ID      FILE#   NAME
---------- ---------- ------------------------------------------------------------------------------------------------------------------------
         1          1    +DATA/ORCLA2/DATAFILE/system.335.1032252797

         1          3    +DATA/ORCL2/DATAFILE/sysaux.387.1032252797

         1          5    +DATA/ORCL2/DATAFILE/undotbs1.385.1032252807

         1          7    +DATA/ORCL2/DATAFILE/users.383.1032252809

         2          2    +DATA/ORCL2/9DE211B2C355239DE053F616C40AA3BE/DATAFILE/system.334.1032252797

         2          4    +DATA/ORCL2/9DE211B2C355239DE053F616C40AA3BE/DATAFILE/sysaux.386.1032252799

         2          6    +DATA/ORCL2/9DE211B2C355239DE053F616C40AA3BE/DATAFILE/undotbs1.384.1032252807

         3          8    +DATA/ORCL2/9DE2A851B0E873A0E053F616C40AD24F/DATAFILE/system.382.1032252809

         3          9    +DATA/ORCL2/9DE2A851B0E873A0E053F616C40AD24F/DATAFILE/sysaux.381.1032252811

         3         10    +DATA/ORCL2/9DE2A851B0E873A0E053F616C40AD24F/DATAFILE/undotbs1.380.1032252811

         3         11    +DATA/ORCL2/9DE2A851B0E873A0E053F616C40AD24F/DATAFILE/users.379.1032252815

         3         12    +DATA/ORCL2/9DE2A851B0E873A0E053F616C40AD24F/DATAFILE/test.378.1032252815

12 rows selected.

SQL>

SOLUTION

To avoid this situation please make sure an access to datafiles in source location from Standby site is configured appropriately before performing plugin at the primary site. To recovery this situation, there are some options depends on system usage and condition. If neither re-creating Standby site nor refreshing Standby site using RECOVER STANDBY DATABASE command is acceptable, PDB level recovery is required using similar way to steps of Deferred PDB Recovery.

For the purposes of this document, the following fictitious environment is used as an example to describe the step-by-step to recover this situation.

DB_UNIQUE_NAME and Service name of Primary site : ORCL1
DB_UNIQUE_NAME and Service name of Standby site : ORCL2
PDB name of plugged-in PDB : PLUGIN_PDB
GUID of plugged-in PDB : 9E71C398289D0EFAE0530A10C40AD04B
Directory Name: /u05/app/oracle including all sub-directories

1. In the primary site:
Verify the file# and name of PLUGIN_PDB at the primary site then close the plugged-in PDB.

SQL> alter session set container = PLUGIN_PDB;
SQL> select con_id,file#,name from v$datafile order by con_id,file#;
CON_ID FILE# NAME
---------- ---------- ----------------------------------------------------------------
4 187 +DATA/ORCL1/9E71C398289D0EFAE0530A10C40AD04B/DATAFILE/system.266.1032338177
4 188 +DATA/ORCL1/9E71C398289D0EFAE0530A10C40AD04B/DATAFILE/sysaux.416.1032338177
4 189 +DATA/ORCL1/9E71C398289D0EFAE0530A10C40AD04B/DATAFILE/undotbs1.261.1032338177

SQL> alter session set container = CDB$ROOT;
SQL> alter pluggable database PLUGIN_PDB close;

2. In the standby site:
The following steps must be executed at the standby site. The commands will be executed within SQL*Plus, DataGuard Broker interface (DGMGRL), and RMAN

a. In SQL*Plus: restart the standby database and disable recovery of the plugged-in PDB

SQL> shutdown immediate;
SQL> startup mount;
SQL> alter session set container = PLUGIN_PDB;
SQL> alter pluggable database PLUGIN_PDB disable recovery;
SQL>

At this point, dummy datafiles (UNNAMED) have been added to the controlfile and are associated to the plugged-in PDB.

SQL> select con_id,file#,name from v$datafile order by con_id,file#;
CON_ID FILE# NAME
---------- ---------- ----------------------------------------------------------------
         4        187 /u05/app/oracle/dbs/UNNAMED00187
         4        188 /u05/app/oracle/dbs/UNNAMED00188
         4        189 /u05/app/oracle/dbs/UNNAMED00189

b. In Data Guard Broker (DGMGRL): Disable REDO Apply

DGMGRL> edit database ORCL2 set state='apply-off';

c. In RMAN: restore the datafiles from the primary site. Set the appropriate datafile name to be created in the standby site. Finally, the ‘switch datafile’ command will update the controlfile with the appropriate datafiles’ information.

RMAN> RUN
{
SET NEWNAME FOR DATAFILE 187 TO '+DATA/ORCL2/9E71C398289D0EFAE0530A10C40AD04B/DATAFILE/system.266.1032338177';
SET NEWNAME FOR DATAFILE 188 TO '+DATA/ORCL2/9E71C398289D0EFAE0530A10C40AD04B/DATAFILE/sysaux.416.1032338177';
SET NEWNAME FOR DATAFILE 189 TO '+DATA/ORCL2/9E71C398289D0EFAE0530A10C40AD04B/DATAFILE/undotbs1.261.1032338177';
restore datafile 187,188,189 from service ORCL1;
}

RMAN>
RMAN> switch datafile 187 to copy;
RMAN> switch datafile 188 to copy;
RMAN> switch datafile 189 to copy;

d. In SQL*Plus: re-enable recovery for the plugged-in PDB. Then open the database.

Verify all datafiles in the plugged-in PDB are corrected.

SQL> alter session set container = PLUGIN_PDB;
SQL> select con_id,file#,name from v$datafile order by con_id,file#;
CON_ID FILE# NAME
---------- ---------- ----------------------------------------------------------------
4 187 +DATA/ORCL2/9E71C398289D0EFAE0530A10C40AD04B/DATAFILE/system.266.1032338177
4 188 +DATA/ORCL2/9E71C398289D0EFAE0530A10C40AD04B/DATAFILE/sysaux.416.1032338177
4 189 +DATA/ORCL2/9E71C398289D0EFAE0530A10C40AD04B/DATAFILE/undotbs1.261.1032338177

SQL> alter pluggable database PLUGIN_PDB enable recovery;
SQL> alter session set container = CDB$ROOT;
SQL> alter database open;
SQL> recover managed standby database disconnect;

e. In Data Guard Broker (DGMGRL): re-enable REDO Apply.

DGMGRL> edit database ORCL2 set state='apply-on';

f. In SQL*Plus: open all the pluggable databases at the primary and standby sites. This time PLUGIN_PDB will be opened successfully as well as PDB1.

SQL> alter pluggable database all open;