Whats new in Oracle Apps R12
Key points to Note:
1)Oracle Applications Release 12 is the latest release in the chain of E-Business Suite Releases by Oracle.
2)This release came up with the new file system model
Friday, December 31, 2010
Difference between 11i and R12
Thursday, December 30, 2010
Socket to Servlet conversion - Oracle Apps 11i
A)How to identify the Forms connect mode:
grep -i connectMode $COMMON_TOP/html/bin/appsweb_$CONTEXT_NAME.cfg
If the above command returns connect mode as http/https -> The forms are running in Servlet Mode:
If the above command returns connect mode as socket -> The forms are running in Socket Mode:
Socket to Servlet conversion (vice - versa) - Oracle Apps 11i
A)In Servlet mode the context file values looks like the following:
<server_url oa_var="s_forms_servlet_serverurl">/forms/formservlet</server_url>
<servlet_comment oa_var="s_forms_servlet_comment"/>
<forms_connect oa_var="s_frmConnectMode">https</forms_connect>
B)In Socket mode the context file values looks like the following:
$APPL_TOP/admin/SID_hostname.xml
<server_url oa_var="s_forms_servlet_serverurl"/>
<servlet_comment oa_var="s_forms_servlet_comment">#</servlet_comment>
<forms_connect oa_var="s_frmConnectMode">socket</forms_connect>
C)Edit the context file as required (servlet or socket) and run the autoconfig to reflect the change
D)Verify the Forms connect mode and start the services
grep -i connectMode $COMMON_TOP/html/bin/appsweb_$CONTEXT_NAME.cfg
check the connectMode socket or http(s)
For more information on socket or servlet mode please go to http://www.dbatutor.com/2010/12/forms-servlet-or-socket-mode-which-is.html
Forms Servlet or Socket Mode - Which is better ?
Forms Servlet or Socket Mode - Which is better ?
A good article from Steve Chan Blog (http://blogs.oracle.com/stevenChan)
Many products within the Oracle E-Business Suite have screens that are built with Oracle Forms. Oracle Forms can be run in either servlet mode or socket mode. Apps 11i is based on Forms 6i and is configured to run in socket mode by default. Apps 12 is based on Forms 10g and is configured to run in servlet mode by default. What are these modes, and which is better?
What is Forms Servlet Mode?
The Forms Listener Servlet is a Java servlet that delivers the ability to run Oracle Forms applications over HTTP and HTTPS connections. It manages the creation of a Forms Server Runtime process for each client, as well as network communications between the client and its associated Forms Server Runtime process.
The desktop client sends HTTP requests and receives HTTP responses from the web server. The HTTP Listener on the web server acts as the network endpoint for the client, keeping other servers and ports from being exposed at the firewall.
What is Forms Socket Mode?
Initial releases of the Oracle Forms Server product used a simple method for connecting the client to the server. The connection from the desktop client to the Forms Listener process was accomplished using a direct socket connection. The direct socket connection mode was suitable for companies providing thin client access to Forms applications within their corporate local area networks. For the direct socket connection mode, the client had to be able to see the server and had to have permission to establish a direct network connection.
Although the direct socket connection mode is perfectly suited for deployments within a company’s internal network, it's not the best choice for application deployment via unsecured network paths via the Internet. A company connected to the Internet typically employs a strict policy defining the types of network connections that can be made by Internet clients to secure corporate networks. Permitting a direct socket connection from an external client exposes the company to potential risk because the true identity of the client can be hard to determine.
Servlet Mode Advantages
1. HTTP and HTTPS traffic is easily recognizable by routers, while socket mode communications is generally considered suspect and treated on an exception basis.
2. Existing networking hardware can be used to support basic functions such as load-balancing and packet encryption for network transit.
3. More resilient to network and firewall reconfigurations.
4. More robust: servlet connections can be reestablished if network connections drop unexpectedly for Forms, Framework, and JSP-based pages.
5. Is the only supported method for generic Oracle Forms customers, and therefore is more thoroughly tested by the Forms and E-Business Suite product groups.
6. Performance traffic can be monitored via tools like Oracle Real User Experience Insight (RUEI).
7. Socket mode is not supported on Windows-based server platforms.
Socket Mode Advantages
1. Uses up to 40% less bandwidth than Forms servlet mode. This may be perceived by Wide Area Network (WAN) users as causing slower responsiveness, depending upon network latency.
2. Uses fewer application-tier JVM resources than servlet mode, due to fewer TCP turns and lack of overhead associated with HTTP POST handling.
Switching Apps Deployments Between Modes
Due to its numerous advantages, Forms servlet mode is the preferred and recommended deployment model for Forms on the web.
There may be circumstances where you need to switch between the default Forms modes. You might wish to switch your Oracle E-Business Suite Release 12 environment to socket mode to improve performance or reduce network load. You might wish to switch your Apps 11i environment to servlet mode as part of your rollout to external web-based end-users outside of your organization.
If you're running Apps 11i and would like to switch to servlet mode, see:
· Using Forms Listener Servlet with Oracle Applications 11i (Note 201340.1)
If you're running Apps 12 and would like to switch to socket mode, see:
· Using Forms Socket Mode in Oracle Applications Release 12 (Note 384241.1)
A good article from Steve Chan Blog (http://blogs.oracle.com/stevenChan)
Many products within the Oracle E-Business Suite have screens that are built with Oracle Forms. Oracle Forms can be run in either servlet mode or socket mode. Apps 11i is based on Forms 6i and is configured to run in socket mode by default. Apps 12 is based on Forms 10g and is configured to run in servlet mode by default. What are these modes, and which is better?
What is Forms Servlet Mode?
The Forms Listener Servlet is a Java servlet that delivers the ability to run Oracle Forms applications over HTTP and HTTPS connections. It manages the creation of a Forms Server Runtime process for each client, as well as network communications between the client and its associated Forms Server Runtime process.
The desktop client sends HTTP requests and receives HTTP responses from the web server. The HTTP Listener on the web server acts as the network endpoint for the client, keeping other servers and ports from being exposed at the firewall.
What is Forms Socket Mode?
Initial releases of the Oracle Forms Server product used a simple method for connecting the client to the server. The connection from the desktop client to the Forms Listener process was accomplished using a direct socket connection. The direct socket connection mode was suitable for companies providing thin client access to Forms applications within their corporate local area networks. For the direct socket connection mode, the client had to be able to see the server and had to have permission to establish a direct network connection.
Although the direct socket connection mode is perfectly suited for deployments within a company’s internal network, it's not the best choice for application deployment via unsecured network paths via the Internet. A company connected to the Internet typically employs a strict policy defining the types of network connections that can be made by Internet clients to secure corporate networks. Permitting a direct socket connection from an external client exposes the company to potential risk because the true identity of the client can be hard to determine.
Servlet Mode Advantages
1. HTTP and HTTPS traffic is easily recognizable by routers, while socket mode communications is generally considered suspect and treated on an exception basis.
2. Existing networking hardware can be used to support basic functions such as load-balancing and packet encryption for network transit.
3. More resilient to network and firewall reconfigurations.
4. More robust: servlet connections can be reestablished if network connections drop unexpectedly for Forms, Framework, and JSP-based pages.
5. Is the only supported method for generic Oracle Forms customers, and therefore is more thoroughly tested by the Forms and E-Business Suite product groups.
6. Performance traffic can be monitored via tools like Oracle Real User Experience Insight (RUEI).
7. Socket mode is not supported on Windows-based server platforms.
Socket Mode Advantages
1. Uses up to 40% less bandwidth than Forms servlet mode. This may be perceived by Wide Area Network (WAN) users as causing slower responsiveness, depending upon network latency.
2. Uses fewer application-tier JVM resources than servlet mode, due to fewer TCP turns and lack of overhead associated with HTTP POST handling.
Switching Apps Deployments Between Modes
Due to its numerous advantages, Forms servlet mode is the preferred and recommended deployment model for Forms on the web.
There may be circumstances where you need to switch between the default Forms modes. You might wish to switch your Oracle E-Business Suite Release 12 environment to socket mode to improve performance or reduce network load. You might wish to switch your Apps 11i environment to servlet mode as part of your rollout to external web-based end-users outside of your organization.
If you're running Apps 11i and would like to switch to servlet mode, see:
· Using Forms Listener Servlet with Oracle Applications 11i (Note 201340.1)
If you're running Apps 12 and would like to switch to socket mode, see:
· Using Forms Socket Mode in Oracle Applications Release 12 (Note 384241.1)
Enable Multi Language - NLS with Oracle Applications 11i
How to enable Mutilple languages (NLS) with Oracle Applications 11i instance
The below process guides through the step by step process of enable multi language in Oracle Ebiz - 11i instance.
Have taken French as example language to enable in the below steps, You can choose the language you wish to enable in the oracle applications.
Follow the below process to enable the NLS, Please note the base language will be English only.
1)Install the required Language (French) from OAM.
Enabling the Arabic from OAM, folllow the below steps
Login to OAM as a sysadmin -> sitemap -> License Manager -> License -> Languages And license the following language
Enable the "French"
Make sure you should not change the base language, it should be english.
2)Validate the language activated
Login to application with apps user and execute the below command.
Select NLS_LANGUAGE from FND_LANGUAGES where INSTALLED_FLAG in ('B','I');
Make sure you get below result.
1. AMERICAN
2. French
Here base lang is American
3)Stop the Oracle application services
cd $OAD_TOP/admin/scripts
./adstpall.sh apps/apps
4)Run Maintain Multi-lingual Tables
AD Administration Main Menu > Maintain Applications Database Entities Menu using adadmin.
5)Run Update current view snapshot from adadmin
adadmin -> Maintain Applications Files -> Maintain snapshot information -> Update current view snapshot)
6)Run the Translation Synchronization Patch utility (adgennls.pl)
Login to applmgr user in application tier, make sure enviorment is sourced properly and execute the following command
perl $AD_TOP/bin/adgennls.pl
Above command will genertate the manifest file which you need to upload to support. Menifist file will be on following location:
$APPL_TOP/admin/$TWO_TASK/out/adgennls.txt
7)Upload the manifest file to oracle support, use the below steps.
Upload the adgennls.txt file to https://updates.oracle.com/TransSync make sure you give your exact email id.
Once you upload the file, select the option Get Latest Translstions and click on next it will provide you the patch number which support is going to generate for NLS syncronization.
A Translation synchronization patch will be generated specifically based on your manifest for each of your active language. The patch will synchronize the language file versions and American English file versions in your Applications instance.
Note : it is suggested option: If you select the option to get latest translations, the patch will also bring your translations up-to-date.
If you do not receive any email regarding your request within 48 hours, you may contact Oracle Support for assistance.
this patch will generate NLS media patck, it will take some time to generate
8)Apply the Translation Synchronization patch
Follow the instructions in the README file to apply the patch.
note: Before Applying patch make sure enough space in appl top directory, patch initially copy all required files to appl top create new lang patch Choose a directory that contains enough space for the NLS updates (about 10 GB for each language Minimum).
9)AD Administration utility to generate message files from the Generate Applications Files menu.
10)Compile the invalid objects using utlrp.
11)Complie Forms and Reports
12)Complile Apps schema and validate apps schema
13)Bring up the applications services
The below process guides through the step by step process of enable multi language in Oracle Ebiz - 11i instance.
Have taken French as example language to enable in the below steps, You can choose the language you wish to enable in the oracle applications.
Follow the below process to enable the NLS, Please note the base language will be English only.
1)Install the required Language (French) from OAM.
Enabling the Arabic from OAM, folllow the below steps
Login to OAM as a sysadmin -> sitemap -> License Manager -> License -> Languages And license the following language
Enable the "French"
Make sure you should not change the base language, it should be english.
2)Validate the language activated
Login to application with apps user and execute the below command.
Select NLS_LANGUAGE from FND_LANGUAGES where INSTALLED_FLAG in ('B','I');
Make sure you get below result.
1. AMERICAN
2. French
Here base lang is American
3)Stop the Oracle application services
cd $OAD_TOP/admin/scripts
./adstpall.sh apps/apps
4)Run Maintain Multi-lingual Tables
AD Administration Main Menu > Maintain Applications Database Entities Menu using adadmin.
5)Run Update current view snapshot from adadmin
adadmin -> Maintain Applications Files -> Maintain snapshot information -> Update current view snapshot)
6)Run the Translation Synchronization Patch utility (adgennls.pl)
Login to applmgr user in application tier, make sure enviorment is sourced properly and execute the following command
perl $AD_TOP/bin/adgennls.pl
Above command will genertate the manifest file which you need to upload to support. Menifist file will be on following location:
$APPL_TOP/admin/$TWO_TASK/out/adgennls.txt
7)Upload the manifest file to oracle support, use the below steps.
Upload the adgennls.txt file to https://updates.oracle.com/TransSync make sure you give your exact email id.
Once you upload the file, select the option Get Latest Translstions and click on next it will provide you the patch number which support is going to generate for NLS syncronization.
A Translation synchronization patch will be generated specifically based on your manifest for each of your active language. The patch will synchronize the language file versions and American English file versions in your Applications instance.
Note : it is suggested option: If you select the option to get latest translations, the patch will also bring your translations up-to-date.
If you do not receive any email regarding your request within 48 hours, you may contact Oracle Support for assistance.
this patch will generate NLS media patck, it will take some time to generate
8)Apply the Translation Synchronization patch
Follow the instructions in the README file to apply the patch.
note: Before Applying patch make sure enough space in appl top directory, patch initially copy all required files to appl top create new lang patch Choose a directory that contains enough space for the NLS updates (about 10 GB for each language Minimum).
9)AD Administration utility to generate message files from the Generate Applications Files menu.
10)Compile the invalid objects using utlrp.
11)Complie Forms and Reports
12)Complile Apps schema and validate apps schema
13)Bring up the applications services
Tuesday, December 28, 2010
Database Interview Questions - Basics I
Database
Q 1: What are the steps involved in database startup?
Ans: Start an Instance, Mount the database and Open the Database.
Q2: What are the steps involved in database shutdown?
Ans : Close the database ; Dismount the database and Shutdown the instance.
Q3: What is Archived Redo log?
Ans: Acrhived Redo Log consists of Redo Log files that have archived before being reused
Q4: What is a temporary segment?
Ans: Temporary segments are created by ORACLE when a sql statement needs a temporary work area to complete execution. When the statement finishes execution , the temporary segment extents are released to the system for future use.
Q5: What does a control file contain?
Ans: A control file records the physical structure of the database, database name,time stamp of database creation,max datafile limit,max redo log group,max log file members, database archive information,backup information..
Q6:What is a Tablespace?
Ans: A database is divided into Logical storage unit called tablespace. A tablespace used to grouped related logical structures together
Q7: Explain the relation ship among the database,tablespace and datafiles?
Ans: Each database logically divided in to one or more tablespaces. One or more datafiles are explicitly created for each tablespace.
Q8:What is Logical structure of the database?
Ans: Database àTablespacesàsegmentsàExtentsàOracle Blocks
Q9: What is the physical structure of the database?
Ans: Datafiles,Control File and redo log files.
Q10: What are Different types of segments?
Ans: Temp Segement, Undo Segment, Table Segment and Index Segment.
Q11: Can you name few DBMS Package names and their use?
Ans: DBMS_JOBS, DBMS_STATS,DBMS_SQLTUNE..
Q12: How Do you find whether the instance was started with PFILE or SPFILE?
Ans: Sho parameter spfile or query from v$parameter.
Q13: What Column differentiates the v$views to the gv$views and how?
Ans: The INST_ID Column which indicates the instance in a RAC environment the information came from ..
Q14: Explain an Ora -1555
Ans: You get this error when you get a snap shot too old within roolback.It can usually be solved by increasing the undo retention or increasing the size of rollbacks
Q15:What is Diffeernce between a temporary tablespace and a permanent tablespace?
Ans: A temporary tablespace is used for Sort Oprations of no sufficient space found in PGA. Permanent tablespace stores the true objects of dataase ..
Q16:How Do you add a datafile to a tablespace?
Ans: alter tablespaceadd datafile size ;
Q17: How Do you resize a datafile?
Ans: alter database datafileresize ;
Q18: What view Would you use to look at th size of a datafile?
Ans: dba_data_files,V$datafile
Q19: What view Would you use to determine free space in a tablespace?
Ans: dba_free_space
Q20: How can you gather statistics on a table?
Ans: The analyze command or using DBMS_STATS package.
Q21: List out the views names we can use to find about undo usage?
Ans: V$undostat,dba_undo_segs
Q22: What is view we can use to find out database locks?
Ans: v$lock
Q23: Using which view will check for session and process details?
Ans: V$session,v$process
Q24: Using which view will you find out , how much % Operation or job completed sofar it is running in database?
Ans: v$session_longops
Q25: How will you find out running job names from database?
Ans: Querying from dba_jobs,Dba_jobs_running views
Q26:How will find out Database creation time details? What is the column name?
Ans: v$database; created is column
Q27: How will you find out instance startup time?
Ans: v$instance,startup_time
Q28: How do you find archive is enabled for database?
Ans: archive log list or v$database ,log_mode Cloumn
Q29: How will you find active/inactive session count from database?
Ans: select status,count(*) from v$session group by status;
Q30: explain about temporary tablespace groups?
Ans: It is 10g new feature . it is a group of temporary tablespaces. We can get more details from dba_tablespace_groups if it is enabled.
Q31: What is undo_retention?
Ans : Time specification for undo segments hold the data ..and these reusable are reusable..
Q32: How will you find undo management is auto or manual?
Ans : Sho parameter undo_management
Q33: If your database contain 4 undo tablespaces..how will you find out which undo tablespace is used by database?
Ans: Sho parameter undo_tablespace
Q34: If you database contain 5 temporary tablespaces how will you find out what is default temporary tablespace to databse?
Ans: By querying database_properties
Q35. What is alert log file and how will you find out the location of file from database?
Ans : Sho parameter dump
UNIX:
Q36: How can you determine if an Oracle Instance is up from the Operating system level?
Ans: ps –efgrep smon/pmon
Q37: How can u find dead processes?
Ans: ps –efgrep zombie or who –d or ps –efgrep defunc
Q38: Give the command to display space usage on the UNIX File system?
Ans: df –lk or df –lh or df –k/h
Q39: Explain sar and vmstat?
Ans: sar used for to get system activity report and Vmstat reports on virtuval memory statistics for processes .
Q40: How do you find the Load average of a server?
Ans: using uptime command.
Q41: How do you find out swap usage on a server?
Ans: free –g
Q42: How will you kill a process id at OS level ?
Ans: kill -9
Q43: How will you find out the count of Oracle processes running on server?
Ans: ps –efgrepwc –l
Q44: How will you check if any Rman backup is running?
Ans: ps –efgrep rman
Q45: How will check cron schedule job details?
Ans: Crontab –l
Q46 : How will find ASM instance is running on server?
Ans : ps –efgrep +ASM
Q47 : I have a very big file at OS and I want see last 50 lines of the file only..Which command will you use?
Ans : tail -50
Q48: How can you find , file last accessed ( modified )date?
Ans : ls –lQ49: How will you find out howmany instances are running on a server?
Ans : ps –efgrep smon (gives all instance names which are running)
Q50 : What is the command we can use to find trace of process at OS level?(means what process is doing)
And : strace –p
Q 1: What are the steps involved in database startup?
Ans: Start an Instance, Mount the database and Open the Database.
Q2: What are the steps involved in database shutdown?
Ans : Close the database ; Dismount the database and Shutdown the instance.
Q3: What is Archived Redo log?
Ans: Acrhived Redo Log consists of Redo Log files that have archived before being reused
Q4: What is a temporary segment?
Ans: Temporary segments are created by ORACLE when a sql statement needs a temporary work area to complete execution. When the statement finishes execution , the temporary segment extents are released to the system for future use.
Q5: What does a control file contain?
Ans: A control file records the physical structure of the database, database name,time stamp of database creation,max datafile limit,max redo log group,max log file members, database archive information,backup information..
Q6:What is a Tablespace?
Ans: A database is divided into Logical storage unit called tablespace. A tablespace used to grouped related logical structures together
Q7: Explain the relation ship among the database,tablespace and datafiles?
Ans: Each database logically divided in to one or more tablespaces. One or more datafiles are explicitly created for each tablespace.
Q8:What is Logical structure of the database?
Ans: Database àTablespacesàsegmentsàExtentsàOracle Blocks
Q9: What is the physical structure of the database?
Ans: Datafiles,Control File and redo log files.
Q10: What are Different types of segments?
Ans: Temp Segement, Undo Segment, Table Segment and Index Segment.
Q11: Can you name few DBMS Package names and their use?
Ans: DBMS_JOBS, DBMS_STATS,DBMS_SQLTUNE..
Q12: How Do you find whether the instance was started with PFILE or SPFILE?
Ans: Sho parameter spfile or query from v$parameter.
Q13: What Column differentiates the v$views to the gv$views and how?
Ans: The INST_ID Column which indicates the instance in a RAC environment the information came from ..
Q14: Explain an Ora -1555
Ans: You get this error when you get a snap shot too old within roolback.It can usually be solved by increasing the undo retention or increasing the size of rollbacks
Q15:What is Diffeernce between a temporary tablespace and a permanent tablespace?
Ans: A temporary tablespace is used for Sort Oprations of no sufficient space found in PGA. Permanent tablespace stores the true objects of dataase ..
Q16:How Do you add a datafile to a tablespace?
Ans: alter tablespace
Ans: alter database datafile
Ans: dba_data_files,V$datafile
Q19: What view Would you use to determine free space in a tablespace?
Ans: dba_free_space
Q20: How can you gather statistics on a table?
Ans: The analyze command or using DBMS_STATS package.
Q21: List out the views names we can use to find about undo usage?
Ans: V$undostat,dba_undo_segs
Q22: What is view we can use to find out database locks?
Ans: v$lock
Q23: Using which view will check for session and process details?
Ans: V$session,v$process
Q24: Using which view will you find out , how much % Operation or job completed sofar it is running in database?
Ans: v$session_longops
Q25: How will you find out running job names from database?
Ans: Querying from dba_jobs,Dba_jobs_running views
Q26:How will find out Database creation time details? What is the column name?
Ans: v$database; created is column
Q27: How will you find out instance startup time?
Ans: v$instance,startup_time
Q28: How do you find archive is enabled for database?
Ans: archive log list or v$database ,log_mode Cloumn
Q29: How will you find active/inactive session count from database?
Ans: select status,count(*) from v$session group by status;
Q30: explain about temporary tablespace groups?
Ans: It is 10g new feature . it is a group of temporary tablespaces. We can get more details from dba_tablespace_groups if it is enabled.
Q31: What is undo_retention?
Ans : Time specification for undo segments hold the data ..and these reusable are reusable..
Q32: How will you find undo management is auto or manual?
Ans : Sho parameter undo_management
Q33: If your database contain 4 undo tablespaces..how will you find out which undo tablespace is used by database?
Ans: Sho parameter undo_tablespace
Q34: If you database contain 5 temporary tablespaces how will you find out what is default temporary tablespace to databse?
Ans: By querying database_properties
Q35. What is alert log file and how will you find out the location of file from database?
Ans : Sho parameter dump
UNIX:
Q36: How can you determine if an Oracle Instance is up from the Operating system level?
Ans: ps –efgrep smon/pmon
Q37: How can u find dead processes?
Ans: ps –efgrep zombie or who –d or ps –efgrep defunc
Q38: Give the command to display space usage on the UNIX File system?
Ans: df –lk or df –lh or df –k/h
Q39: Explain sar and vmstat?
Ans: sar used for to get system activity report and Vmstat reports on virtuval memory statistics for processes .
Q40: How do you find the Load average of a server?
Ans: using uptime command.
Q41: How do you find out swap usage on a server?
Ans: free –g
Q42: How will you kill a process id at OS level ?
Ans: kill -9
Ans: ps –efgrep
Ans: ps –efgrep rman
Q45: How will check cron schedule job details?
Ans: Crontab –l
Q46 : How will find ASM instance is running on server?
Ans : ps –efgrep +ASM
Q47 : I have a very big file at OS and I want see last 50 lines of the file only..Which command will you use?
Ans : tail -50
Ans : ls –l
Ans : ps –efgrep smon (gives all instance names which are running)
And : strace –p
Restore RMAN backup to different server - Database Clone from RMAN Hot backup
How to restore RMAN backup to different server and Recover the database
The situation here is we have taken an RMAN backup on PROD (Source) and we need to restore those backup pieces on different server as TEST (target) database.
Its actually cloning the database from PROD(SOURCE) to TEST(target) using RMAN backup piecies taken on source.
Please follow the following steps outlined below.
Please note source (PROD) is where the backup piecies are available, we need to clone and create a database with another name TEST (target) on different server.
In target Database (TEST) perform the below
Modify the db_name in the $ORACLE_HOME/dbs/init
Eg: PROD
Start database in nomount mode and restore controlfile (this controlfile should be the RMAN backup controlfile after the RMAN backup on source)
Copy the control file backup piece from source to target and restore the controlfile.
rman target /
RMAN>startup nomount;
RMAN>restore controlfile from '/u01/backup/ccf_c-1250176543-20081031-01';
--Mount the database
RMAN>alter database mount;
Copy all the backup piecies of source(PROD) database to target(TEST) server.
Copy all the backup piecies of source(PROD) database to target(TEST) server using scp.
Once copy completes,Run the following script to list the backup pieces available:
Register all the copied backup piecies in the controlfile. (/u01/backup is the location of copied backup piecies in the target)
RMAN target /
RMAN>catalog backuppiece '/u01/backup/LEVEL0_1-10-2010_dxxthtcn_1_1';
--Run the above command for all the backup piecies copied from the source (PROD) to target (TEST)
List the archive logs. Note the last sequence number
The last sequence number should be used in the set until clause in the rman restore script.
It is referred to $SEQ
RMAN>list backup of archivelog all;
In a separate sqlplus session, rename the log files
sqlplus ‘/ as sysdba’
alter database rename file '/prod/oradata/log01.dbf' to '/test/oradata/log01.dbf';
alter database rename file '/prod/oradata/log02.dbf' to '/test/oradata/log02.dbf';
Run the following SQL to create the SQL to create the temp files for the temporary tablespace:
sqlplus ‘/ as sysdba’
select 'alter database rename file '''name''' to '''replace(name,'
Run the generated SQL
alter database rename file '/prod/oradata/temp01.dbf' to '/test/oradata/temp01.dbf' ;
alter database rename file '/prod/oradata/temp02.dbf' to '/test/oradata/temp02.dbf' ;
Use the following SQL to generate the SQL to create the set new name commands to go into the rman command file for the restore
Sql> select 'set newname for datafile ' file# ' to ''' replace(name,'
Restore the database & Recover it:
Run the following rman restore command, pasting in the result of the above SQL where indicated.
Replace the SEQ+1 value with the sequence number + 1 obtained in step Note the last sequence number.
RMAN target /
run { set until sequence
-- Paste in set newname commands from previous SQL here restore database;
switch datafile all;
recover database;
}
Open the database and configure the database
Once Successful recovery of the database Recreate the controlfile:
Sql> alter database backup control file to trace;
Modify the trace file generated in the udump as required (file locations..etc) and SET the DB Name to TARGET SID.
Bring down the database
Modify the db_name parameter in the init
Eg: TEST
Recreate the controlfile:
Open the database:
Sql> alter database open resetlogs;
Tuesday, December 21, 2010
Database Performence Tuning Scripts
Performence Tuning Scripts:
The below are some of the scripts usefull for Performence tuning.
Prompt
Prompt Cache hit ratio
prompt
select 1-(phy.value / (cur.value + con.value)) "Cache Hit Ratio",round((1-(phy.value / (cur.value + con.value)))*100,2) "% Ratio"from v$sysstat cur, v$sysstat con, v$sysstat phywhere cur.name = 'db block gets' andcon.name = 'consistent gets' andphy.name = 'physical reads';
/
Prompt
Prompt Another Buffer Cache hit ratio Calculation
prompt
column "logical_reads" format 99,999,999,999 column "phys_reads" format 999,999,999 column "phy_writes" format 999,999,999 select a.value + b.value "logical_reads", c.value "phys_reads",round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO" from v$sysstat a, v$sysstat b, v$sysstat cwhere a.statistic# = 38 and b.statistic# = 39 and c.statistic# = 40;
/
prompt
prompt Data Dictionary Hit Ratio should be over 90 percent
prompt
select sum(gets) "Data Dict. Gets",sum(getmisses) "Data Dict. Cache Misses",round((1-(sum(getmisses)/sum(gets)))*100) "DATA DICT CACHE HIT RATIO",round(sum(getmisses)*100/sum(gets)) "% MISSED"from v$rowcache;
/
prompt
prompt Library Cache Miss Ratio
prompt
select sum(pins) "executions",sum(reloads) "Cache Misses",round((1-(sum(reloads)/sum(pins)))*100) "LIBRARY CACHE HIT RATIO",round(sum(reloads)*100/sum(pins)) "% Missed" from v$librarycache;
/
prompt
prompt More Library Cache stats
prompt
select namespace,trunc(gethitratio*100) "Hit Ratio",trunc(pinhitratio*100) "Pin Hit Ratio",reloads "Reloads"from v$librarycache;/
prompt
prompt Another Library Cache Calculation, total reloads should be as close to 0 as possible.
prompt
column libcache format 99.99 heading 'Percentage' jus censelect sum(pins) "Total Pins", sum(reloads) "Total Reloads",sum(reloads)/sum(pins) *100 libcachefrom v$librarycache;
/
prompt
prompt Redo Log Buffer should be as close to 0 as possible
prompt
select substr(name,1,30),value from v$sysstat where name ='redo log space requests';
/
prompt
prompt Redo Log Contention, all ratios less than 1
prompt
SET feedback OFFCOLUMN name FORMAT a15COLUMN gets FORMAT 99999999COLUMN misses FORMAT 999999COLUMN immediate_gets FORMAT 99999999 HEADING 'IMM_GETS'COLUMN immediate_misses FORMAT 99999999 HEADING 'IMM_MISSES'
PROMPT Examining Contention for Redo Log Buffer Latches...
PROMPT ----------------------------------------------------
SELECT name, gets, misses, immediate_gets, immediate_misses,Decode(gets,0,0,misses/gets*100) ratio1,Decode(immediate_gets+immediate_misses,0,0,immediate_misses/(immediate_gets+immediate_misses)*100) ratio2FROM v$latch WHERE name IN ('redo allocation', 'redo copy');
/
prompt
prompt Disk Vs. Memory Sorts. Try to keep the disk/memory ratio to less than .10 by increasing the sort_area_size
prompt
SET HEADING OFFSET FEEDBACK OFFCOLUMN name FORMAT a30COLUMN value FORMAT 99999990
SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');
/
prompt
prompt Initialization Parameters
prompt
select substr(name,1,35) "Parameter" ,substr(value,1,35) "Value" from v$parameter order by name asc;
/
The below are some of the scripts usefull for Performence tuning.
Prompt
Prompt Cache hit ratio
prompt
select 1-(phy.value / (cur.value + con.value)) "Cache Hit Ratio",round((1-(phy.value / (cur.value + con.value)))*100,2) "% Ratio"from v$sysstat cur, v$sysstat con, v$sysstat phywhere cur.name = 'db block gets' andcon.name = 'consistent gets' andphy.name = 'physical reads';
/
Prompt
Prompt Another Buffer Cache hit ratio Calculation
prompt
column "logical_reads" format 99,999,999,999 column "phys_reads" format 999,999,999 column "phy_writes" format 999,999,999 select a.value + b.value "logical_reads", c.value "phys_reads",round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO" from v$sysstat a, v$sysstat b, v$sysstat cwhere a.statistic# = 38 and b.statistic# = 39 and c.statistic# = 40;
/
prompt
prompt Data Dictionary Hit Ratio should be over 90 percent
prompt
select sum(gets) "Data Dict. Gets",sum(getmisses) "Data Dict. Cache Misses",round((1-(sum(getmisses)/sum(gets)))*100) "DATA DICT CACHE HIT RATIO",round(sum(getmisses)*100/sum(gets)) "% MISSED"from v$rowcache;
/
prompt
prompt Library Cache Miss Ratio
prompt
select sum(pins) "executions",sum(reloads) "Cache Misses",round((1-(sum(reloads)/sum(pins)))*100) "LIBRARY CACHE HIT RATIO",round(sum(reloads)*100/sum(pins)) "% Missed" from v$librarycache;
/
prompt
prompt More Library Cache stats
prompt
select namespace,trunc(gethitratio*100) "Hit Ratio",trunc(pinhitratio*100) "Pin Hit Ratio",reloads "Reloads"from v$librarycache;/
prompt
prompt Another Library Cache Calculation, total reloads should be as close to 0 as possible.
prompt
column libcache format 99.99 heading 'Percentage' jus censelect sum(pins) "Total Pins", sum(reloads) "Total Reloads",sum(reloads)/sum(pins) *100 libcachefrom v$librarycache;
/
prompt
prompt Redo Log Buffer should be as close to 0 as possible
prompt
select substr(name,1,30),value from v$sysstat where name ='redo log space requests';
/
prompt
prompt Redo Log Contention, all ratios less than 1
prompt
SET feedback OFFCOLUMN name FORMAT a15COLUMN gets FORMAT 99999999COLUMN misses FORMAT 999999COLUMN immediate_gets FORMAT 99999999 HEADING 'IMM_GETS'COLUMN immediate_misses FORMAT 99999999 HEADING 'IMM_MISSES'
PROMPT Examining Contention for Redo Log Buffer Latches...
PROMPT ----------------------------------------------------
SELECT name, gets, misses, immediate_gets, immediate_misses,Decode(gets,0,0,misses/gets*100) ratio1,Decode(immediate_gets+immediate_misses,0,0,immediate_misses/(immediate_gets+immediate_misses)*100) ratio2FROM v$latch WHERE name IN ('redo allocation', 'redo copy');
/
prompt
prompt Disk Vs. Memory Sorts. Try to keep the disk/memory ratio to less than .10 by increasing the sort_area_size
prompt
SET HEADING OFFSET FEEDBACK OFFCOLUMN name FORMAT a30COLUMN value FORMAT 99999990
SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');
/
prompt
prompt Initialization Parameters
prompt
select substr(name,1,35) "Parameter" ,substr(value,1,35) "Value" from v$parameter order by name asc;
/
Monday, December 20, 2010
Database Relink issue
10.2.0.5 database install, Relink issue
When attempting to Install 10.2.0.5.0 database on Linux 64bit, below relink error encoutered.
The cause of the issue is Missing pre-requisite RPM "compat-libstdc++-33-3.2.3-61(x86_64)", The issue solved by installing the missing RPM "compat-libstdc++-33-3.2.3-61(x86_64)" and completed the 10.2.0.5.0 Software installation.
ERROR
-------
INFO: /usr/bin/ld: warning: libstdc++.so.5, needed by /u01/oracle/product/102/ctx/lib//libsc_fa.so, not found (try using -rpath or -rpath-link)/u01/oracle/product/102/ctx/lib/libwv_core.so: undefined reference to `__cxa_pure_virtual@CXXABI_1.2'/u01/oracle/product/102/ctx/lib//libsc_fa.so: undefined reference to `__gxx_personality_v0@CXXABI_1.2'/u01/oracle/product/102/ctx/lib/libwv_core.so: undefined reference to `INFO: vtable for __cxxabiv1::__class_type_info@GLIBCPP_3.2'/u01/oracle/product/102/ctx/lib/libsc_ch.so: undefined reference to `__gxx_personality_v0'/u01/oracle/product/102/ctx/lib//libsc_fa.so: undefined reference to `_Unwind_Resume'/u01/oracle/product/102/ctx/lib/libwv_core.so: undefined reference to `vtable for __cxxabiv1::__si_class_type_info@GLIBCPP_3.2'/u01/oracle/product/102/ctx/lib/libwv_core.so: undefined reference to `std::ios_base::Init::Init()@GLIBCPP_3.2'/u01/oracle/product/102/ctINFO: x/lib/libwv_core.so: undefined reference to `std::ios_base::Init::~Init()@GLIBCPP_3.2'/u01/oracle/product/102/ctx/lib/libwv_core.so: undefined reference to `operator delete(void*)@GLIBCPP_3.2'collect2: ld returned 1 exit statusINFO: make: *** [ctxhx] Error 1
Database upgrade bit conversion issue
DB upgrade Bit conversion Issue from 10.2.0.3 to 10.2.0.5
---------------------------------------------------------------------
We are upgrading the database from 10.2.0.3 to 10.2.0.5 ,we have applied the patch 8202632 on oracle home and it was successful.
have opened the database in upgrade mode and running catupgrade script,while running catupgrade script getting the below error.
Error:
create or replace package body DBMS_ASSERT wrapped
*
ERROR at line 1:
ORA-06544: PL/SQL: internal error, arguments: [56319], [], [], [], [], [], [],
[]
ORA-06553: PLS-801: internal error [56319]
No errors.
CREATE OR REPLACE FUNCTION version_script
*
ERROR at line 1:
ORA-06544: PL/SQL: internal error, arguments: [56319], [], [], [], [], [], [],
[]
ORA-06553: PLS-801: internal error [56319]
Solution:
---------
The issue here is 10.2.0.3 database is 32bit and we are trying to upgrade the database to 10.2.0.5 with 64bit version.
we thought catupgrd.sql will also upgrade the database and convert the bit version - (catupgrd.sql calls utlip.sql) ,
But Converting 32-bit databases to 64-bit databases is not automatic for the 10.2.0.5 upgrade.
So to fix this issue, Performed below
------------------------------------
1. Change catupgrd.sql script to always execute utlip.sql
cd $ORACLE_HOME/rdbms/admin
vi catupgrd.sql change:
==> @@&utlip_file
to:==> @@utlip.sql
2. Rerun upgrade with echo on
SQL> spool /tmp/upgrade.logSQL> set echo onSQL> @?/rdbms/admin/catupgrd.sql
---------------------------------------------------------------------
We are upgrading the database from 10.2.0.3 to 10.2.0.5 ,we have applied the patch 8202632 on oracle home and it was successful.
have opened the database in upgrade mode and running catupgrade script,while running catupgrade script getting the below error.
Error:
create or replace package body DBMS_ASSERT wrapped
*
ERROR at line 1:
ORA-06544: PL/SQL: internal error, arguments: [56319], [], [], [], [], [], [],
[]
ORA-06553: PLS-801: internal error [56319]
No errors.
CREATE OR REPLACE FUNCTION version_script
*
ERROR at line 1:
ORA-06544: PL/SQL: internal error, arguments: [56319], [], [], [], [], [], [],
[]
ORA-06553: PLS-801: internal error [56319]
Solution:
---------
The issue here is 10.2.0.3 database is 32bit and we are trying to upgrade the database to 10.2.0.5 with 64bit version.
we thought catupgrd.sql will also upgrade the database and convert the bit version - (catupgrd.sql calls utlip.sql) ,
But Converting 32-bit databases to 64-bit databases is not automatic for the 10.2.0.5 upgrade.
So to fix this issue, Performed below
------------------------------------
1. Change catupgrd.sql script to always execute utlip.sql
cd $ORACLE_HOME/rdbms/admin
vi catupgrd.sql change:
==> @@&utlip_file
to:==> @@utlip.sql
2. Rerun upgrade with echo on
SQL> spool /tmp/upgrade.logSQL> set echo onSQL> @?/rdbms/admin/catupgrd.sql
Oracle Application Database (10gR2) - Migration to Linux
Migrating Oracle E-Business Suite 11i Database 10g Release 2 (10GR2) to Linux
The following tasks provide clear step by step instructions on performing Oracle Apps database Migration to Linux from any operating system.
Section I: Prepare the source system for Export:-
1)Apply the Applications consolidated export/import utility patch:
Apply patch 4872830 to the source administration server node.
2) Apply latest Applications database preparation scripts patch;
Apply patch 7225862 to every application tier server node in the source system
3) Create a working directory:
$ mkdir /dd02/backup/expimp
4)Generate target database instance creation script aucrdb.sql:
$ sqlplus system/manager @$AU_TOP/patch/115/sql/auclondb.sql 10
5)Record Advanced Queue settings:
Copy the auque1.sql script from the $AU_TOP/patch/115/sql directory on the source administration server node to the working directory in the source database server node,
It generates auque2.sql.
$ sqlplus /nolog
SQL> connect / as sysdba;
SQL> @auque1.sql
6) Remove rebuild index parameter in spatial indexes:
Use SQL*Plus to connect to the source database as sysdba and run the following command:
SQL>select INDEX_NAME,PARAMETERS from dba_indexes where index_type='DOMAIN' and upper(parameters) like '%REBUILD%';
To remove the rebuild index parameter:
SQL> alter indexrebuild parameters
Eg:
alter index MST.MST_MD_ADM_BNDS_N1 rebuild parameters ('sdo_indx_dims=2 sdo_rtr_pctfree=10 tablespace=APPS_TS_TX_IDX');
alter index MST.MST_MD_HYDROS_N1 rebuild parameters ('sdo_indx_dims=2 sdo_rtr_pctfree=10 tablespace=APPS_TS_TX_IDX');
alter index HR.HR_LOCATIONS_SPT rebuild parameters ('sdo_indx_dims=2 sdo_rtr_pctfree=10 tablespace=APPS_TS_TX_IDX');
alter index HR.PER_ADDRESSES_SPT rebuild parameters ('sdo_indx_dims=2 sdo_rtr_pctfree=10 tablespace=APPS_TS_TX_IDX');
Section II: Export the source Release 12 database instance:
0) Create a directory named dmpdir:
$ sqlplus system/
SQL> create directory dmpdir as '/u01/backup/expimp';
1) Create the export parameter file:
Create file auexpdp.dat with the following values, change the log file name & date.
$cat auexpdp.dat
directory=dmpdir
dumpfile=fullexp%U.dmp,fullexp2_%U.dmp,fullexp3_%U.dmp,fullexp4_%U.dmp
filesize=2G
full=y
exclude=SCHEMA:"='MDDATA'"
exclude=SCHEMA:"='OLAPSYS'"
exclude=SCHEMA:"='ORDSYS'"
exclude=SCHEMA:"='DMSYS'"
exclude=SCHEMA:"='OUTLN'"
exclude=SCHEMA:"='ORDPLUGINS'"
#transform=oid:n
logfile=expdpapps_01Feb09.log
JOB_NAME=expfull
2) Shut down All the Services:
Shutdown the Apps ,database and listener and open the database in restricted mode.
SQL>shu immediate;
SQL>host lsnrctl stop
SQL>startup restrict;
3) Back up AZ table data (conditional)
a)If you are using AZ.H, upgrade to AZ.H.DELTA.1. See document 403092.1 b)10.2 datapump does not allow tables with XML type columns to be migrated Perform step 2 of document 402785.1
4) Grant privilege to source system schema:
a) As sysdba run the following sql.
SQL> grant EXEMPT ACCESS POLICY to system;
5) Export the Applications database instance (Full DB):
expdp system/ parfile=auexpdp.dat
Typically export runs for several hours, Please monitor the export log file for any errors.
8) Revoke privilege from source system schema:
SQL> revoke EXEMPT ACCESS POLICY from system;
Section III: Prepare a target Release 12 database instance
1) Create target Oracle 10g (10.2.0.2) Oracle home (conditional):
If the target instance oracle home doesn’t exist and it’s a new build please perform the following steps to install a new oracle home.
If already oracle home exist, please skip this step and proceed further. $ cd /stage/StageR12/startCD/Disk1/rapidwiz
$ rapidwiz –techstack
Choose the "10gR2 RDBMS" option in the techstack components window and provide the details for the new Oracle home like SID, Port , Oracle home ..etc.
2) Create a working directory and copy files from source:
$ mkdir /u01/backup/expimp
Copy all the files generated in the sorce working directory to target i.e /dd02/backup/expimp to /u01/backup/expimp
3) Create the target database instance:
Update the aucrdb.sql script generated on the source database server node with any necessary changes to the directory structures for the log file(s), data file(s), or tablespaces, reflecting the layout of the target database server node
Note: This is very imp step Before running script ensure all the files locations are proper
Run this from VNC:
$ sqlplus /nolog
SQL> connect / as sysdba;
SQL> spool aucrdb.log;
SQL> startup nomount;
SQL> @aucrdb.sql
SQL> exit;
4) Copy database preparation scripts to target Oracle home:
Copy the following files from the $APPL_TOP/admin directory of the source administration server node to the working directory in the target database server node: addb1020.sql, adsy1020.sql, adjv1020.sql, and admsc1020.sql
5) Set up the SYS schema:
Run this from VNC – It runs for a while:
$ sqlplus "/ as sysdba" @addb1020.sql
6) Set up the SYSTEM schema:
If System password is not known go ahead and reset the password.
SQL>alter user system identified by manager;
$ sqlplus system/ @adsy1020.sql
7) Install Java Virtual Machine
$ sqlplus system/ @adjv1020.sql
Attention: This script can be run only once in a given database instance, because the scripts that it calls are not re runnable
8) Install other required components:
sqlplus system/ @admsc1020.sql FALSE SYSAUX TEMP
9)Run adpostcrdb.sql script
Copy the adpostcrdb.sql script, generated in Section 1, from the source administration server node to the target database server node. On the target database server node, use SQL*Plus to connect to the database instance as SYSTEM and run the following command.
$ sqlplus system/ @adpostcrdb.sql
10) Disable automatic gathering of statistics:
Copy $APPL_TOP/admin/adstats.sql from the source administration server node to the target database server node.
$ sqlplus "/ as sysdba"
SQL> shutdown normal;
SQL> startup restrict;
SQL> @adstats.sql
SQL> exit;
11) Back up the Blank target database instance:
$ nohup cp –R /u01/oradata /u01/backup &
Section IV: Import the Release 12 database instance:
1) Pre Import checks:
A) Ensure directory dmpdir has been created as /u01/backup/expimp
B) Ensuare database running in No Archive log mode:
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/arch
Oldest online log sequence 130
Current log sequence 133
C) Resize the redo log files to 400M;
SQL> SELECT a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#;
select group#, status from v$log;
alter system switch logfile;
alter database drop logfile group 2;
alter database add logfile group 2 ('/u01/oradata/data03/log02a.dbf','/u01/oradata/data03/log02b.dbf') size 400m reuse;
alter database drop logfile group 3;
alter database add logfile group 3 ('/u01/oradata/data03/log03a.dbf','/u01/oradata/data03/log03b.dbf') size 400m reuse;
alter database drop logfile group 4;
alter database add logfile group 4 ('/u01/oradata/data03/log04a.dbf','/u01/oradata/data03/log04b.dbf') size 400m reuse;
alter database drop logfile group 1;
alter database add logfile group 1 ('/u01/oradata/data03/log01a.dbf','/u01/oradata/data03/log01b.dbf') size 400m reuse;
C) Create import dump directory:
$ sqlplus system/
SQL> create directory dmpdir as '/u01/backup/expimp';
2) Import the Applications database instance:
Create the par file auimpdp.dat with the following parameters,Modify the log file name as appropriate.
$ cat auimpdp.dat
directory=dmpdir
dumpfile=fullexp%U.dmp,fullexp2_%U.dmp,fullexp3_%U.dmp,fullexp4_%U.dmp
full=y
transform=oid:n
logfile=FULL_import_Sep_15.log
Execute the full import from VNC:
Set NLS_LANG parameter as source instance.
Eg: export NLS_LANG= American_America.UTF8
$ nohup impdp system/ parfile=auimpdp.dat &
Items to take care while import is running:
a) Note down the import start time.
b) Import runs for several hours,depending on the data volume and server performance.
c) Items to monitor while running import.
-Monitor continuously import log file for any errors
-Monitor the alert log file ,If tablespace runs out of space, It will be reported in alert log.
d) Import progress check queries.
SQL> select object_name,owner, TO_CHAR((MAX(LAST_DDL_TIME)),'DD-MON-YYYY HH24:MI:SS') LAST_DDL, TO_CHAR(sysdate,'DD-MON-YYYY HH24:MI:SS')sys_date from dba_objects where last_ddl_time=(select max(last_ddl_time) from dba_objects) group by object_name,owner;
e) Query to identify TableSpaces Filled More Than 80%
SQL> select a.TABLESPACE_NAME, a.MAX, b.USED, round((b.USED/a.MAX)*100,2) PCT from (select TABLESPACE_NAME, sum(MAXBYTES/1024/1024) MAX from dba_Data_files group by TABLESPACE_NAME) a,(select tablespace_name, sum(bytes/1024/1024) USED from dba_segments group by tablespace_name) b where a.TABLESPACE_NAME=b.TABLESPACE_NAME and b.USED > a.MAX *(85/100);f) Check the
errors:
egrep -n '^ERRORERROR:ORA-PLS-SP2-IMP-' FULL_import_Sep_15.log >imp_err.txt
3) Revoke privilege from target system schema:
SQL> revoke EXEMPT ACCESS POLICY from system;
Section V: Post Import Steps:
1) Reset Advanced Queues:
Run the auque2.sql script generated in the source database to enable the Advanced Queue settings that were lost during the export/import process
$ sqlplus /nolog
SQL> connect / as sysdba;
SQL> @auque2.sql
2) Run adgrants.sql:
Copy $APPL_TOP/admin/adgrants.sql from the administration server node to the working directory in the database server node and execute it.
$ sqlplus "/ as sysdba" @adgrants.sql APPLSYS
3) Grant create procedure privilege on CTXSYS:
Copy $AD_TOP/patch/115/sql/adctxprv.sql from the administration server node to the database server node and run it as apps.
sqlplus apps/ @adctxprv.sql CTXSYS
4) Gather statistics for SYS schema:
Copy $APPL_TOP/admin/adstats.sql from the administration server node to the working directory in the database server node and execute it.
$ sqlplus "/ as sysdba"
SQL> shutdown normal;
SQL> startup restrict;
SQL> @adstats.sql
SQL> shutdown normal;
SQL> startup;
SQL> exit;
5) Drop and recreate the database links:
Drop and recreate the DB links APPS_TO_APPS and EDW_APPS_TO_WH
6)Restore AZ table data (conditional)
If you performed the step to back up the AZ table data, Perform steps 4 and 5 of document 402785.1 on OracleMetaLink to restore the AZ table data.
7)Create OWA_MATCH package (conditional)
If you are using iAS 1.0.2, perform the steps in document 312165.1 to create SYS.OWA_MATCH on the target database.
8)Create ConText and Spatial objects
Certain ConText and Spatial objects are not preserved by the import process. Run the following.
$ perl $AU_TOP/patch/115/driver/dpost_imp.pl dpost_imp.drv
Once the driver file has been generated, use AutoPatch to apply it on the target administration server node.
9) Compile invalid objects
$ sqlplus "/ as sysdba" @$ORACLE_HOME/rdbms/admin/utlrp.sql
10) Maintain Applications database objects
Run AD Administration on the target administration server node. From the Maintain Applications Database Objects menu, perform the following tasks:
a. Compile flexfield data in AOL tables
b. Recreate grants and synonyms for APPS schema
11) Start Applications server processes
Start all the server processes on the target Applications system. You can allow users to access the system at this time.
12) Create DQM indexes
Create DQM indexes by following these steps:
a) Log on to Oracle Applications with the "Trading Community Manager" responsibility
b) Click Control > Request > Run
c) Select "Single Request" option
d) Enter "DQM Staging Program" name
e) Enter the following parameters:
Number of Parallel Staging Workers: 4
Staging Command: CREATE_INDEXES
Continue Previous Execution: NO
Index Creation: SERIAL
f) Click "Submit"
The following tasks provide clear step by step instructions on performing Oracle Apps database Migration to Linux from any operating system.
Section I: Prepare the source system for Export:-
1)Apply the Applications consolidated export/import utility patch:
Apply patch 4872830 to the source administration server node.
2) Apply latest Applications database preparation scripts patch;
Apply patch 7225862 to every application tier server node in the source system
3) Create a working directory:
$ mkdir /dd02/backup/expimp
4)Generate target database instance creation script aucrdb.sql:
$ sqlplus system/manager @$AU_TOP/patch/115/sql/auclondb.sql 10
5)Record Advanced Queue settings:
Copy the auque1.sql script from the $AU_TOP/patch/115/sql directory on the source administration server node to the working directory in the source database server node,
It generates auque2.sql.
$ sqlplus /nolog
SQL> connect / as sysdba;
SQL> @auque1.sql
6) Remove rebuild index parameter in spatial indexes:
Use SQL*Plus to connect to the source database as sysdba and run the following command:
SQL>select INDEX_NAME,PARAMETERS from dba_indexes where index_type='DOMAIN' and upper(parameters) like '%REBUILD%';
To remove the rebuild index parameter:
SQL> alter index
Eg:
alter index MST.MST_MD_ADM_BNDS_N1 rebuild parameters ('sdo_indx_dims=2 sdo_rtr_pctfree=10 tablespace=APPS_TS_TX_IDX');
alter index MST.MST_MD_HYDROS_N1 rebuild parameters ('sdo_indx_dims=2 sdo_rtr_pctfree=10 tablespace=APPS_TS_TX_IDX');
alter index HR.HR_LOCATIONS_SPT rebuild parameters ('sdo_indx_dims=2 sdo_rtr_pctfree=10 tablespace=APPS_TS_TX_IDX');
alter index HR.PER_ADDRESSES_SPT rebuild parameters ('sdo_indx_dims=2 sdo_rtr_pctfree=10 tablespace=APPS_TS_TX_IDX');
0) Create a directory named dmpdir:
$ sqlplus system/
SQL> create directory dmpdir as '/u01/backup/expimp';
1) Create the export parameter file:
Create file auexpdp.dat with the following values, change the log file name & date.
$cat auexpdp.dat
directory=dmpdir
dumpfile=fullexp%U.dmp,fullexp2_%U.dmp,fullexp3_%U.dmp,fullexp4_%U.dmp
filesize=2G
full=y
exclude=SCHEMA:"='MDDATA'"
exclude=SCHEMA:"='OLAPSYS'"
exclude=SCHEMA:"='ORDSYS'"
exclude=SCHEMA:"='DMSYS'"
exclude=SCHEMA:"='OUTLN'"
exclude=SCHEMA:"='ORDPLUGINS'"
#transform=oid:n
logfile=expdpapps_01Feb09.log
JOB_NAME=expfull
2) Shut down All the Services:
Shutdown the Apps ,database and listener and open the database in restricted mode.
SQL>shu immediate;
SQL>host lsnrctl stop
SQL>startup restrict;
3) Back up AZ table data (conditional)
a)If you are using AZ.H, upgrade to AZ.H.DELTA.1. See document 403092.1 b)10.2 datapump does not allow tables with XML type columns to be migrated Perform step 2 of document 402785.1
4) Grant privilege to source system schema:
a) As sysdba run the following sql.
SQL> grant EXEMPT ACCESS POLICY to system;
5) Export the Applications database instance (Full DB):
expdp system/
Typically export runs for several hours, Please monitor the export log file for any errors.
8) Revoke privilege from source system schema:
SQL> revoke EXEMPT ACCESS POLICY from system;
1) Create target Oracle 10g (10.2.0.2) Oracle home (conditional):
If the target instance oracle home doesn’t exist and it’s a new build please perform the following steps to install a new oracle home.
If already oracle home exist, please skip this step and proceed further. $ cd /stage/StageR12/startCD/Disk1/rapidwiz
$ rapidwiz –techstack
Choose the "10gR2 RDBMS" option in the techstack components window and provide the details for the new Oracle home like SID, Port , Oracle home ..etc.
2) Create a working directory and copy files from source:
$ mkdir /u01/backup/expimp
Copy all the files generated in the sorce working directory to target i.e /dd02/backup/expimp to /u01/backup/expimp
3) Create the target database instance:
Update the aucrdb.sql script generated on the source database server node with any necessary changes to the directory structures for the log file(s), data file(s), or tablespaces, reflecting the layout of the target database server node
Note: This is very imp step Before running script ensure all the files locations are proper
Run this from VNC:
$ sqlplus /nolog
SQL> connect / as sysdba;
SQL> spool aucrdb.log;
SQL> startup nomount;
SQL> @aucrdb.sql
SQL> exit;
4) Copy database preparation scripts to target Oracle home:
Copy the following files from the $APPL_TOP/admin directory of the source administration server node to the working directory in the target database server node: addb1020.sql, adsy1020.sql, adjv1020.sql, and admsc1020.sql
5) Set up the SYS schema:
Run this from VNC – It runs for a while:
$ sqlplus "/ as sysdba" @addb1020.sql
6) Set up the SYSTEM schema:
If System password is not known go ahead and reset the password.
SQL>alter user system identified by manager;
$ sqlplus system/
7) Install Java Virtual Machine
$ sqlplus system/
Attention: This script can be run only once in a given database instance, because the scripts that it calls are not re runnable
8) Install other required components:
sqlplus system/
9)Run adpostcrdb.sql script
Copy the adpostcrdb.sql script, generated in Section 1, from the source administration server node to the target database server node. On the target database server node, use SQL*Plus to connect to the database instance as SYSTEM and run the following command.
$ sqlplus system/
10) Disable automatic gathering of statistics:
Copy $APPL_TOP/admin/adstats.sql from the source administration server node to the target database server node.
$ sqlplus "/ as sysdba"
SQL> shutdown normal;
SQL> startup restrict;
SQL> @adstats.sql
SQL> exit;
11) Back up the Blank target database instance:
$ nohup cp –R /u01/oradata /u01/backup &
Section IV: Import the Release 12 database instance:
1) Pre Import checks:
A) Ensure directory dmpdir has been created as /u01/backup/expimp
B) Ensuare database running in No Archive log mode:
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/arch
Oldest online log sequence 130
Current log sequence 133
C) Resize the redo log files to 400M;
SQL> SELECT a.group#, a.member, b.bytes FROM v$logfile a, v$log b WHERE a.group# = b.group#;
select group#, status from v$log;
alter system switch logfile;
alter database drop logfile group 2;
alter database add logfile group 2 ('/u01/oradata/data03/log02a.dbf','/u01/oradata/data03/log02b.dbf') size 400m reuse;
alter database drop logfile group 3;
alter database add logfile group 3 ('/u01/oradata/data03/log03a.dbf','/u01/oradata/data03/log03b.dbf') size 400m reuse;
alter database drop logfile group 4;
alter database add logfile group 4 ('/u01/oradata/data03/log04a.dbf','/u01/oradata/data03/log04b.dbf') size 400m reuse;
alter database drop logfile group 1;
alter database add logfile group 1 ('/u01/oradata/data03/log01a.dbf','/u01/oradata/data03/log01b.dbf') size 400m reuse;
C) Create import dump directory:
$ sqlplus system/
SQL> create directory dmpdir as '/u01/backup/expimp';
2) Import the Applications database instance:
Create the par file auimpdp.dat with the following parameters,Modify the log file name as appropriate.
$ cat auimpdp.dat
directory=dmpdir
dumpfile=fullexp%U.dmp,fullexp2_%U.dmp,fullexp3_%U.dmp,fullexp4_%U.dmp
full=y
transform=oid:n
logfile=FULL_import_Sep_15.log
Execute the full import from VNC:
Set NLS_LANG parameter as source instance.
Eg: export NLS_LANG= American_America.UTF8
$ nohup impdp system/
Items to take care while import is running:
a) Note down the import start time.
b) Import runs for several hours,depending on the data volume and server performance.
c) Items to monitor while running import.
-Monitor continuously import log file for any errors
-Monitor the alert log file ,If tablespace runs out of space, It will be reported in alert log.
d) Import progress check queries.
SQL> select object_name,owner, TO_CHAR((MAX(LAST_DDL_TIME)),'DD-MON-YYYY HH24:MI:SS') LAST_DDL, TO_CHAR(sysdate,'DD-MON-YYYY HH24:MI:SS')sys_date from dba_objects where last_ddl_time=(select max(last_ddl_time) from dba_objects) group by object_name,owner;
e) Query to identify TableSpaces Filled More Than 80%
SQL> select a.TABLESPACE_NAME, a.MAX, b.USED, round((b.USED/a.MAX)*100,2) PCT from (select TABLESPACE_NAME, sum(MAXBYTES/1024/1024) MAX from dba_Data_files group by TABLESPACE_NAME) a,(select tablespace_name, sum(bytes/1024/1024) USED from dba_segments group by tablespace_name) b where a.TABLESPACE_NAME=b.TABLESPACE_NAME and b.USED > a.MAX *(85/100);f) Check the
errors:
egrep -n '^ERRORERROR:ORA-PLS-SP2-IMP-' FULL_import_Sep_15.log >imp_err.txt
3) Revoke privilege from target system schema:
SQL> revoke EXEMPT ACCESS POLICY from system;
Section V: Post Import Steps:
1) Reset Advanced Queues:
Run the auque2.sql script generated in the source database to enable the Advanced Queue settings that were lost during the export/import process
$ sqlplus /nolog
SQL> connect / as sysdba;
SQL> @auque2.sql
2) Run adgrants.sql:
Copy $APPL_TOP/admin/adgrants.sql from the administration server node to the working directory in the database server node and execute it.
$ sqlplus "/ as sysdba" @adgrants.sql APPLSYS
3) Grant create procedure privilege on CTXSYS:
Copy $AD_TOP/patch/115/sql/adctxprv.sql from the administration server node to the database server node and run it as apps.
sqlplus apps/
4) Gather statistics for SYS schema:
Copy $APPL_TOP/admin/adstats.sql from the administration server node to the working directory in the database server node and execute it.
$ sqlplus "/ as sysdba"
SQL> shutdown normal;
SQL> startup restrict;
SQL> @adstats.sql
SQL> shutdown normal;
SQL> startup;
SQL> exit;
5) Drop and recreate the database links:
Drop and recreate the DB links APPS_TO_APPS and EDW_APPS_TO_WH
6)Restore AZ table data (conditional)
If you performed the step to back up the AZ table data, Perform steps 4 and 5 of document 402785.1 on OracleMetaLink to restore the AZ table data.
7)Create OWA_MATCH package (conditional)
If you are using iAS 1.0.2, perform the steps in document 312165.1 to create SYS.OWA_MATCH on the target database.
8)Create ConText and Spatial objects
Certain ConText and Spatial objects are not preserved by the import process. Run the following.
$ perl $AU_TOP/patch/115/driver/dpost_imp.pl dpost_imp.drv
Once the driver file has been generated, use AutoPatch to apply it on the target administration server node.
9) Compile invalid objects
$ sqlplus "/ as sysdba" @$ORACLE_HOME/rdbms/admin/utlrp.sql
10) Maintain Applications database objects
Run AD Administration on the target administration server node. From the Maintain Applications Database Objects menu, perform the following tasks:
a. Compile flexfield data in AOL tables
b. Recreate grants and synonyms for APPS schema
11) Start Applications server processes
Start all the server processes on the target Applications system. You can allow users to access the system at this time.
12) Create DQM indexes
Create DQM indexes by following these steps:
a) Log on to Oracle Applications with the "Trading Community Manager" responsibility
b) Click Control > Request > Run
c) Select "Single Request" option
d) Enter "DQM Staging Program" name
e) Enter the following parameters:
Number of Parallel Staging Workers: 4
Staging Command: CREATE_INDEXES
Continue Previous Execution: NO
Index Creation: SERIAL
f) Click "Submit"
Database Links
Database Links:
---------------
A Database Link allows you to access data in a remote database as if it is a local table's data.
The connection can be made to a database hosted on the same machine or remotely on another
server.When you query data on a different database, you are essentially using a distributed query.It is also possible to execute distributed transactions through database links like insert,
update and delete.The authentication used in a database link is either the link's owner or a hard-coded usernameand password, depending on the way the link was created.
A database link in oracle can be either private (for use only by the link's owner) or public(accessible to any user with the correct privileges).Connectivity is established via Sql*Net, using an alias defined in the local database server's tnsnames.ora.
Example:
----------
export TNS_ADMIN=$ORACLE_HOME/network/admin
cd $TNS_ADMIN
vi tnsnames.ora
-- add the entries of target database that needs to be accessed
eg;
TEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = hyderabad.oracle.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = TEST)
)
)
tnsping TEST -- it should work - if not please listener on the target server.
sqlplus '/as sysdba'
SQL>CREATE DATABASE LINK LINK_TEST CONNECT TO SCOTT IDENTIFIED BY TIGER USING 'TEST';
SQL>SELECT * FROM DBA_DB_LINKS;
SQL>SELECT * FROM ALL_DB_LINKS;
SQL>select * from BONUS@LINK_TEST; - It shud give the results of target DB
SQL>DROP DATABASE LINK LINK_TEST; -- To Drop Database Link
TO Create Pubic Database link
PUBLIC database link is a DB link which can acceese by All the users in the Database.
Syntax to create a PUBLIC database:
SQL>CREATE PUBLIC DATABASE LINK LINK_TEST CONNECT TO SCOTT IDENTIFIED BY TIGER USING 'TEST';
Syntax to drop a PUBLIC database:
SQL>Drop PUBLIC DATABASE LINK LINK_TEST;
Transportable Tablespaces
Transportable TS on the same Platform
--------------------------------------
Source
------
SQL>select tablespace_name,status from dba_tablespaces;
SQL>alter tablespace USERDATA read only;
SQL> exec dbms_tts.transport_set_check('USERDATA');
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
no rows selected
$exp file=userdata.dmp transport_tablespace=Y tablespaces=USERDATA
/ as sysdba
$cp /prod/lab/oradata/userdata01.dbf /prod/rk/oradata
$cp /prod/lab/oradata/userdata02.dbf /prod/rk/oradata
SQL> alter tablespace userdata online;
SQL> select username,default_tablespace from dba_users;
USER2 USERDATA
USER1 USERDATA
Target
-------
grant connect,resource to user2 identified by user2;
grant connect,resource to user1 identified by user1;
$imp file=userdata.dmp transport_tablespace=Y tablespaces=USERDATA datafiles=('/prod/rk/oradata/userdata01.dbf','/prod/rk/oradata/userdata02.dbf')
/ as sysdba
SQL>select tablespace_name from dba_tablespaces;
SQL>alter tablespace USERDATA read write;
SQL> alter user user1 default tablespace USERDATA;
SQL> alter user user2 default tablespace USERDATA;
SQL>conn user1/user1
SQL>select * from tab;
Transportal Tablespace across the Platforms;
--------------------------------------------
From Hp Tru64 - Linux
source
------
SQL>select * from v$transportable_platform
-- Endian should match --
SQL> select tablespace_name,status from dba_tablespaces;
SQL> alter tablespace USERDATA read only;
SQL> create directory tts as '/prod/lab/tts';
$mkdir -p /prod/lab/tts
select * from dba_directories;
SQL> exec dbms_tts.transport_set_check('USERDATA');
PL/SQL procedure successfully completed.
SQL> select * from transport_set_violations;
no rows selected
$ expdp directory=tts dumpfile=userdata.dmp transport_tablespaces=USERDATA
/ as sysdba
cd /prod/lab/tts/
ls
cp /prod/lab/oradata/userdata01.dbf /prod/lab/tts
cp /prod/lab/oradata/userdata02.dbf /prod/lab/tts
SQL>alter tablespace userdata read write;
SQL> select username,default_tablespace from dba_users;
Target
------
$cp /prod/lab/tts /prod/rk/tts
SQL>create directory tts as '/prod/rk/tts';
SQL>grant connect,resource to user2 identified by user2;
SQL>grant connect,resource to user1 identified by user1;
$impdp directory=tts dumpfile=userdata.dmp transport_datafiles=('/prod/rk/tts/userdata01.dbf','/prod/rk/tts/userdata02.dbf')
/as sysdba
SQL>select tablespace_name from dba_tablespaces;
SQL>alter tablespace USERDATA read write;
SQL> alter user user1 default tablespace USERDATA;
SQL> alter user user2 default tablespace USERDATA;
SQL>conn user1/user1
SQL>select * from tab;
Oracle Database Recovery Scenarios
The below outlines few of the Oracle Database recovery scenarios in different cases.
Scenario 1 - Loss of C/R/D files
-------------------------------------
sqlplus '/as sysdba'
SQL>startup;
SQL>archive log list; - DB must be running in archive log mode
SQL>conn user1/user1
SQL>select * from emp;
SQL>insert into emp select * from emp;
/
/
/
/
SQL> commit;
SQL>alter system switch logfile;
SQL>exit;
$cd /prod/rk/oradata
$rm -f *
$sqlplus '/as sysdba'
SQL>select name from v$database;
-- You will see Error Here
SQL>shut abort;
SQL>exit;
$cd /prod/rk/backup/cold
$cp * /prod/rk/oradata
$sqlplus '/as sysdba'
SQL>startup mount;
SQL>alter database recover automatic using backup controlfile until cancel;
--
--
--
--
SQL>recover cancel;
SQL>alter database open resetlogs; -- The arch log seq number will be set to 1.
SQL>select name from v$Database;
Scenario 2 - Loss of Non-SYSTEM datafile
----------------------------------------
sqlplus '/as sysdba'
SQL>startup;
SQL>conn user1/user1
SQL>insert into emp as select * from emp;
/
/
/
SQL>commit;
SQL>select USERNAME,DEFAULT_TABLESPACE from dba_users;
SQL> select file_name from dba_data_files where tablespace_name='TS1';
SQL>exit;
$cd /u03/praveen/oradata
$ rm ts1a.dbf
SQL>sqlplus user1/user1
SQL>select * from emp;
-- You will see Error Here
SQL>alter database datafile '/u03/praveen/oradata/ts1a.dbf' offline;
SQL>exit;
$cp /u03/praveen/backup/cold/ts1a.dbf /u03/praveen/oradata
sqlplus '/as sysdba'
SQL>recover datafile '/u03/praveen/oradata/ts1a.dbf';
SQL>alter database datafile '/u03/praveen/oradata/ts1a.dbf' online;
SQL>conn user1/user1
SQL>select * from emp;
Scenario 3 - Loss of SYSTEM datafile
------------------------------------
sqlplus '/as sysdba'
SQL>startup;
SQL>conn user1/user1
SQL>insert into emp as select * from emp;
/
/
/
SQL>commit;
$cd /u03/praveen/oradata
$ rm system01.dbf
SQL>sqlplus user1/user1
SQL>select * from emp;
-- You will see Error Here
SQL>shu abort;
$cp /u03/praveen/backup/cold/system01.dbf /u03/praveen/oradata
sqlplus '/as sysdba'
SQL>startup mount;
SQL>recover database;
SQL>alter database open;
SQL>select name from v$database;
SQL>select * from emp;
Scenario 4 - Point in Time Recovery
-------------------------------------
SQL>conn user1/user1
SQL>select count(*) from emp;
SQL>select count(*) from salgrade;
SQL>insert into emp select * from emp;
SQL>/
SQL>commit;
SQL>set time on
SQL>select count(*) from emp; - Note down the records
SQL>drop table emp purge;
SQL>insert into salgrade select * from salgrade;
SQL>/
SQL>/
SQL>/
SQL>commit;
SQL>conn /as sysdba
SQL>alter system switch logfile;
SQL>/
SQL>shu immediate;
$cd /prod/lab/oradata
$rm -rf *
$cp /prod/lab/backup/cold/* /prod/lab/oradata
$sqlplus '/as sysdba'
SQL>startup mount;
SQL>recover database using backup controlfile until time '2010/09/16/16:18:49';
-- AUTO --
Scenario 5 - Loss of Log files
-------------------------------------
conn user1/user1
SQL>insert into emp select * from emp;
/
/
SQL>commit;
SQL>exit;
$cd /prod/lab/ordaata/
$rm redo1a.log redo2a.log
sqlplus user1/user1
SQL>insert into emp select * from emp;
SQL>/
/
/
/
-- DB will Hang Here -- Open alert log and check
From other session
sqlplus '/as sysdba'
SQL>shu abort;
$cd /prod/lab/backup/cold
cp *.ctl *.dbf /prod/lab/oradata
sqlplus '/as sysdba'
SQL>startup mount;
SQL>recover database using backup controlfile until cancel;
SQL>alter database open resetlogs;
Scenario 6: Loss of Undo Datafile
-------------------------------------
sqlplus user1/user1
SQL>insert into emp select * from emp;
/
/
/
SQL>commit;
SQL>exit;
$cd /prod/lab/oradata
rm labundo1.dbf
sqlplus user1/user1
SQL>insert into emp select * from emp;
/
/
-- Error -- Undo TBS missing
SQL>conn /as sysdba
SQL>shu abort
SQL>exit;
$cd /prod/lab/backup/cold
cp labundo1.dbf /prod/lab/oradata
sqlplus '/as sysdba'
SQL>startup mount;
SQL>select * from v$recover_file;
SQL>recover datafile '/prod/lab/oradata/labundo1.dbf'
SQL>alter database open;
SQL>select * from v$recover_file;
Scenario 7: Loss of 1 control File when Control Files Mutiplexed
--------------------------------------------------------------------------
sqlplus '/as sysdba'
SQL>show parameter control
-- Ensure u have 2 control files --
SQL>conn user1/user1
SQL>insert into emp select * from emp;
/
/
/
SQL>commit;
SQL>exit;
$cd /prod/lab/oradata
$rm cntrl01.ctl
sqlplus user1/user1
SQL>alter database datafile 4 offline;
-- Error Control File Missing --
SQL>shu abort
SQL>exit;
$cd /prod/lab/oradata
$cp cntrl02.ctl cntrl01.ctl
sqlplus '/as sysdba'
SQL>startup;
SQL>select * from user1.emp;
Scenario 8: Loss of all control Files
-------------------------------------
SQL>conn user1/user1
SQL>insert into emp select * from emp;
/
/
/
SQL>commit;
SQL>exit;
$cd /prod/lab/oradata
$rm cntrl01.ctl
sqlplus '/as sysdba'
SQL>alter database datafile 4 offline;
-- Error Control File Missing --
SQL>shu abort
SQL>exit;
$cd /prod/lab/oradata
$cp /prod/lab/backup/cold/cntrl01.ctl .
sqlplus '/as sysdba'
SQL>startup mount;
SQL>recover database using backup controlfile until cancel;
SQL>alter database open resetlogs;
SQL>select * from v$recover_file;
Scenario 9: Loss of control Files with no Backup
--------------------------------------------------------------------------
SQL>conn user1/user1
SQL>insert into emp select * from emp;
/
/
/
SQL>commit;
SQL>exit;
Sqlplus '/as sysdba'
SQL>alter database backup controlfile to trace as '/prod/lab/crt_ctl.sql';
SQL>exit;
$cd /prod/lab/oradata
$rm cntrl01.ctl
sqlplus '/as sysdba'
SQL>alter database datafile 4 offline;
-- Error Control File Missing --
SQL>shu abort
SQL>exit;
$ vi /prod/lab/crt_ctl.sql
-- Modify the control - Use first section
:wq!
sqlplus '/as sysdba'
SQL>@/prod/lab/crt_ctl.sql
SQL>alter database open;
-- If u face any error ---
-- SQL>recover database;
-- SQL>alter database open;
SQL>select count(*) from user1.emp;
SQL>alter database open resetlogs;
SQL>select * from v$recover_file;
Scenario 10: Loss of unbackup datafile
-------------------------------------
sqlplus user1/user1
SQL>insert into emp select * from emp;
/
/
/
SQL>commit;
SQL>conn /as sysdba
SQL>alter tablespace userdata add datafile '/prod/lab/oradata/userdata02.dbf' size 500M;
SQL>conn user1/user1
SQL>insert into emp select * from emp;
/
/
SQL>commit;
SQL>exit;
$cd /prod/lab/oradata
$rm userdata02.dbf
sqlplus user1/user1
SQL>insert into emp select * from emp;
/
/
/
-- Error - Datafile Missing --
SQL>conn /as sysdba
SQL>shu abort;
sqlplus '/as sysdba'
SQL>startup mount;
SQL>alter database create datafile '/prod/lab/oradata/userdata02.dbf';
SQL>recover datafile '/prod/lab/oradata/userdata02.dbf';
SQL>alter database open;
Scenario 11: How to recover from Hot Backup
------------------------------------------------------
sqlplus '/as sysdba'
SQL>select * from v$backup;
conn user1/user1
SQL>insert into salgrade select * from salgrade;
SQL>commit;
SQL>conn /as sysdba
SQL>alter tablespace userdata begin backup; - Taking hot backup of TS USERDATA
SQL>conn user1/user1
SQL>insert into salgrade select * from salgrade;
SQL>commit;
SQL>select count(*) from salgrade;
SQL>exit;
$cd /prod/lab/backup
$mkdir hot
$cd /prod/lab/oradata
$cp userdata01.dbf userdata02.dbf /prod/lab/backup/hot
$sqlplus '/as sysdba'
SQL>select * from v$backup;
SQL>alter tablespace userdata end backup;
SQL>alter system switch logfile;
/
/
SQL>conn user1/user1
SQL>insert into salgrade select * from salgrade;
/
/
SQL>commit;
SQL>select count(*) from salgrade;
SQL>exit;
$cd /prod/lab/oradata
$rm userdata01.dbf
sqlplus user1/user1
SQL>insert into salgrade select * from salgrade;
/
/
/
-- Error userdata datafile missing --
SQL>conn /as sysdba
SQL>alter database datafile '/prod/lab/oradata/userdata01.dbf' offline;
SQL>exit;
$cp ../backup/hot/userdata01.dbf .
sqlplus '/as sysdba'
SQL>recover datafile '/prod/lab/oradata/userdata01.dbf';
- AUTO -
SQL>alter database datafile '/prod/lab/oradata/userdata01.dbf' online;
SQL>select * from v$recover_file;
SQL>select count(*) from user1.salgrade;
SQL>exit;
Sunday, December 19, 2010
What is FNDLOAD
What is FNDLOAD
==============
The Generic Loader is a concurrent program named FNDLOAD. The concurrent executable takes the following parameters:
FNDLOAD apps/pwd 0 Y mode configfile datafile entity [ param ... ]
where
The APPS schema and password in the form
username/password[@connect_string]. If connect_string is omitted, it is taken in a
platform–specific manner from the environment using the name TWO_TASK.
<> Concurrent program flags
mode UPLOAD or DOWNLOAD. UPLOAD causes the datafile to be uploaded to the
database. DOWNLOAD causes the loader to fetch rows and write them to the datafile.
The configuration file to use (usually with a suffix of .lct, but not enforced or
supplied by the loader).
The data file to write (usually with a suffix of .ldt, but not enforced or supplied by
the loader). If the data file already exists, it will be overwritten.
The entity(ies) to upload or download. When uploading, you should always upload
all entities, so specify a ”–” to upload all entities.
< [param] > Zero or more additional parameters are used to provide bind values in the access
SQL (both UPLOAD and DOWNLOAD). Each parameter is in the form
NAME=VALUE. NAME should not conflict with an attribute name for the
entities being loaded.
Syntax
=======
An example of downloading is:
----------------------------
FNDLOAD apps/apps@TEST 0 Y DOWNLOAD testcfg.lct out.ldt FND_APPLICATION_TL APPSNAME=FND
This command does the following:
connects to apps/apps@devd
downloads data using the configuration file testcfg.lct
writes data to data file out.ldt
downloads the FND_APPLICATION_TL entity with APPSNAME parameter defined as value FND’
An example of uploading is:
--------------------------
FNDLOAD apps/apps@custdb 0 Y UPLOAD fndapp.lct fnd_exmpl.ldt
This command does the following:
connects to apps/apps@custdb
uploads data using the configuration file in fndapp.lct from datafile in fnd_exmpl
The contents of the entire data file is uploaded.
FNDLOAD Load Options
---------------------
Application level OR single entity level download
Entire OR Partial upload of a data file
Custom mode force update
Support for NLS uploads
AOL ENTITIES
-------------
FNDLOAD an be used for the below Application entities.
Concurrent program definitions
Request groups
Request Sets
Lookup types and lookup values
Profile options and profile option values
Flexfields setup data
Flexfield Value Sets
Descriptive Flexfields
Key Flexfields
Flexfield Value Security Rules
Flexfields Value Hierarchies (Rollup Groups)
Flexfields Values
Attachments definitions
Messages
Menus
Responsibilities
Security information
Etc..
Examples:
========
A)Concurrent Program Definitions:
---------------------------------
The concurrent program configuration file afcpprog.lct downloads and uploads concurrent program definitions. It takes as parameters program name and application name. The following table lists the entities, sub-entities (if any), and download parameters for this configuration file.
Download
-----------
FNDLOAD apps/apps@db O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME="prod" CONCURRENT_PROGRAM_NAME="concurrent name"
Upload
-----------
FNDLOAD apps/apps@db O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt
B)Request Groups Configuration File:
------------------------------------
Use the file afcpreqg.lct for loading request group data. The following table lists the entities, sub–entities (if any), and download parameters for this configuration file.
Download
---------
FNDLOAD apps/apps@db O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME="request group" APPLICATION_SHORT_NAME="prod"
Upload
------
FNDLOAD apps/apps@db O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt
C)Request Sets Configuration File
----------------------------------
Use the file afcprset.lct for loading request group data. The following table lists the entities, sub–entities (if any), and download parameters for this configuration file.
Download
---------
FNDLOAD apps/apps@db O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET APPLICATION_SHORT_NAME="prod" REQUEST_SET_NAME="request set"
Upload
---------
FNDLOAD apps/apps@db O Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt
D)Lookups Configuration File
----------------------------
Use the file aflvmlu.lct for loading Lookup types and Lookups values. The following table lists the entities, sub–entities (if any), and download parameters for this configuration file.
Download
---------
FNDLOAD apps/apps@db O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="prod" LOOKUP_TYPE="lookup name"
Upload
-----
FNDLOAD apps/apps@db O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt
E)Profile Options and Profile Values Configuration File:
-------------------------------------------------------
Use the file afscprof.lct for loading profile options and profile values. The following table lists the entities, sub–entities (if any), and download parameters for this configuration file.
Download
---------
FNDLOAD apps/apps@db O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME="profile option" APPLICATION_SHORT_NAME="prod"
Upload
---------
FNDLOAD apps/apps@db O Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt
F)Flexfield Value Sets:
----------------------
The entity VALUE_SET includes the following table details of table validated value sets, and user exit details of special/pair validated value sets. It does not include the values, security rules, rollup groups, or value hierarchies for the value set. These entities should be
downloaded and uploaded separately.
The key for this entity is FLEX_VALUE_SET_NAME.
Download
---------
FNDLOAD apps/apps@db O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME="value set name"
Upload
---------
FNDLOAD apps/apps@db O Y UPLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt
G)Responsibilities Configuration File:
-------------------------------------
Use the file afscursp.lct for downloading and uploading forms, functions, menus, and menu entries. The following table lists the entities, sub–entities (if any), and download parameters for this configuration file.
Download
---------
FNDLOAD apps/apps@db O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY="responsibility"
Upload
---------
FNDLOAD apps/apps@db O Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt
Upload Scripts: ----------------
==============
The Generic Loader is a concurrent program named FNDLOAD. The concurrent executable takes the following parameters:
FNDLOAD apps/pwd 0 Y mode configfile datafile entity [ param ... ]
where
username/password[@connect_string]. If connect_string is omitted, it is taken in a
platform–specific manner from the environment using the name TWO_TASK.
<> Concurrent program flags
mode UPLOAD or DOWNLOAD. UPLOAD causes the datafile to be uploaded to the
database. DOWNLOAD causes the loader to fetch rows and write them to the datafile.
supplied by the loader).
the loader). If the data file already exists, it will be overwritten.
all entities, so specify a ”–” to upload all entities.
< [param] > Zero or more additional parameters are used to provide bind values in the access
SQL (both UPLOAD and DOWNLOAD). Each parameter is in the form
NAME=VALUE. NAME should not conflict with an attribute name for the
entities being loaded.
Syntax
=======
An example of downloading is:
----------------------------
FNDLOAD apps/apps@TEST 0 Y DOWNLOAD testcfg.lct out.ldt FND_APPLICATION_TL APPSNAME=FND
This command does the following:
connects to apps/apps@devd
downloads data using the configuration file testcfg.lct
writes data to data file out.ldt
downloads the FND_APPLICATION_TL entity with APPSNAME parameter defined as value FND’
An example of uploading is:
--------------------------
FNDLOAD apps/apps@custdb 0 Y UPLOAD fndapp.lct fnd_exmpl.ldt
This command does the following:
connects to apps/apps@custdb
uploads data using the configuration file in fndapp.lct from datafile in fnd_exmpl
The contents of the entire data file is uploaded.
FNDLOAD Load Options
---------------------
Application level OR single entity level download
Entire OR Partial upload of a data file
Custom mode force update
Support for NLS uploads
AOL ENTITIES
-------------
FNDLOAD an be used for the below Application entities.
Concurrent program definitions
Request groups
Request Sets
Lookup types and lookup values
Profile options and profile option values
Flexfields setup data
Flexfield Value Sets
Descriptive Flexfields
Key Flexfields
Flexfield Value Security Rules
Flexfields Value Hierarchies (Rollup Groups)
Flexfields Values
Attachments definitions
Messages
Menus
Responsibilities
Security information
Etc..
Examples:
========
A)Concurrent Program Definitions:
---------------------------------
The concurrent program configuration file afcpprog.lct downloads and uploads concurrent program definitions. It takes as parameters program name and application name. The following table lists the entities, sub-entities (if any), and download parameters for this configuration file.
Download
-----------
FNDLOAD apps/apps@db O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME="prod" CONCURRENT_PROGRAM_NAME="concurrent name"
Upload
-----------
FNDLOAD apps/apps@db O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt
B)Request Groups Configuration File:
------------------------------------
Use the file afcpreqg.lct for loading request group data. The following table lists the entities, sub–entities (if any), and download parameters for this configuration file.
Download
---------
FNDLOAD apps/apps@db O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME="request group" APPLICATION_SHORT_NAME="prod"
Upload
------
FNDLOAD apps/apps@db O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt
C)Request Sets Configuration File
----------------------------------
Use the file afcprset.lct for loading request group data. The following table lists the entities, sub–entities (if any), and download parameters for this configuration file.
Download
---------
FNDLOAD apps/apps@db O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET APPLICATION_SHORT_NAME="prod" REQUEST_SET_NAME="request set"
Upload
---------
FNDLOAD apps/apps@db O Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt
D)Lookups Configuration File
----------------------------
Use the file aflvmlu.lct for loading Lookup types and Lookups values. The following table lists the entities, sub–entities (if any), and download parameters for this configuration file.
Download
---------
FNDLOAD apps/apps@db O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="prod" LOOKUP_TYPE="lookup name"
Upload
-----
FNDLOAD apps/apps@db O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt
E)Profile Options and Profile Values Configuration File:
-------------------------------------------------------
Use the file afscprof.lct for loading profile options and profile values. The following table lists the entities, sub–entities (if any), and download parameters for this configuration file.
Download
---------
FNDLOAD apps/apps@db O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME="profile option" APPLICATION_SHORT_NAME="prod"
Upload
---------
FNDLOAD apps/apps@db O Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt
F)Flexfield Value Sets:
----------------------
The entity VALUE_SET includes the following table details of table validated value sets, and user exit details of special/pair validated value sets. It does not include the values, security rules, rollup groups, or value hierarchies for the value set. These entities should be
downloaded and uploaded separately.
The key for this entity is FLEX_VALUE_SET_NAME.
Download
---------
FNDLOAD apps/apps@db O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME="value set name"
Upload
---------
FNDLOAD apps/apps@db O Y UPLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt
G)Responsibilities Configuration File:
-------------------------------------
Use the file afscursp.lct for downloading and uploading forms, functions, menus, and menu entries. The following table lists the entities, sub–entities (if any), and download parameters for this configuration file.
Download
---------
FNDLOAD apps/apps@db O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY="responsibility"
Upload
---------
FNDLOAD apps/apps@db O Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt
| Program Name | Scripts For Downloading |
| Concurrent Program | FNDLOAD apps/apps@db O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME="prod" CONCURRENT_PROGRAM_NAME="concurrent name" |
| Request Groups | FNDLOAD apps/apps@db O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME="request group" APPLICATION_SHORT_NAME="prod" |
| Request Sets | FNDLOAD apps/apps@db O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET APPLICATION_SHORT_NAME="prod" REQUEST_SET_NAME="request set" |
| Lookups | FNDLOAD apps/apps@db O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="prod" LOOKUP_TYPE="lookup name" |
| Profile Options | FNDLOAD apps/apps@db O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME="profile option" APPLICATION_SHORT_NAME="prod" |
| Value Sets | FNDLOAD apps/apps@db O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME="value set name" |
| Descriptive Flexfield | FNDLOAD apps/apps@db O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX P_LEVEL=?COL_ALL:REF_ALL:CTX_ONE:SEG_ALL? APPLICATION_SHORT_NAME="prod" DESCRIPTIVE_FLEXFIELD_NAME="desc flex name" P_CONTEXT_CODE="context name" |
| Key Flexfield | FNDLOAD apps/apps@db O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEX P_LEVEL=?COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL? APPLICATION_SHORT_NAME="prod" ID_FLEX_CODE="key flex code" P_STRUCTURE_CODE="structure name" |
| Value Sets with values | FNDLOAD apps/apps@db O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME="value set name" |
| Messages | FNDLOAD apps/apps@db 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct file_name.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME='prod' MESSAGE_NAME='message name' |
| Menus | FNDLOAD apps/apps@db O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME="menu_name" |
| Responsibilities | FNDLOAD apps/apps@db O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY="responsibility" |
| Printer Styles | FNDLOAD apps/apps@db O Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME="printer style name" |
| Spread Table | FNDLOAD apps/apps@db 0 Y DOWNLOAD $FND_TOP/patch/115/import/jtfgrid.lct IEX_CASE.ldt JTF_GRID_DATASOURCES GRID_DATASOURCE_NAME="IEX_CASE" |
| Program name | Scripts For Uploading |
| Concurrent Program | FNDLOAD apps/apps@db O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt |
| Request Groups | FNDLOAD apps/apps@db O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt |
| Request Sets | FNDLOAD apps/apps@db O Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt |
| Lookups | FNDLOAD apps/apps@db O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt |
| Profile Options | FNDLOAD apps/apps@db O Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt |
| Value Sets | FNDLOAD apps/apps@db O Y UPLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt |
| Descriptive Flexfield | FNDLOAD apps/apps@db O Y UPLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt |
| Key Flexfield | FNDLOAD apps/apps@db O Y UPLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt |
| Value Sets with values | FNDLOAD apps/apps@db O Y UPLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt |
| Messages | FNDLOAD apps/apps@db 0 Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct file_name.ldt |
| Menus | FNDLOAD apps/apps@db O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt |
| Responsibilities | FNDLOAD apps/apps@db O Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt |
| Printer Styles | FNDLOAD apps/apps@db O Y UPLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt |
| Spread Table | FNDLOAD apps/apps@db 0 Y UPLOAD $FND_TOP/patch/115/import/jtfgrid.lct IEX_CASE.ldt |
Subscribe to:
Posts (Atom)