Restoring single Pluggable database (PDB )from CDB backup

  

In this post , we will see how we can restore a single Pluggable database from the CDB backup without need to restore the complete CDB.

Environment setup :

RAC Database         : VO19CDB (vo19cdb1 and vo19cdb2)

GRID Home                 : /u01/app/19.3.0.0/grid

RDBMS Home : /u01/app/oracle/product/19.14.0.0/dbhome_1

Version                         : Oracle Database 19c EE - Production Version 19.14.0.0.0

Scan IP                 : rac19cp-scan

hosts         : 19cracp1 & 19cracp2

CDB and PDB database details.

In this exercise, we will follow below steps.

1) create one test table called, PDB2TEST1_Before_backup in PDB2 and insert one row

2) Take RMAN backup of the CDB,vol9cdb

3) connect to PDB2 and insert 2nd row into the table, PDB2TEST1_Before_backup

4) Also, Create 2nd test table called,  on PDBand insert a row into it.

5) Restore PDB2 from the CDB taken in step 2

6) Once the restore completes of PDB2 pluggable database, we should only see one table , PDB2TEST1_Before_backup and with only 1 row.

Step 1)  Create one test table called, PDB2TEST1_Before_backup in PDB2 and insert one row

In this step, we will connect to the pluggable database, PDB2 with a user and create the table.

Step 2) Take RMAN backup of the CDB, vol9cdb

In this step, we will connect to the catalog and then take the backup of the CDB using below rman command.

Backup command and its output.

Step 3) connect to PDB2 and insert 2nd row into the table, PDB2TEST1_Before_backup

Step 4) Also, Create 2nd test table called, PDB2TEST2_after_backup  on PDB and insert a row into it.

Step 5) Restore PDB2 from the CDB taken in step 2

Below is the RMAN recover command, I have used the set until time clause and the time I used is just about the time when our backup got completed.

Below is the RMAN recover process along with the output.

Step 6) Once the restore completes of PDB2 pluggable database, we should only see one table , PDB2TEST1_Before_backup and with only 1 row.

As you can see from the above output, we can see only one table and that table has only one row that we inserted before taking the backup.

 We will run into below error if we use incorrect time in set until time clause in the recover command.

Hope this helps few people who wants to recover the PDB from CDB backup.

Thanks
Sambaiah Sammeta

AutoUpgrade can now be used for Database patching

AutoUpgrade tool now can be used for Database patching as well. lets explore this post . 

In this Article , we will use AutoUpgrade to patch a 19c multitenant RAC database from 19.14 to 19.16.

Our Database environment:
Servers
19cracs2.localdomain
19cracs1.localdomain

19.14 Database home: /u01/app/oracle/product/19.3.0.0/dbhome_1 
Currently database is using this database home.

Database name : hol19cts

 

Database current patch set

I have installed another home and patched it with 19.16 patch. Below is the database home with 19.16 patch

19.16 Database home : /u01/app/oracle/product/19.16.0.0 

Downloaded 19.16 patch set details:  p34133642_190000_Linux-x86-64.zip
Downloaded latest Opatch :  p6880880_190000_Linux-x86-64.zip

unzipped the above matches to /u01/software/19C_JULY_PSU

Download the latest AutoUpgrade tool : As per Oracle, we should always be using the latest version of AutoUpgrade tool . we can download it from the doc id, AutoUpgrade Tool (Doc ID 2485457.1)

Just like for the database upgrade, AutoUpgrade needs a confoguration file, for the patching also, AutoUpgrade tool need similar configuration file . for this Patching activity, I have created below configuration file. 

[oracle@19cracs1 DBPATCH_1916]$ cat hol19cts_patch.cfg

global.autoupg_log_dir=/u01/software/19C_JULY_PSU/DBPATCH_1916
patch1.sid=hol19cts1
patch1.source_home=/u01/app/oracle/product/19.3.0.0/dbhome_1
patch1.target_home=/u01/app/oracle/product/19.16.0.0
patch1.drop_grp_after_upgrade=yes
[oracle@19cracs1 DBPATCH_1916]$  

As you can see from above, the configuration file has source (current) database home and new home(with 19.16 patched) details .  AutoUpgrade tool, creates the guarantee restore point before it upgrades/patches. We are instructing AutoUpgrade to drop the Guarantee restore point (with patch1.drop_grp_after_upgrade=yes) after the patching completes. 

Run the AutoUpgrade in Analyze mode: 

[oracle@19cracs1 DBPATCH_1916]$  $ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config hol19cts_patch.cfg -mode analyze

AutoUpgrade 22.5.221011 launched with default internal options

Processing config file ...

+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 CDB(s) plus 2 PDB(s) will be analyzed
Type 'help' to list console commands

upg> lsj

