Changing the logo in E-business suite

STEPS
1. View the file name of your image. Pay special attention to case sensitivity.
e.g. file name IMAGE.gif
2. Copy the image to the $OA_MEDIA directory on ALL middle tiers
3. Edit profile option "Corporate Branding Image for Oracle Applications" at the site level.
Assign the value of your image file
e.g. IMAGE.gif
Make sure the case is exactly as the file is named. If the file name is not entered exactly as it is named the image will be broken and will not appear.
4. Stop / Start Apache to see the change
;;;
Solution Explanation:
---------------------
The Oracle logo on the right hand side of the Oracle Applications
menu is called from the following path.
$ORACLE_HOME/forms60/java/oracle/forms/icons/oracle_logo.gif
So you need to
1.Rename your logo as oracle_logo.gif
2.Put the logo in the following patch
$ORACLE_HOME/forms60/java/oracle/forms/icons/
3.Most importantly ...rgenerate the jar files using adadmin utility
4.Restart the form server again
5.Clear the jinitiator cache
6.Login into the applications again
You should get your logo now

Creating a Physical Standby Database in Windows

-Preparing the Primary Database for Standby Database Creation
_ Creating a Physical Standby Database
_ Verifying the Physical Standby Database

EX:
PRIMARY=PRIME
STANDBY=SECON
Preparing the Primary Database for Standby Database Creation

Enable Forced Logging
SQL> ALTER DATABASE FORCE LOGGING;

This statement may take a considerable amount of time to complete, because it
waits for all unlogged direct write I/O operations to finish.


Enable Archiving and Define a Local Archiving Destination

Set the local archive destination using the following SQL statement:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=’LOCATION=/disk1/oracle/oradata/payroll
2> MANDATORY’ SCOPE=BOTH;

Enable Archiving on Primary.
If your primary database is not already in Archive Log mode, enable the archive log mode:
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database open;
SQL>archive log list;


To check if a password file already exists, run the following command:

If it doesn’t exist, use the following command to create one
select * from v$pwfile_users;
On Windows:
$cd %ORACLE_HOME%\database
$orapwd file=pwdPRIM.ora password=xxxxxxxx force=y
(Note: Replace xxxxxxxxx with the password for the SYS user.)
- On UNIX:
$Cd $ORACLE_HOME/dbs
$Orapwd file=pwdPRIM.ora password=xxxxxxxx force=y
(Note: Replace xxxxxxxxx with your actual password for the SYS user.)

Set Initialization Parameters on a Primary Database
Set Primary Database Initialization Parameters
Create a text initialization parameter file (PFILE) from the server parameter file (SPFILE), to add the new primary role parameters.

SQL>create pfile=’\database\pfilePRIM.ora’ from spfile.

Edit pfilePRIM.ora to add the new primary and standby role parameters

#################################################################
prime.__db_cache_size=96468992
prime.__java_pool_size=4194304
prime.__large_pool_size=4194304
prime.__shared_pool_size=58720256
prime.__streams_pool_size=0
*.audit_file_dest='C:\oracle\product\10.2.0/admin/PRIME/adump'
*.background_dump_dest='C:\oracle\product\10.2.0/admin/PRIME/bdump'
*.compatible='10.2.0.1.0'
*.control_files='C:\oracle\product\10.2.0\oradata\prime\PRIME\control01.ctl','C:\oracle\product\10.2.0\oradata\prime\PRIME\control02.ctl','C:\oracle\product\10.2.0\oradata\prime\PRIME\control03.ctl'
*.core_dump_dest='C:\oracle\product\10.2.0/admin/PRIME/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_file_name_convert='C:\oracle\product\10.2.0\oradata\secon\secon\','C:\oracle\product\10.2.0\oradata\prime\PRIME\'
*.db_name='PRIME'
*.db_recovery_file_dest_size=2147483648
*.db_recovery_file_dest='C:\oracle\product\10.2.0/flash_recovery_area'
*.db_unique_name='PRIME'
*.dg_broker_start=TRUE
*.dispatchers='(PROTOCOL=TCP) (SERVICE=PRIMEXDB)'

*.fal_client='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.15.99)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(INSTANCE_NAME=PRIME)(SERVICE_NAME=PRIME)))'
*.fal_server='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.15.99)(PORT=1522)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=secon)))'
*.job_queue_processes=10
*.local_listener='PRIME'
*.log_archive_config='DG_CONFIG=(PRIME,SECON)'
*.log_archive_dest_1='C:\oracle\product\10.2.0\oradata\Arch_stby_arch\standby\
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIME'
PRIME.log_archive_dest_1='location="C:\oracle\product\10.2.0\oradata\Arch_stby_arch\standby\
"','valid_for=(ONLINE_LOGFILE,ALL_ROLES)'
*.log_archive_dest_2='service="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.15.99)(PORT=1522)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=secon)))"',' LGWR SYNC AFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1 reopen=300 db_unique_name="secon" register net_timeout=180 valid_for=(online_logfile,primary_role)'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
PRIME.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
PRIME.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=2
*.log_archive_min_succeed_dest=1
PRIME.log_archive_trace=0
*.log_file_name_convert='C:\oracle\product\10.2.0\oradata\secon\secon\','C:\oracle\product\10.2.0\oradata\prime\PRIME\'
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=167772160
PRIME.standby_archive_dest=''
*.standby_file_management='auto'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='C:\oracle\product\10.2.0/admin/PRIME/udump'

#######################################################################

