Migrating SQL Profiles across databases

We were in middle of R12 upgrades and during the upgrade in our test instances, we performed some SQL tuning through OEM and implemented several SQL profiles to improve the SQL query performance.

I was tasked to migrate those profiles from one test instance to another and eventually to production database during the upgrade.

Following are the steps followed:

1. Create the staging table to move the SQL profiles and move the required profiles or all the profiles at once. I used the following script to stage all the SQL profiles into the staging table.

exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name=>'SQL_PROFILES_TT',schema_name=>'APPS');
truncate table apps.SQL_PROFILES_TT;

PROMPT ROWS IN THE SQL PROFILES DICTIONARY TABLE...

SELECT count(*) from dba_sql_profiles;

PROMPT LOADING PROFILES INTO THE STAGING TABLE...

declare
sql_prof_name dba_sql_profiles.name%TYPE;
cursor c1 is
SELECT NAME FROM DBA_SQL_PROFILES order by created;
begin
        open c1;
        loop
                fetch c1 into sql_prof_name;
                exit when c1%NOTFOUND;
                DBMS_SQLTUNE.PACK_STGTAB_SQLPROF (staging_table_name => 'SQL_PROFILES_TT',profile_name=>sql_prof_name,STAGING_SCHEMA_OWNER=>'APPS');
        end loop;
        close c1;
end;
/
PROMPT ROWS IN THE STAGING TABLE ....
select count(*) from apps.SQL_PROFILES_TT;
2. Export the staging table using the traditional export/EXPDP commands. In my case, I chose to use the traditional export and used the following parfile.

exp parfile=export_profiles.par

Parameter file:

userid="/ as sysdba"
file=<dump file name>
log=<log file name>
tables=apps.SQL_PROFILES_TT
3. Move the export file to the target database server and import the SQL profiles.

imp parfile=import_profiles.par

Parameter file:

userid="/ as sysdba"
file=<dump file name>
log=<log file name>
fromuser=apps
touser=apps
ignore=y
4. Connect as sysdba in target database and execute the following command to implement the sql profiles:

EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF(REPLACE => TRUE,staging_table_name => 'SQL_PROFILES_TT',STAGING_SCHEMA_OWNER=>'APPS');

Dataguard Maintenance using DGMGRL Commands...Continuation from previous post.

In my earlier post on DG setup using OEM 12c, I have mentioned that the DG Instance have to act as reporting database whole day except for few hours when the DG instance is converted to recovery mode and have the redo logs applied and then converted back to read-only mode.

We have accomplished this through Dataguard Broker (DGMGRL) commands and scheduled them through OEM 12c.

As we were using Windows platform, I created these scripts as a batch program and scheduled them as an EM job and made sure that we will get notifications in case if there are any problems with the job.

Script to convert to recovery mode:

set ORACLE_SID=ASHGBADS
set ORACLE_HOME=<Your Oracle home path>
set PATH=%ORACLE_HOME%\bin;%path%
%ORACLE_HOME%\bin\dgmgrl -silent / "edit database 'ASHGBADS' set state= offline";
%ORACLE_HOME%\bin\dgmgrl -silent / "startup mount;"
ping -w 1000 -n 120 1.1.1.1 > NULL
%ORACLE_HOME%\bin\dgmgrl -silent / "edit database 'ASHGBADS' set state= apply-on;"

I used ping command in the script to introduce a delay (as a equivalent for sleep command) so that the dataguard broker gets started before we run the next command to turn the apply on.

Script to convert to read-only mode:

set ORACLE_SID=ASHGBADS
set ORACLE_HOME=<Your Oracle home path>
set PATH=%ORACLE_HOME%\bin;%path%
%ORACLE_HOME%\bin\dgmgrl -silent / "edit database 'ASHGBADS' set state = read-only;"

Setting up a Physical Standby database using OEM 12c

Though I am not a big fan of settting up things through OEM, I wanted to give it a try on setting up a Physical database instance using OEM 12c.

Steps and the explanations are provided below:

Earlier before starting the setup, I cloned the oracle home to the dataguard server and also pushed the Oracle 12c Agent to the server.

Now coming to the setup.....

From the database home in OEM 12c, Click "Availability" --> "Add Standby Database" to start the dataguard setup process.

In our case, the Primary database was registered with a listener using a non-standard port (1521) and the OEM throwed out an error message that LOCAL_LISTENER parameter must be set.

Choose the type of standby database to be created. In our case, we were creating a Physical Standby database.


 

OEM provides several ways to create the standby database. We used the online backup method which in turn uses RMAN duplicate from active database.

