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 ??

Test Case of GTT

Step 1: Create GLOBAL TEMPORARY TABLE T1
CREATE GLOBAL TEMPORARY TABLE T1
(
OWNER VARCHAR2(30 BYTE),
OBJECT_NAME VARCHAR2 (128 BYTE),
OBJECT_ID NUMBER,
TIMESTAMP VARCHAR2(19 BYTE)
)
ON COMMIT DELETE ROWS;

Elapsed: 00:00:00.03

Step 2: Create a permanent table T2
CREATE TABLE T2
(
OWNER VARCHAR2(30 BYTE),
OBJECT_NAME VARCHAR2(128 BYTE),
OBJECT_ID NUMBER,
TIMESTAMP VARCHAR2(19 BYTE)
);

Elapsed: 00:00:00.02

Step 3: Create an index on T1.
Create index idx_test_t1 on t1(owner);

Index created.

Elapsed: 00:00:00.01


Step 4: Create an index on T2.
Create index idx_test_t2 on t2(owner);

Index created.

Elapsed: 00:00:00.02


Step 5: Insert records into T1 from a static table T3(Sample Table).
Approach 1:

insert into t1 select * from t3;
4610624 rows created.
Elapsed: 00:17:24.12


Step 6: Insert records into T2 from a static table T3(Sample Table).
Approach 1:

insert into t2 select * from t3;
4610624 rows created.
Elapsed: 00:18:30.82 ****

Approach 2: (Preferable)

INSERT /*+ APPEND +*/ into t2 select * from t3;
4610624 rows created.
Elapsed: 00:03:25.48 ****

Approach 3: (Preferable)

insert /*+ append parallel(t2, 10) nologging */ into t2 select * from t3;

4610624 rows created.
Elapsed: 00:02:03.54 ****

**** There is a significant improved performance after using append hint in static table


Step 7: Execute a select statement count number of records from T1
Approach 1:
select count(1) from t1;

COUNT(1)
----------
4610624

Elapsed: 00:00:18.10

select /*+ dynamic_sampling(t1 4 ) */ count(1) from t1;

COUNT(1)
----------
4610624

Elapsed: 00:00:03.58

Step 8: Execute a select statement count number of records from T2

Approach 1:

select count(1) from t2;

COUNT(1)
----------
4610624

Elapsed: 00:00:06.71
Approach 2:

select /*+ dynamic sampling(t2 4 ) */ count(1) from t2;

COUNT(1)
----------
4610624

Elapsed: 00:00:03.09

Step 8: Execute a select statement which will use index

Select owner, count(1) from t1 where owner in (‘SYS’,’SEARCH’,’PUBLIC’) group by owner order by 2 desc;

OWNER COUNT(1)
------------------------------ ----------
SYS 1494528
PUBLIC 1284992
SEARCH 444608

Elapsed: 00:00:02.20

Step 9: Execute a select statement which will use index
Select owner, count(1) from t2 where owner in (‘SYS’,’SEARCH’,’PUBLIC’) group by owner order by 2 desc;


OWNER COUNT(1)
------------------------------ ----------
SYS 1494528
PUBLIC 1284992
SEARCH 444608

Elapsed: 00:00:02.16


Restriction on Global temporary tables :
1. GTT cannot be partitioned.
2. We cannot create any Foreign key constraint on GTT
3. Parallel DML and parallel queries are not supported for temporary tables. Parallel hints
are ignored
4. GTT cannot contain columns of nested table.
5. Export and Import utilities can be used to transfer the table definitions, but no data
rows are processed.