multitenant - ORACLEAGENT BLOG

Change PDB name with DBNEWID Utility in 19c

DBNEWID Utility:

NID utility has been introduced with Oracle 10g to change the database name and id. Without NID changing the DBNAME is only possible by recreating the control files. Changing the DBID was not possible before, as this is the database unique identifier. Giving a database a new DBNAME is common after migration of a database instance using Data Guard or duplicate where DBNAME and DB_UNIQUE_NAME differs and shall be synchronized afterwards. In some situation an ORA-01103 error occurs and changing the DBNAME to a new value may be needed.

OVERVIEW:

Verifying the pluggable database
For rename the pluggable database, We need to open the database in Restricted Mode
Set the container database name
Renaming the pluggable database
Open the pluggable database

Note : For an example , I am taking Source PDB “Mumbai” and renaming as “Noida”,

Step 1:

Verifying the available pluggable database in our local CDB

SQL> select con_id,name,open_mode from v$Pdbs;

Step 2:

Before we need to rename the pluggable database ,We should bring the PDB close and open it in “RESTRICTED MODE”

alter pluggable database mumbai close;

Step 3:

Opening the PDB in restricted mode,

alter pluggable database mumbai open restricted;

Step 4:

Set the PDB “MUMBAI”

alter session set container=mumbai;

Step 5:

Here we are renaming the pluggable database name “mumbai” as “noida”

alter pluggable database rename global_name to noida;

Step 6:

Here we can check the renamed pdb by issuing the below command

select con_id,name,open_mode from v$Pdbs;

But , the database is in restricted mode so we need to open the database,

Now the database name has been changed .

Convert Non CDB to CDB in Oracle 12c

Description:-

The multitenant option introduced in Oracle Database 12c allows a single container database (CDB) to host multiple separate pluggable databases (PDB). DBMS_PDB package permits to generate an XML metadata file from a non-CDB 12c database, Its like a unplug a Pluggable database. This is the article which will guide step-by-step Converting Non CDB to CDB in Oracle Database 12c Release.

Step 1: Check the database its properly up and running. shutdown the non-CDB and start it in read-only mode.

Non Container Database Name : NONCDB

Container database Name : ORA_CDB

Non Container Database (Noncdb)

[oracle@localhost ~]$ . ora12c.env

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 21 14:45:36 2021

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

NONCDB READ WRITE

SQL> SELECT name, open_mode FROM v$pdbs;
no rows selected

Container database Name (Ora_cdb)

[oracle@localhost ~]$ . oracdb.env

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Jan 21 14:56:55 2021

Connected to an idle instance.

SQL> startup

ORACLE instance started.
Total System Global Area 1258291200 bytes
Fixed Size     8792296 bytes
Variable Size   872417048 bytes
Database Buffers   369098752 bytes
Redo Buffers     7983104 bytes
Database mounted.
Database opened.

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

ORA_CDB READ WRITE

SQL> col name for a20

SQL> select name, open_mode FROM v$pdbs;

NAME OPEN_MODE

PDB$SEED READ ONLY

CDB READ WRITE

[oracle@localhost ~]$ ps -ef | grep pmon

Step 2: Shutdown the non-CDB database and start it in read-only mode.

SQL> shutdown immediate ;

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.
Total System Global Area 1258291200 bytes
Fixed Size     8620224 bytes
Variable Size   872417088 bytes
Database Buffers   369098752 bytes
Redo Buffers     8155136 bytes
Database mounted.

SQL> alter database open read only;

Database altered.

Step 3: Create the XML file needed for cdb conversion. the non-cdb database using the DBMS_PDB_DESCRIBE procedure creates an XML file in the same way that the unplug operation does for a PDB.

SQL> BEGIN
DBMS_PDB.DESCRIBE(
pdb_descr_file => ‘/tmp/noncdb.xml’);
END;
/

Step 4:Shutdown the NON-CDB database ( Noncdb).

SQL> shutdown immediate ;

Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