In the Backup options screen, we need to provide the RMAN degree of parallelism, the host credentials used and the standby redo log names. We already had the Host Credentials saved as Named Credentials in OEM so we used the "Existing" credentials.

In the Database location screen, we need to provide the Standby database name, hostname and the host credentials for the standby database server.

In the "File location" screen, I missed out the small "Customize" button which might have allowed me to specify the new file location for the dataguard instance. But I chose to keep the file names and locations same as that of the primary database.

In this screen, we also need to chose the listener that will be used to connect to the Dataguard instance.

In the "Configuration" screen, we chose to Use the Dataguard Broker so that OEM will do the DG Broker related configurations.

Review the setup and click the Finish button. A new OEM job will be submitted to complete the Dataguard setup.

We can monitor the job progress just by clicking the "view job" link in the previous screen which will take us to the job activity screen.

In our case, the setup was failing with "Timeout" errors while performing the RMAN duplicate setup. This was due to the Windows 2008 firewall which was running in the Dataguard server. We had to create few rules for the Oracle Database in order to resolve the issue.

One more error we encountered is that the primary database had the LOG_ARCHIVE_DEST parameter configured which we need to reconfigure to use LOG_ARCHIVE_DEST_1 parameter.

Once these issues were fixed, the dataguard setup completed without any errors.

  

Cool...Isn't it!!!

We could drill down to each step and see what action were done in the background.  Posting all those information will make the blog more bloated.

We built this standby instance mainly for reporting purpose. The standby database will be in recovery mode for an hour in the morning and evening and rest of the time will be opened in read-only mode so that it can be used for reporting purposes.

I will post more on how we accomplished through DGMGRL (Dataguard Broker) commands and OEM 12c.

RMAN Enhancements in 11g

Rman Enhancements In Oracle 11g. [ID 1115423.1]

--------------------------------------------------------------------------------
In this Document
Purpose
Scope and Application
Rman Enhancements In Oracle 11g.
Improved handling of long-term backups
Backup failover for archived redo logs in the flash recovery area
Archived log deletion policy enhancements
Network-enabled database duplication without backups
Rman Duplicate without target and recovery catalog connections.
Virtual Private Catalog
Import Catalog command
Multisection backups
Undo Optimization
Improved block media recovery performance
Improved block corruption detection
Faster backup compression
Block change tracking support for standby databases
Backup of read-only transportable tablespaces
RMAN Tablespace Point-in-Time Recovery (TSPITR) Enhancements
SET NEWNAME Options
CONVERT DATABASE Option
Expanded Backup Compression Levels
INCARNATION Specifier Enhancement
To Destination Syntax



--------------------------------------------------------------------------------



Applies to:
Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.2.0.1.0 - Release: 11.1 to 11.2
Information in this document applies to any platform.

Purpose
This article provides us the list of RMAN enhancements in Oracle 11g release.
Scope and Application
This article is intended for DBA's having strong RMAN knowledge.
Rman Enhancements In Oracle 11g.

Improved handling of long-term backups
You can create a long-term or archival backup with BACKUP ... KEEP that retains only the archived log files needed to make the backup consistent.

Prior to Oracle Database 11g, if you needed to preserve an online backup for a specified amount of time, RMAN assumed you might want to perform point-in-time recovery for any time within that period and RMAN retained all the archived logs for that time period unless you specified NOLOGS. However, you may have a requirement to simply keep the backup (and what is necessary to keep it consistent and recoverable) for a specified amount of time.

From oracle 11g for the backup with keep option,RMAN includes the data files, archived log files (only those needed to recover an online backup), the relevant autobackup files, and spfiles. All these files must go to the same media family (or group of tapes) and have the same KEEP attributes.

The archival backup provides the RESTORE POINT clause which creates a “consistency” point in the control file. It assigns a name to a specific SCN. The SCN is captured just after the data-file backup completes. The archival backup can be restored and recovered for this point in time, enabling the database to be opened. In contrast, the UNTIL TIME clause specifies the date until which the backup must be kept.


Example
======
RMAN> backup database
2> tag monthly_backup
3> format 'd:\backup_%U'
4> keep forever
5> restore point y2007May;

Starting backup at 01-JUN-2010 19:57:58
current log archived

using channel ORA_DISK_1
backup will never be obsolete
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=D:\DATABASE\ORA11G\ORA11G\SYSTEM01.
input datafile file number=00002 name=D:\DATABASE\ORA11G\ORA11G\SYSAUX01.
input datafile file number=00004 name=D:\DATABASE\ORA11G\ORA11G\USERS01.D
input datafile file number=00003 name=D:\DATABASE\ORA11G\ORA11G\UNDOTBS01
channel ORA_DISK_1: starting piece 1 at 01-JUN-2010 19:58:04
channel ORA_DISK_1: finished piece 1 at 01-JUN-2010 20:01:00
piece handle=D:\BACKUP_06LF5PAC_1_1 tag=MONTHLY_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:56

