Monthly Archives: February 2016

12c, Administration, Performance

Oracle 12c – Use Flash Cache

Leave a comment

The Database Smart Flash Cache allows an instance to access multiple flash devices without requiring a volume manager.  This feature only works on Solaris and Linux OS.

 

Enabling Smart Flash Cache is a benefit if:

  1. Buffer Pool Advisor of AWR or STATSPACK says to double size of buffer cache.
  2. DB FILE SEQUENTIAL READ is top wait event.
  3. System has spare CPU cycles.

 

Two initialization parameters used to configure Database Smart Flash Cache.

DB_FLASH_CACHE_FILE – list of Paths and files for Database Smart Flash Cache which can be ASM or OS files but must be on a flash device.  If not specified, oracle will create on startup.  Support max of 16 files.

DB_FLASH_CACHE_SIZE – Specifies size of each file, if number of sizes does not match number of files, an error will occur.  Size is specified by nG (ie 10G).  You disable FLASH device by specifying zero for size.  But this is not a dynamic change and restart is required.

 

V$FLASHFILESTAT view can determine latency and read counts for each file.

 

 

Larry Catt

OCP

12c, Administration, backup/recovery, RMAN

Oracle 12c New Feature – RMAN

Leave a comment

Table Level Recovery from backup – 12c now has the ability to recover a single table from an RMAN backup with the user of RECOVER TABLE option.

 

RMAN Command-Line SQL – RMAN utility now allows for the execution of SQL commands against target database without using SQL key word.   RMAN also now support the SQLPLUS DESCRIBE command for tables and views.

 

Active Database Duplication – RMAN supports the ability to clone a source database to an auxiliary database over the network.   Retrieving all backup sets from source database over network and allows for use of block compression during the duplication process.

 

NOOPEN option for Duplicate database creation, which allows for recovered database to not be open with resetlogs before administrative tasks can be performed.

 

duplicate target database

to newdatabase

from active database

noopen;

 

SECTION SIZE option with Image Copies – RMAN now allows you to divide data files into subsections and split upon multiple recovery channels.

 

backup

as copy

section size 500M

database;

 

Incremental Backups can include the SECTION SIZE option which allows for parallel channels to be used, thus reducing the time for backup.

 

backup

incremental level 1

section size 500m

Datafile ‘/u01/oradata/orcl/data01.dbf’;

 

SNAPSHOT with the use of BACKUP mode, 12c allows for the use of VM snapshots or storage snapshot without placing database in backup mode.   The snapshot requires 3 things for use:

  1. Database is consistent at time of snapshot.
  2. Write order is maintained for snapshot.
  3. Snapshot stores time of snapshot.

 

SNAPSHOT TIME option has become part of RECOVER command which allows for recovery from OS snapshot to a consistent point.

 

 

Larry Catt

OCP

 

12c, ASM

Oracle 12c – Manage ASM instance

Leave a comment

This article covers the basic management of an ASM instance for database storage.

 

Starting an ASM Instance

  1. You must set the ORACLE_SID variable to ASM SID.
  2. Default ASM SID is +ASM and for RAC is +ASM{node number}.
  3. The ORACLE_HOME variable must be set to Grid infrastructure home where Oracle ASM is installed.
  4. Initialization parameter file must contain text: INSTANCE_TYPE = ASM
  5. When you run STARTUP command, the ASM instance mounst the ASM disk groups.

 

ASM SQL*plus STARTUP command is options interpreted as:

  1. FORCE – Issues SHUTDOWN ABORT to ASM instance before restarting it.
  2. MOUNT or OPEN – Mounts the diskgroups in init parameter ASM_DISKGROUPS. This is the default.   An OPEN state for ASM instance really does not exist, if supplied it is treated as MOUNT.
  3. NOMOUNT – Starts ASM Instance without mounting diskgroups.
  4. RESTRICT – Starts up an instance in restricted mode. Have to have both CREATE SESSION and RESTRICTED SESSION privileges to connect.

 

SYSASM database and OSASM OS privileges gives storage permissions with granting high-level to database itself.  Users can be created in ASM instance and given SYSASM privilege.  OSASM system privilege does same at OS layer.

 

Example of creating a user with SYSASM to new user:

 

$ export ORACLE_SID = + ASM

$ sqlplus / as sysasm

 

CREATE USER asm_admin IDENTIFIED by badpassword_nobiscuit;

User created.

SQL > GRANT SYSASM TO asm_admin;

 

SQLPLUS /NOLOG

SQL > CONNECT asm_admin AS SYSASM

Enter password: badpassword_nobiscuit

Connected to an idle instance.

 

SQL > STARTUP

ASM instance started

Total System Global Area 71303168 bytes

