Thursday, December 19, 2013

Avoid Datafile Conversion during Transportable Database



Purpose

When migrating to a new platform with Transportable Database (RMAN CONVERT DATABASE command), the default behavior is to perform datafile conversion on all datafiles in the database. However, only datafiles that contain undo data require conversion, including all datafiles contained within the SYSTEM tablespace, all datafiles contained within any other tablespace that contains any ROLLBACK segments, and all UNDO tablespaces. Depending on the storage infrastructure and the conversion method used, the time required for a platform migration can be reduced significantly by skipping the conversion process on datafiles that do not require it.

Scope and Application

In Oracle Database 10g Release 2 and Oracle Database 11g Release 1, skipping the conversion process on selected datafiles can only be accomplished when performing a target platform conversion and following the steps shown below.  It is not possible to skip conversion for any datafile when performing a source platform conversion. 

NOTE: When converting FROM/TO HP Tru64 UNIX, in addition to files with UNDO segments, all files with ASSM headers need conversion.

Avoid Datafile Conversion during Transportable Database

To skip datafile conversion for datafiles that do not contain undo data, you must perform a target platform conversion, which is done by running the CONVERT DATABASE ON TARGET PLATFORM command in RMAN.  This command creates three output files, one of which is called the convert script.
For details of the remaining output files and of the transportable database process in general, see Oracle Database Backup and Recovery Advanced User’s Guide for Oracle Database 10g, or Oracle Database Backup and Recovery User's Guide for Oracle Database 11g.
Normally, the convert script contains RMAN CONVERT DATAFILE commands for all datafiles in the database. The script is transferred to the target system, modified to fix file path names, and run to perform the datafile conversions. In order to skip conversion for datafiles that do not contain undo data, when the convert script is modified to fix file path names, it must also be modified to remove CONVERT DATAFILE commands for datafiles that do not contain undo data.

To skip conversion for datafiles that do not contain undo data, perform the following steps:
  1. Perform the transportable database procedure as documented in the Oracle Database Backup and Recovery User's Guide up to the point where the convert script is modified to fix file path names.
During a normal target system conversion, the datafiles are copied from the source system and placed in a staging directory.  The convert script reads the datafiles from the staging directory and places the converted datafiles in their final location.

When implementing this optimization to skip conversion of certain datafiles, only the datafiles that will be converted (i.e. those that contain undo data) are copied to the staging area.  Datafiles that are not converted (i.e. those that are removed from the convert script) must be made accessible to the target system via NFS/shared storage or copied to the target system by other methods. 

  1. Identify datafiles that contain undo data by running the following query:
select distinct(file_name)
from dba_data_files a, dba_rollback_segs b
where a.tablespace_name=b.tablespace_name;

  1. If NOT converting FROM/TO HP Tru64 UNIX, skip this step and go to step #4.
    Identify datafiles with ASSM headers as they also need conversion:
select distinct header_file
from dba_segments
where segment_subtype = 'ASSM';

  1. Edit the convert script.  Remove CONVERT DATAFILE commands for all datafiles except for those that contain undo data, as identified in the previous step.
Note that additional edits will be required to fix file path names, as indicated in the documentation.

  1. Complete the transportable database procedure as documented in the Oracle Database Backup and Recovery User's Guide.  In general:
    • Startup no mount new database
    • Run modified convert script
    • Run transport script, the transport script will:
      • create the controlfile
      • open the database
      • recompile the plsql packages

Example
Datafiles with undo data are identified as follows:
SQL> select distinct(file_name)
     from dba_data_files a, dba_rollback_segs b
     where a.tablespace_name=b.tablespace_name;

FILE_NAME
---------------------------------------------
+ENG/tdb/datafile/undotbs1.6901.662718943
+ENG/tdb/datafile/system.14703.662718941