using channel ORA_DISK_1
backup will never be obsolete
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 01-JUN-2010 20:01:00
channel ORA_DISK_1: finished piece 1 at 01-JUN-2010 20:01:03
piece handle=D:\BACKUP_07LF5PFS_1_1 tag=MONTHLY_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03


current log archived
using channel ORA_DISK_1
backup will never be obsolete
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=48 RECID=47 STAMP=720561667
channel ORA_DISK_1: starting piece 1 at 01-JUN-2010 20:01:12
channel ORA_DISK_1: finished piece 1 at 01-JUN-2010 20:01:15
piece handle=D:\BACKUP_08LF5PG7_1_1 tag=MONTHLY_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

using channel ORA_DISK_1
backup will never be obsolete
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 01-JUN-2010 20:01:24
channel ORA_DISK_1: finished piece 1 at 01-JUN-2010 20:01:31
piece handle=D:\BACKUP_09LF5PGC_1_1 tag=MONTHLY_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 01-JUN-2010 20:01:31


Backup failover for archived redo logs in the flash recovery area
The 11g Archived Redo Log Failover feature enables RMAN to complete a backup even when some archiving destinations are missing logs or contain logs with corrupt blocks where a local archivelog destination is configured alongside the FRA. If at least one log corresponding to a given log sequence and thread is available in the flash recovery area or any of the archiving destinations, then RMAN tries to back it up. If RMAN finds a corrupt block in a log file during backup, it searches other destinations for a copy of that log without corrupt blocks.

The following article explains this feature in detail.


Note 464860.1 11g New Features: Archived Redo Log Failover

Archived log deletion policy enhancements
When you CONFIGURE an archived log deletion policy, the configuration applies to all archiving destinations, including the flash recovery area. Both BACKUP ... DELETE INPUT and DELETE ... ARCHIVELOG obey this configuration, as does the flash recovery area. You can also CONFIGURE an archived redo log deletion policy so that logs are eligible for deletion only after being applied to or transferred to standby database destinations. You can set the policy for mandatory standby destinations only, or for any standby destinations.


Examples
=======
RMAN> configure archivelog deletion policy to applied on standby;
RMAN> configure archivelog deletion policy to applied on standby;
RMAN> configure archivelog deletion policy to backed up 2 times to disk;

Network-enabled database duplication without backups
From Oracle 11g release 1 you can use the DUPLICATE command to create a duplicate database or physical standby database over the network without a need for pre-existing database backups. This form of duplication is called active database duplication.

Please refer the following article for good understanding of active duplicate operation.


Note 452868.1 RMAN 'Duplicate Database' Feature in 11G

Rman Duplicate without target and recovery catalog connections.
Oracle 11g release 2 provides the leverage of creating a duplicate database without connecting to the target database and recovery catalog.

Please refer the following article for good understanding of this feature.


For disk backups:

Note 1113713.1 Creation Of Rman Duplicate Without Target And Recovery Catalog Connection.

For disk and tape backups:

Note 1375864.1 Perform Backup Based RMAN DUPLICATE Without Connecting To Target Database For Both Disk & Tape Backups


Virtual Private Catalog
The owner of a recovery catalog can GRANT or REVOKE access to a subset of the catalog to other database users in the same recovery catalog database. This subset is called a virtual private catalog.

The owner of a centralized recovery catalog, which is also called the base recovery catalog, cangrant or revoke restricted access to the catalog to other database users. All metadata is stored in the base catalog schema.Each restricted user has full read-write access to his or her own metadata, which is called a virtual private catalog.


Import Catalog command
With the IMPORT CATALOG command, you can import the metadata from one recovery catalog schema into a different catalog schema. If you created catalog schemas of different versions to store metadata for multiple target databases, then this command enables you to maintain a single catalog schema for all databases.

The following article explains about this feature.


RMAN 11G Import Catalog

Multisection backups
RMAN can back up a single file in parallel by dividing the work among multiple channels. Each channel backs up one file section. You create a multisection backup by specifying SECTION SIZE on the BACKUP command. Restoring a multisection backup in parallel is automatic and requires no option.

The following article explains about this feature.


RMAN 11G MultiSection Backups

