Sunday, April 13, 2014

SQL Tuning

Things I think important to know for sql tuning:

  • Be able to read execution plan
         - right-most first, from top to bottom
         - visual approach to assist understanding   
         - pay attention to the Predicate Information section
  • Obtaining the execution plan
         - AUTOTRACE 
         - dbms_xplan.display_curosr
  • Cardinality
         - the predicted number of rows generated by an operation
         - cardinality = selectivity * ( number of input rows)
  • COST
         - represent the optimizer's best estimate of the time it will take to execute the statement; CBO always chooses the execution plan with minimum cost.
         - using dbms_xplan.display_cursor to obtain E-row vs A-row ( set statistical_level=all;  or using /* gather_plan_statistics */ )
  • AWR, ADDM, ASH and wait interface - able to identify the offensive SQLs
  • bind variable peeking and plan instability
  • 10046 trace & tkprof
         - a rule of thumb to check efficiency: query+ current/rows < 20
         ie.  select * from ( select col_of_insterest,count(*) from table_A group by col_of_interest order by 2 desc) where rownun <=50
  • Talk to lead developers
         - tune questions not just tune queries
  • Fix a bad query or method of improvement
        - index re-design,
        - hints,
        - gather stats,
        - explore parallelism,
        - plan stability: sql profile( sqlt coe* script) and sql plan baseline (sqlt coe* script)

Friday, April 11, 2014

Creating a DG Broker Configuration and Perform Swithover testing

1. Register Static service

To enable DGMGRL to restart instances during the course of broker operations, a static service must be registered In the listener.ora, the following entries should be presented:
 LISTENER = (DESCRIPTION =
      (ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=host_name)
      (PORT=port_num))))
 SID_LIST_LISTENER=(SID_LIST=(SID_DESC=(SID_NAME=sid_name)
      (GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain)
      (ORACLE_HOME=oracle_home)
      (ENVS="TNS_ADMIN=oracle_home/network/admin")))
In my case, for example in the listener.ora, including:
 SID_LIST_LISTENER =
     (SID_DESC =
       (SID_NAME = etsdb)
       (GLOBAL_DBNAME=etsdb_DGMGRL)          
       (ORACLE_HOME = /apps/opt/oracle/product/11.2.0)
       (ENVS="TNS_ADMIN=/apps/opt/oracle/product/11.2.0/network/admin")
     )
   )

LOCAL_LISTENER initialization parameter shout set,ie

SQL> alter system set local_listener=(ADDRESS=(PROTOCOL=TCP) (HOST=server_name) (PORT=15xx)) scope=both

More details check: Oracle Data Guard Broker and Static Service Registration (Doc ID 1387859.1)

2. Create a broker configuration

pre-requisite: DG_BROKER_START should set to TRUE in order for DMON background process to start
xxx.com:/db1/opt/oracle/product/11.2.0/dbs [ettdb] $ dgmgrl              
DGMGRL for Solaris: Version 11.2.0.3.0 - 64bit Production
 
Copyright (c) 2000, 2009, Oracle. All rights reserved.
 
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys       
Password:
Connected.
DGMGRL>gt; create configuration 'DG_ETS_CONFIG' as
> primary database is 'ettdb'
> connect identifier is ettdb;
Configuration "DG_ETS_CONFIG" created with primary database "ettdb"
DGMGRL> add database 'etsdb' as
> connect identifier is etsdb;
Database "etsdb" added
DGMGRL> show configuration
 
Configuration - DG_ETS_CONFIG
 
  Protection Mode: MaxPerformance
  Databases:
    ettdb - Primary database
    etsdb - Physical standby database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
DISABLED
 
3. Enable the configuration
DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration
 
Configuration - DG_ETS_CONFIG
 
  Protection Mode: MaxPerformance
  Databases:
    ettdb - Primary database
    etsdb - Physical standby database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS

4. Test switchover
xxx.com:/home/oracle [ettdb] $ dgmgrl                
DGMGRL for Solaris: Version 11.2.0.3.0 - 64bit Production
 
Copyright (c) 2000, 2009, Oracle. All rights reserved.
 
Welcome to DGMGRL, type "help" for information.

DGMGRL> connect sys
Password:
Connected.
DGMGRL> show configuration
 
Configuration - DG_ETS_CONFIG
 
  Protection Mode: MaxPerformance
  Databases:
    ettdb - Primary database
    etsdb - Physical standby database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS
 
