Optimizer with Parameters

Oracle 11g Parameters for Optimizer

SQL> show parameter optimizer_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_dynamic_sampling           integer     2
optimizer_features_enable            string      11.2.0.3
optimizer_index_caching              integer     0
optimizer_index_cost_adj             integer     100
optimizer_mode                       string      ALL_ROWS
optimizer_secure_view_merging        boolean     TRUE
optimizer_use_invisible_indexes      boolean     FALSE
optimizer_use_pending_statistics     boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE
SQL>

In Oracle 12c, two new initialization parameters added to Optimizer

1. optimizer_adaptive_features
2. optimizer_adaptive_reporting_only

Oracle Exadata Hybrid Columnar Compression (EHCC) Levels

SQL> connect sh/oracle@orcl
Connected.

Creating a HCC Table Compressed for Query High
==============================================
SQL> create table sales_queryhigh (
empname                 varchar2(30),
empno            number,
empdescr                varchar2(100),
hire_date               date
) tablespace users compress for query high;

Table created.

SQL> select table_name,compression,compress_for from user_tables where table_name='SALES_QUERYHIGH';

TABLE_NAME                     COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
SALES_QUERYHIGH                ENABLED  QUERY HIGH

Creating a HCC Table with HCC Partitions
========================================
SQL> create table orders (
  2  cid number,
  3  pid number,
  4  sid number,
  5  price number(5,2),
  6  discount number(3,2),
  7  odate date)
  8  partition by range (cid)
  9  (partition p1 values less than (100000) nocompress,
 10   partition p2 values less than (200000) compress for archive low,
 11   partition p3 values less than (300000) compress for query high,
 12   partition p4 values less than (maxvalue) compress for query low)
 13  enable row movement
 14  ;

Table created.

SQL> select table_name,compression,compress_for from user_tab_partitions where table_name='ORDERS';

TABLE_NAME                     COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
ORDERS                         DISABLED
ORDERS                         ENABLED  ARCHIVE LOW
ORDERS                         ENABLED  QUERY HIGH
ORDERS                         ENABLED  QUERY LOW

Compression attributes for Tablespace
=====================================

SQL> connect sys/oracle@orcl as sysdba
Connected.

SQL> create bigfile tablespace bigtbs
datafile '+DATA' size 1G
autoextend on next 1m
extent management local autoallocate
segment space management auto
default compress for query low;

Tablespace created.

SQL> select tablespace_name, def_tab_compression, nvl(compress_for,'NONE') compress_for
from dba_tablespaces;
where tablespace_name='BIGTBS';

TABLESPACE_NAME                DEF_TAB_ COMPRESS_FOR
------------------------------ -------- ------------
SYSTEM                         DISABLED NONE
SYSAUX                         DISABLED NONE
UNDOTBS1                       DISABLED NONE
TEMP                           DISABLED NONE
USERS                          DISABLED NONE
EXAMPLE                        DISABLED NONE
BIGTBS                         ENABLED  QUERY LOW

7 rows selected.

SQL>

Oracle GoldenGate 12.2 New Features

Oracle GoldenGate 12.2 New Features

1. Replicat environment 
=======================
Note: No need specify No SOURCEDEFS! & No ASSUMETARGETDEFS!

REPLICAT rorcl
USERIDALIAS ggs_admin
DDL include all
MAP scott.*, TARGET scott.*;

2. Automatic Heartbeat Table
============================
a. Automatically discovers Database Replication Topology
b. Execute GGSCI command ADD HEARTBEATTABLE at each database 
c. Monitoring Lag using GG_LAG View

3. Parameter Validation
=======================
a. New standalone utility checkprm for validation 
b. New INFO PARAM GGSCI command to obtain definitions of parameters

4. Transparent Integration with Oracle Clusterware (OCS)
========================================================
a. Achieve GoldenGate high availability in a cluster configuration.
b. GoldenGate is managed/monitored by Oracle Clusterware.
c. Add parameter “XAG_ENABLE” to GLOBALS to enable this feature

5. Fetching from Oracle Active Data Guard (ADG)
===============================================
a. Need source database for startup validations, registration and some metadata lookups
b. Enable with FETCH_USER_ID ggadmin@adg password oracle or FETCHUSERIDALIAS ggadmin_adg

Oracle Database 12c – Partition Table Enhancements (Interval reference, Cascading Truncate and Exchange Operations) for Pluggable database (PDB) in Container Database (CDB)

Article-67:

The Oracle ACE Program Newsletter | May 2016

Happy to see name in "The Oracle ACE Program Newsletter | May 2016"

