Adpreclone and Adcfgclone

 Adpreclone and Adcfgclone

Adpreclone.pl script prepare the source system to be cloned by collecting information about source

system. Create a cloning stage area,generate template and driver from existing files that contain source specific hard coded value.

When you run “adpreclone.pl dbTier” on DB side

Following directories will be created in the ORACLE_HOME/appsutil/clone/Jlib, db, data where “Jlib” relates to libraries “db” will contain the techstack information, “data” will contain the information related to datafiles and required for cloning.

1) Creates driver files at ORACLE_HOME/appsutil/driver/instconf.drv

2) Converts inventory from binary to xml, the xml file is located at $ORACLE_HOME/appsutil/clone/context/db/Sid_context.xml

3) Prepare database for cloning:  This includes creating database control file script and datafile location information file at

$ORACLE_HOME/appsutil/templateadcrdbclone.sql, dbfinfo.lst

4) Generates database creation driver file at ORACLE_HOME/appsutil/clone/data/driverdata.drv

5)Copy JDBC Libraries at ORACLE_HOME/appsutil/clone/jlib/classes12.jar and appsutil

==============================

When you run “adpreclone.pl appsTier” On Apps Side

This will create stage directory at $COMMON_TOP/clone. This also run in two steps.

Techstack:  Creates template files for Oracle_iAS_Home/appsutil/template and Oracle_806_Home/appsutil/template

Creates Techstack driver files for IAS_ORACLE_HOME/appsutil/driver/instconf.drv and 806_ORACLE_HOME/appsutil/driver/instconf.drv

APPL_TOP preparation:  It will create application top driver file at $COMMON_TOP/clone/appl/driver/appl.drv-Copy JDBC libraries and $COMMON_TOP/clone/jlib/classes111.zip

what Perl adcfgclone.pl dbTechStack do?

Perl adcfgclone.pl dbTechStack will do below things.

1)Create context file

2)Register ORACLE_HOME

3)Relink ORACLE_HOME

4)Configure ORACLE_HOME

5)Start SQL*NET listener

what Perl adcfgclone.pl dbTier do?

1)Create context file

2)Register ORACLE_HOME

3)Relink ORACLE_HOME

4)Configure ORACLE_HOME

5)Recreate controlfile

6)Configure database

7)Start SQL*NET listener

==

cd $ORACLE_HOME/appsutils/clone/bin

perl adcfgclone.pl dbTier pwd=apps

This will use the templates and driver files those were created while running adpreclone.pl on source system and has been copied to target system.

Following scripts are run by adcfgclone.pl dbTier for configuring techstack

adchkutl.sh — This will check the system for ld, ar, cc, and make versions.

adclonectx.pl — This will clone the context file. This will ceate a new context file as per the details of this instance.

runInstallConfigDriver — located in $Oracle_Home/appsutil/driver/instconf.drv

Relinking $Oracle_Home/appsutil/install/adlnkoh.sh — This will relink ORACLE_HOME

For data on database side, following scripts are runDriver file $Oracle_Home/appsutil/clone/context/data/driver/data.drv

Create database adcrdb.zipAutoconfig is runControl file creation adcrdbclone.sql

==============

Run adcfgclone.pl for dbTier.

what Perl adcfgclone.pl appsTier do?

perl adcfgclone.pl appsTier will do below things.

1)Create context file

2)Register ORACLE_HOME

3)Relink ORACLE_HOME

4)Configure ORACLE_HOME

5)Create INST_TOP

6)Configure APPL_TOP

7)Start Apps Processses

==

On Application Side

cd $COMMON_TOP/clone/bin/

perl adcfgclone.pl appsTier pwd=apps

Following scripts are run by adcfgclone.pl:

Creates context file for target adclonectx.pl

Run driver files $ORACLE_HOME/appsutil/driver/instconf.drv and $IAS_ORACLE_HOME/appsutil/driver/instconf.drv

Relinking of Oracle Home $ORACLE_HOME/bin/adlnk806.sh and $IAS_ORACLE_HOME/bin/adlnkiAS.sh

At the end it will run the driver file $COMMON_TOP/clone/appl/driver/appl.drv and then runs autoconfig.

When we run adcfgclone.pl which script it will call?

It will call adclone.pl which is located at $AD_TOP/bin .

When we run perl adpreclone.pl dbTier why it requires apps password?

