Oracle Database 12c New Features

RMAN Command

LIST
RMAN>list archivelog all;
RMAN>list copy of archivelog until time ‘SYSDATE-10’;
RMAN>list copy of archivelog from time ‘SYSDATE-10’
RMAN>list copy of archivelog from time ‘SYSDATE-10’ until time ‘SYSDATE-2’;
RMAN>list copy of archivelog from sequence 1000;
RMAN>list copy of archivelog until sequence 1500;
RMAN>list copy of archivelog from sequence 1000 until sequence 1500;

DELETE
RMAN>delete archivelog all;
RMAN>delete archivelog until time ‘SYSDATE-10’;
RMAN>delete archivelog from time ‘SYSDATE-10’
RMAN>delete archivelog from time ‘SYSDATE-10’ until time ‘SYSDATE-2’;
RMAN>delete archivelog from sequence 1000;
RMAN>delete archivelog until sequence 1500;
RMAN>delete archivelog from sequence 1000 until sequence 1500;
RMAN>delete noprompt archivelog until time ‘SYSDATE-10’;
RMAN>delete noprompt archivelog all completed before 'sysdate - 1/24'; #keep 1 hour
RMAN>delete noprompt archivelog all completed before 'sysdate - 1/48'; #keep 30 mins

PRVF-0002 : Could not retrieve local nodename

If you encounter following error

PRVF-0002 : Could not retrieve local nodename


make sure your ip and hostname set in the /etc/hosts

and try to run runInstaller again.


Install Oracle 12c on Linux 7

Install required packages

yum install xterm libXaw libXpm binutils glibc-kernheaders gcc gcc-c++ glibc glibc-common libstdc++ libstdc++-devel make sysstat compat-libstdc++-33 glibc-headers libaio libaio-devel libgcc glibc-devel expat zlib xorg-x11-utils xorg-x11-xauth wget unzip traceroute smartmontools libXxf86misc libXxf86dga libdmx ksh compat-libcap1 bind-utils bind-libs bc elfutils-libelf-devel sysfsutils lsscsi libXi libXtst

[root@nextcurve ~]# yum install xterm libXaw libXpm binutils glibc-kernheaders gcc gcc-c++ glibc glibc-common libstdc++ libstdc++-devel make sysstat compat-libstdc++-33 glibc-headers libaio libaio-devel libgcc glibc-devel expat zlib xorg-x11-utils xorg-x11-xauth wget unzip traceroute smartmontools libXxf86misc libXxf86dga libdmx ksh compat-libcap1 bind-utils bind-libs bc elfutils-libelf-devel sysfsutils lsscsi libXi libXtst
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: centos.ipserverone.com
 * extras: mirror.vodien.com
 * updates: centos.ipserverone.com