DGMGRL> switchover to etsdb
Performing switchover NOW, please wait...
New primary database "etsdb" is opening...
Operation requires shutdown of instance "ettdb" on database "ettdb"
Shutting down instance "ettdb"...
ORACLE instance shut down.
Operation requires startup of instance "ettdb" on database "ettdb"
Starting instance "ettdb"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "etsdb"
DGMGRL> show configuration

DGMGRL> switchover to ettdb
Performing switchover NOW, please wait...
New primary database "ettdb" is opening...
Operation requires shutdown of instance "etsdb" on database "etsdb"
Shutting down instance "etsdb"...
ORACLE instance shut down.
Operation requires startup of instance "etsdb" on database "etsdb"
Starting instance "etsdb"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "ettdb"


 
Configuration - DG_ETS_CONFIG
 
  Protection Mode: MaxPerformance
  Databases:
    etsdb - Primary database
    ettdb - Physical standby database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS

A few issues encountered during testing:

1. When set up configuration in DGMGRL, receive ORA-00088: command cannot be executed by shared server

The application this database supports requires using shared server, the fix is to add "(server=dedicated)" in the tnsnames.ore i.e.
ettdb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.com)(PORT = 1523))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = ettdb )
      (server=dedicated) 
    )
  )

2. ORA-16664: unable to receive the result from a database

It turns out due to I have a typo when doing "create configuration":

DGMGRL> create configuration 'DG_ETS_CONFIG' as
> primary database is 'ettdb'
> connect identifier is etsdb;    ===========> should be ettdb obviously


3. switchover failed in an attempt


When I use dgmgrl connect to the primary db, I also have a dgmgrl session connected to the standby db. This appears to be the reason. In the standby site Data Guard Broker log file I saw:
SQL Execution error=604, sql=[ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION SHUTDOWN]. See error stack below.
  ORA-00604: error occurred at recursive SQL level 1
  ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected
Sswitchover to primary command failed

After disconnecting any sessions connected to the standby db, switchover to the standby succeeded.

References: http://docs.oracle.com/cd/E11882_01/server.112/e40771/configure.htm#i1021661

Tuesday, April 08, 2014

Manage Physical Standby Database - Add a Datafile

When a datafile is added to the primary database, what could happen at standby database? Today, I had some hands-on experiences about that.

Case 1 - STANDBY_FILE_MANAGEMENT set to MANUAL at standby database

In this case, an UNNAMED file will be created at $ORACLE_HOME/dbs, we need to issue 'ALTER DATABASE CREATE DATAFILE AS' to fix it.
(1) At primary:
alter tablespace dbaets_data add datafile '/db2/u01/oradata/ettdb/dbaets_data_02.dbf' size 500M;
 

(2) At standby, query v$datafile 
NAME                                                  SIZE_M
-------------------------------------------------    -------
/db2st2/u01/oradata/etsdb/system01.dbf                   500
/db2st2/u01/oradata/etsdb/sysaux01.dbf                  2048
/db2st2/u01/oradata/etsdb/undotbs01.dbf                  271
/db2st2/u01/oradata/etsdb/users01.dbf                    500
/db2st2/u01/oradata/etsdb/xdb01.dbf                     1024
/db2st2/u01/oradata/etsdb/dbaets_data_01.dbf             500
/apps/opt/oracle/product/11.2.0/dbs/UNNAMED00007           0
 

(3) At standby, issue 'ALTER DATABAE CREATE DATAFILE AS'

ALTER DATABASE CREATE DATAFILE '/apps/opt/oracle/product/11.2.0/dbs/UNNAMED00007'  AS '/db2st2/u01/oradata/etsdb/dbaets_data_02.dbf';

(4) At standby, query v$datafile again:

NAME                                                   SIZE_M
-------------------------------------------------- ----------
/db2st2/u01/oradata/etsdb/system01.dbf                    500
/db2st2/u01/oradata/etsdb/sysaux01.dbf                   2048
/db2st2/u01/oradata/etsdb/undotbs01.dbf                   271
/db2st2/u01/oradata/etsdb/users01.dbf                     500
/db2st2/u01/oradata/etsdb/xdb01.dbf                      1024
/db2st2/u01/oradata/etsdb/dbaets_data_01.dbf              500
/db2st2/u01/oradata/etsdb/dbaets_data_02.dbf              500


Case 2 - STANDBY_FILE_MANAGEMENT set to AUTO, but file cannot be created due to other error

At standby, initially I have DB_FILE_NAMEC_CONVERT set as folllow:


 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string      /db2st2/u01/oradata/etsdb, /db
                                                 2/u01/oradata/ettdb
This is not in the correct order as at standby site the path is '/db2st2/u01/oradata/etsdb' whereas '/db2/u01' does not exist.