Create spfile from pfile, and restart primary database using the new spfile
Data Guard must use SPFILE. Create the SPFILE and restart database
SQL> shutdown immediate;
SQL> startup nomount pfile=’\database\pfilePRIM.ora’;
SQL>create spfile from pfile=’\database\pfilePRIM.ora’;
-- Restart the Primary database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>Startup;
(Verify that the new parameter is set in the file---using the newly created SP-file)
SQL>sho parameter pfile;

Creating a Physical Standby Database

Identify the Primary Database Datafiles
SQL> SELECT NAME FROM V$DATAFILE;
NAME
----------------------------------------------------------------------------
/disk1/oracle/oradata/payroll/system01.dbf
/disk1/oracle/oradata/payroll/undotbs01.dbf
/disk1/oracle/oradata/payroll/cwmlite01.dbf
.
.
.
Make a Copy of the Primary Database
Step 1 Shut down the primary database.
Issue the following SQL*Plus statement to shut down the primary database:
SQL> SHUTDOWN IMMEDIATE;

Step 2 Copy the datafiles from primary to a standby database location.

Copy all the datafiles(.dbf) and redlog

Restart the primary database.

SQL> STARTUP;

Create a Control File for the Standby Database

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS
2> 'C:\oracle\product\10.2.0\oradata\secon\secon\stabdby.ctl';

(Use the same control file to bring up secondary)

Prepare the Initialization Parameter File to be Copied to the Standby Database from Primary
Issue this command from primary
SQL> CREATE PFILE=’ C:\oracle\product\10.2.0\db_1\database\initsecon.ora’ FROM SPFILE;

Set Initialization Parameters on a Physical Standby Database

Modifying Initialization Parameters for a Physical Standby Database

###################################################################
prime.__db_cache_size=96468992
prime.__java_pool_size=4194304
prime.__large_pool_size=4194304
prime.__shared_pool_size=58720256
prime.__streams_pool_size=0
*.audit_file_dest='C:\oracle\product\10.2.0/admin/secon/adump'
*.background_dump_dest='C:\oracle\product\10.2.0/admin/secon/bdump'
*.compatible='10.2.0.1.0'
*.control_files='C:\oracle\product\10.2.0\oradata\secon\secon\STANDBY.ctl'
*.core_dump_dest='C:\oracle\product\10.2.0/admin/secon/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_file_name_convert='C:\oracle\product\10.2.0\oradata\secon\secon\','C:\oracle\product\10.2.0\oradata\prime\PRIME\'
*.db_name='PRIME'
*.db_recovery_file_dest_size=2147483648
*.db_recovery_file_dest='C:\oracle\product\10.2.0/flash_recovery_area'
*.db_unique_name='secon'
*.dg_broker_start=TRUE
*.db_create_file_dest='C:\oracle\product\10.2.0\test'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=PRIMEXDB)'
*.fal_server='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.15.99)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(INSTANCE_NAME=PRIME)(SERVICE_NAME=PRIME)))'
*.fal_client='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.15.99)(PORT=1522)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=secon)))'
*.job_queue_processes=10
*.log_archive_config='DG_CONFIG=(PRIME,SECON)'
#*.log_archive_dest_1='location=C:\oracle\product\10.2.0\oradata\Arch_stby_arch\ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=secon'
*.LOG_ARCHIVE_DEST_1='LOCATION="C:\oracle\product\10.2.0\oradata\Arch_stby_arch"',
'VALID_FOR=(ALL_LOGFILES,ALL_ROLES)',
'DB_UNIQUE_NAME=secon'
#PRIME.log_archive_dest_1='location="C:\oracle\product\10.2.0\oradata\Arch_stby_arch\standby"','valid_for=(ONLINE_LOGFILE,ALL_ROLES)'
*.log_archive_dest_2='service="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.15.99)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=prime)))"',' LGWR SYNC AFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1 reopen=300 db_unique_name="prime" register net_timeout=180 valid_for=(online_logfile,primary_role)'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
PRIME.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
PRIME.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=2
*.log_archive_min_succeed_dest=1
PRIME.log_archive_trace=0
*.log_file_name_convert='C:\oracle\product\10.2.0\oradata\secon\secon\','C:\oracle\product\10.2.0\oradata\prime\PRIME\'
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=167772160
PRIME.standby_archive_dest=''
*.standby_file_management='auto'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='C:\oracle\product\10.2.0/admin/secon/udump'

####################################################################

Create all required directories for dump directories and archived log destination\

EX:
[vmractest2] > mkdir -p /oradisk/app01/oracle/admin/blackowl/adump
{oracle} /oradisk/app01/oracle/product/10gDB/dbs [vmractest2] > mkdir -p /oradisk/app01/oracle/admin/blackowl/bdump
{oracle} /oradisk/app01/oracle/product/10gDB/dbs [vmractest2] > mkdir -p /oradisk/app01/oracle/admin/blackowl/cdump
{oracle} /oradisk/app01/oracle/product/10gDB/dbs [vmractest2] > mkdir -p /oradisk/app01/oracle/admin/blackowl/udump
{oracle} /oradisk/app01/oracle/product/10gDB/dbs [vmractest2] > mkdir -p /oradisk/od01/BLACKOWL/archives/


Configure the listener and tnsnames to support the database on both nodes

Configure listener.ora on both servers to hold entries for both databases
Configure tnsnames.ora on both servers to hold entries for both databases


Start the listener and check tnsping on both nodes to both services
[vmractest1.partnergsm.co.il] > tnsping prime
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-SEP-2007 15:10:00
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/oradisk/app01/oracle/product/10gDB/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =
vmractest1)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =
whiteowl)))
OK (10 msec)
{oracle} /oradisk/app01/oracle/product/10gDB/network/admin
[vmractest1.partnergsm.co.il] > tnsping secon
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-SEP-2007 15:10:09
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/oradisk/app01/oracle/product/10gDB/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =
vmractest2)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =
blackowl)))
OK (10 msec)


