Category Archives: 12c

12c, backup/recovery, RMAN

Oracle 12c -RMAN Backup Overview

Leave a comment

Oracle provides the Recovery Manager utility (RMAN) to perform two types of database backups: Image Backup and Backup Set.    RMAN command BACKUP AS COPY command creates an image copy or bit for bit copy of the data files, archived redo log and controlfiles.

 

RMAN> BACKUP AS COPY DEVICE TYPE DISK DATABASE;

 

You can set the default backup to image copy with CONFIGURE DEVICE TYPE command

 

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COPY;

 

Backup sets are the other type of RMAN backups, each backup set has one or more binary files called backup pieces, in proprietary format that can be restored by RMAN.  You can limit the size of size of backup pieces by the MAXPIECESIZE option.   Use the RMAN command BACKUP AS BACKUPSET to create backup copy of database.

 

RMAN> BACKUP AS BACKUPSET DATABASE.

 

You can set the default backup to backup set with the CONIFGURE DEVICE TYPE command.

 

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET;

 

You can backup the entire database with the command:

 

RMAN> BACKUP DATABASE;

 

Add PLUS ARCHIVELOGS clause will cause a log switch that allows for full media recovery to the point of starting the backup.

 

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

 

Incremental Backups are part of a backup set that only contains changes to the database since the last full backup.   The benefit of an incremental backup is size of the backup and time required to tack the backup.   Incremental Backups are only good if combined with the last full backup and any incremental backup between the last full backup.

 

You can create an incremental backup with the RMAN command BACKUP INCREMENTAL in three ways

 

Level 0 – Identical to full back

Level 1 Differential – backups all changed blocks since most recent incremental backup.

Level 1 Cumulative – backups all changed block since most recent Level 0 incremental backup

 

Restoring incremental backup use level 0 at start and apply level 1 cumulative or all differential.

 

Example of incremental Level 1:

 

RMAN> backup incremental level 1 cumulative database;

 

RMAN> backup incremental level 1 database;

 

 

Larry Catt

OCP

12c, Cloud Control, EM, EM Express

EM Express 12c Security Page

Leave a comment

Oracle EM Express Security Page provides the ability to administrate the Users, Roles, and Profile requirements of an Oracle database.  This article covers the general layout of EM Express Security Page and its functional usage.

 

  1. Logon the EM Express as an administrative user.   In this case we will use the URL:  https://10.30.15.63:5500/em.   This indicates the EM Express is installed on server 10.30.15.63, listening on port 5500 (which is the default).  NOTE:  You may receive a certificate warning but select continue to site option.   Enter your sysdba credentials at the sign in page and mark the check box as sysdba, normally this is the user name and password you used to create the database.

  1. From the top level drop down memory you have four options: Configuration, Storage, Security and Performance.

  1. The Security drop down you have the options: Users, Roles, and Profiles.

  1. The Users option allows you to view all users of the database, add/drop users, modify the permissions of a user and perform any maintenance tasks to a user account.

  1. The Roles option allows you to view all defined database roles, add/drop roles, add permissions to roles, and perform any maintenance tasks to existing roles.

  1. The Profiles option allows you to view all defined profiles of the database, add/drop profiles, modify profile parameters and perform any additional profile administration required.

 

This article summarized the feature and use of the Security Page of Oracle 12c EM Express.

 

Larry Catt

OCP

12c, Performance

Oracle 12c – Steps in SPA analyzing performance change

Leave a comment

SQL Performance Analyzer (SPA) allows you to evaluate a set of SQL statements against known workloads.  This set of SQL statements with execution statistics and execution context is known as an SQL Tuning set.    A single SQL Tuning set can be adjusted with updated SQL statements to test various evolutions of the same process.   Additionally, you can use cursor cache and AWR snapshots to track performance.  Using SQL TUNING SET enables you to:

 

  1. Store SQL text and auxiliary info in single persistent database objects.
  2. Capture Insert, update, delete, and select SQL statements in the SQL TUNING SET.
  3. Load and merge content from different data sources like AWR and cursor cache.
  4. Export SQL TUNING SET where SQL workload is captured and import into other systems.
  5. Reuse SQL workload as input source for other advisors like SQL Tuning Advisor and SQL Access Advisor.

 

The Views and  package that relate to SPA are:

DBA_ADVISOR_TASKS – Info about SPA tasks created.

DBA_ADVISOR_EXECUTIONS – info about SPA tasks executed.

DBA_ADVISOR_FINDINGS – info about SPA finding, generate the following reports:

  • Performance regressions problems.
  • Changing structure of execution plan.
  • Errors like non-existence of objects.
  • Info messages.

DBA_ADVISOR_SQLPLANS – list of execution plans.

DBA_ADVISOR_SQLSTATS – list of SQWL compilations and execution statistics.

V$ADVISOR_PROGRESS – displays operation progress of SPA.

Functions

DBMS_SQLPA .CREATE_ANALYSIS_TASK – This function create analysis tasks for single SQL statements from SQL text, AWR, or cursor cache.  Also create analysis task from SQL Tuning set.

DBMS_SQLPA .EXECUTE_ANALYSIS_TASK – This function executes defined analysis tasks.

DBMS_SQLPA .REPORT_ANALYSIS_TASK – This function displays results of executed tasks.

 

 

Steps in Performing Performance Analysis:

 

  1. Capture SQL workload to analyze and store it in a SQL tuning set.
  2. If using test system it should be as close to production as possible and transfer SQL Tuning Set.
  3. On test system create SPA task
  4. Build pre-change SQL trails by executing the SQL statements store in the SQL tuning set.
  5. Perform the system change.
  6. Build post-change SQL trial by re-executing the SQL statements in SQL tuning set on the post-change test system.
  7. Compare and analyze the pre-change and post-change versions of performance data and generate a report to identify the SLQ statements that have improved, remained unchanged, or regressed after system change.
  8. Tune any regressed SQL statements that are identified.

 

Larry Catt

OCP

12c, backup/recovery, CDB, flashback, Multitenant

Perform Flashback for a CDB in Oracle 12c

Leave a comment

Oracle 12c provides the ability to flashback the database to a previous state.  This procedures demonstrates the use of this process to flashback the database 15 minutes.   NOTE:  Four states must exist for flashback to work:

  1. Must be in archivelog mode.
  2. Database must have flashback on.
  3. Control files must contain flashback logs, thus not recreated.
  4. No tablespaces exist with flashback off set.

 

 

  1. Logon to your Oracle database server as the Oracle software owner and logon to SQLPLUS.

 

[root@linux2 etc]# su – oracle

Last login: Thu Jul  1 09:25:23 EST 2015 on pts/1

[oracle@linux2 ~]$

