OracleAppsDBAKK1: DATABASE

Showing posts with label DATABASE. Show all posts

Tuesday, February 6, 2024

FAQ: Oracle E-Business Suite and the Oracle Multitenant Architecture (Doc ID 2567105.1)

Introduced with Oracle Database 12c, the multitenant architecture enables an Oracle database to function as a container database (CDB). A pluggable database (PDB) is a portable collection of schemas, schema objects, and non-schema objects, contained within a CDB.

This document presents a series of commonly asked questions and answers about using Oracle E-Business Suite (EBS) with the multitenant architecture: for example, how many PDBs are currently supported within a CDB.

The topics are grouped in the order they are most likely to be needed by those new to this area, but can be referred to in any desired order or combination.

Section 7: Oracle E-Business Suite Multitenant Database Diagnostics
- How do I find the location of the alert log and trace files for my multitenant database?
- How do I generate an AWR snapshot report for my pluggable database?

There is a change log at the end of this document.

Section 1: General Questions

How does Oracle E-Business Suite support the multitenant architecture?

Oracle E-Business Suite supports the multitenant architecture with a single CDB containing a single pluggable Oracle E-Business Suite database (PDB) in the following combinations:

Oracle E-Business Suite 12.2.3 and later with Oracle Database 19c on-premises
Oracle E-Business Suite 12.2.3 and later with Oracle Database 12cR1 VM DB Systems or Oracle Database 12cR1 in an Exadata DB System
Oracle E-Business Suite 12.1.3 with Oracle Database 19c on-premises
Oracle E-Business Suite 12.1.3 with Oracle Database 12cR1 VM DB Systems or Oracle Database 12cR1 in an Exadata DB System

How many PDBs are supported in a CDB that is running Oracle E-Business Suite?

At present, Oracle E-Business Suite supports a single PDB (single tenant) in a CDB.

Are there additional licensing requirements?

Oracle E-Business Suite currently supports a single pluggable database (single tenant) with a CDB architecture (see previous question). A single PDB in a CDB does not require licensing the Oracle Multitenant database option.

For more information, refer to the following resources:

Do I need to make any Oracle E-Business Suite Infrastructure changes?

Whether you need to make changes to your Oracle E-Business Suite environment depends on where your environment resides:

For on-premises Oracle E-Business Suite environments:
- As part of the upgrade of your Oracle E-Business Suite Database to 19c you will convert your database to the CDB architecture with a single pluggable database (PDB). For more information, refer to the following My Oracle Support knowledge documents:
  - Document 2552181.1, Interoperability Notes: Oracle E-Business Suite Release 12.2 with Oracle Database 19c
For Oracle E-Business Suite environments on Oracle Cloud Infrastructure (OCI):
- If you are moving to a database service (1-node or 2-node VM DB System, or Exadata DB System) as part of the migration of an Oracle E-Business Suite 12c database, you will need to convert the database to the CDB architecture.
  - For more information, refer to Section 3, Certifications and Automations, of My Oracle Support Knowledge Document 2517025.1, Getting Started with Oracle E-Business Suite and Oracle Cloud Infrastructure.

Section 2: Oracle E-Business Suite Container Database (CDB)

Note: Except for cloning, the questions and answers in this section apply both to on-premises and OCI environments.

How do I source the Oracle E-Business Suite CDB environment?

Oracle E-Business Suite database CDB environment files are located in the database ORACLE_HOME. Run the following commands:

$ cd $ORACLE_HOME
$ source <CDB_NAME>_<NODE_NAME>.env

How do I connect to the Oracle E-Business Suite CDB as SYSDBA?

Source the CDB environment file in the ORACLE_HOME and then use SQL*Plus to connect to the CDB as SYSDBA:

$ cd $ORACLE_HOME
$ source <CDB_NAME>_<NODE_NAME>.env
$ sqlplus "/ as sysdba"

How do I start the CDB that hosts the Oracle E-Business suite PDB?

You will use either SQL*Plus or srvctl, depending on whether you are using a single-node instance or Oracle RAC system.

On a single-node instance, run the following commands:
$ source <CDB_NAME>_<NODE_NAME>.env $ sqlplus "/ as sysdba" SQL> startup;
On an Oracle RAC system, run the following commands:
$ source <CDB_NAME>_<NODE_NAME>.env $ srvctl start database -d <CDB name>

