Thinking Out Loud

June 10, 2017

12c How to UN Expire Password for CDB Users

Filed under: 12c,CDB — mdinh @ 8:11 pm

Use dbms_metadata.get_ddl to extract user and replace create with alter.

oracle@arrow ~ $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sat Jun 10 15:31:13 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> select username, expiry_date, account_status from dba_users where username like '%MONITOR%';

USERNAME
--------------------------------------------------------------------------------
EXPIRY_DA ACCOUNT_STATUS
--------- --------------------------------
C##MONITOR
10-JUN-17 EXPIRED

SQL> show con_name

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

SQL> set long 1000000 longchunksize 32000 linesize 32000 pages 0 newpage none
SQL> set heading off tab off echo off define off sqlprefix off blockterminator off timing off verify off feedb off
SQL> set sqlblanklines on embedded on trimspool on  
SQL> select dbms_metadata.get_ddl('USER','C##MONITOR') from dual;

   CREATE USER "C##MONITOR" IDENTIFIED BY VALUES 'S:***'
      DEFAULT TABLESPACE "USERS"
      TEMPORARY TABLESPACE "TEMP"
      PASSWORD EXPIRE

SQL> ALTER USER "C##MONITOR" IDENTIFIED BY VALUES 'S:***'
SQL> select username, expiry_date, account_status from dba_users where username like '%MONITOR%';
C##MONITOR                                                                                       07-DEC-17 OPEN
SQL> 

July 30, 2016

What convention to use for PDB?

Filed under: 12c,CDB,oracle — mdinh @ 4:55 pm

I don’t know but best to have one.

With multi-tenant database, I would use OMF to keep it simple and to avoid having to remember about FILE_NAME_CONVERT.

When creating PDB, there may be specification for ADMIN USER as show below:

CREATE PLUGGABLE DATABASE april ADMIN USER april_admin IDENTIFIED BY oracle;

Here are some examples I have come across (Names and Blogs are not provided to protect the innocents):

create pluggable database PDB1 admin user pdb1_admin 
CREATE PLUGGABLE DATABASE TESTPDB ADMIN USER TESTADMIN
CREATE PLUGGABLE DATABASE devpdb2 ADMIN USER pdb2dba
CREATE PLUGGABLE DATABASE mypdb ADMIN USER pdbadmin
CREATE PLUGGABLE DATABASE pdb3 ADMIN USER pdb_admadmin user App_Admin

It’s no big deal, right? It’s always possible to dig for it.

Just playing around.

CDB$ROOT:(SYS@tmnt):PRIMARY> @/media/sf_working/sql/cdb_tbs.sql

 CON_ID NAME     TABLESPACE_NAME      FILE_NAME                                                                                  ONLINE_STATUS
------- -------- -------------------- ------------------------------------------------------------------------------------------ -------------
      1 CDB$ROOT SYSAUX               /oradata/TMNT_A/datafile/o1_mf_sysaux_crx20pot_.dbf                                        ONLINE
      1 CDB$ROOT UNDOTBS1             /oradata/TMNT_A/datafile/o1_mf_undotbs1_crx20ssy_.dbf                                      ONLINE
      1 CDB$ROOT USERS                /oradata/TMNT_A/datafile/o1_mf_users_crx20tq8_.dbf                                         ONLINE
      1 CDB$ROOT SYSTEM               /oradata/TMNT_A/datafile/o1_mf_system_crx20gsp_.dbf                                        SYSTEM
      2 PDB$SEED
      3 APRIL    SYSAUX               /oradata/TMNT_A/380455732D1B1EFFE0530100007F18AB/datafile/o1_mf_sysaux_crx522d1_.dbf       ONLINE
      3 APRIL    USERS                /oradata/TMNT_A/380455732D1B1EFFE0530100007F18AB/datafile/o1_mf_users_crx522d2_.dbf        ONLINE
      3 APRIL    SYSTEM               /oradata/TMNT_A/380455732D1B1EFFE0530100007F18AB/datafile/o1_mf_system_crx522cr_.dbf       SYSTEM

8 rows selected.

CDB$ROOT:(SYS@tmnt):PRIMARY> @/media/sf_working/sql/cdb_role_privs.sql

