Learn oracle 12c database management

Subscribe to Learn oracle 12c database management  feed
I will be concentrating mostly on 12c database on this site. you can visit my other blog for knowledge on previous versions of oracle database. Arvind Toorpunoreply@blogger.comBlogger33125
Updated: 17 hours 56 min ago

Create Common Users in 12c container database

Mon, 2016-03-14 10:51
********************************** Create Common Users**********************************
When creating a common user the following requirements must all be met.

You must be connected to a common user with the CREATE USER privilege.
The current container must be the root container.
The username for the common user must be prefixed with "C##" or "c##" and contain only ASCII or EBCDIC characters.
The username must be unique across all containers.
The DEFAULT TABLESPACE, TEMPORARY TABLESPACE, QUOTA and PROFILE must all reference objects that exist in all containers.
You can either specify the CONTAINER=ALL clause, or omit it, as this is the default setting when the current container is the root.
The following example shows how to create common users with and without the CONTAINER clause from the root container.

oracle@Linux03 echo $ORACLE_SID
ORCL

CONN / AS SYSDBA

SQL> show con_name
con_name CDB$ROOT

Lets query some local users by filtering con_id >2. ususally CON_ID greater that 2 is user created PDB's.

SQL> set echo on
SQL> show con_name
con_name CDB$ROOT
SQL> column USERNAME format a40
SQL> column CON_ID format 99
SQL> select username,CON_ID,common from CDB_USERS where CON_ID >2 and common='NO';

USERNAME                                 CON_ID COM
---------------------------------------- ------ ---
PDBORCL1_USR2                                 5 NO
PDBORCL1                                      5 NO
PDBORCL1_USR1                                 5 NO
PDBORCL1_USR2                                 4 NO
PDBORCL2                                      4 NO
IX                                            3 NO
SH                                            3 NO
PDBADMIN                                      3 NO
BI                                            3 NO
OE                                            3 NO
SCOTT                                         3 NO
HR                                            3 NO
PM                                            3 NO

13 rows selected

******************************************************
Now I want to create user C##CDBADMIN1
******************************************************

SQL> select username,CON_ID,common from CDB_USERS where username like '%CDB%';

no rows selected


-- Create the common user using the CONTAINER clause.
CREATE USER C##CDBADMIN1 IDENTIFIED BY oracle CONTAINER=ALL;
GRANT CREATE SESSION TO C##CDBADMIN1 CONTAINER=ALL;

SQL> select username,CON_ID,common from CDB_USERS where username like '%CDB%';

USERNAME CON_ID COM
---------------------------------------- ------ ---
C##CDBADMIN1      3 YES
C##CDBADMIN1      5 YES
C##CDBADMIN1      1 YES
C##CDBADMIN1      4 YES

******************************************************
-- Create the common user using the default CONTAINER setting.
******************************************************

SQL> CREATE USER C##CDBADMIN1 IDENTIFIED BY oracle;

User created.

SQL> select username,CON_ID,common from CDB_USERS where username like '%CDB%';

USERNAME CON_ID COM
---------------------------------------- ------ ---
C##CDBADMIN1      1 YES
C##CDBADMIN1      5 YES
C##CDBADMIN1      3 YES
C##CDBADMIN1      4 YES

SQL> alter session set container=pdborcl1;

Session altered.

***************************************************************
We can grant permission only on current pdb to common user.
****************************************************************

SQL> grant create session TO C##CDBADMIN1 CONTAINER=CURRENT;

Grant succeeded.

**********************************
Lets test connecting to current pdb:
**********************************
SQL> conn C##CDBADMIN1/oracle@pdborcl1
Connected.
SQL> show user
USER is "C##CDBADMIN1"
SQL> show con_name

CON_NAME
------------------------------
PDBORCL1

*************************************
Lets test connecting to other pdb now:
**************************************

SQL> conn C##CDBADMIN1/oracle@pdborcl2
ERROR:
ORA-01045: user C##CDBADMIN1 lacks CREATE SESSION privilege; logon denied

Warning: You are no longer connected to ORACLE.


*****************************************************************
YOU can revoke the grants on CDBADMIN11 using REVOKE cmd:
*****************************************************************

SQL> revoke CREATE SESSION from C##CDBADMIN1 CONTAINER=ALL;

Revoke succeeded.

SQL> select username,CON_ID,common from CDB_USERS where username like '%CDB%';

USERNAME CON_ID COM
---------------------------------------- ------ ---
C##CDBADMIN1      3 YES
C##CDBADMIN1      1 YES
C##CDBADMIN1      5 YES
C##CDBADMIN1      4 YES

*******************************************************
You can drop COMMON USER as below connecting to sys:
*******************************************************

SQL> drop user C##CDBADMIN1 ;

User dropped.

SQL> select username,CON_ID,common from CDB_USERS where username like '%CDB%';

no rows selected



Categories: DBA Blogs

Enabling archive log mode -12c

Fri, 2016-03-04 11:05
Since the Redologs are created at container database level in 12c and not at pluggable database level. (Enabling archivelog at pluggable database level is not possible). Archiving is done at CDB's.

You can check archive log mode either by querying v$database or archivelog list 

SQL> select name,open_mode,log_mode from v$database;

NAME  OPEN_MODE       LOG_MODE
--------- -------------------- ------------
ORCL  READ WRITE       NOARCHIVELOG

(OR)

SQL> archive log list
Database log mode       Archive Mode
Automatic archival       Disabled
Archive destination       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     11
Next log sequence to archive   13
Current log sequence       13