+----+---------+---------+---------+-------+----------+-------+----------------------------+
|Job#|  DB_NAME|    STAGE|OPERATION| STATUS|START_TIME|UPDATED|                     MESSAGE|
+----+---------+---------+---------+-------+----------+-------+----------------------------+
 100|hol19cts1|PRECHECKS|EXECUTING|RUNNING|  12:14:20| 2s ago|Loading database information|
+----+---------+---------+---------+-------+----------+-------+----------------------------+

Total jobs 1

 : lsh

upg> lsj

+----+---------+---------+---------+-------+----------+-------+----------------------------+
|Job#|  DB_NAME|    STAGE|OPERATION| STATUS|START_TIME|UPDATED|                     MESSAGE|
+----+---------+---------+---------+-------+----------+-------+----------------------------+| 100|hol19cts1|PRECHECKS|EXECUTING|RUNNING|  12:14:20| 8s ago|Loading database information|
+----+---------+---------+---------+-------+----------+-------+----------------------------+
Total jobs 1
upg> Job 100 completed

------------------- Final Summary --------------------
Number of databases            [ 1 ]
 Jobs finished                  [1]
Jobs failed                    [0]

 

Please check the summary report at:

/u01/software/19C_JULY_PSU/DBPATCH_1916/cfgtoollogs/upgrade/auto/status/status.html
/u01/software/19C_JULY_PSU/DBPATCH_1916/cfgtoollogs/upgrade/auto/status/status.log
[oracle@19cracs1 DBPATCH_1916]$ 

Below are the log locations created by above AutoUpgrade run.

[oracle@19cracs1 DBPATCH_1916]$ ls -ltr

total 4

-rw-r--r--. 1 oracle oinstall 232 Oct 23 12:13 hol19cts_patch.cfg
drwxr-xr-x. 3 oracle oinstall  21 Oct 23 12:14 cfgtoollogs
drwxr-xr-x. 4 oracle oinstall  29 Oct 23 12:14 hol19cts1
[oracle@19cracs1 DBPATCH_1916]$ cd hol19cts1
[oracle@19cracs1 hol19cts1]$ ls -ltr
total 8
drwx------. 2 oracle oinstall 4096 Oct 23 12:14 temp
drwx------. 3 oracle oinstall 4096 Oct 23 12:14 100
[oracle@19cracs1 hol19cts1]$ cd 100
[oracle@19cracs1 100]$ ls -ltr
total 460
-rw-------. 1 oracle oinstall    448 Oct 23 12:14 autoupgrade_err.log
-rw-------. 1 oracle oinstall    608 Oct 23 12:14 autoupgrade_20221023_user.log
drwx------. 2 oracle oinstall   4096 Oct 23 12:14 prechecks
-rw-------. 1 oracle oinstall 458446 Oct 23 12:14 autoupgrade_20221023.log
[oracle@19cracs1 100]$ cd prechecks/

[oracle@19cracs1 prechecks]$ ls -ltr
total 484
-rw-------. 1 oracle oinstall 133370 Oct 23 12:14 prechecks_cdb_root.log
-rw-------. 1 oracle oinstall  92271 Oct 23 12:14 prechecks_pdb_seed.log
-rw-------. 1 oracle oinstall  92175 Oct 23 12:14 prechecks_testpdb.log
-rw-------. 1 oracle oinstall  13812 Oct 23 12:14 hol19cts_checklist.xml
-rw-------. 1 oracle oinstall   5653 Oct 23 12:14 hol19cts_checklist.cfg
-rw-------. 1 oracle oinstall  14722 Oct 23 12:14 hol19cts_checklist.json
-rw-------. 1 oracle oinstall  39968 Oct 23 12:14 hol19cts_preupgrade.html
-rw-------. 1 oracle oinstall  65359 Oct 23 12:14 upgrade.xml
-rw-------. 1 oracle oinstall  22514 Oct 23 12:14 hol19cts_preupgrade.log 

 

[oracle@19cracs1 DBPATCH_1916]$  $ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME/rdbms/admin/autoupgrade.jar -config hol19cts_patch.cfg -mode deploy

AutoUpgrade 22.5.221011 launched with default internal options

Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 CDB(s) plus 2 PDB(s) will be processed

Type 'help' to list console commands

upg> lsj

+----+---------+-----+---------+-------+----------+-------+-------+
|Job#|  DB_NAME|STAGE|OPERATION| STATUS|START_TIME|UPDATED|MESSAGE|
+----+---------+-----+---------+-------+----------+-------+-------+
| 101|hol19cts1|  GRP|EXECUTING|RUNNING|  12:30:12| 1s ago|       |
+----+---------+-----+---------+-------+----------+-------+-------+
Total jobs 1
 

