Unified Audit features of Oracle Database 12c

Article Name: Unified Audit features of Oracle Database 12c

Article URL: http://www.toadworld.com/platforms/oracle/w/wiki/11096.unified-audit-features-of-oracle-database-12c.aspx

Thanks to TOAD WORLD.

Seed Database (pdb$seed) - Read Write OR Read Only Mode in Oracle Database 12c (12.1.0.1.0)

Connect to container database (cdb1)

SQL> connect sys/oracle@cdb1 as sysdba
Connected.

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

Perform close instance operation in seed database (pdb$seed)

SQL> alter pluggable database pdb$seed close;
alter pluggable database pdb$seed close
                         *
ERROR at line 1:
ORA-65017: seed pluggable database may not be dropped or altered

SQL> alter session set "_oracle_script"=TRUE;
Session altered.

Perform close instance operation in seed database (pdb$seed)

SQL> alter pluggable database pdb$seed close immediate instances=all;
Pluggable database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

Open the pdb$seed database in read write mode

SQL> alter pluggable database pdb$seed open read write;
Pluggable database altered.

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> alter session set container=PDB$SEED;
Session altered.

SQL> show con_name

CON_NAME
------------------------------
PDB$SEED

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> show pdbs

    CON_ID CON_NAME  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED  READ WRITE NO

SQL> connect sys/oracle@cdb1 as sysdba
Connected.

Create Pluggable Database (cdb1_pdb4) in Container Database (cdb1)

SQL> show pdbs

    CON_ID CON_NAME        OPEN MODE  RESTRICTED
---------- ------------------------------ -----------------   ----------
2 PDB$SEED        READ WRITE NO
3 CDB1_PDB3        MOUNTED

create pluggable database cdb1_pdb4 admin user user1 identified by oracle
*
ERROR at line 1:
ORA-65081: database or pluggable database is not open in read only mode

Close the pdb$seed and open it in read only mode

SQL> alter pluggable database pdb$seed close immediate instances=all;
Pluggable database altered.

SQL> alter pluggable database pdb$seed open read only;
Pluggable database altered.

SQL> show con_name

CON_NAME
------------------------------
PDB$SEED

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY

Set the parameter "_oracle_script" and create pluggable database (cdb1_pdb4)

SQL> alter session set "_oracle_script"=FALSE;
Session altered.

SQL> create pluggable database cdb1_pdb4 admin user user1 identified by oracle;
Pluggable database created.

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

SQL> show pdbs

    CON_ID CON_NAME        OPEN MODE   RESTRICTED
---------- ------------------------------ ----------------     ----------
2 PDB$SEED        READ ONLY   NO
3 CDB1_PDB3        MOUNTED
4 CDB1_PDB4        READ WRITE  NO


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

Conclusion: pdb$seed is template database for new pluggable databases in container database (cdb).

Seed Database (pdb$seed) - Read Write OR Read Only Mode in Oracle Database 12c (12.1.0.2.0)

1. Check the instance and check the pluggable databases. 

[oracle@Server ~]$ ps -ef | grep pmon
oracle    3268     1  0 07:40 ?        00:00:00 ora_pmon_cdb1
oracle    4235  4205  0 08:00 pts/1    00:00:00 grep pmon

[oracle@Server ~]$ . oraenv
ORACLE_SID = [oracle] ? cdb1
The Oracle base has been set to /u01/app/oracle

[oracle@Server ~]$ sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Tue Feb 10 08:00:34 2015
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

SQL> connect sys/oracle@cdb1 as sysdba
Connected.

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> show con_id

CON_ID
------------------------------
1

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

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

    CON_ID NAME                       OPEN_MODE
---------- ------------------------------ -----------------
         2 PDB$SEED                       READ ONLY
         3 PDB_AUDIT                     MOUNTED

2. Perform close instance operation in seed database (pdb$seed)

SQL> alter pluggable database pdb$seed close;
alter pluggable database pdb$seed close
                         *
ERROR at line 1:
ORA-65017: seed pluggable database may not be dropped or altered

3. Set the parameter "_oracle_script" and close the seed database (pdb$seed)

SQL> alter session set "_oracle_script"=TRUE;
Session altered.

SQL> alter pluggable database pdb$seed close immediate instances=all;
Pluggable database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> show con_id

CON_ID
------------------------------
1

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

    CON_ID NAME                       OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       MOUNTED
         3 PDB_AUDIT                     MOUNTED

4. Open the seed database in Read-Write Mode

SQL> alter pluggable database pdb$seed open read write;
Pluggable database altered.

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

    CON_ID NAME                        OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ WRITE
         3 PDB_AUDIT                     MOUNTED

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> show con_id

CON_ID
------------------------------
1

SQL> alter session set container=PDB$SEED;
Session altered.

SQL> show con_name

CON_NAME
------------------------------
PDB$SEED

SQL> show con_id

CON_ID
------------------------------
2

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
TEMP

SQL> create tablespace example;
Tablespace created.

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
TEMP
EXAMPLE

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------           -----------------  ------------------
         2 PDB$SEED                                 READ WRITE NO

SQL> connect sys/oracle@cdb1 as sysdba
Connected.

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> show con_id

CON_ID
------------------------------
1

SQL> create pluggable database cdb1_pdb1 admin user user1 identified by oracle;
Pluggable database created.

SQL> show pdbs;

    CON_ID CON_NAME             OPEN MODE  RESTRICTED
---------- ------------------------------ -----------------  -------------------
         2 PDB$SEED                       READ WRITE NO
         3 PDB_AUDIT                     MOUNTED
         4 CDB1_PDB1                     MOUNTED

SQL> create pluggable database cdb1_pdb2 admin user user1 identified by oracle;
Pluggable database created.

