Oracle 12c – Use Index enhancements

March 29, 2016 Larry Catt Leave a comment

You can create multiple indexes of different types on the same set of columns now. But only one index will be visible at any given time. Multiple indexes can exist when one of the following is true

B-tree vs bitmap
Different partitioning sets exist.
Unique vs non-unique.

Example: Creating two indexes on single column:

Logon to SQLPLUS.

[oracle@linux2 ~]$ sqlplus scott/password

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jan 29 13:47:09 2016

Last Successful login time: Tue Jan 27 2016 09:57:58 -05:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

SQL>

Create bitmap index on table scott.emp(job).

SQL> create bitmap index bm_scott_emp_job on scott.emp(job);

Index created.

SQL>

View the new index from user_constraints.

SQL> column index_name format a20

SQL> column index_type format a10

SQL> select index_name, index_type, visibility from user_indexes where table_name=’EMP’;

INDEX_NAME INDEX_TYPE VISIBILIT

——————– ———- ———

BM_SCOTT_EMP_JOB BITMAP VISIBLE

PK_EMP NORMAL VISIBLE

SQL>

Make first index invisible.

alter index bm_scott_emp_job invisible;

SQL> alter index bm_scott_emp_job invisible;

Index altered.

SQL>

Now create second index on table scott.emp(job) but this one a b-tree.

create index bt_scott_emp_job on scott.emp(job);

SQL> create index bt_scott_emp_job on scott.emp(job);

Index created.

SQL>

NOTE: the first index must be invisible before you can create the second index. Now see the indexes on table EMP.

SQL> column index_name format a20

column index_type format a10

select index_name, index_type, visibility from user_indexes where table_name=’EMP’;SQL> SQL>

INDEX_NAME INDEX_TYPE VISIBILIT

——————– ———- ———

BM_SCOTT_EMP_JOB BITMAP INVISIBLE

BT_SCOTT_EMP_JOB NORMAL VISIBLE

PK_EMP NORMAL VISIBLE

SQL>

This completes index enhancements in Oracle 12c

Larry Catt

OCP

12c, audit

Oracle 12c – Disabling Unified Auditing

March 22, 2016 Larry Catt Leave a comment

Oracle 12c does permit for you to disable unified auditing and use traditional auditing features. This article covers the steps to disabling unified auditing.

Logon to oracle database server and logon to sqlplus with sysdba.

[oracle@linux2 lib]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 15 10:35:07 2015

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

SQL>

Disable any enabled unified audit policies, preventing database from going into mixed mode auditing. You can use the statement: select ‘noaudit policy ‘||policy_name||’;’ from audit_unified_enabled_policies;

SQL> select ‘noaudit policy ‘||policy_name||’;’ from audit_unified_enabled_policies;

‘NOAUDITPOLICY’||POLICY_NAME||’;’

——————————————————————————–

noaudit policy ORA_SECURECONFIG;

noaudit policy ORA_LOGON_FAILURES;

SQL> noaudit policy ORA_SECURECONFIG;

Noaudit succeeded.

SQL>

SQL> noaudit policy ORA_LOGON_FAILURES;

Noaudit succeeded.

SQL>

Verify that no unified audit policies are now enabled by querying the audit_unified_enabled_policy table.

SQL> select count(*) from audit_unified_enabled_policies;

COUNT(*)

———-

SQL>

Shutdown the database with immediate option.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

Exit out of SQLPLUS and shutdown the oracle listener with command: lsnrctl stop

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics, Real Application Testing

and Unified Auditing options

[oracle@linux2 lib]$ lsnrctl stop

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 15-DEC-2015 10:38:36

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.15.98)(PORT=1521)))

The command completed successfully

[oracle@linux2 lib]$

From the command prompt go to directory $ORACLE_HOME/rdbms/lib and use make to execute ins_rdbms.mk uniaud_off ioracle command

cd $ORACLE_HOME/rdbms/lib

make -f ins_rdbms.mk uniaud_off ioracle

Startup the Oracle listener with command: lsnrctl stop

[oracle@linux2 lib]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 15-DEC-2015 10:41:06

Starting /opt/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait…

TNSLSNR for Linux: Version 12.1.0.2.0 – Production

System parameter file is /opt/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora

Log messages written to /opt/app/oracle/diag/tnslsnr/linux2/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.30.15.98)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.15.98)(PORT=1521)))

STATUS of the LISTENER

————————

Alias LISTENER

