How to Stop and Start a Pluggable Database - 19c

1. Shutdown a container database (CDB) and all pluggable databases (PDBs)
[code] [oracle@orcl19c admin]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 25 02:29:24 2020 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> SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> SQL> col name format a20 SQL> col open_mode format a20 SQL> select name, open_mode from v$pdbs; NAME OPEN_MODE -------------------- -------------------- PDB$SEED READ ONLY ORCL1PDB MOUNTED PDB1 MOUNTED RCAT READ WRITE SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. [/code]
2. Startup the CDB

[code] [oracle@orcl19c admin]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 25 02:35:15 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 893385440 bytes Fixed Size 9140960 bytes Variable Size 310378496 bytes Database Buffers 566231040 bytes Redo Buffers 7634944 bytes Database mounted. Database opened. SQL> SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> SQL> col name format a20 SQL> col open_mode format a20 SQL> select name, open_mode from v$pdbs; NAME OPEN_MODE -------------------- -------------------- PDB$SEED READ ONLY ORCL1PDB MOUNTED PDB1 MOUNTED RCAT MOUNTED SQL> [/code]
Note: When you start a CDB it does not automatically start the PDBs (you can see all PDBs ORCL1PDB, PDB1 and RCAT in MOUNT state)

3. Start a PDB

To start single PDB
[code] SQL> alter pluggable database ORCL1PDB open; Pluggable database altered. SQL> select name, open_mode from v$pdbs; NAME OPEN_MODE -------------------- -------------------- PDB$SEED READ ONLY ORCL1PDB READ WRITE PDB1 MOUNTED RCAT MOUNTED [/code]
This will open pluggable database ORCL1PDB and you can see PDB1 and RCAT still in MOUNT state

To start all PDB's

[code] SQL> alter pluggable database all open; Pluggable database altered. SQL> select name, open_mode from v$pdbs; NAME OPEN_MODE -------------------- -------------------- PDB$SEED READ ONLY ORCL1PDB READ WRITE PDB1 READ WRITE RCAT READ WRITE [/code]
4. Stop a PDB

To stop single PDB
[code] SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> SQL> alter pluggable database RCAT close immediate; Pluggable database altered. SQL> select name, open_mode from v$pdbs; NAME OPEN_MODE -------------------- -------------------- PDB$SEED READ ONLY ORCL1PDB READ WRITE PDB1 READ WRITE RCAT MOUNTED SQL> [/code]
To stop all PDB's
[code] SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> SQL> alter pluggable database all close immediate; Pluggable database altered. SQL> select name, open_mode from v$pdbs; NAME OPEN_MODE -------------------- -------------------- PDB$SEED READ ONLY ORCL1PDB MOUNTED PDB1 MOUNTED RCAT MOUNTED SQL> [/code]

ORA-01109: database not open

Today I got "ORA-01109: database not open" when trying to connect to RMAN catalog (In my case PDB "RCAT")
[code] [oracle@orcl19c admin]$ rman target sys/oracle@ORCL1 catalog RCATOWN/oracle@rcat Recovery Manager: Release 19.0.0.0.0 - Production on Sat Apr 25 02:05:25 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL1 (DBID=1414679838) RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00554: initialization of internal recovery manager package failed RMAN-04004: error from recovery catalog database: ORA-01109: database not open [/code]

First check your container database status (In my case RCAT)
[code] [oracle@orcl19c admin]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 25 02:12:05 2020 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> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> SQL> col name format a20 SQL> col open_mode format a20 SQL> select name, open_mode from v$pdbs; NAME OPEN_MODE -------------------- -------------------- PDB$SEED READ ONLY ORCL1PDB MOUNTED PDB1 MOUNTED RCAT MOUNTED [/code]
In my case PDB RCAT in MOUNTED state. Connect to container database and open PDB RCAT
[code] SQL> alter session set container=RCAT; Session altered. SQL> alter pluggable database RCAT open; Pluggable database altered. SQL> select name, open_mode from v$pdbs; NAME OPEN_MODE -------------------- -------------------- RCAT READ WRITE [/code]

Now try to connect RMAN catalog
[code] [oracle@orcl19c admin]$ rman target sys/oracle@ORCL1 catalog RCATOWN/oracle@rcat Recovery Manager: Release 19.0.0.0.0 - Production on Sat Apr 25 02:13:49 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL1 (DBID=1414679838) connected to recovery catalog database RMAN> exit Recovery Manager complete. [/code]

Release Schedule of Current Database Releases

Database Release Roadmap

Patching End Dates for Roadmap

