Import of Partitioned tables running for Long time

Hi,

Recently we did full import of our Database from 10g to 11g. There are couple issues found during import.
1) The export activity took around 3 hrs to complete but Import took around 20hrs to complete.
2) Database size has grown large than it is expected.

I tried to debug the issue and find the solution and below are the details.

 

Issue1:

Tablespaces with partitioned objects have grown from 20GB to 100GB.

This is issue is due to new hidden parameter _partition_large_extents introduced in 11gR2 specifically.

 

Solution:

Before start of import set the parameter _partition_large_extents to FALSE

 

Issue2:

Import during TUP1:

  full import of 10g database is done into 11g database as part of platform migration. This activity took around 20 hrs to complete.

Identified Time consuming tasks:

1)    We have partitoined table with around 4000+ partitions – 10 hrs – Due to large number of partitions and many partitions have data in them.

2)      Table Statistics import – 6 hrs

3)      Index statistics - 2 hr

 

Solution:

To minimize the downtime, the full database import is split and performed. Below is the complete set of activities tested with timing details captured. This total activity has brought down the total time to 4 hrs.

 1) Import Full database Import of full database excluding some objects & statistics.
Excluded partition tab;e and objects statistics
  e.g.: Impdp full=y exclude=TABLE:<TABLE NAME>
2) Imp excluded objects into a different table Import excluded Partitioned object sepeartely with merge partition option
 A separate table is created without any partitions using partitions=merge and remap_table options.
 
  e.g.: impdp TABLES=<TABLE_NAME> REMAP_TABLE= SOURCENAME:TARGETNAME 

3) Import Metadata Only Import of metadata only for excluded object into database
Imported only metadata only of excluded source table along with partitions
  e.g.: impdp CONTENT=METADATA_ONLY 

4) Insert data  Insert data from table created in step 2 to Table created in Step 3 using sqlplus
#Import data from non partitioned table to partitioned table

#5) Gather stats Gather statistics of all objects
This is estimated time and needs to be tested again.

export of partitioned tables from 10 to 11g resulted in huge increase in size

Observation :

performed completed export of 10g database to 11g Database using datapump utility. Some of the tablespaces are have default initial extent defined at database level. Due to this the import of objects lying under this tablespaces have grown hugely while importing.

 

e.g: Tablespace test has been created with default storage extents as 10g and any object create in this tablespace without nay storage clause has been allocated 64 KB. When this object is imported to 11g tablespace this object had been allocated a default of 8MB space even there is no data in this object.

 

Cause :

                10g

                The custom tablespaces in 10g are created with default initial extent size of database (64K by default for 10g) whereas the standard tablespaces are created with its own initial extent(Uniform) of 1MB. Each subpartition in custom tablespaces are created with a default initial extent of 64KB even there is no data in the database.

                11g

                Whereas the default initial extent size allocation for database from 11.2.0.2 is 8MB to improve performance. Hence as part of import the partitions created in custom tablespaces has picked up default value of 8MB for every empty sub partition created resulting in huge increase of database.

 

 

Solution:

                To prevent this default allocation we need set the hidden parameter _partition_large_extents to FALSE explicitly and then initiate the import.

 

Initial Extent Size of a Partition Changed to 8MB from 64KB After Upgrade to 11.2.0.2 or Later (Doc ID 1295484.1) explains on allocation of default size

 

I tested the scenario by creating one test partition with and without the init parameter and the results are as expected. I will include this step to my import process document.

1)      Create a empty table with partitions and it allocated 8MB ß Init parameter _partition_large_extents is not defined in pfile

2)      Set init parameter _partition_large_extents to FALSE and created a empty table with partitions in custom ts and it allocated only 64KB.

Recovery of Missing undo datafile from coldbackup

Recovery of lost Undo Datafile

Hi All,

This blog is about strange experience I had during the restoration of database from cold backup.