Package xterm-295-3.el7.x86_64 already installed and latest version
Package libXaw-1.0.12-5.el7.x86_64 already installed and latest version
Package libXpm-3.5.11-3.el7.x86_64 already installed and latest version
Package binutils-2.23.52.0.1-55.el7.x86_64 already installed and latest version
Package kernel-headers-3.10.0-327.18.2.el7.x86_64 already installed and latest version
Package gcc-4.8.5-4.el7.x86_64 already installed and latest version
Package gcc-c++-4.8.5-4.el7.x86_64 already installed and latest version
Package glibc-2.17-106.el7_2.6.x86_64 already installed and latest version
Package glibc-common-2.17-106.el7_2.6.x86_64 already installed and latest version
Package libstdc++-4.8.5-4.el7.x86_64 already installed and latest version
Package libstdc++-devel-4.8.5-4.el7.x86_64 already installed and latest version
Package 1:make-3.82-21.el7.x86_64 already installed and latest version
Package sysstat-10.1.5-7.el7.x86_64 already installed and latest version
Package compat-libstdc++-33-3.2.3-72.el7.x86_64 already installed and latest version
Package glibc-headers-2.17-106.el7_2.6.x86_64 already installed and latest version
Package libaio-0.3.109-13.el7.x86_64 already installed and latest version
Package libaio-devel-0.3.109-13.el7.x86_64 already installed and latest version
Package libgcc-4.8.5-4.el7.x86_64 already installed and latest version
Package glibc-devel-2.17-106.el7_2.6.x86_64 already installed and latest version
Package expat-2.1.0-8.el7.x86_64 already installed and latest version
Package zlib-1.2.7-15.el7.x86_64 already installed and latest version
Package xorg-x11-utils-7.5-13.1.el7.x86_64 already installed and latest version
Package 1:xorg-x11-xauth-1.0.9-1.el7.x86_64 already installed and latest version
Package wget-1.14-10.el7_0.1.x86_64 already installed and latest version
Package unzip-6.0-15.el7.x86_64 already installed and latest version
Package 3:traceroute-2.0.19-5.el7.x86_64 already installed and latest version
Package 1:smartmontools-6.2-4.el7.x86_64 already installed and latest version
Package libXxf86misc-1.0.3-7.1.el7.x86_64 already installed and latest version
Package libXxf86dga-1.1.4-2.1.el7.x86_64 already installed and latest version
Package libdmx-1.1.3-3.el7.x86_64 already installed and latest version
Package ksh-20120801-22.el7_1.3.x86_64 already installed and latest version
Package compat-libcap1-1.10-7.el7.x86_64 already installed and latest version
Package 32:bind-utils-9.9.4-29.el7_2.3.x86_64 already installed and latest version
Package 32:bind-libs-9.9.4-29.el7_2.3.x86_64 already installed and latest version
Package bc-1.06.95-13.el7.x86_64 already installed and latest version
Package elfutils-libelf-devel-0.163-3.el7.x86_64 already installed and latest version
Package sysfsutils-2.1.0-16.el7.x86_64 already installed and latest version
Package lsscsi-0.27-3.el7.x86_64 already installed and latest version
Package libXi-1.7.4-2.el7.x86_64 already installed and latest version
Package libXtst-1.2.2-2.1.el7.x86_64 already installed and latest version
Nothing to do

Create user and group

[root@nextcurve ~]# groupadd oinstall
[root@nextcurve ~]# groupadd dba
[root@nextcurve ~]# 
[root@nextcurve ~]# useradd -g oinstall -G dba oracle -d /home/oracle
[root@nextcurve ~]# passwd oracle
Changing password for user oracle.
New password: 
BAD PASSWORD: The password is shorter than 8 characters
Retype new password: 
passwd: all authentication tokens updated successfully.
[root@nextcurve ~]# 


Unzip Oracle software

[oracle@nextcurve oracle-12.1.0.2]$ pwd
/home/oracle/oracle-12.1.0.2
[oracle@nextcurve ~]$
[oracle@nextcurve ~]$
[oracle@nextcurve oracle-12.1.0.2]$ ls -ltr
total 2625932
-rw-r--r--. 1 oracle oinstall 1015358809 May  4 05:25 linuxamd64_12102_database_se2_2of2.zip
-rw-r--r--. 1 oracle oinstall 1673591558 May  4 05:25 linuxamd64_12102_database_se2_1of2.zip
[oracle@nextcurve oracle-12.1.0.2]$ unzip linuxamd64_12102_database_se2_1of2.zip 
Archive:  linuxamd64_12102_database_se2_1of2.zip
   creating: database/
   creating: database/rpm/
  inflating: database/rpm/cvuqdisk-1.0.9-1.rpm  

[oracle@nextcurve oracle-12.1.0.2]$ 
[oracle@nextcurve oracle-12.1.0.2]$ 
[oracle@nextcurve oracle-12.1.0.2]$ unzip linuxamd64_12102_database_se2_2of2.zip 
Archive:  linuxamd64_12102_database_se2_2of2.zip
   creating: database/stage/Components/oracle.ctx/
   creating: database/stage/Components/oracle.ctx/12.1.0.2.0/
   creating: database/stage/Components/oracle.ctx/12.1.0.2.0/1/
   creating: database/stage/Components/oracle.ctx/12.1.0.2.0/1/DataFiles/


Create oracle home

mkdir -p /opt/oracle/rdbms/12.1.0
mkdir -p /opt/oracle/oraInventory
chown -R oracle:oinstall /opt/oracle
chown -R oracle:oinstall /opt/oracle/oraInventory

[root@nextcurve oracle]# mkdir -p /opt/oracle/rdbms/12.1.0
[root@nextcurve oracle]# mkdir -p /opt/oracle/oraInventory
[root@nextcurve oracle]# chown -R oracle:oinstall /opt/oracle
[root@nextcurve oracle]# chown -R oracle:oinstall /opt/oracle/oraInventory
[root@nextcurve oracle]# 
[root@nextcurve oracle]# 