upg> lsj
+----+---------+-----+---------+-------+----------+-------+-------+
|Job#|  DB_NAME|STAGE|OPERATION| STATUS|START_TIME|UPDATED|MESSAGE|
+----+---------+-----+---------+-------+----------+-------+-------+
| 101|hol19cts1|  GRP|EXECUTING|RUNNING|  12:30:12| 4s ago|       |
+----+---------+-----+---------+-------+----------+-------+-------+
Total jobs 1

upg> lsj

+----+---------+---------+---------+-------+----------+-------+----------------------------+
|Job#|  DB_NAME|    STAGE|OPERATION| STATUS|START_TIME|UPDATED|                    
ESSAGE|
+----+---------+---------+---------+-------+----------+-------+----------------------------+
| 101|hol19cts1|PRECHECKS|EXECUTING|RUNNING|  12:30:12| 0s ago|Loading database
nformation|
+----+---------+---------+---------+-------+----------+-------+----------------------------+
Total jobs 1
 upg> lsj+----+---------+---------+---------+-------+----------+-------+----------------------------+

|Job#|  DB_NAME|    STAGE|OPERATION| STATUS|START_TIME|UPDATED|                     MESSAGE|
+----+---------+---------+---------+-------+----------+-------+----------------------------
| 101|hol19cts1|PRECHECKS|EXECUTING|RUNNING|  12:30:12| 2s ago|Loading database information|
+----+---------+---------+---------+-------+----------+-------+----------------------------+
Total jobs 1
upg> lsj
+----+---------+-----+---------+-------+----------+-------+------------------------+
|Job#|  DB_NAME|STAGE|OPERATION| STATUS|START_TIME|UPDATED|                 MESSAGE|
+----+---------+-----+---------+-------+----------+-------+------------------------+

| 101|hol19cts1|DRAIN|EXECUTING|RUNNING|  12:30:12|33s ago|Disabling RAC if present|
+----+---------+-----+---------+-------+----------+-------+------------------------+
Total jobs 1
 upg> lsj
+----+---------+-----+---------+-------+----------+-------+----------------------+
|Job#|  DB_NAME|STAGE|OPERATION| STATUS|START_TIME|UPDATED|               MESSAGE|
+----+---------+-----+---------+-------+----------+-------+----------------------+
| 101|hol19cts1|DRAIN|EXECUTING|RUNNING|  12:30:12| 4s ago|Shutting down database|
+----+---------+-----+---------+-------+----------+-------+----------------------+
Total jobs 1
 
upg> lsj
+----+---------+---------+---------+-------+----------+-------+--------------------+
|Job#|  DB_NAME|    STAGE|OPERATION| STATUS|START_TIME|UPDATED|             MESSAGE|
+----+---------+---------+---------+-------+----------+-------+--------------------+
| 101|hol19cts1|DBUPGRADE|EXECUTING|RUNNING|  12:30:12|14s ago|49%Patching
DB$ROOT|
+----+---------+---------+---------+-------+----------+-------+--------------------+

upg> Job 101 completed

------------------- Final Summary --------------------
Number of databases            [ 1 ]
 
Jobs finished                  [1]
Jobs failed                      [0]
Jobs restored                  [0]
Jobs pending                   [0] 

 Please check the summary report at:
/u01/software/19C_JULY_PSU/DBPATCH_1916/cfgtoollogs/upgrade/auto/status/status.html
/u01/software/19C_JULY_PSU/DBPATCH_1916/cfgtoollogs/upgrade/auto/status/status.log
[oracle@19cracs1 DBPATCH_1916]$

  It took 20 mins for the database patching to complete.  

Apart from the patching, Autoupgrade updates oratab and copies the TN sentries and sqlnet.ora file from source database home to target datgabase homes.

It is cool that we can now use AutoUpgrade for patching as well. 

Things I observed.

 1. We need to install new home and patch it with new patch and keep it ready for AutoUpgrade to do the home switch. this means, we need more space to accommodate multiple DB homes.

2. Patching using AutoUpgrade does required full downtime of the database. At this moment I don't think it supports AutoUpgrade Patching(I don't see any such option at this moment).  I hope we will be able to do rolling patching using AutoUpgrade in its future versions.

Thanks for reading this post, I hope above post helps few of you. 

Thanks
Sambaiah Sammeta

 

 

Enabling TDE in New PDB database.
----------------------------------------------

1. Check if the keystore is already open in the PDB.

a)  set lines 200

        col DATABASE_HOST for a30;
        col HOST_NAME for a15;
        col DATABASE_ROLE for a10
        col OPEN_MODE for a10
        col STARTUP_TIME for a20

        SELECT i.HOST_NAME "DATABASE_HOST" ,i.INSTANCE_NAME "DB_NAME", .DATABASE_ROLE " DATABASE_ROLE", d.OPEN_MODE " OPEN_MODE ", STARTUP_TIME        from GV$DATABASE d, gv$instance i where i.INST_ID=d.INST_ID;