Release	Patching End Date	Notes and Exceptions
19c Long-Term Release	Mar 31, 2023 with no ES/ULA Mar 31, 2026 with ES/ULA	Premier Support (PS) ends Mar 31, 2023, Extended Support (ES) fees will be required beginning Apr 01, 2023 through Mar 31, 2026 Error Correction / Patching is available through Mar 31, 2026 with paid ES. Without paid ES, patching is only available until Mar 31, 2023.
18c Annual Release	Jun 08, 2021	Error Correction / Patching is available until Jun 08, 2021 18c is not eligible for Extended Support (ES)
12.2.0.1 Annual Release	* March 31, 2022 with Limited Error Correction from Dec 1, 2020	Error Correction / Patching is available until Nov 30, 2020 Limited Error Correction (Sev 1 and Security Updates only) is available from Dec 1, 2020 - March 31, 2022   See Note 161818.1 for details. 12.2.0.1 is not eligible for Extended Support (ES) Gen 1 ExaCC, OCC DBCS, and ODA will have 3 additional months of support life. The end of life dates for databases on these platforms is: 28-Feb-2021
12.1.0.2 Terminal Release	* Jul 31, 2022 with paid ES, ULA, or EBS waiver	Premier Support (PS) ended Jul 31, 2018 and one year of free Extended Support (ES) was in effect until July 31, 2019. ES fees or a ULA was required beginning 01-Aug-2019 through 31-Jul-2022. Without paid ES or ULA, patching ends 31-Jul-2019.   We have a global ES fee waiver in place for E-Business customers, see details and dates of expiration see: Extended Support Fee Waiver for Oracle Database 12.1 and 11.2 for Oracle E-Business Suite (Doc ID 2522948.1) or Technical Support Policy Document. * One additional year (01-Aug-2021 through 31-July-2022) has been added to the Extended Support timeframe but the following platform exceptions apply: Microsoft Windows and Apple Macintosh were not included in the additional year. The end date for those platforms is still July 31, 2021.
12.1.0.1	Aug 31, 2016	Error Correction / Patching has ended for this release 12.1.0.1 is not eligible for Extended Support (ES) 12.1.0.1 was the last release of Standard Edition (SE) and Standard Edition One (SE1)
11.2.0.4 Terminal Release	Dec 31, 2020 with paid ES, ULA, or EBS waiver	Premier Support (PS) ended 31-Jan-2015 and one year of free Extended Support (ES) was in effect until Dec 31, 2018. ES fees or a ULA was required beginning Jan 1, 2019 through Dec 31, 2020.  At the end of ES, Market Driven Support (MDS) will be available for two additional years.  See https://www.oracle.com/a/ocom/docs/ds-mds-database-11g-r2.pdf We have a global ES fee waiver in place for E-Business customers, see details and dates of expiration see: Extended Support Fee Waiver for Oracle Database 12.1 and 11.2 for Oracle E-Business Suite (Doc ID 2522948.1) or Technical Support Policy Document. Gen 1 ExaCC, OCC DBCS, and ODA will have 3 additional months of support life. The end of life dates for databases on these platforms is: 31-March 2021

ORA-20001: Statistics Advisor: Invalid task name for the current user - DBCA Known Bugs

ORA-12012 , ORA-20001 AND ORA-06512 FROM ORA$AT_OS_OPT_SY_XXX

Bug 25710407

When creating a database using the DBCA from the General_Pupose.dbc or Data_Warehouse.dbc templates (or from the Seed Database) using the 12.2.0.1 Production SH in Linux and Solaris platform, the following errors in the database alert log are returned:

ORA-12012 , ORA-20001 AND ORA-06512 FROM ORA$AT_OS_OPT_SY_XXX

Platforms : Linux 12.2.0.1 Production SH

                Solaris 12.2.0.1 Production SH

The database alert log contents are:

This issue is happening because of the unavailability of the Stats Advisor Tasks from the created database:

Workaround:

Connect to the created database using the SYSDBA privilege and run the following command:

Now there are rows in these queries:

Ref:

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/readm/dbca-known-bugs.html#GUID-CC849FAD-6D19-4ED9-8866-DC9D556651EA

RMAN Backup Archive log files - 19c

Archive log files plays major role when it comes to recovery in database.

Let us see how to take backup of Archive log files.

Step 1: Connect to RMAN
[code] [oracle@orcl19c admin]$ rman target sys/oracle@ORCL1 catalog RCATOWN/oracle@RCAT Recovery Manager: Release 19.0.0.0.0 - Production on Sun Apr 19 18:48:44 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL1 (DBID=1414679838) connected to recovery catalog database RMAN> [/code]
 

Step 2: Take a backup using the following script
[code] RMAN> run { allocate channel a1 device type disk format '/u01/bkp/arc/%U'; backup archivelog all delete all input; }2> 3> 4> allocated channel: a1 channel a1: SID=183 device type=DISK Starting backup at 19-APR-20 current log archived channel a1: starting archived log backup set channel a1: specifying archived log(s) in backup set input archived log thread=1 sequence=1 RECID=7 STAMP=1038163880 channel a1: starting piece 1 at 19-APR-20 channel a1: finished piece 1 at 19-APR-20 piece handle=/u01/bkp/arc/0iuu27tc_1_1 tag=TAG20200419T185123 comment=NONE channel a1: backup set complete, elapsed time: 00:00:01 channel a1: deleting archived log(s) archived log file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_1_1038092356.dbf RECID=7 STAMP=1038163880 channel a1: starting archived log backup set channel a1: specifying archived log(s) in backup set input archived log thread=1 sequence=9 RECID=6 STAMP=1038092357 input archived log thread=1 sequence=10 RECID=4 STAMP=1038092357 input archived log thread=1 sequence=11 RECID=5 STAMP=1038092357 channel a1: starting piece 1 at 19-APR-20 channel a1: finished piece 1 at 19-APR-20 piece handle=/u01/bkp/arc/0juu27td_1_1 tag=TAG20200419T185123 comment=NONE channel a1: backup set complete, elapsed time: 00:00:01 channel a1: deleting archived log(s) archived log file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_9_1030230368.dbf RECID=6 STAMP=1038092357 archived log file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_10_1030230368.dbf RECID=4 STAMP=1038092357 archived log file name=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch1_11_1030230368.dbf RECID=5 STAMP=1038092357 Finished backup at 19-APR-20 Starting Control File and SPFILE Autobackup at 19-APR-20 piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/c-1414679838-20200419-04 comment=NONE Finished Control File and SPFILE Autobackup at 19-APR-20 released channel: a1 RMAN> [/code]