Version TNSLSNR for Linux: Version 12.1.0.2.0 – Production

Start Date 15-DEC-2015 10:41:06

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /opt/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora

Listener Log File /opt/app/oracle/diag/tnslsnr/linux2/listener/alert/log.xml

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.30.15.98)(PORT=1521)))

Services Summary…

Service “cdb1” has 1 instance(s).

Instance “cdb1”, status UNKNOWN, has 1 handler(s) for this service…

The command completed successfully

[oracle@linux2 lib]$

Logon to SQL*PLUS start the database.

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 15 10:41:32 2015

Connected to an idle instance.

SQL> startup open

ORACLE instance started.

Total System Global Area 549453824 bytes

Fixed Size 2926616 bytes

Variable Size 268437480 bytes

Database Buffers 272629760 bytes

Redo Buffers 5459968 bytes

Database mounted.

Database opened.

SQL>

Verify that the unified Auditing is off by following statement: select value from v$option where parameter=’Unified Auditing’;

SQL> select value from v$option where parameter=’Unified Auditing’;

VALUE

—————————————————————-

FALSE

SQL>

This completes disabling the Unified Auditing in Oracle 12c.

Larry Catt

OCP

12c, CDB, Multitenant, PDB

Root and Multitenant Architecture

March 19, 2016 Larry Catt Leave a comment

Oracle multitenant database architecture gives the ability to virtualize oracle database implementations, similarly to OS virtualization. The multitenant architecture has two main components: The CDB – Container Database and PDB – Pluggable database. These two components have many aspects which help to describe their architecture and are detailed below:

CDB – Container Database

CDB is commonly referred to as the root container.
One root container in any implementation and contains all oracle provided metadata and common user.
CDB$ROOT is the name of the root container.
All PDBs belong to a single root container.
User or application data should never stored in the root.
You can have common users for system administration in root that crosses all PDB in the root container.
One seed PDB exist in all root containers, Oracle supplies this template to create new PDB.

PDB – Pluggable Database

PDB$SEED is the name of the seed PDB in all root container databases.
You cannot add or modify objects in PDB$SEED.
Zero or more user-created PDBs in CDB.
PDBs are entities that contain application data and code for specific features.
PDBs do not contain oracle supplied metadata or common users.
No PDBs exist at CDB creation.
PDBs are uniquely named in a CDB.
PDBs name must be unique across multiple CDB if using the same listener.

Larry Catt

OCP

12c, CDB, Multitenant, PDB

Non-supported features in Oracle 12c Multitenant database (CDB)

March 13, 2016 Larry Catt Leave a comment

As of Oracle 12c release 12.1.0.2 the following features are not supported for use in CDB instances.

Database Change Notification
Continuous Query Notification (CQN)
Client Side Cache
Heat Map
Automatic Data Optimization
Oracle Streams
Oracle Fail Safe
Flashback Pluggable Database (Flashback Database works but will flashback CDB$ROOT including all PDBs)
DBVERIFY
Data Recovery Advisor (DRA)
Flashback Transaction Backout

Larry Catt

OCP

12c, storage, tablespaces

Oracle 12c – Manage database Structures

March 12, 2016 Larry Catt Leave a comment

Management of Oracle database storage structures provides the ability to persistently store data for long term retrieval and recovery processes. The management of this structures can be done both from the command line, via EM Express and EM cloud control. EM Express and EM Cloud control gives the administrator the ability to view, create, and alter various storage structures in the database in a Graphical User Interface through the STORAGE page. Command line option requires that the administrator knows the syntax of DDL statements to perform the same tasks. Oracle data structure of concern of storage are:

Tablespaces: create, drop, add datafiles, and change status of tablespace
Undo Management – shows current status of undo tablespaces and gives validation that it is sized correctly.
Redo Log Groups – allows you to create, drop, add members to redo log groups.
Archive Logs – Allows you view info about archive logs.
Control Files: – see content of control files and allows you to backup control files to trace.

OMF – Oracle Managed Files are intends to simplify the management of files associated with database. When OMF is in use, the administrator specifies a directory for different file types. OMF cannot be used with RAW devices. OMF can be used for the following storage structures:

Tablespaces
Redo log files
control files
archive logs
block change tracking files
Flashback logs
RMAN backups.

OMF does nothing with administrative file like trace files, audit files, alert logs, and core files. OMF requires three initialization parameter to be set