It requires a database connection to validate apps schema.

When do you run adpreclone on Production?

If any changes made to either TechStack,database or any patches applied.

How do we find adpreclone is run in source or not ?

 If clone directory exists under $RDBMS_ORACLE_HOME/appsutil for oracle user and $COMMON_TOP/clone for applmgr user.

When we run perl adpreclone.pl appTier why it will not prompt for apps password?

It doesn’t require db a connection.

adcfgclone on database node we had three modes

perl adcfgclone.pl dbTier

It will configure the ORACLE_HOME on the target database tier node and  recreate the controlfiles.

This is specially used in case of standby database and/or hot backups. It will take care of all the steps.

perl adcfgclone.pl dbTechStack

It will configure the ORACLE_HOME on the target database tier node only. Relink the oracle home.

perl adcfgclone.pl dbconfig

It is used to configure the database with  context file. Database should be in open mode.

adcfgclone.pl appsTier dualfs

DUALFS – new feature is introduced in the latest AD-TXK Delta 7.

This feature will create both the filesystems fs1 and fs2 during the cloning process.

Steps to run SQL Tuning Advisor from Database.

 Dear Folks,

As we already know, we can run tuning advisor again SQL_ID in OEM and implement through it. But some times, we can't implement recommendations.

We have gone through same phase in OEM where we couldn't implement SQL profile through OEM. Hence we have decided to run from DB.

Here are the steps

19c

===

ONPROD [****@servername ~]$ export ORACLE_PDB_SID=OLBUI

NONPROD [****@servername ~]$ sqlplus / as sysdba

SQL> set long 1000000000

Col recommendations for a200

SQL> SQL> DECLARE

l_sql_tune_task_id VARCHAR2(100);

BEGIN

l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

sql_id => '2jy3n3dtmntxm',

scope => DBMS_SQLTUNE.scope_comprehensive,

time_limit => 500,

task_name => '2jy3n3dtmntxm_tuning_task_1',

 2  3  4  5  6  7  8  9 description => 'Tuning task for statement 2jy3n3dtmntxm');

DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/

10  11  12 DECLARE

*

ERROR at line 1:

ORA-13780: SQL statement does not exist.

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79

ORA-06512: at "SYS.PRVT_SQLADV_INFRA", line 257

ORA-06512: at "SYS.DBMS_SQLTUNE", line 771

ORA-06512: at line 4

>>If you receive above error.Use AWR snap IDS between SQL_ID Runs.

SQL> SELECT SNAP_ID FROM DBA_HIST_SQLSTAT

WHERE SQL_ID='2jy3n3dtmntxm'

ORDER BY SNAP_ID; 2  3

  SNAP_ID

----------

   3140

   3141

   3142

   3143

   3144

   3145

   3146

   3147

8 rows selected.

SQL> declare

l_sql_tune_task_id varchar2(100);

 2  3 begin

 4  l_sql_tune_task_id := dbms_sqltune.create_tuning_task (

 begin_snap => 3140,

 end_snap => 3147,

 sql_id => '2jy3n3dtmntxm',

 scope => dbms_sqltune.scope_comprehensive,

 time_limit => 10800,

 task_name => '2jy3n3dtmntxm_tuning_task',

 description => 'tuning_in_OLBUI');

dbms_output.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

end;

/ 5  6  7  8  9  10  11  12  13  14

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '2jy3n3dtmntxm_tuning_task');

PL/SQL procedure successfully completed.

Once above done. Please execute following SQL query to get recommendations.

SET LONG 10000000;

SET PAGESIZE 100000000

SET LINESIZE 200

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

SET PAGESIZE 24

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

FINDINGS SECTION (1 finding)

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

1- SQL Profile Finding (see explain plans section below)

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

  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 82.5%)

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

  - Consider accepting the recommended SQL profile.

    execute dbms_sqltune.accept_sql_profile(task_name =>

    '2jy3n3dtmntxm_tuning_task', task_owner => 'SYS', replace =>

    TRUE);

Executed same in PROD and performance was improved.

SQL> show user

USER is "SYS"

SQL> exec dbms_sqltune.accept_sql_profile(task_name =>'2jy3n3dtmntxm_tuning_task', task_owner => 'SYS', replace =>TRUE);

PL/SQL procedure successfully completed.

After implemented above recommendation, Performance was improved for SQL. 

Thanks.