Step 5: Open the cdb database (ORA_CDB)

[oracle@localhost Desktop]$ cd

[oracle@localhost ~]$ . oracdb.env

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jan 19 20:36:39 2021

Connected to an idle instance.

SQL> startup

SQL> select name,open_mode,cdb from v$database;

NAME OPEN_MODE CDB

ORA_CDB READ WRITE YES

Step 6: Check the compatibility of PDB in ( ORA_CDB).

SQL> SET SERVEROUTPUT ON;
DECLARE
compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(pdb_descr_file => ‘/tmp/noncdb.xml’)
WHEN TRUE THEN ‘YES’
ELSE ‘NO’
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/

Step 7: Check the violations of PDB in (ORA_CDB).

SQL> select name,cause,type,message,status from PDB_PLUG_IN_VIOLATIONS where name=’NONCDB’;

Step 8: Create pluggable database in container db (ORA_CDB).

SQL> create pluggable database pdb1 using ‘/tmp/noncdb.xml’ NOCOPY;

create pluggable database pdb1 using ‘/tmp/noncdb.xml’ NOCOPY

*ERROR at line 1:
ORA-27038: created file already exists
ORA-01119: error in creating database file

‘/u01/app/oracle/oradata/NONCDB/datafile/o1_mf_temp_j0lhxlpy_.tmp’

Note: As tempfile is already there, so mention tempfile reuse tag, to avoid this error.

SQL> create pluggable database pdb1 using ‘/tmp/noncdb.xml’ NOCOPY tempfile reuse;

Step 9: Switch to the PDB container and run the “$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql” script to clean up the new PDB, removing any items that should not be present in a PDB.

SQL> ALTER SESSION SET CONTAINER=pdb1;

SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

Step 10: Startup the PDB and check the open mode PDB.

SQL> alter Pluggable database pdb1 open;

SQL> SELECT name, open_mode FROM v$pdbs;

The non-cdb database has been converted to CDB now successfully.

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

CREATE CONTAINER DATABASE USING DBCA METHOD

In Putty session while we execute ./dbca command we will get this screen.

STEP 1:

select create database option then click next button

STEP 2:

We need to supply Global database name and password

STEP 3:

click finish button to complete database creation

Once progress completed we will get below screen

STEP 5:

Now the database has been created. We can check by logging in.

CREATE CONTAINER DATABASE USING DBCA METHOD

In Putty session while we execute ./dbca command we will get this screen.

STEP 1:

select create database option then click next button

STEP 2:

We need to supply Global database name and password

STEP 3:

click finish button to complete database creation

Once progress completed we will get below screen

STEP 5:

Now the database has been created. We can check by logging in.

CREATING PLUGGABLE DATABASE (PDB) IN DBCA METHOD

In Putty session while we execute ./dbca command we will get this screen. In that screen we need to select manage Pluggable databases option to create new PDB database.

Select Create a Pluggable database option.

Click finish button to complete database creation.

Click on new Pluggable database

Enter Pluggable database name and passwords

We need to chose storage locations. click on next button

Check on the summary of inputs in Pluggable databases

Once all details confirmed click on finish button to progress on installation.

Once progress completed PDB100 plugged in.

We can check pluggable databases in putty session whether its installed properly.

Now we can see that pluggable databases installed successfully.

CREATING PLUGGABLE DATABASE (PDB) IN DBCA METHOD

In Putty session while we execute ./dbca command we will get this screen. In that screen we need to select manage Pluggable databases option to create new PDB database.

Select Create a Pluggable database option.

Click finish button to complete database creation.

Click on new Pluggable database

Enter Pluggable database name and passwords

We need to chose storage locations. click on next button

Check on the summary of inputs in Pluggable databases

Once all details confirmed click on finish button to progress on installation.

Once progress completed PDB100 plugged in.

We can check pluggable databases in putty session whether its installed properly.

Now we can see that pluggable databases installed successfully.

Tag Archives: multitenant