Set Up the Environment to Support the Standby Database on the standby node.
Copy the password file from Primary to Standby, sys password must be identical
And rename as it was in standby



Bringing UP the the STANDBY:

Setup the environment variables to point to the Satndby database
ORACLE_HOME=/oradisk/app01/oracle/product/10gDB
ORACLE_SID=secon
Startup nomount the Standby database and generate an spfile
{oracle} /oradisk/app01/oracle/product/10gDB/dbs [vmractest2] > sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 19 16:17:18 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/oradisk/app01/oracle/product/10gDB/dbs/initsecon.ora'
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
SQL> create spfile from pfile='/oradisk/app01/oracle/product/10gDB/dbs/initsecon.ora';
File created.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
Startup mount the Standby database and perform recovery
SQL> startup mount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
The alert log of the standby will show the operations taking place
…
…
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
Wed Sep 19 16:46:26 2007
Attempt to start background Managed Standby Recovery process (blackowl)
MRP0 started with pid=47, OS id=12498
Wed Sep 19 16:46:26 2007
MRP0: Background Managed Standby Recovery process started (blackowl)
Managed Standby Recovery not using Real Time Apply
Clearing online redo logfile 1 /oradisk/od01/BLACKOWL/onlinelog/o1_mf_1_310n215q_.log
Clearing online log 1 of thread 1 sequence number 95
Deleted Oracle managed file /oradisk/od01/BLACKOWL/onlinelog/o1_mf_1_310n215q_.log
Wed Sep 19 16:46:32 2007
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
Wed Sep 19 16:46:33 2007
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /oradisk/od01/BLACKOWL/onlinelog/o1_mf_2_310n21sx_.log
Clearing online log 2 of thread 1 sequence number 96
Deleted Oracle managed file /oradisk/od01/BLACKOWL/onlinelog/o1_mf_2_310n21sx_.log
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 /oradisk/od01/BLACKOWL/onlinelog/o1_mf_3_310n22jj_.log
Clearing online log 3 of thread 1 sequence number 94
Deleted Oracle managed file /oradisk/od01/BLACKOWL/onlinelog/o1_mf_3_310n22jj_.log
Clearing online redo logfile 3 complete
Media Recovery Waiting for thread 1 sequence 96
Start the Primary Database
The alert log of the primary will show how it recognize the standby and start shipping archived logs
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Wed Sep 19 16:01:07 2007
LNS: Standby redo logfile selected for thread 1 sequence 100 for destination
LOG_ARCHIVE_DEST_2
Wed Sep 19 16:01:07 2007
Successfully onlined Undo Tablespace 1.
Wed Sep 19 16:01:07 2007
SMON: enabling tx recovery
Wed Sep 19 16:01:09 2007
Database Characterset is AL32UTF8
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=21, OS id=13864
Wed Sep 19 16:01:12 2007
Completed: ALTER DATABASE OPEN
Wed Sep 19 16:01:13 2007
ARCq: Standby redo logfile selected for thread 1 sequence 99 for destination
LOG_ARCHIVE_DEST_2
Wed Sep 19 16:05:05 2007
Thread 1 advanced to log sequence 101
Current log# 1 seq# 101 mem# 0:
/vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_1_310n215q_.log
Wed Sep 19 16:05:06 2007
LNS: Standby redo logfile selected for thread 1 sequence 101 for destination
LOG_ARCHIVE_DEST_2
Verify the Physical Standby Database Is Performing Properly
Check archived redo log on Standby
SQL> show parameters db_unique_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string blackowl
SQL> l
1* SELECT NAME FROM V$DATABASE
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
96 19-SEP-07 19-SEP-07
97 19-SEP-07 19-SEP-07
98 19-SEP-07 19-SEP-07
99 19-SEP-07 19-SEP-07
100 19-SEP-07 19-SEP-07
Switch logfiles on Primary
SQL> alter system switch logfile;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /vmasmtest/whiteowl/archdest/
Oldest online log sequence 100
Next log sequence to archive 102
Current log sequence 102
SQL> alter system switch logfile;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /vmasmtest/whiteowl/archdest/
Oldest online log sequence 101
Next log sequence to archive 103
Current log sequence 103
Check archived redo log on Standby
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME
---------- -------------- --------------
96 19/09/07 09:35 19/09/07 09:45
97 19/09/07 09:45 19/09/07 15:20
98 19/09/07 15:20 19/09/07 15:48
99 19/09/07 15:48 19/09/07 16:00
100 19/09/07 16:00 19/09/07 16:05
101 19/09/07 16:05 19/09/07 16:08
102 19/09/07 16:08 19/09/07 16:08
7 rows selected.

Formula to calculate approximating size of the SGA(Shared Global Area)

7.X
----
((db_block_buffers * db_block_size) + shared_pool_size + log_buffers) / 0.9