Undo Optimization
In backup undo optimization, RMAN excludes undo not needed for recovery from the backup, that is, for transactions which have already been committed. For example, a user updates the salaries table in the USERS tablespace. The change is written to the USERS tablespace, while the before image of the data is written to the UNDO tablespace. The user commits. A subsequent RMAN backup of the UNDO tablespace does not include the undo information for the salary changes as a restore of this backup would already have the committed data.

The following article explains about this feature.


Note.406468.1 RMAN 11G RMAN UNDO backup optimization

Improved block media recovery performance
When performing block media recovery, RMAN automatically searches the flashback logs, if they are available, for the required blocks before searching backups. Using blocks from the flashback logs can significantly improve block media recovery performance.


Improved block corruption detection
Several database components and utilities, including RMAN, can now detect a corrupt block and record it in V$DATABASE_BLOCK_CORRUPTION. When instance recovery detects a corrupt block, it records it in this view automatically. Oracle Database automatically updates this view when block corruptions are detected or repaired. The VALIDATE command is enhanced with many new options such as VALIDATE ... BLOCK and VALIDATE DATABASE.Improved block corruption detection

You can use the VALIDATE command to manually check for physical and logical corruptions in database files. This command performs the same types of checks as BACKUP VALIDATE, but VALIDATE can check a larger selection of objects. For example, you can validate individual blocks with the VALIDATE DATAFILE ... BLOCK command.



Examples
========
RMAN > validate database;
RMAN > validate backupset 10;
RMAN > validate datafile 1 block 10;

Faster backup compression
In addition to the existing BZIP2 algorithm for binary compression of backups, RMAN also supports the ZLIB algorithm. ZLIB runs faster than BZIP2, but produces larger files. ZLIB requires the Oracle Advanced Compression option. You can use the CONFIGURE COMPRESSION ALGORITHM command to choose between BZIP2 (default) and ZLIB for RMAN backups.


Block change tracking support for standby databases
You can enable block change tracking on a physical standby database. When you back up the standby database, RMAN can use the block change tracking file to quickly identify the blocks that changed since the last incremental backup.


Backup of read-only transportable tablespaces
In previous releases, RMAN could not back up transportable tablespaces until they were made read/write at the destination database. Now RMAN can back up transportable tablespaces when they are not read/write and restore these backups.


RMAN Tablespace Point-in-Time Recovery (TSPITR) Enhancements
From oracle 11g release 2 TSPITR can be used to recover a dropped tablespace and to recover to a point-in-time before the tablespace is brought online. The latter TSPITR operation can be repeated as many times as necessary.

The steps to perform this operation remain the same as they were in previous release but the time/scn/sequence which you provide in the until clause should be prior to the tablespace drop.


SET NEWNAME Options
The SET NEWNAME command is more powerful and easier to use. You can use this command on a specific tablespace or on all datafiles and tempfiles. You can also change the names for multiple files in the database.

The oracle 11g release 2 provides the the following options for set newname.


1.SET NEWNAME FOR DATAFILE and SET NEWNAME FOR TEMPFILE
2.SET NEWNAME FOR TABLESPACE
3.SET NEWNAME FOR DATABASE

The following Substitution Variables are introduced for SET NEWNAME in oracle 11g release 2.



%b Specifies the file name stripped of directory paths. For example, if a datafile is named
/oradata/prod/financial.dbf, then %b results in financial.dbf.
%f Specifies the absolute file number of the datafile for which the new name is generated. For
example, if datafile 2 is duplicated, then %f generates the value 2.
%I Specifies the DBID.
%N Specifies the tablespace name.
%U Specifies the following format: data-D-%d_id-%I_TS-%N_FNO-%f.

Here are a few examples which depict the usage of these variables.


1) If you want to restore the datafiles of a tablespace to a different location and retain the name file names, The following command can be used.

RMAN > run
{
set newname for tablespace users to '/home/oradata/%b'
restore tablespace;
}

2) If you want to restore the entire database to a different location and you need the file names to be uniquely identified by their absolute filenumber,dbid and tablespace name then you can use the following syntax.

RMAN > run
{
set newname for database to '/home/oradata/%U';
restore database;
}

The resulting filename will be something like: /home/oradata/data-D-PROD_id-87650928_TS-SYSTEM_FNO-1

CONVERT DATABASE Option
A new option, SKIP UNNECESSARY DATAFILES, is now supported for the CONVERT DATABASE command. When the option is invoked, the only datafiles that are converted are those that require RMAN processing during transfer between the specified platforms. The rest of the datafiles can be used by the destination database via shared storage or pathname. By skipping the conversion of datafiles that do not contain undo segments, overall database transport time can be reduced. You can use this option when converting at the source or converting ON DESTINATION PLATFORM.