How do I shut down the CDB that hosts the Oracle E-Business Suite PDB?

You will use either SQL*Plus or srvctl, depending on whether you are using a single-node instance or Oracle RAC system.

On a single-node instance, run the following commands:
$ source <CDB_NAME>_<NODE_NAME>.env $ sqlplus "/ as sysdba" SQL> shutdown normal;
On an Oracle RAC system, run the following commands:
$ source <CDB_NAME>_<NODE_NAME>.env $ srvctl stop database -d <CDB name>

How do I clone an Oracle E-Business Suite environment with a CDB?

In on-premises environments, the Oracle E-Business Suite Rapid Clone utility can be used to clone both the CDB and PDB together.

Section 3: Oracle E-Business Suite Pluggable Database (PDB)

How do I source the Oracle E-Business Suite PDB environment?

The Oracle E-Business Suite database PDB environment files are located in the database ORACLE_HOME:

$ cd $ORACLE_HOME
$ source <PDB_NAME>_<NODE_NAME>.env

How do I connect to the Oracle E-Business Suite PDB as SYSDBA?

Source the CDB environment file, export the Oracle E-Business Suite PDB name, then use SQL*Plus to connect to the PDB as SYSDBA:

$ source <CDB_NAME>_<NODE_NAME>.env
$ export ORACLE_PDB_SID=<PDB NAME>;
$ sqlplus "/ as sysdba"

How do I open the Oracle E-Business Suite PDB?

Source the CDB environment file, connect to the CDB as SYSDBA, then execute the SQL shown in the example to start the PDB:

$ source <CDB_NAME>_<NODE_NAME>.env
$ sqlplus "/ as sysdba"
SQL> alter pluggable database <EBS PDB Name> open read write services=all;

How do I close the Oracle E-Business Suite PDB?

Source the CDB environment, connect to the CDB as SYSDBA, then execute the SQL shown in the example to close the PDB:

$ source <CDB_NAME>_<NODE_NAME>.env
$ sqlplus "/ as sysdba"
SQL> alter pluggable database <EBS PDB Name> close immediate;

There is also the option to save the state of the PDB. Oracle will then preserve the mode when you restart the CDB. You can find the current saved state by querying DBA_PDB_SAVED_STATES:

$ source <CDB_NAME>_<NODE_NAME>.env
$ sqlplus "/ as sysdba"
SQL> alter pluggable database <EBS PDB Name> save state;
SQL> alter pluggable database <EBS PDB Name> close immediate;

How do I find Oracle E-Business Suite PDB information and status?

The following SQL returns the values for the con_id, con_name, open mode, restricted values of all your PDBs.

$ source <CDB_NAME>_<NODE_NAME>.env
$ sqlplus "/ as sysdba"
SQL> show pdbs;

Alternatively, you could use the following SQL to return only the values for name and open mode:

$ source <CDB_NAME>_<NODE_NAME>.env
$ sqlplus "/ as sysdba"
SQL> select name, open_mode from v$pdbs;

How do I access the Oracle E-Business Suite PDB while logged into the CDB?

Use the following command to set the environment, show the PDB name, and then connect to that PDB:

$ source <CDB_NAME>_<NODE_NAME>.env
$ sqlplus "/ as sysdba"
SQL> show pdbs;
SQL> alter session set container=”PDBNAME”;

Where do I look for PDB errors if I encounter a problem?

Source the environment and then review any plugin violations, as shown in the following example:

$ source <CDB_NAME>_<NODE_NAME>.env
$ sqlplus "/ as sysdba"
SQL> select name, cause, message, status from PDB_PLUG_IN_VIOLATIONS;

Can I unplug an Oracle E-Business Suite PDB from one CDB and plug it into another CDB?

At present, there is no supported procedure to unplug an Oracle E-Business Suite PDB and plug (relocate or clone) it to another CDB.

What parameters in the Oracle E-Business Suite database context file support the multitenant architecture?

Context variables used to support the multitenant architecture are as follows:

s_pluggable_database: This variable is set to TRUE in a multitenant database.
s_pdb_name: This variable is set to PDB name.
s_cdb_name: This variable is set to CDB SID.
s_cdb_unique_name: This variable is set to the DB unique name for the CDB.
s_dbService: This variable is set to ebs_<PDB name> in a multitenant database.
s_db_tenancy: This variable is set to multi-tenant in a multitenant database.
s_cdb_tnsadmin: This variable is set to $ORACLE_HOME/network/admin.

