Category Archives: Database

Manual Creation of SQL Profile

SQL Profiling is required when there are multiple execution plan available in the database and there is a need to use or better performance in one of the execution plan. Now, it is not guaranteed that CBO will always use the same plan hence there is a need of SQL Profiling. In SQL Profiling we lock the execution plan which we and CBO feels the optimal performance can be achieved.

For Test, I will be taking following query from PeopleSoft Application.

SELECT DISTINCT BUSINESS_UNIT,
VOUCHER_ID,
INVOICE_ID,
GROSS_AMT,
PAY_AMT,
INVOICE_DT,
TO_CHAR (INVOICE_DT, ‘YYYY-MM-DD’),
VENDOR_NAME_SHORT,
VENDOR_ID,
NAME1,
VOUCHER_STYLE,
VOUCHER_ID_RELATED,
ENTRY_STATUS_SRH,
VCHR_SRC
FROM PS_VOUCHER_SRCH_VW
WHERE BUSINESS_UNIT = ‘COMP1’ AND VOUCHER_ID LIKE ‘123456%’
ORDER BY BUSINESS_UNIT, VOUCHER_ID DESC ;

Lets Begin:

First, We have to create tuning task.

For Tuning task we need to have the sql id for the sql and then create tuning task, to get sqlid we can use following:

select * from v$sql where sql_text like “SELECT DISTINCT BUSINESS_UNIT,
VOUCHER_ID,
INVOICE_ID,
GROSS_AMT,
PAY_AMT,
INVOICE_DT,%”

Once, we have the sqlid we can create tuning task as follow:

declare
stmt_task VARCHAR2(40);
begin
stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => ‘7u4t0k75bavcp’);
DBMS_OUTPUT.put_line(‘task_id: ‘ || stmt_task );
end;
/

This will give a task ID, in my case it was “TASK_87241”

Next, We execute the task as follow:

begin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => ‘TASK_87241’);
end;
/

Once task is executed, we have the recommendation from CBO. Which can be queried as follow:

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(task_name => ‘TASK_87241’) from dual;

 

If you are satisfied with the sql profile recommended by the CBO then you can accept it as follow:

DECLARE
l_sql_tune_task_id  VARCHAR2(200);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.accept_sql_profile (
task_name => ‘TASK_87204’,
name      => ‘TEST_PROFILE’,
force_match  => TRUE,
profile_type => DBMS_SQLTUNE.PX_PROFILE);
END;
/

If you are not satisfied with the SQL profile then try changing the session parameter and try running the above steps again, optimizer should provide different results.

 

Leave a comment if you found anything which can be changed or not up to the mark. There are multiple ways to do this, this is just one way. In other blog I will be taking through the process of locking down the SQL profile in one database and then copy the profile to other database.

 

Thanks.

Upgrade Oracle Database

These steps are for Upgrading Oracle Document, I have used 11.2.0.4 as target and 11.1.0.7 as source. You might have different versions while upgrading but the step remains same if the path you are following is certified from Oracle.

Always good to check the Oracle Support for certified path.

Task A: Installing new Oracle Software on UNIX box.

1.    Go to Binary location

  1. Will find the 11gR2 release under: “/oracle_cds/staging/64bit/11_2_release”
  2. Installing 11.2.0.4  [This is just an example, in your case database version may vary]:
    “/oracle_cds/staging/64bit/Oracle11.2.0.4/database”

2.    Start X server at your Windows Workstation

  1. Set Display environmental variable
    1. “export DISPLAY=<your_IP>.0:0

3.    Installation Wizard – 30 min

  1. Start the “runInstaller” from -20 min
    “/oracle_cds/ibm/64bit/11_2_release/Oracle11.2.0.4/database” as oracle user.
  2. Execute root.sh script as a root user – 2 min

 

Task B : Upgrading the Database from 11gR1 to 11.2.0.4

Task B-1: Pre-upgrade steps