You can specify the DELETE INPUT or DELETE ALL INPUT clauses for the BACKUP ARCHIVELOG command to delete archived logs after they are backed up, eliminating the separate step of manually deleting the archived redo logs. With DELETE INPUT, RMAN only deletes the specific copy of the archived redo log chosen for the backup set. With DELETE ALL INPUT, RMAN will delete each backed-up archived redo log file from all log archiving destinations.

 

Step 3: List backup of archive log
[code] RMAN> list backup of archivelog all; List of Backup Sets =================== BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 410 120.72M DISK 00:00:00 19-APR-20 BP Key: 414 Status: AVAILABLE Compressed: NO Tag: TAG20200419T185123 Piece Name: /u01/bkp/arc/0iuu27tc_1_1 List of Archived Logs in backup set 410 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 1 2520909 18-APR-20 2567598 19-APR-20 BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ --------------- 411 87.83M DISK 00:00:00 19-APR-20 BP Key: 415 Status: AVAILABLE Compressed: NO Tag: TAG20200419T185123 Piece Name: /u01/bkp/arc/0juu27td_1_1 List of Archived Logs in backup set 411 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 9 2376808 31-JAN-20 2498967 18-APR-20 1 10 2498967 18-APR-20 2503560 18-APR-20 1 11 2503560 18-APR-20 2520909 18-APR-20 [/code]

Configure RMAN recovery catalog and Register Database - 19c

Purpose of the Recovery Catalog

A recovery catalog is a database schema used by RMAN to store metadata about one or more Oracle databases. Typically, you store the catalog in a dedicated database. A recovery catalog provides the following benefits:

• A recovery catalog creates redundancy for the RMAN repository stored in the control file of each target database. The recovery catalog serves as a secondary metadata repository. If the target control file and all backups are lost, then the RMAN metadata still exists in the recovery catalog.

• A recovery catalog centralizes metadata for all your target databases. Storing the metadata in a single place makes reporting and administration tasks easier to perform.

Steps to create recovery catalog database

Step 1: Connect to the target database which you want to use as a recovery catalog.

Here in my case, I am using pdb "RCAT" as a recovery catalog

[code] SQL> select name,open_mode from v$database; NAME OPEN_MODE -------------------- -------------------- ORCL1 READ WRITE SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORCL1PDB MOUNTED 4 PDB1 READ WRITE NO 5 RCAT READ WRITE NO SQL> [/code]

Step 2: Change your database to the pdb database. In my case RCAT
[code] SQL> alter session set container=RCAT; Session altered. SQL> show con_name CON_NAME ------------------------------ RCAT SQL> [/code]


Step 3: We need to create tablespace which we need to store backup information of registered target database.
[code] SQL> show parameter db_create_file_dest; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_create_file_dest string /u01/app/oracle/oradata/PDB SQL> SQL> CREATE TABLESPACE RCAT_TBS DATAFILE SIZE 100M; Tablespace created. SQL> [/code]


Step 4: Create user who will act as a recovery catalog owner
[code] SQL> CREATE USER RCATOWN IDENTIFIED BY "oracle" DEFAULT TABLESPACE RCAT_TBS QUOTA UNLIMITED ON RCAT_TBS; User created. [/code]


Step 5: "RECOVERY_CATALOG_OWNER" role is needed for the user to create catalog database
[code] SQL> GRANT RECOVERY_CATALOG_OWNER TO RCATOWN; Grant succeeded. SQL> [/code]


Step 6: Create TNS entry in tnsnames.ora for pdb "RCAT" database
[code] RCAT = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = orcl19c.oracledbashop.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = RCAT.oracledbashop.com) ) ) [/code]


Step 7: Connect RMAN to pdb "RCAT" database with catalog clause
[code] [oracle@orcl19c admin]$ rman catalog RCATOWN/'oracle'@RCAT Recovery Manager: Release 19.0.0.0.0 - Production on Sun Apr 19 18:27:28 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to recovery catalog database RMAN> [/code]

Step 8: Issue create catalog database command 

[code] RMAN> create catalog; recovery catalog created RMAN> [/code]

Step 9: Connect to RMAN with target and cataloe
[code] [oracle@orcl19c admin]$ rman target sys/oracle@ORCL1 catalog RCATOWN/oracle@RCAT Recovery Manager: Release 19.0.0.0.0 - Production on Sun Apr 19 18:31:19 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL1 (DBID=1414679838) connected to recovery catalog database RMAN> [/code]

