Tuesday, October 8, 2013

Oracle Database 12c Installation and Configurations

Oracle 12c Container & Pluggable Database Installation

All Oracle Database 12c installation options are very similar to the Oracle Database 11g installation other than the new concept of container & pluggable databases. To create the container database with pluggale databases in Oracle 12c following options can be used in the installation wizard or in DBCA.

The checkbox "Create As Container Database" has to be selected and the option "Create a Container Database with one or more PDBs" should be chosen with the number of pluggable databases and prefix of the pluggable database.


Important Queries for Administration


[oracle@prod admin]$ sqlplus '/ as sysdba'

SQL*Plus: Release 12.1.0.1.0 Production on Tue Oct 8 16:30:29 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, OLAP, Advanced Analytics and Real Application Testing options

SQL>

SQL> select con_id, banner from v$version;

    CON_ID
----------
BANNER
--------------------------------------------------------------------------------
         0
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

         0
PL/SQL Release 12.1.0.1.0 - Production

         0
CORE    12.1.0.1.0      Production

    CON_ID
----------
BANNER
--------------------------------------------------------------------------------
         0
TNS for Linux: Version 12.1.0.1.0 - Production

         0
NLSRTL Version 12.1.0.1.0 - Production


SQL> select sys_context('userenv','con_name') from dual;

SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
CDB$ROOT

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

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PDB1                           READ WRITE
         4 PDB2                           READ WRITE


SQL> alter session set container = PDB1;

Session altered.

SQL> select sys_context('userenv','con_name') from dual;

SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
PDB1

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

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         3 PDB1                           READ WRITE

SQL> alter session set container = PDB2;

Session altered.

SQL> select sys_context('userenv','con_name') from dual;

SYS_CONTEXT('USERENV','CON_NAME')
--------------------------------------------------------------------------------
PDB2

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

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         4 PDB2                           READ WRITE

SQL> SELECT df.tablespace_name TABLESPACE, df.total_space TOTAL_SPACE,
  2  fs.free_space FREE_SPACE, df.total_space_mb TOTAL_SPACE_MB,
  3  (df.total_space_mb - fs.free_space_mb) USED_SPACE_MB, fs.free_space_mb FREE_SPACE_MB,
  4  ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
  5  FROM (SELECT tablespace_name, SUM (bytes) TOTAL_SPACE,
  6  ROUND( SUM (bytes) / 1048576) TOTAL_SPACE_MB
  7  FROM dba_data_files GROUP BY tablespace_name) df,
  8  (SELECT tablespace_name, SUM (bytes) FREE_SPACE, ROUND( SUM (bytes) / 1048576) FREE_SPACE_MB
  9  FROM dba_free_space GROUP BY tablespace_name) fs
 10  WHERE df.tablespace_name = fs.tablespace_name(+) ORDER BY ROUND(100 * (fs.free_space / df.total_space),2);

TABLESPACE                     TOTAL_SPACE FREE_SPACE TOTAL_SPACE_MB
------------------------------ ----------- ---------- --------------
USED_SPACE_MB FREE_SPACE_MB   PCT_FREE
------------- ------------- ----------
SYSTEM                           272629760   10354688            260
          250            10        3.8

SYSAUX                           639631360   32309248            610
          579            31       5.05

USERS                              5242880    4194304              5
            1             4         80


RMAN Backup

[oracle@prod admin]$ rman

Recovery Manager: Release 12.1.0.1.0 - Production on Tue Oct 8 16:45:56 2013

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target /

connected to target database: TEST (DBID=2126431506)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name TEST 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/12.1.0/dbhome_1/dbs/snapcf_TEST.f'; # default

RMAN> backup database plus archivelog;

Starting backup at 08-OCT-13
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=359 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=1 STAMP=828285237
input archived log thread=1 sequence=5 RECID=2 STAMP=828285267
input archived log thread=1 sequence=6 RECID=3 STAMP=828285630
input archived log thread=1 sequence=7 RECID=4 STAMP=828287384
input archived log thread=1 sequence=8 RECID=5 STAMP=828290783
channel ORA_DISK_1: starting piece 1 at 08-OCT-13
channel ORA_DISK_1: finished piece 1 at 08-OCT-13
piece handle=/u01/app/oracle/fast_recovery_area/TEST/backupset/2013_10_08/o1_mf_annnn_TAG20131008T164623_957td82r_.bkp tag=TAG20131008T164623 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-OCT-13