Fixed Size 1069292 bytes

Variable Size 45068052 bytes

ASM Cache 25165824 bytes

ASM disk groups mounted

 

 

Shutting Down an Oracle ASM Instance.

  1. ASM instance uses the SQL*Plus shutdown command and you must set the ORACLE_SID variable to ASM SID.
  2. Before shutting down an ASM instance you should shutdown all databases using it, if not they will be shutdown with abort option upon ASM shutdown.
  3. Before shutting down an ASM instance you should dismount all Dynamic Volume Manager volumes.
  4. example of shutdown.

 

SQLPLUS /NOLOG

SQL>  CONNECT asm_user as sysasm

Enter password: *******

Connected.

SQL> SHUTDOWN NORMAL

 

 

SHUTDOWN Options in ASM:

  1. NORMAL – instance waits for all in-progress SQL to complete, dismounts disk groups and shuts down. Instance also waits for all currently connected users to disconnect from instance.  Any database instance connected, will cause the command to return an error and will not shutdown.   NORMAL is default shutdown.
  2. IMMEDIATE or TRANSACTIONAL – instance waits for all in-progress SQL to complete before dismounting all disk groups and shutting down ASM instance. It does not wait for current users to disconnect.  If any database instances are connected to ASM, will cause the command to return an error and will not shutdown.
  3. ABORT – ASM immediately shuts down without dismounting disk groups. This will cause recovery on next ASM startup.  All databases connected to ASM instance will also perform shutdown abort.

 

 

Larry Catt

OCP

12c, CDB, Multitenant, PDB

Methods to Migrate stand-alone database to PDB

Leave a comment

12c release has given us the ability to virtualize databases.   These procedure outlines three methods to move a stand-alone database to a 12c PDB.   Each of these methods have both positive and negatives aspects.  Careful thought, analysis and some testing should be performed before deciding which method is best for your situation.

 

 

Method 1 – DBMS_PDB package.

DBMS_PDB package is provided with Oracle 12c RDBMS release.  It provides multiple procedures for checking, preparing, creating conversions, and cleaning up after movement of standalone database to PDB.

 

Pros:

  1. Removes guess work from conversion process.
  2. No coding on administrators part.
  3. No need to create PDB before procedure.

 

Cons:

  1. No ability to transform data elements.

 

Method 2 – Data Pump

Use the EXP/IMP to extract metadata and data from standalone database into PDB.

 

Pros:

  1. EXP/IMP utility of data pump are well known.
  2. Able to do inclusive/exclusive data movement.
  3. Able to start and stop.

 

Cons:

  1. Have to build CDB and PDB for destination.
  2. Can requires disk space for exports.
  3. Can be very time consuming.

 

Method 3 – Goldengate / ETL technology

Use Goldengate or other ETL tool to move data from existing standalone into PDB.

 

Pros:

  1. Can be done online with minimal down time.
  2. Is extremely flexible.
  3. Able to start and stop.
  4. Able to transform data during process.

 

Cons:

  1. Have to build CDB and PDB for destination.
  2. Expensive licensing cost.
  3. Can be time consuming.

 

 

 

Larry Catt

OCP

12c, backup/recovery, Multitenant, PDB, RMAN

RMAN Backup of PDB in Oracle 12c Multitenant Database

Leave a comment

RMAN backup of a PDB in Oracle 12c Multitenant Database can be performed from the Recovery Manager utility or EM Cloud Control.    This procedure shows the steps of performing a PDB backup from RMAN utility.

 

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

 

[root@linux2 larry]# su – oracle

Last login: Thu Jan  3 11:02:25 EDT 2016 on pts/1

[oracle@linux2 ~]$

 

  1. Enter the RMAN utility.

 

[oracle@linux2 ~]$ rman

 

Recovery Manager: Release 12.1.0.2.0 – Production on Tue Jan 15 12:26:22 2016

 

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

 

RMAN>

 

 

  • You can now either connect to the CDB and issue BACKUP PLUGGABLE DATABASE command or connect directly to PDB and use BACKUP DATABASE

 

  1. Connect to your target CDB database with SYSBACKUP privilege.

 

RMAN> connect target ‘”sys/@cdb1 as sysbackup”‘

 

target database Password:

connected to target database: CDB1 (DBID=898165058)

 

RMAN>

 

  1. From the CDB issue BACKUP PLUGGABLE DATABASE
    • Single PDB backup

 

RMAN>  BACKUP PLUGGABLE DATABASE PDB1;

 

  • Or multiple PDB backup

 

RMAN>  BACKUP PLUGGABLE DATABASE PDB1, PDB2;

 

  1. Additionally you can connect directly to the PDB. To do this you must connect as a common user with sysdba or sysbackup privilege,  in this example we are using C##DBA.

 

