Backup and Clone 10G Oracle Home to Different Server or File System

Backup Oracle Home 10g


Considerations:

1. Database instances are shutdown

2. Listeners are stopped

3. DBConsole / Agent are stopped

4. All other database appliances are stopped.

Backup location:

Server: 10.235.21.XXX

Location : /u02/bin/ORACLE_HOME_BKP/PROD/

Step 1: go to $ORACLE_HOME directory (/u01/app/oracle/product/10.2.0)

% cd $ORACLE_HOME

Step 2: Run tar to run recursive backup of ORACLE_HOME directory

% tar cvf /u02/bin/ORACLE_HOME_BKP/PROD/oracle10ghome_0405.tar .

Restore or Clone Oracle Home 10g


Step 1 : Check the Inventory to see if the ORACLE_HOME was removed (verify the REMOVED=”T” option is added to the ‘HOME NAME’ tag)

% cd $ORACLE_BASE/oraInventory/ContentsXML

% grep "HOME NAME" *

inventory.xml:

Step 2: Create $ORACLE_HOME directory

% mkdir –p $ORACLE_HOME

Step 3: Copy tar backup file to $ORACLE_HOME

cp /u02/bin/ORACLE_HOME_BKP/PROD/oracle10ghome_05.tar $ORACLE_HOME/

Step 4: Unpack the tarball into to $ORACLE_HOME directory

% tar xvf oracle10ghome_05.tar


Step 5: check the contents of /etc/oraInst.loc

vi /etc/oraInst.loc

inventory_loc=/u01/app/oracle/oraInventory

inst_group=oinstall

Step 6: Reregister the ORACLE_HOME

cd $ORACLE_HOME/clone/bin

% perl clone.pl ORACLE_HOME=$ORACLE_HOME ORACLE_HOME_NAME="OraDb10g_home1"

Step 7: Run Root.sh as root

. /u01/app/oracle/product/10.2.0/root.sh

Global Temporary Table performance issue

Based on Oracle SR responses and vendor’s recommendation I ran few test cases to analyze global temporary table performance issues






Test Case 1:



Step 1: Create Global Temporary Table PRADIPTA.PATIENT_INTERFACE_TEMP with primary key



SQL > CREATE GLOBAL TEMPORARY TABLE PRADIPTA.PATIENT_INTERFACE_TEMP

( …………………

……………………

) ON COMMIT PRESERVE ROWS NOCACHE ENABLE ROW MOVEMENT;



SQL > ALTER TABLE PRADIPTA.PATIENT_INTERFACE_TEMP ADD (

CONSTRAINT PK_PATIENT_INTERFACE_TEMP

PRIMARY KEY

(PATIENT_INT_KEY));



Step 2: Checking table statistics information





SQL> select table_name,num_rows,last_analyzed from dba_tab_statistics where table_name='PATIENT_INTERFACE_TEMP' and owner='PRADIPTA';



TABLE_NAME NUM_ROWS LAST_ANALYZED

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

PATIENT_INTERFACE_TEMP



Step 3: Gathering Status on PRADIPTA.PATIENT_INTERFACE_TEMP as ORACLE MAINTANANCE windows does every night



SQL > begin

dbms_stats.gather_table_stats(ownname => 'PRADIPTA', tabname => 'PATIENT_INTERFACE_TEMP', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt=>'FOR ALL COLUMNS SIZE AUTO');

end;

/



Step4: Checking table statistics information after Gather stats



TABLE_NAME NUM_ROWS LAST_ANALYZED

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

PATIENT_INTERFACE_TEMP 0 16-FEB-12



Step 5: Run the process



Process 1: Insert into PRADIPTA.PATIENT_INTERFACE_TEMP;

105695 rows created.

Elapsed: 00:00:01.81



Process 2: Select from PRADIPTA.PATIENT_INTERFACE_TEMP using join to DD.PATIENT_DIM

105674 rows created

Elapsed: 3 – 4 hours



Test Case 2:



Step 1: Delete table statistics on PRADIPTA.PATIENT_INTERFACE_TEMP



SQL > BEGIN

dbms_stats.delete_table_stats(ownname => 'PRADIPTA', tabname => 'PATIENT_INTERFACE_TEMP');

END;

/



Step 2: Checking table statistics information after Gather stats



TABLE_NAME NUM_ROWS LAST_ANALYZED

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

PATIENT_INTERFACE_TEMP



Step 3: Run the process



Process 1: Insert into PRADIPTA.PATIENT_INTERFACE_TEMP;

105695 rows created.

Elapsed: 00:00:01.81 (2 seconds)



Process 2: Select from PRADIPTA.PATIENT_INTERFACE_TEMP using join to DD.PATIENT_DIM

105674 rows created

Elapsed: 00:01:36.91 (1 mins 36 seconds)



Test Case 3:



Step 1: Lock table statistics on PRADIPTA.PATIENT_INTERFACE_TEMP so ORACLE MAINTANANCE window will not gather stats.



SQL > BEGIN

dbms_stats.unlock_table_stats(ownname => 'PRADIPTA', tabname => 'PATIENT_INTERFACE_TEMP');

END;



/

Step 2: Checking table statistics information after Gather stats



TABLE_NAME NUM_ROWS LAST_ANALYZED

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

PATIENT_INTERFACE_TEMP





Step 3: Run the process



Process 1: Insert into PRADIPTA.PATIENT_INTERFACE_TEMP;

105695 rows created.

Elapsed: 00:00:01.81 (2 seconds)



Process 2: Select from PRADIPTA.PATIENT_INTERFACE_TEMP using join to DD.PATIENT_DIM

105674 rows created

Elapsed: 00:01:36.91 (1 mins 36 seconds)







Conclusion:



Oracle (nightly maintenance window job) is gathering statistics on global temporary table, which eventually causing the PATIENT matching performance issue.



Question 1: Why Oracle is gathering status on GTT (Global Temporary Table)?



Question 2: Are Oracle gather stats parameters efficient enough to analyze correct statistics for our application ??