GRANTEE              GRANTED_ROLE
-------------------- --------------------
C##GGS_ADMIN         CDB_DBA
C##GGS_ADMIN         CDB_DBA
DBSNMP               CDB_DBA
DBSNMP               CDB_DBA
SYS                  CDB_DBA
SYS                  CDB_DBA
C##GGS_ADMIN         DBA
C##GGS_ADMIN         DBA
MDINH                DBA
SYS                  DBA
SYS                  DBA
SYSTEM               DBA
SYSTEM               DBA
APRIL_ADMIN          PDB_DBA
C##GGS_ADMIN         PDB_DBA
C##GGS_ADMIN         PDB_DBA
SYS                  PDB_DBA
SYS                  PDB_DBA
DBA                  XDBADMIN
DBA                  XDBADMIN
SYS                  XDBADMIN
SYS                  XDBADMIN

22 rows selected.

CDB$ROOT:(SYS@tmnt):PRIMARY> CREATE PLUGGABLE DATABASE donnie FROM april;

Pluggable database created.

CDB$ROOT:(SYS@tmnt):PRIMARY> @/media/sf_working/sql/cdb_tbs.sql

 CON_ID NAME     TABLESPACE_NAME      FILE_NAME                                                                                  ONLINE_STATUS
------- -------- -------------------- ------------------------------------------------------------------------------------------ -------------
      1 CDB$ROOT SYSAUX               /oradata/TMNT_A/datafile/o1_mf_sysaux_crx20pot_.dbf                                        ONLINE
      1 CDB$ROOT UNDOTBS1             /oradata/TMNT_A/datafile/o1_mf_undotbs1_crx20ssy_.dbf                                      ONLINE
      1 CDB$ROOT USERS                /oradata/TMNT_A/datafile/o1_mf_users_crx20tq8_.dbf                                         ONLINE
      1 CDB$ROOT SYSTEM               /oradata/TMNT_A/datafile/o1_mf_system_crx20gsp_.dbf                                        SYSTEM
      2 PDB$SEED
      3 APRIL    SYSAUX               /oradata/TMNT_A/380455732D1B1EFFE0530100007F18AB/datafile/o1_mf_sysaux_crx522d1_.dbf       ONLINE
      3 APRIL    USERS                /oradata/TMNT_A/380455732D1B1EFFE0530100007F18AB/datafile/o1_mf_users_crx522d2_.dbf        ONLINE
      3 APRIL    SYSTEM               /oradata/TMNT_A/380455732D1B1EFFE0530100007F18AB/datafile/o1_mf_system_crx522cr_.dbf       SYSTEM
      4 DONNIE

9 rows selected.

Where are the tablespaces and datafiles for DONNIE?

CDB$ROOT:(SYS@tmnt):PRIMARY> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 APRIL                          READ WRITE NO
         4 DONNIE                         MOUNTED
CDB$ROOT:(SYS@tmnt):PRIMARY> alter pluggable database donnie open;

Pluggable database altered.

CDB$ROOT:(SYS@tmnt):PRIMARY> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 APRIL                          READ WRITE NO
         4 DONNIE                         READ WRITE NO
CDB$ROOT:(SYS@tmnt):PRIMARY> @/media/sf_working/sql/cdb_tbs.sql

 CON_ID NAME     TABLESPACE_NAME      FILE_NAME                                                                                  ONLINE_STATUS
