Friday, 4 February 2022

Multitenant Dataguard: Create multitenant database on priamry and standby database using FILE_NAME_CONVERT parameter

Recently we faced some issue on multitenant standby database . As we create pdb on primary same not reflected on standby database as it was conflicting  with destination directory and datafile as well. 

This scenario  about using FILE_NAME_CONVERT parameter we create multitenant  primary pluggable  database and same will reflect on standby database. 

PRIMARY Pluggable database :


SQL> sho pdbs

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


  • NOTE : Create directory in pluggable standby database before creting pluggable database on primary database.

Example: 

[oracle@srv7 ~]$ mkdir -pv /u02/app/oracle/oradata/PRIMARY19C/pdb3

mkdir: created directory ‘/u02/app/oracle/oradata/PRIMARY19C/pdb3’

  • Create Pluggable database on primary database and mention file_name_convert parameter  below and provide directory of new pdb3 we have mentioned  /u02/app/oracle/oradata/PRIMARY19C/pdb3 and it will be created automatically. 

SQL> CREATE PLUGGABLE DATABASE pdb3 ADMIN USER pdb_adm IDENTIFIED BY Password1 FILE_NAME_CONVERT=('/u02/app/oracle/oradata/PRIMARY19C/pdbseed','/u02/app/oracle/oradata/PRIMARY19C/pdb3');


Pluggable database created.

SQL> sho pdbs

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

  • Open primary pluggable database 
SQL> alter pluggable database all open;

Pluggable database altered.

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            READ WRITE NO
         4 PDB3                           READ WRITE NO


SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            READ WRITE NO
         4 PDB3                           READ WRITE NO
         5 PDB4                           READ WRITE NO

  • Check the PDB service entry  

SQL> COLUMN name FORMAT A30
SQL> COLUMN PDB FORMAT a40
SQL>
SQL> set lines 200
SQL> SELECT name, pdb FROM   v$services ORDER BY name;

NAME                           PDB
------------------------------ ----------------------------------------
SYS$BACKGROUND                 CDB$ROOT
SYS$USERS                      CDB$ROOT
pdb                            PDB
pdb3                           PDB3
pdb4                           PDB4
primary19c                     CDB$ROOT
primary19cXDB                  CDB$ROOT

7 rows selected.


SQL> connect sys/sys@PDB3 as sysdba
Connected.
SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         4 PDB3                           READ WRITE NO
SQL>

  • Check datafile details below on primary multitenant database 
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u02/app/oracle/oradata/PRIMARY19C/system01.dbf
/u02/app/oracle/oradata/PRIMARY19C/sysaux01.dbf
/u02/app/oracle/oradata/PRIMARY19C/undotbs01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdbseed/system01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdbseed/sysaux01.dbf
/u02/app/oracle/oradata/PRIMARY19C/users01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdbseed/undotbs01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdb/system01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdb/sysaux01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdb/undotbs01.dbf
/u01/app/oracle/oradata/PRIMARY19C/pdb/users01.dbf

NAME
--------------------------------------------------------------------------------
/u02/app/oracle/oradata/PRIMARY19C/pdb3/system01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdb3/sysaux01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdb3/undotbs01.dbf

  • Check Details on Multitenant standby database 