The Oracle ACE Program Newsletter | April 2016

Happy to see my name in "The Oracle ACE Program Newsletter | April 2016" 

Oracle Open World (OOW) 2016

Key Dates for Oracle Open World (OOW) 2016

Call for Proposals—Open Monday, April 11
Call for Proposals—Closed Monday, May 9, 11:59 p.m. PDT

Regisration: https://www.oracle.com/openworld/register/index.html

Oracle Database 12c - DataPump (LOGTIME Parameter)

The LOGTIME parameter determines if timestamps should be included in the output messages from the expdp and impdp utilities.

LOGTIME=[NONE | STATUS | LOGFILE | ALL] 
The allowable values are explained below.

1. NONE : The default value, which indicates that no timestamps should be included in the output, 
   making the output look similar to that of previous versions.
2. STATUS : Timestamps are included in output to the console, but not in the associated log file.
3. LOGFILE : Timestamps are included in output to the log file, but not in the associated console messages.
4. ALL : Timestamps are included in output to the log file and console.
[oracle@localhost ddl]$ sqlplus /nolog

SQL> connect sys/oracle@orcl as sysdba

SQL> CREATE OR REPLACE DIRECTORY test_dir AS '/u01/app/oracle/oradata/';
Directory created.

SQL> GRANT READ, WRITE ON DIRECTORY test_dir TO scott;
Grant succeeded.

SQL> !expdp scott/oracle@orcl tables=emp directory=test_dir dumpfile=emp.dmp logfile=expdp_emp.log logtime=all

SQL> ! ls -lrt /u01/app/oracle/oradata/
total 192
-rw-r--r--. 1 oracle oinstall   1879 Nov  4 17:16 expdp_emp.log
-rw-r-----. 1 oracle oinstall 163840 Nov  4 17:16 emp.dmp

Oracle Database 12c – Creation Of Database - Manual Method

Creating a Container Database – Using SQL *Plus 

Step-1: Create the following directory and password file under ‘/u01/app/oracle/oradata’

a. ->cdb3

Note: under ->cdb3 directory – create ‘seed’ directory
  
[oracle@12casm oracle]$ cd /u01/app/oracle/product/12.1.0.1/db_1/dbs
[oracle@12casm oracle]$ orapwd file=orapwcdb3 password=oracle entries=5 force=y 
[oracle@12casm ~]$ cd /u01/app/oracle/oradata
[oracle@12casm oracle]$ mkdir -p  cdb3/pdbseed

Step-2: Create the following directories under ‘/u01/app/oracle/admin’

a. ->cdb3

[oracle@12casm cdb1]$ cd /u01/app/oracle/admin/
[oracle@12casm admin]$ mkdir cdb3

Step-3: Create the following directories under ‘/u01/app/oracle/admin/cdb3’

a. ->dpdump
b. ->pfile
c. ->adump
d. ->xdb_wallet

[oracle@12casm admin]$ cd cdb3/
[oracle@12casm cdb3]$ mkdir dpdump xdb_wallet pfile adump

Step-4: Create the following directories under ‘/u01/app/oracle’

a. ->fast_recovery_area

[oracle@12casm pfile]$ cd /u01/app/oracle/
[oracle@12casm oracle]$ ls -lrth
[oracle@12casm oracle]$ mkdir -p fast_recovery_area/cdb3

Step-5: Create the following directories under ‘/u01/app/oracle/fast_recovery_area’

a. ->cdb3

[oracle@12casm oracle]$ cd fast_recovery_area/cdb3/

Step-6: Set the environment variables and Oracle Home Directory


[oracle@12casm ~]$ export ORACLE_SID=cdb3
[oracle@12casm ~]$ export ORACLE_HOME =  /u01/app/oracle/product/12.1.0.1/db_1

Step-7: Prepare the initcdb3.ora and place in '/u01/app/oracle/admin/cdb3/pfile/initcdb3.ora'

###########################################
# Cache and I/O
###########################################
db_block_size=8192

###########################################
# Cursors and Library Cache
###########################################
open_cursors=300

###########################################
# Database Identification
###########################################
db_domain=""
db_name="cdb3"

###########################################
# File Configuration
###########################################
control_files=("/u01/app/oracle/oradata/cdb3/control01.ctl","/u01/app/oracle/fast_recovery_area/cdb3/control02.ctl")
db_recovery_file_dest="/u01/app/oracle/fast_recovery_area"
db_recovery_file_dest_size=4800m