At primary, I added another datafile:

ops$oracle@ETSDB> alter tablespace dbaets_data add datafile '/db2/u01/oradata/ettdb/dbaets_data_03.dbf' size 100M;
 
Tablespace altered.

In the alert log file of  the standby database, it can be seen the file is not created and managed recovery process (MRP0)  is terminated:

Media Recovery Log /db2st2/arch/1_1115_790089239.dbf
WARNING: File being created with same name as in Primary
Existing file may be overwritten
Errors in file /apps/opt/oracle/admin/etsdb/diag/rdbms/etsdb/etsdb/trace/etsdb_pr00_8554.trc:
ORA-01119: error in creating database file '/db2/u01/oradata/ettdb/dbaets_data_03.dbf'
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
SVR4 Error: 13: Permission denied
File #8 added to control file as 'UNNAMED00008'.
Originally created as:
'/db2/u01/oradata/ettdb/dbaets_data_03.dbf'
Recovery was unable to create the file as:
'/db2/u01/oradata/ettdb/dbaets_data_03.dbf'
MRP0: Background Media Recovery terminated with error 1274
Errors in file /apps/opt/oracle/admin/etsdb/diag/rdbms/etsdb/etsdb/trace/etsdb_pr00_8554.trc:
ORA-01274: cannot add datafile '/db2/u01/oradata/ettdb/dbaets_data_03.dbf' - file could not be created
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovery stopped due to failure in applying recovery marker (opcode 17.30).
Datafiles are recovered to a consistent state at change 12910838021570 but controlfile could be ahead of datafiles.

I reset the the db_file_name_covert to be '/db2/u01/oradata/ettdb','/db2st2/u01/oradata/etsdb' and restarted the MRP0, in the alert log I saw:

MRP0: Background Managed Standby Recovery process started (etsdb)
 started logmerger process
Tue Apr 08 16:43:35 2014
Managed Standby Recovery starting Real Time Apply
MRP0: Background Media Recovery terminated with error 1111
Errors in file /apps/opt/oracle/admin/etsdb/diag/rdbms/etsdb/etsdb/trace/etsdb_pr00_10330.trc:
ORA-01111: name for data file 8 is unknown - rename to correct file
ORA-01110: data file 8: '/apps/opt/oracle/product/11.2.0/dbs/UNNAMED00008'
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01111: name for data file 8 is unknown - rename to correct file
ORA-01110: data file 8: '/apps/opt/oracle/product/11.2.0/dbs/UNNAMED00008'
Managed Standby Recovery not using Real Time Apply
Recovery Slave PR00 previously exited with exception 1111
MRP0: Background Media Recovery process shutdown (etsdb)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT

So an UNNAMED file is created at $ORACLE_HOME/dbs  and MRP0 is stopped again. To fix:

SQL> alter system set standby_file_management=manual;
 
System altered.
 
SQL> alter database create datafile '/apps/opt/oracle/product/11.2.0/dbs/UNNAMED00008' as '/db2st2/u01/oradata/etsdb/dbaets_data_03.dbf';
 
Database altered.
 
SQL> alter system set standby_file_management=auto;
 
System altered.

Case 3 STANDBY_FILE_MANAGEMENT set to AUTO and DB_FILE_NAME_CONVERT  is correct

No issue as expected:
At primary:

ops$oracle@ETSDB> alter tablespace dbaets_data add datafile '/db2/u01/oradata/ettdb/dbaets_data_04.dbf' size 100M;
 
Tablespace altered.

At standby, the alert log shows the datafile is added with the converted file name:

Tue Apr 08 16:56:22 2014
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT
Attempt to start background Managed Standby Recovery process (etsdb)
Tue Apr 08 16:56:22 2014
MRP0 started with pid=33, OS id=12229 
MRP0: Background Managed Standby Recovery process started (etsdb)
 started logmerger process
Tue Apr 08 16:56:27 2014
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 4 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log /db2st2/arch/1_1115_790089239.dbf
Media Recovery Log /db2st2/arch/1_1116_790089239.dbf
Media Recovery Log /db2st2/arch/1_1117_790089239.dbf
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT
Recovery created file /db2st2/u01/oradata/etsdb/dbaets_data_04.dbf
Successfully added datafile 9 to media recovery
Datafile #9: '/db2st2/u01/oradata/etsdb/dbaets_data_04.dbf'

Note: The testing dataguard environment is the same as the one that is described in the previous post. However, I did a switchover, so in today's testing, ettdb is primary and etsdb is standby.