DB_CREATE_FILE_DEST – defines directory where data files and temp files are stored, Also used or redo logs and control files if DB_CREATE_ONLINE_LOG_DEST_n is not defined.
DB_CREATE_ONLINE_LOG_DEST_n – Defines directory for redo log files and control files. You can specify up to 5 multiplex locations.
DB_RECOVERY_FILE_DEST – Define Fast Recovery Area. , Also used or redo logs and control files if DB_CREATE_ONLINE_LOG_DEST_n is not defined.

Larry Catt

OCP

12c, ASM, storage

Oracle 12c – Administer ASM

March 10, 2016 Larry Catt Leave a comment

Oracle Automatic Storage Management (ASM) is a storage solution for Oracle that acts as a volume manager providing file system for exclusive use of the database. When using ASM partitioned disks are assigned to ASM for striping, mirroring, management and ASM implements OMF. ASM has its own database instance which is specifically designed to manage the disks and distribute I/O for performance. The benefits of using ASM are:

Simplifies operations by creating database and managing disk space.
Distributes data across physical disks to provide uniform performance.
Rebalance data automatically after storage change.

An ASM instance uses same basic instance as Oracle database instance with a few exceptions. SGA of ASM is much smaller because it performs fewer activities. Only function of ASM is to mount disk groups and make associate files available to database instances. The logical storage elements of an Oracle ASM instance:

ASM Disks – Storage devices can be physical disks or LUN (Logical Unit Number) from array or network attached file.
ASM Disk Group – Group of ASM Disks managed as a logical unit.
ASM Files – File stored in an Oracle ASM disk group can be data files, control files, online redo logs, other types of files.
ASM Extents – Raw storage used to hold contents of Oracle ASM file. ASM file is one or more file extents and ASM extent is one or more ASM allocation units.
ASM Allocation Units – smallest unit of allocation within a disk group.
ASM Instance – special oracle instance to manage ASM disks. Manages metadata and provides access to database instances.

The files that an ASM instance support are: NOTE ASM does not support trace files, audit files, alert logs, export files, or core files.

Control Files.
Data files
Redo log files
Archive log files.
Temporary files.
Data file backup pieces
Archive log backup pieces.
Persistent initialization parameter files (SPFILE)
Flashback logs
Data Pump dumpset

The file naming conventions in an ASM instance include Fully Qualified Name, Individual Element Names and Alias Names. A Fully qualified file name is created when a new ASM file is created and gives a complete path name in the ASM file system. You use the filly qualified file name for existing Oracle ASM files, except for disk group creation. They have the following format:

+diskgroup/dbname/filetype/filetypetag.file.incarnation

An individual element Name is the name of a single file in the ASM instance. The breakdown of Individual elements of file name includes:

+diskgroup – the disk group name preceded by a plus sign. plus sign(+) means root directory of ASM instance.
dbname – DB_UNIQUE_NAME of database owning file.
filetype – Oracle file type.
filetypetag – Type specific info about the file.
incarnation – file/incarnation pair ensure uniqueness.

Example of ASM filename.

+data/orcl_db/controlfile/current.1234.1234221

File creation request do not give full name, it gives alias or disk group name. If Alias is given, the ASM instance will create it and point at full name. ASM can take single or multiple file create requests at the same time.

Aliases can be used to reference existing ASM file or create new ones. Alias name consist of a Disk Group name proceeded by a plus sign (+), a slash (/), and a sting of characters. Aliases must include the Disk Group name. They cannot exist in root (+) level. When a file is created with alias, both are recorded by ASM and you can access by both. Alias names cannot end in dotted pair of numbers. Example of Alias names

+data/orcldb/control_file_main
+data/orcldb/control_file_backup
+flashback/recover/backup1.dbf

Example of tablespace datafile creation with alias name:

Create tablespace data_one datafile ‘+data/orcldb/data_01.dbf’ size 100m;

If an alias is used, it is not an OMF file and will not be automatically deleted when tablespace is dropped. You must manually delete it as shown below.

Alter diskgroup data drop file ‘+data/orcldb/data_01.dbf’;

Larry Catt

OCP

12c, CDB, listener, network, Oracle

Configure listener.ora for CDB

March 6, 2016 Larry Catt Leave a comment

With Oracle 12c multitenant databases connection is still normally performed through the Oracle networking files of listener.ora, tnsnames.ora, and sqlnet.ora. This articles shows the configuration of listener.ora file to support connection to a CDB database called cdb1.

Logon to your Oracle Server as the oracle software owner.