Step 10: Register database and check the status. The REPORT SCHEMA command lists and displays information about the database files, tablespaces, and so on.
[code] RMAN> register database; database registered in recovery catalog starting full resync of recovery catalog full resync complete RMAN> report schema; Report of database schema for database with db_unique_name ORCL1 List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 910 SYSTEM YES /u01/app/oracle/oradata/ORCL1/system01.dbf 3 540 SYSAUX NO /u01/app/oracle/oradata/ORCL1/sysaux01.dbf 4 330 UNDOTBS1 YES /u01/app/oracle/oradata/ORCL1/undotbs01.dbf 5 270 PDB$SEED:SYSTEM NO /u01/app/oracle/oradata/ORCL1/pdbseed/system01.dbf 6 330 PDB$SEED:SYSAUX NO /u01/app/oracle/oradata/ORCL1/pdbseed/sysaux01.dbf 7 5 USERS NO /u01/app/oracle/oradata/ORCL1/users01.dbf 8 100 PDB$SEED:UNDOTBS1 NO /u01/app/oracle/oradata/ORCL1/pdbseed/undotbs01.dbf 9 270 ORCL1PDB:SYSTEM NO /u01/app/oracle/oradata/ORCL1/orcl1pdb/system01.dbf 10 340 ORCL1PDB:SYSAUX NO /u01/app/oracle/oradata/ORCL1/orcl1pdb/sysaux01.dbf 11 100 ORCL1PDB:UNDOTBS1 NO /u01/app/oracle/oradata/ORCL1/orcl1pdb/undotbs01.dbf 12 5 ORCL1PDB:USERS NO /u01/app/oracle/oradata/ORCL1/orcl1pdb/users01.dbf 13 1 GG_TBS NO /u01/app/oracle/oradata/OGG/datafile/gg_cdb.dbf 14 1 ORCL1PDB:GG_TBS NO /u01/app/oracle/oradata/OGG/datafile/gg_pdb1.dbf 15 1024 ENCRYPT_TEST NO /u01/app/oracle/oradata/ORCL1/encrypt_test.dbf 16 270 PDB1:SYSTEM YES /u01/app/oracle/oradata/ORCL1/PDB1/system01.dbf 17 330 PDB1:SYSAUX NO /u01/app/oracle/oradata/ORCL1/PDB1/sysaux01.dbf 18 100 PDB1:UNDOTBS1 YES /u01/app/oracle/oradata/ORCL1/PDB1/undotbs01.dbf 19 5 PDB1:USERS NO /u01/app/oracle/oradata/ORCL1/PDB1/users01.dbf 20 280 RCAT:SYSTEM YES /u01/app/oracle/oradata/ORCL1/RCAT/system01.dbf 21 340 RCAT:SYSAUX NO /u01/app/oracle/oradata/ORCL1/RCAT/sysaux01.dbf 22 100 RCAT:UNDOTBS1 YES /u01/app/oracle/oradata/ORCL1/RCAT/undotbs01.dbf 23 5 RCAT:USERS NO /u01/app/oracle/oradata/ORCL1/RCAT/users01.dbf 24 100 RCAT:RCAT_TBS NO /u01/app/oracle/oradata/PDB/ORCL1/A3ADAF86A9AD6CE3E05301BEA8AC2362/datafile/o1_mf_rcat_tbs_h9sq14sq_.dbf List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 32 TEMP 32767 /u01/app/oracle/oradata/ORCL1/temp01.dbf 2 36 PDB$SEED:TEMP 32767 /u01/app/oracle/oradata/ORCL1/pdbseed/temp012020-01-20_23-16-38-484-PM.dbf 3 36 ORCL1PDB:TEMP 32767 /u01/app/oracle/oradata/ORCL1/orcl1pdb/temp01.dbf 4 36 PDB1:TEMP 32767 /u01/app/oracle/oradata/ORCL1/PDB1/temp012020-01-20_23-16-38-484-PM.dbf 5 36 RCAT:TEMP 32767 /u01/app/oracle/oradata/ORCL1/RCAT/temp012020-01-20_23-16-38-484-PM.dbf [/code]

Step 11: Take backup with recovery catalog (Optional)
[code] RMAN> backup database; Starting backup at 19-APR-20 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=347 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=00015 name=/u01/app/oracle/oradata/ORCL1/encrypt_test.dbf input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL1/system01.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL1/sysaux01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL1/undotbs01.dbf input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL1/users01.dbf input datafile file number=00013 name=/u01/app/oracle/oradata/OGG/datafile/gg_cdb.dbf channel ORA_DISK_1: starting piece 1 at 19-APR-20 channel ORA_DISK_1: finished piece 1 at 19-APR-20 piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/0cuu2726_1_1 tag=TAG20200419T183653 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00021 name=/u01/app/oracle/oradata/ORCL1/RCAT/sysaux01.dbf input datafile file number=00020 name=/u01/app/oracle/oradata/ORCL1/RCAT/system01.dbf input datafile file number=00022 name=/u01/app/oracle/oradata/ORCL1/RCAT/undotbs01.dbf input datafile file number=00024 name=/u01/app/oracle/oradata/PDB/ORCL1/A3ADAF86A9AD6CE3E05301BEA8AC2362/datafile/o1_mf_rcat_tbs_h9sq14sq_.dbf input datafile file number=00023 name=/u01/app/oracle/oradata/ORCL1/RCAT/users01.dbf channel ORA_DISK_1: starting piece 1 at 19-APR-20 channel ORA_DISK_1: finished piece 1 at 19-APR-20 piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/0duu272d_1_1 tag=TAG20200419T183653 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00010 name=/u01/app/oracle/oradata/ORCL1/orcl1pdb/sysaux01.dbf input datafile file number=00009 name=/u01/app/oracle/oradata/ORCL1/orcl1pdb/system01.dbf input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL1/orcl1pdb/undotbs01.dbf input datafile file number=00012 name=/u01/app/oracle/oradata/ORCL1/orcl1pdb/users01.dbf input datafile file number=00014 name=/u01/app/oracle/oradata/OGG/datafile/gg_pdb1.dbf channel ORA_DISK_1: starting piece 1 at 19-APR-20 channel ORA_DISK_1: finished piece 1 at 19-APR-20 piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/0euu272g_1_1 tag=TAG20200419T183653 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00017 name=/u01/app/oracle/oradata/ORCL1/PDB1/sysaux01.dbf input datafile file number=00016 name=/u01/app/oracle/oradata/ORCL1/PDB1/system01.dbf input datafile file number=00018 name=/u01/app/oracle/oradata/ORCL1/PDB1/undotbs01.dbf input datafile file number=00019 name=/u01/app/oracle/oradata/ORCL1/PDB1/users01.dbf channel ORA_DISK_1: starting piece 1 at 19-APR-20 channel ORA_DISK_1: finished piece 1 at 19-APR-20 piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/0fuu272k_1_1 tag=TAG20200419T183653 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00006 name=/u01/app/oracle/oradata/ORCL1/pdbseed/sysaux01.dbf input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL1/pdbseed/system01.dbf input datafile file number=00008 name=/u01/app/oracle/oradata/ORCL1/pdbseed/undotbs01.dbf channel ORA_DISK_1: starting piece 1 at 19-APR-20 channel ORA_DISK_1: finished piece 1 at 19-APR-20 piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/0guu272n_1_1 tag=TAG20200419T183653 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 19-APR-20 Starting Control File and SPFILE Autobackup at 19-APR-20 piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/c-1414679838-20200419-03 comment=NONE Finished Control File and SPFILE Autobackup at 19-APR-20 RMAN> [/code]   