Before running AutoConfig on the database tier, do I source the PDB environment or CDB environment?

You should always source the Oracle E-Business Suite PDB environment before running AutoConfig. You can do so by running the following commands:

$ cd $ORACLE_HOME
$ source <PDB_NAME>_<NODE_NAME>.env

Can I export an Oracle 12c non-CDB database and import it into an Oracle 19c PDB?

Data from a non-CDB database can be directly imported into a PDB using the Data Pump export/import commands. Refer to My Oracle Support Knowledge Document 2554156.1, Export/Import Process for Oracle E-Business Suite Release 12.2 Database Instances Using Oracle Database 19c.

Section 4: Oracle E-Business Suite Multitenant Database SQL and Scripts

How do I set up the environment to run an Oracle E-Business Suite Script or SQL session?

Source the Oracle Home <CDB_NAME>_<HOSTNAME>.env and set the PDB environment variable ORACLE_PDB_SID=<PDB_NAME> before running Oracle E-Business Suite programs such as adgrants.sql.

$ source <CDB_NAME>_<NODE_NAME>.env
$ export ORACLE_PDB_SID=<PDB_NAME>
$ sqlplus "/ as sysdba"

Section 5: Oracle E-Business Suite Multitenant Database Patching

How do I run the EBS Technology Codelevel Checker (ETCC) against my Oracle E-Business Suite PDB?

The EBS Technology Codelevel Checker (ETCC) utility provides two scripts to help ensure you have the necessary database and application tier patches installed on your Oracle E-Business Suite Release 12.2 instance.

ETCC extracts environment-related information from the context file (using the location defined in $CONTEXT_FILE), so you need to source the Oracle E-Business Suite PDB environment before you run the database checker script. For OCI-based environments, you also need to add the cloud=y parameter.

Download ETCC as Patch 17537119 from My Oracle Support and unzip it into a working directory. Then proceed with whichever of the following steps applies to you:

For an on-premises environment, run the commands:
$ source <EBS PDB Name>_<NODE_NAME>.env $ ./checkDBpatch.sh
For an environment in Oracle Cloud Infrastructure, run the commands:
$ source <EBS PDB Name>_<NODE_NAME>.env $ ./checkDBpatch.sh cloud=y

How do I list the OPatch inventory for a multitenant database?

You can list the OPatch inventory of a multitenant database in the same way as for non-CDB. Run the following commands:

$ source <CDB_NAME>_<NODE_NAME>.env
$ export PATH=$PATH:$ORACLE_HOME/OPatch
$ opatch lsinventory -detail

How do I set up the environment to install a database patch in an Oracle E-Business Suite multitenant database?

Source the CDB environment and add the OPatch directory to the path, as shown in the following example:

$ source <CDB_NAME>_<NODE_NAME>.env
$ export PATH=$PATH:$ORACLE_HOME/OPatch

Note: Before running datapatch, ensure that the Oracle E-Business Suite PDB is open as datapatch will only apply or roll back SQL fixes for open PDBs. For more information, refer to My Oracle Support Knowledge Document 1585822.1, Datapatch: Database 12c Post Patch SQL Automation.

Section 6: Oracle E-Business Suite Performance Recommendations

What methodology should I follow to ensure I am getting the best performance out of Oracle E-Business Suite?

Refer to My Oracle Support Knowledge Document 2528000.1, Oracle E-Business Suite Performance Best Practices.

Where can I find real-world practical tips and lessons for tuning my Oracle E-Business Suite environment?

Refer to My Oracle Support Knowledge Document 2125596.1, Achieving Optimal Performance with Oracle E-Business Suite.

Where do I find the recommended performance patches for Oracle E-Business Suite?

Refer to My Oracle Support Knowledge Document 244040.1, Oracle E-Business Suite Recommended Performance Patches.

Where do I find the recommended initialization parameters for Oracle E-Business Suite?

Refer to My Oracle Support Knowledge Document 396009.1, Database Initialization Parameters for Oracle E-Business Suite Release 12. This document includes two main sections:

A common set of database initialization parameters that are applicable to all releases of the Oracle Database
An Oracle Database release-specific section

These two sections combine to provide a complete list of release-specific database initialization parameters.

For Oracle E-Business Suite Release 12.1 on Oracle Database 19c, refer to the following:

Document 396009.1 - Section 1: Common Database Initialization Parameters For All Releases
Document 396009.1 - Section 4: Release-Specific Database Initialization Parameters for Oracle 19c

For Oracle E-Business Suite Release 12.2 on Oracle Database 19c, also refer to the following:

Document 396009.1 - Section 5: Additional Database Initialization Parameters For Oracle E-Business Suite Release 12.2

Note: Each database section has a list of parameters in a removal list. These must be removed because they are obsolete or else the default database value is mandatory and no other value may be set.

Section 7: Oracle E-Business Suite Multitenant Database Diagnostics

How do I find the location of the alert log and trace files for my multitenant database?

Each CDB has an associated alert log, which is used to record log information, warnings and alerts about the Oracle E-Business Suite PDB. It is located in the "Diag Trace" of the container database. Also, all PDBs plugged in a given CDB write trace data to the "Diag Trace" of the container database, which can be found by querying v$diag_info dynamic view as in the following example:

$ source <CDB_NAME>_<NODE_NAME>.env
$ sqlplus "/ as sysdba"
SQL> select value from v$diag_info where name = 'Diag Trace';

How do I generate an AWR snapshot report for my pluggable database?

In Oracle Database 12c Release 1, AWR snapshots are only created at the CDB-level (CDB root). This AWR snapshot is for the whole database system in that it contains the statistical information about the CDB as well as all the PDBs in a multitenant environment.

Oracle 19c multitenant supports AWRs at the CDB and PDB level. There are two new views:

AWR_ROOT view: This shows the AWR data stored only on a CDB root, which are generally equivalent to the DBA_HIST views.
AWR_PDB view: There are a few PDB-level metrics, but the vast majority are instance-wide statistics that are not shown at this level for security reasons. Hence these will not be flushed in AWR, and will always be shown as 0 in the AWR report.

Use the following commands as a basis to create a snapshot and specify AWR_ROOT or AWR_PDB as appropriate:

$ source <CDB_NAME>_<NODE_NAME>.env
$ export ORACLE_PDB_SID=<PDB NAME>;
$ sqlplus "/ as sysdba"
! Run awrrpt.sql file
SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql
...
Specify the location of AWR Data
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
AWR_ROOT - Use AWR data from root (default)
AWR_PDB - Use AWR data from PDB
...

Wednesday, March 2, 2022

CREATE PASSWORD FILE IN ASM DISK GROUP

FOR ORACLE 12C ONLY

ASMCMD> pwcreate –dbuniquename {db_unique_name} {file_path} {sys_password}

ASMCMD> pwcreate –dbuniquename PRDPRE +DATA/PWDFILE/pwdPRDPREoracle

FOR ALL VERSION

orapwd file=’+DATA/orapwPRODPRE’ ENTRIES=10 DBUNIQUENAME=’PRODPRE’

https://docs.oracle.com/database/121/OSTMG/GUID-2ACBBB1E-A39D-473E-A9EF-E7BC3872C36E.htm

Thursday, July 22, 2021

Oracle-Provided Diagnostic Tools

Oracle-Provided Diagnostic Tools • Individual tools • HANGFG: semi-automates hang and dumpfile generation • HANGFG User Guide (Doc ID 362094.1) • Procwatcher • Procwatcher: Script to Monitor and Examine Oracle DB and Clusterware Processes (Doc ID 459694.1) • How To Troubleshoot Database Contention With Procwatcher (Doc ID 1352623.1) • ORATOP • oratop - Utility for Near Real-time Monitoring of Databases, RAC and Single Instance (Doc ID 1500864.1)

Thursday, July 15, 2021

Data Guard Physical Standby - Converting a Snapshot Standby back to a Physical Standby using Data Guard Broker (Doc ID 1546657.1)

this Document

Goal

Solution

References

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.2.0.1 to 12.1.0.2 [Release 11.2 to 12.1]
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.

GOAL

***Checked for relevance on 20-Oct-2016***

Perform a conversion of a Snapshot Standby database back to a Physical Standby Database.

Perform checks relevant to a successful conversion.

Monitor the conversion process to ensure that it has been executed correctly without error.

SOLUTION

NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data
from the Oracle sample schema(s), Public Documentation delivered with an Oracle database product or other training material.
Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.

