Change CDB and PDB names in 12c

Change CDB name from DEVCDB to TESTCDB

*********************************************

Backup database before performing this action

*********************************************

	

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP MOUNT
ORACLE instance started.
Total System Global Area 2.0133E+10 bytes
Fixed Size                  3721176 bytes
Variable Size            2684356648 bytes
Database Buffers         1.7381E+10 bytes
Redo Buffers               63385600 bytes
Database mounted.
SQL> exit

[oracle@hostname ~]$ nid TARGET=/ DBNAME=TESTCDB
DBNEWID: Release 12.1.0.2.0 - Production on Fri Jul 1 12:44:48 2022
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
Connected to database DEVCDB (DBID=2721637057)
Connected to server version 12.1.0
Control Files in database:
    /oradata/devcdb/oradata/DEVCDB/controlfile/o1_mf_kcx8zx9n_.ctl
    /oradata/devcdb/fast_recovery_area/DEVCDB/controlfile/o1_mf_kcx8zxcj_.ctl
Change database ID and database name DEVCDB to TESTCDB? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 2721637057 to 2850317248
Changing database name from DEVCDB to TESTCDB
    Control File /oradata/devcdb/oradata/DEVCDB/controlfile/o1_mf_kcx8zx9n_.ctl - modified
    Control File /oradata/devcdb/fast_recovery_area/DEVCDB/controlfile/o1_mf_kcx8zxcj_.ctl - modified
    Datafile /oradata/devcdb/oradata/DEVCDB/datafile/o1_mf_system_kcx8xpcx_.db - dbid changed, wrote new name
    Datafile /oradata/devcdb/oradata/DEVCDB/datafile/o1_mf_sysaux_kcx8wm74_.db - dbid changed, wrote new name
    Datafile /oradata/devcdb/oradata/DEVCDB/datafile/o1_mf_undotbs1_kcx8ytp3_.db - dbid changed, wrote new name
    Datafile /oradata/devcdb/oradata/DEVCDB/datafile/o1_mf_system_kcx9039v_.db - dbid changed, wrote new name
    Datafile /oradata/devcdb/oradata/DEVCDB/datafile/o1_mf_users_kcx8yslq_.db - dbid changed, wrote new name
    Datafile /oradata/devcdb/oradata/DEVCDB/datafile/o1_mf_sysaux_kcx9039r_.db - dbid changed, wrote new name
    Datafile /oradata/devcdb/oradata/DEVCDB/E2BA8506A8CF2123E053052AA8C092B1/datafile/o1_mf_system_kcx9b6cy_.db - dbid changed, wrote new name
    Datafile /oradata/devcdb/oradata/DEVCDB/E2BA8506A8CF2123E053052AA8C092B1/datafile/o1_mf_sysaux_kcx9b6d3_.db - dbid changed, wrote new name
    Datafile /oradata/devcdb/oradata/DEVCDB/E2BA8506A8CF2123E053052AA8C092B1/datafile/o1_mf_users_kcx9bb00_.db - dbid changed, wrote new name
    Datafile /oradata/devcdb/oradata/DEVCDB/datafile/o1_mf_temp_kcx901k3_.tm - dbid changed, wrote new name
    Datafile /oradata/devcdb/oradata/DEVCDB/datafile/pdbseed_temp012022-07-01_11-37-02-AM.db - dbid changed, wrote new name
    Datafile /oradata/devcdb/oradata/DEVCDB/E2BA8506A8CF2123E053052AA8C092B1/datafile/o1_mf_temp_kcx9b6d3_.db - dbid changed, wrote new name
    Control File /oradata/devcdb/oradata/DEVCDB/controlfile/o1_mf_kcx8zx9n_.ctl - dbid changed, wrote new name
    Control File /oradata/devcdb/fast_recovery_area/DEVCDB/controlfile/o1_mf_kcx8zxcj_.ctl - dbid changed, wrote new name
    Instance shut down
Database name changed to TESTCDB.
Modify parameter file and generate a new password file before restarting.
Database ID for database TESTCDB changed to 2850317248.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.
[oracle@hostname ~]$

