User Managed hot backup of oracle database
Used managed backup means you take backup without any oracle feature. Suppose if you take backup by Operating system then it is called user managed backup. And the term hot backup means taking your backup whenever your database is at open state.
To take full database backup follow the following steps.
1)Before proceed remember you can take online/hot backup whenever your database is in Archivelog mode. If your database run on noarchivelog mode then you must take consistent backup that is after cleanly shutdown. In order to determine the archival mode, issue the query,
SQL> SELECT LOG_MODE FROM V$DATABASE;
LOG_MODE
------------
ARCHIVELOG
If you see ARCHIVELOG then you can proceed further. In order to take backup while you are in noarhivelog mode follow other post on my blog.
2)Determine the files that you need to take backup.
Whenever you decide to take your database backup then take backup of data files , online redo log files ,control files, spfile.
In order to decide which files you need to backup issue the following query.
SQL>SELECT NAME "File Need Backup" FROM V$DATAFILE
UNION ALL
SELECT MEMBER FROM V$LOGFILE
UNION ALL
SELECT NAME FROM V$CONTROLFILE
UNION ALL
SELECT VALUE FROM V$PARAMETER WHERE NAME='spfile';
File Need Backup
--------------------------------------------------------------------------------
/oradata2/data1/dbase/system01.dbf
/oradata2/data1/dbase/undotbs01.dbf
/oradata2/data1/dbase/sysaux01.dbf
/oradata2/data1/dbase/users01.dbf
/oradata2/data.dbf
/oradata2/data1/data02.dbf
/oradata2/6.dbf
/oradata2/DBASE/datafile/o1_mf_permanen_42l31vg0_.dbf
/oradata2/data_test.dbf
/oradata2/data1/dbase/redo03.log
/oradata2/data1/dbase/redo02.log
/oradata2/data1/dbase/redo01.log
/oracle/app/oracle/product/10.2.0/db_1/dbs/cntrldupbase.dbf
/oracle/app/oracle/product/10.2.0/db_1/dbs/spfiledupbase.ora
13 rows selected.
So after running the above query I can say I need to backup 13 files.
3)Take the tablespace in backup mode rather than offline and read-only tablespace. In case of offline and read only tablespace you do not have to place the tablespace in backup mode because the database is not permitting changes to the datafiles.
You can check the status, tablespace_name and it's associated data file name with the following query,
SELECT t.STATUS,t.TABLESPACE_NAME "Tablespace", f.FILE_NAME "Datafile"
FROM DBA_TABLESPACES t, DBA_DATA_FILES f
WHERE t.TABLESPACE_NAME = f.TABLESPACE_NAME;
ORDER BY t.NAME;
Take the tablespace in backup mode rather than offline and read-only tablespace.
You can easily make a script of taking the online tablespace in backup mode by following query.
SQL>SELECT 'ALTER TABLESPACE ' ||TABLESPACE_NAME ||' BEGIN BACKUP;' "Script" FROM DBA_TABLESPACES WHERE STATUS NOT IN ('READ ONLY','OFFLINE');
Script
-------------------------------------------------------------
ALTER TABLESPACE SYSTEM BEGIN BACKUP;
ALTER TABLESPACE UNDOTBS1 BEGIN BACKUP;
ALTER TABLESPACE SYSAUX BEGIN BACKUP;
ALTER TABLESPACE TEMP BEGIN BACKUP;
ALTER TABLESPACE USERS BEGIN BACKUP;
ALTER TABLESPACE TEMP_T BEGIN BACKUP;
6 rows selected.
Alternatively, you can issue
SQL>ALTER DATABASE BEGIN BACKUP;
4)Copy the datafile to backup location.
After making a tablespace in backup mode take backup/copy of the associated datafiles. Here you can also make a script in order to copy datafiles to another location.
For online tablespace you must at first take it backup mode. You can check whether backup mode now active or not by issuing following query,
SQL>SELECT t.name AS "TB_NAME", d.file# as "DF#", d.name AS "DF_NAME", b.status
FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b
WHERE d.TS#=t.TS#
AND b.FILE#=d.FILE#
AND b.STATUS='ACTIVE';
SQL> SELECT 'host scp '|| FILE_NAME || ' &backup_location ' "Backup Command" FROM DBA_DATA_FILES;
Enter value for backup_location: /backup
old 1: SELECT 'host scp '|| FILE_NAME || ' &backup_location ' "Backup Command" FROM DBA_DATA_FILES
new 1: SELECT 'host scp '|| FILE_NAME || ' /backup ' "Backup Command" FROM DBA_DATA_FILES
Backup Command
------------------------------------------------------------------------------------------
host scp /oradata2/data1/dbase/system01.dbf /backup
host scp /oradata2/data1/dbase/undotbs01.dbf /backup
host scp /oradata2/data1/dbase/sysaux01.dbf /backup
host scp /oradata2/data1/dbase/users01.dbf /backup
host scp /oradata2/data.dbf /backup
host scp /oradata2/data1/data02.dbf /backup
host scp /oradata2/6.dbf /backup
host scp /oradata2/DBASE/datafile/o1_mf_permanen_42l31vg0_.dbf /backup
host scp /oradata2/data_test.dbf /backup
9 rows selected.
Also you can backup network files. Do a recursive search for *.ora starting in your Oracle home directory and under it.
In order to make script for to copy data files for those tablespace which are only in backup mode then issue,
SQL>SELECT 'host scp '|| d.name ||' &backup_location' FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b
WHERE d.TS#=t.TS#
AND b.FILE#=d.FILE#
AND b.STATUS='ACTIVE';
Run the script that you genereted.
On windows or other operating system you can use graphical browser to copy or other associated copy command.
5)Whenever you copy is finished make the tablespace out of backup mode. You can issue BEGIN BACKUP .. SCP serially (Take one tablespace in begin backup mode and then copy the associated datafiles and make the tablespace out of backup mode) or you can do it parallely(Take all tablespaces in begin backup mode and then copy the associated datafiles of all tabelspaces and then make the tablespace out of backup mode).
You here also make a script like,
SQL>SELECT 'ALTER TABLESPACE ' ||t.name ||' END BACKUP;' "End Backup Script"
FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b
WHERE d.TS#=t.TS#
AND b.FILE#=d.FILE#
AND b.STATUS='ACTIVE';
End Backup Script
----------------------------------------------------------
ALTER TABLESPACE SYSTEM END BACKUP;
You if you have taken Database in backup mode then issue
SQL>ALTER DATABASE END BACKUP;
Monday, March 28, 2011
Sunday, March 27, 2011
How to configure printers ins EBS 11i/R12
How to configure printers ins EBS 11i/R12
Oracle E-Business Suite offers two printing solutions to handle all your printing requirements. For most printing needs, the Pasta Utility offers quick setup and easy maintenance. For additional flexibility, Oracle E-Business Suite allows you to define your own printer drivers and print styles.
We can summarize these configuration as
1. Setup the printer at the OS level
2. Add a valid entry in the hosts file (Printer Name and the IP Address)
3. Login to System Administrator responsibility
4. Navigate to Install > Printer > Register
5. Define a new printer by entering the Printer Name you have set in the hosts file
6. Save
7. Bounce the Concurrent Manager
8. Submit any standard concurrent request
Cheers!
Oracle E-Business Suite offers two printing solutions to handle all your printing requirements. For most printing needs, the Pasta Utility offers quick setup and easy maintenance. For additional flexibility, Oracle E-Business Suite allows you to define your own printer drivers and print styles.
We can summarize these configuration as
1. Setup the printer at the OS level
2. Add a valid entry in the hosts file (Printer Name and the IP Address)
3. Login to System Administrator responsibility
4. Navigate to Install > Printer > Register
5. Define a new printer by entering the Printer Name you have set in the hosts file
6. Save
7. Bounce the Concurrent Manager
8. Submit any standard concurrent request
Cheers!
UNIX/Linux script to remove old archived redo log files
UNIX/Linux script to remove old archived redo log files
This is an excerpt from the book "Oracle Shell scripting", a great source of UNIX scripts for file management.
The UNIX/Linux "find" command can be used to locate unwanted Oracle archived redo log files. Below is a script that will automatically remove all old archived redo log files from the in UNIX.
# Cleanup redo log files that are more than 7 days old
root> find $DBA/$ORACLE_SID/arch/*.log -mtime +7 -exec rm {} \;
Note that the first part of this script (before the –exec) displays all redo log files that are more than 7 days old.
root> find $DBA/$ORACLE_SID/arch/*.log -mtime +7
This is an excerpt from the book "Oracle Shell scripting", a great source of UNIX scripts for file management.
The UNIX/Linux "find" command can be used to locate unwanted Oracle archived redo log files. Below is a script that will automatically remove all old archived redo log files from the in UNIX.
# Cleanup redo log files that are more than 7 days old
root> find $DBA/$ORACLE_SID/arch/*.log -mtime +7 -exec rm {} \;
Note that the first part of this script (before the –exec) displays all redo log files that are more than 7 days old.
root> find $DBA/$ORACLE_SID/arch/*.log -mtime +7
Friday, March 25, 2011
Health Check Alert: The number of records in the table FND_CONCURRENT_REQUESTS is very high and should be purged to avoid performance issues
Health Check Alert: The number of records in the table FND_CONCURRENT_REQUESTS is very high and should be purged to avoid performance issues [ID 1095625.1]
Modified 04-JAN-2011 Type REFERENCE Status PUBLISHED
In this Document
Purpose
Scope
Health Check Alert: The number of records in the table FND_CONCURRENT_REQUESTS is very high and should be purged to avoid performance issues
Description
Risk
Recommendation
References
Applies to:
Oracle Application Object Library - Version: ALL
Information in this document applies to Any Platform,.
Health Check Category: Performance
Severity Level: Warning
Purpose
This document provides a quick reference explaining the following Health Check Alert:
The number of records in the table FND_CONCURRENT_REQUESTS is very high and should be purged to avoid performance issues
Scope
This document is intended for Database Administrators (DBA) / System Administrators / Application Managers.
Health Check Alert: The number of records in the table FND_CONCURRENT_REQUESTS is very high and should be purged to avoid performance issues
IMPORTANT
My Oracle Support provides a proactive health check that automatically detects and notifies you of the existence of this issue before it impacts your business. To leverage this proactive support capability, install the Oracle Configuration Manager (OCM). Information on installing the Oracle Configuration Manager can be found on the Collector tab of My Oracle Support. To view the full portfolio of health checks available, see Note 868955.1
Description
Checks the number of records in the table FND_CONCURRENT_REQUESTS for any performance issues at concurrent manager tier.
Risk
There are 500000+ records in the FND_CONCURRENT_REQUESTS table which can cause performance issues with the the concurrent processing sub-system. We recommended you purge the eligible records.
Recommendation
Follow the below steps to run the 'Purge Concurrent Request and/or Manager Data (FNDCPPUR)' concurrent program to purge eligible records from the FND_CONCURRENT_REQUESTS table. Login as System Administrator, navigate to the Requests form and select 'Purge Concurrent Request and/or Manager Data.' See the System Administrator's Guide - Maintenance for more details
Modified 04-JAN-2011 Type REFERENCE Status PUBLISHED
In this Document
Purpose
Scope
Health Check Alert: The number of records in the table FND_CONCURRENT_REQUESTS is very high and should be purged to avoid performance issues
Description
Risk
Recommendation
References
Applies to:
Oracle Application Object Library - Version: ALL
Information in this document applies to Any Platform,.
Health Check Category: Performance
Severity Level: Warning
Purpose
This document provides a quick reference explaining the following Health Check Alert:
The number of records in the table FND_CONCURRENT_REQUESTS is very high and should be purged to avoid performance issues
Scope
This document is intended for Database Administrators (DBA) / System Administrators / Application Managers.
Health Check Alert: The number of records in the table FND_CONCURRENT_REQUESTS is very high and should be purged to avoid performance issues
IMPORTANT
My Oracle Support provides a proactive health check that automatically detects and notifies you of the existence of this issue before it impacts your business. To leverage this proactive support capability, install the Oracle Configuration Manager (OCM). Information on installing the Oracle Configuration Manager can be found on the Collector tab of My Oracle Support. To view the full portfolio of health checks available, see Note 868955.1
Description
Checks the number of records in the table FND_CONCURRENT_REQUESTS for any performance issues at concurrent manager tier.
Risk
There are 500000+ records in the FND_CONCURRENT_REQUESTS table which can cause performance issues with the the concurrent processing sub-system. We recommended you purge the eligible records.
Recommendation
Follow the below steps to run the 'Purge Concurrent Request and/or Manager Data (FNDCPPUR)' concurrent program to purge eligible records from the FND_CONCURRENT_REQUESTS table. Login as System Administrator, navigate to the Requests form and select 'Purge Concurrent Request and/or Manager Data.' See the System Administrator's Guide - Maintenance for more details
Health Check Alert: The number of records in the table FND_CONCURRENT_REQUESTS is very high and should be purged to avoid performance issues
Health Check Alert: The number of records in the table FND_CONCURRENT_REQUESTS is very high and should be purged to avoid performance issues [ID 1095625.1]
Modified 04-JAN-2011 Type REFERENCE Status PUBLISHED
In this Document
Purpose
Scope
Health Check Alert: The number of records in the table FND_CONCURRENT_REQUESTS is very high and should be purged to avoid performance issues
Description
Risk
Recommendation
References
Applies to:
Oracle Application Object Library - Version: ALL
Information in this document applies to Any Platform,.
Health Check Category: Performance
Severity Level: Warning
Purpose
This document provides a quick reference explaining the following Health Check Alert:
The number of records in the table FND_CONCURRENT_REQUESTS is very high and should be purged to avoid performance issues
Scope
This document is intended for Database Administrators (DBA) / System Administrators / Application Managers.
Health Check Alert: The number of records in the table FND_CONCURRENT_REQUESTS is very high and should be purged to avoid performance issues
IMPORTANT
My Oracle Support provides a proactive health check that automatically detects and notifies you of the existence of this issue before it impacts your business. To leverage this proactive support capability, install the Oracle Configuration Manager (OCM). Information on installing the Oracle Configuration Manager can be found on the Collector tab of My Oracle Support. To view the full portfolio of health checks available, see Note 868955.1
Description
Checks the number of records in the table FND_CONCURRENT_REQUESTS for any performance issues at concurrent manager tier.
Risk
There are 500000+ records in the FND_CONCURRENT_REQUESTS table which can cause performance issues with the the concurrent processing sub-system. We recommended you purge the eligible records.
Recommendation
Follow the below steps to run the 'Purge Concurrent Request and/or Manager Data (FNDCPPUR)' concurrent program to purge eligible records from the FND_CONCURRENT_REQUESTS table. Login as System Administrator, navigate to the Requests form and select 'Purge Concurrent Request and/or Manager Data.' See the System Administrator's Guide - Maintenance for more details
Modified 04-JAN-2011 Type REFERENCE Status PUBLISHED
In this Document
Purpose
Scope
Health Check Alert: The number of records in the table FND_CONCURRENT_REQUESTS is very high and should be purged to avoid performance issues
Description
Risk
Recommendation
References
Applies to:
Oracle Application Object Library - Version: ALL
Information in this document applies to Any Platform,.
Health Check Category: Performance
Severity Level: Warning
Purpose
This document provides a quick reference explaining the following Health Check Alert:
The number of records in the table FND_CONCURRENT_REQUESTS is very high and should be purged to avoid performance issues
Scope
This document is intended for Database Administrators (DBA) / System Administrators / Application Managers.
Health Check Alert: The number of records in the table FND_CONCURRENT_REQUESTS is very high and should be purged to avoid performance issues
IMPORTANT
My Oracle Support provides a proactive health check that automatically detects and notifies you of the existence of this issue before it impacts your business. To leverage this proactive support capability, install the Oracle Configuration Manager (OCM). Information on installing the Oracle Configuration Manager can be found on the Collector tab of My Oracle Support. To view the full portfolio of health checks available, see Note 868955.1
Description
Checks the number of records in the table FND_CONCURRENT_REQUESTS for any performance issues at concurrent manager tier.
Risk
There are 500000+ records in the FND_CONCURRENT_REQUESTS table which can cause performance issues with the the concurrent processing sub-system. We recommended you purge the eligible records.
Recommendation
Follow the below steps to run the 'Purge Concurrent Request and/or Manager Data (FNDCPPUR)' concurrent program to purge eligible records from the FND_CONCURRENT_REQUESTS table. Login as System Administrator, navigate to the Requests form and select 'Purge Concurrent Request and/or Manager Data.' See the System Administrator's Guide - Maintenance for more details
Monday, March 21, 2011
When-New-Form-Instance Trigger Raised Unhandled Exception ORA-1403 FRM-40735
When-New-Form-Instance Trigger Raised Unhandled Exception ORA-1403 FRM-40735 [ID 265657.1]
--------------------------------------------------------------------------------
Modified 21-JUN-2010 Type PROBLEM Status PUBLISHED
Applies to:
Oracle Order Management - Version: 11.5.9 to 11.5.10.3 - Release: 11.5 to 11.5
Information in this document applies to any platform.
Form:OEXOEORD.FMB - Sales Orders
Checked for relevance on 21-Jun-2010
Symptoms
While trying to enter a new customer in the Sales Orders Form,
receive the following errors:
.
FRM-40735: WHEN-NEW-FORM-INSTANCE trigger unhandled exception.
ORA-1403.
ORA-1403: no data found.
.
Taken to the Add Customer Form, and receive the following errors:
FRM-41084: Error getting Group Cell.
FRM-40105: Unable to resolve reference to item INLINE_ADDRESS..
FRM-41045: Cannot find item: invalid ID.
Note: You cannot pass the account number because account number auto-generation is enabled.
Value for account_number must be unique.
.
Sales Orders Form:
FRM-40212: Invalid value for field SOLD_TO_CONTACT.
Errors in the Forms Trace (.FRD log) :
.
FRM-40815: Variable GLOBAL.GHR_INSTALLATION_STATE does not exist.
FRM-40815: Variable GLOBAL.OPM_GML_INSTALLED1 does not exist.
FRM-40815: Variable GLOBAL.ADD_CUSTOMER_PROFILE does not exist.
FRM-40815: Variable GLOBAL.TELESALES_CALL_ADD_CUSTOMER does not exist.
FRM-40815: Variable GLOBAL.CONTACT_SEARCH does not exist.
FRM-40815: Variable GLOBAL.RECALCULATE_CHARGE does not exist.
FRM-40815: Variable GLOBAL.TELESALES_CALL_ADD_CUSTOMER does not exist.
FRM-40815: Variable GLOBAL.RECALCULATE_CHARGE does not exist.
Cause
Profile Option Setting
Solution
1. Set profile option 'Default Country' to local country (i.e.: United States).
2. Set profile option OM:Add Customer to 'All'.
Note: Some customers have observed that the above do not take affect unless the Defaulting Rule Generator has been run after resetting these profile options.
References
BUG:3228397 - ADD CUSTOMER FROM SALES ORDERS FORM FRM-40735 ORA-01403
Related
--------------------------------------------------------------------------------
Products
--------------------------------------------------------------------------------
Oracle E-Business Suite > Order Management > Order Management > Oracle Order Management
Errors
--------------------------------------------------------------------------------
ORA-1403; FRM-40735; FRM-40815; FRM-40105; FRM-41045; FRM-41084; FRM-40212
Back to top
--------------------------------------------------------------------------------
Modified 21-JUN-2010 Type PROBLEM Status PUBLISHED
Applies to:
Oracle Order Management - Version: 11.5.9 to 11.5.10.3 - Release: 11.5 to 11.5
Information in this document applies to any platform.
Form:OEXOEORD.FMB - Sales Orders
Checked for relevance on 21-Jun-2010
Symptoms
While trying to enter a new customer in the Sales Orders Form,
receive the following errors:
.
FRM-40735: WHEN-NEW-FORM-INSTANCE trigger unhandled exception.
ORA-1403.
ORA-1403: no data found.
.
Taken to the Add Customer Form, and receive the following errors:
FRM-41084: Error getting Group Cell.
FRM-40105: Unable to resolve reference to item INLINE_ADDRESS..
FRM-41045: Cannot find item: invalid ID.
Note: You cannot pass the account number because account number auto-generation is enabled.
Value for account_number must be unique.
.
Sales Orders Form:
FRM-40212: Invalid value for field SOLD_TO_CONTACT.
Errors in the Forms Trace (.FRD log) :
.
FRM-40815: Variable GLOBAL.GHR_INSTALLATION_STATE does not exist.
FRM-40815: Variable GLOBAL.OPM_GML_INSTALLED1 does not exist.
FRM-40815: Variable GLOBAL.ADD_CUSTOMER_PROFILE does not exist.
FRM-40815: Variable GLOBAL.TELESALES_CALL_ADD_CUSTOMER does not exist.
FRM-40815: Variable GLOBAL.CONTACT_SEARCH does not exist.
FRM-40815: Variable GLOBAL.RECALCULATE_CHARGE does not exist.
FRM-40815: Variable GLOBAL.TELESALES_CALL_ADD_CUSTOMER does not exist.
FRM-40815: Variable GLOBAL.RECALCULATE_CHARGE does not exist.
Cause
Profile Option Setting
Solution
1. Set profile option 'Default Country' to local country (i.e.: United States).
2. Set profile option OM:Add Customer to 'All'.
Note: Some customers have observed that the above do not take affect unless the Defaulting Rule Generator has been run after resetting these profile options.
References
BUG:3228397 - ADD CUSTOMER FROM SALES ORDERS FORM FRM-40735 ORA-01403
Related
--------------------------------------------------------------------------------
Products
--------------------------------------------------------------------------------
Oracle E-Business Suite > Order Management > Order Management > Oracle Order Management
Errors
--------------------------------------------------------------------------------
ORA-1403; FRM-40735; FRM-40815; FRM-40105; FRM-41045; FRM-41084; FRM-40212
Back to top
How to find location of Install, Autoconfig, Patching , Clone and other logs in R12
How to find location of Install, Autoconfig, Patching , Clone and other logs in R12 [ID 804603.1]
--------------------------------------------------------------------------------
Modified 15-JUL-2009 Type HOWTO Status PUBLISHED
In this Document
Goal
Solution
--------------------------------------------------------------------------------
Applies to:
Oracle Applications Manager - Version: 12.0
Information in this document applies to any platform.
Goal
How to find location of Install, Autoconfig, Patching , Clone and other logs in R12
Solution
Log files are useful in troubleshooting issues in Oracle Applications.
Here is the list of Log file location in Oracle Applications for Startup/Shutdown, Cloning, Patching, DB & Apps Listener and various components in Apps R12/12i:
Note:Instance top ($INST_TOP) is new directory added in R12 to keep the log files ,Startup/stop scripts for the application tier
A. Startup/Shutdown Log files for Application Tier in R12
=========================================================
i) Startup/Shutdown error message text files like adapcctl.txt, adcmctl.txt… :
$INST_TOP/apps/$CONTEXT_NAME/logs/appl/admin/log
ii) Startup/Shutdown error message related to tech stack (10.1.2, 10.1.3 forms/reports/web) :
$INST_TOP/apps/$CONTEXT_NAME/logs/ora/ (10.1.2 & 10.1.3)
$INST_TOP/apps/$CONTEXT_NAME/logs/ora/10.1.3/Apache/error_log[timestamp]
$INST_TOP/apps/$CONTEXT_NAME/logs/ora/10.1.3/opmn/ (OC4J~…, oa*, opmn.log)
$INST_TOP/apps/$CONTEXT_NAME/logs/ora/10.1.2/network/ (listener log)
$INST_TOP/apps/$CONTEXT_NAME/logs/appl/conc/log (CM log files)
B. Log files related to cloning in R12
=======================================
Preclone (adpreclone.pl) log files in source instance
i) Database Tier-$ORACLE_HOME/appsutil/log/$CONTEXT_NAME/(StageDBTier_
--------------------------------------------------------------------------------
Modified 15-JUL-2009 Type HOWTO Status PUBLISHED
In this Document
Goal
Solution
--------------------------------------------------------------------------------
Applies to:
Oracle Applications Manager - Version: 12.0
Information in this document applies to any platform.
Goal
How to find location of Install, Autoconfig, Patching , Clone and other logs in R12
Solution
Log files are useful in troubleshooting issues in Oracle Applications.
Here is the list of Log file location in Oracle Applications for Startup/Shutdown, Cloning, Patching, DB & Apps Listener and various components in Apps R12/12i:
Note:Instance top ($INST_TOP) is new directory added in R12 to keep the log files ,Startup/stop scripts for the application tier
A. Startup/Shutdown Log files for Application Tier in R12
=========================================================
i) Startup/Shutdown error message text files like adapcctl.txt, adcmctl.txt… :
$INST_TOP/apps/$CONTEXT_NAME/logs/appl/admin/log
ii) Startup/Shutdown error message related to tech stack (10.1.2, 10.1.3 forms/reports/web) :
$INST_TOP/apps/$CONTEXT_NAME/logs/ora/ (10.1.2 & 10.1.3)
$INST_TOP/apps/$CONTEXT_NAME/logs/ora/10.1.3/Apache/error_log[timestamp]
$INST_TOP/apps/$CONTEXT_NAME/logs/ora/10.1.3/opmn/ (OC4J~…, oa*, opmn.log)
$INST_TOP/apps/$CONTEXT_NAME/logs/ora/10.1.2/network/ (listener log)
$INST_TOP/apps/$CONTEXT_NAME/logs/appl/conc/log (CM log files)
B. Log files related to cloning in R12
=======================================
Preclone (adpreclone.pl) log files in source instance
i) Database Tier-$ORACLE_HOME/appsutil/log/$CONTEXT_NAME/(StageDBTier_
Sunday, March 20, 2011
RMAN - Sample Backup Scripts 10g
RMAN - Sample Backup Scripts 10g [ID 397315.1]
Modified 08-JUN-2010 Type HOWTO Status PUBLISHED
In this Document
Goal
Solution
Applies to:
Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.1 - Release: 10.1 to 10.2
Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.5.0 [Release: 10.1 to 10.2]
Information in this document applies to any platform.
Goal
Audience: Novice RMAN users.
The following note provides a DBA with several RMAN sample backup scripts. The scripts are very basic and an be executed as shown in examples.
Solution
RMAN - Sample Backup Scripts 10g
• Backup up Whole Database Backups with RMAN
• Backing up Individual Tablespaces with RMAN
• Backing up Individual Datafiles and Datafile Copies with RMAN
• Backing up Control Files with RMAN
• Backing up Server Parameter Files with RMAN
• Backing up Archived Redo Logs with RMAN
• Backing up the Whole database including archivelogs
=====================================================================================
Making Whole Database Backups with RMAN
You can perform whole database backups with the database mounted or open. To perform a whole database backup from the RMAN prompt the BACKUP DATABASE command can be used. The simplest form of the command requires no parameters, as shown in this example:
RMAN> backup database;
In the following example no backup location was specified meaning that the backups will automatically be placed in the Flash Recovery Area (FRA). If the FRA has not been setup then all backups default to $ORACLE_HOME/dbs.
How to check if the RFA has been setup:
SQL> show parameter recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /recovery_area
db_recovery_file_dest_size big integer 50G
If your FRA is not setup (ie values are null) please refer to the following note for assistance in setting it up.
Note 305648.1 What is a Flash Recovery Area and how to configure it ?
If you wish to place your backup outside the FRA then following RMAN syntax may be used.
RMAN> backup database format '/backups/PROD/df_t%t_s%s_p%p';
• Backing Up Individual Tablespaces with RMAN
RMAN allows individual tablespaces to be backed up with the database in open or mount stage.
RMAN> backup tablespace SYSTEM, UNDOTBS, USERS;
• Backing Up Individual Datafiles and Datafile Copies with RMAN
The flexibilty of being able to backup a single datafile is also available. As seen below you are able to reference the datafile via the file# or file name. Multiple datafiles can be backed up at a time.
RMAN> backup datafile 2;
RMAN> backup datafile 2 format '/backups/PROD/df_t%t_s%s_p%p';
RMAN> backup datafile 1,2,3,6,7,8;
RMAN> backup datafile '/oradata/system01.dbf';
• Backing Up the current controlfile & Spfile
The controlfile and spfile are backed up in similar ways. Whenever a full database backup if performed, the controlfile and spfile are backed up. In fact whenever file#1 is backed up these two files are backed up also.
It is also good practise to backup the controlfile especially after tablespaces and datafiles have been added or deleted.
If you are not using an RMAN catalog it is more impotant that you frequently backup of your controlfile. You can also configure another method of controlfile backup which is referred to as 'autobackup of controlfile'.
Refer to the manual for more information regarding this feature.
RMAN> backup current controlfile;
RMAN> backup current controlfile format '/backups/PROD/df_t%t_s%s_p%p';
RMAN> backup spfile;
• Backing Up Archivelogs
It is important that archivelogs are backed up in a timely manner and correctly removed to ensure the file system does not fill up. Below are a few different examples. Option one backs up all archive logs to the FRA or default location. Option two backs up all archivelogs generate between 7 and 30 days and option three backs up archive logs from log sequence number XXX until logseq YYY then deletes the archivelogs. It also backups the archive logs to a specified location.
RMAN> backup archivelog all;
RMAN> backup archivelog from time 'sysdate-30' until time 'sysdate-7';
RMAN> backup archivelog from logseq=XXX until logseq=YYY delete input format '/backups/PROD/%d_archive_%T_%u_s%s_p%p';
• Backing up the Whole database including archivelogs
Below is an example of how the whole database can be backed up and at the same time backup the archive logs and purge them following a successful backup. The first example backups up to the FRA, however it you wish to redirect the output the second command shows how this is achieved.
RMAN> backup database plus archivelog delete input;
RMAN> backup database plus archivelog delete input format '/backups/PROD/df_t%t_s%s_p%p';
Modified 08-JUN-2010 Type HOWTO Status PUBLISHED
In this Document
Goal
Solution
Applies to:
Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.1 - Release: 10.1 to 10.2
Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.5.0 [Release: 10.1 to 10.2]
Information in this document applies to any platform.
Goal
Audience: Novice RMAN users.
The following note provides a DBA with several RMAN sample backup scripts. The scripts are very basic and an be executed as shown in examples.
Solution
RMAN - Sample Backup Scripts 10g
• Backup up Whole Database Backups with RMAN
• Backing up Individual Tablespaces with RMAN
• Backing up Individual Datafiles and Datafile Copies with RMAN
• Backing up Control Files with RMAN
• Backing up Server Parameter Files with RMAN
• Backing up Archived Redo Logs with RMAN
• Backing up the Whole database including archivelogs
=====================================================================================
Making Whole Database Backups with RMAN
You can perform whole database backups with the database mounted or open. To perform a whole database backup from the RMAN prompt the BACKUP DATABASE command can be used. The simplest form of the command requires no parameters, as shown in this example:
RMAN> backup database;
In the following example no backup location was specified meaning that the backups will automatically be placed in the Flash Recovery Area (FRA). If the FRA has not been setup then all backups default to $ORACLE_HOME/dbs.
How to check if the RFA has been setup:
SQL> show parameter recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /recovery_area
db_recovery_file_dest_size big integer 50G
If your FRA is not setup (ie values are null) please refer to the following note for assistance in setting it up.
Note 305648.1 What is a Flash Recovery Area and how to configure it ?
If you wish to place your backup outside the FRA then following RMAN syntax may be used.
RMAN> backup database format '/backups/PROD/df_t%t_s%s_p%p';
• Backing Up Individual Tablespaces with RMAN
RMAN allows individual tablespaces to be backed up with the database in open or mount stage.
RMAN> backup tablespace SYSTEM, UNDOTBS, USERS;
• Backing Up Individual Datafiles and Datafile Copies with RMAN
The flexibilty of being able to backup a single datafile is also available. As seen below you are able to reference the datafile via the file# or file name. Multiple datafiles can be backed up at a time.
RMAN> backup datafile 2;
RMAN> backup datafile 2 format '/backups/PROD/df_t%t_s%s_p%p';
RMAN> backup datafile 1,2,3,6,7,8;
RMAN> backup datafile '/oradata/system01.dbf';
• Backing Up the current controlfile & Spfile
The controlfile and spfile are backed up in similar ways. Whenever a full database backup if performed, the controlfile and spfile are backed up. In fact whenever file#1 is backed up these two files are backed up also.
It is also good practise to backup the controlfile especially after tablespaces and datafiles have been added or deleted.
If you are not using an RMAN catalog it is more impotant that you frequently backup of your controlfile. You can also configure another method of controlfile backup which is referred to as 'autobackup of controlfile'.
Refer to the manual for more information regarding this feature.
RMAN> backup current controlfile;
RMAN> backup current controlfile format '/backups/PROD/df_t%t_s%s_p%p';
RMAN> backup spfile;
• Backing Up Archivelogs
It is important that archivelogs are backed up in a timely manner and correctly removed to ensure the file system does not fill up. Below are a few different examples. Option one backs up all archive logs to the FRA or default location. Option two backs up all archivelogs generate between 7 and 30 days and option three backs up archive logs from log sequence number XXX until logseq YYY then deletes the archivelogs. It also backups the archive logs to a specified location.
RMAN> backup archivelog all;
RMAN> backup archivelog from time 'sysdate-30' until time 'sysdate-7';
RMAN> backup archivelog from logseq=XXX until logseq=YYY delete input format '/backups/PROD/%d_archive_%T_%u_s%s_p%p';
• Backing up the Whole database including archivelogs
Below is an example of how the whole database can be backed up and at the same time backup the archive logs and purge them following a successful backup. The first example backups up to the FRA, however it you wish to redirect the output the second command shows how this is achieved.
RMAN> backup database plus archivelog delete input;
RMAN> backup database plus archivelog delete input format '/backups/PROD/df_t%t_s%s_p%p';
RMAN - Sample Backup Scripts 10g
RMAN - Sample Backup Scripts 10g [ID 397315.1]
Modified 08-JUN-2010 Type HOWTO Status PUBLISHED
In this Document
Goal
Solution
Applies to:
Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.1 - Release: 10.1 to 10.2
Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.5.0 [Release: 10.1 to 10.2]
Information in this document applies to any platform.
Goal
Audience: Novice RMAN users.
The following note provides a DBA with several RMAN sample backup scripts. The scripts are very basic and an be executed as shown in examples.
Solution
RMAN - Sample Backup Scripts 10g
• Backup up Whole Database Backups with RMAN
• Backing up Individual Tablespaces with RMAN
• Backing up Individual Datafiles and Datafile Copies with RMAN
• Backing up Control Files with RMAN
• Backing up Server Parameter Files with RMAN
• Backing up Archived Redo Logs with RMAN
• Backing up the Whole database including archivelogs
=====================================================================================
Making Whole Database Backups with RMAN
You can perform whole database backups with the database mounted or open. To perform a whole database backup from the RMAN prompt the BACKUP DATABASE command can be used. The simplest form of the command requires no parameters, as shown in this example:
RMAN> backup database;
In the following example no backup location was specified meaning that the backups will automatically be placed in the Flash Recovery Area (FRA). If the FRA has not been setup then all backups default to $ORACLE_HOME/dbs.
How to check if the RFA has been setup:
SQL> show parameter recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /recovery_area
db_recovery_file_dest_size big integer 50G
If your FRA is not setup (ie values are null) please refer to the following note for assistance in setting it up.
Note 305648.1 What is a Flash Recovery Area and how to configure it ?
If you wish to place your backup outside the FRA then following RMAN syntax may be used.
RMAN> backup database format '/backups/PROD/df_t%t_s%s_p%p';
• Backing Up Individual Tablespaces with RMAN
RMAN allows individual tablespaces to be backed up with the database in open or mount stage.
RMAN> backup tablespace SYSTEM, UNDOTBS, USERS;
• Backing Up Individual Datafiles and Datafile Copies with RMAN
The flexibilty of being able to backup a single datafile is also available. As seen below you are able to reference the datafile via the file# or file name. Multiple datafiles can be backed up at a time.
RMAN> backup datafile 2;
RMAN> backup datafile 2 format '/backups/PROD/df_t%t_s%s_p%p';
RMAN> backup datafile 1,2,3,6,7,8;
RMAN> backup datafile '/oradata/system01.dbf';
• Backing Up the current controlfile & Spfile
The controlfile and spfile are backed up in similar ways. Whenever a full database backup if performed, the controlfile and spfile are backed up. In fact whenever file#1 is backed up these two files are backed up also.
It is also good practise to backup the controlfile especially after tablespaces and datafiles have been added or deleted.
If you are not using an RMAN catalog it is more impotant that you frequently backup of your controlfile. You can also configure another method of controlfile backup which is referred to as 'autobackup of controlfile'.
Refer to the manual for more information regarding this feature.
RMAN> backup current controlfile;
RMAN> backup current controlfile format '/backups/PROD/df_t%t_s%s_p%p';
RMAN> backup spfile;
• Backing Up Archivelogs
It is important that archivelogs are backed up in a timely manner and correctly removed to ensure the file system does not fill up. Below are a few different examples. Option one backs up all archive logs to the FRA or default location. Option two backs up all archivelogs generate between 7 and 30 days and option three backs up archive logs from log sequence number XXX until logseq YYY then deletes the archivelogs. It also backups the archive logs to a specified location.
RMAN> backup archivelog all;
RMAN> backup archivelog from time 'sysdate-30' until time 'sysdate-7';
RMAN> backup archivelog from logseq=XXX until logseq=YYY delete input format '/backups/PROD/%d_archive_%T_%u_s%s_p%p';
• Backing up the Whole database including archivelogs
Below is an example of how the whole database can be backed up and at the same time backup the archive logs and purge them following a successful backup. The first example backups up to the FRA, however it you wish to redirect the output the second command shows how this is achieved.
RMAN> backup database plus archivelog delete input;
RMAN> backup database plus archivelog delete input format '/backups/PROD/df_t%t_s%s_p%p';
Modified 08-JUN-2010 Type HOWTO Status PUBLISHED
In this Document
Goal
Solution
Applies to:
Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.1 - Release: 10.1 to 10.2
Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.5.0 [Release: 10.1 to 10.2]
Information in this document applies to any platform.
Goal
Audience: Novice RMAN users.
The following note provides a DBA with several RMAN sample backup scripts. The scripts are very basic and an be executed as shown in examples.
Solution
RMAN - Sample Backup Scripts 10g
• Backup up Whole Database Backups with RMAN
• Backing up Individual Tablespaces with RMAN
• Backing up Individual Datafiles and Datafile Copies with RMAN
• Backing up Control Files with RMAN
• Backing up Server Parameter Files with RMAN
• Backing up Archived Redo Logs with RMAN
• Backing up the Whole database including archivelogs
=====================================================================================
Making Whole Database Backups with RMAN
You can perform whole database backups with the database mounted or open. To perform a whole database backup from the RMAN prompt the BACKUP DATABASE command can be used. The simplest form of the command requires no parameters, as shown in this example:
RMAN> backup database;
In the following example no backup location was specified meaning that the backups will automatically be placed in the Flash Recovery Area (FRA). If the FRA has not been setup then all backups default to $ORACLE_HOME/dbs.
How to check if the RFA has been setup:
SQL> show parameter recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /recovery_area
db_recovery_file_dest_size big integer 50G
If your FRA is not setup (ie values are null) please refer to the following note for assistance in setting it up.
Note 305648.1 What is a Flash Recovery Area and how to configure it ?
If you wish to place your backup outside the FRA then following RMAN syntax may be used.
RMAN> backup database format '/backups/PROD/df_t%t_s%s_p%p';
• Backing Up Individual Tablespaces with RMAN
RMAN allows individual tablespaces to be backed up with the database in open or mount stage.
RMAN> backup tablespace SYSTEM, UNDOTBS, USERS;
• Backing Up Individual Datafiles and Datafile Copies with RMAN
The flexibilty of being able to backup a single datafile is also available. As seen below you are able to reference the datafile via the file# or file name. Multiple datafiles can be backed up at a time.
RMAN> backup datafile 2;
RMAN> backup datafile 2 format '/backups/PROD/df_t%t_s%s_p%p';
RMAN> backup datafile 1,2,3,6,7,8;
RMAN> backup datafile '/oradata/system01.dbf';
• Backing Up the current controlfile & Spfile
The controlfile and spfile are backed up in similar ways. Whenever a full database backup if performed, the controlfile and spfile are backed up. In fact whenever file#1 is backed up these two files are backed up also.
It is also good practise to backup the controlfile especially after tablespaces and datafiles have been added or deleted.
If you are not using an RMAN catalog it is more impotant that you frequently backup of your controlfile. You can also configure another method of controlfile backup which is referred to as 'autobackup of controlfile'.
Refer to the manual for more information regarding this feature.
RMAN> backup current controlfile;
RMAN> backup current controlfile format '/backups/PROD/df_t%t_s%s_p%p';
RMAN> backup spfile;
• Backing Up Archivelogs
It is important that archivelogs are backed up in a timely manner and correctly removed to ensure the file system does not fill up. Below are a few different examples. Option one backs up all archive logs to the FRA or default location. Option two backs up all archivelogs generate between 7 and 30 days and option three backs up archive logs from log sequence number XXX until logseq YYY then deletes the archivelogs. It also backups the archive logs to a specified location.
RMAN> backup archivelog all;
RMAN> backup archivelog from time 'sysdate-30' until time 'sysdate-7';
RMAN> backup archivelog from logseq=XXX until logseq=YYY delete input format '/backups/PROD/%d_archive_%T_%u_s%s_p%p';
• Backing up the Whole database including archivelogs
Below is an example of how the whole database can be backed up and at the same time backup the archive logs and purge them following a successful backup. The first example backups up to the FRA, however it you wish to redirect the output the second command shows how this is achieved.
RMAN> backup database plus archivelog delete input;
RMAN> backup database plus archivelog delete input format '/backups/PROD/df_t%t_s%s_p%p';
Backup and Recovery Scenarios
Backup and Recovery Scenarios [ID 94114.1]
Modified 01-MAR-2011 Type TROUBLESHOOTING Status PUBLISHED
In this Document
Purpose
Last Review Date
Instructions for the Reader
Troubleshooting Details
BACKUP SCENARIOS
a) Consistent backups
b) Inconsistent backups
c) Database Archive mode
d) Backup Methods
e) Incremental backups
f) Support scenarios
RECOVERY SCENARIOS
1. Online Block Recovery.
2. Thread Recovery.
3. Media Recovery.
Media Failure and Recovery in Noarchivelog Mode
Media Failure and Recovery in Archivelog Mode
a) Point in Time recovery:
b) Recovery without control file
c) Recovery of missing datafile with rollback segments
d) Recovery of missing datafile without undo segments
e) Recovery with missing online redo logs
f) Recovery with missing archived redo logs
g) Recovery with resetlogs option
h) Recovery with corrupted undo segments.
i) Recovery with System Clock change.
j) Recovery with missing System tablespace.
k) Media Recovery of offline tablespace
l) Recovery of Read-Only tablespaces
References
Applies to:
Oracle Server - Personal Edition - Version: 7.2.3.0 to 10.2.0.4 - Release: 7.2.3 to 10.2
Oracle Server - Enterprise Edition - Version: 7.3.4.5 to 10.2.0.4 [Release: 7.3.4 to 10.2]
Oracle Server - Standard Edition - Version: 7.2.2.0 to 10.2.0.4 [Release: 7.2.2 to 10.2]
Information in this document applies to any platform.
***Checked for relevance on 01-Mar-2011***
Purpose
Describe various Backup and Recovery Scenarios.
Last Review Date
November 27, 2006
Instructions for the Reader
A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.
Troubleshooting Details
BACKUP SCENARIOS
a) Consistent backups
A consistent backup means that all data files and control files are consistent to a point in time. I.e. they have the same SCN. This is the only method of backup when the database is in NO Archive log mode.
b) Inconsistent backups
An Inconsistent backup is possible only when the database is in Archivelog mode. You must apply redo logs to the data files, in order to restore the database to a consistent state. Inconsistant backups can be taken using RMANwhen the database is open.
Inconsistant backups can also be taken using other OS tools provided the tablespaces (or database) is put into backup mode.
ie: SQL> alter tablespace data begin backup;
SQL> alter database begin backup; (version 10 and above only)
c) Database Archive mode
The database can run in either Archivelog mode or noarchivelog mode. When you first create the database, you specify if it is to be in Archivelog mode. Then in the init.ora file you set the parameter log_archive_start=true so that archiving will start automatically on startup.
If the database has not been created with Archivelog mode enabled, you can issue the command whilst the database is mounted, not open.
SQL> alter database Archivelog;.
SQL> log archive start
SQL> alter database open;
SQL> archive log list
This command will show you the log mode and if automatic archival is set.
d) Backup Methods
Essentially, there are two backup methods, hot and cold, also known as online and offline, respectively. A cold backup is one taken when the database is shutdown. The database must be shutdown cleanly. A hot backup is on taken when the database is running. Commands for a hot backup:
For non RMAN backups:
1. Have the database in archivelog mode (see above)
2. SQL> archive log list
--This will show what the oldest online log sequence is. As a precaution, always keep the all archived log files starting from the oldest online log sequence.
3. SQL> Alter tablespace tablespace_name BEGIN BACKUP;
or SQL> alter database begin backup (for v10 and above).
4. --Using an OS command, backup the datafile(s) of this tablespace.
5. SQL> Alter tablespace tablespace_name END BACKUP
--- repeat step 3, 4, 5 for each tablespace.
or SQL> alter database end backup; for version 10 and above
6. SQL> archive log list
---do this again to obtain the current log sequence. You will want to make sure you have a copy of this redo log file.
7. So to force an archived log, issue
SQL> ALTER SYSTEM SWITCH LOGFILE
A better way to force this would be:
SQL> alter system archive log current;
8. SQL> archive log list
This is done again to check if the log file had been archived and to find the latest archived sequence number.
9. Backup all archived log files determined from steps 2 and 8.
10. Back up the control file:
SQL> Alter database backup controlfile to 'filename'
For RMAN backups:
see Note.<<397315.1>> RMAN - Sample Backup Scripts 10g
or the appropriate RMAN documentation.
e) Incremental backups
These are backups that are taken on blocks that have been modified since the last backup. These are useful as they don't take up as much space and time. There are two kinds of incremental backups Cumulative and Non cumulative.
Cumulative incremental backups include all blocks that were changed since the last backup at a lower level. This one reduces the work during restoration as only one backup contains all the changed blocks.
Noncumulative only includes blocks that were changed since the previous backup at the same or lower level.
Using rman, you issue the command "backup incremental level n"
Oracle v9 and below RMAN will back up empty blocks, oracle v10.2 RMAN will not back up empty blocks
f) Support scenarios
When the database crashes, you now have a backup. You restore the backup and
then recover the database. Also, don't forget to take a backup of the control
file whenever there is a schema change.
RECOVERY SCENARIOS
Note: All online datafiles must be at the same point in time when completing recovery;
There are several kinds of recovery you can perform, depending on the type of failure and the kind of backup you have. Essentially, if you are not running in archive log mode, then you can only recover the cold backup of the database and you will lose any new data and changes made since that backup was taken. If, however, the database is in Archivelog mode you will be able to restore the database up to the time of failure. There are three basic types of recovery:
1. Online Block Recovery.
This is performed automatically by Oracle.(pmon) Occurs when a process dies while changing a buffer. Oracle will reconstruct the buffer using the online redo logs and writes it to disk.
2. Thread Recovery.
This is also performed automatically by Oracle. Occurs when an instance crashes while having the database open. Oracle applies all the redo changes in the thread that occurred since the last time the thread was checkpointed.
3. Media Recovery.
This is required when a data file is restored from backup. The checkpoint count in the data files here are not equal to the check point count in the control file.
Now let's explain a little about Redo vs Undo.
Redo information is recorded so that all commands that took place can be repeated during recovery. Undo information is recorded so that you can undo changes made by the current transaction but were not committed. The Redo Logs are used to Roll Forward the changes made, both committed and non- committed changes. Then from the Undo segments, the undo information is used to
rollback the uncommitted changes.
Media Failure and Recovery in Noarchivelog Mode
In this case, your only option is to restore a backup of your Oracle files. The files you need are all datafiles, and control files. You only need to restore the password file or parameter files if they are lost or are corrupted.
Media Failure and Recovery in Archivelog Mode
In this case, there are several kinds of recovery you can perform, depending on what has been lost. The three basic kinds of recovery are:
1. Recover database - here you use the recover database command and the database must be closed and mounted. Oracle will recover all datafiles that are online.
2. Recover tablespace - use the recover tablespace command. The database can be open but the tablespace must be offline.
3. Recover datafile - use the recover datafile command. The database can be open but the specified datafile must be offline.
Note: You must have all archived logs since the backup you restored from, or else you will not have a complete recovery.
a) Point in Time recovery:
A typical scenario is that you dropped a table at say noon, and want to recover it. You will have to restore the appropriate datafiles and do a point-in-time recovery to a time just before noon.
Note: you will lose any transactions that occurred after noon. After you have recovered until noon, you must open the database with resetlogs. This is necessary to reset the log numbers, which will protect the database from having the redo logs that weren't used be applied.
The four incomplete recovery scenarios all work the same:
Recover database until time '1999-12-01:12:00:00';
Recover database until cancel; (you type in cancel to stop)
Recover database until change n;
Recover database until cancel using backup controlfile;
Note: When performing an incomplete recovery, the datafiles must be online. Do a select * from v$recover_file to find out if there are any files which are offline. If you were to perform a recovery on a database which has tablespaces offline, and they had not been taken offline in a normal state, you will lose them when you issue the open resetlogs command. This is because the data file needs recovery from a point before the resetlogs option was used.
b) Recovery without control file
If you have lost the current control file, or the current control file is inconsistent with files that you need to recover, you need to recover either by using a backup control file command or create a new control file. You can also recreate the control file based on the current one using the 'SQL> backup control file to trace' command which will create a script for you to run to create a new one. Recover database using backup control file command must be used when using a control file other that the current. The database must then be opened with
resetlogs option.
c) Recovery of missing datafile with rollback segments
The tricky part here is if you are performing online recovery. Otherwise you can just use the recover datafile command. Now, if you are performing an online recovery, you will need to create a new undo tablespace to be used. Once the old tablespace has been recovered it can be dropped once any uncommitted transactions have rolled back.
d) Recovery of missing datafile without undo segments
There are three ways to recover in this scenario, as mentioned above.
1. recover database;
2. recover datafile 'c:\orant\database\usr1orcl.ora';
3. recover tablespace user_data;
e) Recovery with missing online redo logs
Missing online redo logs means that somehow you have lost your redo logs before they had a chance to archived. This means that crash recovery cannot be performed, so media recovery is required instead. All datafiles will need to be restored and rolled forwarded until the last available archived log file is applied. This is thus an incomplete recovery, and as such, the recover
database command is necessary.
As always, when an incomplete recovery is performed, you must open the database with resetlogs.
Note: the best way to avoid this kind of a loss, is to mirror your online log files.
f) Recovery with missing archived redo logs
If your archives are missing, the only way to recover the database is to restore from your latest backup. You will have lost any uncommitted
transactions which were recorded in the archived redo logs. Again, this is why Oracle strongly suggests mirroring your online redo logs and duplicating copies of the archives.
g) Recovery with resetlogs option
Reset log option should be the last resort, however, as we have seen from above, it may be required due to incomplete recoveries. (recover using a backup control file, or a point in time recovery). It is imperative that you backup up the database immediately after you have opened the database with reset logs. It is possible to recover through a resetlogs, and made easier with Oracle V10, but easier
to restore from the backup taken after the resetlogs
h) Recovery with corrupted undo segments.
If an undo segment is corrupted, and contains uncommitted system data you may not be able to open the database.
The best alternative in this situation is to recover the corrupt block using the RMAN blockrecover command next best would be to restore the datafile from backup and do a complete recovery.
If a backup does not exist and If the database is able to open (non system object) The first step is to find out what object is causing the rollback to appear corrupted. If we can determine that, we can drop that object.
So, how do we find out if it's actually a bad object?
1. Make sure that all tablespaces are online and all datafiles are online. This can be checked through via the v$recover_file view.
2. Put the following in the init.ora:
event = "10015 trace name context forever, level 10"
This event will generate a trace file that will reveal information about the transaction Oracle is trying to roll back and most importantly, what object Oracle is trying to apply the undo to.
Note: In Oracle v9 and above this information can be found in the alert log.
Stop and start the database.
3. Check in the directory that is specified by the user_dump_dest parameter (in the init.ora or show parameter command) for a trace file that was generated at startup time.
4. In the trace file, there should be a message similar to: error recovery tx(#,#) object #.
TX(#,#) refers to transaction information.
The object # is the same as the object_id in sys.dba_objects.
5. Use the following query to find out what object Oracle is trying to perform recovery on.
select owner, object_name, object_type, status
from dba_objects where object_id =
Modified 01-MAR-2011 Type TROUBLESHOOTING Status PUBLISHED
In this Document
Purpose
Last Review Date
Instructions for the Reader
Troubleshooting Details
BACKUP SCENARIOS
a) Consistent backups
b) Inconsistent backups
c) Database Archive mode
d) Backup Methods
e) Incremental backups
f) Support scenarios
RECOVERY SCENARIOS
1. Online Block Recovery.
2. Thread Recovery.
3. Media Recovery.
Media Failure and Recovery in Noarchivelog Mode
Media Failure and Recovery in Archivelog Mode
a) Point in Time recovery:
b) Recovery without control file
c) Recovery of missing datafile with rollback segments
d) Recovery of missing datafile without undo segments
e) Recovery with missing online redo logs
f) Recovery with missing archived redo logs
g) Recovery with resetlogs option
h) Recovery with corrupted undo segments.
i) Recovery with System Clock change.
j) Recovery with missing System tablespace.
k) Media Recovery of offline tablespace
l) Recovery of Read-Only tablespaces
References
Applies to:
Oracle Server - Personal Edition - Version: 7.2.3.0 to 10.2.0.4 - Release: 7.2.3 to 10.2
Oracle Server - Enterprise Edition - Version: 7.3.4.5 to 10.2.0.4 [Release: 7.3.4 to 10.2]
Oracle Server - Standard Edition - Version: 7.2.2.0 to 10.2.0.4 [Release: 7.2.2 to 10.2]
Information in this document applies to any platform.
***Checked for relevance on 01-Mar-2011***
Purpose
Describe various Backup and Recovery Scenarios.
Last Review Date
November 27, 2006
Instructions for the Reader
A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.
Troubleshooting Details
BACKUP SCENARIOS
a) Consistent backups
A consistent backup means that all data files and control files are consistent to a point in time. I.e. they have the same SCN. This is the only method of backup when the database is in NO Archive log mode.
b) Inconsistent backups
An Inconsistent backup is possible only when the database is in Archivelog mode. You must apply redo logs to the data files, in order to restore the database to a consistent state. Inconsistant backups can be taken using RMANwhen the database is open.
Inconsistant backups can also be taken using other OS tools provided the tablespaces (or database) is put into backup mode.
ie: SQL> alter tablespace data begin backup;
SQL> alter database begin backup; (version 10 and above only)
c) Database Archive mode
The database can run in either Archivelog mode or noarchivelog mode. When you first create the database, you specify if it is to be in Archivelog mode. Then in the init.ora file you set the parameter log_archive_start=true so that archiving will start automatically on startup.
If the database has not been created with Archivelog mode enabled, you can issue the command whilst the database is mounted, not open.
SQL> alter database Archivelog;.
SQL> log archive start
SQL> alter database open;
SQL> archive log list
This command will show you the log mode and if automatic archival is set.
d) Backup Methods
Essentially, there are two backup methods, hot and cold, also known as online and offline, respectively. A cold backup is one taken when the database is shutdown. The database must be shutdown cleanly. A hot backup is on taken when the database is running. Commands for a hot backup:
For non RMAN backups:
1. Have the database in archivelog mode (see above)
2. SQL> archive log list
--This will show what the oldest online log sequence is. As a precaution, always keep the all archived log files starting from the oldest online log sequence.
3. SQL> Alter tablespace tablespace_name BEGIN BACKUP;
or SQL> alter database begin backup (for v10 and above).
4. --Using an OS command, backup the datafile(s) of this tablespace.
5. SQL> Alter tablespace tablespace_name END BACKUP
--- repeat step 3, 4, 5 for each tablespace.
or SQL> alter database end backup; for version 10 and above
6. SQL> archive log list
---do this again to obtain the current log sequence. You will want to make sure you have a copy of this redo log file.
7. So to force an archived log, issue
SQL> ALTER SYSTEM SWITCH LOGFILE
A better way to force this would be:
SQL> alter system archive log current;
8. SQL> archive log list
This is done again to check if the log file had been archived and to find the latest archived sequence number.
9. Backup all archived log files determined from steps 2 and 8.
10. Back up the control file:
SQL> Alter database backup controlfile to 'filename'
For RMAN backups:
see Note.<<397315.1>> RMAN - Sample Backup Scripts 10g
or the appropriate RMAN documentation.
e) Incremental backups
These are backups that are taken on blocks that have been modified since the last backup. These are useful as they don't take up as much space and time. There are two kinds of incremental backups Cumulative and Non cumulative.
Cumulative incremental backups include all blocks that were changed since the last backup at a lower level. This one reduces the work during restoration as only one backup contains all the changed blocks.
Noncumulative only includes blocks that were changed since the previous backup at the same or lower level.
Using rman, you issue the command "backup incremental level n"
Oracle v9 and below RMAN will back up empty blocks, oracle v10.2 RMAN will not back up empty blocks
f) Support scenarios
When the database crashes, you now have a backup. You restore the backup and
then recover the database. Also, don't forget to take a backup of the control
file whenever there is a schema change.
RECOVERY SCENARIOS
Note: All online datafiles must be at the same point in time when completing recovery;
There are several kinds of recovery you can perform, depending on the type of failure and the kind of backup you have. Essentially, if you are not running in archive log mode, then you can only recover the cold backup of the database and you will lose any new data and changes made since that backup was taken. If, however, the database is in Archivelog mode you will be able to restore the database up to the time of failure. There are three basic types of recovery:
1. Online Block Recovery.
This is performed automatically by Oracle.(pmon) Occurs when a process dies while changing a buffer. Oracle will reconstruct the buffer using the online redo logs and writes it to disk.
2. Thread Recovery.
This is also performed automatically by Oracle. Occurs when an instance crashes while having the database open. Oracle applies all the redo changes in the thread that occurred since the last time the thread was checkpointed.
3. Media Recovery.
This is required when a data file is restored from backup. The checkpoint count in the data files here are not equal to the check point count in the control file.
Now let's explain a little about Redo vs Undo.
Redo information is recorded so that all commands that took place can be repeated during recovery. Undo information is recorded so that you can undo changes made by the current transaction but were not committed. The Redo Logs are used to Roll Forward the changes made, both committed and non- committed changes. Then from the Undo segments, the undo information is used to
rollback the uncommitted changes.
Media Failure and Recovery in Noarchivelog Mode
In this case, your only option is to restore a backup of your Oracle files. The files you need are all datafiles, and control files. You only need to restore the password file or parameter files if they are lost or are corrupted.
Media Failure and Recovery in Archivelog Mode
In this case, there are several kinds of recovery you can perform, depending on what has been lost. The three basic kinds of recovery are:
1. Recover database - here you use the recover database command and the database must be closed and mounted. Oracle will recover all datafiles that are online.
2. Recover tablespace - use the recover tablespace command. The database can be open but the tablespace must be offline.
3. Recover datafile - use the recover datafile command. The database can be open but the specified datafile must be offline.
Note: You must have all archived logs since the backup you restored from, or else you will not have a complete recovery.
a) Point in Time recovery:
A typical scenario is that you dropped a table at say noon, and want to recover it. You will have to restore the appropriate datafiles and do a point-in-time recovery to a time just before noon.
Note: you will lose any transactions that occurred after noon. After you have recovered until noon, you must open the database with resetlogs. This is necessary to reset the log numbers, which will protect the database from having the redo logs that weren't used be applied.
The four incomplete recovery scenarios all work the same:
Recover database until time '1999-12-01:12:00:00';
Recover database until cancel; (you type in cancel to stop)
Recover database until change n;
Recover database until cancel using backup controlfile;
Note: When performing an incomplete recovery, the datafiles must be online. Do a select * from v$recover_file to find out if there are any files which are offline. If you were to perform a recovery on a database which has tablespaces offline, and they had not been taken offline in a normal state, you will lose them when you issue the open resetlogs command. This is because the data file needs recovery from a point before the resetlogs option was used.
b) Recovery without control file
If you have lost the current control file, or the current control file is inconsistent with files that you need to recover, you need to recover either by using a backup control file command or create a new control file. You can also recreate the control file based on the current one using the 'SQL> backup control file to trace' command which will create a script for you to run to create a new one. Recover database using backup control file command must be used when using a control file other that the current. The database must then be opened with
resetlogs option.
c) Recovery of missing datafile with rollback segments
The tricky part here is if you are performing online recovery. Otherwise you can just use the recover datafile command. Now, if you are performing an online recovery, you will need to create a new undo tablespace to be used. Once the old tablespace has been recovered it can be dropped once any uncommitted transactions have rolled back.
d) Recovery of missing datafile without undo segments
There are three ways to recover in this scenario, as mentioned above.
1. recover database;
2. recover datafile 'c:\orant\database\usr1orcl.ora';
3. recover tablespace user_data;
e) Recovery with missing online redo logs
Missing online redo logs means that somehow you have lost your redo logs before they had a chance to archived. This means that crash recovery cannot be performed, so media recovery is required instead. All datafiles will need to be restored and rolled forwarded until the last available archived log file is applied. This is thus an incomplete recovery, and as such, the recover
database command is necessary.
As always, when an incomplete recovery is performed, you must open the database with resetlogs.
Note: the best way to avoid this kind of a loss, is to mirror your online log files.
f) Recovery with missing archived redo logs
If your archives are missing, the only way to recover the database is to restore from your latest backup. You will have lost any uncommitted
transactions which were recorded in the archived redo logs. Again, this is why Oracle strongly suggests mirroring your online redo logs and duplicating copies of the archives.
g) Recovery with resetlogs option
Reset log option should be the last resort, however, as we have seen from above, it may be required due to incomplete recoveries. (recover using a backup control file, or a point in time recovery). It is imperative that you backup up the database immediately after you have opened the database with reset logs. It is possible to recover through a resetlogs, and made easier with Oracle V10, but easier
to restore from the backup taken after the resetlogs
h) Recovery with corrupted undo segments.
If an undo segment is corrupted, and contains uncommitted system data you may not be able to open the database.
The best alternative in this situation is to recover the corrupt block using the RMAN blockrecover command next best would be to restore the datafile from backup and do a complete recovery.
If a backup does not exist and If the database is able to open (non system object) The first step is to find out what object is causing the rollback to appear corrupted. If we can determine that, we can drop that object.
So, how do we find out if it's actually a bad object?
1. Make sure that all tablespaces are online and all datafiles are online. This can be checked through via the v$recover_file view.
2. Put the following in the init.ora:
event = "10015 trace name context forever, level 10"
This event will generate a trace file that will reveal information about the transaction Oracle is trying to roll back and most importantly, what object Oracle is trying to apply the undo to.
Note: In Oracle v9 and above this information can be found in the alert log.
Stop and start the database.
3. Check in the directory that is specified by the user_dump_dest parameter (in the init.ora or show parameter command) for a trace file that was generated at startup time.
4. In the trace file, there should be a message similar to: error recovery tx(#,#) object #.
TX(#,#) refers to transaction information.
The object # is the same as the object_id in sys.dba_objects.
5. Use the following query to find out what object Oracle is trying to perform recovery on.
select owner, object_name, object_type, status
from dba_objects where object_id =
Best Practice For Hyperion Enterprise Backup and Virus Scanning.
Best Practice For Hyperion Enterprise Backup and Virus Scanning. [ID 1100507.1]
Modified 21-MAY-2010 Type HOWTO Status PUBLISHED
In this Document
Goal
Solution
Applies to:
Hyperion Enterprise - Version: 6.3.1.0.00 and later [Release: 6.3 and later ]
Information in this document applies to any platform.
Goal
Back-up guidelines for backing up an Oracle Hyperion Enterprise Application where Internal back-up is not used or in version where it is not available.
Virus Scanning best practice for Hyperion Enterprise Applications.
Solution
Best Practices for backing up an Hyperion Enterprise Application.
Terminology.
These notes use the term “Application Folder” (App Folder) to describe the Enterprise Database folder containing the Database files.
This is not the same as the “Software Folder” which contains the Executable code and associated DLL files etc
Note : the App Folder may be a sub folder of the Software Folder, please be aware of this. The location of both these folders are user definable at install/app creation time.
The default Software Folder being C:\Progam files\Hyperion solutions\Hyperion Enterprise.
Application Name (APPNAME) this is the “name “ of a particular Enterprise Database created by the user..
To identify your Software Folder, search for the file Hypent.exe, your should only have one copy, resident in your Software Folder.
The App Folder contains the following files amongst other.
APPNAME.EXA
APPNAME.INI
APPNAME.lok
APPNAME.use
It also has sub folders
\data
\reports
\inbox
\outbox
The App folder is again user definable but, the above sub folders are the default sub folder names.
The Application folder is defined by the APPPATH parameter in your Hypent.ini under each [APPNAME] section. You will find the hypent.ini in the citrix home folder or user profile folder.
Note the hypent.ini may contain several Application defined by the appropriate [APPNAME] sections.
BACKING UP
Always ensure there are no users logged on to any Enterprise or Reporting modeule when the back-up kicks off. Set a Back-Up Window (time) in which users know not to log on to the application while the back-up takes place.
Always back-up entire APP FOLDER never parts of APP FOLDER
.
RESTORING
Before restoring a back-up, ensure users are not logged in to any Enterprise or Reporting module..
When restoring you have the option to either overwrite the present app folder (suggest you clear it first). If you do overwrite the users will be able to connect to the restore with no changes needed.
If you restore to a new App Folder , the users will have to use the “Add Application” option at their logon screen to locate the restored folder and then add the restored application. When they do this, the reference to that particular application is overwritten with the new location within hypent.ini.
Note you can only have one instance of an APPNAME in your hypent.ini file at any one time.
When the users add an APPNAME that is already there, they get a message warning this will Overwrite the Application. It is only the link via the hypent.ini file that gets overwritten and not the Application itself.
Never restore partial applications. ALWAYS THE COMPLETE APP FOLDER.
If the software folder is damaged, run the install program again and select the REPAIR OPTION or run a complete re-install. If you restore a Software Folder form back-up you may run into file registration issues.
VIRUS SCANNING
The APP FOLDER should not be scanned live scanned for viruses.
Exclude the APP FOLDERS form live virus scanning.
Run a full scan over the APP FOLDERS daily when users are not active.
The danger is that the virus scanning software locks a file while scanning, and Enterprise cannot then lock the file itself.
Make sure users do not store office documents in the APP FOLDER as these can harbor viruses and need to be live scanned.
.
VERIONS 65 AND ABOVE
Enterprise version 6.5.x and 6,5,1.x include an internal back-up routine which does allow back-ups to be taken with active users.
This functionality was not available in earlier versions
Modified 21-MAY-2010 Type HOWTO Status PUBLISHED
In this Document
Goal
Solution
Applies to:
Hyperion Enterprise - Version: 6.3.1.0.00 and later [Release: 6.3 and later ]
Information in this document applies to any platform.
Goal
Back-up guidelines for backing up an Oracle Hyperion Enterprise Application where Internal back-up is not used or in version where it is not available.
Virus Scanning best practice for Hyperion Enterprise Applications.
Solution
Best Practices for backing up an Hyperion Enterprise Application.
Terminology.
These notes use the term “Application Folder” (App Folder) to describe the Enterprise Database folder containing the Database files.
This is not the same as the “Software Folder” which contains the Executable code and associated DLL files etc
Note : the App Folder may be a sub folder of the Software Folder, please be aware of this. The location of both these folders are user definable at install/app creation time.
The default Software Folder being C:\Progam files\Hyperion solutions\Hyperion Enterprise.
Application Name (APPNAME) this is the “name “ of a particular Enterprise Database created by the user..
To identify your Software Folder, search for the file Hypent.exe, your should only have one copy, resident in your Software Folder.
The App Folder contains the following files amongst other.
APPNAME.EXA
APPNAME.INI
APPNAME.lok
APPNAME.use
It also has sub folders
\data
\reports
\inbox
\outbox
The App folder is again user definable but, the above sub folders are the default sub folder names.
The Application folder is defined by the APPPATH parameter in your Hypent.ini under each [APPNAME] section. You will find the hypent.ini in the citrix home folder or user profile folder.
Note the hypent.ini may contain several Application defined by the appropriate [APPNAME] sections.
BACKING UP
Always ensure there are no users logged on to any Enterprise or Reporting modeule when the back-up kicks off. Set a Back-Up Window (time) in which users know not to log on to the application while the back-up takes place.
Always back-up entire APP FOLDER never parts of APP FOLDER
.
RESTORING
Before restoring a back-up, ensure users are not logged in to any Enterprise or Reporting module..
When restoring you have the option to either overwrite the present app folder (suggest you clear it first). If you do overwrite the users will be able to connect to the restore with no changes needed.
If you restore to a new App Folder , the users will have to use the “Add Application” option at their logon screen to locate the restored folder and then add the restored application. When they do this, the reference to that particular application is overwritten with the new location within hypent.ini.
Note you can only have one instance of an APPNAME in your hypent.ini file at any one time.
When the users add an APPNAME that is already there, they get a message warning this will Overwrite the Application. It is only the link via the hypent.ini file that gets overwritten and not the Application itself.
Never restore partial applications. ALWAYS THE COMPLETE APP FOLDER.
If the software folder is damaged, run the install program again and select the REPAIR OPTION or run a complete re-install. If you restore a Software Folder form back-up you may run into file registration issues.
VIRUS SCANNING
The APP FOLDER should not be scanned live scanned for viruses.
Exclude the APP FOLDERS form live virus scanning.
Run a full scan over the APP FOLDERS daily when users are not active.
The danger is that the virus scanning software locks a file while scanning, and Enterprise cannot then lock the file itself.
Make sure users do not store office documents in the APP FOLDER as these can harbor viruses and need to be live scanned.
.
VERIONS 65 AND ABOVE
Enterprise version 6.5.x and 6,5,1.x include an internal back-up routine which does allow back-ups to be taken with active users.
This functionality was not available in earlier versions
Archiver Best Practices
Archiver Best Practices [ID 45042.1]
Modified 28-JUN-2009 Type FAQ Status PUBLISHED
"Checked for relevance on 28-Jun-2009"
Document ID: 12004524.61
Title: Archiver Best Practices
Creation Date: 08-May-1996
Last Revision Date: 08-May-1996
Revision Number: 0
Product: RDBMS
Product Version: Oracle7
Platform: GENERIC
Information Type: ADVISORY
Impact: MEDIUM
Abstract: Describes archiver functionality and some of the best
practices in configuring and tuning archiver.
Keywords: ARCHIVER;PRACTICES;ARCH;ORACLE7;BACKUP:RECOVERY
________________________________________________________________________________
Archiver Best Practices
INTRODUCTION:
Archiving provides the mechanism needed to backup the changes of the database.
The archive files are essential in providing the necessary information to
recover a database. However, as transaction rates increase, we are facing
more and more problems in devising an efficient archiving strategy that does
not impede with database performance which also accomodates your MTTR
(Mean Time to Recover) service levels. This paper describes some of the best
practices to help tune archiver and provides suggestions in preventing
archiving outages.
ONLINE REDO LOG AND ARCHIVE FILE CONFIGURATION:
Since archiver reads from log files, we also need to configured log files
properly to tune archiving. Log files are primary written by log writer
(LGWR) and should be read by archiver (ARCH) or any process doing recovery.
The disks are write intensive and at times read intensive but there is generally
low concurrency. We suggest that log files should be Oracle multiplexed or
hardware mirrored. They should never be placed on the same disks as the
archive files. Ideally, they should be located in their own set of disks
separated from all other files. The members or mirrored files should be on
different disks and controllers to prevent any single point of failure and
to increase thoroughput. Due to its IO behavior and importance, log files
ideally should be on raw disks with a recommended RAID 0+1 configuration
(mirroring and fine grain striping). Striping introduces parallelism to the
disk writes, thus, it could speed up sequential writes by increasing the
amount of data whose write would complete in a quantum of time.
The Archive files are always on ufs (UNIX file systems) with ideally a
RAID 0+1 configuration. Again, fine grained striping whenever archives are
on dedicated disks is recommended. Archives should always be separated from
the log files.
ARCHIVER STEPS:
Generically, archiver will
1) read the control file to find any unarchived logs,
2) open online redo log members to be read,
3) allocate redo log buffers (log_archive_buffers),
4) (async) read of the online redo log (log_archive_buffer_size),
usually aioread call if supported by operating system
uses alternative redo log members per buffer stream
5) fill redo log buffers,
6) (async) write of to the archive files (log_archive_buffer_size),
creates ufs archive file if not already created
first checks if buffer is full or if end of log
makes aiowrite call if supported by operating system
7) update control file with new information,
modify the archive log link list and change redo log statuses
8) starts the loop again.
In Oracle Parallel Server, archiver can also prompt idle instances to archive
their logs to prevent any thread of redo from falling far behind. This is
often referred to as kicking an idle instance. This help ensures that online
redo logs are archived out from all enabled threads so that media recovery in
a standby database environment does not fall behind.
ARCHIVER TUNING:
When encountering archiver busy waits or checkpoint busy waits warnings in the
Alert file, one should:
1) evaluate the number and size of the online redo logs
Most often, increasing the size and the number of online redo log
groups will give archiver more time to catch up.
Adding more online logs does not help a situation where the archiver
cannot keep up with LGWR. It can help if there are bursts of redo
generation since it gives ARCH more time to average its processing
rate over time.
2) evaluate checkpoint interval and frequency
There are several possible actions include adding DBWR processes,
increasing db_block_checkpoint_batch, reducing db_block_buffers.
Turning on or allowing async IO capabilities definitely helps
alleviate most DBWR inefficiencies.
3) consider adding multiple archiver processes
Create 'alter system archive log all' scripts to spawn archive
processes at some fixed interval may be required. These processes
once spawned will assist archiver in archiving any unarchived log in
that thread of redo. Once it has been completed, the temporary
processes will go away.
4) tune archiver process
change log_archive_buffer_size (max 128 in some ports)
change log_archive_buffer (max 8 in some ports)
In many platforms, a patch needs to be applied to increase
these values. In some ports, 7.3.2 fixes this limitation.
5) check operating system supportability of async IO
Async reads should help tremendously.
Async writes may help if OS supports aio on file systems.
Please check with your vendor if the current version of
your operating system supports async IO to file systems (ufs).
6) Check for system or IO contention.
Check queue lengths, CPU waits and usage, disk/channel/controller
level bottlenecks. Please check operating system manuals for the
appropriate commands to monitor system performance. For example,
some UNIX ports can use "sar -d" and "iostat" to identify disk
bottlenecks.
It is common for environments where there is intensive batch processing to
see ARCH fall behind of LGWR. In those cases, you should review the above
suggestions. In many cases increasing the number and/or size of log groups as
well as spawning extra archive processes is the most sufficient solution.
ARCHIVING STRATEGY:
There are three primary goals:
* Ensure that all online redo logs are archived and backed up
successfully.
* Prevent any archiver busy waits.
* Keep all archives on disk from last database backup to reduce
recovery time.
Ensure that all online redo logs are archived and backed up
-----------------------------------------------------------
To accomplish this first goal, one needs to monitor the database, archiver
progress (by looking at V$LOG and archive trace files), archive destination,
and tape management procedure. You should never archive a log until the ARC
column in V$LOG is set to YES. Scripts can be written that log into the
database and query V$LOG to build a set of archives to write out to tape.
The tape management procedure should have checksums to ensure that the
archive file was successfully backed up. Error checking and a good reporting
tool are essential in detecting and resolving archiving and tape backup/restore
errors. In 7.3, Oracle provides a checksumming mechanism when copying redo
from the online redo log to the archive files. This new init.ora parameter
is called log_block_checksum.
Having multiple members to a logfile group is also advisable. If there
are multiple members, all members in a group are used to perform the
archive process. Assuming that there are three members, the first chunk
is read from one of the members and is being written to the archive while
reading a second chunk from second member, then a third chunk from third
member, then back to first and the process continues in round robin fashion.
If a corruption is found in one of the members, it is validated (read again)
and if the corruption still exists, reading from that member is stopped
and the rest are used.
This is one of the big benefits of using multiple members instead of
mirroring at o/s level. Oracle knows about the multiple members so it can
optimize on archiving, but it does not know about the mirrors. One other big
benefit with using multiple members is that a separate write is issued for
each member, so the odds of having a totally corrupted redo log is
diminished (corruption written to one mirror will usually propagate over to
all other copies).
Note: All archives from all enabled threads need to be backed up. If you have
an idle instance, it will still create archive header files that are essential
for media recovery. This applies to Oracle Parallel Server only.
Prevent any archiver busy waits
-------------------------------
To prevent archiver busy waits, the archiver should be tuned by adjusting
log_archive_buffers and log_archive_buffer_size. The tuning tips described
earlier in the paper should be followed.
Keep all archives on disk from last database backup
---------------------------------------------------
Keeping all archives on disk from last database backup will reduce recovery
time by bypassing the time required to restore the archives from tape. This
may reduce MTTR (Mean Time to Recover) dramatically.
You may be able to achieve this by creating several archive destinations
or having one large archive destination. For example, lets assume you have
several archive destinations. Archiver is writing to archive DEST 1. When
DEST 1 fills up to a certain threshold, say 80% (enough room for two more
archive files), you can switch the archive destination by issuing the
command, 'alter system archive log to 'DEST2'. Archiver will then archive
the NEXT log in the new destination.
Tape backups can occur in DEST1 while archiver is writing to DEST2. This
reduces IO contention for those disks. Furthermore, depending on the size of
the destination, you can optimally keep a large number of archives on disk.
Before switching back to DEST1, we can purge the archives in DEST 1 that
has been successfully backed up to tape.
Some sites have one very large archive destination instead of several archive
destinations. Again, scripts are created to monitor and to log in to the
database to determine which Oracle archives to back up to tape. These archives
are backed up as soon as possible. A purging algorithm is produce to purge
only those files that have been successfully backed up to tape and with a
timestamp that is older than the beginning of the last successful Oracle hot
backup. Unfortunately, there may be some additional disk contention with this
plan due to the IO concurrency from the archive process(es) and tape backup
process(es).
ARCHIVER MONITORING:
Other best practices include monitoring the statuses of log files to check for
STALE or INVALID logs. If the logs remain STALE, then you should investigate
any possible media problems and relocate or recreate new members to maintain
the level of resiliency for the logs. STALE logs imply that there are missing
writes in this log. Oracle considers incomplete logs as STALE; so, you
get STALE logs after a shutdown abort or if the LGWR process simply cannot
write to that redo log member. Archiver can easily detect if there are
missing changes in the redo log member by verifying the correctness of the
redo log block. If the archiver detects a problem, it will switch to another
member searching for a sound set of redo log blocks. The archiver will never
complain if it can create an "good" archive file from the composite information
of all the online redo log members.
If archiver falls behind often, then one can spawn extra archiver processes.
We recommend monitoring V$LOG to alert and spawn extra archiver processes
whenever there are more than 2 logs that need archiving.
Note: There is an enhancement, bug 260126, to allow for the ability to have
several archiver processes.
Checking the alert.log for archiver or checkpoint errors, archiver and
log writer background trace files for errors, and archive destination for lack
of free space are essential in catching most potential archiving related
problems
Modified 28-JUN-2009 Type FAQ Status PUBLISHED
"Checked for relevance on 28-Jun-2009"
Document ID: 12004524.61
Title: Archiver Best Practices
Creation Date: 08-May-1996
Last Revision Date: 08-May-1996
Revision Number: 0
Product: RDBMS
Product Version: Oracle7
Platform: GENERIC
Information Type: ADVISORY
Impact: MEDIUM
Abstract: Describes archiver functionality and some of the best
practices in configuring and tuning archiver.
Keywords: ARCHIVER;PRACTICES;ARCH;ORACLE7;BACKUP:RECOVERY
________________________________________________________________________________
Archiver Best Practices
INTRODUCTION:
Archiving provides the mechanism needed to backup the changes of the database.
The archive files are essential in providing the necessary information to
recover a database. However, as transaction rates increase, we are facing
more and more problems in devising an efficient archiving strategy that does
not impede with database performance which also accomodates your MTTR
(Mean Time to Recover) service levels. This paper describes some of the best
practices to help tune archiver and provides suggestions in preventing
archiving outages.
ONLINE REDO LOG AND ARCHIVE FILE CONFIGURATION:
Since archiver reads from log files, we also need to configured log files
properly to tune archiving. Log files are primary written by log writer
(LGWR) and should be read by archiver (ARCH) or any process doing recovery.
The disks are write intensive and at times read intensive but there is generally
low concurrency. We suggest that log files should be Oracle multiplexed or
hardware mirrored. They should never be placed on the same disks as the
archive files. Ideally, they should be located in their own set of disks
separated from all other files. The members or mirrored files should be on
different disks and controllers to prevent any single point of failure and
to increase thoroughput. Due to its IO behavior and importance, log files
ideally should be on raw disks with a recommended RAID 0+1 configuration
(mirroring and fine grain striping). Striping introduces parallelism to the
disk writes, thus, it could speed up sequential writes by increasing the
amount of data whose write would complete in a quantum of time.
The Archive files are always on ufs (UNIX file systems) with ideally a
RAID 0+1 configuration. Again, fine grained striping whenever archives are
on dedicated disks is recommended. Archives should always be separated from
the log files.
ARCHIVER STEPS:
Generically, archiver will
1) read the control file to find any unarchived logs,
2) open online redo log members to be read,
3) allocate redo log buffers (log_archive_buffers),
4) (async) read of the online redo log (log_archive_buffer_size),
usually aioread call if supported by operating system
uses alternative redo log members per buffer stream
5) fill redo log buffers,
6) (async) write of to the archive files (log_archive_buffer_size),
creates ufs archive file if not already created
first checks if buffer is full or if end of log
makes aiowrite call if supported by operating system
7) update control file with new information,
modify the archive log link list and change redo log statuses
8) starts the loop again.
In Oracle Parallel Server, archiver can also prompt idle instances to archive
their logs to prevent any thread of redo from falling far behind. This is
often referred to as kicking an idle instance. This help ensures that online
redo logs are archived out from all enabled threads so that media recovery in
a standby database environment does not fall behind.
ARCHIVER TUNING:
When encountering archiver busy waits or checkpoint busy waits warnings in the
Alert file, one should:
1) evaluate the number and size of the online redo logs
Most often, increasing the size and the number of online redo log
groups will give archiver more time to catch up.
Adding more online logs does not help a situation where the archiver
cannot keep up with LGWR. It can help if there are bursts of redo
generation since it gives ARCH more time to average its processing
rate over time.
2) evaluate checkpoint interval and frequency
There are several possible actions include adding DBWR processes,
increasing db_block_checkpoint_batch, reducing db_block_buffers.
Turning on or allowing async IO capabilities definitely helps
alleviate most DBWR inefficiencies.
3) consider adding multiple archiver processes
Create 'alter system archive log all' scripts to spawn archive
processes at some fixed interval may be required. These processes
once spawned will assist archiver in archiving any unarchived log in
that thread of redo. Once it has been completed, the temporary
processes will go away.
4) tune archiver process
change log_archive_buffer_size (max 128 in some ports)
change log_archive_buffer (max 8 in some ports)
In many platforms, a patch needs to be applied to increase
these values. In some ports, 7.3.2 fixes this limitation.
5) check operating system supportability of async IO
Async reads should help tremendously.
Async writes may help if OS supports aio on file systems.
Please check with your vendor if the current version of
your operating system supports async IO to file systems (ufs).
6) Check for system or IO contention.
Check queue lengths, CPU waits and usage, disk/channel/controller
level bottlenecks. Please check operating system manuals for the
appropriate commands to monitor system performance. For example,
some UNIX ports can use "sar -d" and "iostat" to identify disk
bottlenecks.
It is common for environments where there is intensive batch processing to
see ARCH fall behind of LGWR. In those cases, you should review the above
suggestions. In many cases increasing the number and/or size of log groups as
well as spawning extra archive processes is the most sufficient solution.
ARCHIVING STRATEGY:
There are three primary goals:
* Ensure that all online redo logs are archived and backed up
successfully.
* Prevent any archiver busy waits.
* Keep all archives on disk from last database backup to reduce
recovery time.
Ensure that all online redo logs are archived and backed up
-----------------------------------------------------------
To accomplish this first goal, one needs to monitor the database, archiver
progress (by looking at V$LOG and archive trace files), archive destination,
and tape management procedure. You should never archive a log until the ARC
column in V$LOG is set to YES. Scripts can be written that log into the
database and query V$LOG to build a set of archives to write out to tape.
The tape management procedure should have checksums to ensure that the
archive file was successfully backed up. Error checking and a good reporting
tool are essential in detecting and resolving archiving and tape backup/restore
errors. In 7.3, Oracle provides a checksumming mechanism when copying redo
from the online redo log to the archive files. This new init.ora parameter
is called log_block_checksum.
Having multiple members to a logfile group is also advisable. If there
are multiple members, all members in a group are used to perform the
archive process. Assuming that there are three members, the first chunk
is read from one of the members and is being written to the archive while
reading a second chunk from second member, then a third chunk from third
member, then back to first and the process continues in round robin fashion.
If a corruption is found in one of the members, it is validated (read again)
and if the corruption still exists, reading from that member is stopped
and the rest are used.
This is one of the big benefits of using multiple members instead of
mirroring at o/s level. Oracle knows about the multiple members so it can
optimize on archiving, but it does not know about the mirrors. One other big
benefit with using multiple members is that a separate write is issued for
each member, so the odds of having a totally corrupted redo log is
diminished (corruption written to one mirror will usually propagate over to
all other copies).
Note: All archives from all enabled threads need to be backed up. If you have
an idle instance, it will still create archive header files that are essential
for media recovery. This applies to Oracle Parallel Server only.
Prevent any archiver busy waits
-------------------------------
To prevent archiver busy waits, the archiver should be tuned by adjusting
log_archive_buffers and log_archive_buffer_size. The tuning tips described
earlier in the paper should be followed.
Keep all archives on disk from last database backup
---------------------------------------------------
Keeping all archives on disk from last database backup will reduce recovery
time by bypassing the time required to restore the archives from tape. This
may reduce MTTR (Mean Time to Recover) dramatically.
You may be able to achieve this by creating several archive destinations
or having one large archive destination. For example, lets assume you have
several archive destinations. Archiver is writing to archive DEST 1. When
DEST 1 fills up to a certain threshold, say 80% (enough room for two more
archive files), you can switch the archive destination by issuing the
command, 'alter system archive log to 'DEST2'. Archiver will then archive
the NEXT log in the new destination.
Tape backups can occur in DEST1 while archiver is writing to DEST2. This
reduces IO contention for those disks. Furthermore, depending on the size of
the destination, you can optimally keep a large number of archives on disk.
Before switching back to DEST1, we can purge the archives in DEST 1 that
has been successfully backed up to tape.
Some sites have one very large archive destination instead of several archive
destinations. Again, scripts are created to monitor and to log in to the
database to determine which Oracle archives to back up to tape. These archives
are backed up as soon as possible. A purging algorithm is produce to purge
only those files that have been successfully backed up to tape and with a
timestamp that is older than the beginning of the last successful Oracle hot
backup. Unfortunately, there may be some additional disk contention with this
plan due to the IO concurrency from the archive process(es) and tape backup
process(es).
ARCHIVER MONITORING:
Other best practices include monitoring the statuses of log files to check for
STALE or INVALID logs. If the logs remain STALE, then you should investigate
any possible media problems and relocate or recreate new members to maintain
the level of resiliency for the logs. STALE logs imply that there are missing
writes in this log. Oracle considers incomplete logs as STALE; so, you
get STALE logs after a shutdown abort or if the LGWR process simply cannot
write to that redo log member. Archiver can easily detect if there are
missing changes in the redo log member by verifying the correctness of the
redo log block. If the archiver detects a problem, it will switch to another
member searching for a sound set of redo log blocks. The archiver will never
complain if it can create an "good" archive file from the composite information
of all the online redo log members.
If archiver falls behind often, then one can spawn extra archiver processes.
We recommend monitoring V$LOG to alert and spawn extra archiver processes
whenever there are more than 2 logs that need archiving.
Note: There is an enhancement, bug 260126, to allow for the ability to have
several archiver processes.
Checking the alert.log for archiver or checkpoint errors, archiver and
log writer background trace files for errors, and archive destination for lack
of free space are essential in catching most potential archiving related
problems
Thursday, March 17, 2011
How to check which Techstack patchsets have been applied on 11i or R12
How to check which Techstack patchsets have been applied on 11i or R12 ? [ID 390864.1]
Modified 09-FEB-2010 Type HOWTO Status PUBLISHED
In this Document
Goal
Solution
References
Applies to:
Application Install - Version: 11.5.10 to 12.1.2 - Release: to 12.1
Information in this document applies to any platform.
Goal
How to check which Techstack patchsets have been applied
Solution
For Single Tier Release 11i :
SET head off Lines 120 pages 100
col n_patch format A65
col bug_number format A10
col patch_name format A10
spool atg_pf_ptch_level.txt
select ' atg_pf ' FROM dual;
/
select bug_number, decode(bug_number,
'3438354', '11i.ATG_PF.H'
,'4017300' ,'11i.ATG_PF.H.RUP1'
,'4125550' ,'11i.ATG_PF.H.RUP2'
,'4334965' ,'11i.ATG_PF.H RUP3'
,'4676589' ,'11i.ATG_PF.H RUP4'
,'5382500' ,'11i.ATG_PF.H RUP5 HELP'
,'5473858' ,'11i.ATG_PF.H.5'
,'5674941' ,'11i.ATG_PF.H RUP5 SSO Integrat'
,'5903765' ,'11i.ATG_PF.H RUP6'
,'6117031' ,'11i.ATG_PF.H RUP6 SSO 10g Integration'
,'6330890' ,'11i.ATG_PF.H RUP6 HELP'
) n_patch, last_update_date
FROM ad_bugs
WHERE bug_number
IN ( '3438354', '4017300', '4125550', '4334965', '4676589', '5382500', '5473858', '5674941', '5903765', '6117031', '6330890' );
For Multi Tier Release 11i :
set serveroutput on size 100000
DECLARE
TYPE p_patch_array_type is varray(100) of varchar2(10);
TYPE a_abstract_array_type is varray(100) of varchar2(60);
p_patchlist p_patch_array_type;
a_abstract a_abstract_array_type;
p_appltop_name VARCHAR2(50);
p_patch_status VARCHAR2(15);
p_appl_top_id NUMBER;
CURSOR alist_cursor IS
SELECT appl_top_id, name
FROM ad_appl_tops;
procedure println(msg in varchar2)
IS
BEGIN
dbms_output.put_line(msg);
END;
BEGIN
open alist_cursor;
p_patchlist := p_patch_array_type( '3438354'
,'4017300'
,'4125550'
,'4334965'
,'4676589'
,'5382500'
,'5473858'
,'5674941'
,'5903765'
,'6117031'
,'6330890'
);
a_abstract := a_abstract_array_type( '11i.ATG_PF.H'
,'11i.ATG_PF.H.RUP1'
,'11i.ATG_PF.H.RUP2'
,'11i.ATG_PF.H RUP3'
,'11i.ATG_PF.H RUP4'
,'11i.ATG_PF.H RUP5 HELP'
,'11i.ATG_PF.H.5'
,'11i.ATG_PF.H RUP5 SSO Integrat'
,'11i.ATG_PF.H RUP6'
,'11i.ATG_PF.H RUP6 SSO 10g Integration'
,'11i.ATG_PF.H RUP6 HELP'
);
LOOP
FETCH alist_cursor INTO p_appl_top_id, p_appltop_name;
EXIT WHEN alist_cursor%NOTFOUND;
IF p_appltop_name NOT IN ('GLOBAL','*PRESEEDED*')
THEN
println(p_appltop_name || ':');
for i in 1..p_patchlist.count
LOOP
p_patch_status := ad_patch.is_patch_applied('11i', p_appl_top_id, p_patchlist(i));
println('..Patch ' || a_abstract(i)
||' '||p_patchlist(i)||' was '||
p_patch_status);
END LOOP;
END IF;
println('.');
END LOOP;
close alist_cursor;
END;
/
For Single Tier Release 12 :
SET head off Lines 120 pages 100
col n_patch format A65
col bug_number format A10
col patch_name format A10
spool atg_pf_ptch_level.txt
select ' atg_pf ' FROM dual;
/
select bug_number, decode(bug_number,
'6272680', 'R12.ATG_PF.A.delta.4'
,'6077669', 'R12.ATG_PF.A.delta.3'
,'5917344', 'R12.ATG_PF.A.delta.2'
) n_patch, last_update_date
FROM ad_bugs
WHERE bug_number
IN ('6272680', '6077669', '5917344');
For Multi Tier Release R12 :
set serveroutput on size 100000
DECLARE
TYPE p_patch_array_type is varray(100) of varchar2(10);
TYPE a_abstract_array_type is varray(100) of varchar2(60);
p_patchlist p_patch_array_type;
a_abstract a_abstract_array_type;
p_appltop_name VARCHAR2(50);
p_patch_status VARCHAR2(15);
p_appl_top_id NUMBER;
CURSOR alist_cursor IS
SELECT appl_top_id, name
FROM ad_appl_tops;
procedure println(msg in varchar2)
IS
BEGIN
dbms_output.put_line(msg);
END;
BEGIN
open alist_cursor;
p_patchlist := p_patch_array_type( '6272680'
,'6077669'
,'5917344'
);
a_abstract := a_abstract_array_type( 'R12.ATG_PF.A.delta.4'
,'R12.ATG_PF.A.delta.3'
,'R12.ATG_PF.A.delta.2'
);
LOOP
FETCH alist_cursor INTO p_appl_top_id, p_appltop_name;
EXIT WHEN alist_cursor%NOTFOUND;
IF p_appltop_name NOT IN ('GLOBAL','*PRESEEDED*')
THEN
println(p_appltop_name || ':');
for i in 1..p_patchlist.count
LOOP
p_patch_status := ad_patch.is_patch_applied('11i', p_appl_top_id, p_patchlist(i));
println('..Patch ' || a_abstract(i)
||' '||p_patchlist(i)||' was '||
p_patch_status);
END LOOP;
END IF;
println('.');
END LOOP;
close alist_cursor;
END;
/
NB:
For the new coming patches 11i ATG RUP7, or the new R12 patches and so on..., please edit the select script and add the patch number you wish to check
Modified 09-FEB-2010 Type HOWTO Status PUBLISHED
In this Document
Goal
Solution
References
Applies to:
Application Install - Version: 11.5.10 to 12.1.2 - Release: to 12.1
Information in this document applies to any platform.
Goal
How to check which Techstack patchsets have been applied
Solution
For Single Tier Release 11i :
SET head off Lines 120 pages 100
col n_patch format A65
col bug_number format A10
col patch_name format A10
spool atg_pf_ptch_level.txt
select ' atg_pf ' FROM dual;
/
select bug_number, decode(bug_number,
'3438354', '11i.ATG_PF.H'
,'4017300' ,'11i.ATG_PF.H.RUP1'
,'4125550' ,'11i.ATG_PF.H.RUP2'
,'4334965' ,'11i.ATG_PF.H RUP3'
,'4676589' ,'11i.ATG_PF.H RUP4'
,'5382500' ,'11i.ATG_PF.H RUP5 HELP'
,'5473858' ,'11i.ATG_PF.H.5'
,'5674941' ,'11i.ATG_PF.H RUP5 SSO Integrat'
,'5903765' ,'11i.ATG_PF.H RUP6'
,'6117031' ,'11i.ATG_PF.H RUP6 SSO 10g Integration'
,'6330890' ,'11i.ATG_PF.H RUP6 HELP'
) n_patch, last_update_date
FROM ad_bugs
WHERE bug_number
IN ( '3438354', '4017300', '4125550', '4334965', '4676589', '5382500', '5473858', '5674941', '5903765', '6117031', '6330890' );
For Multi Tier Release 11i :
set serveroutput on size 100000
DECLARE
TYPE p_patch_array_type is varray(100) of varchar2(10);
TYPE a_abstract_array_type is varray(100) of varchar2(60);
p_patchlist p_patch_array_type;
a_abstract a_abstract_array_type;
p_appltop_name VARCHAR2(50);
p_patch_status VARCHAR2(15);
p_appl_top_id NUMBER;
CURSOR alist_cursor IS
SELECT appl_top_id, name
FROM ad_appl_tops;
procedure println(msg in varchar2)
IS
BEGIN
dbms_output.put_line(msg);
END;
BEGIN
open alist_cursor;
p_patchlist := p_patch_array_type( '3438354'
,'4017300'
,'4125550'
,'4334965'
,'4676589'
,'5382500'
,'5473858'
,'5674941'
,'5903765'
,'6117031'
,'6330890'
);
a_abstract := a_abstract_array_type( '11i.ATG_PF.H'
,'11i.ATG_PF.H.RUP1'
,'11i.ATG_PF.H.RUP2'
,'11i.ATG_PF.H RUP3'
,'11i.ATG_PF.H RUP4'
,'11i.ATG_PF.H RUP5 HELP'
,'11i.ATG_PF.H.5'
,'11i.ATG_PF.H RUP5 SSO Integrat'
,'11i.ATG_PF.H RUP6'
,'11i.ATG_PF.H RUP6 SSO 10g Integration'
,'11i.ATG_PF.H RUP6 HELP'
);
LOOP
FETCH alist_cursor INTO p_appl_top_id, p_appltop_name;
EXIT WHEN alist_cursor%NOTFOUND;
IF p_appltop_name NOT IN ('GLOBAL','*PRESEEDED*')
THEN
println(p_appltop_name || ':');
for i in 1..p_patchlist.count
LOOP
p_patch_status := ad_patch.is_patch_applied('11i', p_appl_top_id, p_patchlist(i));
println('..Patch ' || a_abstract(i)
||' '||p_patchlist(i)||' was '||
p_patch_status);
END LOOP;
END IF;
println('.');
END LOOP;
close alist_cursor;
END;
/
For Single Tier Release 12 :
SET head off Lines 120 pages 100
col n_patch format A65
col bug_number format A10
col patch_name format A10
spool atg_pf_ptch_level.txt
select ' atg_pf ' FROM dual;
/
select bug_number, decode(bug_number,
'6272680', 'R12.ATG_PF.A.delta.4'
,'6077669', 'R12.ATG_PF.A.delta.3'
,'5917344', 'R12.ATG_PF.A.delta.2'
) n_patch, last_update_date
FROM ad_bugs
WHERE bug_number
IN ('6272680', '6077669', '5917344');
For Multi Tier Release R12 :
set serveroutput on size 100000
DECLARE
TYPE p_patch_array_type is varray(100) of varchar2(10);
TYPE a_abstract_array_type is varray(100) of varchar2(60);
p_patchlist p_patch_array_type;
a_abstract a_abstract_array_type;
p_appltop_name VARCHAR2(50);
p_patch_status VARCHAR2(15);
p_appl_top_id NUMBER;
CURSOR alist_cursor IS
SELECT appl_top_id, name
FROM ad_appl_tops;
procedure println(msg in varchar2)
IS
BEGIN
dbms_output.put_line(msg);
END;
BEGIN
open alist_cursor;
p_patchlist := p_patch_array_type( '6272680'
,'6077669'
,'5917344'
);
a_abstract := a_abstract_array_type( 'R12.ATG_PF.A.delta.4'
,'R12.ATG_PF.A.delta.3'
,'R12.ATG_PF.A.delta.2'
);
LOOP
FETCH alist_cursor INTO p_appl_top_id, p_appltop_name;
EXIT WHEN alist_cursor%NOTFOUND;
IF p_appltop_name NOT IN ('GLOBAL','*PRESEEDED*')
THEN
println(p_appltop_name || ':');
for i in 1..p_patchlist.count
LOOP
p_patch_status := ad_patch.is_patch_applied('11i', p_appl_top_id, p_patchlist(i));
println('..Patch ' || a_abstract(i)
||' '||p_patchlist(i)||' was '||
p_patch_status);
END LOOP;
END IF;
println('.');
END LOOP;
close alist_cursor;
END;
/
NB:
For the new coming patches 11i ATG RUP7, or the new R12 patches and so on..., please edit the select script and add the patch number you wish to check
Use the following query to find the ATG rollup Patch level in your application.
Use the following query to find the ATG rollup Patch level in your application.
connect as apps:
SELECT (bug_number),
decode((bug_number),
'3438354','ATG_PF.H',
'4017300','ATG_PF.H RUP 1',
'4125550','ATG_PF.H RUP 2',
'4334965','ATG_PF.H RUP 3',
'4676589','ATG_PF.H RUP 4',
'5473858','ATG_PF.H RUP 5',
'5903765','ATG_PF.H RUP 6',
'6241631','ATG_PF.H RUP 7')
FROM ad_bugs
WHERE bug_number IN
('3384350',
'3438354',
'4017300',
'4125550',
'4334965',
'4676589',
'5473858',
'5903765',
'6241631')
order by bug_number desc
connect as apps:
SELECT (bug_number),
decode((bug_number),
'3438354','ATG_PF.H',
'4017300','ATG_PF.H RUP 1',
'4125550','ATG_PF.H RUP 2',
'4334965','ATG_PF.H RUP 3',
'4676589','ATG_PF.H RUP 4',
'5473858','ATG_PF.H RUP 5',
'5903765','ATG_PF.H RUP 6',
'6241631','ATG_PF.H RUP 7')
FROM ad_bugs
WHERE bug_number IN
('3384350',
'3438354',
'4017300',
'4125550',
'4334965',
'4676589',
'5473858',
'5903765',
'6241631')
order by bug_number desc
Monday, March 14, 2011
Unable To Start Concurrent Manager And Failing With Message Concurrent Manager cannot find error description for CONC-Get plsql file name
Unable To Start Concurrent Manager And Failing With Message Concurrent Manager cannot find error description for CONC-Get plsql file names [ID 1161386.1]
Modified 13-SEP-2010 Type PROBLEM Status MODERATED
In this Document
Symptoms
Cause
Solution
This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
Applies to:
Oracle Application Object Library - Version: 11.5.10.2 and later [Release: 11.5.10 and later ]
Information in this document applies to any platform.
Symptoms
On R11.5.10.2, it is not possible to start concurrent manager using adcmctl, but it is possible to start it using adstrtal. The ICM log file is showing the following error:
Concurrent Manager cannot find error description for CONC-Get plsql file names
Running the script cmclean does not fix the issue. FNDLIBR processes are showing as defunct on the operating system as follows:
oracle 27109 25564 0 15:07 ? 00:00:00 [FNDLIBR]
Cause
The package fnd_file was invalid.
This can be verified by checking the results for the following query:
select obj.owner,obj.object_type, obj.object_name , err.text
from dba_errors err, dba_objects obj
where err.name =obj.object_name
and err.owner =obj.owner
and obj.status ='INVALID'
order by 1,2;
Solution
Please execute the following to re-create the package FND_FILE:
1. Navigate to the directory containing the package files:
cd $FND_TOP/patch/115/sql/
2. Login to SqlPlus as apps, and run the following:
@AFCPPIOB.pls
Modified 13-SEP-2010 Type PROBLEM Status MODERATED
In this Document
Symptoms
Cause
Solution
This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
Applies to:
Oracle Application Object Library - Version: 11.5.10.2 and later [Release: 11.5.10 and later ]
Information in this document applies to any platform.
Symptoms
On R11.5.10.2, it is not possible to start concurrent manager using adcmctl, but it is possible to start it using adstrtal. The ICM log file is showing the following error:
Concurrent Manager cannot find error description for CONC-Get plsql file names
Running the script cmclean does not fix the issue. FNDLIBR processes are showing as defunct on the operating system as follows:
oracle 27109 25564 0 15:07 ? 00:00:00 [FNDLIBR]
Cause
The package fnd_file was invalid.
This can be verified by checking the results for the following query:
select obj.owner,obj.object_type, obj.object_name , err.text
from dba_errors err, dba_objects obj
where err.name =obj.object_name
and err.owner =obj.owner
and obj.status ='INVALID'
order by 1,2;
Solution
Please execute the following to re-create the package FND_FILE:
1. Navigate to the directory containing the package files:
cd $FND_TOP/patch/115/sql/
2. Login to SqlPlus as apps, and run the following:
@AFCPPIOB.pls
Sunday, March 13, 2011
Oracle E-Business Suite Release 12 Technology Stack Documentation Roadmap
Oracle E-Business Suite Release 12 Technology Stack Documentation Roadmap [ID 380482.1]
Modified 18-FEB-2011 Type BULLETIN Status PUBLISHED
This knowledge document acts as a central list of My Oracle Support documents that are designed to describe the most effective use of various optional and required components of the technology stack that underpins the overall Oracle E-Business Suite Release 12 architecture.
The documents listed do not replace the standard books in the Documentation Library, but should be read in conjunction with the relevant books: for example, a document may refer the reader to a book for background information, and a book may point to a document as the best source of up-to-date information on a new or rapidly-changing topic.
The most current version of this document can be obtained in My Oracle Support Knowledge Document 380482.1.
Note: The Release 11i equivalent of this document is My Oracle Support Knowledge Document 207159.1.
There is a change log at the end of this document.
In This Document
The various knowledge documents listed here are written and maintained by the Oracle Applications Technology Group (ATG) and other development organizations, and divided into the following categories:
Section 1: Installation and Configuration
Section 2: Database Tier
Section 3: Application Tier
Section 4: Client Tier
Section 5: Security
Section 6: Documentation Resources
Section 1: Installation and Configuration
1.1 General
Document 399362.1, Oracle Applications Release 12 Upgrade Sizing and Best Practices
Document 387859.1, Using AutoConfig to Manage System Configurations with Oracle E-Business Suite Release 12
Document 406369.1, Oracle Configuration Manager and E-Business Suite Release 12 Release Notes
Document 727157.1, Upgrading OCM and Switching to Native Integration with Oracle E-Business Suite Release 12
Document 438086.1, Platform Migration with Oracle Applications Release 12
Document 975182.1, Integrating Oracle E-Business Suite with Oracle Access Manager using Oracle E-Business Suite AccessGate
Document 406558.1, Configuring Applications Node Services in Oracle Applications Release 12
Document 384248.1, Sharing the Application Tier File System in Oracle E-Business Suite Release 12
Document 567015.1, Using IPv6 with Oracle E-Business Suite Releases 11i and 12
Document 380483.1, Oracle E-Business Suite Release 12 Additional Configuration and Deployment Options
Document 380489.1, Using Load-Balancers with Oracle E-Business Suite Release 12
Document 393861.1, Globalization Guide for Oracle Applications Release 12
Document 563019.1, Complying with Daylight Saving Time (DST) and Time Zone Rule Changes in E-Business Suite Release 12
Document 558959.1, Configuring Oracle Connection Manager With Oracle E-Business Suite Release 12
Document 414992.1, Using Cygwin to Maintain Oracle E-Business Suite Release 12 on Windows
Document 1112325.1, Deploying Oracle GoldenGate to Achieve Operational Reporting for Oracle E-Business Suite
1.2 Cloning
Document 406982.1, Cloning Oracle Applications Release 12 with Rapid Clone
Document 559518.1, Cloning Oracle E-Business Suite Release 12 RAC-Enabled Systems with Rapid Clone
Document 783188.1, Certified Oracle RAC Scenarios for Oracle E-Business Suite Cloning
1.3 Oracle VM
Document 975734.1, Using Oracle VM Templates for Oracle E-Business Suite
Document 977681.1, Oracle VM Template Developer's Guide For Oracle E-Business Suite
Document 465915.1, Using Oracle VM with Oracle E-Business Suite Release 11i or Release 12
1.4 Secure Enterprise Search
Document 462377.1, Installing Oracle E-Business Suite Secure Enterprise Search, Release 12
Document 744820.1, Oracle E-Business Suite Secure Enterprise Search Best Practices, Release 12
Document 726239.1, Oracle E-Business Suite Secure Enterprise Search Troubleshooting Guidelines, Release 12
Document 551247.1, Oracle E-Business Suite Secure Enterprise Search Documentation Update, Release 12
Document 740499.1, Oracle E-Business Suite Secure Enterprise Search Release Notes, Release 12.0.6
Document 566097.1, Oracle E-Business Suite Secure Enterprise Search Release Notes, Release 12.1.1
1.5 Oracle Forms
Document 397174.1, Deploying a New Forms .ear File in Oracle Applications Release 12
Document 384241.1, Using Forms Socket Mode with Oracle E-Business Suite Release 12
Document 373548.1, Using Forms Trace in Oracle Applications Release 12
Document 437878.1, Upgrading Forms and Reports 10g in Oracle Applications Release 12
Document 1292611.1, Upgrading Form Personalizations and OA Framework Personalizations from Oracle E-Business Suite Release 11i to 12.1
Section 2: Database Tier
2.1 General
Document 396009.1, Database Initialization Parameter Settings for Oracle Applications Release 12
Document 467778.1, Oracle 10g Release 2 (10.2.0.2) Database Preparation Guidelines for an E-Business Suite Release 12 Upgrade
Document 552973.1, Oracle 10g Release 2 (10.2.0.3) Database Preparation Guidelines for an E-Business Suite Release 12 Upgrade
Document 403339.1, Oracle 10g Release 2 Database Preparation Guidelines for an E-Business Suite Release 12.0.4 Upgrade
2.2 Interoperability Notes
Document 454750.1, Interoperability Notes for Oracle E-Business Suite Release 12 with Oracle Database 10g Release 2 (10.2.0)
Document 735276.1, Interoperability Notes for Oracle E-Business Suite Release 12 with Oracle Database 11g Release 1 (11.1.0)
Document 812362.1, Interoperability Notes for Oracle E-Business Suite Release 12.1 with Oracle Database 10g Release 2 (10.2.0)
Document 802875.1, Interoperability Notes for Oracle E-Business Suite Release 12.1 with Oracle Database 11g Release 1 (11.1.0)
2.3 Oracle Real Application Clusters and High Availability
Document 1072636.1, Oracle E-Business Suite Release 12 High Availability Documentation Roadmap
Document 823587.1, Using Oracle 11g Release 2 Real Application Clusters and Automatic Storage Management with Oracle E-Business Suite Release 12
Document 466649.1, Using Oracle 11g Release 1 (11.1.0.7) Real Application Clusters and Automatic Storage Management with Oracle E-Business Suite Release 12
Document 783044.1, Using Oracle 11g Release 1 (11.1.0.6) Real Application Clusters and Automatic Storage Management with Oracle E-Business Suite Release 12
Document 388577.1, Using Oracle 10g Release 2 Real Application Clusters and Automatic Storage Management with Oracle E-Business Suite Release 12
Document 1137763.1, Using Oracle 10g Release 2 (10.2.0.4) Real Application Clusters and Automatic Storage Management with Oracle E-Business Suite Release 12
Document 1134753.1, Adding and Deleting Oracle RAC Nodes for Oracle E-Business Suite Release 12
Document 265633.1, Automatic Storage Management Technical Best Practices
Document 452056.1, Business Continuity for Oracle Applications Release 12 on Database Release 10gR2 - Single Instance and RAC
Document 1070033.1, Business Continuity for Oracle E-Business Release 12 Using Oracle 11g Physical Standby Database
Document 603325.1, Using Cisco ACE Series Application Control Engine with Oracle E-Business Suite Release 12
Oracle Maximum Availability Architecture
2.4 Patching
Document 734025.1, Using a Staged Applications System to Reduce Patching Downtime in Oracle Applications Release 12
Document 244040.1, Recommended Performance Patches for Oracle E-Business Suite
Document 1147107.1, Database Patch Set Update Overlay Patches Required by E-Business Suite
Document 1061366.1, List of Critical Patches Required For Oracle 11g Table Compression
Section 3: Application Tier
3.1 General
Document 415007.1, Oracle Application Server with Oracle E-Business Suite Release 12: Frequently Asked Questions
Document 454811.1, Upgrading to the Latest OracleAS 10g 10.1.3.x Patch Set in Oracle E-Business Suite Release 12
Document 380487.1, Oracle Application Server 10g with Oracle E-Business Suite Release 12 Troubleshooting
Document 454178.1, Oracle Application Server Diagnostic Tools and Log Files in Applications Release 12
3.2 External Integrations
Document 376811.1, Integrating Oracle E-Business Suite Release 12 with Oracle Internet Directory and Oracle Single Sign-On
Document 876539.1, Using the Latest Oracle Internet Directory 11gR1 Patchset with Oracle Single Sign-on and Oracle E-Business Suite
Document 373634.1, Using Discoverer 10.1.2 with Oracle E-Business Suite Release 12
Document 1074326.1, Using Discoverer 11.1.1 with Oracle E-Business Suite Release 12
Document 380484.1, Using Oracle Portal 10g with Oracle E-Business Suite Release 12
Document 1074334.1, Using Oracle Portal 11.1.1 with Oracle E-Business Suite Release 12
Document 380486.1, Installing and Configuring Web Cache 10g and Oracle E-Business Suite 12
Document 755067.1, Using Oracle BPEL 10g with E-Business Suite Release 12.1.1
Document 1070257.1, Integrating Oracle E-Business Suite 12.1.3 with BPEL in SOA Suite 11g
Document 557221.1, Oracle WebCenter 10g Application Creation and Deployment Guide for Oracle E-Business Suite Release 12
Document 1296491.1, Integration of Oracle E-Business Suite and Oracle Application Development Framework (ADF) Applications
Section 4: Client Tier
Document 1155883.1, Oracle E-Business Suite Desktop Client Hardware and Software Requirements
Document 389422.1, Recommended Browsers for Oracle E-Business Suite Release 12
Document 402138.1, Oracle Applications Release Notes for Apple Macintosh OS X 10.4
Document 418664.1, Overview of Using Java with Oracle E-Business Suite Release 12
Document 362851.1, Guidelines to Set Up the JVM in Oracle E-Business Suite Releases 11i and 12
Document 393931.1, Upgrading Sun J2SE (Native Plug-in) with Oracle Applications 12 for Windows Clients
Document 384249.1, Using Latest Update of JDK 5.0 with Oracle E-Business Suite Release 12
Document 455492.1, Using Latest Update of Java 6.0 with Oracle E-Business Suite Release 12
Section 5: Security
Document 403537.1, Best Practices for Securing Oracle E-Business Suite Release 12
Document 376694.1, Using the Oracle Wallet Manager Command Line Interface with Oracle E-Business Suite Release 12
Document 376700.1, Enabling SSL in Oracle Applications Release 12
Document 380490.1, Oracle E-Business Suite Release 12 Configuration in a DMZ
Document 566841.1, Integrating Oracle E-Business Suite Release 12 with Oracle Database Vault 10.2.0.4
Document 859397.1, Integrating Oracle E-Business Suite Release 12 with Oracle Database Vault 11.1.0.7
Section 6: Documentation Resources
Document 396957.1, Oracle Applications Technology Stack Documentation Resources, Release 12
Document 405565.1, Oracle Applications Release 12 Installation Guidelines
Modified 18-FEB-2011 Type BULLETIN Status PUBLISHED
This knowledge document acts as a central list of My Oracle Support documents that are designed to describe the most effective use of various optional and required components of the technology stack that underpins the overall Oracle E-Business Suite Release 12 architecture.
The documents listed do not replace the standard books in the Documentation Library, but should be read in conjunction with the relevant books: for example, a document may refer the reader to a book for background information, and a book may point to a document as the best source of up-to-date information on a new or rapidly-changing topic.
The most current version of this document can be obtained in My Oracle Support Knowledge Document 380482.1.
Note: The Release 11i equivalent of this document is My Oracle Support Knowledge Document 207159.1.
There is a change log at the end of this document.
In This Document
The various knowledge documents listed here are written and maintained by the Oracle Applications Technology Group (ATG) and other development organizations, and divided into the following categories:
Section 1: Installation and Configuration
Section 2: Database Tier
Section 3: Application Tier
Section 4: Client Tier
Section 5: Security
Section 6: Documentation Resources
Section 1: Installation and Configuration
1.1 General
Document 399362.1, Oracle Applications Release 12 Upgrade Sizing and Best Practices
Document 387859.1, Using AutoConfig to Manage System Configurations with Oracle E-Business Suite Release 12
Document 406369.1, Oracle Configuration Manager and E-Business Suite Release 12 Release Notes
Document 727157.1, Upgrading OCM and Switching to Native Integration with Oracle E-Business Suite Release 12
Document 438086.1, Platform Migration with Oracle Applications Release 12
Document 975182.1, Integrating Oracle E-Business Suite with Oracle Access Manager using Oracle E-Business Suite AccessGate
Document 406558.1, Configuring Applications Node Services in Oracle Applications Release 12
Document 384248.1, Sharing the Application Tier File System in Oracle E-Business Suite Release 12
Document 567015.1, Using IPv6 with Oracle E-Business Suite Releases 11i and 12
Document 380483.1, Oracle E-Business Suite Release 12 Additional Configuration and Deployment Options
Document 380489.1, Using Load-Balancers with Oracle E-Business Suite Release 12
Document 393861.1, Globalization Guide for Oracle Applications Release 12
Document 563019.1, Complying with Daylight Saving Time (DST) and Time Zone Rule Changes in E-Business Suite Release 12
Document 558959.1, Configuring Oracle Connection Manager With Oracle E-Business Suite Release 12
Document 414992.1, Using Cygwin to Maintain Oracle E-Business Suite Release 12 on Windows
Document 1112325.1, Deploying Oracle GoldenGate to Achieve Operational Reporting for Oracle E-Business Suite
1.2 Cloning
Document 406982.1, Cloning Oracle Applications Release 12 with Rapid Clone
Document 559518.1, Cloning Oracle E-Business Suite Release 12 RAC-Enabled Systems with Rapid Clone
Document 783188.1, Certified Oracle RAC Scenarios for Oracle E-Business Suite Cloning
1.3 Oracle VM
Document 975734.1, Using Oracle VM Templates for Oracle E-Business Suite
Document 977681.1, Oracle VM Template Developer's Guide For Oracle E-Business Suite
Document 465915.1, Using Oracle VM with Oracle E-Business Suite Release 11i or Release 12
1.4 Secure Enterprise Search
Document 462377.1, Installing Oracle E-Business Suite Secure Enterprise Search, Release 12
Document 744820.1, Oracle E-Business Suite Secure Enterprise Search Best Practices, Release 12
Document 726239.1, Oracle E-Business Suite Secure Enterprise Search Troubleshooting Guidelines, Release 12
Document 551247.1, Oracle E-Business Suite Secure Enterprise Search Documentation Update, Release 12
Document 740499.1, Oracle E-Business Suite Secure Enterprise Search Release Notes, Release 12.0.6
Document 566097.1, Oracle E-Business Suite Secure Enterprise Search Release Notes, Release 12.1.1
1.5 Oracle Forms
Document 397174.1, Deploying a New Forms .ear File in Oracle Applications Release 12
Document 384241.1, Using Forms Socket Mode with Oracle E-Business Suite Release 12
Document 373548.1, Using Forms Trace in Oracle Applications Release 12
Document 437878.1, Upgrading Forms and Reports 10g in Oracle Applications Release 12
Document 1292611.1, Upgrading Form Personalizations and OA Framework Personalizations from Oracle E-Business Suite Release 11i to 12.1
Section 2: Database Tier
2.1 General
Document 396009.1, Database Initialization Parameter Settings for Oracle Applications Release 12
Document 467778.1, Oracle 10g Release 2 (10.2.0.2) Database Preparation Guidelines for an E-Business Suite Release 12 Upgrade
Document 552973.1, Oracle 10g Release 2 (10.2.0.3) Database Preparation Guidelines for an E-Business Suite Release 12 Upgrade
Document 403339.1, Oracle 10g Release 2 Database Preparation Guidelines for an E-Business Suite Release 12.0.4 Upgrade
2.2 Interoperability Notes
Document 454750.1, Interoperability Notes for Oracle E-Business Suite Release 12 with Oracle Database 10g Release 2 (10.2.0)
Document 735276.1, Interoperability Notes for Oracle E-Business Suite Release 12 with Oracle Database 11g Release 1 (11.1.0)
Document 812362.1, Interoperability Notes for Oracle E-Business Suite Release 12.1 with Oracle Database 10g Release 2 (10.2.0)
Document 802875.1, Interoperability Notes for Oracle E-Business Suite Release 12.1 with Oracle Database 11g Release 1 (11.1.0)
2.3 Oracle Real Application Clusters and High Availability
Document 1072636.1, Oracle E-Business Suite Release 12 High Availability Documentation Roadmap
Document 823587.1, Using Oracle 11g Release 2 Real Application Clusters and Automatic Storage Management with Oracle E-Business Suite Release 12
Document 466649.1, Using Oracle 11g Release 1 (11.1.0.7) Real Application Clusters and Automatic Storage Management with Oracle E-Business Suite Release 12
Document 783044.1, Using Oracle 11g Release 1 (11.1.0.6) Real Application Clusters and Automatic Storage Management with Oracle E-Business Suite Release 12
Document 388577.1, Using Oracle 10g Release 2 Real Application Clusters and Automatic Storage Management with Oracle E-Business Suite Release 12
Document 1137763.1, Using Oracle 10g Release 2 (10.2.0.4) Real Application Clusters and Automatic Storage Management with Oracle E-Business Suite Release 12
Document 1134753.1, Adding and Deleting Oracle RAC Nodes for Oracle E-Business Suite Release 12
Document 265633.1, Automatic Storage Management Technical Best Practices
Document 452056.1, Business Continuity for Oracle Applications Release 12 on Database Release 10gR2 - Single Instance and RAC
Document 1070033.1, Business Continuity for Oracle E-Business Release 12 Using Oracle 11g Physical Standby Database
Document 603325.1, Using Cisco ACE Series Application Control Engine with Oracle E-Business Suite Release 12
Oracle Maximum Availability Architecture
2.4 Patching
Document 734025.1, Using a Staged Applications System to Reduce Patching Downtime in Oracle Applications Release 12
Document 244040.1, Recommended Performance Patches for Oracle E-Business Suite
Document 1147107.1, Database Patch Set Update Overlay Patches Required by E-Business Suite
Document 1061366.1, List of Critical Patches Required For Oracle 11g Table Compression
Section 3: Application Tier
3.1 General
Document 415007.1, Oracle Application Server with Oracle E-Business Suite Release 12: Frequently Asked Questions
Document 454811.1, Upgrading to the Latest OracleAS 10g 10.1.3.x Patch Set in Oracle E-Business Suite Release 12
Document 380487.1, Oracle Application Server 10g with Oracle E-Business Suite Release 12 Troubleshooting
Document 454178.1, Oracle Application Server Diagnostic Tools and Log Files in Applications Release 12
3.2 External Integrations
Document 376811.1, Integrating Oracle E-Business Suite Release 12 with Oracle Internet Directory and Oracle Single Sign-On
Document 876539.1, Using the Latest Oracle Internet Directory 11gR1 Patchset with Oracle Single Sign-on and Oracle E-Business Suite
Document 373634.1, Using Discoverer 10.1.2 with Oracle E-Business Suite Release 12
Document 1074326.1, Using Discoverer 11.1.1 with Oracle E-Business Suite Release 12
Document 380484.1, Using Oracle Portal 10g with Oracle E-Business Suite Release 12
Document 1074334.1, Using Oracle Portal 11.1.1 with Oracle E-Business Suite Release 12
Document 380486.1, Installing and Configuring Web Cache 10g and Oracle E-Business Suite 12
Document 755067.1, Using Oracle BPEL 10g with E-Business Suite Release 12.1.1
Document 1070257.1, Integrating Oracle E-Business Suite 12.1.3 with BPEL in SOA Suite 11g
Document 557221.1, Oracle WebCenter 10g Application Creation and Deployment Guide for Oracle E-Business Suite Release 12
Document 1296491.1, Integration of Oracle E-Business Suite and Oracle Application Development Framework (ADF) Applications
Section 4: Client Tier
Document 1155883.1, Oracle E-Business Suite Desktop Client Hardware and Software Requirements
Document 389422.1, Recommended Browsers for Oracle E-Business Suite Release 12
Document 402138.1, Oracle Applications Release Notes for Apple Macintosh OS X 10.4
Document 418664.1, Overview of Using Java with Oracle E-Business Suite Release 12
Document 362851.1, Guidelines to Set Up the JVM in Oracle E-Business Suite Releases 11i and 12
Document 393931.1, Upgrading Sun J2SE (Native Plug-in) with Oracle Applications 12 for Windows Clients
Document 384249.1, Using Latest Update of JDK 5.0 with Oracle E-Business Suite Release 12
Document 455492.1, Using Latest Update of Java 6.0 with Oracle E-Business Suite Release 12
Section 5: Security
Document 403537.1, Best Practices for Securing Oracle E-Business Suite Release 12
Document 376694.1, Using the Oracle Wallet Manager Command Line Interface with Oracle E-Business Suite Release 12
Document 376700.1, Enabling SSL in Oracle Applications Release 12
Document 380490.1, Oracle E-Business Suite Release 12 Configuration in a DMZ
Document 566841.1, Integrating Oracle E-Business Suite Release 12 with Oracle Database Vault 10.2.0.4
Document 859397.1, Integrating Oracle E-Business Suite Release 12 with Oracle Database Vault 11.1.0.7
Section 6: Documentation Resources
Document 396957.1, Oracle Applications Technology Stack Documentation Resources, Release 12
Document 405565.1, Oracle Applications Release 12 Installation Guidelines
Pfile vs SPfile
Pfile vs SPfile [ID 249664.1]
Modified 21-OCT-2010 Type BULLETIN Status PUBLISHED
Applies to:
Oracle Server - Enterprise Edition
Information in this document applies to any platform.
Purpose
To get acquainted with SPFile
Scope and Application
For DBA'S , Support Analysts...,
Pfile vs SPfile
Until Oracle 8i DBAs have been using a text file called the pfile (parameter file) to store the database initialization parameters.
The pfile is read at instance startup time to get specific instance characteristics. Any changes made the pfile would only take effect when the database is restarted.
However, parameters that were dynamically alterable could be changed using the appropriate ALTER SYSTEM or ALTER SESSION statement, which would take effect immediately.
As of Oracle9i, new feature called the spfile (server parameter file). The spfile is a binary file that contains the same information as the old pfile.
The spfile is a server-side initialization parameter file; parameters stored in this file are persistent across database startups.
This makes all the changes made to the instance using the ALTER SYSTEM statement persistent. Oracle requires that you start an instance for the first time using the pfile and then create the spfile.
The server parameter file (also called SPFILE) is in a single location where all the necessary parameters are defined and stored. The defined parameter values are applicable for all the instances in the cluster.
The SPFILE permits dynamic changes without requiring you to bring down the instance.
You can still use the client side parameter file to manage parameter settings in Real Application Clusters; however, administrative convenience is sacrificed and the advantage of dynamic change is lost.
By default, if you do not specify PFILE in your STARTUP command, Oracle will use a server parameter file.
SERVER PARAMETER FILE ( SPFILE )
================================
A server parameter file is basically a repository for initialization parameters.
Initialization parameters stored in a SPFILE are persistent, meaning any parameter changes made while an instance is running can persist across instance shutdown and startup.
In this way, all the initialization parameters manually updated by ALTER SYSTEM SET commands become persistent.
It also provides a basis for the Oracle database server to self-tune.
Another advantage, particularly for multi-instance RAC systems, is that a single copy of the parameter file can be used by all instances. Even though a single file is used to specify parameters, it has different format styles to support both the common values for all instances, as well as the specific values for an individual instance.
A server parameter file is initially built from the traditional text initialization parameter file, using the create SPFILE statement. It is a binary file that cannot be browsed or edited with a text editor.
Oracle provides other interfaces for viewing and modifying parameter settings. At system startup, the default behavior of the STARTUP command is to read a SPFILE to obtain initialization parameter settings. If the STARTUP command doesn't have a PFILE clause, it reads the SPFILE from a location
specified by the operating system.
If you choose to use the traditional text initialization parameter file, you must specify the PFILE clause when issuing the STARTUP command.
SETTING THE SERVER PARAMETER FILE VALUES
=========================================
Use the SID designator to set instance-specific parameter values in the server parameter file.
For settings across the database, use a '*', and for a specific instance, set the prefix with SID as indicated below.
*.OPEN_CURSORS=400 # For database-wide setting
RACDB1.OPEN_CURSORS=800# For RACDB1 instance
Note that even though open_cursors is set at 400 for all instances in the first entry, the value of 800 remains in effect for the SID 'RACDB1'.
Some initialization parameters are dynamic since they can be modified using the ALTER SESSION or ALTER SYSTEM statement while an instance is running. Use the following syntax to dynamically alter
initialization parameters:
ALTER SESSION SET parameter_name = value
ALTER SYSTEM SET parameter_name = value [DEFERRED]
Use the SET clause of the ALTER SYSTEM statement to set or change initialization parameter values. Additionally, the SCOPE clause specifies the scope of a change as described below:
SCOPE = SPFILE
(For both static and dynamic parameters, changes are recorded in the spfile, to be given effect in the next restart.)
SCOPE = MEMORY
(For dynamic parameters, changes are applied in memory only. No static parameter change is allowed.)
SCOPE = BOTH
For dynamic parameters, the change is applied in both the server parameter file and memory. No static parameter change is allowed.)
For dynamic parameters, we can also specify the DEFERRED keyword. When specified, the change is effective only for future sessions.
HERE ARE A FEW EXAMPLES
===========================
The following statement affects all instances. However, the values are only effective for the current instances, they are not written to binary SPFILE.
ALTER SYSTEM SET OPEN_CURSORS=500 SID='*' SCOPE=MEMORY;
The next statement resets the value for the instance 'RACDB1'.
At this point, the database-wide setting becomes effective for SID of RACDB1.
ALTER SYSTEM RESET OPEN_CURSORS SCOPE=SPFILE sid='RACDB1';
To reset a parameter to its default value throughout the cluster database, use the command:
ALTER SYSTEM RESET OPEN_CURSORS SCOPE=SPFILE sid='*';
CREATING A SERVER PARAMETER FILE
=================================
The server parameter file is initially created from a text initialization parameter file (init.ora).
It must be created prior to its use in the STARTUP command.
The create SPFILE statement is used to create a server parameter file.
The following example creates a server parameter file from an initialization parameter file.
CREATE SPFILE FROM PFILE='/u01/oracle/product/920/dbs/initRAC1.ora';
Below is another example that illustrates creating a server parameter file and supplying a name.
CREATE SPFILE='/u01/oracle/product/920/dbs/racdb_spfile.ora'
FROM PFILE='/u01/oracle/product/920/dbs/init.ora';
EXPORTING THE SERVER PARAMETER FILE
===================================
We can export the server parameter file to create a traditional text initialization parameter file.
This would be useful for:
1) Creating backups of the server parameter file.
2) For diagnostic purposes to list all of the parameter values currently used by an instance.
3) Modifying the server parameter file by first exporting it, editing the output file, and then recreating it.
The following example creates a text initialization parameter file from the server parameter file:
CREATE PFILE FROM SPFILE;
The example below creates a text initialization parameter file from a server parameter file, where the names of the files are specified:
CREATE PFILE='/u01/oracle/product/920/dbs/racdb_init.ora'
FROM SPFILE='/u01/oracle/product/dbs/racdb_spfile.ora';
Refer to 'Oracle 9i Database Reference' for all the parameters that can be changed with an ALTER SYSTEM command...
IS MY DATABASE USING SPFILE ?
=============================
Am I using spfile or pfile ?
The following query will let you know..
1) SQL> SELECT name,value FROM v$parameter WHERE name = 'spfile';
NAME VALUE
---------- --------------------------------------------------
spfile /fsys1/oracle/product/9.2.0/spfileTEST.ora
2) SQL> show parameter spfile;
The v$spparameter view
The contents of the SPFILE can be obtained from the V$SPPARAMETER view:
SQL> ALTER SYSTEM SET timed_statistics=FALSE SCOPE=SPFILE;
System altered.
SQL> SELECT name,value FROM v$parameter WHERE name='timed_statistics';
NAME VALUE
-------------------- ---------------------
timed_statistics TRUE
SQL> SELECT name,value FROM v$spparameter WHERE name='timed_statistics';
NAME VALUE
-------------------- ---------------------
timed_statistics FALSE
Modified 21-OCT-2010 Type BULLETIN Status PUBLISHED
Applies to:
Oracle Server - Enterprise Edition
Information in this document applies to any platform.
Purpose
To get acquainted with SPFile
Scope and Application
For DBA'S , Support Analysts...,
Pfile vs SPfile
Until Oracle 8i DBAs have been using a text file called the pfile (parameter file) to store the database initialization parameters.
The pfile is read at instance startup time to get specific instance characteristics. Any changes made the pfile would only take effect when the database is restarted.
However, parameters that were dynamically alterable could be changed using the appropriate ALTER SYSTEM or ALTER SESSION statement, which would take effect immediately.
As of Oracle9i, new feature called the spfile (server parameter file). The spfile is a binary file that contains the same information as the old pfile.
The spfile is a server-side initialization parameter file; parameters stored in this file are persistent across database startups.
This makes all the changes made to the instance using the ALTER SYSTEM statement persistent. Oracle requires that you start an instance for the first time using the pfile and then create the spfile.
The server parameter file (also called SPFILE) is in a single location where all the necessary parameters are defined and stored. The defined parameter values are applicable for all the instances in the cluster.
The SPFILE permits dynamic changes without requiring you to bring down the instance.
You can still use the client side parameter file to manage parameter settings in Real Application Clusters; however, administrative convenience is sacrificed and the advantage of dynamic change is lost.
By default, if you do not specify PFILE in your STARTUP command, Oracle will use a server parameter file.
SERVER PARAMETER FILE ( SPFILE )
================================
A server parameter file is basically a repository for initialization parameters.
Initialization parameters stored in a SPFILE are persistent, meaning any parameter changes made while an instance is running can persist across instance shutdown and startup.
In this way, all the initialization parameters manually updated by ALTER SYSTEM SET commands become persistent.
It also provides a basis for the Oracle database server to self-tune.
Another advantage, particularly for multi-instance RAC systems, is that a single copy of the parameter file can be used by all instances. Even though a single file is used to specify parameters, it has different format styles to support both the common values for all instances, as well as the specific values for an individual instance.
A server parameter file is initially built from the traditional text initialization parameter file, using the create SPFILE statement. It is a binary file that cannot be browsed or edited with a text editor.
Oracle provides other interfaces for viewing and modifying parameter settings. At system startup, the default behavior of the STARTUP command is to read a SPFILE to obtain initialization parameter settings. If the STARTUP command doesn't have a PFILE clause, it reads the SPFILE from a location
specified by the operating system.
If you choose to use the traditional text initialization parameter file, you must specify the PFILE clause when issuing the STARTUP command.
SETTING THE SERVER PARAMETER FILE VALUES
=========================================
Use the SID designator to set instance-specific parameter values in the server parameter file.
For settings across the database, use a '*', and for a specific instance, set the prefix with SID as indicated below.
*.OPEN_CURSORS=400 # For database-wide setting
RACDB1.OPEN_CURSORS=800# For RACDB1 instance
Note that even though open_cursors is set at 400 for all instances in the first entry, the value of 800 remains in effect for the SID 'RACDB1'.
Some initialization parameters are dynamic since they can be modified using the ALTER SESSION or ALTER SYSTEM statement while an instance is running. Use the following syntax to dynamically alter
initialization parameters:
ALTER SESSION SET parameter_name = value
ALTER SYSTEM SET parameter_name = value [DEFERRED]
Use the SET clause of the ALTER SYSTEM statement to set or change initialization parameter values. Additionally, the SCOPE clause specifies the scope of a change as described below:
SCOPE = SPFILE
(For both static and dynamic parameters, changes are recorded in the spfile, to be given effect in the next restart.)
SCOPE = MEMORY
(For dynamic parameters, changes are applied in memory only. No static parameter change is allowed.)
SCOPE = BOTH
For dynamic parameters, the change is applied in both the server parameter file and memory. No static parameter change is allowed.)
For dynamic parameters, we can also specify the DEFERRED keyword. When specified, the change is effective only for future sessions.
HERE ARE A FEW EXAMPLES
===========================
The following statement affects all instances. However, the values are only effective for the current instances, they are not written to binary SPFILE.
ALTER SYSTEM SET OPEN_CURSORS=500 SID='*' SCOPE=MEMORY;
The next statement resets the value for the instance 'RACDB1'.
At this point, the database-wide setting becomes effective for SID of RACDB1.
ALTER SYSTEM RESET OPEN_CURSORS SCOPE=SPFILE sid='RACDB1';
To reset a parameter to its default value throughout the cluster database, use the command:
ALTER SYSTEM RESET OPEN_CURSORS SCOPE=SPFILE sid='*';
CREATING A SERVER PARAMETER FILE
=================================
The server parameter file is initially created from a text initialization parameter file (init.ora).
It must be created prior to its use in the STARTUP command.
The create SPFILE statement is used to create a server parameter file.
The following example creates a server parameter file from an initialization parameter file.
CREATE SPFILE FROM PFILE='/u01/oracle/product/920/dbs/initRAC1.ora';
Below is another example that illustrates creating a server parameter file and supplying a name.
CREATE SPFILE='/u01/oracle/product/920/dbs/racdb_spfile.ora'
FROM PFILE='/u01/oracle/product/920/dbs/init.ora';
EXPORTING THE SERVER PARAMETER FILE
===================================
We can export the server parameter file to create a traditional text initialization parameter file.
This would be useful for:
1) Creating backups of the server parameter file.
2) For diagnostic purposes to list all of the parameter values currently used by an instance.
3) Modifying the server parameter file by first exporting it, editing the output file, and then recreating it.
The following example creates a text initialization parameter file from the server parameter file:
CREATE PFILE FROM SPFILE;
The example below creates a text initialization parameter file from a server parameter file, where the names of the files are specified:
CREATE PFILE='/u01/oracle/product/920/dbs/racdb_init.ora'
FROM SPFILE='/u01/oracle/product/dbs/racdb_spfile.ora';
Refer to 'Oracle 9i Database Reference' for all the parameters that can be changed with an ALTER SYSTEM command...
IS MY DATABASE USING SPFILE ?
=============================
Am I using spfile or pfile ?
The following query will let you know..
1) SQL> SELECT name,value FROM v$parameter WHERE name = 'spfile';
NAME VALUE
---------- --------------------------------------------------
spfile /fsys1/oracle/product/9.2.0/spfileTEST.ora
2) SQL> show parameter spfile;
The v$spparameter view
The contents of the SPFILE can be obtained from the V$SPPARAMETER view:
SQL> ALTER SYSTEM SET timed_statistics=FALSE SCOPE=SPFILE;
System altered.
SQL> SELECT name,value FROM v$parameter WHERE name='timed_statistics';
NAME VALUE
-------------------- ---------------------
timed_statistics TRUE
SQL> SELECT name,value FROM v$spparameter WHERE name='timed_statistics';
NAME VALUE
-------------------- ---------------------
timed_statistics FALSE
Subscribe to:
Posts (Atom)