SQL> show pdbs;

    CON_ID CON_NAME             OPEN MODE   RESTRICTED
---------- ------------------------------ ------------------- ------------------
         2 PDB$SEED                       READ WRITE  NO
         3 PDB_AUDIT                     MOUNTED
         4 CDB1_PDB1                     MOUNTED
         5 CDB1_PDB2                     MOUNTED

SQL> alter pluggable database pdb$seed close immediate instances=all;
alter pluggable database pdb$seed close immediate instances=all
                         *
ERROR at line 1:
ORA-65017: seed pluggable database may not be dropped or altered

SQL> alter pluggable database pdb$seed open read only;
alter pluggable database pdb$seed open read only
                         *
ERROR at line 1:
ORA-65017: seed pluggable database may not be dropped or altered

SQL> alter session set container=PDB$SEED;
Session altered.

SQL> alter pluggable database pdb$seed close immediate instances=all;
alter pluggable database pdb$seed close immediate instances=all
*
ERROR at line 1:
ORA-65017: seed pluggable database may not be dropped or altered

SQL> alter pluggable database pdb$seed open read only;
alter pluggable database pdb$seed open read only
*
ERROR at line 1:
ORA-65017: seed pluggable database may not be dropped or altered

SQL> alter session set "_oracle_script"=TRUE;
Session altered.

SQL> alter pluggable database pdb$seed close immediate instances=all;
Pluggable database altered.

SQL> show con_name

CON_NAME
------------------------------
PDB$SEED

SQL> show con_id

CON_ID
------------------------------
2

SQL> connect sys/oracle@cdb1 as sysdba
Connected.

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> sho con_id

CON_ID
------------------------------
1

SQL> show pdbs;

    CON_ID CON_NAME             OPEN MODE  RESTRICTED
---------- ------------------------------ -----------------  -------------------
         2 PDB$SEED                       MOUNTED
         3 PDB_AUDIT                     MOUNTED
         4 CDB1_PDB1                     MOUNTED
         5 CDB1_PDB2                     MOUNTED

SQL> create pluggable database cdb1_pdb3 admin user user1 identified by oracle;
create pluggable database cdb1_pdb3 admin user user1 identified by oracle
*
ERROR at line 1:
ORA-65036: pluggable database PDB$SEED not open in required mode

SQL> alter pluggable database pdb$seed open read only;
alter pluggable database pdb$seed open read only
                         *
ERROR at line 1:
ORA-65017: seed pluggable database may not be dropped or altered

SQL> alter session set container=PDB$SEED;
Session altered.

SQL> show con_name

CON_NAME
------------------------------
PDB$SEED

SQL> show con_id

CON_ID
------------------------------
2

SQL> alter pluggable database pdb$seed open read only;
alter pluggable database pdb$seed open read only
*
ERROR at line 1:
ORA-65017: seed pluggable database may not be dropped or altered

SQL> alter session set "_oracle_script"=TRUE;
Session altered.

SQL> alter pluggable database pdb$seed open read only;
Pluggable database altered.

SQL> connect sys/oracle@cdb1 as sysdba
Connected.

SQL> show pdbs;

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------  ------------------ ----------
         2 PDB$SEED                        READ ONLY  NO
         3 PDB_AUDIT                      MOUNTED
         4 CDB1_PDB1                      MOUNTED
         5 CDB1_PDB2                      MOUNTED

SQL> create pluggable database cdb1_pdb3 admin user user1 identified by oracle;
Pluggable database created.

SQL> show pdbs;

    CON_ID CON_NAME              OPEN MODE  RESTRICTED
---------- ------------------------------ ------------------  ----------
         2 PDB$SEED                        READ ONLY   NO
         3 PDB_AUDIT                      MOUNTED
         4 CDB1_PDB1                      MOUNTED
         5 CDB1_PDB2                      MOUNTED
         6 CDB1_PDB3                      MOUNTED

Oracle Articles published in TOAD World (Connected Intelligence)

Article-1: Oracle Exadata Database Machine - DCLI Introduction and Setup

http://www.toadworld.com/platforms/oracle/w/wiki/11009.oracle-exadata-database-machine-dcli-introduction-and-setup.aspx

Article-2: Rolling RECO data disk group resize activity for Oracle Exadata Database Machine

http://www.toadworld.com/platforms/oracle/w/wiki/11021.rolling-reco-data-disk-group-resize-activity-for-oracle-exadata-database-machine.aspx

Article-3: Container Database (CDB) with Pluggable Databases (PDBs) in Oracle 12c RAC

http://www.toadworld.com/platforms/oracle/w/wiki/11059.container-database-cdb-with-pluggable-databases-pdbs-in-oracle-12c-rac.aspx

Article-4: Recovering Table in Non-Container Database and Pluggable Database (PDB) in Container Database (CDB)

http://www.toadworld.com/platforms/oracle/w/wiki/11065.recovering-table-in-non-container-database-and-pluggable-database-pdb-in-container-database-cdb.aspx

Artile-5: Oracle Database In-Memory Option

http://www.toadworld.com/platforms/oracle/w/wiki/11083.oracle-database-in-memory-option.aspx

Debug check for Oracle Database 12c In-Memory Option

The Oracle Database In-Memory option is a new memory-optimized database technology that implements an in-memory columnar data format to deliver extremely fast SQL processing.

The Oracle Database In-Memory option requires an In-Memory column store, which is a new component of the SGA called the In-Memory Area.

You can allocate as little or as much memory as you wish to the In-Memory Area. The larger the In-Memory Area, the greater the number of objects that can be populated into the In-Memory column store.

To check you’re in-memory results against disk queries parameter is

_inmemory_query_check

This can be enabled in two ways

Values are

0 - default no check
2 - basic lightweight
4 - heavyweight (Oracle Regression testing uses 4).