SQL> startup nomount;
ORACLE instance started.
Total System Global Area 2.0133E+10 bytes
Fixed Size                  3721176 bytes
Variable Size            2684356648 bytes
Database Buffers         1.7381E+10 bytes
Redo Buffers               63385600 bytes

SQL> alter system set db_name='TESTCDB' scope=spfile;
System altered.

SQL> SHUTDOWN IMMEDIATE;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> exit

[oracle@hostname ~]$ export ORACLE_SID=TESTCDB

SQL> STARTUP MOUNT
ORACLE instance started.
Total System Global Area 2.0133E+10 bytes
Fixed Size                  3721176 bytes
Variable Size            2684356648 bytes
Database Buffers         1.7381E+10 bytes
Redo Buffers               63385600 bytes
Database mounted.
SQL>

SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 DEVPDB                         MOUNTED

SQL> alter pluggable database DEVPDB open;
Pluggable database altered.

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 DEVPDB                         READ WRITE NO

SQL> select name from v$database;
NAME
---------
TESTCDB

SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
TESTCDB

SQL> show parameter db_name
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      TESTCDB



    

Change PDB name from DEVPDB to TESTPDB (NON TDE Environment)

*********************************************

Backup database before performing this action

*********************************************

	

SQL> alter pluggable database DEVPDB close;
Pluggable database altered.

SQL> alter pluggable database DEVPDB unplug into '/orabin/app/product/12.1.0/dbs/DEVPDB_meta.xml';
Pluggable database altered.

SQL> drop pluggable database DEVPDB;
Pluggable database dropped.

SQL> create pluggable database TESTPDB using '/orabin/app/product/12.1.0/dbs/DEVPDB_meta.xml' NOCOPY;
Pluggable database created.

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TESTPDB                        MOUNTED

SQL> alter pluggable database TESTPDB open;
Pluggable database altered.

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TESTPDB                        READ WRITE NO

SQL> alter pluggable database all save state instances=all;
Pluggable database altered.

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TESTPDB                        READ WRITE NO

SQL> show parameter service
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
service_names                        string      TESTCDB


SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@hostname ~]$



    

Change PDB name from DEVPDB to TESTPDB (TDE Environment)

*********************************************

Backup database before performing this action

*********************************************

In RAC Environment run the below commands from only one node, close the PDB on other nodes using below commands

	

alter pluggable database DEVPDB close immediate instances=all ;

alter pluggable database DEVPDB open restricted ;

alter session set container=DEVPDB ;

alter pluggable database rename global_name to TESTPDB ;

alter pluggable database close immediate ;

alter pluggable database open instances=all ;


    

Script to Monitor Dataguard Log Gap

=> SQL to fetch the log gap

	
[oracle@hostname ~]$ cat /home/oracle/scripts/standby-lag.sql
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') WHEN,': DR is ' || trim(to_char(1440 * (sysdate - max (next_time)),99999.99) ||' minutes behind') LAG from v$archived_log where applied ='YES';
exit;
[oracle@hostname ~]$

    

=> Shell script to email log gap every 4 hours

	

[oracle@hostname ~]$ cat /home/oracle/scripts/standby-lag.sh
#!/bin/bash
##########################################################################################
#
# Purpose     : Script to email standby lag details
# Created on  : 
# Last Update :
#
##########################################################################################
echo -e "Please find the details below : "
echo -e " "
echo -e " "
echo -e "Script Started at `date`"
echo -e " "
export PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/u01/app/oracle/product/12.1.0.2/dbhome_1/bin:/home/oracle/.local/bin:/home/oracle/bin
export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1
export LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0.2/dbhome_1/lib
export ORACLE_SID=PRODCDB
/u01/app/oracle/product/12.1.0.2/dbhome_1/bin/sqlplus -s / as sysdba @/home/oracle/scripts/standby-lag.sql