[larry@linux2 ~]$ su – oracle

Password:

Last login: Mon FEB 24 09:57:56 EDT 2016 on pts/0

[oracle@linux2 ~]$

Create the file listener.ora with the following statement:

echo -e “SID_LIST_LISTENER =\n” \

” (SID_LIST =\n” \

” (SID_DESC =\n” \

” (SID_NAME = “$ORACLE_SID”)\n” \

” (SERVICE_NAME = “$ORACLE_SID”)\n” \

” (GLOBAL_DBNAME = “$ORACLE_SID”)\n” \

” (ORACLE_HOME = “$ORACLE_HOME”)\n” \

” )\n” \

“\nLISTENER =\n” \

” (DESCRIPTION_LIST =\n” \

” (DESCRIPTION =\n” \

” (ADDRESS = (PROTOCOL = TCP)(HOST = “`hostname -i`”)(PORT = 1521))\n” \

” ))\n” \

“\nADR_BASE_LISTENER = /opt/app/oracle\n” \

“\nLOGGING_LISTENER=ON\n” \

“\nTRACE_LEVEL_SERVER=SUPPORT\n” \

“\nSQLNET.INBOUND_CONNECT_TIMEOUT=240\n” >> $ORACLE_HOME/network/admin/listener.ora

Execution example:

[oracle@linux2 admin]$ echo -e “SID_LIST_LISTENER =\n” \

> ” (SID_LIST =\n” \

> ” (SID_DESC =\n” \

> ” (SID_NAME = “$ORACLE_SID”)\n” \

> ” (SERVICE_NAME = “$ORACLE_SID”)\n” \

> ” (GLOBAL_DBNAME = “$ORACLE_SID”)\n” \

> ” (ORACLE_HOME = “$ORACLE_HOME”)\n” \

> ” )\n” \

> “\nLISTENER =\n” \

> ” (DESCRIPTION_LIST =\n” \

> ” (DESCRIPTION =\n” \

> ” (ADDRESS = (PROTOCOL = TCP)(HOST = “`hostname -i`”)(PORT = 1521))\n” \

> ” ))\n” \

> “\nADR_BASE_LISTENER = /opt/app/oracle\n” \

> “\nLOGGING_LISTENER=ON\n” \

> “\nTRACE_LEVEL_SERVER=SUPPORT\n” \

> “\nSQLNET.INBOUND_CONNECT_TIMEOUT=240\n” >> $ORACLE_HOME/network/admin/listener.ora

Now start the listener with the command: lsnrctl start

[oracle@linux2 ~]$ lsnrctl start

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 24-FEB-2016 10:58:06

Starting /opt/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait…

TNSLSNR for Linux: Version 12.1.0.2.0 – Production

System parameter file is /opt/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora

Log messages written to /opt/app/oracle/diag/tnslsnr/linux2/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.30.15.75)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.15.75)(PORT=1521)))

STATUS of the LISTENER

————————

Alias LISTENER

Version TNSLSNR for Linux: Version 12.1.0.2.0 – Production

Start Date 24-FEB-2016 10:58:06

Uptime 0 days 0 hr. 0 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /opt/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora

Listener Log File /opt/app/oracle/diag/tnslsnr/linux2/listener/alert/log.xml

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.30.15.75)(PORT=1521)))

Services Summary…

Service “cdb1” has 1 instance(s).

Instance “cdb1”, status UNKNOWN, has 1 handler(s) for this service…

The command completed successfully

[oracle@linux2 ~]$

You can test the listener configuration by suppling the tnsping utility with the <machine_IP>:<Port>/<service_name> as below:

[oracle@linux2 admin]$ tnsping 10.30.15.75:1521/cdb1

TNS Ping Utility for Linux: Version 12.1.0.2.0 – Production on 24-FEB-2016 11:04:40

Used parameter files:

Used HOSTNAME adapter to resolve the alias

Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=cdb1))(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.15.75)(PORT=1521)))

OK (10 msec)

[oracle@linux2 admin]$

This completes configuration of listener.ora file for CDB.

Larry Catt

OCP

Catt Solutions

Monthly Archives: March 2016

Oracle 12c – Use Index enhancements

Oracle 12c – Disabling Unified Auditing

Root and Multitenant Architecture

Non-supported features in Oracle 12c Multitenant database (CDB)

Oracle 12c – Manage database Structures

Oracle 12c – Administer ASM

Configure listener.ora for CDB

Oracle tips and tricks.