Wednesday, February 24, 2016

Datapump enhancements and new features in 12c

*************************************************************************
Datapump enhancements and new features in  12c 

*************************************************************************

Login as sys dba and create user & data pump directory as below:


sqlplus /"as sysdba"


SQL> create directory dpump as '/u01/app/oracle/dpump';

Directory created.


SQL> grant read,write on directory dpump to pdborcl1_usr2;

Grant succeeded.

***************************************************
EXPORTING TABLES FROM PDB :
***************************************************

[oracle@Linux03 oracle]$ expdp pdborcl1_usr2/xxxxxxxx@pdborcl1 directory=dpump dumpfile=pdborc1_usr2_tables tables=pdborcl1_usr2.TEST,pdborcl1_usr2.TEST_ID

Export: Release 12.1.0.2.0 - Production on Fri Mar 11 14:54:24 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  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
Starting "PDBORCL1_USR2"."SYS_EXPORT_TABLE_01":  pdborcl1_usr2/********@pdborcl1 directory=dpump dumpfile=pdborc1_usr2_tables tables=pdborcl1_usr2.TEST,pdborcl1_usr2.TEST_ID 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "PDBORCL1_USR2"."TEST"                      5.078 KB       1 rows
. . exported "PDBORCL1_USR2"."TEST_ID"                   5.937 KB       2 rows
Master table "PDBORCL1_USR2"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for PDBORCL1_USR2.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/dpump/pdborc1_usr2_tables.dmp
Job "PDBORCL1_USR2"."SYS_EXPORT_TABLE_01" successfully completed at Fri Mar 11 14:54:39 2016 elapsed 0 00:00:13



***************************************************
LOGTIME=[NONE | STATUS | LOGFILE | ALL]
NONE : The default value, no timestamps to be included in output, same as previous versions.
STATUS : Timestamps are included in output console, but not in log file.
LOGFILE : Timestamps are included in log file, but not on console.
ALL : Timestamps are included in output to the log file and console.
***************************************************

Simple example : 

expdp pdborcl1_usr2/********@pdborcl1 directory=dpump dumpfile=pdborc1_usr2_tables1 tables=pdborcl1_usr2.TEST,pdborcl1_usr2.TEST_ID logtime=all

Export: Release 12.1.0.2.0 - Production on Fri Mar 11 14:56:54 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  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
11-MAR-16 14:56:56.798: Starting "PDBORCL1_USR2"."SYS_EXPORT_TABLE_01":  pdborcl1_usr2/********@pdborcl1 directory=dpump dumpfile=pdborc1_usr2_tables1 tables=pdborcl1_usr2.TEST,pdborcl1_usr2.TEST_ID logtime=all 
11-MAR-16 14:56:57.303: Estimate in progress using BLOCKS method...
11-MAR-16 14:56:58.213: Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
11-MAR-16 14:56:58.257: Total estimation using BLOCKS method: 128 KB
11-MAR-16 14:57:01.225: Processing object type TABLE_EXPORT/TABLE/TABLE
11-MAR-16 14:57:01.700: Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN
11-MAR-16 14:57:02.212: Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
11-MAR-16 14:57:03.340: Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
11-MAR-16 14:57:03.385: Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
11-MAR-16 14:57:03.978: Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
11-MAR-16 14:57:03.994: Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
11-MAR-16 14:57:08.998: . . exported "PDBORCL1_USR2"."TEST"                      5.078 KB       1 rows
11-MAR-16 14:57:09.013: . . exported "PDBORCL1_USR2"."TEST_ID"                   5.937 KB       2 rows
11-MAR-16 14:57:09.235: Master table "PDBORCL1_USR2"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
11-MAR-16 14:57:09.240: ******************************************************************************
11-MAR-16 14:57:09.241: Dump file set for PDBORCL1_USR2.SYS_EXPORT_TABLE_01 is:
11-MAR-16 14:57:09.246:   /u01/app/oracle/dpump/pdborc1_usr2_tables1.dmp
11-MAR-16 14:57:09.260: Job "PDBORCL1_USR2"."SYS_EXPORT_TABLE_01" successfully completed at Fri Mar 11 14:57:09 2016 elapsed 0 00:00:13

***************************************************
DISABLE ARCHIVE LOGGINGTHE. DEFAULT SETTING OF "N" HAS NO AFFECT ON LOGGING BEHAVIOUR.USING A VALUE "Y" REDUCES THE LOGGING ASSOCIATED WITH TABLES :
***************************************************

impdp pdborcl1_usr2/pdborcl1_usr2@pdborcl1 directory=dpump dumpfile=pdborc1_usr2_tables3 tables=pdborcl1_usr2.TEST,pdborcl1_usr2.TEST_ID logtime=status TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y

The effect can be limited to a specific type of object (TABLE or INDEX) as below.

TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:TABLE
TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y:INDEX

Friday, February 19, 2016

Audit data pump actions in 12c

   ******************************************************************************
Audit Data pump actions
******************************************************************************

[oracle@Linux03 Desktop]$ sqlplus /"As sysdba"

SQL> alter session set container=pdborcl1;

Session altered.

SQL> create directory dpump as '/u01/app/oracle/dpump';

Directory created.

SQL> grant read,write on directory dpump to pdborcl1_usr2;

Grant succeeded.

******************************************************************************

SQL> SET LINESIZE 200
COLUMN event_timestamp FORMAT A30
COLUMN dp_text_parameters1 FORMAT A30
COLUMN dp_boolean_parameters1 FORMAT A30

SELECT event_timestamp,
       dp_text_parameters1,
       dp_boolean_parameters1
FROM   unified_audit_trail
WHERE  audit_type = 'Datapump';

no rows selected

SQL> CREATE AUDIT POLICY audit_dp_all_policy ACTIONS COMPONENT=DATAPUMP ALL;

Audit policy created.

SQL> AUDIT POLICY audit_dp_all_policy BY pdborcl1_usr2;

Audit succeeded.

******************************************************************************

[oracle@Linux03 oracle]$ expdp pdborcl1_usr2/XXXXXXX@pdborcl1 directory=dpump dumpfile=pdborc1_usr2_tables15 tables=pdborcl1_usr2.TEST,pdborcl1_usr2.TEST_ID logtime=status

Export: Release 12.1.0.2.0 - Production on Fri Mar 11 15:24:13 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  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
11-MAR-16 15:24:15.351: Starting "PDBORCL1_USR2"."SYS_EXPORT_TABLE_01":  pdborcl1_usr2/********@pdborcl1 directory=dpump dumpfile=pdborc1_usr2_tables15 tables=pdborcl1_usr2.TEST,pdborcl1_usr2.TEST_ID logtime=status
11-MAR-16 15:24:15.810: Estimate in progress using BLOCKS method...
11-MAR-16 15:24:16.683: Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
11-MAR-16 15:24:16.778: Total estimation using BLOCKS method: 128 KB
11-MAR-16 15:24:21.341: Processing object type TABLE_EXPORT/TABLE/TABLE
11-MAR-16 15:24:22.237: Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN
11-MAR-16 15:24:23.233: Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
11-MAR-16 15:24:24.615: Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
11-MAR-16 15:24:24.656: Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
11-MAR-16 15:24:25.230: Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
11-MAR-16 15:24:25.245: Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
11-MAR-16 15:24:29.893: . . exported "PDBORCL1_USR2"."TEST"                      5.078 KB       1 rows
11-MAR-16 15:24:29.907: . . exported "PDBORCL1_USR2"."TEST_ID"                   5.937 KB       2 rows
11-MAR-16 15:24:30.137: Master table "PDBORCL1_USR2"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
11-MAR-16 15:24:30.142:

******************************************************************************
11-MAR-16 15:24:30.142: Dump file set for PDBORCL1_USR2.SYS_EXPORT_TABLE_01 is:
11-MAR-16 15:24:30.147:   /u01/app/oracle/dpump/pdborc1_usr2_tables15.dmp
11-MAR-16 15:24:30.164: Job "PDBORCL1_USR2"."SYS_EXPORT_TABLE_01" successfully completed at Fri Mar 11 15:24:30 2016 elapsed 0 00:00:15

******************************************************************************

SQL> SET LINESIZE 200
COLUMN event_timestamp FORMAT A30
COLUMN dp_text_parameters1 FORMAT A30
COLUMN dp_boolean_parameters1 FORMAT A30

SELECT event_timestamp,
       dp_text_parameters1,
       dp_boolean_parameters1
FROM   unified_audit_trail
WHERE  audit_type = 'Datapump';

EVENT_TIMESTAMP       DP_TEXT_PARAMETERS1      DP_BOOLEAN_PARAMETERS1
------------------------------ ------------------------------ ------------------------------
11-MAR-16 08.24.15.775940 AM   MASTER TABLE:  "PDBORCL1_USR2" MASTER_ONLY: FALSE, DATA_ONLY:
      ."SYS_EXPORT_TABLE_01" , JOB_T  FALSE, METADATA_ONLY: FALSE,
      YPE: EXPORT, METADATA_JOB_MODE DUMPFILE_PRESENT: TRUE, JOB_RE
      : TABLE_EXPORT, JOB VERSION: 1 STARTED: FALSE
      2.1.0.2.0, ACCESS METHOD: AUTO
      MATIC, DATA OPTIONS: 0, DUMPER
DIRECTORY: NULL  REMOTE LINK:
NULL, TABLE EXISTS: NULL, PAR
      TITION OPTIONS: NONE

Thursday, February 18, 2016

create pluggable database with dbca


Create a Pluggable Database (PDB) using the DBCA


On the "Manage Pluggable Databases" screen shown previously, select the "Create a Pluggable Database" option and click the "Next"






select the container database to use for the new pluggable database and click the "Next" button.



If you were plugging in a previously unplugged database, you would select the PDB Archive or PDB File.




Add name for pdb.
location to keep files.
Administrator account username & password for this pdb. 




Review you pdb summary and select finish.



That's it pdb database is created. Lets query the database from v$pdbs



Tuesday, February 9, 2016

Step by Step installation of oracle 12C database on Linux 6 (centos)

Assumptions :


  • You have a some flavor of Linux operating system installed (I have used centos 6 in this example).
  • If you cant afford a separate machine you can use Virtual box or stemware software to visualize your desktop or laptop.
  • Assuming that you have downloaded oracle 12 software onto linux machine. If not you can download from this link Software-Download
  • You have full/required privileges on you Linux host.

Oracle Installation Prerequisites


In order to perform the installtion of oracle 12c software on Linux box you need to perform some pre-reqs, which can be done automatically or through manual updates.Please follow below instructions.

Automatic Setup

If you plan to use the "oracle-rdbms-server-12cR1-preinstall" package to perform all your prerequisite setup, issue the following command.

# yum install oracle-rdbms-server-12cR1-preinstall -y


It will be a good option to to do an update.


# yum update





************* ***********
MANUAL SETUP
************* ***********

If you have not used the "oracle-rdbms-server-12cR1-preinstall" package to perform all prerequisites, you will need to manually perform the following setup tasks.


Add or amend the following lines in the "/etc/sysctl.conf" file.

fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500
Run the following command to change the current kernel parameters.

/sbin/sysctl -p

Add the following lines to the "/etc/security/limits.conf" file.

oracle   soft   nofile    1024
oracle   hard   nofile    65536
oracle   soft   nproc    16384
oracle   hard   nproc    16384
oracle   soft   stack    10240
oracle   hard   stack    32768

MANUALLY INSTALL PACKAGES FROM INTERNET OR FROM CD DRIVE(below is to install from INTERNET) :

# From Public Yum or ULN
 Yum install binutils,  compat-libcap1,  compat-libstdc++-33,  compat-libstdc++-33.i686,  gcc,  gcc-c++,  glibc,  glibc.i686,  glibc-devel,  glibc-devel.i686,  ksh,  libgcc,  libgcc.i686,  libstdc++,  libstdc++.i686,  libstdc++-devel,  libstdc++-devel.i686,  libaio,  libaio.i686,  libaio-devel,  libaio-devel.i686,  libXext,  libXext.i686,  libXtst,   libXtst.i686,  libX11,   libX11.i686,  libXau,  libXau.i686,   libxcb,  libxcb.i686,  libXi,  libXi.i686,  make,  sysstat,  unixODBC,  unixODBC-devel
Create the new groups and users as per your requirement. For my case just to keep it simple lets use 3 groups & oracle user.

groupadd -g 54321 oinstall
groupadd -g 54322 dba
groupadd -g 54323 oper

useradd -u 54321 -g oinstall -G dba,oper oracle.

Set SELINUX to permissive or diable it if this is test env.
Set secure Linux to permissive by editing the "/etc/selinux/config" file, making sure the SELINUX flag is set as follows.

SELINUX=permissive

Create the directories in which the Oracle software will be installed.

mkdir -p /u01/app/oracle/product/12.1/db_1
chown -R oracle:oinstall /u01
chmod -R 775 /u01

LOGIN AS ORACLE USER AND 
Add the following lines at the end of the "/home/oracle/.bash_profile" file.

# Oracle Settings
export TMP=/tmp
export TMPDIR=$TMP

export ORACLE_HOSTNAME=ol6-121.localdomain
export ORACLE_UNQNAME=orcl
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1/db_1
export ORACLE_SID=orcl

export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

Change your directory to location where you have downloaded oracle software.




Start the Oracle Universal Installer (OUI) by issuing the following command in the database directory.

./runInstaller



I don't want any update to uncheck to receive updates.





Lets create a server class.





To keep this simple I am going to select typical installation.



Make sure you have selected the home path correct. 




After This step you will be notified if you have any pre-req failures. Make sure you have cleared them all. Missing ksh package can be ignored as this is a known bug. Oracle is expecting a specific version of ksh & i have a latest pkg. Assuming that you have all cleared up.




Now select install.


Now you will be prompted to execute shell scripts before the installation of software is complete. I have missed that prompt screen but it will ask you to execute below 2 shell scripts as root user. see below screen



After executing hit OK and it will continue to install oracle DB software.




You will see this screen after installation is complete.





That's it you have completed your oracle 12c database software installation. You can query as below




Please drop your comments below if you found this blog helpful to you.