8.0.X
-----
((db_block_buffers*db_block_size) + (shared_pool_size + large_pool_size +
log_buffers) + 1MB

8.1.X
------
((db_block_buffers * db_block_size) + (shared_pool_size + large_pool_size + java_pool_size + log_buffers) + 1MB

9.X
---
DB_CACHE_SIZE + DB_KEEP_CACHE_SIZE + DB_RECYCLE_CACHE_SIZE + DB_nk_CACHE_SIZE
+ SHARED_POOL_SIZE + LARGE_POOL_SIZE + JAVA_POOL_SIZE + LOG_BUFFERS +
+ 1MB


Additional Info
-----------------------
- Redo Buffers in SHOW SGA does not mattch init.ora:log_buffer parameter setting.
- Enforced mininum is set to 4 times thhe maximum database block size.
- Java_pool_size not accounted for in SSHOW SGA or v$sga.
This is a bug that is fixed in 8.1.6.
- Java_pool_size restrictions in 8.1.5..
The default is 20000K.
If specifying in the init.ora, must it must be > 1000K, or will receive
ORA-01078
"failure in processing initialization parameters" error on startup.
- Java_pool_size restrictions in 8.1.6..
The default is 20000K.
This parameter can be set in the init.ora, but the enforced mininum is 32768.

Core Database Question:

What is stored in database redo log files???
1. changes to data made by the INSERT, UPDATE and DELETE DML statements
2. All DDL statements entered into the database

What causes a checkpoint to occur?
1. Shutting down the database with IMMEDIATE option
2. Taking a tablespace offline with the OFFLINE NORMAL option
3. Switching redo log files



UNDO_SUPPRESS_ERRORS parameter is used to suppress errors while attempting to execute manual operations in auto mode

The UNDO_TABLESPACE parameter specifies the UNDO tablespace to be used. This parameter can be set in the initialization file or altered dynamically using the ALTER SYSTEM command.

The UNDO_MANAGEMENT parameter determines the undo mode of the database and must be set in the initialization file.

REORGANIZE, TRUNCATE, and DROP a table
1. Corresponding indexes are also truncated
2. the table- and row- level triggers are not fired
3. All the rows in the table are deleted, and all of the unused space is released.


ALTER TABLE MOVE command
1. To reorganize a table to eliminate row migration
2. To move a table from one tablespace to another

Which of the following statements about indexes are true?
• Normal index maintenance is performed when the corresponding columns are updated in the underlying table.
• Indexes can be partitioned even if the table is not partitioned.
• Function-based indexes can be created as either B-tree or bitmap indexes.
• Indexes can be created using up to 32 columns, but the combined length of all the columns cannot exceed one third of the space in an index block.

CREATING VARIOUS TYPES OF INDEXES
• Indexes speed up query performance and slow down DML operations.
• If you use the NOLOGGING clause when creating large indexes, then the performance of the database while the index is being created is much better.

When creating an index, you should use a low PCT FREE value for indexes on columns that are monotonically increasing, such as system-generated invoice numbers

ALTER TABLE orders MODIFY CONSTRAINT order_pk PRIMARY KEY (order_id) DEFERERRABLE ENABLE NOVALIDATE command



ENABLE---causes new data to be checked
NOVALIDATE---- skips the checking of existing data
DEFERRABLE---- forces the use of a nonunique index

Profiles can be disabled and enabled, they are assigned to users with the CREATE or ALTER USER commands, and they can inherit values only from the DEFAULT profile.

DBMS_OUTPUT package is an Oracle-supplied PL/SQL object used to aid in the debugging of user-developed PL/SQL code objects.
It cannot be used to change the language-dependent behavior settings of be database.

Obtain control file information:
1. V$CONTROLFILE: Lists the name and status of all control files associated with the instance
SELECT name FROM V$CONTROLFILE;

2. V$PARAMETER: Lists status and location of all parameters
SELECT name, value FROM V$PARAMETER
WHERE name= ‘control_files’;

3. V$CONTROLFILE_RECORD_SECTION: Provides information abt the control file record sections
SELECT type, record_size, records_total, records_used
FROM v$controlfile_record_section
WHERE type=’controlfile’;
3. SHOW PARAMETER control_files: Lists the name, status and location of the control files.


Describe the logical structure of the database
Locally managed tablespaces have which of the following advantages over dictionary-managed tablespaces??
1. Recursive space management operations are avoided.
2. Adjacent free space is automatically tracked, so you do not need to coalesce free extents.
3. Contention on data dictionary tables is reduced, because locally managed tablespaces do not record free space there.

A tablespace can belong to only one database at a time and can consist of one or more data files.

Change the status of tablespaces
To make a tablespace read only
1. The tablespace must not be of the temporary type.
2. The tablespace must not be in ACTIVE backup status
3. The tablespace must not contain any active rollback segments.

Difference between read only and offline tablespaces is that the segments of a read only tablespace are available for read operations and can be dropped from the database.

Change the storage settings of tablespaces

To resize a tablespace
1. Use the ALTER DATABASE cmd to change the size of the datafiles used by a tablespace.
2. Use the ALTER DATABASE cmd to turn the autoextend feature on for each of the datafiles within a tablespace.

Situation LGWR performs sequential writes from the redo log buffer
1. When redo log buffer is one-third full.
2. When a transaction is committed.
3. Before DBWn writes modified blocks in the database buffer cache to the datafiles


Monitoring the Use of Diagnostic Files
The ALTER SESSION SET SQL_TRACE = TRUE command causes the server to write a trace file for the session that issued the statement. The trace file is written to the directory specified by the USER_DUMP_DEST parameter.


To let all DML operation finish and then shut down the database, you use the SHUTDOWN TRANSACTIONAL command.

The correct answer is alert log: this is where the database's day-to-day activities are tracked

The BACKGROUND_DUMP_DEST parameter controls where the alert log and background trace file are written.

Prerequisites Necessary for Database Creation
ORAPWD this is the utility you use to create a password file for an Oracle database

SYSDBA privileges are required to create a database. These are granted either using operating system authentication or password file authentication.

Create a Database Using Oracle Database Configuration Assistant
Benefits of using templates
1. Templates allow database options to be changed at database creation
2. Templates can be shared
3. Templates save time when you create a database

The ORACLE_SID operating system environment variable uniquely identifies the Oracle instance on the server.

The Database Configuration Assistant is Oracle's GUI tool for creating a database.

Create a Database Manually
The TIME_ZONE argument of the CREATE DATABASE statement sets the time zone for the database

MAXLOGFILES the maximum number of log file groups can be changed only by re-creating the control file.

DEFAULT TEMPORARY TABLESPACE: this argument of the CREATE DATABASE statement creates a default tablespace for sort operations for users that are not assigned a temporary tablespace.


Explain the Uses of Control File
The control file is opened and read when the database enters the Mount state, and the control file remains open until the database is dismounted.






Allocate Space for Temporary Segments
Tempfiles:
1. Tempfiles are always set to the NOLOGGING mode
2. You cannot make a tempfile read only.
3. You cannot rename a tempfile
4. You cannot create a tempfile with the ALTER DATABASE command.

List the Keywords That Control Block Space Usage
Segments created in a tablespace with automatic space management
1. The tablespace must have locally managed extents.
2. The PCTUSED and FREELISTS parameters are ignored.

Obtain Information about Storage Structures from the Data Dictionary
All the space in a data file (excluding the header block) must be accounted for in either the DBA_FREE_SPACE or DBA_EXTENTS view.

The DBA_FREE_SPACE table shows the amount of free space available in each tablespace

Implement Automatic Undo Management
Multiple undo tablespaces can exist, but only one at a time can be active per instance. Undo tablespaces can be dropped: they are not required for Oracle9i, because you can still use rollback segments. The system can switch to another undo tablespace only when a DBA issues the proper command.

1. UNDO_MANAGEMENT parameter determines the undo mode of the database and must be set in the initialization parameter file;
2. UNDO_TABLESPACE parameter specifies the UNDO tablespace to be used, and it can be set in the initialization files or altered dynamically using the ALTER SYSTEM command;
3. UNDO_SUPPRESS_ERRORS parameter is used to suppress errors while attempting to execute manual operations in Auto mode.

Implement Data Integrity Constraints
1. Constraints enforce rules even when application code is bypassed with ad hoc tools.
2. Constraints are stored in a central repository.
3. Constraints are faster than application code

Three primary ways in which data integrity can be maintained within a database
Application code, database triggers, and declarative integrity constraints

Manage Passwords Using Profiles
Which views do you select from to see the password limit settings currently enforced for a particular user?
DBA_USERS and DBA_PROFILES

The RESOURCE_LIMIT parameter can be used to enable and disabled kernel limits in profiles; password limits are always enforced.

Administer Profiles
Each profile can be assigned to one or more users. However, each user must be assigned one and only one profile. The DEFAULT profile is assigned to all users initially until another profile is explicitly assigned.

Profiles can be disabled and enabled, they are assigned to users with the CREATE or ALTER USER commands, and they can inherit values only from the DEFAULT profile

Control Use of Resources Using Profiles

Profiles allow database administrators to prevent users from "hogging" resources, by configuring the system so that the user's session is killed when SESSION_IDLE_TIME is exceeded.
When any session resource limit is exceeded, the user receives an error message and is disconnected.

Create New Database Users
No quota should be assigned to the TEMP tablespace. The database kernel internally manages quotas on the designated temporary tablespace that is assigned to a user.

A user created with the IDENTIFIED EXTERNALLY clause can use the same username for both operating system and database access.

Alter and Drop Existing Database Users
If a user is altered to have 0 quota on a particular tablespace, then that user loses access to all of the objects he or she owns which are stored in that tablespace [FALSE]
Such users maintains access to any remaining free space in any objects they own that are currently stored in that tablespace. They cannot create or extend any new objects which require storage space in that tablespace.

The CASCADE clause is used with the DROP USER command in order to remove any database objects currently owned by the user being dropped.

Grant and Revoke Privileges
You must have the References object privilege on any table in another schema to which you want to create a foreign key.

Unlike a system privilege, which remains in force for users who were granted the privilege by a subsequent grantor, object privileges are revoked in a cascade, and therefore removed from subsequent grantees.

Create and Modify Roles
Which of the following statements about roles are true?
1. User privileges obtained through a role are set at login or with the SET ROLE command
2. A role can be assigned to another role.

A role can be granted to a user or another role. Grouping application roles into job function roles is an common way to use roles to support a large number of users.

Control Availability of Roles
An application role is created with the USING keyword and a PL/SQL object to be used for the role authentication.

The GRANT command is used to assign a role to a user. The ALTER ROLE command is used to maintain the contents of a role.

Managing Tablespaces and data files:

Tablespaces: Can belong to only one database at a time
Consist of one or more data files
Are further divided into logical units of storage
Data files: Can belong to only one tablespace and one database
Is a repository for schema object data

SYSTEM tablespace
1. Created with the database
2. Required in all databases
3. Contains the data dictionary, including the stored program units
4. Contains the SYSTEM undo segment
5. Should not contain user data, although it is allowed.

Non-SYSTEM tablespaces:
1. Enables more flexibility in database administration
2. Separate undo, temporary, application data, and application index segments
3. Separate data by backup requirements
4. separate dynamic and static data
5. Control the amount of space allocated 2 the users.

Creating tablespace:

CREATE TABLESPACE tablespace
[DATAFILE clause]
[MINIMUN EXTENT integer [K|M]
[BLOCK SIZE integer [K]]
[LOGGING|NOLOGGING]--- All tables, indexes, and partitions within the tablespace have the changes written to online redo log files.
[DEFAULT storage_clause]
[ONLINE|OFFLINE]
[PERMANENT|TEMPORARY]
[extent_management clause]
[segment_management clause]

Locally managed Tablespaces:
Reduced contention on data dictionary tables
No undo generated when space allocation or deallocation occurs
No coalescing required

E.g. CREATE TABLESPACE userdata
DATAFILE ‘/u01/oradata/userdata01.dbf’ SIZE 500M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

Extent_management_clause:

[EXTENT MANAGEMENT [DICTIONARY | LOCAL
[AUTOALLOCATE | UNIFORM [SIZE integer [K|M]]]]

DICTIONARY: tbspc is managed using data dictionary tables
LOCAL: locally managed via bitmaps. If you specify LOCAL you cannot specify DEFAULT storage_clause, MINIMUM EXTENT, or TEMPORARY.
AUTOALLOCATE: tbspc is system managed. Users cannot specify an extent size.
UNIFORM: tbspc is managed with uniform extents of SIZE bytes. Default size 1 MB.

Advantages of locally Managed Tablespaces:
1. Recursive space management operations are avoided.
2. Adjacent free space is automatically tracked, so you do not need to coalesce free extents.
3. Contention on data dictionary tables is reduced, because locally managed tablespaces do not record free space there.


Dictionary- Managed Tablespaces
Extents are managed in the data dictionary
Each segment stored in the tablespace can have a different storage clause.
Coalescing is required.

E.g. CREATE TABLESPACE userdata
DATAFILE ‘/u01/oradata/userdata01.dbf’
SIZE 500M EXTENT MANAGEMENT DICTIONARY
DEFAULT STORAGE
(Initial 1M NEXT 1M PCTINCREASE 0);

Storage is more flexible than locally managed tablespaces but much less efficient.


Undo Tablespaces

Used to store undo segments
Cannot contain any other objects
Extents are locally managed
Can only use the DATAFILE and EXTENT MANAGEMENT clauses

E.g. CREATE UNDO TABLESPACE undo1
DATAFILE ‘/u01/oradata/undo01.dbf’ SIZE 40M;

An undo tbspc cannot be dropped if it is being used by any instance or contains any undo information to recover transactions.

Temporary Tablespaces

Used for sort operations
Can be shared by multiple users
Cannot contain any permanent objects
Locally managed extents recommended.

E.g. CREATE TEMPORARY TABLESPACE temp
TEMPFILE ‘u01/oradata/temp01.dbf’ SIZE 20M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4M;

Note: Nonstandard block size cannot be specified when creating temporary tbspc.


Tempfiles:

Always set to the NOLOGGING mode.
Cannot make a tempfile read-only
Cannot rename a tempfile
Cannot create a tempfile with the ALTER DATABASE command
Tempfiles are required for read-only databases
Media recovery does not recover tempfiles

To optimize the performance of a sort area in a temporary tbspc, set the UNIFORM SIZE to be a multiple of the parameter SORT_SIZE_SIZE.

Default Temporary Tablespace

Specifies a database-wide default temporary tablespace
Eliminates using SYSTEM tbspc for storing temporary data
Can be created using:
CRETAE DATABASE DBA01
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE ‘/$HOME/ORADATA/u03/temp01.dbf’ SIZE 4M
Or
ALTER DATABASE
DEFAULT TEMPORARY TABLESPACE default_temp2;

To find the default temp tbspc for the database query DATABASE_PROPERTIES
SELECT * FROM DATABASE_PROPERTIES;

Restrictions on default temporary tablespace:

Default tbspc cannot be:
Dropped until after a new default is made available
Taken offline
Altered to a permanent tablespace

Read Only Tablespaces
ALTER TABLESPACE userdata READ ONLY;

Causes a checkpoint
Data available only for red operations
Objects can be dropped from tbspc (tables and indexes because these commands affect only the data dictionary

To make a tablespace read only
1. The tablespace must not be of the temporary type.
2. The tablespace must not be in ACTIVE backup status
3. The tablespace must not contain any active rollback segments.


Taking a tablespace offline

Reasons:
Make a portion of the database unavailable, while allowing normal access to the remainder of the database
Perform an offline tablespace backed up (although a tbspc can be backed up while online and in use)
Recover a tablespace or data file while the database is open
Move a data file while the database is open

Offline status of a tablespace
Tbspc offline, Oracle does not permit any subsequent SQL statements to reference objects contained in that tbspc
When tbspc goes offline or comes back online, the event is recorded in the data dictionary and in the control file.

Taking Tablespace Offline
ALTER TABLSPACE tablespace
{ONLINE | OFFLINE [NORMAL | TEMPORARY | IMMEDIATE | FOR RECOVERY]}

NORMAL: flushes all blocks in all data files in the tbspc out of SGA
TEMPORARY: performs a checkpoint for all online datafiles. Any offline file may require media recovery
IMMEDIATE: does not ensure that the tbspc files are available, does not perform a checkpoint, and must perform media recovery before bringing it back online.
FOR RECOVERY: point-in-time recovery

Changing storage Settings

Using ALTER TABLESPACE command to change storage setting
E.g. ALTER TABLESPACE userdata MINIMUN EXTENT 2M;

ALTER TABLESPACE userdata
DEFAULT STORAGE (INITIAL 2M NEXT 2M MAXEXTENTS 999);

Resizing a Tablespace

1. Enabling Automatic Extension of data files
Can be resized automatically with the following commands:
-CREATE DATABASE
-CREATE TABLESPACE
-ALTER TABLESPACE … ADD DATAFILE

E.g. CREATE TABLESPACE user_data
DATAFILE ‘/u01/oradata/userdata01.dbf’ SIZE 200M
AUTOEXTEND ON NEXT 10M MAXSIZE 500M;

Query the DBA_DATA_FILES view to determine whether AUTOEXTEND is enabled.
SELECT tablespace_name, file_name, autoextensible
FROM dba_data_files;


2. Manually Resizing a Data File
Adds more space without adding more data files
Reclaims unused space in database
ALTER DATABASE
DATAFILE ‘u03/oradata/userdata02.dbf’
RESIZE 200M;

3. Adding Data Files to a tablespace
ALTER TABLESPACE user_data
ADD DATAFILE ‘u01/oradata/userdata03.dbf’
SIZE 200M;

Moving Data Files

• ALTER TABLESPACE
[Tbspc must be offline
Target data files must exist]

Steps
Take the tbspc offline
Use an OS command to move or copy the files
Execute ALTER TABLESPACE userdata RENAME
DATAFILE ‘ u01/oradata/userdata01.dbf’
TO ‘u02/oradata/userdata01.dbf’;
Bring tbspc online
Use OS command to delete the file if necessary

• ALTER DATABASE
[Database must be mounted
Target data file must exist]

Steps
Shut down the database
Use OS command to move the file
Mount the database
Execute ALTER TABLESPACE userdata RENAME
DATAFILE ‘ u01/oradata/userdata01.dbf’
TO ‘u03/oradata/userdata01.dbf’;
Open the database


Dropping Tablespaces

You cannot drop a tbspc if it
- is the SYSTEM tbspc
- has active segments
INCLUDING CONTENTS drops the segments
INCLUDING CONTENTS AND DATAFILES deletes data files
CASCADE CONSTRAINTS drops all referential integrity constraints.

Note: tbspc dropped, only file pointers in the control file of the associated database is dropped.
Take tbspc offline before dropping it to ensure that no transactions access any of the segments in the tbspc.

Obtaining Tablespace Information
Tbspc info: DBA_TABLESPACE
V$TABLESPACE

Datafile info: DBA_DATA_FILES
V$DATAFILE

Temp file info: DBA_TEMP_FILES
V$TEMPFILE

Patching related questions

Patching related questions

Prereq not met while patching.

In this case the patch ultimately fails.

Solution:
========
1) Immediately take a backup of restart files which contains the current patch/failed patch information.
2) Take a backup of AD_DEFFERED_JOBS and FND_INSTALL_PROCESSES tables if they exist, and drop them.
3) Check which pre-requisite you haven't applied.
4) Download and apply the pre-requisite.
5) Once it is successfully applied, restore the failed patch restart files and the tables which are backed up.
6) Run adpatch and continue from the previous session.