For the purposes of this document, the following fictitious environment is used as an example to describe the procedure:
Primary database name: rs1_stb

Standby/Snapshot database name : rs1

Diskgroup : +DATA ,+FRA

The current configuration

The Primary Site

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 18 11:58:26 2013



Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> select db_unique_name,database_role,open_mode from v$database;

DB_UNIQUE_NAME               DATABASE_ROLE    OPEN_MODE
------------------------------ ---------------- --------------------
rs1_stb                PRIMARY        READ WRITE

The Standby Site

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 18 11:50:31 2013


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> select db_unique_name,database_role,open_mode from v$database;

DB_UNIQUE_NAME               DATABASE_ROLE    OPEN_MODE
------------------------------ ---------------- --------------------
rs1                   SNAPSHOT STANDBY READ WRITE

Checks to perform prior to the conversion

1. Check from a Data Guard Broker perspective the configuration is healthy and the configuration status is reported as "SUCCESS".

$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys@rs1_stb
Password:
Connected.
DGMGRL> show configuration;

Configuration - rs1_dg

  Protection Mode: MaxPerformance
  Databases:
    rs1_stb - Primary database
    rs1     - Snapshot standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

2. Confirm log shipping is occurring between the sites

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Thu Apr 18 12:08:18 2013



Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.


SQL> archive log list
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     297
Next log sequence to archive   299
Current log sequence           299

Primary Site Alert Log

Details the switching occurring and the shipping of the logs

Starting background process SMCO
Thu Apr 18 11:57:15 2013
SMCO started with pid=24, OS id=3796
Thu Apr 18 12:09:04 2013
Destination LOG_ARCHIVE_DEST_2 is SYNCHRONIZED
LGWR: Standby redo logfile selected for thread 1 sequence 298 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 298 (LGWR switch)
  Current log# 3 seq# 298 mem# 0: +DATA/rs1_stb/onlinelog/group_3.278.801239719
  Current log# 3 seq# 298 mem# 1: +FRA/rs1_stb/onlinelog/group_3.315.801239719
Thu Apr 18 12:09:07 2013
Archived Log entry 380 added for thread 1 sequence 297 ID 0x6cd69690 dest 1:
Thu Apr 18 12:09:19 2013
LGWR: Standby redo logfile selected for thread 1 sequence 299 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 299 (LGWR switch)
  Current log# 1 seq# 299 mem# 0: +DATA/rs1_stb/onlinelog/group_1.276.801239713
  Current log# 1 seq# 299 mem# 1: +FRA/rs1_stb/onlinelog/group_1.327.801239715
Thu Apr 18 12:09:22 2013
Archived Log entry 382 added for thread 1 sequence 298 ID 0x6cd69690 dest 1:

Standby Site Alert Log

Details the receiving of the logs in the Snapshot Standby

$ tail -f /u01/app/oracle/diag/rdbms/rs1/rs1/trace/alert_rs1.log
Thu Apr 18 11:52:11 2013
RFS[4]: Assigned to RFS process 3799
RFS[4]: Selected log 5 for thread 1 sequence 296 dbid 1823795963 branch 800115647
Archived Log entry 528 added for thread 1 sequence 296 ID 0x6cd69690 dest 3:
Thu Apr 18 11:53:30 2013
ALTER SYSTEM SET local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=IP)(PORT=1521))))' SCOPE=MEMORY SID='rs1';
Thu Apr 18 11:54:09 2013
Starting background process SMCO
Thu Apr 18 11:54:09 2013
SMCO started with pid=23, OS id=3812
Thu Apr 18 12:09:03 2013
RFS[2]: Selected log 5 for thread 1 sequence 298 dbid 1823795963 branch 800115647
Thu Apr 18 12:09:04 2013
Archived Log entry 529 added for thread 1 sequence 297 ID 0x6cd69690 dest 3:
Thu Apr 18 12:09:18 2013
RFS[2]: Selected log 4 for thread 1 sequence 299 dbid 1823795963 branch 800115647
Thu Apr 18 12:09:19 2013
Archived Log entry 530 added for thread 1 sequence 298 ID 0x6cd69690 dest 3:

Perform the conversion from a Snapshot Standby to a Physical Standby

Given the shipping is successful and the broker configuration is begin reported as healthy the conversion back to a physical standby can be performed.

1. Start the broker command line and ensure a sys@TNSALIAS connection is made to ensure a successful shutdown and startup of the standby site required throughout this process.