[oracle@linux2 ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 1 13:47:01 2015

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>

 

  1. Determine SCN of database 15 min ago by using the timestamp_to_scn().

 

SQL> select timestamp_to_scn(sysdate-1/96) from dual;

TIMESTAMP_TO_SCN(SYSDATE-1/96)

——————————

                       5067892

SQL>

 

  1. Now using SCN extracted by the previous step, shutdown the database, startup in mount mode and execute the flashback command.

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

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.

SQL> FLASHBACK DATABASE TO SCN 5067892;

 

Flashback complete.

 

SQL>

 

  1. Startup the database for normal use with resetlogs option and see that all transactions of the last 15 minutes are gone.

 

SQL> alter database open resetlogs;

Database altered.

SQL>

 

 

  1. NOTE: You should take a full backup of the database as soon after flashback as possible to ensure future recovery.

 

  1. This completes the use of Flashback in Oracle 12c.

 

 

Larry Catt

OCP

12c, Performance

Oracle 12c – Implement Application Tuning

Leave a comment

Tuning an application to run efficiently against any version of the Oracle RDBMS is a recursive process which requires continuous monitoring and adjustments.    There are multiple Oracle tools and process to aid in both the monitoring and adjustment process.    This article covers the basic process of Application Tuning.

 

  1. Tune to a problem or performance level – Due not go about changing settings just to see what happens. Rather try to find a problem or bottle-neck in the database and tune to reduce the problem or performance slow down.
  2. Gather Data/statistics – Use diagnostics tools to gather info about problem/slow down. Normally this tools will be related to gathering statistics based against a known baseline.
  3. Make Discrete Changes – Make small exact changes and observe response. Try to make one change at a time, thus you will be able to determine what change helped solve the issue.
  4. Stop – Stop making changes once the problem or bottle-neck has been addressed.
  5. Monitor – Monitor the database and observe things that can be associated with performance problems:
    1. Long waits.
    2. Long held latches and locks.
    3. Long running processes.
    4. High CPU usage.
    5. High I/O
  6. Create new baselines for performance with known workloads. These baselines can be used to truly determine that a perceived database issue is truly abnormal and needs to be addressed.

 

Larry Catt

OCP

12c, OPatch

Oracle 12c – Apply and review patches

Leave a comment

In Oracle 12c, Patching is performed to correct discovered bugs and close security vulnerabilities.   Oracle provides four types of Patches:

Interim Patches – single/collection of bug fixes or one-offs.

Diagnostic Patches – Diagnose or verify a fix.

Patch Set Updates (PSU) – Collection of high impact, low risk, proven fixes for products and components

Critical Patch Updates (CPU) – Collection of patches for security vulnerabilities.

Patching of the RDBMS can be done with two methods: Manually or EM Cloud Control Patch Wizard.   Additionally, the patching processes have added:  Online Patching and Queryable Inventories.   This article will give a brief overview of these Features.

Manual Patching

Step in manually patching are:

  1. Logon to oracle support https://support.oracle.com
  2. Click Patches & Updates tab.
  3. Enter the patch number you are looking for on Patches % Updates page.
  4. Click download on patch for your OS
  5. Click download Patch Metadata.
  6. Review the documentation for specific installation instructions.
  7. Use the OPatch utility to install all patches.

EM Cloud Control Patch Wizard

The EM Cloud Control Patch Wizard has the following benefits over Manual Patching:

  1. Integrate Patch work with Oracle support and provides single view to determine patch needs.
  2. End to End workflow of patching, resolving patch conflicts.
  3. Easy determination of Patch needs for single system.
  4. Patch plans can be saved as templates.
  5. Out of place patching is possible for support of RAC and GRID infrastructure.
  6. EM Cloud allows you create a patch plan to apply patches to one or more targets. A patch can be added to plan only if it’s for same release and OS.

EM Cloud can apply patches with two methods:

  1. Online Mode – EM connects to Oracle Support, searches for necessary patches, resolves any conflicts and adds them to patch plan.
  2. Offline Mode – Used when EM cannot connect to Oracle Support. EM searches for patches manually uploaded to software library and adds them to patch plan.

Online Patches

Before 11g all patches require relink of RDBMS libraries and a shutdown of system.  Now a large number of Patches can be done online, they contain .so files and do not require relink of RDBMS.

Benefit of Online Patches:

  1. No downtime.
  2. persistent across shutdown
  3. Allow rolling patches in RAC
  4. Fast install.

Downside of Online Patches:

  1. Require more memory
  2. Online patches not available for all OS
  3. Not all patches are hot patches.

Queryable Patch Inventory

Queryable Patch inventory is new 12c, implemented with DBMS_QOPATCH package and provides view of patches installed just as OPATCH lsinventory command.    DBMS_QOPATCH queries the OUI Oracle Universal Installer inventory to gather this metadata.

It provides the following:

  1. Query patches installed from SQL prompt.
  2. Creates reports and provides validation across many installs.
  3. Checks patches on RAC from single node.

 

DBMS_QOPATCH components:

GET_OPTACH_BUGS – Provides list of bug fixes per patch number.

GET_OPATCH_COUNT – Provides total number of patches installed.

GET_OPATCH_DATA – Provides top level patch info – number, creation date.

GET_OPATCH_FILES – Provides list of files updated by patch number.

GET_OPATCH_INSTALL_INFO – Provides info such as ORACLE_HOME, patch, inventory_location

GET_OPATCH_LIST – List of Patches installed.

GET_OPATCH_LSINVENTORY – Whole patch inventory

GET_OPATCH_OLAYS –Overlay for specific patch

GET_OPATCH_PREQS – Provides prerequisites for a patch

GET_OPATCH_XSLT – Provides style sheet for opatch XML inventory

GET_PENDING_ACTIVITY – Provides SQL patches applied to single inventory from binaries.

GET_SQLPATCH_STATUS – Patch status from SQL patch registry.

IS_PATCH_INSTALLED – Patches applied info from XML inventory

PATCH_CONFLICT_DETETION – Patch in conflict for give file.

SET_CURRENT_OPINST – inventory information for specific node in RAC.

 

Larry Catt

OCP

12c, Cloud Control, EM, EM Express

EM Express 12c Performance Page

Leave a comment

EM Express 12c Performance Page

 

Oracle EM Express Performance Page provides the ability to administrate the Users, Roles, and Profile requirements of an Oracle database.  This article covers the general layout of EM Express Performance Page and its functional usage.

 

  1. Logon the EM Express as an administrative user.   In this case we will use the URL:  https://10.30.15.63:5500/em.   This indicates the EM Express is installed on server 10.30.15.63, listening on port 5500 (which is the default).  NOTE:  You may receive a certificate warning but select continue to site option.   Enter your sysdba credentials at the sign in page and mark the check box as sysdba, normally this is the user name and password you used to create the database.

  1. From the top level drop down memory you have four options: Configuration, Storage, Security and Performance.

  1. The Performance option allows access to view performance of the database via the Performance Hub and perform performance optimization via the SQL Tuning Advisor.

  1. The Performance Hub option allows you to view the performance of your database based on multiple criteria and via a Summary, Activity, Workload, Monitored SQL, ADDM views. This can provide insight into the optimization of your instance and database configurations.

  1. The SQL Tuning Advisor option allows you to tune individual SQL statements and monitor executing SQL within the database. NOTE:   This option requires additional Tuning Pack license.

This article summarized the feature and use of the Performance Page of Oracle 12c EM Express.

 

Larry Catt

OCP

12c, flashback

Oracle 12c – Flashback Version Query

Leave a comment

Flashback Version Query is used to get metadata and historical data for a particular time interval.  This is done by VERSIONS BETWEEN clause with time interval being either timestamps or SCN using the following pseudo columns:

  • VERSIONS_START
  • VERSIONS_END
  • VERSIONS_XID
  • VERSIONS_OPERATIONS

 

Return columns are from START date and up until (not including) the END date.

 

EXAMPLE:

 

 

  1. Logon to your Oracle 12c database as user scott.

 

[oracle@linux2 flash_recovery_area]$ sqlplus scott/password

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 6 07:33:45 2015

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

Last Successful login time: Wed Jan 04 2015 07:45:52 -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>

 

  1. Create a table fb_scott.

 

SQL> create table fb_test(fname varchar2(30), amt number);

Table created.

 

  1. Select current system timestamp

 

select systimestamp from dual;

SQL> select systimestamp from dual;

SYSTIMESTAMP

—————————————————————————

06-JAN-15 07.44.13.923919 AM -05:00

SQL>

 

  1. Insert values into your new table fb_test and commit insert.

 

insert into scott.fb_test(fname,amt) values(‘Larry’,111);

commit;

SQL> insert into scott.fb_test(fname,amt) values(‘Larry’,111);

commit;

1 row created.

SQL>

Commit complete.

SQL>

 

  1. Now perform 3 update operations on table fb_test and commit between each update.

 

update scott.fb_test set amt=222 where fname=’Larry’;

commit;

update scott.fb_test set amt=333  where fname=’Larry’;

commit;

update scott.fb_test set amt=444 where fname=’Larry’;

commit;

 

SQL> update scott.fb_test set amt=222 where fname=’Larry’;

commit;

update scott.fb_test set amt=333  where fname=’Larry’;

commit;

update scott.fb_test set amt=444 where fname=’Larry’;

commit;

1 row updated.

SQL>

Commit complete.

SQL>

1 row updated.

SQL>

Commit complete.

SQL>

1 row updated.

SQL>

Commit complete.

SQL>

 

  1. Finally select current systimestamp from dual.

 

select systimestamp from dual;

SQL> select systimestamp from dual;

SYSTIMESTAMP

—————————————————————————

06-JAN-15 07.45.55.308034 AM -05:00

SQL>

 

 

  1. Select the past versions of your table fb_test using the versions between clause.

 

select versions_starttime, versions_endtime, versions_xid, versions_operation as op, amt

from scott.fb_test  versions between timestamp to_timestamp (’06-JAN-15 07:44:13′, ‘DD-MON-YY HH24:MI:SS’) and to_timestamp (’06-JAN-15 07:45:55′, ‘DD-MON-YY HH24:MI:SS’) where fname=’Larry’;

 

Results:

 

SQL> select versions_starttime, versions_endtime, versions_xid, versions_operation as op, amt

from scott.fb_test  versions between timestamp to_timestamp (’06-JAN-15 07:44:13′, ‘DD-MON-YY HH24:MI:SS’) and to_timestamp (’06-JAN-15 07:45:55′, ‘DD-MON-YY HH24:MI:SS’) where fname=’Larry’;

  2

VERSIONS_STARTTIME

—————————————————————————

VERSIONS_ENDTIME

—————————————————————————

VERSIONS_XID     O        AMT

—————- – ———-

06-JAN-15 07.45.30 AM

090009006F0B0000 U        444

06-JAN-15 07.45.30 AM

06-JAN-15 07.45.30 AM

02001500AF0B0000 U        333

VERSIONS_STARTTIME

—————————————————————————

VERSIONS_ENDTIME

—————————————————————————

VERSIONS_XID     O        AMT

—————- – ———-

06-JAN-15 07.45.27 AM

06-JAN-15 07.45.30 AM

04000A00680A0000 U        222

06-JAN-15 07.45.12 AM

06-JAN-15 07.45.27 AM

VERSIONS_STARTTIME

—————————————————————————

VERSIONS_ENDTIME

—————————————————————————

VERSIONS_XID     O        AMT

—————- – ———-

080021001F0C0000 I        111

SQL>

 

  1. This completes viewing previous versions of a table in Oracle 12c.

 

Larry Catt

OCP

12c, data pump

Oracle 12c – Migrating Data by Using Oracle Data PUMP

Leave a comment

You can use Data Pump to migrate an existing Oracle database into an empty 12c Database with or without transportable option.  This is known as full transportable export/import if transportable option is in use.  Non-transportable tablespaces like SYSTEM and SYSAUX cannot use transportable option and must use conventional data movement.

 

Considerations when using Data Pump to populate new 12c Oracle database.

  1. You can use Data Pump to do this but oracle recommends upgrading to 11.2.0.3 or higher to ensure that registered options and components are included in export.
  2. When doing this with 11.2.0.3 or higher set VERSION=12 option in data pump export. This is for transportable tablespaces.
  3. For Network-based full transportable imports use parameter FULL=YES, TANSPORTABLE=ALWAYS, and TANSPORT_DATAFILES=datafile_name for source db higher than 11.2.0.3 but lower then 12.1
  4. File-based full transportable imports only require use of TRANSPORT_DATAFILE=datafile_name.
  5. Default Data Pump directory object DATA_PUMP_DIR does not work with PDBs, you have to create an explicit directory object within the PDB for export and import operations.

 

 

Larry Catt

OCP

12c, linux

Part 11 – Building Oracle 12c development suite – Installation of Oracle 12c

Leave a comment

Installation of Oracle 12c
Oracle Corporation provides the ability for developers to create a complete development suite on their personal windows desktop or laptop without requiring the expensive equipment required for a true production system. This article outlines the process for actual installation of Oracle 12c RDBMS Oracle Enterprise Linux 6.5 platform. This procedure assumes that Part 1 through Part 10 of this series has been complete successfully.
NOTE: This process can be used for most Linux installations, regardless of original company or organization providing the base Linux OS.

1. Logon to your Windows desktop/laptop as an administrator and open Oracle VirtualBox.
2. SSH to your OEL virtual server as the oracle software owner.
3. CD to the directory /opt/app/oracle_software/database that was created during part 10 of this series and execute the command ‘cp ./response/db_install.rsp .’

[root@oel65 oracle_software]# cp ./response/db_install.rsp .
[root@oel65 oracle_software]#

4. Open the file db_install.rsp and edit the variable “ORACLE_HOSTNAME” and make ORACLE_HOSTNAME equal to the correct server name for your VM.

5. As the oracle user, change directory to /opt/app/oracle_software/database and execute the command ‘./runInstaller -silent -responseFile /opt/app/oracle_software/database/db_install.rsp’

[root@oel65 database]# ./runInstaller –silent –responseFile /opt/app/oracle_software/database/db_install.rsp
Starting Oracle Universal Installer….
[root@oel65 database]#

6. Change to the user root with the command ‘su -‘ and execute the scripts “/opt/app/oracle_inventory/inventory/orainstRoot.sh” and “/opt/app/oracle/ucop_db/root.sh”

7. This completes the installation of Oracle 12c on your Linux VM.

Larry Catt, OCP