Monthly Archives: May 2016

12c, Performance, storage, tablespaces

Oracle 12c – Manage VLDB

Leave a comment

Very Large Databases use partitioning to improve the ability to support large data sets.  Breaking large tables into smaller manageable chunks.  This allows for partition pruning, which prevents access to partitions which will not have data queried.  Partitioning and indexing work together to provide parallel processing to search multiple partitions at same time as in data ware housing.

 

Partitioning Methods: 

You can partition data into a single level or two level for a composite partition.  How the data is accessed determines the best implementation method.   Regardless of partitioning method, you have three ways to control distributing of partitions:

  1. Range
  2. Hash
  3. List

 

Single Level Partitioning

One or more of the columns acts as the partition key spitting the table into multiple sections.

  1. Range Partitioning – maps data to a range of values in the partition key. Each row is identified with a mapping to partition key.   This is the most common partitioning method and is commonly used with dates.  Partition is created with a VALUES LESS THEN clause which show the upper bound of a date column.  The highest partition can use a MAXVALUE to provide an upper limit.
  2. Hash Partitioning – This method uses a hashing algorithm to map data to partitioning key. Used to evenly spread data among partitions. It is easier to implement then range.  Good to use if no date column exists and no obvious partitioning key exists.
  3. List Partitioning – Allows you to provide a list of values for partitioning key column and divides data base on list. You control exactly how rows are mapped to partitions.  You list a set of values to be placed in separate partitions and use the DEFAULT clause to specify a partition for any value not in the list.

 

Composite Partitions

This combines the basic partitioning methods into multiple sub-partitions of the same method or different methods.  Provides for a finer level of sub-division of data.   Possible composite partitions are:

  • Range-Range
  • Range-Hash
  • Range-List
  • List-Range
  • List-Hash
  • List-List
  • Hash-Hash

 

 

Larry Catt

OCP

12c, CDB, Multitenant, PDB

Startup CDB

Leave a comment

In Oracle 12c multitenant databases consist of a container database CDB which contain one or more pluggable databases PDB.   Startup of the CDB can be done with none of the PDBs it contains being started.   This procedures covers the startup of a CDB.

 

  1. Logon to your server as the oracle software owner.

 

[larry@linux2 ~]$ su – oracle

Password:

Last login: Mon Apr 24 14:55:04 EDT 2016 on pts/1

[oracle@linux2 ~]$

 

  1. No CDB in non-RAC system acts the same as any Oracle single instance database with three startup modes: NOMOUNT, MOUNT, and OPEN. Please see related article about modes.   You can check for running CDB instances from the OS layer by searching for the SMON process.   This does not show what startup mode the instance is in.

 

[oracle@linux2 ~]$ ps -ef|grep smon

oracle    2660  2395  0 09:34 pts/1    00:00:00 grep –color=auto smon

oracle    3849     1  0 Apr24 ?        00:00:02 ora_smon_cdb1

[oracle@linux2 ~]$

 

This shows that there is one instance started in some mode called cdb1.   In this example our CDB database is cdb1.    NOTE:   You cannot validate any PDB in this manner.   PDBs are services within a CDB and cannot be viewed via OS layer.

 

  1. Since this CDB is up and running, we will first demonstrate the shutdown process which is identical to non-CDB databases in single instance mode with three options: IMMEDIATE, ABORT, NORMAL.  Please see related articles about modes.   You have to connect to CDB as the SYSDBA privileged user to perform shutdown as shown below.

 