------- -------- -------------------- ------------------------------------------------------------------------------------------ -------------
      1 CDB$ROOT SYSAUX               /oradata/TMNT_A/datafile/o1_mf_sysaux_crx20pot_.dbf                                        ONLINE
      1 CDB$ROOT USERS                /oradata/TMNT_A/datafile/o1_mf_users_crx20tq8_.dbf                                         ONLINE
      1 CDB$ROOT UNDOTBS1             /oradata/TMNT_A/datafile/o1_mf_undotbs1_crx20ssy_.dbf                                      ONLINE
      1 CDB$ROOT SYSTEM               /oradata/TMNT_A/datafile/o1_mf_system_crx20gsp_.dbf                                        SYSTEM
      2 PDB$SEED
      3 APRIL    SYSAUX               /oradata/TMNT_A/380455732D1B1EFFE0530100007F18AB/datafile/o1_mf_sysaux_crx522d1_.dbf       ONLINE
      3 APRIL    USERS                /oradata/TMNT_A/380455732D1B1EFFE0530100007F18AB/datafile/o1_mf_users_crx522d2_.dbf        ONLINE
      3 APRIL    SYSTEM               /oradata/TMNT_A/380455732D1B1EFFE0530100007F18AB/datafile/o1_mf_system_crx522cr_.dbf       SYSTEM
      4 DONNIE   SYSAUX               /oradata/TMNT_A/38DDD14641845D0EE0530100007F0289/datafile/o1_mf_sysaux_csso885x_.dbf       ONLINE
      4 DONNIE   USERS                /oradata/TMNT_A/38DDD14641845D0EE0530100007F0289/datafile/o1_mf_users_csso885y_.dbf        ONLINE
      4 DONNIE   SYSTEM               /oradata/TMNT_A/38DDD14641845D0EE0530100007F0289/datafile/o1_mf_system_csso885k_.dbf       SYSTEM

11 rows selected.

PDB DONNIE does not have ADMIN USER.

CDB$ROOT:(SYS@tmnt):PRIMARY> @/media/sf_working/sql/cdb_role_privs.sql

GRANTEE              GRANTED_ROLE
-------------------- --------------------
C##GGS_ADMIN         CDB_DBA
C##GGS_ADMIN         CDB_DBA
C##GGS_ADMIN         CDB_DBA
DBSNMP               CDB_DBA
DBSNMP               CDB_DBA
DBSNMP               CDB_DBA
SYS                  CDB_DBA
SYS                  CDB_DBA
SYS                  CDB_DBA
C##GGS_ADMIN         DBA
C##GGS_ADMIN         DBA
C##GGS_ADMIN         DBA
MDINH                DBA
MDINH                DBA
SYS                  DBA
SYS                  DBA
SYS                  DBA
SYSTEM               DBA
SYSTEM               DBA
SYSTEM               DBA
APRIL_ADMIN          PDB_DBA
APRIL_ADMIN          PDB_DBA
C##GGS_ADMIN         PDB_DBA
C##GGS_ADMIN         PDB_DBA
C##GGS_ADMIN         PDB_DBA
SYS                  PDB_DBA
SYS                  PDB_DBA
SYS                  PDB_DBA
DBA                  XDBADMIN
DBA                  XDBADMIN
DBA                  XDBADMIN
SYS                  XDBADMIN
SYS                  XDBADMIN
SYS                  XDBADMIN

34 rows selected.

CDB$ROOT:(SYS@tmnt):PRIMARY>

UPDATED: Looks like APRIL_ADMIN is for 2 different PDB's. Now that's confusing.

CDB$ROOT:(SYS@tmnt):PRIMARY> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 APRIL                          READ WRITE NO
         4 DONNIE                         READ WRITE NO
CDB$ROOT:(SYS@tmnt):PRIMARY> @/media/sf_working/sql/cdb_role_privs.sql

GRANTEE              GRANTED_ROLE          CON_ID
-------------------- -------------------- -------
C##GGS_ADMIN         CDB_DBA                    1
C##GGS_ADMIN         CDB_DBA                    3
C##GGS_ADMIN         CDB_DBA                    4
DBSNMP               CDB_DBA                    1
DBSNMP               CDB_DBA                    3
DBSNMP               CDB_DBA                    4
SYS                  CDB_DBA                    1
SYS                  CDB_DBA                    3
SYS                  CDB_DBA                    4
C##GGS_ADMIN         DBA                        1
C##GGS_ADMIN         DBA                        3
C##GGS_ADMIN         DBA                        4
MDINH                DBA                        3
MDINH                DBA                        4
SYS                  DBA                        1
SYS                  DBA                        3
SYS                  DBA                        4
SYSTEM               DBA                        1
SYSTEM               DBA                        3
SYSTEM               DBA                        4
APRIL_ADMIN          PDB_DBA                    3
APRIL_ADMIN          PDB_DBA                    4
C##GGS_ADMIN         PDB_DBA                    1
C##GGS_ADMIN         PDB_DBA                    3
C##GGS_ADMIN         PDB_DBA                    4
SYS                  PDB_DBA                    1
SYS                  PDB_DBA                    3
SYS                  PDB_DBA                    4
DBA                  XDBADMIN                   1
DBA                  XDBADMIN                   3
DBA                  XDBADMIN                   4
SYS                  XDBADMIN                   1
SYS                  XDBADMIN                   3
SYS                  XDBADMIN                   4