[oracle@srv7 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 3 20:49:56 2022
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         3 PDB                            MOUNTED
         4 PDB3                           MOUNTED
SQL>  sho pdbs

  • Check datafile details below on Standby multitenant database 
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u02/app/oracle/oradata/PRIMARY19C/system01.dbf
/u02/app/oracle/oradata/PRIMARY19C/sysaux01.dbf
/u02/app/oracle/oradata/PRIMARY19C/undotbs01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdbseed/system01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdbseed/sysaux01.dbf
/u02/app/oracle/oradata/PRIMARY19C/users01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdbseed/undotbs01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdb/system01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdb/sysaux01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdb/undotbs01.dbf
/u01/app/oracle/oradata/PRIMARY19C/pdb/users01.dbf

NAME
--------------------------------------------------------------------------------
/u02/app/oracle/oradata/PRIMARY19C/pdb3/system01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdb3/sysaux01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdb3/undotbs01.dbf

14 rows selected.


Alter log file file standby database :


Recovery created pluggable database PDB3
2022-02-03T20:48:59.217732+05:30
Recovery copied files for tablespace SYSTEM
Recovery successfully copied file /u02/app/oracle/oradata/PRIMARY19C/pdb3/system01.dbf from /u02/app/oracle/oradata/PRIMARY19C/pdbseed/system01.dbf
PDB3(4):WARNING: File being created with same name as in Primary
PDB3(4):Existing file may be overwritten
PDB3(4):Recovery created file /u02/app/oracle/oradata/PRIMARY19C/pdb3/system01.dbf
PDB3(4):Successfully added datafile 25 to media recovery
PDB3(4):Datafile #25: '/u02/app/oracle/oradata/PRIMARY19C/pdb3/system01.dbf'
2022-02-03T20:49:23.126371+05:30
Recovery copied files for tablespace SYSAUX
Recovery successfully copied file /u02/app/oracle/oradata/PRIMARY19C/pdb3/sysaux01.dbf from /u02/app/oracle/oradata/PRIMARY19C/pdbseed/sysaux01.dbf
PDB3(4):WARNING: File being created with same name as in Primary
PDB3(4):Existing file may be overwritten
PDB3(4):Recovery created file /u02/app/oracle/oradata/PRIMARY19C/pdb3/sysaux01.dbf
PDB3(4):Successfully added datafile 26 to media recovery
PDB3(4):Datafile #26: '/u02/app/oracle/oradata/PRIMARY19C/pdb3/sysaux01.dbf'
2022-02-03T20:49:30.846103+05:30
Recovery copied files for tablespace UNDOTBS1
Recovery successfully copied file /u02/app/oracle/oradata/PRIMARY19C/pdb3/undotbs01.dbf from /u02/app/oracle/oradata/PRIMARY19C/pdbseed/undotbs01.dbf
PDB3(4):WARNING: File being created with same name as in Primary
PDB3(4):Existing file may be overwritten
PDB3(4):Recovery created file /u02/app/oracle/oradata/PRIMARY19C/pdb3/undotbs01.dbf
PDB3(4):Successfully added datafile 27 to media recovery
PDB3(4):Datafile #27: '/u02/app/oracle/oradata/PRIMARY19C/pdb3/undotbs01.dbf'
2022-02-03T20:52:50.952812+05:30


  • Same example now we create   PDB4 multitenant database on primary DB
  • NOTE : Create directory in pluggable standby database before creting pluggable database on primary database.
[oracle@srv7 ~]$ mkdir -pv /u02/app/oracle/oradata/PRIMARY19C/pdb4
mkdir: created directory ‘/u02/app/oracle/oradata/PRIMARY19C/pdb4’
[oracle@srv7 ~]$ cd /u02/app/oracle/oradata/PRIMARY19C/pdb4
[oracle@srv7 pdb4]$ ll

Create Pluggable database on primary database and mention file_name_convert parameter  below and provide directory of new pdb4 we have mentioned  /u02/app/oracle/oradata/PRIMARY19C/pdb4 and it will be created automatically.

SQL> CREATE PLUGGABLE DATABASE pdb4 ADMIN USER pdb_adm IDENTIFIED BY Password1 FILE_NAME_CONVERT=('/u02/app/oracle/oradata/PRIMARY19C/pdbseed','/u02/app/oracle/oradata/PRIMARY19C/pdb4');

Pluggable database created.

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            READ WRITE NO
         4 PDB3                           READ WRITE NO
         5 PDB4                           MOUNTED

  • Open primary pluggable database 
SQL> alter pluggable database PDB4 open;

Pluggable database altered.


SQL> sho parameter file_name_convert

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string      /u02/app/oracle/oradata/PRIMAR
                                                 Y19C, /u02/app/oracle/oradata/
                                                 STANDBY19C
log_file_name_convert                string      /u02/app/oracle/oradata/PRIMAR
                                                 Y19C, /u02/app/oracle/oradata/
                                                 STANDBY19C
pdb_file_name_convert                string


SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u02/app/oracle/oradata/PRIMARY19C/system01.dbf
/u02/app/oracle/oradata/PRIMARY19C/sysaux01.dbf
/u02/app/oracle/oradata/PRIMARY19C/undotbs01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdbseed/system01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdbseed/sysaux01.dbf
/u02/app/oracle/oradata/PRIMARY19C/users01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdbseed/undotbs01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdb/system01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdb/sysaux01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdb/undotbs01.dbf
/u01/app/oracle/oradata/PRIMARY19C/pdb/users01.dbf

NAME
--------------------------------------------------------------------------------
/u02/app/oracle/oradata/PRIMARY19C/pdb3/system01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdb3/sysaux01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdb3/undotbs01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdb4/system01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdb4/sysaux01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdb4/undotbs01.dbf

17 rows selected.

SQL>



STANDBY Pluggable database :

[oracle@srv7 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 3 20:49:56 2022
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         3 PDB                            MOUNTED
         4 PDB3                           MOUNTED
SQL>  sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         3 PDB                            MOUNTED
         4 PDB3                           MOUNTED
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u02/app/oracle/oradata/PRIMARY19C/system01.dbf
/u02/app/oracle/oradata/PRIMARY19C/sysaux01.dbf
/u02/app/oracle/oradata/PRIMARY19C/undotbs01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdbseed/system01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdbseed/sysaux01.dbf
/u02/app/oracle/oradata/PRIMARY19C/users01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdbseed/undotbs01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdb/system01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdb/sysaux01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdb/undotbs01.dbf
/u01/app/oracle/oradata/PRIMARY19C/pdb/users01.dbf

NAME
--------------------------------------------------------------------------------
/u02/app/oracle/oradata/PRIMARY19C/pdb3/system01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdb3/sysaux01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdb3/undotbs01.dbf

14 rows selected.

SQL> host

total 0
[oracle@srv7 pdb4]$ pwd
/u02/app/oracle/oradata/PRIMARY19C/pdb4
[oracle@srv7 pdb4]$ ll
total 716828
-rw-r----- 1 oracle oinstall 346038272 Feb  3 20:53 sysaux01.dbf
-rw-r----- 1 oracle oinstall 283123712 Feb  3 20:53 system01.dbf
-rw-r----- 1 oracle oinstall 104865792 Feb  3 20:53 undotbs01.dbf
[oracle@srv7 pdb4]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 3 20:54:21 2022
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> sho parameter file_name_convert

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string      /u02/app/oracle/oradata/STANDB
                                                 Y19C, /u02/app/oracle/oradata/
                                                 PRIMARY19C
log_file_name_convert                string      /u02/app/oracle/oradata/STANDB
                                                 Y19C, /u02/app/oracle/oradata/
                                                 PRIMARY19C
pdb_file_name_convert                string

  • Check datafile details below on primary multitenant database 
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u02/app/oracle/oradata/PRIMARY19C/system01.dbf
/u02/app/oracle/oradata/PRIMARY19C/sysaux01.dbf
/u02/app/oracle/oradata/PRIMARY19C/undotbs01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdbseed/system01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdbseed/sysaux01.dbf
/u02/app/oracle/oradata/PRIMARY19C/users01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdbseed/undotbs01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdb/system01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdb/sysaux01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdb/undotbs01.dbf
/u01/app/oracle/oradata/PRIMARY19C/pdb/users01.dbf

NAME
--------------------------------------------------------------------------------
/u02/app/oracle/oradata/PRIMARY19C/pdb3/system01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdb3/sysaux01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdb3/undotbs01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdb4/system01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdb4/sysaux01.dbf
/u02/app/oracle/oradata/PRIMARY19C/pdb4/undotbs01.dbf

17 rows selected.

  • Check the multitenant database status on standby database 

SQL> @/home/oracle/d

NAME      INSTANCE_NAME    DB_UNIQUE_NAME                 OPEN_MODE            DATABASE_ROLE    CURRENT_SCN        FAILOVER_SCN
--------- ---------------- ------------------------------ -------------------- ---------------- ------------------ ----------------------------------------
PRIMARY1  standby19c       standby19c                     MOUNTED              PHYSICAL STANDBY NO                 0



SQL> sho pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       MOUNTED
         3 PDB                            MOUNTED
         4 PDB3                           MOUNTED
         5 PDB4                           MOUNTED
SQL>


Alter log file file standby database :


Recovery created pluggable database PDB3
2022-02-03T20:48:59.217732+05:30
Recovery copied files for tablespace SYSTEM
Recovery successfully copied file /u02/app/oracle/oradata/PRIMARY19C/pdb3/system01.dbf from /u02/app/oracle/oradata/PRIMARY19C/pdbseed/system01.dbf
PDB3(4):WARNING: File being created with same name as in Primary
PDB3(4):Existing file may be overwritten
PDB3(4):Recovery created file /u02/app/oracle/oradata/PRIMARY19C/pdb3/system01.dbf
PDB3(4):Successfully added datafile 25 to media recovery
PDB3(4):Datafile #25: '/u02/app/oracle/oradata/PRIMARY19C/pdb3/system01.dbf'
2022-02-03T20:49:23.126371+05:30
Recovery copied files for tablespace SYSAUX
Recovery successfully copied file /u02/app/oracle/oradata/PRIMARY19C/pdb3/sysaux01.dbf from /u02/app/oracle/oradata/PRIMARY19C/pdbseed/sysaux01.dbf
PDB3(4):WARNING: File being created with same name as in Primary
PDB3(4):Existing file may be overwritten
PDB3(4):Recovery created file /u02/app/oracle/oradata/PRIMARY19C/pdb3/sysaux01.dbf
PDB3(4):Successfully added datafile 26 to media recovery
PDB3(4):Datafile #26: '/u02/app/oracle/oradata/PRIMARY19C/pdb3/sysaux01.dbf'
2022-02-03T20:49:30.846103+05:30
Recovery copied files for tablespace UNDOTBS1
Recovery successfully copied file /u02/app/oracle/oradata/PRIMARY19C/pdb3/undotbs01.dbf from /u02/app/oracle/oradata/PRIMARY19C/pdbseed/undotbs01.dbf
PDB3(4):WARNING: File being created with same name as in Primary
PDB3(4):Existing file may be overwritten
PDB3(4):Recovery created file /u02/app/oracle/oradata/PRIMARY19C/pdb3/undotbs01.dbf
PDB3(4):Successfully added datafile 27 to media recovery
PDB3(4):Datafile #27: '/u02/app/oracle/oradata/PRIMARY19C/pdb3/undotbs01.dbf'
2022-02-03T20:52:50.952812+05:30
Recovery created pluggable database PDB4
2022-02-03T20:53:08.208662+05:30
Recovery copied files for tablespace SYSTEM
Recovery successfully copied file /u02/app/oracle/oradata/PRIMARY19C/pdb4/system01.dbf from /u02/app/oracle/oradata/PRIMARY19C/pdbseed/system01.dbf
PDB4(5):WARNING: File being created with same name as in Primary
PDB4(5):Existing file may be overwritten
PDB4(5):Recovery created file /u02/app/oracle/oradata/PRIMARY19C/pdb4/system01.dbf
PDB4(5):Successfully added datafile 28 to media recovery
PDB4(5):Datafile #28: '/u02/app/oracle/oradata/PRIMARY19C/pdb4/system01.dbf'
2022-02-03T20:53:39.119703+05:30
Recovery copied files for tablespace SYSAUX
Recovery successfully copied file /u02/app/oracle/oradata/PRIMARY19C/pdb4/sysaux01.dbf from /u02/app/oracle/oradata/PRIMARY19C/pdbseed/sysaux01.dbf
PDB4(5):WARNING: File being created with same name as in Primary
PDB4(5):Existing file may be overwritten
PDB4(5):Recovery created file /u02/app/oracle/oradata/PRIMARY19C/pdb4/sysaux01.dbf
PDB4(5):Successfully added datafile 29 to media recovery
PDB4(5):Datafile #29: '/u02/app/oracle/oradata/PRIMARY19C/pdb4/sysaux01.dbf'
2022-02-03T20:53:45.814030+05:30
Recovery copied files for tablespace UNDOTBS1
Recovery successfully copied file /u02/app/oracle/oradata/PRIMARY19C/pdb4/undotbs01.dbf from /u02/app/oracle/oradata/PRIMARY19C/pdbseed/undotbs01.dbf
PDB4(5):WARNING: File being created with same name as in Primary
PDB4(5):Existing file may be overwritten
PDB4(5):Recovery created file /u02/app/oracle/oradata/PRIMARY19C/pdb4/undotbs01.dbf
PDB4(5):Successfully added datafile 30 to media recovery
PDB4(5):Datafile #30: '/u02/app/oracle/oradata/PRIMARY19C/pdb4/undotbs01.dbf'


Posted by at
Labels: ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)