Modify system parameter to minimum

[root@nextcurve 12.1.0]# cat /etc/sysctl.conf 
# System default settings live in /usr/lib/sysctl.d/00-system.conf.
# To override those settings, enter new settings here, or in an /etc/sysctl.d/<name>.conf file
#
# For more information, see sysctl.conf(5) and sysctl.d(5).


fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 3975677952
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586



[root@nextcurve fixup]# cat /etc/security/limits.conf
#ftp             hard    nproc           0
#@student        -       maxlogins       4

# End of file


oracle soft nproc 2047 
oracle hard nproc 16384 
oracle soft nofile 1024 
oracle hard nofile 65536 


reboot system

[root@nextcurve 12.1.0]# reboot

runInstaller

[oracle@nextcurve oracle-12.1.0.2]$ cd database/
[oracle@nextcurve database]$ ls
install  response  rpm  runInstaller  sshsetup  stage  welcome.html
[oracle@nextcurve database]$ ./runInstaller 
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 500 MB.   Actual 46276 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 3967 MB    Passed
Checking monitor: must be configured to display at least 256 colors.    Actual 16777216    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2016-05-14_03-21-02PM. Please wait ...[oracle@nextcurve database]$ You can find the log of this install session at:
 /opt/oracle/oraInventory/logs/installActions2016-05-14_03-21-02PM.log

Tablespaces in multitenancy

Note:

  • In a non-CDB, all the tablespaces belong to one database. 
  • In the CDB, one set of tablespaces belong to the root container, and each PDB has its own set of tablespaces. 
  • Common objects are created and their data stored in a tablespace in the root container. The common object is visible in the PDBs through links. 
  • There are new clauses in the CREATE DATABASE command. The USER_DATA TABLESPACE allows you to specify a default tablespace other than USERS when using DBCA to create a database. This tablespace will also be used for XDB options.
  • The UNDO tablespace is common to all PDBs, that is, you can have more than one but there is only one active UNDO tablespace per CDB.

To view the all tablespaces

SQL> SELECT tablespace_name, DECODE(pdb_name, NULL, 'ROOT',pdb_name) pdb_name
  2  FROM cdb_tablespaces t, cdb_pdbs p
  3  WHERE t.con_id = p.pdb_id(+)
  4  ORDER BY 2,1;

TABLESPACE_NAME                PDB_NAME
------------------------------ --------------------
SYSAUX                         PDB$SEED
SYSTEM                         PDB$SEED
TEMP                           PDB$SEED
SYSAUX                         PDB2
SYSTEM                         PDB2
TEMP                           PDB2
SYSAUX                         ROOT
SYSTEM                         ROOT
TEMP                           ROOT
UNDOTBS1                       ROOT
UNDOTBS2                       ROOT
USERS                          ROOT

12 rows selected.

The CREATE TABLESPACE command should be familiar. The change in its behavior in a CDB is that the tablespace is created in the container where the command is executed. Separating the data files into different directories by PDB can help determine which files belong to which PDB, though it is not necessary but it is good from administration perspective.

Create tablespace at CDB

SQL> connect system/oracle@cdb
Connected.
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> CREATE TABLESPACE cdb_tbs
  2  DATAFILE '+DATA/ora12c/cdb_tbs01.dbf'
  3  SIZE 5M;

Tablespace created.

Create tablespace at PDB

SQL> connect system/oracle@pdb2
Connected.

SQL> show con_name
CON_NAME
------------------------------
PDB2
SQL> CREATE TABLESPACE pdb2_tbs
  2  DATAFILE '+DATA/pdb2/pdb2_tbs01.dbf'
  3  SIZE 5M;

Tablespace created.

Tablespaces in multitenancy

Note:

  • In a non-CDB, all the tablespaces belong to one database. 
  • In the CDB, one set of tablespaces belong to the root container, and each PDB has its own set of tablespaces. 
  • Common objects are created and their data stored in a tablespace in the root container. The common object is visible in the PDBs through links. 
  • There are new clauses in the CREATE DATABASE command. The USER_DATA TABLESPACE allows you to specify a default tablespace other than USERS when using DBCA to create a database. This tablespace will also be used for XDB options.
  • The UNDO tablespace is common to all PDBs, that is, you can have more than one but there is only one active UNDO tablespace per CDB.

To view the all tablespaces