###########################################
# Miscellaneous
###########################################
#compatible=12.1.0.0.0
diagnostic_dest=/u01/app/oracle
enable_pluggable_database=true
memory_target=1560m

###########################################
# Processes and Sessions
###########################################
processes=300

###########################################
# Security and Auditing
###########################################
audit_file_dest="/u01/app/oracle/admin/cdb3/adump"
audit_trail=db
remote_login_passwordfile=EXCLUSIVE

###########################################
# Shared Server
###########################################
dispatchers="(PROTOCOL=TCP) (SERVICE=cdb3XDB)"

###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_tablespace=UNDOTBS

[oracle@12casm ~]$

Step-8: Login as sysdba and create spfile from pfile

[oracle@12casm ~]$ sqlplus /nolog
SQL> connect /as sysdba
SQL> startup pfile ='/u01/app/oracle/admin/cdb3/pfile/initcdb3.ora' nomount;

SQL> create spfile from pfile ='/u01/app/oracle/admin/cdb3/pfile/initcdb3.ora';
File created.

SQL> shut immediate;
SQL> startup nomount;

Step-9: Execute ‘create database’ command

SQL> create database cdb3
user sys identified by oracle user system identified by oracle
logfile group 1 ('/u01/app/oracle/oradata/cdb3/redo1a.log','/u01/app/oracle/oradata/cdb3/redo1b.log') size 50M,
group 2 
('/u01/app/oracle/oradata/cdb3/redo2a.log','/u01/app/oracle/oradata/cdb3/redo2b.log') size 50M
character set AL32UTF8 national character set AL16UTF16
extent management local datafile
                           '/u01/app/oracle/oradata/cdb3/system01.dbf' size 480M
sysaux datafile '/u01/app/oracle/oradata/cdb3/sysaux01.dbf' size 480M
default temporary tablespace tbstemp
tempfile '/u01/app/oracle/oradata/cdb3/temp01.dbf' size 50M
undo tablespace undotbs
datafile '/u01/app/oracle/oradata/cdb3/undotbs01.dbf' size 200M
enable pluggable database
seed file_name_convert = ('/u01/app/oracle/oradata/cdb3','/u01/app/oracle/oradata/cdb3/pdbseed/');
Database created.

Step-10: Run the post creation scripts

SQL> alter session set "_oracle_script"=TRUE;
Session altered.

SQL> select name,open_mode from v$pdbs;
NAME                            OPEN_MODE
------------------------------  ----------
PDB$SEED                        READ ONLY

SQL> select name,open_mode from v$database;
NAME       OPEN_MODE
---------   --------------------
CDB3       READ WRITE

Step-11: Close and Open the PDB

SQL> alter pluggable database pdb$seed close;
Pluggable database altered.

SQL> alter pluggable database pdb$seed open;
Pluggable database altered.

SQL> select name,open_mode from v$pdbs;
NAME                            OPEN_MODE
------------------------------  ----------
PDB$SEED                        READ WRITE

Step-12: Execute the scripts

a. @?/rdbms/admin/catalog.sql
b. @?/rdbms/admin/catblock.sql
c. @?/rdbms/admin/catproc.sql
d. @?/rdbms/admin/catoctk.sql
e. @?/rdbms/admin/owminst.plb
f. @?/sqlplus/admin/pupbld.sql

Step-13: Configure EM Express 

SQL> SELECT DBMS_XDB_CONFIG.gethttpport FROM dual;
SQL> SELECT DBMS_XDB_CONFIG.gethttpsport FROM dual;
SQL> EXEC DBMS_XDB_CONFIG.sethttpsport(5505);
PL/SQL procedure successfully completed.

SQL> SELECT DBMS_XDB_CONFIG.gethttpsport FROM dual;

GETHTTPSPORT
------------
        5501

1 row selected.

SQL> alter user sys identified by oracle;
User altered.

SQL> alter user system identified by oracle;
User altered.

URL: https://192.168.56.101:5505/em

Basic tasks on Pluggable Database (PDB) in Container Database (CDB)

Query to check whether the Database is Multitenant Database CDB:

1.[oracle@localhost ~]$ sqlplus sys/oracle@192.168.56.101:1521/cdb1 as sysdba

2.SQL> select NAME,DECODE(CDB,'YES','Multitenant Option Enabled','Regular 12c Database: ') "Multitenant Option ?", open_mode,con_id
       from v$database;

Query on currently connected instance:
============================
SQL> show con_name
SQL> show con_id