DGMGRL> connect sys@rs1_stb
Password:
Connected.

2. Issue the convert command from the broker

DGMGRL> convert database rs1 to physical standby;

The convert command will perform the following steps

- Shutdown snapshot standby down and restart it to mount

Converting database "rs1" to a Physical Standby database, please wait...
Operation requires shutdown of instance "rs1" on database "rs1"
Shutting down instance "rs1"...
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "rs1" on database "rs1"
Starting instance "rs1"...
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Database mounted.

- Another shutdown of the standby is initiated once the flashback operation has been performed. This flashback will set the standby site back to a time prior to the conversion to a snapshot standby.

Continuing to convert database "rs1" ...
Operation requires shutdown of instance "rs1" on database "rs1"
Shutting down instance "rs1"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

- The Standby site which has now been converted back to a Physical Standby site will now be restarted.

Operation requires startup of instance "rs1" on database "rs1"
Starting instance "rs1"...
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Database mounted.
Database "rs1" converted successfully

Alert Log Extract from the Standby Site detailing the change from Snapshot Standby to Physical Standby

$ tail -f /u01/app/oracle/diag/rdbms/rs1/rs1/trace/alert_rs1.log
..
.
Fri Apr 19 11:41:46 2013
Shutting down instance (immediate)
Stopping background process SMCO
Shutting down instance: further logons disabled
..
.
Fri Apr 19 11:41:55 2013
NOTE: Deferred communication with ASM instance
NOTE: deferred map free for map id 12
Completed: alter database CLOSE NORMAL
alter database DISMOUNT
Shutting down archive processes
Archiving is disabled
..
.
Fri Apr 19 11:42:03 2013
Instance shutdown complete
Fri Apr 19 11:42:04 2013
Starting ORACLE instance (normal)
..
.
Fri Apr 19 11:42:33 2013
RSM0 started with pid=41, OS id=13962
alter database convert to physical standby
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (rs1)
krsv_proc_kill: Killing 4 processes (all RFS)
Flashback Restore Start
..
.
Fri Apr 19 11:42:50 2013
Flashback Restore Complete
Drop guaranteed restore point
Guaranteed restore point  dropped
Deleted Oracle managed file +FRA/rs1/flashback/log_6.412.802083919
Deleted Oracle managed file +FRA/rs1/flashback/log_4.265.800203569
Deleted Oracle managed file +FRA/rs1/flashback/log_3.308.801298337
Clearing standby activation ID 1836586315 (0x6d78154b)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Fri Apr 19 11:42:50 2013
ARC2: Becoming the active heartbeat ARCH
Clearing online redo logfile 1 +DATA/rs1/onlinelog/group_1.261.800115647
Clearing online log 1 of thread 1 sequence number 7
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 +DATA/rs1/onlinelog/group_2.262.800115651
Clearing online log 2 of thread 1 sequence number 8
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 +DATA/rs1/onlinelog/group_3.263.800115655
Clearing online log 3 of thread 1 sequence number 6
Clearing online redo logfile 3 complete
Completed: alter database convert to physical standby
Fri Apr 19 11:43:01 2013
Primary database is in MAXIMUM PERFORMANCE mode
Changing standby controlfile to MAXIMUM PERFORMANCE mode
RFS[4]: Assigned to RFS process 13967
RFS[4]: Selected log 4 for thread 1 sequence 304 dbid 1823795963 branch 800115647
Fri Apr 19 11:43:03 2013
Shutting down instance (immediate)
Shutting down instance: further logons disabled
..
.
Fri Apr 19 11:43:04 2013
NOTE: Deferred communication with ASM instance
All dispatchers and shared servers shutdown
alter database CLOSE NORMAL
ORA-1109 signalled during: alter database CLOSE NORMAL...
alter database DISMOUNT
Shutting down archive processes
Archiving is disabled
..
.
Completed: alter database DISMOUNT
..
.
Fri Apr 19 11:43:16 2013
Instance shutdown complete
Fri Apr 19 11:43:17 2013
Starting ORACLE instance (normal)
..
.
Fri Apr 19 11:43:31 2013
ARC3 started with pid=32, OS id=14084
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Physical Standby Database mounted.
Lost write protection disabled
ARC2: Becoming the active heartbeat ARCH
Completed: alter database  mount
..
.
Fri Apr 19 11:43:44 2013
RSM0 started with pid=39, OS id=14112
ALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='rs1';
ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='rs1';
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE
Attempt to start background Managed Standby Recovery process (rs1)
Fri Apr 19 11:43:48 2013
MRP0 started with pid=40, OS id=14114
MRP0: Background Managed Standby Recovery process started (rs1)
 started logmerger process