SQL> SELECT tablespace_name, DECODE(pdb_name, NULL, 'ROOT',pdb_name) pdb_name
  2  FROM cdb_tablespaces t, cdb_pdbs p
  3  WHERE t.con_id = p.pdb_id(+)
  4  ORDER BY 2,1;

TABLESPACE_NAME                PDB_NAME
------------------------------ --------------------
SYSAUX                         PDB$SEED
SYSTEM                         PDB$SEED
TEMP                           PDB$SEED
SYSAUX                         PDB2
SYSTEM                         PDB2
TEMP                           PDB2
SYSAUX                         ROOT
SYSTEM                         ROOT
TEMP                           ROOT
UNDOTBS1                       ROOT
UNDOTBS2                       ROOT
USERS                          ROOT

12 rows selected.

The CREATE TABLESPACE command should be familiar. The change in its behavior in a CDB is that the tablespace is created in the container where the command is executed. Separating the data files into different directories by PDB can help determine which files belong to which PDB, though it is not necessary but it is good from administration perspective.

Create tablespace at CDB

SQL> connect system/oracle@cdb
Connected.
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> CREATE TABLESPACE cdb_tbs
  2  DATAFILE '+DATA/ora12c/cdb_tbs01.dbf'
  3  SIZE 5M;

Tablespace created.

Create tablespace at PDB

SQL> connect system/oracle@pdb2
Connected.

SQL> show con_name
CON_NAME
------------------------------
PDB2
SQL> CREATE TABLESPACE pdb2_tbs
  2  DATAFILE '+DATA/pdb2/pdb2_tbs01.dbf'
  3  SIZE 5M;

Tablespace created.

Using glogin.sql to have friendly and nice SQLPLUS multitenancy environment

When we are working at multitenancy it is good to have glogin configured to show container info. I am having following glogin.sql.

You can find the glogin.sql file in $ORACLE_HOME/sqlplus/admin


column id_plus_exp format 990 heading i
column parent_id_plus_exp format 990 heading p
column plan_plus_exp format a60
column object_node_plus_exp format a8
column other_tag_plus_exp format a29
column other_plus_exp format a44
define _editor=notepad
--define _editor=vi
set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 100
set pagesize 9999
column plan_plus_exp format a80
column global_name new_value gname
set termout off
define gname=idle
column global_name new_value gname
select lower(user) || '@' || substr(global_name, 1,
decode( dot, 0,
length(global_name), dot-1)
) || '->'|| host_name global_name
from (select global_name, instr(global_name,'.') dot from global_name)
,v$instance;
set sqlprompt '&gname> '
set termout on

For unix or linux you need to change the define_editor
define_editor=notepad

to
define_editor=vi
The sample like following

C:\>sqlplus system/oracle@pdb2

SQL*Plus: Release 12.1.0.1.0 Production on Tue Dec 17 15:40:38 2013

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

Last Successful login time: Tue Dec 17 2013 15:32:59 +08:00

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

system@PDB2->ora12cn1>

Using glogin.sql to have friendly and nice SQLPLUS multitenancy environment

When we are working at multitenancy it is good to have glogin configured to show container info. I am having following glogin.sql.

You can find the glogin.sql file in $ORACLE_HOME/sqlplus/admin


column id_plus_exp format 990 heading i
column parent_id_plus_exp format 990 heading p
column plan_plus_exp format a60
column object_node_plus_exp format a8
column other_tag_plus_exp format a29
column other_plus_exp format a44
define _editor=notepad
--define _editor=vi
set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 100
set pagesize 9999
column plan_plus_exp format a80
column global_name new_value gname
set termout off
define gname=idle
column global_name new_value gname
select lower(user) || '@' || substr(global_name, 1,
decode( dot, 0,
length(global_name), dot-1)
) || '->'|| host_name global_name
from (select global_name, instr(global_name,'.') dot from global_name)
,v$instance;
set sqlprompt '&gname> '
set termout on

For unix or linux you need to change the define_editor
define_editor=notepad

to
define_editor=vi
The sample like following

C:\>sqlplus system/oracle@pdb2

SQL*Plus: Release 12.1.0.1.0 Production on Tue Dec 17 15:40:38 2013

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

Last Successful login time: Tue Dec 17 2013 15:32:59 +08:00

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

system@PDB2->ora12cn1>
Subscribe to: Posts (Atom)