34 rows selected.

CDB$ROOT:(SYS@tmnt):PRIMARY>

July 21, 2016

Different names for 12c CDB

Filed under: 12c,CDB,oracle — mdinh @ 2:15 pm

Truthfully, I am feeling a little lazy to write a nice blog.

Configuring listener.ora and tnsnames.ora, varying names for database, connecting to database.

Summary:

db_name                              string      tmnt
db_unique_name                       string      tmnt_a
instance_name                        string      tmnt01

ADR Home /u01/app/oracle/diag/rdbms/tmnt_a/tmnt

Services Summary...
Service "april" has 1 instance(s).
  Instance "tmnt01", status READY, has 1 handler(s) for this service...
Service "tmnt" has 1 instance(s).
  Instance "tmnt", status UNKNOWN, has 1 handler(s) for this service...
Service "tmnt_a" has 1 instance(s).
  Instance "tmnt01", status READY, has 1 handler(s) for this service...

Details:

[06:58]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$ cat listener.ora
LISTENER_TMNT =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1551))
      (ADDRESS=(PROTOCOL=tcp)(HOST=arrow.localdomain)(PORT=1551)(SEND_BUF_SIZE=32767)(RECV_BUF_SIZE=32767))
    )
  )

SID_LIST_LISTENER_TMNT =
  (SID_LIST =
    (SID_DESC =
      (SDU=32767)
      (GLOBAL_DBNAME = tmnt)
      (ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1)
      (SID_NAME = tmnt)
    )
  )

USE_SID_AS_SERVICE_LISTENER_TMNT = ON
INBOUND_CONNECT_TIMEOUT_LISTENER_TMNT = 120
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER_TMNT = OFF
DIAG_ADR_ENABLED_LISTENER_TMNT = OFF
LOGGING_LISTENER_TMNT = OFF
TRACE_LEVEL_LISTENER_TMNT = OFF
SAVE_CONFIG_ON_STOP_LISTENER_TMNT = TRUE

[06:58]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$ cat tnsnames.ora
APRIL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = arrow.localdomain)(PORT = 1551))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = APRIL)
    )
  )

TMNT_A =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = arrow.localdomain)(PORT = 1551))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TMNT_A)
    )
  )

TMNT =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = arrow.localdomain)(PORT = 1551))
    )
    (CONNECT_DATA =
      (SID = tmnt)
    )
  )

[06:58]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$ lsnrctl status listener_tmnt

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 21-JUL-2016 06:58:32

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1551)))
STATUS of the LISTENER
------------------------
Alias                     listener_tmnt
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                21-JUL-2016 06:23:03
Uptime                    0 days 0 hr. 35 min. 28 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1551)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow.localdomain)(PORT=1551)))
Services Summary...
Service "april" has 1 instance(s).
  Instance "tmnt01", status READY, has 1 handler(s) for this service...
Service "tmnt" has 1 instance(s).
  Instance "tmnt", status UNKNOWN, has 1 handler(s) for this service...
Service "tmnt_a" has 1 instance(s).
  Instance "tmnt01", status READY, has 1 handler(s) for this service...
The command completed successfully

[06:58]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$ ps -ef|grep pmon
oracle    2572     1  0 Jul20 ?        00:00:02 ora_pmon_tmnt
oracle    8998  3716  0 06:58 pts/0    00:00:00 grep pmon

[06:58]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$ sqlplus system/oracle@tmnt

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 21 06:58:58 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Thu Jul 21 2016 06:57:32 -07:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

CDB$ROOT:(SYSTEM@tmnt):PRIMARY> 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

[06:59]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$ sqlplus system/oracle@tmnt_a

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 21 06:59:06 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Thu Jul 21 2016 06:58:58 -07:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