CREATE PLUGGABLE DATABASE USING DBCA (from PDB$SEED) 19c

RMAN Particular DATAFILE backup 19c

Check DATAFILES
[code] SQL> select file_id,tablespace_name,file_name from dba_data_files; FILE_ID TABLESPACE_NAME FILE_NAME ---------- ------------------------ --------------------------------------------- 1 SYSTEM /u01/app/oracle/oradata/ORCL1/system01.dbf 3 SYSAUX /u01/app/oracle/oradata/ORCL1/sysaux01.dbf 4 UNDOTBS1 /u01/app/oracle/oradata/ORCL1/undotbs01.dbf 7 USERS /u01/app/oracle/oradata/ORCL1/users01.dbf 13 GG_TBS /u01/app/oracle/oradata/OGG/datafile/gg_cdb.dbf 15 ENCRYPT_TEST /u01/app/oracle/oradata/ORCL1/encrypt_test.dbf 6 rows selected. SQL> [/code]


Create sample table and try to insert some records
[code] SQL> create table sample(description varchar2(100)) tablespace users; Table created. SQL> insert into sample values ('#####INSERT - BEFORE BACKUP#####'); 1 row created. SQL> commit; Commit complete. [/code]


Backup datafile USERS
[code] RMAN> backup datafile 7; Starting backup at 18-APR-20 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=184 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=00007 name=/u01/app/oracle/oradata/ORCL1/users01.dbf channel ORA_DISK_1: starting piece 1 at 18-APR-20 channel ORA_DISK_1: finished piece 1 at 18-APR-20 piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/07uu03n8_1_1 tag=TAG20200418T232735 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 18-APR-20 Starting Control File and SPFILE Autobackup at 18-APR-20 piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/c-1414679838-20200418-02 comment=NONE Finished Control File and SPFILE Autobackup at 18-APR-20 RMAN> quit Recovery Manager complete. [/code]

After backup try to insert one more record
[code] SQL> insert into sample values ('#####INSERT - AFTER BACKUP#####'); 1 row created. SQL> commit; Commit complete. [/code]

Now shutdown database and remove datafile USERS
[code] SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> quit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 [oracle@orcl19c ~]$ rm -rf /u01/app/oracle/oradata/ORCL1/users01.dbf [/code]

Try to start database
[code] SQL> startup ORACLE instance started. Total System Global Area 893385440 bytes Fixed Size 9140960 bytes Variable Size 234881024 bytes Database Buffers 641728512 bytes Redo Buffers 7634944 bytes Database mounted. ORA-01157: cannot identify/lock data file 7 - see DBWR trace file ORA-01110: data file 7: '/u01/app/oracle/oradata/ORCL1/users01.dbf' [/code]

We need to restore and recover the datafile USERS and OPEN the database
[code] RMAN> RESTORE DATAFILE 7; Starting restore at 18-APR-20 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=16 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ORCL1/users01.dbf channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/19.0.0/dbhome_1/dbs/07uu03n8_1_1 channel ORA_DISK_1: piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/07uu03n8_1_1 tag=TAG20200418T232735 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 18-APR-20 RMAN> RECOVER DATAFILE 7; Starting recover at 18-APR-20 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at 18-APR-20 RMAN> ALTER DATABASE OPEN; Statement processed [/code]