NOTE:-This is usually followed if a large patch fails after running for a long time. Say if a patch fails immediately after applying, you can go for reapplying the patch freshly after all the prerequisites are met.

Apply only the database portion of a unified Driver
$ adpatch options=nocopyportion,nogenerateportion

At the prompt for the driver name, specify the unified (u) driver. AutoPatch runs the driver, applying only the database portion of the patch.

Enabling Password Validation
adpatch options=validate

AD_BUGS holds information about the various Oracle Applications bugs whose fixes have been applied (ie. patched) in the Oracle Applications installation.

AD_APPLIED_PATCHES (ie OAM) holds information about the "distinct" Oracle Application patches that have been applied. If 2 patches happen to have the same name but are different in content (eg. "merged" patches), then they are considered distinct and this table will therefore hold 2 records

the patches on OAM take the values from ad_applied_patches, while it stores the values of bug in the ad_bugs.

REAPPLY PATCH:
AD_BUGS--will give you information about the bug fixes in the system
(AD_PATCH_RUN_BUGS will also do the same)

AD_APPLIED_PATCHES--will give you information about the patches appplied to the system.
Say if you have a ROLLUP patch or a patchset, which will fix many small bug fixes, AD_APPLIED_PATCHES will have only the patch number,
but
AD_BUGS(AD_PATCH_RUN_BUGS) will have all the bug fixes by that particular patch.