CDB$ROOT:(SYSTEM@tmnt_a):PRIMARY> 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

[06:59]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$ sqlplus system/oracle@april

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 21 06:59:13 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Thu Jul 21 2016 06:59:06 -07:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

APRIL:(SYSTEM@april):PRIMARY> 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

[06:59]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$ sqlplus sys/oracle@tmnt_a as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 21 06:59:22 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

CDB$ROOT:(SYS@tmnt_a):PRIMARY> show con_name

CON_NAME
------------------------------
CDB$ROOT
CDB$ROOT:(SYS@tmnt_a):PRIMARY> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 APRIL                          READ WRITE NO
CDB$ROOT:(SYS@tmnt_a):PRIMARY> show parameter name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name               string
db_file_name_convert                 string
db_name                              string      tmnt
db_unique_name                       string      tmnt_a
global_names                         boolean     FALSE
instance_name                        string      tmnt01
lock_name_space                      string
log_file_name_convert                string
pdb_file_name_convert                string
processor_group_name                 string
service_names                        string      tmnt_a
CDB$ROOT:(SYS@tmnt_a):PRIMARY> @/home/oracle/diag_info.sql

   INST_ID NAME                           VALUE                                                                                CON_ID
---------- ------------------------------ -------------------------------------------------------------------------------- ----------
         1 Diag Enabled                   TRUE                                                                                      0
         1 ADR Base                       /u01/app/oracle                                                                           0
         1 ADR Home                       /u01/app/oracle/diag/rdbms/tmnt_a/tmnt                                                    0
         1 Diag Trace                     /u01/app/oracle/diag/rdbms/tmnt_a/tmnt/trace                                              0
         1 Diag Alert                     /u01/app/oracle/diag/rdbms/tmnt_a/tmnt/alert                                              0
         1 Diag Incident                  /u01/app/oracle/diag/rdbms/tmnt_a/tmnt/incident                                           0
         1 Diag Cdump                     /u01/app/oracle/diag/rdbms/tmnt_a/tmnt/cdump                                              0
         1 Health Monitor                 /u01/app/oracle/diag/rdbms/tmnt_a/tmnt/hm                                                 0
         1 Default Trace File             /u01/app/oracle/diag/rdbms/tmnt_a/tmnt/trace/tmnt_ora_9016.trc                            0
         1 Active Problem Count           0                                                                                         0
         1 Active Incident Count          0                                                                                         0

11 rows selected.

tmnt

CDB$ROOT:(SYS@tmnt_a):PRIMARY> 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
[07:00]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/network/admin
$

July 20, 2016

12.1.0.2 New Features

Filed under: 12c,CDB,oracle — mdinh @ 2:51 am

Just a quick note as I play with CDB/PDB.

No more triggers!

PDB State Management Across CDB

select con_name, instance_name, state from DBA_PDB_SAVED_STATES;
alter pluggable database PDB1 SAVE STATE;
alter pluggable database ALL SAVE STATE;
alter pluggable database ALL DISCARD STATE;
alter pluggable database ALL OPEN;

startup force mount exclusive restrict;
drop database;

Drop database still does not clean up ADR.

[19:32]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/sqlplus/admin
$ sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 19 19:32:38 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

CDB$ROOT:(SYS@cdb12c):PRIMARY> show con_name

CON_NAME
------------------------------
CDB$ROOT
CDB$ROOT:(SYS@cdb12c):PRIMARY> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           MOUNTED
CDB$ROOT:(SYS@cdb12c):PRIMARY> select con_name, instance_name, state from DBA_PDB_SAVED_STATES;

no rows selected

CDB$ROOT:(SYS@cdb12c):PRIMARY> alter pluggable database all open;

Pluggable database altered.

CDB$ROOT:(SYS@cdb12c):PRIMARY> 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
CDB$ROOT:(SYS@cdb12c):PRIMARY> alter pluggable database all SAVE state;

Pluggable database altered.

CDB$ROOT:(SYS@cdb12c):PRIMARY> select con_name, instance_name, state from DBA_PDB_SAVED_STATES;