*************** ***************
To enable the Archvielog mode
*************** ***************

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  788529152 bytes
Fixed Size    2929352 bytes
Variable Size  541068600 bytes
Database Buffers  239075328 bytes
Redo Buffers    5455872 bytes
Database mounted.

SQL> ALTER DATABASE ARCHIVELOG;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> select name,open_mode,log_mode from v$database;

NAME  OPEN_MODE       LOG_MODE
--------- -------------------- ------------
ORCL  READ WRITE       ARCHIVELOG


SQL> archive log list
Database log mode       Archive Mode
Automatic archival       Enabled
Archive destination       USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     11
Next log sequence to archive   13
Current log sequence       13
Categories: DBA Blogs

open all pluggable databases -12c

Fri, 2016-03-04 09:48
SQL> select name, open_mode from v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       MOUNTED
PDBORCL2       MOUNTED
PDBORCL1       READ ONLY
PDBORCL3       READ WRITE

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  788529152 bytes
Fixed Size    2929352 bytes
Variable Size  541068600 bytes
Database Buffers  239075328 bytes
Redo Buffers    5455872 bytes
Database mounted.


SQL> ALTER DATABASE OPEN;

Database altered.

SQL> select name, open_mode from v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       MOUNTED
PDBORCL2       MOUNTED
PDBORCL1       MOUNTED
PDBORCL3       MOUNTED


SQL> ALTER PLUGGABLE DATABASE ALL OPEN;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       READ WRITE
PDBORCL2       READ WRITE
PDBORCL1       READ WRITE
PDBORCL3       READ WRITE

Categories: DBA Blogs

Clone the pluggable database - 12c

Fri, 2016-03-04 09:40
********************* *********************
   Select a pluggable database to clone
********************* *********************

SQL> select name,open_mode from v$pdbs order by name;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       READ WRITE
PDBORCL1       READ ONLY
PDBORCL2       READ WRITE

SQL> alter pluggable database pdborcl1 close;

Pluggable database altered.

SQL> alter pluggable database pdborcl1 open read only;

Pluggable database altered.


********************* *********************
  Lets clone the pluggable database now
********************* *********************

SQL> create pluggable database pdborcl3 from pdborcl1 file_name_convert=('/u01/app/oracle/oradata/orcl/pdborcl1/'
,'/u01/app/oracle/oradata/orcl/pdborcl3/');  

Pluggable database created.

SQL> alter pluggable database pdborcl3 open;

Pluggable database altered.


SQL> select name,open_mode from v$pdbs order by name;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       READ WRITE
PDBORCL1       READ ONLY
PDBORCL2       READ WRITE
PDBORCL3       READ WRITE


SQL> alter pluggable database pdborcl1 close;

Pluggable database altered.

SQL> alter pluggable database pdborcl1 open;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs order by name;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       READ WRITE
PDBORCL1       READ WRITE
PDBORCL2       READ WRITE
PDBORCL3       READ WRITE


************************** **************************
Check this below link on how to drop a pluggable database :
************************** **************************

DROPPING-PLUGGABLE-DATABASE








Categories: DBA Blogs

Dropping a pluggable database -12c

Fri, 2016-03-04 09:38
********************* *********************
Dropping a pluggable database :
********************* *********************

SQL> select name, open_mode from v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       READ WRITE
PDBORCL2       READ WRITE
PDBORCL1       READ WRITE
PDBORCL3       READ WRITE



SQL> DROP PLUGGABLE DATABASE PDBORCL3 INCLUDING DATAFILES;
DROP PLUGGABLE DATABASE PDBORCL3 INCLUDING DATAFILES
*
ERROR at line 1:
ORA-65025: Pluggable database PDBORCL3 is not closed on all instances.

You need to close the database before dropping.

SQL> alter pluggable database PDBORCL3 close;

Pluggable database altered.

SQL> select name, open_mode from v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       READ WRITE
PDBORCL2       READ WRITE
PDBORCL1       READ WRITE
PDBORCL3       MOUNTED

SQL> DROP PLUGGABLE DATABASE PDBORCL3 INCLUDING DATAFILES;

Pluggable database dropped.

SQL> select name, open_mode from v$pdbs;

NAME       OPEN_MODE
------------------------------ ----------
PDB$SEED       READ ONLY
PDBORCL       READ WRITE
PDBORCL2       READ WRITE
PDBORCL1       READ WRITE


Lets make sure by looking at datafile location

[oracle@Linux03 pdborcl3]$ pwd
/u01/app/oracle/oradata/orcl/pdborcl3

[oracle@Linux03 pdborcl3]$ ls -ll
total 0


[oracle@Linux03 pdborcl3]$ 


Categories: DBA Blogs

Connecting to a pluggable database -12c

Thu, 2016-03-03 10:05
******* ********** *********
USING EZCONNECT:
******* ********** *********

[oracle@Linux03 ~]$ sqlplus pdborcl1/xxxxxx@localhost:1521/pdborcl1

SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 3 09:12:17 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
------------------------------
PDBORCL1

******** ********* *********
USING TNSNAMES:
******** ********* *********

Create a tnsnames entry for the pdb that you want to connect

[oracle@Linux03 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

pdborcl1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Linux03)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
 



[oracle@Linux03 ~]$ sqlplus pdborcl1/xxxxx@pdborcl1

SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 3 09:15: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
------------------------------
PDBORCL1

Categories: DBA Blogs

Pages