Use AD_PATCH_RUN_BUGS.

STATUS OF A PATCH:

ps -ef | grep adpatch and if it returns, the patch is still running
adadmin and adctrl

Applying a main patch, which fails for a pre-req patch, and now you want to apply the pre-req patch to resolve the error but DONOT want to skip the main patch??

1. Using the adctrl utility, shutdown the workers of the main patch.
a. adctrl
b. Select option 3 "Tell worker to shutdown/quit"

2. Backup the FND_INSTALL_PROCESSES table which is owned by the APPLSYS schema
a. sqlplus applsys/
b. create table fnd_Install_processes_back
as select * from fnd_Install_processes;
c. The 2 tables should have the same number of records.
select count(*) from fnd_Install_processes_back;
select count(*) from fnd_Install_processes;

3. Backup the AD_DEFERRED_JOBS table.
a. sqlplus applsys/
b. create table AD_DEFERRED_JOBS_back
as select * from AD_DEFERRED_JOBS;
c. The 2 tables should have the same number of records.
select count(*) from AD_DEFERRED_JOBS_back;
select count(*) from AD_DEFERRED_JOBS;

4. Backup the .rf9 files located in $APPL_TOP/admin//restart directory.
At this point, the adpatch session should have ended and the cursor should
be back at the Unix prompt.
a. cd $APPL_TOP/admin/
b. mv restart restart_back
c. mkdir restart