RMAN> connect target ‘”c##dba/@pdb1 as sysdba”‘

 

target database Password:

connected to target database: CDB1 (DBID=898165058)

 

RMAN> backup database;

 

 

  1. This completes backup of PDBs in the Oracle 12c Multitenant Database.

 

 

 

Larry Catt

OCP

12c, CDB, linux, Multitenant, PDB

Configure Linux for multiple Oracle Database connections

Leave a comment

You can have multiple database on a single server of any platform.    The key to this configuration is have the appropriate environmental parameter for the database you which to attach to.   This procedure demonstrates the setup of .bash_profile file to set the appropriate OS environmental parameters upon attachment as the Oracle software owner.  NOTE: This procedure was done on a Oracle Enterprise Linux server.

 

  1. Logon to your database server as the root user.

 

[larry@linux2 ~]$ su –

Password:

Last login: Wed Jan  7 08:49:18 EST 2016 on pts/1

[root@linux2 ~]#

 

  1. List the current databases running on your database server with command: ps –ef|grep smon

 

[root@linux2 ~]# ps -ef|grep smon

oracle    3793     1  0 08:47 ?        00:00:00 ora_smon_cdb1

oracle    6454     1  0 09:38 ?        00:00:00 ora_smon_orcl

root     15059 15008  0 13:44 pts/1    00:00:00 grep –color=auto smon

[root@linux2 ~]#

 

  1. From the ‘ps’ command, we can see there are two active database instance on this server: CDB1 and ORCL.   We need to adjust the .bash_profile file in the oracle software owner home directory to choose between these two instances at logon.
  2. Go to the home directory of your oracle software owner.

 

[root@linux2 ~]# cd /home/oracle

[root@linux2 oracle]#

 

  1. Now normal install of multiple databases on a single server will all use the same Oracle binaries, thus the only thing which will be different between the environmental variables will be the ORACLE_SID value. If something else is different, you can adjust the shell script below to allow for more variable definition.   In this example attach the following to end of the .bash_profile file with vi.

 

## Beginning of request for information

database=””

while [ -z “$db” ]

do

   echo “Enter database to use:”

   echo “1 – ORCL       “

   echo “2 – CDB1       “

   echo “Option > “

read db

   if [[ “$db” != “1” &&

         “$db” != “2” ]]

   then

      echo “$db” is not valid >&2

      db=””

   fi

done

## Ending of request for information </strong>

 

 

 

##Beginning of local variable definitions

if [[ $db = “1” ]]

then

   export ORACLE_SID=orcl

elif [[ $db = “2” ]]

then

   export ORACLE_SID=cdb1

else

   echo “Invalid options “

fi

## Ending of local variable definitions

 

  1. Now logon as the oracle software owner and you will be prompted to give 1 for orcl instance or 2 for cdb1. Any other options will produce nothing.

 

Choosing option 1 example:

 

[root@linux2 oracle]# su – oracle

Last login: Wed Jan  7 14:02:23 EST 2016 on pts/1

Enter database to use:

1 – ORCL

2 – CDB1

1

[oracle@linux2 ~]$ echo $ORACLE_SID

orcl

[oracle@linux2 ~]$

 

 

 

 

 

Choosing option 2 example:

 

[root@linux2 oracle]# su – oracle

Last login: Wed Jan  7 14:04:51 EST 2016 on pts/1

Enter database to use:

1 – ORCL

2 – CDB1

2

[oracle@linux2 ~]$ echo $ORACLE_SID

cdb1

[oracle@linux2 ~]$

 

 

Choosing option other than 1 or 2 example:

 

[root@linux2 oracle]# su – oracle

Last login: Wed Jan  7 14:12:24 EST 2016 on pts/1

Enter database to use:

1 – ORCL

2 – CDB1

Option >

3

3 is not valid

Enter database to use:

1 – ORCL

2 – CDB1

Option >

4

4 is not valid

Enter database to use:

1 – ORCL

2 – CDB1

Option >

 

 

 

  1. This completes setup of Linux for multiple Oracle Databases.

 

 

Larry Catt

OCP

 

12c, backup/recovery

Oracle 12c – Disable Flashback Database

Leave a comment

This procedure outlines the steps to disable flashback on an oracle 12c database.

 

 

  1. Logon to server as the oracle software owner.

 

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

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

mylinux#

 

 

  1. Open the oracle database in mount mode.

 

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. Disable flashback for database with ALTER DATABASE command.

 

SQL> alter database flashback off;

Database altered.

SQL>

 

  1. Open the database.

 

SQL> alter database open;

Database altered.

SQL>

 

 

  1. This completes disabling flashback in Oracle 12c database.

 

Larry Catt

OCP