Check the database status and records in sample table
[code] RMAN> RESTORE DATAFILE 7; Starting restore at 18-APR-20 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=16 device type=DISK channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ORCL1/users01.dbf channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/19.0.0/dbhome_1/dbs/07uu03n8_1_1 channel ORA_DISK_1: piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/07uu03n8_1_1 tag=TAG20200418T232735 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 Finished restore at 18-APR-20 RMAN> RECOVER DATAFILE 7; Starting recover at 18-APR-20 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:00 Finished recover at 18-APR-20 RMAN> ALTER DATABASE OPEN; Statement processed RMAN> QUIT Recovery Manager complete. [oracle@orcl19c ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 18 23:35:29 2020 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> select * from sample; DESCRIPTION --------------------------------------- #####INSERT - BEFORE BACKUP##### #####INSERT - AFTER BACKUP##### SQL> [/code]

RMAN Recovery From Missing All Control Files 19c

RMAN Recovery From Missing All Control File

Note down DBID
[code] SQL> select dbid from v$database; DBID ---------- 1414679838 [oracle@orcl19c ORCL1]$ rman target / Recovery Manager: Release 19.0.0.0.0 - Production on Sat Apr 18 22:49:51 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL1 (DBID=1414679838) RMAN> select name from v$database; using target database control file instead of recovery catalog NAME --------- ORCL1 [/code]


Check RMAN configuration
[code] RMAN> show all; RMAN configuration parameters for database with db_unique_name ORCL1 are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP ON; # default CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/snapcf_orcl1.f'; # default [/code]


Verify CONTROLFILE AUTOBACKUP ON or OFF
If OFF enable using following command

[code] RMAN> configure controlfile autobackup on; new RMAN configuration parameters: CONFIGURE CONTROLFILE AUTOBACKUP ON; new RMAN configuration parameters are successfully stored [/code]


Take database backup using following command
[code] RMAN> backup database; Starting backup at 18-APR-20 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=26 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=00015 name=/u01/app/oracle/oradata/ORCL1/encrypt_test.dbf input datafile file number=00001 name=/u01/app/oracle/oradata/ORCL1/system01.dbf input datafile file number=00003 name=/u01/app/oracle/oradata/ORCL1/sysaux01.dbf input datafile file number=00004 name=/u01/app/oracle/oradata/ORCL1/undotbs01.dbf input datafile file number=00007 name=/u01/app/oracle/oradata/ORCL1/users01.dbf input datafile file number=00013 name=/u01/app/oracle/oradata/OGG/datafile/gg_cdb.dbf channel ORA_DISK_1: starting piece 1 at 18-APR-20 channel ORA_DISK_1: finished piece 1 at 18-APR-20 piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/01uu01kj_1_1 tag=TAG20200418T225202 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00010 name=/u01/app/oracle/oradata/ORCL1/orcl1pdb/sysaux01.dbf input datafile file number=00009 name=/u01/app/oracle/oradata/ORCL1/orcl1pdb/system01.dbf input datafile file number=00011 name=/u01/app/oracle/oradata/ORCL1/orcl1pdb/undotbs01.dbf input datafile file number=00012 name=/u01/app/oracle/oradata/ORCL1/orcl1pdb/users01.dbf input datafile file number=00014 name=/u01/app/oracle/oradata/OGG/datafile/gg_pdb1.dbf channel ORA_DISK_1: starting piece 1 at 18-APR-20 channel ORA_DISK_1: finished piece 1 at 18-APR-20 piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/02uu01kq_1_1 tag=TAG20200418T225202 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00006 name=/u01/app/oracle/oradata/ORCL1/pdbseed/sysaux01.dbf input datafile file number=00005 name=/u01/app/oracle/oradata/ORCL1/pdbseed/system01.dbf input datafile file number=00008 name=/u01/app/oracle/oradata/ORCL1/pdbseed/undotbs01.dbf channel ORA_DISK_1: starting piece 1 at 18-APR-20 channel ORA_DISK_1: finished piece 1 at 18-APR-20 piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/03uu01kt_1_1 tag=TAG20200418T225202 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03 Finished backup at 18-APR-20 Starting Control File and SPFILE Autobackup at 18-APR-20 piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/c-1414679838-20200418-00 comment=NONE Finished Control File and SPFILE Autobackup at 18-APR-20 [/code]

Check the Control file location
[code] SQL> select name from v$controlfile; NAME --------------------------------------------- /u01/app/oracle/oradata/ORCL1/control01.ctl /u01/app/oracle/oradata/ORCL1/control02.ctl [/code]

Shutdown database and delete control files
[code] [oracle@orcl19c ORCL1]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 18 22:55:28 2020 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> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> quit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 [oracle@orcl19c suh_test]$ rm -rf /u01/app/oracle/oradata/ORCL1/control01.ctl [oracle@orcl19c suh_test]$ rm -rf /u01/app/oracle/oradata/ORCL1/control02.ctl [/code]

Try to start the database
[code] SQL> startup ORACLE instance started. Total System Global Area 893385440 bytes Fixed Size 9140960 bytes Variable Size 234881024 bytes Database Buffers 641728512 bytes Redo Buffers 7634944 bytes ORA-00205: error in identifying control file, check alert log for more info [/code]

Recover the control file using RMAN
[code] [oracle@orcl19c ~]$ rman target / Recovery Manager: Release 19.0.0.0.0 - Production on Sat Apr 18 22:57:25 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL1 (not mounted) RMAN> set dbid 1414679838 executing command: SET DBID RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP; Starting restore at 18-APR-20 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=330 device type=DISK channel ORA_DISK_1: looking for AUTOBACKUP on day: 20200418 channel ORA_DISK_1: AUTOBACKUP found: c-1414679838-20200418-00 channel ORA_DISK_1: restoring control file from AUTOBACKUP c-1414679838-20200418-00 channel ORA_DISK_1: control file restore from AUTOBACKUP complete output file name=/u01/app/oracle/oradata/ORCL1/control01.ctl output file name=/u01/app/oracle/oradata/ORCL1/control02.ctl Finished restore at 18-APR-20 RMAN> ALTER DATABASE MOUNT; released channel: ORA_DISK_1 Statement processed RMAN> RESTORE DATABASE; Starting restore at 18-APR-20 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=330 device type=DISK skipping datafile 5; already restored to file /u01/app/oracle/oradata/ORCL1/pdbseed/system01.dbf skipping datafile 6; already restored to file /u01/app/oracle/oradata/ORCL1/pdbseed/sysaux01.dbf skipping datafile 8; already restored to file /u01/app/oracle/oradata/ORCL1/pdbseed/undotbs01.dbf skipping datafile 9; already restored to file /u01/app/oracle/oradata/ORCL1/orcl1pdb/system01.dbf skipping datafile 10; already restored to file /u01/app/oracle/oradata/ORCL1/orcl1pdb/sysaux01.dbf skipping datafile 11; already restored to file /u01/app/oracle/oradata/ORCL1/orcl1pdb/undotbs01.dbf skipping datafile 12; already restored to file /u01/app/oracle/oradata/ORCL1/orcl1pdb/users01.dbf skipping datafile 14; already restored to file /u01/app/oracle/oradata/OGG/datafile/gg_pdb1.dbf channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ORCL1/system01.dbf channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORCL1/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ORCL1/undotbs01.dbf channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ORCL1/users01.dbf channel ORA_DISK_1: restoring datafile 00013 to /u01/app/oracle/oradata/OGG/datafile/gg_cdb.dbf channel ORA_DISK_1: restoring datafile 00015 to /u01/app/oracle/oradata/ORCL1/encrypt_test.dbf channel ORA_DISK_1: reading from backup piece /u01/app/oracle/product/19.0.0/dbhome_1/dbs/01uu01kj_1_1 channel ORA_DISK_1: piece handle=/u01/app/oracle/product/19.0.0/dbhome_1/dbs/01uu01kj_1_1 tag=TAG20200418T225202 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:07 Finished restore at 18-APR-20 RMAN> RECOVER DATABASE; Starting recover at 18-APR-20 using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 11 is already on disk as file /u01/app/oracle/oradata/ORCL1/redo02.log archived log file name=/u01/app/oracle/oradata/ORCL1/redo02.log thread=1 sequence=11 media recovery complete, elapsed time: 00:00:00 Finished recover at 18-APR-20 RMAN> ALTER DATABASE OPEN RESETLOGS; Statement processed RMAN> QUIT Recovery Manager complete. [/code]

Check the status
[code] SQL> select status from v$instance; STATUS ------------ OPEN SQL> select name from v$database; NAME --------- ORCL1 [/code]

How To Enable/Disable Archive Log Mode In Oracle Database 19c

There are 2 types of modes in Oracle Database.

1. Archivelog mode

In this mode, after the online redo logs are filled , it will move to archive location

2. Noarchivelog mode

In this mode, filled online redo logs wont be archives, instead they will be overwritten. 

How to Enable archive log mode:

[code] SQL> select name,log_mode from v$database; NAME LOG_MODE --------- ------------ ORCL1 NOARCHIVELOG SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination /u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch Oldest online log sequence 9 Current log sequence 11 SQL> SQL> show parameter spfile; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/19.0.0 /dbhome_1/dbs/spfileorcl1.ora SQL> SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> startup mount ORACLE instance started. Total System Global Area 893385440 bytes Fixed Size 9140960 bytes Variable Size 234881024 bytes Database Buffers 641728512 bytes Redo Buffers 7634944 bytes Database mounted. SQL> SQL> alter database archivelog; Database altered. SQL> alter database open; Database altered. SQL> select name,log_mode from v$database; NAME LOG_MODE --------- ------------ ORCL1 ARCHIVELOG SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/product/19.0.0/dbhome_1/dbs/arch Oldest online log sequence 9 Next log sequence to archive 11 Current log sequence 11 SQL> [/code]

How to solve ORA-00031: session marked for kill?

Find out thread id that needed to be killed from OS level
[code] select vs.sid, vs.username, vs.osuser, vs.process fg_pid, vp.spid bg_pid from v$session vs, v$process vp where vs.paddr = vp.addr; [/code]
If you know the user detail you can filter the query.
[code] select vs.sid,vs.username,vs.osuser, vs.process,vp.spid from v$session vs, v$process vp where vs.paddr = vp.addr and vs.username='TEST_DAVID' and vs.osuser='TEST_DAVID'; Output: SID USERNAME OSUSER PROCESS SPID ---- --------------- ---------- --------- --------- 10 TEST_DAVID TEST_DAVID 2046:6845 8245 [/code]
Kill the session from OS level

Unix: Run in UNIX as the oracle/root user kill –9 spid

Example from the above output:

[code] kill -9 8245 [/code]

Transparent Data Encryption (TDE) In Oracle 19c

Transparent Data Encryption (TDE) enables you to encrypt data so that only an authorized recipient can read it.

Use encryption to protect sensitive data in a potentially unprotected environment, such as data you placed on backup media that is sent to an off-site storage location. You can encrypt individual columns in a database table, or you can encrypt an entire tablespace.

To use Transparent Data Encryption, you do not need to modify your applications. TDE enables your applications to continue working seamlessly as before. It automatically encrypts data when it is written to disk, and then automatically decrypts the data when your applications access it.

Lets see how to configure TDE

1. Create a wallet/keystore location.

[code] [oracle@orcl19c admin]$ cd $ORACLE_BASE [oracle@orcl19c oracle]$ ls admin audit cfgtoollogs checkpoints diag fast_recovery_area oradata product recovery_area [oracle@orcl19c oracle]$ pwd /u01/app/oracle [oracle@orcl19c oracle]$ cd admin/ [oracle@orcl19c admin]$ ls orcl1 [oracle@orcl19c admin]$ cd orcl1/ [oracle@orcl19c orcl1]$ ls adump dpdump pfile xdb_wallet [oracle@orcl19c orcl1]$ mkdir wallet [oracle@orcl19c orcl1]$ cd wallet/ [oracle@orcl19c wallet]$ pwd /u01/app/oracle/admin/orcl1/wallet [/code]

2. update the wallet/keystore location in sqlnet.ora. It should look like.

[code] [oracle@orcl19c admin]$ pwd /u01/app/oracle/product/19.0.0/dbhome_1/network/admin [oracle@orcl19c admin]$ cat sqlnet.ora # sqlnet.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora # Generated by Oracle configuration tools. NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT) ENCRYPTION_WALLET_LOCATION = (SOURCE =(METHOD = FILE)(METHOD_DATA = (DIRECTORY = /u01/app/oracle/admin/orcl1/wallet))) [/code]

3. Create keystore:

[code] [oracle@orcl19c wallet]$ pwd /u01/app/oracle/admin/orcl1/wallet [oracle@orcl19c wallet]$ ls [oracle@orcl19c wallet]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sat Apr 18 20:01:01 2020 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> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/oracle/admin/orcl1/wallet/' IDENTIFIED BY walletpass#19c; keystore altered. SQL> !ls -lrt total 4 -rw-------. 1 oracle oinstall 2555 Apr 18 20:01 ewallet.p12 [/code]

4. Now open the keystore:

[code] SQL> ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY walletpass#19c; keystore altered. [/code] 

5. Now activate the key:

[code] SQL> SET LINESIZE 100 SQL> SELECT con_id, key_id FROM v$encryption_keys; no rows selected SQL> ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY walletpass#19c WITH BACKUP; keystore altered. SQL> SELECT con_id, key_id FROM v$encryption_keys; CON_ID KEY_ID ---------- ------------------------------------------------------------------------------ 1 ASOSR67lfE9mv12Vw4SWA9AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA SQL> SET LINESIZE 200 SQL> COLUMN wrl_parameter FORMAT A50 SQL> SELECT * FROM v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID ------------------- -------------------------------------------------- ------------------------------ FILE /u01/app/oracle/admin/orcl1/wallet/ OPEN PASSWORD SINGLE NONE NO 1 [/code]

6. create a encrypted a tablespace

[code] SQL> CREATE TABLESPACE ENCRYPT_TEST datafile '/u01/app/oracle/oradata/ORCL1/encrypt_test.dbf' size 1G ENCRYPTION USING 'AES256' DEFAULT STORAGE(ENCRYPT); 2 3 4 Tablespace created. SQL> select tablespace_name,encrypted from dba_tablespaces where tablespace_name='ENCRYPT_TEST'; TABLESPACE_NAME ENC ------------------------------ --- ENCRYPT_TEST YES [/code]

7. Create a table with encrypted column:
[code] SQL> CREATE TABLE test_encrypt ( first_name VARCHAR2(128), last_name VARCHAR2(128), empID NUMBER, salary NUMBER(6) ENCRYPT ) tablespace ENCRYPT_TEST; 2 3 4 5 6 Table created. SQL> select * from dba_encrypted_columns where owner='TEST_ENC'; OWNER TABLE_NAME COLUMN_NAME ENCRYPTION_ALG SAL INTEGRITY_AL --------- -------------------- -------------------- ----------------------- TEST_ENC TEST_ENCRYPT SALARY AES 192 bits key YES SHA-1 [/code]


8. Enable Autologin:
[code] SQL> col WRL_TYPE format a20 col WRL_PARAMETER format a35 SQL> SELECT * FROM v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID --------- ----------------------------------- ------------------------------ -------------------- --------- FILE /u01/app/oracle/admin/orcl1/wallet/ OPEN PASSWORD SINGLE NONE NO 1 [/code]


Here the wallet_type is PASSWORD , i.e every time we restart the database, we need to open the key/wallet explicitly. To avoid this, we can enable auto login ,so that next time when db gets restart, it will open the wallet automatically.

[code] SQL> ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u01/app/oracle/admin/orcl1/wallet/' IDENTIFIED BY walletpass#19c; keystore altered. SQL> SELECT * FROM v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID -------------------- -------------------------------------------------------------- ---------------- FILE /u01/app/oracle/admin/orcl1/wallet/ OPEN PASSWORD SINGLE NONE NO 1 SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 893385440 bytes Fixed Size 9140960 bytes Variable Size 234881024 bytes Database Buffers 641728512 bytes Redo Buffers 7634944 bytes Database mounted. Database opened. SQL> SQL> col WRL_TYPE format a20 SQL> col WRL_PARAMETER format a35 SQL> SELECT * FROM v$encryption_wallet; WRL_TYPE WRL_PARAMETER STATUS WALLET_TYPE WALLET_OR KEYSTORE FULLY_BAC CON_ID -------------------- ----------------------------------- ------------------------------ -------------------- FILE /u01/app/oracle/admin/orcl1/wallet/ OPEN AUTOLOGIN SINGLE NONE NO 1 SQL> [/code]

we can see the wallet opened automatically and the wallet_type has been changed from PASSWORD TO AUTOLOGIN.

For multi-tenant database:

In a multi-tenant database (CDB), the Keystore has to be be created in the ROOT container (CDB$ROOT).

This single Keystore will be shared by all the associated PDBs as well as the CDB$ROOT container.

So for this we need to use CONTAINER=ALL clause to open and activate the keystore  in all pdbs.
[code] ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY walletpass#19c CONTAINER=ALL; ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY walletpass#19c WITH BACKUP CONTAINER=ALL; [/code]