DATABASE_HOST     DB_NAME   DATABASE_ROLE    OPEN_MODE         STARTUP_TIME
--------------------------    ---------------- ------------------------     ------------ -----         ---------------------
vm19rac1.localdomain           wallcdb1         PRIMARY          READ WRITE           10-FEB-21
vm19rac2.localdomain           wallcdb2         PRIMARY          READ WRITE           10-FEB-21

b) Connect to PDB and check the keystore status 

SQL> alter session set container=EVAAPDB;
Session altered.

SQL> show con_name;

CON_NAME
------------------------------
EVAAPDB

set lines 200
col wrl_parameter for a25
col wrl_type for a19
select * from v$encryption_wallet;

WRL_TYPE    WRL_PARAMETER     STATUS     WALLET_TYPE          WALLET_OR KEYSTORE FULLY_BAC CON_ID

------------------- ------------------------- --------------------------------- -------------------- --------- -------- ------ASM      OPEN_NO_MASTER_KEY   AUTOLOGIN            SINGLE    UNITED   UNDEFINED      4

2. If its not open, you can Open the keystore in that PDB 

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY Welcome1;

3. Now generate master encryption key for the PDB

SQL> ADMINISTER KEY MANAGEMENT SET KEY FORCE  IDENTIFIED BY Welcome1 WITH BACKUP;

SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY Welcome1 WITH BACKUP;

ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY Welcome1 WITH BACKUP
*
ERROR at line 1:
ORA-28417: password-based keystore is not open

So use FORCE KEYSTORE 

SQL> ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY Welcome1 WITH BACKUP;

keystore altered.

Check the status again.

set lines 200

col wrl_parameter for a25

col wrl_type for a19

select * from v$encryption_wallet;

WRL_TYPE            WRL_PARAMETER             STATUS                         WALLET_TYPE          WALLET_OR KEYSTORE FULLY_BAC     CON_ID
-------- ------------------------- ------------------------------ -------------------- --------- -------- --------- ----------
ASM        OPEN                           AUTOLOGIN            SINGLE    UNITED   NO                 4

4. Now Create a Encrypted tablespace

SQL> CREATE TABLESPACE ENCRYPTED_TS_ENCRYPTED DATAFILE '+DATA' SIZE 100M AUTOEXTEND ON  maxsize 1G ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT);

Tablespace created.

SQL>  select tablespace_name, encrypted from dba_tablespaces;

TABLESPACE_NAME          ENCRYPTED
------------------------------         ------------------
SYSTEM                                                NO
SYSAUX                                                NO
UNDOTBS1                                           NO
TEMP                                                     NO
USERS                                                   NO
UNDOTBS2                                           NO
ENCRYPTED_TS                                  NO
ENCRYPTED_TS_ENCRYPTED        YES

8 rows selected.

5. Check and change the parameter, encrypt_new_tablespaces to ALWAYS.

SQL> show parameter encrypt;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
encrypt_new_tablespaces              string      CLOUD_ONLY

SQL> alter system set encrypt_new_tablespaces=always scope=both sid='*';

System altered.

SQL>  show parameter encrypt;

NAME                                     TYPE            VALUE
------------------------------------ -----------     ------------------------------
encrypt_new_tablespaces        string              ALWAYS

6.  Check the tablesapces and ENCRYPT ONLINE

 6a) Check the current encrypted tablespaces as below

SQL> select tablespace_name, encrypted from dba_tablespaces;

    TABLESPACE_NAME                 ENCRYPTED
------------------------------          ------------------
SYSTEM                                                NO
SYSAUX                                                NO
UNDOTBS1                                           NO
TEMP                                                     NO
USERS                                                    NO
UNDOTBS2                                           NO
ENCRYPTED_TS                                  NO
ENCRYPTED_TS_ENCRYPTED         YES

8 rows selected.

 6b) Encrypt the tablespace ONLINE as below.

SQL > alter tablespace  ENCRYPTED_TS ENCRYPTION ONLINE USING 'AES256'  ENCRYPT;
Tablespace altered.

 6c) Check the current encrypted tablespaces again as below

SQL> select tablespace_name, encrypted from dba_tablespaces;

TABLESPACE_NAME      ENCRYPTED

------------------------------      ------------------
SYSTEM                                                NO
SYSAUX                                                NO
UNDOTBS1                                           NO
TEMP                                                     NO
USERS                                                    NO
UNDOTBS2                                           NO
ENCRYPTED_TS                                 YES
ENCRYPTED_TS_ENCRYPTED       YES

8 rows selected.