Cloning An Existing Oracle11g Release 2 (11.2.0.x) RDBMS Installation Using OUI

Cloning An Existing Oracle11g Release 2 (11.2.0.x) RDBMS Installation Using OUI

The source installation is packed up using the "tar" command:

cd /optware/oracle/11.2.0.4

tar -cvf /tmp/db_1.tar

and then moved to the target area(different server) and unpacked:

cd /optware/oracle/11.2.0.4

tar -xvf db_1.tar

cd $ORACLE_HOME/clone/bin

perl clone.pl ORACLE_HOME="" ORACLE_HOME_NAME="" ORACLE_BASE="" OSDBA_GROUP= OSOPER_GROUP=

In my scenario:

cd $ORACLE_HOME/clone/bin

perl clone.pl ORACLE_HOME="/optware/oracle/11.2.0.4/db_1" ORACLE_HOME_NAME="OraHome1" ORACLE_BASE="/optware/oracle" OSDBA_GROUP=dba OSOPER_GROUP=oper

Run the orainstRoot.sh and root.sh scripts as mentioned and you are good to go!!

Confirm the clone by . oraenv, if it prompts for ORACLE_SID.

References:

Cloning An Existing Oracle11g Release 2 (11.2.0.x) RDBMS Installation Using OUI (Doc ID 1221705.1)

SQL Tuning Advisor

Hello friends,

Delayed blog update!

Worked on SQL Tuning Advisor recently, details as below:

The statements to be analyzed can be retrieved from the Automatic Workload Repository (AWR) or ADDM. Here, I have used the ADDM report to get the SQL statement for the mentioned period of time by the application team.

Ran the addmrpt.sql to generate the ADDM report.

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

For using the SQL tuning advisor, first create a new tuning task using the CREATE_TUNING_TASK function.

SET SERVEROUTPUT ON
DECLARE
  l_sql_tune_task_id  VARCHAR2(100);
BEGIN
  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
                          begin_snap  => 2394,
                          end_snap    => 2406,
                          sql_id      => 'dsd586414tchz',
                          scope       => DBMS_SQLTUNE.scope_comprehensive,
                          time_limit  => 60,
                          task_name   => 'dsd586414tchz_AWR_tuning_task',
                          description => 'Tuning task for statement dsd586414tchz in AWR.');
  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

With the tuning task created, we now execute it using the EXECUTE_TUNING_TASK procedure.

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'dsd586414tchz_AWR_tuning_task');

The status of the tuning task can be monitored using the DBA_ADVISOR_LOG view.

SELECT task_name, status FROM dba_advisor_log WHERE task_name='dsd586414tchz_AWR_tuning_task';

Once the tuning task has executed successfully the recommendations can be displayed using the REPORT_TUNING_TASK function.

SET LONG 999999999

SET PAGESIZE 500

SET LINESIZE 500

SELECT DBMS_SQLTUNE.report_tuning_task('dsd586414tchz_AWR_tuning_task') AS recommendations FROM dual;

BEGIN

  DBMS_SQLTUNE.drop_tuning_task (task_name => 'dsd586414tchz_AWR_tuning_task');

END;

/