Starting backup at 08-OCT-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/TEST/system01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/TEST/sysaux01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/TEST/undotbs01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/TEST/users01.dbf
channel ORA_DISK_1: starting piece 1 at 08-OCT-13
channel ORA_DISK_1: finished piece 1 at 08-OCT-13
piece handle=/u01/app/oracle/fast_recovery_area/TEST/backupset/2013_10_08/o1_mf_nnndf_TAG20131008T164625_957td9jb_.bkp tag=TAG20131008T164625 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/u01/app/oracle/oradata/TEST/PDB1/sysaux01.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/TEST/PDB1/system01.dbf
input datafile file number=00010 name=/u01/app/oracle/oradata/TEST/PDB1/PDB1_users01.dbf
channel ORA_DISK_1: starting piece 1 at 08-OCT-13
channel ORA_DISK_1: finished piece 1 at 08-OCT-13
piece handle=/u01/app/oracle/fast_recovery_area/TEST/E8389F6267BA2A3EE0431C6FA8C0FFB3/backupset/2013_10_08/o1_mf_nnndf_TAG20131008T164625_957tdrrc_.bkp tag=TAG20131008T164625 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00012 name=/u01/app/oracle/oradata/TEST/PDB2/sysaux01.dbf
input datafile file number=00011 name=/u01/app/oracle/oradata/TEST/PDB2/system01.dbf
input datafile file number=00013 name=/u01/app/oracle/oradata/TEST/PDB2/PDB2_users01.dbf
channel ORA_DISK_1: starting piece 1 at 08-OCT-13
channel ORA_DISK_1: finished piece 1 at 08-OCT-13
piece handle=/u01/app/oracle/fast_recovery_area/TEST/E838A04FA7AD2A98E0431C6FA8C01C02/backupset/2013_10_08/o1_mf_nnndf_TAG20131008T164625_957tf804_.bkp tag=TAG20131008T164625 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
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/TEST/pdbseed/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/TEST/pdbseed/system01.dbf
channel ORA_DISK_1: starting piece 1 at 08-OCT-13
channel ORA_DISK_1: finished piece 1 at 08-OCT-13
piece handle=/u01/app/oracle/fast_recovery_area/TEST/E83894A1E2CD234FE0431C6FA8C0EE1E/backupset/2013_10_08/o1_mf_nnndf_TAG20131008T164625_957tfq7k_.bkp tag=TAG20131008T164625 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 08-OCT-13

Starting backup at 08-OCT-13
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=9 RECID=6 STAMP=828290846
channel ORA_DISK_1: starting piece 1 at 08-OCT-13
channel ORA_DISK_1: finished piece 1 at 08-OCT-13
piece handle=/u01/app/oracle/fast_recovery_area/TEST/backupset/2013_10_08/o1_mf_annnn_TAG20131008T164726_957tg6hz_.bkp tag=TAG20131008T164726 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-OCT-13

Starting Control File and SPFILE Autobackup at 08-OCT-13
piece handle=/u01/app/oracle/fast_recovery_area/TEST/autobackup/2013_10_08/o1_mf_s_828290847_957tg7qs_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 08-OCT-13

RMAN>


Pluggable Database Cloning

SQL> conn / as sysdba
Connected.

SQL> alter pluggable database PDB2 close;

Pluggable database altered.

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

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PDB1                           READ WRITE
         4 PDB2                           MOUNTED

SQL> alter pluggable database PDB2 open read only;

Pluggable database altered.

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

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PDB1                           READ WRITE
         4 PDB2                           READ ONLY

SQL> create pluggable database PDB3 from PDB2 file_name_convert = ('PDB2','PDB3');

Pluggable database created.

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

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PDB1                           READ WRITE
         4 PDB2                           READ ONLY
         5 PDB3                           MOUNTED

SQL> alter pluggable database PDB3 open;

Pluggable database altered.

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

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PDB1                           READ WRITE
         4 PDB2                           READ ONLY
         5 PDB3                           READ WRITE

SQL> alter pluggable database PDB2 close;

Pluggable database altered.

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

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PDB1                           READ WRITE
         4 PDB2                           MOUNTED
         5 PDB3                           READ WRITE

SQL> alter pluggable database PDB2 open;

Pluggable database altered.

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

    CON_ID NAME                           OPEN_MODE
---------- ------------------------------ ----------
         2 PDB$SEED                       READ ONLY
         3 PDB1                           READ WRITE
         4 PDB2                           READ WRITE
         5 PDB3                           READ WRITE

SQL>