1.    Collecting Pre-upgrade report

  • Copy the “utlu112i.sql” from new ORACLE_HOME/rdbms/admin to a temp location
    “/opt/oracle/product/11.2.0/db4/rdbms/admin”

2.    Executing the pre-upgrade tool
time – 10 mins

  • Change the location to that temporary directory
  • Start the sqlplus and login as sysdba into the target database (old version)
  • Execute the script which was copied at temporary location with spooling enabled
  • Read the spool log to get the further steps.

3.    Analyze the log for recommended changes.

 

Task B-2: Preparing for the Upgrade

1.    Check the integrity of database — used for lower versions [Prior to 11gR1]

  • Download “DBUPGDIAG.SQL” from Oracle support
  • Execute the script to check the integrity issues
  • Ask for the log location, give the temporary directory used above as the temporary log directory, all logs will be generated under that directory.

2.    Optimizer Statistics

Gather stats on SYS Schema

4.    Disable Oracle Database Vault

  • SELECT * FROM V$OPTION WHERE PARAMETER = ‘Oracle Database Vault’;
  • Follow Oracle Support Document 453903.1
  • Link

5.    Backing up Enterprise Manager Database Control Data – Skip

  • Initiate the oraenv for the old version of oracle
  • Change the current directory to new ORACLE_HOME
    • $ emdwgrd -save -sid old_SID -path save_directory
  • where old_SID is the SID of the database being upgraded and save_directory is the path to the storage place you have chosen for your Database Control files and data.

7.    Ensure the users SYS and SYSTEM have ‘SYSTEM’ as their default tablespace.

  • Check using:
  • SELECT username, default_tablespace
    FROM dba_users
    WHERE username in (‘SYS’,’SYSTEM’);
  • Alter by this code:
    SQL> ALTER user SYS default tablespace SYSTEM;
    SQL> ALTER user SYSTEM default tablespace SYSTEM;

8.    Note down the location of datafiles, redo logs and control files

  • SQL> SELECT name FROM v$controlfile;
    SQL> SELECT file_name FROM dba_data_files;
    SQL> SELECT group#, member FROM v$logfile;.
  • Take a backup of all files

9.    Stop the listener

  • lsnrctl stop testdb_lsnr
  • initiate the netca from previous version of ORACLE_HOME
  • delete the existing listener using netca
  • stop the dbconsole also

10. Shutdown the Database

11. Take a cold backup/rman backup

  • backup database format ‘/oracle/tempdb/andy/%u’ tag ‘before_upgrade’
  • BACKUP CURRENT CONTROLFILE TO ‘/oracle/tempdb/andy’;
  • Backup the whole db and pfile/spfile/control file

 

 

12. Modify the init parameters

  • 11gR1 parameters are similar to 11gR2
  • We done need to modify the parameters in our scenario but may vary on your database, best thing is to refer pre-upgrade report generated earlier

13. Set Environment Variables at OS

  • Set Oracle_HOME to new HOME [ 11gR2 home]
  • Set Oracle_BASE
  • Set Oracle_SID
  • Update the oratab entried according to the database which you are upgrading
  • Copy the .profile from user home to a new file and edit the oracle home value

 

Task B-3: Upgrading steps

 

  • Go to $ORACLE_HOME/rdbms/admin
  • Start the database in upgrade mode
  • Run the following: – 40 min
    1. SQL> set echo on
      SQL> SPOOL upgrade.log
      SQL> @catupgrd.sql
      SQL> spool off

 

 

TASK B-4: Post Upgrade Steps

 

  • Run the Post-Upgrade Status Tool $ORACLE_HOME/rdbms/admin/utlu112s.sql
  • Run utlrp.sql to compile all system objects

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

  • select * from v$version;
  • Create the listener entry in the listener.ora
  • Start the listener
  • Set the listener parameter in the database
  • Gather the statistics on the database
    1. Not gathering statistics can cause performance issues.

 

 

ISSUES

  1. RMAN Catalog

Your rman catalog might be at the lower version, whereas rman catalog have to be at upper version of the database. Hence, upgrade your catalog version.