Feroz DBA'S Blog

  ---- Check backup locations SELECT      database_name,      backup_finish_date,      CASE msdb..backupset.type          WHEN 'D' THEN 'Database'          WHEN 'L' THEN 'Log'      END AS backup_type,      physical_device_name,   device_type  FROM msdb.dbo.backupmediafamily  INNER JOIN msdb.dbo.backupset      ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  ORDER BY database_name,backup_finish_date; use msdb select * from backupset select logical_name,physical_name,backup_size from backupfile  EXEC msdb.dbo.sp_delete_database_backuphistory  @database_name = N'AdventureWorks2012' GO select name,recovery_model_desc from sys.databases order by name SELECT database_name AS [Database],  COUNT(backup_set_id) AS Orphans FROM   backupset WHERE  database_name NOT IN (SELECT n...

  Backup full database (CDB + all PDBs): It is similar to non-CDB database. Connect to root container and run the below commands. rman target / backup database;  ---- this will backed up CDB and all PDBs backup archivelog all; backup database plus archivelog; [oracle@oracle12c rman_backup]$ rman target / Recovery Manager: Release 12.2.0.1.0 - Production on Sat Jun 11 17:24:01 2022 Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved. connected to target database: PRDCDB (DBID=3622167870) RMAN> backup database; Starting backup at 11-JUN-22 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=73 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00020 name=/u01/app/oracle/oradata/dupdb/undotbs01.dbf input datafile file number=00018 name=/u01/app/oracle/oradata/dupdb/system01.dbf inp...

  SQL> SET LINES 132 PAGES 100 SQL> COL con_name FORM A15 HEAD "Container|Name" COL tablespace_name FORM A15 COL fsm FORM 999,999,999,999 HEAD "Free|Space Meg." COL apm FORM 999,999,999,999 HEAD "Alloc|Space Meg." -- COMPUTE SUM OF fsm apm ON REPORT BREAK ON REPORT ON con_id ON con_name ON tablespace_name -- WITH x AS (SELECT c1.con_id, cf1.tablespace_name, SUM(cf1.bytes)/1024/1024 fsm FROM cdb_free_space cf1 ,v$containers c1 WHERE cf1.con_id = c1.con_id GROUP BY c1.con_id, cf1.tablespace_name), y AS (SELECT c2.con_id, cd.tablespace_name, SUM(cd.bytes)/1024/1024 apm FROM cdb_data_files cd ,v$containers c2 WHERE cd.con_id = c2.con_id GROUP BY c2.con_id ,cd.tablespace_name) SELECT x.con_id, v.name con_name, x.tablespace_name, x.fsm, y.apm FROM x, y, v$containers v WHERE x.con_id = y.con_id AND x.tablespace_name = y.tablespace_name AND v.con_id = y.con_id UNION SELECT vc2.con_id, vc2.name, tf.tablespace_name, null, SUM(tf.bytes)/1024/1024 FROM v$contain...

  Note:  Generally, Container database won't let open all PDBs in READ-WRITE mode after each database restarts and to make all the PDBs to open in READ-WRITE mode we need to create a trigger to achieve this. Method-1: Using SAVE STATE option SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 1593835520 bytes Fixed Size                  8793256 bytes Variable Size             956302168 bytes Database Buffers          620756992 bytes Redo Buffers                7983104 bytes Database mounted. Database opened. SQL> show pdbs     CON_ID CON_NAME                       OPEN MODE  RESTRICTED ---------- ------------------------------ ---------- ----------   ...

Cloning from PDB1 to PDB4  SQL> -- cloning PDB SQL> alter pluggable database pdb1 close immediate; Pluggable database altered. SQL> alter pluggable database pdb1 open read only; Pluggable database altered. SQL> create pluggable database pdb4 from pdb1 file_name_convert=('/u01/app/oracle/oradata/prdcdb/pdb1/','/u01/app/oracle/oradata/prdcdb/pdb4/'); Pluggable database created. SQL> alter pluggable database pdb1 close immediate; Pluggable database altered. SQL> alter pluggable database pdb1 open; Pluggable database altered. SQL> alter pluggable database pdb4 open; Pluggable database altered. SQL> show pdbs     CON_ID CON_NAME                       OPEN MODE  RESTRICTED ---------- ------------------------------ ---------- ----------          2 PDB$SEED                       READ ONLY  NO   ...

Plug pdb5 and unplug pdb4 in the same container database (CDB) SQL> show pdbs     CON_ID CON_NAME                       OPEN MODE  RESTRICTED ---------- ------------------------------ ---------- ----------          2 PDB$SEED                       READ ONLY  NO          3 PDB1                           READ WRITE NO          4 PDB2                           READ WRITE NO          5 PDB4                           READ WRITE NO          6 PDB3                      ...

Shutdown a container database (CDB) sqlplus / as sysdba shutdown immediate Startup a container database (CDB) sqlplus / as sysdba startup Shutdown a Pluggable database (PDB) SQL> alter pluggable database pdb1 close immediate; Pluggable database altered. Check the status of pluggable database (PDB) SQL> select name, open_mode from v$pdbs where name='PDB1'; NAME       OPEN_MODE ---------- ---------- PDB1       MOUNTED SQL> select name,open_mode from v$containers where name='PDB1'; NAME       OPEN_MODE ---------- ---------- PDB1       MOUNTED Startup pluggable database (PDB) SQL> alter pluggable database pdb1 open; Pluggable database altered. Stop all pluggable databases (PDBs) SQL> alter pluggable database all close immediate; Pluggable database altered. Startup all pluggable databases (PDBs) SQL> alter pluggable database all open; Pluggable database altered.

 How to run expdp in Pluggable database (PDB): Step-1: PDB services should be registered in the listener. [oracle@oracle12c ~]$ lsnrctl status |grep pdb1 Service "pdb1" has 1 instance(s). Step-2: Add PDB entries in TNS file. PDB1 =   (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = oracle12c.localdomain)(PORT = 1521))     )     (CONNECT_DATA =       (SERVICE_NAME = pdb1)     )   ) Step-3: Create a data pump directory under the PDB. SQL> alter session set container=pdb1; Session altered. $ mkdir -p /u01/pdb1_expdir SQL> create directory pdb1_expdir as '/u01/pdb1_expdir'; Directory created. SQL> grant read,write on DIRECTORY pdb1_expdir to HR; Grant succeeded. Step-4: Backup the PDB. expdp HR/HR@pdb1 directory=pdb1_expdir dumpfile=pdb1_hr.dmp logfile=exp_pdb1_hr.log schemas=HR Export logfile: [oracle@oracle12c ~]$ expdp HR/HR@pdb1 directory=pdb1_expdir dumpfile=pdb1_hr....

 Non-CDB database name: dupdb CDB database name: prdcdb Step-1: Shutdown non-cdb database (dupdb) SQL> show parameter uniq NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ db_unique_name                       string      DUPDB SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. Step-2: Startup non-cdb database in read-only mode SQL> startup mount ORACLE instance started. Total System Global Area 1073741824 bytes Fixed Size                  8628936 bytes Variable Size             373294392 bytes Database Buffers          687865856 bytes Redo Buffers        ...