CON_NAME INSTANCE_NAME STATE
-------- ------------- --------------
PDB2     cdb12c        OPEN
PDB1     cdb12c        OPEN

CDB$ROOT:(SYS@cdb12c):PRIMARY> startup force;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             713031784 bytes
Database Buffers          352321536 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.
CDB$ROOT:(SYS@cdb12c):PRIMARY> 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
CDB$ROOT:(SYS@cdb12c):PRIMARY> alter pluggable database all DISCARD state;

Pluggable database altered.

CDB$ROOT:(SYS@cdb12c):PRIMARY> select con_name, instance_name, state from DBA_PDB_SAVED_STATES;

no rows selected

CDB$ROOT:(SYS@cdb12c):PRIMARY> startup force;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             713031784 bytes
Database Buffers          352321536 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.
CDB$ROOT:(SYS@cdb12c):PRIMARY> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           MOUNTED
         4 PDB2                           MOUNTED
CDB$ROOT:(SYS@cdb12c):PRIMARY> startup force mount exclusive restrict;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             713031784 bytes
Database Buffers          352321536 bytes
Redo Buffers                5455872 bytes
Database mounted.
CDB$ROOT:(SYS@cdb12c):PRIMARY> !ls /u01/app/oracle/product/12.1.0.2/db_1/dbs
cr_cdb.sql  hc_cdb12c.dat  hc_tmnt.dat  inittmnt.ora  lkCDB12C  lkTMNT_A  orapwcdb12c  orapwtmnt  spfilecdb12c.ora  spfiletmnt.ora

CDB$ROOT:(SYS@cdb12c):PRIMARY> drop database;

Database dropped.

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
CDB$ROOT:(SYS@cdb12c):PRIMARY>
CDB$ROOT:(SYS@cdb12c):PRIMARY> exit
[19:39]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/sqlplus/admin
$ ps -ef|grep pmon
oracle    7151     1  0 13:04 ?        00:00:01 ora_pmon_tmnt
oracle   16898  5756  0 19:39 pts/1    00:00:00 grep pmon
[19:39]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/sqlplus/admin
$ ls /u01/app/oracle/product/12.1.0.2/db_1/dbs
cr_cdb.sql  hc_cdb12c.dat  hc_tmnt.dat  inittmnt.ora  lkCDB12C  lkTMNT_A  orapwcdb12c  orapwtmnt  spfiletmnt.ora
[19:39]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/sqlplus/admin
$ ls /u01/app/oracle/diag/rdbms/
cdb12c  tmnt_a
[19:40]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/sqlplus/admin
$

July 19, 2016

Create 12c CDB using dbca or sqlplus ???

Filed under: 12c,CDB,oracle — mdinh @ 10:27 pm

How do you typically create database, using dbca or sqlplus?

I am still surprised with all the bloatware added for creating database using dbca.

Also, is there a reason to prefix PDB with pdb?

[14:54]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/dbs
$ ll
total 28
-rw-r--r--. 1 oracle oinstall  801 Jul 19 12:59 cr_cdb.sql
-rw-rw----. 1 oracle oinstall 1544 Jul 19 13:06 hc_tmnt.dat
-rw-r--r--. 1 oracle oinstall  216 Jul 19 12:48 inittmnt.ora
-rw-r-----. 1 oracle oinstall   24 Jul 19 13:05 lkTMNT_A
-rw-r-----. 1 oracle oinstall 5120 Jul 19 11:13 orapwtmnt
-rw-r-----. 1 oracle oinstall 2560 Jul 19 13:36 spfiletmnt.ora

[14:54]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/dbs
$ cat /etc/oratab
grid:/u01/app/12.1.0.2/grid
tmnt:/u01/app/oracle/product/12.1.0.2/db_1

[14:54]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/dbs
$ dbca -silent \
> -createDatabase -templateName General_Purpose.dbc -createAsContainerDatabase true \
> -gdbName cdb12c -sid cdb12c \
> -SysPassword oracle -SystemPassword oracle \
> -numberOfPdbs 2 -pdbName pdb \
> -pdbadminUsername pdba -pdbadminPassword oracle \
> -emConfiguration NONE \
> -redoLogFileSize 100 \
> -storageType FS -datafileDestination /oradata \
> -characterSet AL32UTF8 -nationalCharacterSet AL16UTF16 \
> -totalMemory 1024 -databaseType MULTIPURPOSE \
> -initparams audit_trail=NONE