I am working on R12 Upgrade project and as part of DBA Upgrade activity I took cold backup of complete database and application at the initial state of upgrade. Customer has provided us cifs file system to store our backups and it is somewhat different to the normal unix file system. This file system is not case sensitive and it is replacing the files with the same name but with different case and thus resulting in some loss of files. Luckily/Unlucky in my current database I have UNDO Tablespace with 3 data files and out of these two data files have same name with different case and thus when I took a complete cold backup of database resulting in loss of data file.

Now I had to use this backup for building another database and during recovery I found that one of the data files and when I investigated I could identify that the undo data file 1 is replaced by undo data file 2. So, As this is an undo data file I know that it doesn't contain user/system data(database shutdown is graceful) and losing it doesn't lose any of our data. Thus this gave us scenario of recovering database due to loss of undo data file.

This cold backup can be helpful only if the database is shutdown gracefully else the backup would be vane and I need to find another source of backup for restoration. As this is cold backup I could go ahead in recreating the lost undo data file and recover the database successfully.

Below is the procedure followed to restore the UNDO tablespace:

1) Verified the data files in backup location and showed that both files are existing

                [oratacrw@aoccol-101 CRP1Backup]$ pwd

                /mnt/R12_Backups/CRP1Backup

                [oratacrw@aoccol-101 CRP1Backup]$ ls -lrt APPS_UNDOTS1_2.DBF

                -rwxrwSrwt  1 root root 2097160192 Aug 26 11:02 APPS_UNDOTS1_2.DBF

                [oratacrw@aoccol-101 CRP1Backup]$ ls -lrt APPS_UNDOTS1_2*

                -rwxrwxrwx  1 oratacrw dba 2097160192 Aug 26 11:02 APPS_UNDOTS1_2.dbf

2) Copy all the data files from backup location to data files location

3) Modified the init parameters as per new db_name and also changed below parameters for disabling auto undo management.

                UNDO_MANAGEMENT=MANUAL

                The above setting makes database to use SYSTEM as undo tablespace instead of using actual undo tablespace(by default)

4) Create spfile from modified pfile;

                SQL> create spfile from pfile;     

5) Recreate control file manually and shutdown the database.

6) Mount the database in restricted mode.

                SQL> STARTUP RESTRICT MOUNT

                ORACLE instance started.

                Total System Global Area 1073741824 bytes

                Fixed Size                  2089336 bytes

                Variable Size             427822728 bytes

                Database Buffers          629145600 bytes

                Redo Buffers               14684160 bytes

                Database mounted.

7) Offline drop the missing undo data file from database and open the database.

                SQL> ALTER DATABASE DATAFILE '/u02/tacprefdata/APPS_UNDOTS1_01.dbf' OFFLINE DROP;

                Database altered.

                SQL> alter database open;

                Database altered.

8) Drop complete undo tablespace including datafiles.

                SQL> DROP TABLESPACE APPS_UNDOTS1 INCLUDING CONTENTS;

                Tablespace dropped.

               

9) Recreate the undo tablespace with required datafiles.

                SQL> CREATE UNDO TABLESPACE APPS_UNDOTS1 DATAFILE '/u02/tacprefdata/APPS_UNDOTS1_01.dbf' size 2G REUSE;

                Tablespace created.

10) shutdown the database and modify UNDO_MANAGEMENT parameter to "AUTO" and UNDO_TABLESPACE to "new undo ts"              

                SQL> shutdown immediate;

                Database closed.

                Database dismounted.

                ORACLE instance shut down.

               

                Init parameters

               

                undo_management = AUTO

                undo_tablespace = APPS_UNDOTS1

               

11) Recreate spfile and bring up the database

               

                SQL> create spfile from pfile;

                File created.

                SQL> startup

                ORACLE instance started.

                Total System Global Area 1073741824 bytes

                Fixed Size                  2089336 bytes

                Variable Size             427822728 bytes

                Database Buffers          629145600 bytes

                Redo Buffers               14684160 bytes

                Database mounted.

                Database opened

               

                SQL> sho parameter undo

                NAME                                 TYPE        VALUE

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

                undo_management                      string      AUTO

                undo_retention                       integer     900

                undo_tablespace                      string      APPS_UNDOTS1

               

Now the database is up and completely operational.

Thank you.