[oracle@linux2 ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 12.1.0.2.0 Production on Tue Apr 25 09:39:29 2016

Copyright (c) 1982, 2014, Oracle.  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

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

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

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

[oracle@linux2 ~]$

 

 

  1. Now to startup the CDB, again you have to connect with SYSDBA privileged user, as shown below. Open is the default mode.

 

[oracle@linux2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Apr 25 09:41:06 2016

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

Connected to an idle instance.

 

SQL> startup open

ORACLE instance started.

Total System Global Area  734003200 bytes

Fixed Size                  2928728 bytes

Variable Size             524292008 bytes

Database Buffers          201326592 bytes

Redo Buffers                5455872 bytes

Database mounted.

Database opened.

SQL>

 

  1. Note you have to startup the listener process to enable oracle networking, but as far as the CDB is concerned, the database is ready for use.

 

  1. This completes startup of CDB in Oracle 12c.

 

 

Larry Catt

OCP

12c, backup/recovery, flashback, RMAN

Oracle 12c – Enabling Flashback Database

Leave a comment

In Oracle 12c Flashback Database must be configured which allow for tracking of changes for the database to revert to.   This article details the steps in enabling flashback database feature.

 

 

  1. Logon to server as the oracle software owner.

 

[root@orclc-db1 devadmin]# su – oracle

Last login: Mon Feb 19 18:31:43 UTC 2016 on pts/0

mylinux#

 

  1. Open or mount the oracle database.

 

mylinux# sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Feb 20 12:41:43 2016

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

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 5.1540E+10 bytes

Fixed Size                  2938792 bytes

Variable Size            3.4628E+10 bytes

Database Buffers         1.6777E+10 bytes

Redo Buffers              131276800 bytes

Database mounted.

SQL>

 

  1. Set the DB_FLASHBACK_RETENTION_TARGET initialization parameter to time period of recovery, default is 1440 minutes or 1 day.

SQL> alter system set DB_FLASHBACK_RETENTION_TARGET=2880 scope=both;

System altered.

SQL>

 

  1. Set the initialization parameter DB_RECOVERY_FILE_DEST_SIZE to a suitable size. NOTE:  This is simple a test system so we area setting the recovery size to 4GB,  a real system will require a larger size.

 

SQL> alter system set db_recovery_file_dest_size = 4g scope=both;

System altered.

 

  1. Set the initialization parameter DB_RECOVERY_FILE_DEST to a suitable large enough space one disk.

 

SQL> alter system set db_recovery_file_dest=’ /u04/oradata/ORCL/flash_recovery_area’ scope=both;

System altered.

SQL>

 

  1. Ensure that archivelog mode is enabled and enable if not.

 

SQL> archive log list

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            /opt/app/oracle/orcl_db/dbs/arch

Oldest online log sequence     1613

Current log sequence           1633

 

  1. Archiving log is not currently enabled, restart the database in mount mode and enable archiving.

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

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.

SQL> alter database archivelog;

 

Database altered.

 

SQL>

 

  1. Enable flashback for database with ALTER DATABASE command, before opening database.

 

SQL> alter database flashback on;

Database altered.

SQL>

 

  1. Alter the database to open.

 

SQL> alter database open;

Database altered.

SQL>

 

  1. This completes enabling flashback for your Oracle RDBMS.

 

Larry Catt

OCP

 

12c, CDB, network

Configure tnsnames.ora file for CDB

Leave a comment

Oracle 12c multitenant database normally uses the same oracle networking files as other Oracle RDBMS implementations.    This article covers the configuration of the tnsnames.ora file for connection to a CDB database cdb1.

 

  1. Connect to your oracle database server as the oracle software owner.

 

[larry@linux2 ~]$ su – oracle

Password:

Last login: Mon Apr 24 10:45:22 EDT 2016 on pts/1

[oracle@linux2 ~]$

 

  1. You can use the following code to create the appropriate tnsnames.ora entries in the file $ORACLE_HOME/network/admin/tnsnames.ora or manually insert into the file knowing the machine IP, Port, SID, and service name.

 

echo -e “CDB1 =\n” \

”  (DESCRIPTION =\n” \

”    (ADDRESS_LIST =\n” \

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

”    )\n” \

”    (CONNECT_DATA =\n” \

”      (SERVER = DEDICATED)\n”  \

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

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

”    )\n” \

”  )\n” >> $ORACLE_HOME/network/admin/tnsnames.ora

 

Execution Example:

 

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

> ”  (DESCRIPTION =\n” \

> ”    (ADDRESS_LIST =\n” \

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

> ”    )\n” \

> ”    (CONNECT_DATA =\n” \

> ”      (SERVER = DEDICATED)\n”  \

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

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

> ”    )\n” \

> ”  )\n” >> $ORACLE_HOME/network/admin/tnsnames.ora

[oracle@linux2 admin]

 

 

  1. After execution or editing, you can view the contains of the tnsnames.ora file with the cat command as shown below:

 

 

oracle@linux2 admin]$ cat tnsnames.ora

CDB1 =

   (DESCRIPTION =

     (ADDRESS_LIST =

       (ADDRESS = (PROTOCOL = TCP)(HOST = 10.30.15.75)(PORT = 1521))

     )

     (CONNECT_DATA =

       (SERVER = DEDICATED)

       (SERVICE_NAME = cdb1)

       (SID = cdb1)

     )

   )

 

  1. As long as the listener process and database is up and running, you can test the connection through the tnsping utility by suppling the reference name in the tnsnames.ora file. In this example the reference name is CDB1, as shown below.

 

[oracle@linux2 ~]$ tnsping CDB1

 

TNS Ping Utility for Linux: Version 12.1.0.2.0 – Production on 24-APR-2016 11:49:44

 

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

 

Used parameter files:

 

 

Used TNSNAMES adapter to resolve the alias

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

OK (0 msec)

[oracle@linux2 ~]$

 

 

  1. This completes setup of tnsnames.ora file for CDB connection in Oracle 12c.

 

Larry Catt

OCP