5. Drop the FND_INSTALL_PROCESSES table and the AD_DEFERRED_JOBS table.
a. sqlplus applsys/
b. drop table FND_INSTALL_PROCESSES;
c. drop table AD_DEFERRED_JOBS;

6. Apply the new patch.- The pre-req patch which was missed.

7. Restore the .rf9 files located in $APPL_TOP/admin//restart_back
directory.
a. cd $APPL_TOP/admin/
b. mv restart restart_
c. mv restart_back restart

8. Restore the FND_INSTALL_PROCESSES table which is owned by the APPLSYS
schema.
a. sqlplus applsys/
b. create table fnd_Install_processes
as select * from fnd_Install_processes_back;
c. The 2 tables should have the same number of records.
select count(*) from fnd_Install_processes;
select count(*) from fnd_Install_processes_back;

9. Restore the AD_DEFERRED_JOBS table.
a. sqlplus applsys/
b. create table AD_DEFERRED_JOBS
as select * from AD_DEFERRED_JOBS_back;
c. The 2 tables should have the same number of records.
select count(*) from AD_DEFERRED_JOBS_back;
select count(*) from AD_DEFERRED_JOBS;

10. Re-create synonyms
a. sqlplus apps/apps
b. create synonym AD_DEFERRED_JOBS for APPLSYS.AD_DEFERRED_JOBS;
c. create synonym FND_INSTALL_PROCESSES FOR APPLSYS.FND_INSTALL_PROCESSES;