Expanded Backup Compression Levels
RMAN now offers a wider range of compression levels with the Advanced Compression Option (ACO). Although the existing BASIC compression option may be suitable for most environments, you may want to explore the ACO backup compression levels (LOW, MEDIUM, and HIGH) to achieve better performance or higher compression ratios.

If you have enabled the Oracle Database 11g Release 2 Advanced Compression Option, you can choose from the following compression levels:

HIGH Best suited for backups over slower networks where the limiting factor is network speed
MEDIUM Recommended for most environments. Good combination of compression ratios and speed
LOW Least impact on backup throughput and suited for environments where CPU resources are the limiting factor.



Example
======
RMAN> CONFIGURE COMPRESSION ALGORITHM 'HIGH';

INCARNATION Specifier Enhancement
Incarnations may now be used to further qualify archived redo log ranges for the BACKUP, RESTORE, and LIST commands. You can now specify ALL or CURRENT or designate a particular incarnation number when listing ranges of archived logs.

From oracle 11g release 2 the archivelogs can be listed,backed up and restored based on the incarnation to which they belong. This can be achieved by using the key word "incarnation all/current/integer". The incarnation integer is nothing but the Inc Key obtained from "list incarnation" output.


Examples
=======
RMAN> list archivelog sequence between 10 and 20 incarnation 2;
RMAN> backup archivelog sequence between 10 and 20 incarnation 1;
RMAN> restore archivelog sequence between 10 and 20 incarnation 1;

To Destination Syntax
TO DESTINATION syntax has been added to the BACKUP command. This addition allows you to designate a specific directory location for backups to disk and is primarily for use with the BACKUP RECOVERY AREA command. If backup optimization is enabled, then RMAN only skips backups of identical files that reside in the directory location specified by the TO DESTINATION option.



Examples
========
RMAN> backup tablespace users to destination 'd:\database';
RMAN> backup recovery area to destination 'd:\database';

Transportable Tablespaces Across Different Platforms

Here goes some very useful metalink documents to perform a cross platform database migration using Transportable tablespaces.

10g : Transportable Tablespaces Across Different Platforms [ID 243304.1]
Master Note for Transportable Tablespaces (TTS) -- Common Questions and Issues [ID 1166564.1]
NOTE:414878.1 - Cross-Platform Migration on Destination Host Using Rman Convert Database
NOTE:733824.1 - How To Recreate a database using TTS (Transportable TableSpace)

11gR2 New feature DEFERRED_SEGMENT_CREATION

The new feature for the space allocation method in 11gR2 causes the export utility to skip the table in case of full or schema level exports. Using a table level export it displays "EXP-00011: 'Table Name' does not exist" in the export log.

Changes
This Note is applicable only when the parameter "DEFERRED_SEGMENT_CREATION=TRUE".

By default the value of DEFERRED_SEGMENT_CREATION is TRUE. If a user sets this parameter to FALSE then the instance has to be bounced, but this will not effect the existing tables. New tables that the user creates will have the impact of this changed parameter setting.

Cause

The Oracle Database 11gR2 includes a new space allocation method. According to this new space allocation method the non-partitioned heap-organized table in a locally managed tablespace the table segment creation is deferred until the first row is inserted.


Note : This new feature in not applicable to SYS and the SYSTEM users as the segment to the table is created along with the table creation.


Solution
To export this non-partitioned heap-organized table we need to manually allocate the segment to the table by using " Alter table ...MOVE or ALLOCATE EXTENT " command.

Example :

SQL> alter table DEFERRED_SEGMENT.DEF_TAB move;

Table altered.

OR

SQL> alter table deferred_segment.DEF_TAB allocate extent;

Table altered.

Courtesy: support.oracle.com - In 11gR2 Skips The Table At Full And Schema Level Export Or Shows EXP-00011 At Table Level Export [ID 1178343.1]

Find costly SQLs in Oracle 10g

Recently I used the following SQL query to find out the costly SQLs in 10g.

There is no baseline used here to decide which sql is costly, but I considered the SQLs with cost more than 10000 as costly.

select
sql_id,
(select sql_text from dba_hist_sqltext where sql_id=dhsp.sql_id) sql_text,
cost,
cardinality,
bytes,
cpu_cost,
io_cost,
time,
temp_space,
other_xml
from dba_hist_sql_plan dhsp where timestamp > trunc(sysdate) -7
and depth=1
and cost > 10000
order by cost desc

Above SQL returns all the SQLs executed in last 7 days and with the cost greater than 10000.