Fri Apr 19 11:43:54 2013
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 2 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log /tmp/1_284_800115647.dbf
Media Recovery Log /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_285_800115647.dbf
Fri Apr 19 11:43:56 2013
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  USING CURRENT LOGFILE
Media Recovery Log /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_286_800115647.dbf
Media Recovery Log /tmp/1_287_800115647.dbf
..
.
Media Recovery Log /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_302_800115647.dbf
Media Recovery Log +FRA/rs1/archivelog/2013_04_19/thread_1_seq_303.308.813152615
Media Recovery Log +FRA/rs1/archivelog/2013_04_19/thread_1_seq_304.265.813152621
Media Recovery Waiting for thread 1 sequence 305 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 305 Reading mem 0
  Mem# 0: +DATA/rs1/onlinelog/group_5.269.800367453
  Mem# 1: +FRA/rs1/onlinelog/group_5.316.800367455

Standby Data Guard Broker Log detailing the change from Snapshot Standby to Physical Standby

$  tail -f /u01/app/oracle/diag/rdbms/rs1/rs1/trace/drcrs1.log
04/18/2013 13:53:50
Creating process RSM0
04/18/2013 13:53:54
Process RSM0 re-created with PID = 7625
04/18/2013 13:56:39
Process RSM0, PID = 7625, will be killed
04/18/2013 13:56:46
Creating process RSM0
04/18/2013 13:56:50
Process RSM0 re-created with PID = 7635
04/19/2013 11:41:37
Data Guard notifying Oracle Clusterware of database role change
conversion to physical standby requires this database be restarted to the mounted state
04/19/2013 11:41:52
Notifying DMON of db close
Notifying RSM0 of db close
04/19/2013 11:41:56
Data Guard Broker shutting down
posting shutdown message to primary database 0x02001000
RSM0 successfully terminated
04/19/2013 11:41:58
Command CONVERT DATABASE rs1 completed with warning ORA-16570
>> DMON Process Shutdown <<
04/19/2013 11:42:22
>> Starting Data Guard Broker bootstrap <<
Broker Configuration File Locations:
      dg_broker_config_file1 = "+DATA/rs1/dr1rs1.dat"
      dg_broker_config_file2 = "+FRA/rs1/dr2rs1.dat"
04/19/2013 11:42:27
DMON Registering service rs1_DGB with listener(s)
Broker Configuration:       "rs1_dg"
      Protection Mode:            Maximum Performance
      Fast-Start Failover (FSFO): Disabled, flags=0x0, version=3
      Primary Database:           rs1_stb (0x02010000)
      Standby Database:           rs1, Enabled Snapshot Standby (0x01010000)
04/19/2013 11:42:28
Data Guard notifying Oracle Clusterware of database role change
04/19/2013 11:42:32
Creating process RSM0
04/19/2013 11:43:00
CONVERT to PHYSICAL succeeded. Restart required.
04/19/2013 11:43:09
Data Guard Broker shutting down
posting shutdown message to primary database 0x02001000
RSM0 successfully terminated
04/19/2013 11:43:12
>> DMON Process Shutdown <<
04/19/2013 11:43:35
>> Starting Data Guard Broker bootstrap <<
Broker Configuration File Locations:
      dg_broker_config_file1 = "+DATA/rs1/dr1rs1.dat"
      dg_broker_config_file2 = "+FRA/rs1/dr2rs1.dat"
04/19/2013 11:43:39
DMON Registering service rs1_DGB with listener(s)
Broker Configuration:       "rs1_dg"
      Protection Mode:            Maximum Performance
      Fast-Start Failover (FSFO): Disabled, flags=0x0, version=3
      Primary Database:           rs1_stb (0x02010000)
      Standby Database:           rs1, Enabled Physical Standby (0x01010000)
Physical standby bootstrap requires Oracle Clusterware buildup
04/19/2013 11:43:44
rs1 version check successfully completed
rs1 has current configuration metadata,
      completing bootstrap