11. Start adpatch, it will resume where it stopped previously.

Patching Doubt :

c and g drivers BOTH will go on all those nodes which have APPL_TOP present on them.
So now it depends on our configuration of the "web" node.
If our Web node is a pure web server with only IAS_ORACLE_HOME on it then we donot apply "Apps" patches(c,d or g) on them,we will apply only "oracle" patches there.So neither c or g go should go on a pure Web node.

And if our Web node is also handling a bit of forms(which is normally the case) i.e. if it contains any plls etc.. then BOTH c & g drivers will be applied.


QUESTIONS:
1. What is wdbsvr.app file used for? What's full path of this file? What's significance of this file ?

Database connection information, IAS_ORACLE_HOME/Apache/modplsql/conf

2. adident utility is used for what ?
to find file version

3. How can you license a product after installation ?
adlicmgr.sh or via OAM (Oracle Application Manager)

4. What are main configuration files in Web Server (Apache) ?
httpd.conf, jserv.conf, jserv.properties, zone.properties....

5. How to check if Apps 11i System is Autoconfig enabled ?
Check in any configuration file and if on top of file it is written that created by autoconfig

6. How to check if Oracle Apps 11i System is Rapid Clone enabled ?
If rapid clone patches are applied

7. What is the difference between apps listener & web listener ?
Apps listener are FNDFS and FNDSM listener and web listener is for web requests (HTTP/HTTPS)

8. What is difference between adpatch & opatch ? Can you use both adpatch & opatch in Apps ?
adpatch is used for applying apps patch
opatch is used for applying database patch

Yes both are used in apps , adpatch for apps patch and opatch for database patch

Significance of _ALL tables
_all tables store data for all organizations if you have multi-org enabled

Patch Metadata files

These contain a list of files included in the patch and the relationships between this patch and other patches. The metadata files are normally called b.ldt and/or f.ldt. The information in the patch metadata files are used by the AD utilities to determine prerequisite patches, analyze the impact of the patch on the system and compare versions of files in the patch with those of files in the system

Oracle Application-- Maintainance mode

Here is the main reason for maintenance mode and how it minimizes downtime:

There is a new Workflow feature called Cache Invalidation, which is built on the Business Events System. This feature generates Java cache invalidation messages for changes to seed data and, as a result, hampers the performance of FNDLOAD. As some patch sessions update a significant amount of seed data and also require running FNDLOAD in the background,
Having this feature enabled negatively impacts the performance of Auto Patch sessions. Maintenance mode shuts down the Workflow Business Events System.

GUEST, APPLSYS, APPLSYSPUB, APPS users:

GUEST is like a Dummy Account. By default it has ORACLE as password.
GUEST account used by JDBC Drivers and Oracle Self Service Web Applications like istores, irecrutiment, iprocurement, ipayables, ireceivables etc to make initial Connection.

APPLSUSPUB Account is used at Initital Client Connectiviey to Oracle Applications.
The Name tells that it as a PUBLIC Account.
When User Login in to Applications using his username and password. These credentials (Username and Passwords) are validated by login into APPLSYSPUB Account. APPLSYSPUB account is Gateway User ID / GWYUID.

APPLSYS is same as other oracle users like AP, AR, GL etc whose hold their set of tables, views etc. Same way APPLSYS Account holds its set of tables like FND_USER and FND_APPLICATION, AD_APPLIED_PATCHES etc.



APPS is central Schema which holds synonyms for all other Users Database Objects.
One main thing is APPLSYS and APPS should have same password. Why these contains same password.

Apps is a universal schema has synonyms to all base product tables and sequences. This also has code objects for all products (triggers, views, packages, synonyms etc.).

Applsys schema has applications technology layer products like FND and AD etc.

Both need to have same password because when you sign on to apps, initially it connects to a public schema called APPLSYSPUB. This validates AOL name and password that we enter (operations/welcome). Once this is verified we select responsibility, this is validated by APPLSYS schema and then it connects to apps schema.

Since it uses both applsys and apps during signon process this expects both the password to be identical. Try changing apps password to something else and try to login, the validation at the last stage would fail. This would result in failure of application login

Printer Setup for Oracle Apps E-Business suite

 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

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.

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;

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.

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;
}