Registering database with Oracle Restart
4% complete
Copying database files
5% complete
6% complete
12% complete
17% complete
22% complete
30% complete
Creating and starting Oracle instance
32% complete
35% complete
36% complete
37% complete
41% complete
44% complete
45% complete
48% complete
Completing Database Creation
50% complete
53% complete
55% complete
63% complete
71% complete
74% complete
Creating Pluggable Databases
79% complete
84% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/cdb12c/cdb12c.log" for further details.

[15:11]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/dbs
$ ll
total 48
-rw-r--r--. 1 oracle oinstall  801 Jul 19 12:59 cr_cdb.sql
-rw-rw----. 1 oracle oinstall 1544 Jul 19 15:10 hc_cdb12c.dat
-rw-rw----. 1 oracle oinstall 1544 Jul 19 13:06 hc_tmnt.dat
-rw-r--r--. 1 oracle oinstall  216 Jul 19 12:48 inittmnt.ora
-rw-r-----. 1 oracle oinstall   24 Jul 19 14:56 lkCDB12C
-rw-r-----. 1 oracle oinstall   24 Jul 19 13:05 lkTMNT_A
-rw-r-----. 1 oracle oinstall 7680 Jul 19 15:07 orapwcdb12c
-rw-r-----. 1 oracle oinstall 5120 Jul 19 11:13 orapwtmnt
-rw-r-----. 1 oracle oinstall 3584 Jul 19 15:11 spfilecdb12c.ora
-rw-r-----. 1 oracle oinstall 2560 Jul 19 13:36 spfiletmnt.ora

[15:12]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/dbs
$ cat /etc/oratab
#Backup file is  /u01/app/12.1.0.2/grid/srvm/admin/oratab.bak.arrow line added by Agent
grid:/u01/app/12.1.0.2/grid
tmnt:/u01/app/oracle/product/12.1.0.2/db_1
cdb12c:/u01/app/oracle/product/12.1.0.2/db_1:N          # line added by Agent

[15:12]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/dbs
$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 19 15:12:33 2016
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
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
SQL> @/media/sf_working/sql/dba_registry.sql

NAME      COMP_ID      COMP_NAME                                VERSION                        STATUS
--------- ------------ ---------------------------------------- ------------------------------ --------------------------------------------
CDB12C    DV           Oracle Database Vault                    12.1.0.2.0                     VALID
CDB12C    APEX         Oracle Application Express               4.2.5.00.08                    VALID
CDB12C    OLS          Oracle Label Security                    12.1.0.2.0                     VALID
CDB12C    SDO          Spatial                                  12.1.0.2.0                     VALID
CDB12C    ORDIM        Oracle Multimedia                        12.1.0.2.0                     VALID
CDB12C    CONTEXT      Oracle Text                              12.1.0.2.0                     VALID
CDB12C    OWM          Oracle Workspace Manager                 12.1.0.2.0                     VALID
CDB12C    XDB          Oracle XML Database                      12.1.0.2.0                     VALID
CDB12C    CATALOG      Oracle Database Catalog Views            12.1.0.2.0                     VALID
CDB12C    CATPROC      Oracle Database Packages and Types       12.1.0.2.0                     VALID
CDB12C    JAVAVM       JServer JAVA Virtual Machine             12.1.0.2.0                     VALID
CDB12C    XML          Oracle XDK                               12.1.0.2.0                     VALID
CDB12C    CATJAVA      Oracle Database Java Packages            12.1.0.2.0                     VALID
CDB12C    APS          OLAP Analytic Workspace                  12.1.0.2.0                     VALID
CDB12C    XOQ          Oracle OLAP API                          12.1.0.2.0                     VALID
CDB12C    RAC          Oracle Real Application Clusters         12.1.0.2.0                     OPTION OFF

16 rows selected.

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

Manually created CDB.

[15:14]oracle@arrow:cdb12c:/u01/app/oracle/product/12.1.0.2/db_1/dbs
$ . oraenv <<< tmnt ORACLE_SID = [cdb12c] ? The Oracle base remains unchanged with value /u01/app/oracle 