Creating process RSM0
04/19/2013 11:43:54
Notifying Oracle Clusterware that database is open
Data Guard notifying Oracle Clusterware to start services and other instances change

REFERENCES

NOTE:443720.1 - Using Snapshot Standby Database.
NOTE:1623815.1 - Data Guard Snapshot Standby Archive Redo Log Management and Configuration

Repairing SQL Performance Regression with SQL Plan Management

Nigel Bayliss
PRODUCT MANAGER

You were hoping for an uneventful day at work when you notice that you have a critical workload query that is taking much longer to execute than usual, and you can see that it is beginning to affect your service levels. You suspect that it has a sub-optimal execution plan and you need to get it sorted out immediately. Is there a quick and easy way to do that?

Automatic SQL plan management can deal with this type of issue without DBA intervention, but what can you do if this feature is not available to you? Fortunately, you can take advantage of enhancements made in SQL plan management (SPM) from Oracle Database 18c onwards (I have a word to say about Oracle Database 12c Release 2 later on). The approach presented here must be initiated by a DBA, but it is nevertheless very easy to use.

The steps are as follows:

Capture the 'problem' SQL statement plan in a SQL plan baseline.
Run the SPM evolve task for this SQL plan baseline (using a particular set of parameters which I'll show you below).
Accept the recommended plan.

In many cases - that's it!

SPM evolution will locate and test execute previously-used SQL execution plans and figure out which ones are really the best. It does not rely on optimizer costings alone. It can be configured to search the automatic workload repository (AWR), the Oracle Database cursor cache and SQL tuning sets. In other words, if you have a better plan in a query repository or the cursor cache, SPM can find it and apply it to the problem query.

In broad terms, this is what the procedure looks like:

BEGIN 
   --
   -- Create a SQL plan baseline for the problem query plan
   -- (in this case assuming that it is in the cursor cache)
   -- 
   n := dbms_spm.load_plans_from_cursor_cache(
                  sql_id => '<problem_SQL_ID>', 
                  plan_hash_value=> <problem_plan_hash_value>, 
                  enabled => 'no');
   --
   -- Set up evolve
   --
   tname := DBMS_SPM.CREATE_EVOLVE_TASK(sql_handle=>handle); 

   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( 
      task_name => tname,
      parameter => 'ALTERNATE_PLAN_BASELINE', 
      value     => 'EXISTING');

   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( 
      task_name => tname,
      parameter => 'ALTERNATE_PLAN_SOURCE', 
      value     => 'CURSOR_CACHE+AUTOMATIC_WORKLOAD_REPOSITORY+SQL_TUNING_SET');

   DBMS_SPM.SET_EVOLVE_TASK_PARAMETER( 
      task_name => tname,
      parameter => 'ALTERNATE_PLAN_LIMIT', 
      value     => 'UNLIMITED');
   --
   -- Evolve
   --
   ename := DBMS_SPM.EXECUTE_EVOLVE_TASK(tname);
   --
   -- Optionally, choose to implement immediately
   --
   n := DBMS_SPM.IMPLEMENT_EVOLVE_TASK(tname);
END; 
/

There will be cases where a SQL statement was previously performing very well and was never picked up and stored in AWR (and perhaps are no longer in the cursor cache). In this case, SPM might not be able to find the previous good plan. You can mitigate this risk if you periodically capture your workloads in SQL tuning sets or if you enable the automatic SQL tuning set. The 'SQL_TUNING_SET' option shown above will include SQL tuning sets as the source of potential execution plans. This will improve the likelihood that you can repair queries that previously ran very quickly and did not show up in AWR.

The method described above is available in Oracle Database 18c, but note that SPM internals were enhanced in this area for Oracle Database 19c. It is therefore possible that you will find that the technique will work best in this release. Oracle Database 12c Release 2 includes the parameter settings used above but the task (at the time of writing) will sometimes fail with ORA-01422 due to bug number 29539794.

SQL plan management applies some acceptance criteria and will only accept plans that perform better than the regressed plan by some margin. In the fully-worked example in GitHub you will see that the SPM evolve report is displayed (the primary script is spm.sql). The report clearly indicates whether or not the acceptance criteria is passed and if it is not, then the plan found in AWR will not be accepted.

The use of SQL tuning sets and AWR has licensing implications. Always check the Database Licence Information User Manual.

You can help to improve the scripts - comments welcome.