Install the printer in the OS
Navigation and forms that are used-(Login as system administrator)
The following steps to setup printer(s) should be performed in the following
order:
1. Printer types > Navigate - Install - Printer – Types
This window you must define a printer type for your new printer. You may provide any name you choose.
An example would be if you had a line printer, you may name it "LINE" or "LN03" for the model number of the printer. This name will be associated to the actual printer name when you register the printer to Oracle Applications.
2. Printer > Navigate -Install - Printer – Register
You must have defined a printer type before you can register a new printer. The value for printer name will be the operating system printer name. Then choose the printer type that you defined in the previous step.
3. Printer types > Navigate - Install - Printer – Types
You will need to come to this form again to associate the below printer styles to the printer type that you defined above.
4. Print Styles > Navigate - Install - Printer – Style
If you are defining new styles specifically for your printer, you would do this here. Please review manual for the specific parameters needed to be defined. Oracle Applications reports are designed to work with standard shipped styles:
Portrait
Landscape
Landwide
A4
Dynamic Portrait
You may point to the seeded styles that we have, but if you have customized reports you may have to create a new printer style to accommodate the custom report(s).
5. Printer Driver > Navigate - Install - Printer – Driver
Oracle does provide the printer drivers for the above Styles, so unless you are adding your own, it should not be required to define one right now. If you are defining your own, you must specify a unique printer driver name and an unique user name for a given platform.
6. Lastly, if any new updates or changes have been made to any of the printer definitions, you must bounce the concurrent manager to ensure that all changes do take effect.
Change the profile option GSM enabled to N (from Y)
To change this the following steps need to be done:
- Switch responsibility from sys admin to application developer
- Application Profile- (F11)- %GSM%- (ctrl F11)
- Validate both visible and updateable under user access
- Save the changes
- Again switch responsibility from application manager to sys admin
- Profile - system- %GSM%-N
Wednesday, July 7, 2010
New Features of Oracle Database 10g
• Computationally intensive PL/SQL programs compiled under Oracle Database 10g will run, on average, twice as fast as they did under Oracle9i Database Release 2.
• PL/SQL will run three times as fast as they did under Oracle8 Database.
• Oracle Database 10g enhances support for querying hierarchical, or tree-structured, data
• Automatic Storage Management (ASM) now includes multiple disk operations, and a non-ASM database migration utility to help migrate existing databases while taking advantage of ASM’s dynamic data striping and load balancing.
• Enhancements to data provisioning and Oracle Streams making it much easier and faster to archive, move, and copy large data sets to better share information in a grid environment.
• Cluster Ready Services Open API for higher application availability and built in cluster ware file redundancy to reduce the risk of failure.
• Fully integrated tape backup and recovery solution, offering reliable Oracle database and file system protection utilizing tape devices.
• New Fast-Start Failover for rapid and automatic fail over to standby databases, without requiring any manual intervention.
• Integrated transparent data encryption and key management in the database, helping customers protect sensitive data in an Oracle Database without any changes to their application.
• Automated database administration enhancements include statistics collection directly from memory, eliminating the need to execute SQL queries.
• Application development enhancements include Xquery feature for queries and mapping of XML results inside the database.
• The cost of business intelligence is reduced with data mining PL/SQL package support for analytic applications such as Oracle Discoverer.
• Improved VLDB support is available with more partitions per table and more efficient partition management and query optimization.
• Information cycle time is reduced with enhanced data loading and query processing improvements.
• PL/SQL will run three times as fast as they did under Oracle8 Database.
• Oracle Database 10g enhances support for querying hierarchical, or tree-structured, data
• Automatic Storage Management (ASM) now includes multiple disk operations, and a non-ASM database migration utility to help migrate existing databases while taking advantage of ASM’s dynamic data striping and load balancing.
• Enhancements to data provisioning and Oracle Streams making it much easier and faster to archive, move, and copy large data sets to better share information in a grid environment.
• Cluster Ready Services Open API for higher application availability and built in cluster ware file redundancy to reduce the risk of failure.
• Fully integrated tape backup and recovery solution, offering reliable Oracle database and file system protection utilizing tape devices.
• New Fast-Start Failover for rapid and automatic fail over to standby databases, without requiring any manual intervention.
• Integrated transparent data encryption and key management in the database, helping customers protect sensitive data in an Oracle Database without any changes to their application.
• Automated database administration enhancements include statistics collection directly from memory, eliminating the need to execute SQL queries.
• Application development enhancements include Xquery feature for queries and mapping of XML results inside the database.
• The cost of business intelligence is reduced with data mining PL/SQL package support for analytic applications such as Oracle Discoverer.
• Improved VLDB support is available with more partitions per table and more efficient partition management and query optimization.
• Information cycle time is reduced with enhanced data loading and query processing improvements.
Tuesday, July 6, 2010
Important Application related queries
To check The concurrent request running
set pages 66
set line 132
-- spool progs1.lst
col user_name format a20 word_wrapped
column ProgName format a25 word_wrapped
column requestId format 9999999999
column StartDate format a20 word_Wrapped
column OS_PROCESS_ID format a6
column ETime format 99999999 word_Wrapped
col sid format 99999 word_Wrapped
select sess.sid, oracle_process_id OS_PROCESS_ID,
fusr.description user_name ,
fcp.user_concurrent_program_name progName,
to_char(actual_Start_date,'DD-MON-YYYY HH24:MI:SS') StartDate,
request_id RequestId,
(sysdate - actual_start_date)*24*60*60 ETime
from fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcp,
fnd_user fusr,
v$session sess
where fcp.concurrent_program_id = fcr.concurrent_program_id
and fcr.program_application_id = fcp.application_id
and fcp.language = 'US'
and fcr.phase_code = 'R'
and fcr.status_code = 'R'
and fcr.requested_by = fusr.user_id
and fcr.oracle_session_id = sess.audsid (+)
-- and p.addr = sess.paddr
order by 5 DESC;
Patch Consolidated Info from one databse of another:
ad_applied_patches
ad_patch_runs
ad_patch_drivers
ad_appl_tops
SELECT b.applications_system_name instance_name, d.patch_name, d.patch_type,
c.patch_abstract patch_description, a.patch_top,
a.end_date applied_date
FROM apps.ad_patch_runs@db_link_name a,
apps.ad_appl_tops@db_link_name b,
apps.ad_patch_drivers@db_link_name c,
apps.ad_applied_patches@db_link_name d
WHERE a.appl_top_id = b.appl_top_id
AND a.patch_driver_id = c.patch_driver_id
AND c.applied_patch_id = d.applied_patch_id
AND a.patch_top NOT LIKE '%autobuild%'
ORDER BY a.end_date ASC;
To compare the patches in 2 instances using minus:
create db link in second instance and , then execute in first instance:
set lines 100
col INSTANCE_NAME for a15
col PATCH_NAME for a10
col PATCH_TOP for a40
col APPLIED_DATE for a15
SELECT b.applications_system_name instance_name, d.patch_name,
a.patch_top,
a.end_date applied_date
FROM apps.ad_patch_runs a,
apps.ad_appl_tops b,
apps.ad_patch_drivers c,
apps.ad_applied_patches d
WHERE a.appl_top_id = b.appl_top_id
AND a.patch_driver_id = c.patch_driver_id
AND c.applied_patch_id = d.applied_patch_id
AND a.patch_top NOT LIKE '%autobuild%'
ORDER BY a.end_date ASC;
minus
SELECT b.applications_system_name instance_name, d.patch_name, d.patch_type,
c.patch_abstract patch_description, a.patch_top,
a.end_date applied_date
FROM apps.ad_patch_runs@db_link_name a,
apps.ad_appl_tops@db_link_name b,
apps.ad_patch_drivers@db_link_name c,
apps.ad_applied_patches@db_link_name d
WHERE a.appl_top_id = b.appl_top_id
AND a.patch_driver_id = c.patch_driver_id
AND c.applied_patch_id = d.applied_patch_id
AND a.patch_top NOT LIKE '%autobuild%'
ORDER BY a.end_date ASC;
Script to find out the Status and Patch set level for any modules:
select decode(nvl(a.APPLICATION_short_name,'Not Found'),
'SQLAP','AP','SQLGL','GL','OFA','FA',
'Not Found','id 'to_char(fpi.application_id),
a.APPLICATION_short_name) apps,
decode(fpi.status,'I','Installed','S','Shared',
'N','Inactive',fpi.status) status,
fpi.product_version,
nvl(fpi.patch_level,'-- Not Available --') Patchset,
to_char(fpi.last_update_date,'dd-Mon-RRRR') "Update Date"
from fnd_oracle_userid o, fnd_application a, fnd_product_installations fpi
where fpi.application_id = a.application_id(+)
and fpi.oracle_id = o.oracle_id(+)
order by 1,2;
DB Link:
create database link "db_link_name"
connect to
identified by
using '';
GRANT CREATE DATABASE LINK TO ;
select * from global_name;
select sysdate from dual
select * from global_name@
SELECT 'connect ' owner '/$' owner '_PWD ' ' ' 'drop database link ' db_link ';'
,DBMS_METADATA.GET_DDL('DB_LINK',a.db_link,a.owner) ';' from dba_db_links a;
set pages 66
set line 132
-- spool progs1.lst
col user_name format a20 word_wrapped
column ProgName format a25 word_wrapped
column requestId format 9999999999
column StartDate format a20 word_Wrapped
column OS_PROCESS_ID format a6
column ETime format 99999999 word_Wrapped
col sid format 99999 word_Wrapped
select sess.sid, oracle_process_id OS_PROCESS_ID,
fusr.description user_name ,
fcp.user_concurrent_program_name progName,
to_char(actual_Start_date,'DD-MON-YYYY HH24:MI:SS') StartDate,
request_id RequestId,
(sysdate - actual_start_date)*24*60*60 ETime
from fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcp,
fnd_user fusr,
v$session sess
where fcp.concurrent_program_id = fcr.concurrent_program_id
and fcr.program_application_id = fcp.application_id
and fcp.language = 'US'
and fcr.phase_code = 'R'
and fcr.status_code = 'R'
and fcr.requested_by = fusr.user_id
and fcr.oracle_session_id = sess.audsid (+)
-- and p.addr = sess.paddr
order by 5 DESC;
Patch Consolidated Info from one databse of another:
ad_applied_patches
ad_patch_runs
ad_patch_drivers
ad_appl_tops
SELECT b.applications_system_name instance_name, d.patch_name, d.patch_type,
c.patch_abstract patch_description, a.patch_top,
a.end_date applied_date
FROM apps.ad_patch_runs@db_link_name a,
apps.ad_appl_tops@db_link_name b,
apps.ad_patch_drivers@db_link_name c,
apps.ad_applied_patches@db_link_name d
WHERE a.appl_top_id = b.appl_top_id
AND a.patch_driver_id = c.patch_driver_id
AND c.applied_patch_id = d.applied_patch_id
AND a.patch_top NOT LIKE '%autobuild%'
ORDER BY a.end_date ASC;
To compare the patches in 2 instances using minus:
create db link in second instance and , then execute in first instance:
set lines 100
col INSTANCE_NAME for a15
col PATCH_NAME for a10
col PATCH_TOP for a40
col APPLIED_DATE for a15
SELECT b.applications_system_name instance_name, d.patch_name,
a.patch_top,
a.end_date applied_date
FROM apps.ad_patch_runs a,
apps.ad_appl_tops b,
apps.ad_patch_drivers c,
apps.ad_applied_patches d
WHERE a.appl_top_id = b.appl_top_id
AND a.patch_driver_id = c.patch_driver_id
AND c.applied_patch_id = d.applied_patch_id
AND a.patch_top NOT LIKE '%autobuild%'
ORDER BY a.end_date ASC;
minus
SELECT b.applications_system_name instance_name, d.patch_name, d.patch_type,
c.patch_abstract patch_description, a.patch_top,
a.end_date applied_date
FROM apps.ad_patch_runs@db_link_name a,
apps.ad_appl_tops@db_link_name b,
apps.ad_patch_drivers@db_link_name c,
apps.ad_applied_patches@db_link_name d
WHERE a.appl_top_id = b.appl_top_id
AND a.patch_driver_id = c.patch_driver_id
AND c.applied_patch_id = d.applied_patch_id
AND a.patch_top NOT LIKE '%autobuild%'
ORDER BY a.end_date ASC;
Script to find out the Status and Patch set level for any modules:
select decode(nvl(a.APPLICATION_short_name,'Not Found'),
'SQLAP','AP','SQLGL','GL','OFA','FA',
'Not Found','id 'to_char(fpi.application_id),
a.APPLICATION_short_name) apps,
decode(fpi.status,'I','Installed','S','Shared',
'N','Inactive',fpi.status) status,
fpi.product_version,
nvl(fpi.patch_level,'-- Not Available --') Patchset,
to_char(fpi.last_update_date,'dd-Mon-RRRR') "Update Date"
from fnd_oracle_userid o, fnd_application a, fnd_product_installations fpi
where fpi.application_id = a.application_id(+)
and fpi.oracle_id = o.oracle_id(+)
order by 1,2;
DB Link:
create database link "db_link_name"
connect to
identified by
using '';
GRANT CREATE DATABASE LINK TO ;
select * from global_name;
select sysdate from dual
select * from global_name@
SELECT 'connect ' owner '/$' owner '_PWD ' ' ' 'drop database link ' db_link ';'
,DBMS_METADATA.GET_DDL('DB_LINK',a.db_link,a.owner) ';' from dba_db_links a;
Oracle 11i Applications Concepts
1. What are server, node and tier in 11i?
• A server is a process or group of processes that runs on a single machine and provides a particular functionality, often referred to as a service.
• A tier is a logical grouping of services, potentially spread across more than one physical machine.
• A machine may be referred to as a node, particularly in the context of a group of computers that work closely together in a cluster.
2. How many tiers present in a typical Oracle Applications and explain them briefly?
a. Desktop Tier
The client interface is provided through HTML for the newer HTML-based applications, and via a Java applet in a Web browser for the traditional Forms-based interface.
Each user logs in to Oracle Applications through the E-Business Suite Home Page on a desktop client web browser. The E-Business Suite Home Page provides a single point of access to HTML-based applications, Forms-based applications, and Business Intelligence applications.
b. Application Tier
The application tier has a dual role: hosting the various servers that process the business logic, and managing communication between the desktop tier and the database tier. This tier is sometimes referred to as the middle tier.
Six servers comprise the application tier for Oracle Applications:
• Web server
• Forms server
• Reports server
• Discoverer server (optional)
• Concurrent Processing server
• Admin server
c. Database Tier
The database tier contains the Oracle database server, which stores all the data maintained by Oracle Applications. The database also stores the Oracle Applications online help information.
More specifically, the database tier contains the Oracle data server files and Oracle Applications database executables that physically store the tables, indexes, and other database objects for your system. In general, the database server does not communicate directly with the desktop clients, but rather with the servers on the application tier, which mediate the communications between the database server and the clients.
3. What is Client applet?
The Forms client applet is a general-purpose presentation applet that supports all Oracle Applications Forms-based products, including those with customizations and extensions.
The Forms client applet is packaged as a collection of Java Archive (JAR) files. The JAR files contain all Java classes required to run the presentation layer of Oracle Applications forms.
4. What is Oracle Jinitiator?
The Forms client applet must run within a Java Virtual Machine (JVM) on the desktop client. The Oracle JInitiator component allows use of the Oracle JVM on web clients, instead of the browser’s own JVM. The Forms client applet and commonly used JAR files are downloaded from the Web server at the beginning of the client’s first session. Less commonly used JAR files are downloaded as needed. The JAR files are cached in a directory specified when JInitiator was installed.
5. What is Application tier Load balancing?
The application tier supports load balancing among many of its servers to provide higher availability, fault tolerance, reliability, and optimal scalability.
Typically, load balancing is most commonly used to spread the load across multiple
Web servers, where the load may vary significantly.
6. What is Web Server?
The Oracle HTTP server (powered by Apache) acts as the Web server. It processes the
requests received over the network from the desktop clients, and includes additional
components such as:
• Web Listener
• Java Servlet Engine
• JavaServer Pages (JSP)
The Web listener component of the Oracle HTTP server accepts incoming HTTP requests (for particular URLs) from client browsers.
If possible, the Web server services the requests itself, for example by returning the
HTML to construct a simple Web page. If the page referenced by the URL needs
advanced processing, the listener passes the request on to the servlet engine, which
contacts the database server as needed.
7. What is Oracle Applications Framework?
The Oracle Applications Framework is the development platform for HTML-based applications. It consists of a Java-based application tier framework and associated services, designed to facilitate the rapid deployment of HTML-based applications.
8. What is Business Components for Java (BC4J)?
BC4J is included in Oracle JDeveloper, is used to create Java business components for representing business logic. It also provides a mechanism for mapping relational tables to Java objects, and allows the separation of the application business logic from the user interface.
9. What is AOL/J ?
AOL/J supplies the Oracle Applications Framework with underlying security and applications Java services. It provides the Oracle Applications Framework with its connection to the database, and with application-specific functionality such as flexfields.
10. What is Forms Server?
The Forms server hosts the Oracle Applications forms and associated runtime engine that support the professional interface. It is an Oracle Developer 6i component that mediates the communication between the desktop client and the Oracle database server, displaying client screens and initiating changes in the database according to user actions.
11. What protocols used during Forms server access?
The Forms server communicates with the desktop client using these protocols:
• Standard HTTP network connection
• Secure HTTPS network connection
• TCP/IP connection
12. What is Metrics server?
Oracle Applications supports automatic load balancing among multiple Forms
servers. Such a load balancing configuration utilizes an additional Forms component
called the Metrics server, which acts as a single point of coordination for all Forms
server requests.
The Metrics server is located on one application server. Metrics clients located on the
other application servers periodically send load information to the Metrics server so that
it can determine which application server has the lightest load.
13. What is Reports server?
Reports generated by the Reports server are monitored and administered separately from concurrent processing reports. The Reports server dynamically selects the report language at runtime, so users see their reports in their preferred language.
14. What is Discoverer server?
Discoverer is an ad hoc query, reporting, analysis, and
publishing tool that allows business users at all levels of an organization to gain
immediate access to information from data marts, data warehouses, and online
transaction processing (OLTP) systems.
15. What is Concurrent Processing server?
An Oracle Applications server that runs time-consuming, noninteractive tasks in the background.
16. What is a concurrent request?
A request issued to the Concurrent Processing server when you submit a noninteractive task, such as running a report.
17. What is Concurrent Manager?
A process manager that coordinates the processes generated by users’ requests to run various data-intensive programs. An Oracle Applications product group can have several concurrent managers.
18. What is concurrent queue?
A list of concurrent requests awaiting completion. Each concurrent manager has its own queue of pending requests.
19. What is Internal Concurrent manager?
The Internal Concurrent Manager service monitors the database table for new requests, controls the other concurrent managers, and determines when a request should be processed and which concurrent manager should carry it out.
20. What is Parallel Concurrent Processing?
Parallel concurrent processing allows you to distribute concurrent managers across multiple nodes in a cluster environment, spreading concurrent processing across available nodes.
21. What is the advantage of having Parallel Concurrent Processing?
Parallel concurrent processing enables you to:
• Run concurrent processes on multiple nodes to improve concurrent processing throughput.
• Continue running concurrent processes on the remaining nodes when one or more nodes fail.
• Administer concurrent managers running on multiple nodes from any node in the cluster.
22. What is Admin Server?
The Admin (Administration) server is located on the node where you maintain the data model and data in your Oracle Applications database.
• Upgrading Oracle Applications
• Applying database patches to Oracle Applications
• Maintaining Oracle Applications data
23. What is Daily Business Intelligence?
Daily Business Intelligence (DBI) is a reporting framework that is integrated with Oracle E-Business Suite.
Using Daily Business Intelligence overview pages, managers can view summarized information across multiple organizations, drilling down to specific transaction details on a daily basis.
24. What is Oracle Applications Technology Layer?
The Oracle Applications technology layer lies between the Oracle Applications technology stack and the Oracle Applications product-specific modules. It provides features common to all Oracle Applications products.
25. Name few products available in Oracle Applications Technology Layer?
Products in the Oracle Applications technology layer include:
• Oracle Applications DBA (AD)
• Oracle Applications Object Library (FND)
• Oracle Applications Utilities (AU)
• Oracle Common Modules (AK)
• Oracle Workflow (WF)
• Oracle Alert (ALR)
• Oracle Applications Framework (FWK)
• Oracle XML Publisher (XDO)
26. What is Oracle Applications DBA (AD)?
The Applications DBA product provides a set of tools for administration of the Oracle Applications file system and database. AD tools are used for installing, upgrading, maintaining, and patching the Oracle Applications system.
• A server is a process or group of processes that runs on a single machine and provides a particular functionality, often referred to as a service.
• A tier is a logical grouping of services, potentially spread across more than one physical machine.
• A machine may be referred to as a node, particularly in the context of a group of computers that work closely together in a cluster.
2. How many tiers present in a typical Oracle Applications and explain them briefly?
a. Desktop Tier
The client interface is provided through HTML for the newer HTML-based applications, and via a Java applet in a Web browser for the traditional Forms-based interface.
Each user logs in to Oracle Applications through the E-Business Suite Home Page on a desktop client web browser. The E-Business Suite Home Page provides a single point of access to HTML-based applications, Forms-based applications, and Business Intelligence applications.
b. Application Tier
The application tier has a dual role: hosting the various servers that process the business logic, and managing communication between the desktop tier and the database tier. This tier is sometimes referred to as the middle tier.
Six servers comprise the application tier for Oracle Applications:
• Web server
• Forms server
• Reports server
• Discoverer server (optional)
• Concurrent Processing server
• Admin server
c. Database Tier
The database tier contains the Oracle database server, which stores all the data maintained by Oracle Applications. The database also stores the Oracle Applications online help information.
More specifically, the database tier contains the Oracle data server files and Oracle Applications database executables that physically store the tables, indexes, and other database objects for your system. In general, the database server does not communicate directly with the desktop clients, but rather with the servers on the application tier, which mediate the communications between the database server and the clients.
3. What is Client applet?
The Forms client applet is a general-purpose presentation applet that supports all Oracle Applications Forms-based products, including those with customizations and extensions.
The Forms client applet is packaged as a collection of Java Archive (JAR) files. The JAR files contain all Java classes required to run the presentation layer of Oracle Applications forms.
4. What is Oracle Jinitiator?
The Forms client applet must run within a Java Virtual Machine (JVM) on the desktop client. The Oracle JInitiator component allows use of the Oracle JVM on web clients, instead of the browser’s own JVM. The Forms client applet and commonly used JAR files are downloaded from the Web server at the beginning of the client’s first session. Less commonly used JAR files are downloaded as needed. The JAR files are cached in a directory specified when JInitiator was installed.
5. What is Application tier Load balancing?
The application tier supports load balancing among many of its servers to provide higher availability, fault tolerance, reliability, and optimal scalability.
Typically, load balancing is most commonly used to spread the load across multiple
Web servers, where the load may vary significantly.
6. What is Web Server?
The Oracle HTTP server (powered by Apache) acts as the Web server. It processes the
requests received over the network from the desktop clients, and includes additional
components such as:
• Web Listener
• Java Servlet Engine
• JavaServer Pages (JSP)
The Web listener component of the Oracle HTTP server accepts incoming HTTP requests (for particular URLs) from client browsers.
If possible, the Web server services the requests itself, for example by returning the
HTML to construct a simple Web page. If the page referenced by the URL needs
advanced processing, the listener passes the request on to the servlet engine, which
contacts the database server as needed.
7. What is Oracle Applications Framework?
The Oracle Applications Framework is the development platform for HTML-based applications. It consists of a Java-based application tier framework and associated services, designed to facilitate the rapid deployment of HTML-based applications.
8. What is Business Components for Java (BC4J)?
BC4J is included in Oracle JDeveloper, is used to create Java business components for representing business logic. It also provides a mechanism for mapping relational tables to Java objects, and allows the separation of the application business logic from the user interface.
9. What is AOL/J ?
AOL/J supplies the Oracle Applications Framework with underlying security and applications Java services. It provides the Oracle Applications Framework with its connection to the database, and with application-specific functionality such as flexfields.
10. What is Forms Server?
The Forms server hosts the Oracle Applications forms and associated runtime engine that support the professional interface. It is an Oracle Developer 6i component that mediates the communication between the desktop client and the Oracle database server, displaying client screens and initiating changes in the database according to user actions.
11. What protocols used during Forms server access?
The Forms server communicates with the desktop client using these protocols:
• Standard HTTP network connection
• Secure HTTPS network connection
• TCP/IP connection
12. What is Metrics server?
Oracle Applications supports automatic load balancing among multiple Forms
servers. Such a load balancing configuration utilizes an additional Forms component
called the Metrics server, which acts as a single point of coordination for all Forms
server requests.
The Metrics server is located on one application server. Metrics clients located on the
other application servers periodically send load information to the Metrics server so that
it can determine which application server has the lightest load.
13. What is Reports server?
Reports generated by the Reports server are monitored and administered separately from concurrent processing reports. The Reports server dynamically selects the report language at runtime, so users see their reports in their preferred language.
14. What is Discoverer server?
Discoverer is an ad hoc query, reporting, analysis, and
publishing tool that allows business users at all levels of an organization to gain
immediate access to information from data marts, data warehouses, and online
transaction processing (OLTP) systems.
15. What is Concurrent Processing server?
An Oracle Applications server that runs time-consuming, noninteractive tasks in the background.
16. What is a concurrent request?
A request issued to the Concurrent Processing server when you submit a noninteractive task, such as running a report.
17. What is Concurrent Manager?
A process manager that coordinates the processes generated by users’ requests to run various data-intensive programs. An Oracle Applications product group can have several concurrent managers.
18. What is concurrent queue?
A list of concurrent requests awaiting completion. Each concurrent manager has its own queue of pending requests.
19. What is Internal Concurrent manager?
The Internal Concurrent Manager service monitors the database table for new requests, controls the other concurrent managers, and determines when a request should be processed and which concurrent manager should carry it out.
20. What is Parallel Concurrent Processing?
Parallel concurrent processing allows you to distribute concurrent managers across multiple nodes in a cluster environment, spreading concurrent processing across available nodes.
21. What is the advantage of having Parallel Concurrent Processing?
Parallel concurrent processing enables you to:
• Run concurrent processes on multiple nodes to improve concurrent processing throughput.
• Continue running concurrent processes on the remaining nodes when one or more nodes fail.
• Administer concurrent managers running on multiple nodes from any node in the cluster.
22. What is Admin Server?
The Admin (Administration) server is located on the node where you maintain the data model and data in your Oracle Applications database.
• Upgrading Oracle Applications
• Applying database patches to Oracle Applications
• Maintaining Oracle Applications data
23. What is Daily Business Intelligence?
Daily Business Intelligence (DBI) is a reporting framework that is integrated with Oracle E-Business Suite.
Using Daily Business Intelligence overview pages, managers can view summarized information across multiple organizations, drilling down to specific transaction details on a daily basis.
24. What is Oracle Applications Technology Layer?
The Oracle Applications technology layer lies between the Oracle Applications technology stack and the Oracle Applications product-specific modules. It provides features common to all Oracle Applications products.
25. Name few products available in Oracle Applications Technology Layer?
Products in the Oracle Applications technology layer include:
• Oracle Applications DBA (AD)
• Oracle Applications Object Library (FND)
• Oracle Applications Utilities (AU)
• Oracle Common Modules (AK)
• Oracle Workflow (WF)
• Oracle Alert (ALR)
• Oracle Applications Framework (FWK)
• Oracle XML Publisher (XDO)
26. What is Oracle Applications DBA (AD)?
The Applications DBA product provides a set of tools for administration of the Oracle Applications file system and database. AD tools are used for installing, upgrading, maintaining, and patching the Oracle Applications system.
Practice 9i DBA practicals
SESSION 1.
1. Create a database user?
SQL> create user test1 identified by test1
default tablespace users temporary tablespace temp;
2. Grant connect and resource privileges to the user created?
SQL> grant connect, resource to test1;
3. Create a tablespace with locally managed extent along with a datafile?
SQL> create tablespace test01
2 logging
3 datafile 'd:\oracle\oradata\prod\test01.dbf'
4 size 10m
5 autoextend on
6 next 10m maxsize 100m
7 extent management local;
4. Create a temporary tablespace with locally managed extents along with a datafile?
SQL> create temporary tablespace temp_test01
2 tempfile 'd:\oracle\oradata\prod\temp_test_01.dbf'
3 size 10m
4 autoextend on
5 next 10m maxsize 100m
6 extent management local;
5. Alter tablespace to add additional datafile?
SQL> alter tablespace test01
2 add datafile 'd:\oracle\oradata\prod\test_02.dbf'
3 size 10m autoextend on
4 next 10m maxsize 100m;
6. Alter tablespace to increase the size of one datafile?
SQL> alter database
2 datafile 'd:\oracle\oradata\prod\TEST_02.dbf'
3 resize 200m;
7. Get the list of data files present in a database?
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
D:\ORACLE\ORADATA\PROD\SYSTEM01.DBF
D:\ORACLE\ORADATA\PROD\UNDOTBS01.DBF
D:\ORACLE\ORADATA\PROD\CWMLITE01.DBF
D:\ORACLE\ORADATA\PROD\DRSYS01.DBF
D:\ORACLE\ORADATA\PROD\EXAMPLE01.DBF
D:\ORACLE\ORADATA\PROD\INDX01.DBF
D:\ORACLE\ORADATA\PROD\ODM01.DBF
D:\ORACLE\ORADATA\PROD\TOOLS01.DBF
D:\ORACLE\ORADATA\PROD\USERS01.DBF
D:\ORACLE\ORADATA\PROD\XDB01.DBF
D:\ORACLE\ORADATA\PROD\TEST01.DBF
NAME
--------------------------------------------------------------------------------
D:\ORACLE\ORADATA\PROD\TEST_02.DBF
12 rows selected.
8. get the list of tablespaces present in a database?
SQL> select name from v$tablespace;
NAME
------------------------------
CWMLITE
DRSYS
EXAMPLE
INDX
ODM
SYSTEM
TOOLS
UNDOTBS1
USERS
XDB
TEMP
NAME
------------------------------
TEST01
TEMP_TEST01
13 rows selected.
9. Get the list of users and their default tablespaces associated with them in a given database?
SQL> select username, default_tablespace
2 from dba_users
3 order by username;
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
ANONYMOUS XDB
CTXSYS DRSYS
DBSNMP SYSTEM
HR EXAMPLE
MDSYS SYSTEM
ODM ODM
ODM_MTR ODM
OE EXAMPLE
OLAPSYS CWMLITE
ORDPLUGINS SYSTEM
ORDSYS SYSTEM
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
OUTLN SYSTEM
PM EXAMPLE
QS EXAMPLE
QS_ADM EXAMPLE
QS_CB EXAMPLE
QS_CBADM EXAMPLE
QS_CS EXAMPLE
QS_ES EXAMPLE
QS_OS EXAMPLE
QS_WS EXAMPLE
RMAN TOOLS
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
SCOTT SYSTEM
SH EXAMPLE
SYS SYSTEM
SYSTEM SYSTEM
TEST1 USERS
WKPROXY DRSYS
WKSYS DRSYS
WMSYS SYSTEM
XDB XDB
31 rows selected.
10. Query database to get to know if spfile or pfile being used?
This query returns NULL in the value column if you are using the pfile.
SQL> SELECT name, value FROM v$parameter
2 WHERE name = 'spfile';
NAME VALUE
---------- --------------------------------------------------
spfile %ORACLE_HOME%\DATABASE\SPFILE%ORACLE_SID%.ORA
Alternatively, use the following query, which returns NULL in the value column if you are using pfile and not spfile:
SQL> SHOW PARAMETER spfile
NAME TYPE VALUE
------- ------- ------------------------------
spfile string %ORACLE_HOME%\DATABASE\SPFILE%ORACLE_SID%.ORA
On the other hand, you may check for the count of the following query; if the count is non-zero then the instance is using a spfile, and if the count is zero then it is using a pfile:
SQL> SELECT COUNT(*) FROM v$spparameter WHERE value IS NOT NULL;
SESSION 2.
1. Check if the database is in archivelog mode or not?
SQL> select name, log_mode
2 from v$database;
NAME LOG_MODE
--------- ------------
PROD ARCHIVELOG
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination D:\oracle\oradata\prod\archive
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3
2. Check the size of the database in SQL session?
To find out how many megabytes are allocated to ALL datafiles:
>select sum(bytes)/1024/1024 "Meg" from dba_data_files;
To get the size of all TEMP files:
>select nvl(sum(bytes),0)/1024/1024 "Meg" from dba_temp_files;
To get the size of the on-line redo-logs:
>select sum(bytes)/1024/1024 "Meg" from sys.v_$log;
Putting it all together into a single query:
select a.data_size+b.temp_size+c.redo_size "total_size"
from ( select sum(bytes) data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0) temp_size
from dba_temp_files ) b,
( select sum(bytes) redo_size
from sys.v_$log ) c
/
3. Check the size of all the tablespace and also check if they have autoextendable or not?
4. Export data of schema into a physical file?
>exp system/ajai@prod file=test2.dmp compress=n consistent=n
constraints=y owner=test1 grants=y indexes=y record=y rows=y feedback=1000 log=test2.log buffer=1000
5. Take a full export of database into a physical file?
> exp userid=system/manager@prod file=full_db_18sep06.dmp
log=full_db_18sep06.log full=y
6. Create a rman user and assign access privileges to the user and check for the connectivity to rman with the user?
SQL> create user backup_admin identified by backup_admin
2 default tablespace users temporary tablespace temp;
SQL> grant sysdba to backup_admin;
C:\>rman target backup_admin/backup_admin@prod
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: PROD (DBID=5469274)
RMAN> show all;
using target database controlfile instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\ORACLE\ORA92\DATABASE\SNCFPROD.ORA';
# default
RMAN>
7. Create a database link and verify if the data is accessible from the other database?
SQL> create database link testcrp
2 connect to system identified by "manager"
3 using 'TEST';
SQL> select * from tab@testcrp;
8. Create a table with five columns and insert two rows of data into the table?
CREATE TABLE TEMPO1
(
NAME VARCHAR2(10 BYTE) NOT NULL,
AGE NUMBER NOT NULL,
CITY VARCHAR2(20 BYTE),
STATE VARCHAR2(20 BYTE),
COUNTRY VARCHAR2(20 BYTE)
)
SQL> insert into tempo1
2 (name,age,city,state,country)
3 values
4 ('Ram',32,'Bangalore','Karnataka','India');
SQL> insert into tempo1
2 (Name, Age, City, State, Country)
3 values
4 ('Sree',30,'Bellary','Karnataka','India');
9. Change the parameter file to pfile instead of spfile?
SQL> create pfile from spfile;
SQL> shutdown immediate;
SQL> startup pfile='D:\oracle\ora92\database\initprod.ora';
10. Create a passworfile for authentication from OS?
SESSION 3.
1. Change the database to use archivelog mode?
# add the following parameters
log_archive_start=TRUE
log_archive_max_processes=2
log_archive_dest_1="LOCATION=/demo/oracle/db/oradata/demo/arch
MANDATORY REOPEN"
log_archive_format=arch_%t_%s.arc
SQL> show parameter log_archive_start;
NAME TYPE VALUE
------------------------------------ ----------- --------------
log_archive_start boolean TRUE
if the value is FALSE then execute below command:
SQL> show parameter log_archive_max_processes;
NAME TYPE VALUE
------------------------------------ ----------- ---------------
log_archive_max_processes integer 2
SQL> show parameter log_archive_dest_1;
NAME TYPE VALUE
----------------------- ---------- -----------------
log_archive_dest_1 string LOCATION=D:\oracle\oradata\prod\archive
SQL> show parameter log_archive_format;
NAME TYPE VALUE
------------------------------------ ----------- --------------
log_archive_format string %t_%s.dbf
SQL> alter system set log_archive_start=TRUE scope=spfile;
SQL> alter system set log_archive_max_processes=2 scope=both;
SQL> alter system set log_archive_dest_1=”LOCATION=d:\oracle\oradata\prod\arch MANDATORY REOPEN” scope=both;
SQL> alter system set log_archive_format=”arch%s.arc” scope=spfile;
Then
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database open;
After the database is started check for archivelog mode.
SQL>select log_mode from v$database;
And
SQL>archive log list
2. Take a backup of control file to trace in a binary format ?
SQL> alter database backup
2 controlfile to trace as 'd:\ramakrishna\pract\PROD_controlfile.sql';
3. Configure the database to do adhoc hotbackup of database and archivelog’s through rman without a catalog?
1. Create a rman user ‘backup_admin/backup_admin’ with default tablespace as users and temporary tablespace as temp.
SQL>create user backup_admin identified as backup_admin
Default tablespace users temporary tablespace temp;
2. Give sysdba privileges to the above user.
SQL>conn / as sysdba
SQL>grant sysdba to backup_admin;
3. Connect to SQL session with user backup_admin and check.
SQL>conn backup_admin/backup_admin
SQL>select * from tab;
4. Connect to rman with user backup_admin and check for configuration.
C:\>rman target backup_admin/backup_admin@prod nocatalog
5. Check the default configuration of rman.
RMAN>show all;
6. Change below configuration in RMAN to take backup to hard disk.
RMAN> run {
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'd:\oracle\oradata\prod\%F';
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT 'd:\ramakrishna\pract\rman\backup_db_%d_S_%s_P_%p_T_%t' MAXPIECESIZE 1024M;
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT 'd:\ramakrishna\pract\rman\backup_db2_%d_S_%s_P_%p_T_%t' MAXPIECESIZE 1024M;
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
}
7. Make a note of DBID you are taking backup.
C:\>rman target backup_admin/backup_admin@prod nocatalog
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: PROD (DBID=5469274)
RMAN>
8. Backup the target database by running the commands as a script.
C:\>rman target backup_admin/backup_admin@prod nocatalog msglog rman_full_backup_db_20sep2006.log
RMAN>run {
# -----------------------------------------------------------
# The following RMAN commands are run each day.
# The steps are:
# - Re-start the database to perform crash recovery, in
# case the database is not currently open, and was not
# shut down consistently. The database is started in DBA
# mode so that normal users cannot connect.
# - Shut down with the IMMEDIATE option to close the
# database consistently.
# - Startup and mount the database.
# - Backup database.
# - Open database for normal operation.
# -----------------------------------------------------------
startup force dba;
shutdown immediate;
startup mount;
backup database plus archivelog delete input;
alter database open;
}
exit
9. Validate the backup taken in the previous step.
run {
restore database validate;
}
10. Also check for the consistency of restoring a tablespace.
run {
restore tablespace users validate;
}
4. Recover only a tablespace from the backup?
1. Connect to rman and check if the latest backup is present.
C:\>rman target backup_admin/backup_admin@prod nocatalog
RMAN>list backup summary;
RMAN>run {
restore database validate;
}
RMAN>run {
restore tablespace test01 validate;
}
1. Create a database user?
SQL> create user test1 identified by test1
default tablespace users temporary tablespace temp;
2. Grant connect and resource privileges to the user created?
SQL> grant connect, resource to test1;
3. Create a tablespace with locally managed extent along with a datafile?
SQL> create tablespace test01
2 logging
3 datafile 'd:\oracle\oradata\prod\test01.dbf'
4 size 10m
5 autoextend on
6 next 10m maxsize 100m
7 extent management local;
4. Create a temporary tablespace with locally managed extents along with a datafile?
SQL> create temporary tablespace temp_test01
2 tempfile 'd:\oracle\oradata\prod\temp_test_01.dbf'
3 size 10m
4 autoextend on
5 next 10m maxsize 100m
6 extent management local;
5. Alter tablespace to add additional datafile?
SQL> alter tablespace test01
2 add datafile 'd:\oracle\oradata\prod\test_02.dbf'
3 size 10m autoextend on
4 next 10m maxsize 100m;
6. Alter tablespace to increase the size of one datafile?
SQL> alter database
2 datafile 'd:\oracle\oradata\prod\TEST_02.dbf'
3 resize 200m;
7. Get the list of data files present in a database?
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
D:\ORACLE\ORADATA\PROD\SYSTEM01.DBF
D:\ORACLE\ORADATA\PROD\UNDOTBS01.DBF
D:\ORACLE\ORADATA\PROD\CWMLITE01.DBF
D:\ORACLE\ORADATA\PROD\DRSYS01.DBF
D:\ORACLE\ORADATA\PROD\EXAMPLE01.DBF
D:\ORACLE\ORADATA\PROD\INDX01.DBF
D:\ORACLE\ORADATA\PROD\ODM01.DBF
D:\ORACLE\ORADATA\PROD\TOOLS01.DBF
D:\ORACLE\ORADATA\PROD\USERS01.DBF
D:\ORACLE\ORADATA\PROD\XDB01.DBF
D:\ORACLE\ORADATA\PROD\TEST01.DBF
NAME
--------------------------------------------------------------------------------
D:\ORACLE\ORADATA\PROD\TEST_02.DBF
12 rows selected.
8. get the list of tablespaces present in a database?
SQL> select name from v$tablespace;
NAME
------------------------------
CWMLITE
DRSYS
EXAMPLE
INDX
ODM
SYSTEM
TOOLS
UNDOTBS1
USERS
XDB
TEMP
NAME
------------------------------
TEST01
TEMP_TEST01
13 rows selected.
9. Get the list of users and their default tablespaces associated with them in a given database?
SQL> select username, default_tablespace
2 from dba_users
3 order by username;
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
ANONYMOUS XDB
CTXSYS DRSYS
DBSNMP SYSTEM
HR EXAMPLE
MDSYS SYSTEM
ODM ODM
ODM_MTR ODM
OE EXAMPLE
OLAPSYS CWMLITE
ORDPLUGINS SYSTEM
ORDSYS SYSTEM
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
OUTLN SYSTEM
PM EXAMPLE
QS EXAMPLE
QS_ADM EXAMPLE
QS_CB EXAMPLE
QS_CBADM EXAMPLE
QS_CS EXAMPLE
QS_ES EXAMPLE
QS_OS EXAMPLE
QS_WS EXAMPLE
RMAN TOOLS
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
SCOTT SYSTEM
SH EXAMPLE
SYS SYSTEM
SYSTEM SYSTEM
TEST1 USERS
WKPROXY DRSYS
WKSYS DRSYS
WMSYS SYSTEM
XDB XDB
31 rows selected.
10. Query database to get to know if spfile or pfile being used?
This query returns NULL in the value column if you are using the pfile.
SQL> SELECT name, value FROM v$parameter
2 WHERE name = 'spfile';
NAME VALUE
---------- --------------------------------------------------
spfile %ORACLE_HOME%\DATABASE\SPFILE%ORACLE_SID%.ORA
Alternatively, use the following query, which returns NULL in the value column if you are using pfile and not spfile:
SQL> SHOW PARAMETER spfile
NAME TYPE VALUE
------- ------- ------------------------------
spfile string %ORACLE_HOME%\DATABASE\SPFILE%ORACLE_SID%.ORA
On the other hand, you may check for the count of the following query; if the count is non-zero then the instance is using a spfile, and if the count is zero then it is using a pfile:
SQL> SELECT COUNT(*) FROM v$spparameter WHERE value IS NOT NULL;
SESSION 2.
1. Check if the database is in archivelog mode or not?
SQL> select name, log_mode
2 from v$database;
NAME LOG_MODE
--------- ------------
PROD ARCHIVELOG
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination D:\oracle\oradata\prod\archive
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3
2. Check the size of the database in SQL session?
To find out how many megabytes are allocated to ALL datafiles:
>select sum(bytes)/1024/1024 "Meg" from dba_data_files;
To get the size of all TEMP files:
>select nvl(sum(bytes),0)/1024/1024 "Meg" from dba_temp_files;
To get the size of the on-line redo-logs:
>select sum(bytes)/1024/1024 "Meg" from sys.v_$log;
Putting it all together into a single query:
select a.data_size+b.temp_size+c.redo_size "total_size"
from ( select sum(bytes) data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0) temp_size
from dba_temp_files ) b,
( select sum(bytes) redo_size
from sys.v_$log ) c
/
3. Check the size of all the tablespace and also check if they have autoextendable or not?
4. Export data of schema into a physical file?
>exp system/ajai@prod file=test2.dmp compress=n consistent=n
constraints=y owner=test1 grants=y indexes=y record=y rows=y feedback=1000 log=test2.log buffer=1000
5. Take a full export of database into a physical file?
> exp userid=system/manager@prod file=full_db_18sep06.dmp
log=full_db_18sep06.log full=y
6. Create a rman user and assign access privileges to the user and check for the connectivity to rman with the user?
SQL> create user backup_admin identified by backup_admin
2 default tablespace users temporary tablespace temp;
SQL> grant sysdba to backup_admin;
C:\>rman target backup_admin/backup_admin@prod
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: PROD (DBID=5469274)
RMAN> show all;
using target database controlfile instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\ORACLE\ORA92\DATABASE\SNCFPROD.ORA';
# default
RMAN>
7. Create a database link and verify if the data is accessible from the other database?
SQL> create database link testcrp
2 connect to system identified by "manager"
3 using 'TEST';
SQL> select * from tab@testcrp;
8. Create a table with five columns and insert two rows of data into the table?
CREATE TABLE TEMPO1
(
NAME VARCHAR2(10 BYTE) NOT NULL,
AGE NUMBER NOT NULL,
CITY VARCHAR2(20 BYTE),
STATE VARCHAR2(20 BYTE),
COUNTRY VARCHAR2(20 BYTE)
)
SQL> insert into tempo1
2 (name,age,city,state,country)
3 values
4 ('Ram',32,'Bangalore','Karnataka','India');
SQL> insert into tempo1
2 (Name, Age, City, State, Country)
3 values
4 ('Sree',30,'Bellary','Karnataka','India');
9. Change the parameter file to pfile instead of spfile?
SQL> create pfile from spfile;
SQL> shutdown immediate;
SQL> startup pfile='D:\oracle\ora92\database\initprod.ora';
10. Create a passworfile for authentication from OS?
SESSION 3.
1. Change the database to use archivelog mode?
# add the following parameters
log_archive_start=TRUE
log_archive_max_processes=2
log_archive_dest_1="LOCATION=/demo/oracle/db/oradata/demo/arch
MANDATORY REOPEN"
log_archive_format=arch_%t_%s.arc
SQL> show parameter log_archive_start;
NAME TYPE VALUE
------------------------------------ ----------- --------------
log_archive_start boolean TRUE
if the value is FALSE then execute below command:
SQL> show parameter log_archive_max_processes;
NAME TYPE VALUE
------------------------------------ ----------- ---------------
log_archive_max_processes integer 2
SQL> show parameter log_archive_dest_1;
NAME TYPE VALUE
----------------------- ---------- -----------------
log_archive_dest_1 string LOCATION=D:\oracle\oradata\prod\archive
SQL> show parameter log_archive_format;
NAME TYPE VALUE
------------------------------------ ----------- --------------
log_archive_format string %t_%s.dbf
SQL> alter system set log_archive_start=TRUE scope=spfile;
SQL> alter system set log_archive_max_processes=2 scope=both;
SQL> alter system set log_archive_dest_1=”LOCATION=d:\oracle\oradata\prod\arch MANDATORY REOPEN” scope=both;
SQL> alter system set log_archive_format=”arch%s.arc” scope=spfile;
Then
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database open;
After the database is started check for archivelog mode.
SQL>select log_mode from v$database;
And
SQL>archive log list
2. Take a backup of control file to trace in a binary format ?
SQL> alter database backup
2 controlfile to trace as 'd:\ramakrishna\pract\PROD_controlfile.sql';
3. Configure the database to do adhoc hotbackup of database and archivelog’s through rman without a catalog?
1. Create a rman user ‘backup_admin/backup_admin’ with default tablespace as users and temporary tablespace as temp.
SQL>create user backup_admin identified as backup_admin
Default tablespace users temporary tablespace temp;
2. Give sysdba privileges to the above user.
SQL>conn / as sysdba
SQL>grant sysdba to backup_admin;
3. Connect to SQL session with user backup_admin and check.
SQL>conn backup_admin/backup_admin
SQL>select * from tab;
4. Connect to rman with user backup_admin and check for configuration.
C:\>rman target backup_admin/backup_admin@prod nocatalog
5. Check the default configuration of rman.
RMAN>show all;
6. Change below configuration in RMAN to take backup to hard disk.
RMAN> run {
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'd:\oracle\oradata\prod\%F';
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT 'd:\ramakrishna\pract\rman\backup_db_%d_S_%s_P_%p_T_%t' MAXPIECESIZE 1024M;
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT 'd:\ramakrishna\pract\rman\backup_db2_%d_S_%s_P_%p_T_%t' MAXPIECESIZE 1024M;
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
}
7. Make a note of DBID you are taking backup.
C:\>rman target backup_admin/backup_admin@prod nocatalog
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: PROD (DBID=5469274)
RMAN>
8. Backup the target database by running the commands as a script.
C:\>rman target backup_admin/backup_admin@prod nocatalog msglog rman_full_backup_db_20sep2006.log
RMAN>run {
# -----------------------------------------------------------
# The following RMAN commands are run each day.
# The steps are:
# - Re-start the database to perform crash recovery, in
# case the database is not currently open, and was not
# shut down consistently. The database is started in DBA
# mode so that normal users cannot connect.
# - Shut down with the IMMEDIATE option to close the
# database consistently.
# - Startup and mount the database.
# - Backup database.
# - Open database for normal operation.
# -----------------------------------------------------------
startup force dba;
shutdown immediate;
startup mount;
backup database plus archivelog delete input;
alter database open;
}
exit
9. Validate the backup taken in the previous step.
run {
restore database validate;
}
10. Also check for the consistency of restoring a tablespace.
run {
restore tablespace users validate;
}
4. Recover only a tablespace from the backup?
1. Connect to rman and check if the latest backup is present.
C:\>rman target backup_admin/backup_admin@prod nocatalog
RMAN>list backup summary;
RMAN>run {
restore database validate;
}
RMAN>run {
restore tablespace test01 validate;
}
Subscribe to:
Posts (Atom)