[15:14]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/dbs 
$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 19 15:14:10 2016 
Copyright (c) 1982, 2014, Oracle. All rights reserved. 
Connected to: 
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> show con_name

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

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 APRIL                          MOUNTED
SQL> @/media/sf_working/sql/dba_registry.sql

NAME      COMP_ID      COMP_NAME                                VERSION                        STATUS
--------- ------------ ---------------------------------------- ------------------------------ --------------------------------------------
TMNT      XDB          Oracle XML Database                      12.1.0.2.0                     VALID
TMNT      CATALOG      Oracle Database Catalog Views            12.1.0.2.0                     VALID
TMNT      CATPROC      Oracle Database Packages and Types       12.1.0.2.0                     VALID

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
[15:14]oracle@arrow:tmnt:/u01/app/oracle/product/12.1.0.2/db_1/dbs
$

August 2, 2013

12c Cloning PDB – Oracle Multitenant (PDB)

Filed under: 12c,CDB,GoldenGate — mdinh @ 5:19 am

It’s so easy that even a caveman can do it.

Where do you think I am going with this?

What PDB was just cloned and what users were there?

oracle@san:cdb01:/u01/app/ggs01
> sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 1 21:56:35 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> create pluggable database pdb02 from pdb01;

create pluggable database pdb02 from pdb01
*
ERROR at line 1:
ORA-65081: database or pluggable database is not open in read only mode

SQL> show pdbs

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

SQL> alter pluggable database pdb01 close;

Pluggable database altered.

SQL> alter pluggable database pdb01 open read only;

Pluggable database altered.

SQL> create pluggable database pdb02 from pdb01;

Pluggable database created.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB01                          READ ONLY  NO
         4 PDB02                          MOUNTED

SQL> alter pluggable database pdb02 open;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB01                          READ ONLY  NO
         4 PDB02                          READ WRITE NO

SQL> alter session set container=pdb02;

Session altered.

SQL> select username from dba_users where username like ‘GG%’;

USERNAME
--------------------------------------------------------------------------------
GGR
GGE
GGS

SQL>

August 1, 2013

12c Startup and Shutdown – Oracle Multitenant (PDB)

Filed under: 12c,CDB — mdinh @ 1:22 pm

Eventually, all databases will be container based. The first PDB in container is FREE, additional PDB is license option.

Startup / Shutdown Summary:

Starting CDB (container database) will NOT OPEN PDB (pluggable database)

Use SHOW CON_NAME to know where you are at, i.e. CDB or PDB

Use ALL to have OPEN/CLOSE all PDB, i.e. ALTER PLUGGABLE DATABASE ALL OPEN;

Use ALTER SESSION to switch to PDB, i.e. alter session set container=PDB01;

Demo:

> sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Aug 1 05:41:27 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

SQL> SHOW CON_NAME

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

SQL> SHOW PDBS

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

SQL> shutdown immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> SHOW CON_NAME

ERROR:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

SP2-1545: This feature requires Database availability.

SQL> startup;

ORACLE instance started.

Total System Global Area 3206836224 bytes
Fixed Size                  2293496 bytes
Variable Size             738197768 bytes
Database Buffers         2449473536 bytes
Redo Buffers               16871424 bytes
Database mounted.
Database opened.

SQL> SHOW CON_NAME

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

SQL> SHOW PDBS

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB01                          MOUNTED

SQL> ALTER PLUGGABLE DATABASE PDB01 OPEN;

Pluggable database altered.
SQL> SHOW PDBS

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

SQL> ALTER PLUGGABLE DATABASE ALL CLOSE;

		 
Pluggable database altered.

SQL> SHOW PDBS

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

SQL> ALTER PLUGGABLE DATABASE ALL OPEN;

Pluggable database altered.

SQL> alter session set container=PDB01;

Session altered.

SQL> SHOW CON_NAME

CON_NAME
------------------------------
PDB01
SQL>

References:

ALTER PLUGGABLE DATABASE

Oracle Multitenant Overview

Oracle Multitenant Learn More

Blog at WordPress.com.