Information on Pluggable Databases (PDBs) in Container Database (CDB):
======================================================
SQL> select con_id, name, open_mode from v$pdbs;
SQL> col member format a45
SQL> select group#,member,con_id from v$logfile;
SQL> col name format a60
SQL> select name,con_id from v$controlfile;

SQL> set pagesize 40
SQL> col tablespace_name format A8
SQL> col file_id format 9999
SQL> col con_id format 999
SQL> select FILE_NAME, TABLESPACE_NAME, FILE_ID, con_id from cdb_data_files order by con_id ;

SQL> col NAME format A12
SQL> select FILE#, ts.name, ts.ts#, ts.con_id
from v$datafile d, v$tablespace ts
where d.ts#=ts.ts# and d.con_id=ts.con_id
order by 4,3;

SQL> col file_name format A50
SQL> select FILE_NAME, TABLESPACE_NAME, FILE_ID
from cdb_temp_files;

SQL> col username format A22
SQL> select username, common, con_id from cdb_users
where username ='SYSTEM';

Connection Information for Container Database and Pluggable Databases:
====================================================
SQL> connect sys/oracle@192.168.56.101:1521/cdb1 as sysdba
SQL> select con_id, name, open_mode from v$pdbs;
SQL> connect sys/oracle@192.168.56.101:1521/pdb1 as sysdba
SQL> select con_id, name, open_mode from v$pdbs;
SQL> connect sys/oracle@192.168.56.101:1521/pdb2 as sysdba
SQL> alter pluggable database pdb2 open;
SQL> select con_id, name, open_mode from v$pdbs;

Information on Datafiles Of Container Database and Pluggable Databases:
=====================================================
SQL> set linesize 300
SQL> set lines 300 pages 100
SQL> col file_name format a60
SQL> connect sys/oracle@192.168.56.101:1521/cdb1 as sysdba
SQL> select file_name,tablespace_name from dba_data_files;
SQL> select file_name,tablespace_name from dba_temp_files;

Information on Datafiles Of Pluggable Databases:
=====================================
SQL> connect sys/oracle@192.168.56.101:1521/pdb1 as sysdba
SQL> select file_name,tablespace_name from dba_data_files;
SQL> select file_name,tablespace_name from dba_temp_files;

Information on Datafiles Of Pluggable Databases:
====================================
SQL> connect sys/oracle@192.168.56.101:1521/pdb2 as sysdba
SQL> select file_name,tablespace_name from dba_data_files;
SQL> select file_name,tablespace_name from dba_temp_files;

Explore the cdb instance and its background processes and the container database
==========================================================
[oracle@localhost admin]$ pgrep -lf cdb3

Explore the services : Using the lsnrctl utility determine which services are currently registered
====================================================================

[oracle@localhost admin]$ lsnrctl services

Use SYS_CONTEXT function to view the CON_NAME and CON_ID attributes of your session context.
=====================================================================
[oracle@localhost ~]$ . oraenv
ORACLE_SID = [noncdb] ? cdb3
[oracle@localhost ~]$ sqlplus /nolog
SQL> connect sys/oracle@192.168.56.101:1521/cdb3 as sysdba
SQL> SELECT sys_context('userenv','CON_NAME') from dual;
SQL> SELECT sys_context('userenv','CON_ID') from dual;

View new family of views CDB_xxx
================================
SQL> col PDB_NAME format a8
SQL> col CON_ID format 999999
SQL> select PDB_ID, PDB_NAME, DBID, GUID, CON_ID from cdb_pdbs ;

View all data files of the CDB, including those of the root and all PDBs, with CDB_DATA_FILES view
========================================================================
SQL> set pagesize 40
SQL> col file_name format A50
SQL> col tablespace_name format A8
SQL> col file_id format 9999
SQL> col con_id format 999
SQL> select FILE_NAME, TABLESPACE_NAME, FILE_ID, con_id from cdb_data_files order by con_id ;

Use V$TABLESPACE and V$DATAFILE view
====================================
SQL> col NAME format A12
SQL> select FILE#, ts.name, ts.ts#, ts.con_id
from v$datafile d, v$tablespace ts
where d.ts#=ts.ts# and d.con_id=ts.con_id
order by 4,3;

List all common users in the CDB
========================
SQL> select distinct username from cdb_users where common ='YES';

List all local users in the CDB
======================
SQL> select distinct username from cdb_users where common ='NO';

List local users in root
================
SQL> select distinct username from dba_users where common ='NO';

Note: Notice that there is no local user in the root container because it is impossible to create any local user in the root.

List all roles and privileges in the CDB
============================
SQL> col role format A30
SQL> select role, common, con_id from cdb_roles;