If the generated convert script has the following contents (Note that the necessary file path name corrections, as referenced in the documentation, are not reflected in this example.):
RUN {

CONVERT DATAFILE '+ENG/tdb/datafile/system.14703.662718941'
FROM PLATFORM 'Linux IA (32-bit)'
FORMAT '/tmp/system.14703.662718941';

CONVERT DATAFILE '+ENG/tdb/datafile/sysaux.13732.662718941'
FROM PLATFORM 'Linux IA (32-bit)'
FORMAT '/tmp/sysaux.13732.662718941';

CONVERT DATAFILE '+ENG/tdb/datafile/example.14713.662719027'
FROM PLATFORM 'Linux IA (32-bit)'
FORMAT '/tmp/example.14713.662719027';

CONVERT DATAFILE '+ENG/tdb/datafile/undotbs1.6901.662718943'
FROM PLATFORM 'Linux IA (32-bit)'
FORMAT '/tmp/undotbs1.6901.662718943';

CONVERT DATAFILE '+ENG/tdb/datafile/users.7185.662718943'
FROM PLATFORM 'Linux IA (32-bit)'
FORMAT '/tmp/users.7185.662718943';

}

Then edit the convert script and leave the CONVERT DATAFILE commands for only the datafiles that contain undo data (Note that the necessary file path name corrections, as referenced in the documentation, are not reflected in this example).
RUN {

CONVERT DATAFILE '+ENG/tdb/datafile/system.14703.662718941'
FROM PLATFORM 'Linux IA (32-bit)'
FORMAT '/tmp/system.14703.662718941';

CONVERT DATAFILE '+ENG/tdb/datafile/undotbs1.6901.662718943'
FROM PLATFORM 'Linux IA (32-bit)'
FORMAT '/tmp/undotbs1.6901.662718943';

}

When you transfer the source system datafiles to the target system, transfer the following datafiles directly to their final destination (i.e. the location where they will reside during normal database use) because they do not contain undo data:
  • +ENG/tdb/datafile/sysaux.13732.662718941
  • +ENG/tdb/datafile/example.14713.662719027
  • +ENG/tdb/datafile/users.7185.662718943

Transfer the following datafiles to the staging area referenced in the convert script because they contain undo data and, therefore, require conversion:
  • +ENG/tdb/datafile/system.14703.662718941
  • +ENG/tdb/datafile/undotbs1.6901.662718943
These datafiles will be converted when the convert script is run, which places a converted copy of each datafile in its final location.

Note that for some platform combination (such as Linux to Windows and vice-versa in 11g), you need not run any CONVERT command in RMAN. You can directly backup and restore RMAN backupsets/imagecopes across platforms. Check below note for details:
Note 1079563.1 RMAN DUPLICATE/RESTORE/RECOVER Mixed Platform Support

References

BUG:8398158 - RMAN CONVERT ORA-600 KCBZ_CHECK_OBJD_TYP_3
NOTE:417455.1 - Datafiles are not converted in parallel for transportable database
NOTE:1079563.1 - RMAN DUPLICATE/RESTORE/RECOVER Mixed Platform Support
Posted by at No comments:
Labels:

Duplicate database from non ASM to ASM (vise versa) to a Different Host



Assumed database names:

Primary Database SID:       PROD
Duplicate Database SID:     AUX
RMAN Catalog SID:          RMAN

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

Steps

1. Backup the primary database.

2 Determine how much disk space will be required.

3 Ensuring you have enough space within your ASM instance.

4 Making the backup available for the duplicate process.

5 Creating the init.ora & administration directories for the duplicate database.

6. Ensuring SQL*NET connections to primary database and RMAN catalog are working.

7. Prepare RMAN duplicate script.

8. Execute the RMAN script.

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

1. Backup of the primary database.


Host A (Target)

Prior to creating a duplicate database you must have a backup of the target database. In this example we assume the backup media is disk. Find appropriate disk space on Host A and take a full backup including archive logs and current controlfile (Figure 1a). If you are planning to duplicate a subset of the database refer to (Figure 1b) this illustrates the RMAN command to backing up certain tablespaces.
 [oracle@linux] export ORACLE_SID=PROD

%rman target=/ catalog=rman/rman@RMAN

RMAN> run {
allocate channel d1 type disk;
backup format '/backups/ORA102/df_t%t_s%s_p%p' database;
sql 'alter system archive log current';
backup format '/backups/ORA102/al_t%t_s%s_p%p' archivelog all;
release channel d1;
}
      Figure 1a - This command will perform a full database backup including archivelogs and the current controlfile.  If backup pieces are on ASM, then a new backup to filesystem is required.

  [oracle@linux] export ORACLE_SID=PROD

 [oracle@linux] rman target=/ catalog=rman/rman@RMAN