echo -e " "
echo -e " "
echo -e "Script Ended at `date`"
echo -e " "
mailx -r "smtp_email@domain.com" -s "DR Sync Status" -S smtp="smtp.office365.com:587" -S smtp-use-starttls -S smtp-auth=login -S smtp-auth-user="smtp_email@domain.com" -S smtp-auth-password='smtp_email_password' -S ssl-verify=ignore -S nss-config-dir="/etc/pki/nssdb/" -c "to_user1_email@domain.com" "to_user2_email@domain.com" < /home/oracle/scripts/logs/standby-lag.log
exit;
[oracle@hostname ~]$ 


    

=> Schedule the script in crontab 

	

[oracle@hostname ~]$ crontab -l
00 */4 * * * /home/oracle/scripts/standby-lag.sh > /home/oracle/scripts/logs/standby-lag.log
[oracle@hostname ~]$


    

Sample RMAN backup script

	
#!/bin/bash
#Declare your environment variables
#export ORACLE_SID=
#export ORACLE_HOME=
#export PATH=$PATH:${ORACLE_HOME}/bin
#export LD_LIBRARY_PATH=${ORACLE_HOME}/bin/lib

dd=`date '+%d-%h-%Y'`
mkdir -p /backup_location/$dd
touch /backup_location/$dd/rmanbkup_$dd.log
echo rman backup for PROD started `date` > /backup_location/$dd/rmanbkup_$dd.log

/oracle/db_software/bin/rman target=/ << EOF >> /backup_location/$dd/rmanbkup_$dd.log
CONFIGURE CONTROLFILE AUTOBACKUP ON;
allocate channel for maintenance type disk;
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup_location/$dd/autobackup_control_file%F';
run {
ALLOCATE CHANNEL RMAN_BACK_CH01 TYPE DISK;
ALLOCATE CHANNEL RMAN_BACK_CH02 TYPE DISK;
ALLOCATE CHANNEL RMAN_BACK_CH03 TYPE DISK;
ALLOCATE CHANNEL RMAN_BACK_CH04 TYPE DISK;
ALLOCATE CHANNEL RMAN_BACK_CH05 TYPE DISK;
ALLOCATE CHANNEL RMAN_BACK_CH06 TYPE DISK;
ALLOCATE CHANNEL RMAN_BACK_CH07 TYPE DISK;
ALLOCATE CHANNEL RMAN_BACK_CH08 TYPE DISK;
ALLOCATE CHANNEL RMAN_BACK_CH09 TYPE DISK;
ALLOCATE CHANNEL RMAN_BACK_CH10 TYPE DISK;
ALLOCATE CHANNEL RMAN_BACK_CH11 TYPE DISK;
ALLOCATE CHANNEL RMAN_BACK_CH12 TYPE DISK;
ALLOCATE CHANNEL RMAN_BACK_CH13 TYPE DISK;
ALLOCATE CHANNEL RMAN_BACK_CH14 TYPE DISK;
ALLOCATE CHANNEL RMAN_BACK_CH15 TYPE DISK;
CROSSCHECK BACKUP;
BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT '/backup_location/$dd/databasefiles_%d_%u_%s_%T';
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG ALL FORMAT '/backup_location/$dd/archivelogs_%d_%u_%s_%T';
BACKUP AS COMPRESSED BACKUPSET CURRENT CONTROLFILE FORMAT '/backup_location/$dd/controlfile_%d_%u_%s_%T';
RELEASE CHANNEL RMAN_BACK_CH01;
RELEASE CHANNEL RMAN_BACK_CH02;
RELEASE CHANNEL RMAN_BACK_CH03;
RELEASE CHANNEL RMAN_BACK_CH04;
RELEASE CHANNEL RMAN_BACK_CH05;
RELEASE CHANNEL RMAN_BACK_CH06;
RELEASE CHANNEL RMAN_BACK_CH07;
RELEASE CHANNEL RMAN_BACK_CH08;
RELEASE CHANNEL RMAN_BACK_CH9;
RELEASE CHANNEL RMAN_BACK_CH10;
RELEASE CHANNEL RMAN_BACK_CH11;
RELEASE CHANNEL RMAN_BACK_CH12;
RELEASE CHANNEL RMAN_BACK_CH13;
RELEASE CHANNEL RMAN_BACK_CH14;
RELEASE CHANNEL RMAN_BACK_CH15;
}
EXIT;
EOF