RMAN> run {
allocate channel d1 type disk;
backup format '/backups/ORA102/df_t%t_s%s_p%p' tablespace SYSTEM, SYSAUX, UNDO, USERS;
sql 'alter system archive log current';
backup format '/backups/ORA102/al_t%t_s%s_p%p' archivelog all;
release channel d1;
}
      Figure 1b- This command will perform a tablespace backup (SYSTEM, SYSAUX, UNDO & USERS) including archive logs
      and the current controlfile.

2 Determine how much disk space will be required.

Host A(Target)

After deciding what you will be duplicating, calculate the entire space this will require on the new Host. The full database calculation (Figure 2a) will calculate the entire space required whereas (figure 2b) allows you to enter the tablespace names in order to calculate the space required. Figure 2c provides a sample output.
SQL> select DF.TOTAL/1048576 "DataFile Size Mb",
LOG.TOTAL/1048576 "Redo Log Size Mb",
CONTROL.TOTAL/1048576 "Control File Size Mb",
(DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 "Total Size Mb" from dual,
(select sum(a.bytes) TOTAL from dba_data_files a) DF,
(select sum(b.bytes) TOTAL from v$log b) LOG,
(select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL;
      Figure 2a - Calculate total space for all datafiles within database.
SQL> select DF.TOTAL/1048576 "DataFile Size Mb",
LOG.TOTAL/1048576 "Redo Log Size Mb",
CONTROL.TOTAL/1048576 "Control File Size Mb",
(DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 "Total Size Mb" from dual,
(select sum(a.bytes) TOTAL from dba_data_files a where tablespace_name in ('SYSTEM','SYSAUX','UNDO','USERS')) DF,
(select sum(b.bytes) TOTAL from v$log b) LOG,
(select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL;
      Figure 2b - Calculate space for list of datafiles within primary database.


DataFile Size Mb Redo Log Size Mb Control File Size Mb Total Size Mb
---------------- ---------------- -------------------- -------------
             900              150             20.34375    1070.34375
    Figure 2c - Sample output of space calculation.

3. Ensuring you have enough space within your ASM instance.

Host B (AUX)

Prior to starting  the duplicate process you must ensure you have enough disk space within ASM to hold the duplicate database.
% export ORACLE_SID=+ASM

SQL> select NAME, STATE, TOTAL_MB, FREE_MB from v$asm_diskgroup;

NAME                                 STATE   TOTAL_MB    FREE_MB
------------------------------ ----------- ---------- ----------
DGROUP2                            MOUNTED        976          3
DGROUP3                            MOUNTED       4882       4830
Figure 3a - Sample output showing the space available with the ASM diskgroup.


Compare the result received from this query with the output from 2a or 2b and ensure you have enough disk space for your duplicate database.

If you do not have enough disk space within your ASM instance ensure you must add it prior to continuing.

Keep in mind you may also be storing archive logs and/or the recovery area within the ASM instance so ample room must be available.

If you are duplicating from ASM to a filesystem ensure you have enough disk space on the source file system.

4. Making the backup available for the duplicate process.

If your backup resides on disk you will need to copy this back up from Host A to Host B. Ensure you place it in the same directory as where it was created. In the example below (figure 4a) the backup piece resides in ‘/backups/ORA102’ these files need to be copied into the same directory on host B.

Furthermore also ensure than all archive log backups have also been moved and any archive logs in the archive log directory that may be require for the recovery. The archive logs required will depend on the point in time of the recovery.
RMAN> list backup;

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
22 Full 529M DISK 00:00:51 2006/05/16 11:12:54
BP Key: 22 Status: AVAILABLE Compressed: NO Tag: TAG20060516T111203
Piece Name: /backups/PROD/df_t590584323_s23_p1
List of Datafiles in backup set 22
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- ------------------- ----
1 Full 1393845 2006/05/16 11:12:03 /oradata/PROD/system01.dbf
2 Full 1393845 2006/05/16 11:12:03 /oradata/PROD/undotbs01.dbf
3 Full 1393845 2006/05/16 11:12:03 /oradata/PROD/sysaux01.dbf
4 Full 1393845 2006/05/16 11:12:03 /oradata/PROD/users01.dbf


BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
24 48M DISK 00:00:06 2006/05/16 11:13:07
BP Key: 24 Status: AVAILABLE Compressed: NO Tag: TAG20060516T111301
Piece Name: /backups/PROD/al_t590584381_s25_p1

List of Archived Logs in backup set 24
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- ------------------- ---------- ---------
1 78 1344750 2006/05/15 04:55:34 1353208 2006/05/15 10:00:19
1 79 1353208 2006/05/15 10:00:19 1353213 2006/05/15 10:00:20
1 80 1353213 2006/05/15 10:00:20 1371564 2006/05/15 22:00:11
1 81 1371564 2006/05/15 22:00:11 1373291 2006/05/15 22:00:59
1 82 1373291 2006/05/15 22:00:59 1381066 2006/05/16 03:00:05
1 83 1381066 2006/05/16 03:00:05 1390685 2006/05/16 09:03:00
1 84 1390685 2006/05/16 09:03:00 1393870 2006/05/16 11:13:00
1 85 1393870 2006/05/16 11:13:00 1393929 2006/05/16 11:13:00
     Figure 4a - A list backup showing the backup pieces that need to be copied across Host B

5. Creating the init.ora & administration directories for the duplicate database.

Host B(Target)

Create the pfile [initAUX.ora] parameter file in the $ORACLE_HOME/dbs directory for the auxiliary database. An example is shown below (figure 5a) with bare minimum settings. Note you may require to set parameters which are the same as your production database (refer to you primary init.ora)
# +----------------------------------------+
# | FILE : initAUX.ora                     |
# | DATABASE NAME : AUX                    |
# +----------------------------------------+
# Set the below to location of the clone Duplicate database / name of
# clone database.

audit_file_dest =/apps/oracle/admin/AUX/adump
background_dump_dest =/apps/oracle/admin/AUX/bdump
core_dump_dest =/apps/oracle/admin/AUX/cdump
user_dump_dest =/apps/oracle/admin/AUX/udump
db_name ="AUX"
instance_name =AUX

# Set the below to the location of the duplicate clone control file.

control_files =+DGROUP1/control01.ctl

# Set the below for the from and to location for all data files / redo
# logs to be cloned. As you can the the ASM disk group is specified.

db_file_name_convert =("/u01/AUX/ORA102", "+DGROUP1")
log_file_name_convert =("/u01/AUX/ORA102", "+DGROUP1")
#Set the below to the same as the production target

### NOTE IF YOU ARE DUPLICATING FROM ASM TO FILE SYSTEM YOUR CONVERT PARAMETER WOULD BE
### REVERSED.
## control_files =/u01/AUX/ORA102/control01.ctl
## db_file_name_convert =("+DGROUP1","/u01/AUX/ORA102")
## log_file_name_convert =("+DGROUP1","/u01/AUX/ORA102")

undo_management =AUTO
undo_retention =10800
undo_tablespace =UNDOTBS1
db_block_size = 8192
compatible = 10.2.0.1.0
     Figure 5a - Sample initAUX.ora with minimal settings

Following the creation of the initAUX.ora startup nomount the auxiliary instance.
[oracle@linux]export ORACLE_SID=AUX

[oracle@linux] sqlplus '/as sysdba'

SQLPLUS> startup nomount;
      Figure 5b - startup nomount the AUX instance.

6. Ensuring SQL*NET connections to primary database and RMAN catalog are working.

Host B(Target)

Ensure the production target database is open or at least mounted. If using a catalog database this must also be open. Test your SQL*NET connections: From Host B you must be able to connect to the primary DB as sysdba and make an rman connection to RMAN. Perform a basic test using sqlplus. When you have successfully connected exit sqlplus and move onto the next step.
[oracle@linux]% sqlplus ‘sys/oracle@PROD as sysdba’

[oracle@linux]% sqlplus rman/rman@RMAN
Figure 6a - SQL*NET connections

7. Prepare RMAN duplicate script.

In a working directory on Host B create an RMAN script file cr_duplicate.rcv. The example below (figure 7a) shows the command for a partial duplicate.
RUN {
allocate auxiliary channel C1 device type disk;
duplicate target database to AUX;
}
     Figure 7a - Sample standby creation command.

8. Execute the RMAN script.

Start RMAN, connect to the production target, the catalog instance and also the auxiliary clone. Run the RMAN duplicate script as shown below. Before doing this ensure that the Oracle SID environment variable is set to the duplicate clone database.
export ORACLE_SID=AUX

%rman target sys/sys@PROD catalog rman/rman@rman auxiliary /

RMAN> @cr_duplicate.rcv
     Figure 8a - Execute the RMAN duplicate script.
Posted by at No comments:
Labels:

Different types of Patch Conflicts - Superset/Subset/Duplicate/Bug/File





Different types of Patch Conflicts - Superset/Subset/Duplicate/Bug/File

Superset
If all the bugs fixed by a patch which is already installed in the Oracle Home are also fixed by the patch to be applied, then the patch to be applied is considered a superset of the patch already applied.
Example:
  • Patch A, which is already installed in the Oracle home, fixed bugs 1, 2, and 3
  • Patch B,which is to be installed, fixes bugs 1, 2, 3, and 4.
  • Patch B is considered a superset of Patch A.
Result: it is not considered as an error situation. OPatch removes the subset patches from the Oracle Home and applies the new patch.

Subset
If all the bugs fixed by the patch being installed are fixed in a patch already installed in the Oracle Home, the patch being installed is a subset.

Example:
  • Patch A, which is already installed in the Oracle home, fixed bugs 1, 2 and 3
  • Patch B, which is to be installed, fixes bugs 1 and 2.
  • Patch B is a subset of Patch A
Result: it is not considered as an error situation. Patch B will not be installed and reported as a subset to Patch A

Duplicate
If a patch is installed and it is reinstalled, it is considered a duplicate
Example:
  • Patch A, which is already installed in the Oracle home, fixed bugs 1, 2 and 3
  • Patch A is reinstalled
 Results: If -skip_duplicate is included in the apply command, OPatch will skip the duplicate.  If not set, OPatch will rollback Patch A and reapply it

Bug Conflict
If a patch already installed in the Oracle Home has updated an object which the patch being installed also updates, the patch being installed is in Conflict with the patch already installed.
Example:
  • Patch A, which is already installed in the Oracle home, updates aaa.o, bbb.o and ccc.o in libserver10.a
  • Patch B, which is to be installed, updates aaa.o and ddd.o in libserver10.a
  • Patch B is in conflict with Patch A because they both update object aaa.o in libserver10.a
Result: OPatch reports Patch B is in conflict with Patch A and recommends an SR be filed requesting a merge of Patch A and Patch B created.  If the option to continue the installation is selected, Patch A will be rolled back and Patch B will be installed.  The bugs fixed in Patch A will no longer be fixed.

File Conflict
If a patch already installed in the Oracle Home has updated files which the patch being installed also updates, the patch being installed is in File Conflict with the patch already installed.
Example:
  • Patch A, which is already installed in the Oracle home, updates ins_rdbms.mk and env_rdbms.mk
  • Patch B, which is to be installed, updates ins_rdbms.mk, env_rdbms.mk and ins_sqlplus.mk
  • Patch B is in conflict with Patch A because they both update ins_rdbms.mk and env_rdbms.mk
Result: OPatch reports Patch B is in conflict with Patch A and recommends an SR be filed requesting a merge of Patch A and Patch B created.  If the option to continue the installation is selected, Patch A will be rolled back and Patch B will be installed.  The bugs fixed in Patch A will no longer be fixed. 

Attention 
It may happen that opatch returns a confusing output such as
"..is a  bug superset of patch .......but is not a file superset."
ex.:
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./13343461
.................................................................................................
Given patch "13343461" is a bug superset of patch "12419384" but is not a file superset.
 
The "is not a file superset" can be just ignored and will be fixed in later OPatch versions
Posted by at No comments:
Labels:
Subscribe to: Posts (Atom)