Database startup Options

Database startup Options:

DBA's responsibility is to start and shutdown of oracle instance/database. It is very important to be aware of all the options used and use the appropriate option.

To start up or shutdown oracle database, appropriate privileges are needed.

Two special connection accounts / authorizations are available for startup and shutdown:

• SYSDBA: Any database task can be performed with SYSDBA authorization.
• SYSOPER: The SYSOPER authorization is a less powerful authorization that allows startup and shutdown abilities but restricts other administrative tasks, such as access to non-administrative schema objects.

These authorizations are managed either through a passwords file or via operating-system control. Only the SYS schema can connect to the database with the SYSDBA authorization on database installation. SYS authorization and the SYSOPER authorization can be provided to give others the ability to perform these tasks other than SYS user.

On Oracle database startup, the memory structures and background processes are initialized and started for users to communicate with oracle database.

On oracle database startup, it goes through Series of steps to ensure database consistency and a database passes through three modes as:

•  NOMOUNT
•   MOUNT
•  OPEN

Now let me discuss with these modes and the series of events happens on each mode before database is available for normal use.

STARTUP NOMOUNT:

STARTUP NOMOUNT starts the instance without mounting the database. On starting the database on this mode, Parameter file is read file is read and background processes and memory structures are initiated. But the processes are not attached or communicating with the disk structures of the database. Database is not available for use and we can perform only certain tasks.

One of the most common tasks is running scripts to create the underlying database.

The next mode is the mount and some of the times because of some reasons, it hinders database to go to next mode i.e. mount mode. This happens when oracle has a problem in reading the control file structures, which has the important information to continue with startup process. If these structures are damaged or not available, need to solve the problem before database startup process continue.

STARTUP MOUNT:

The STARTUP MOUNT option attaches and interacts with the database structures. On this state Oracle retrieves the information from the control file s that it uses to locate and attach to main database structures.

Many administrative tasks can be performed while database is in mount mode like recovery. You can also physically change file locations or place the database in archive log mode.

STARTUP OPEN:

The STARTUP OPEN option is the default startup mode if no mode is specified on the STARTUP command line. STARTUP OPEN performs all the steps of the STARTUP NOMOUNT and STARTUP MOUNT options. This option makes the database available to all users.

STARTUP FORCE:

In case of difficulty in normal database startup, we use startup force option. Some of the scenarios where this option is used like if a database server lost power and the database stopped abruptly, it can leave the database in a state in which a STARTUP FORCE startup is necessary. What is also different about STARTUP FORCE is that it can be issued no matter what mode the database is in. STARTUP FORCE does a shutdown abort and then restarts the database.

STARTUP RESTRICT:

The STARTUP RESTRICT option starts up the database and places it in OPEN mode, but gives access only to users who have the RESTRICTED SESSION privilege. When you want to perform maintenance on the database while it is open but ensure that users cannot connect and perform work on the database and we use RESTRICTED option.

You can disable the restricted session, SQL>ALTER SYSTEM DISABLE RESTRICTED SESSION; , so everyone can connect to the database.

STARTUP UPGRADE:

When database started with upgrade option, it starts with OPEN UPGRADE mode and sets the initialization parameters to specific values. These values are required to run the database upgrade scripts. This option is only used while we start the database for first time with new version of oracle database server.

Once the upgrade completes, the database should be shut down and restarted normally.

Invalid Objects and Compilation

Recompiling Invalid Objects:

Schema objects can be invalidated by various operations such as upgrades, patches and DDL changes. These changes will not cause compilation failures but will revalidate by automatic recompilation. This can take long time to complete, For this reason it makes sense to compile invalid objects in advance of user calls.

For compiling invalid object we have various methods:

1. Using utlrp.sql at $ORACLE_HOME/RDBMS/ADMIN
2.  Using ADADMIN utility
3. Manually compile

It is always a good idea to know the count of invalid objects before we compile it and again verify after compilation.

We can get count of invalid objects from dba_objects table as below:

SQL> select count(*) from dba_objects where status='INVALID';

UTLRP.SQL:

we can execute utlrp.sql from $ORACLE_HOME/RDBMS/ADMIN and this script will call utlprp.sql from the same location and will compile invalid objects.

ADADMIN Utility:

We can also compile the invalid objects with adadmin utility with below steps:

• Login as application tier user (APPLMGR in my case)
• Set environment variable (under APPL_TOP/APPS[sid]_[hostname].env)
• adadmin  and Answer the list of question and will get the adadmin menu
• option 3 compile/reload Applications Database Entities menu
• option 1 Compile Apps Schema

Through adadmin utility adcompsc.pls script is called and it will compile all schema in order of sys,system and apps etc. This script is located at  $AD_TOP/sql

We can also compile single schema with adcompsc.pls as below:

sqlplus @adcompsc.pls apps [apps_password]

Manual Compilation of Invalid Objects:

We can compile any package, procedure, function, trigger and view etc. as given below:

SQL>ALTER PACKAGE my_package COMPILE;

SQL>ALTER PACKAGE my_package COMPILE BODY;

SQL>ALTER PROCEDURE my_procedure COMPILE;

SQL>ALTER FUNCTION my_function COMPILE;

SQL>ALTER TRIGGER my_trigger COMPILE;

SQL>ALTER VIEW my_view COMPILE;

Tablespaces

Tablespace types and overview:

Before I start with Tableespace, We must have fair idea of data. Tablespace is the logical memory to hold the information about physical memory.
Data can be classified into many types. While we install database , we have data related to system so, those data are called System data and can be taken into consideration for System tablespace.

While we do any operation like join, orderby etc. , for this process to succeed we need some temporary space to happen this process and temp space is used and considered as part of temp tablespace.

Any operation like alter, create, we need to hold these information for flashback and recovery purpose. So these data are stored into undo section and considered into Undo tablespace. By default undo retention is 900 second. We can make it more if we need the data for more that this duration.

Sysaux tablespace is the auxiliary tablespace of System tablespace. While we make some additional feature then the system related data will come into the Sysaux tablespace.

TEMPORARY TABLESPACE:

Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. For example, if you join two large tables, and Oracle cannot do the sort in memory (see SORT_AREA_SIZE initialisation parameter), space will be allocated in a temporary tablespace for doing the sort operation. Other SQL operations that might require disk sorting are: CREATE INDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc.

The DBA should assign a temporary tablespace to each user in the database to prevent them from allocating sort space in the SYSTEM tablespace. This can be done with one of the following commands:

SQL> CREATE USER scott DEFAULT TABLESPACE data TEMPORARY TABLESPACE temp;
SQL> ALTER USER scott TEMPORARY TABLESPACE temp;

Note: A temporary tablespace cannot contain permanent objects and therefore doesn't need to be backed up.

TEMPFILEs are not recorded in the database's control file. This implies that one can just recreate them whenever you restore the database, or after deleting them by accident.

One cannot remove datafiles from a tablespace until you drop the entire tablespace. However, one can remove a TEMPFILE from a database. Look at his example:

SQL> ALTER DATABASE TEMPFILE '/oradata/temp02.dbf' DROP INCLUDING DATAFILES;

How does one create Temporary Tablespaces?

Oracle provides various ways of creating TEMPORARY tablespaces (mainly to provide backward compatibility). One should use the most recent method available:

- Prior to Oracle 7.3 - CREATE TABLESPACE temp DATAFILE ...;
- Oracle 7.3 & 8.0 - CREATE TABLESPACE temp DATAFILE ... TEMPORARY;
- Oracle 8i and above - CREATE TEMPORARY TABLESPACE temp TEMPFILE ...;

SQL> CREATE TEMPORARY TABLESPACE temp
      TEMPFILE '/oradata/mytemp_01.tmp' SIZE 20M
      EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

Monitoring Temporary Tablespaces and Sorting:

Unlike datafiles, tempfiles are not listed in V$DATAFILE and DBA_DATA_FILES. Use V$TEMPFILE and DBA_TEMP_FILES instead.
One can monitor temporary segments from V$SORT_SEGMENT and V$SORT_USAGE
DBA_FREE_SPACE does not record free space for temporary tablespaces. Use V$TEMP_SPACE_HEADER instead:

Oracle Application and Database cloning

Oracle Application and Database Cloning:

Prepare the Source system for cloning

1. Prepare the source system for cloning by running adpreclone

The adpreclone.pl script prepares the source system to be cloned by collecting information about the source system, creates a cloning stage area and generates templates and drivers from existing files that contain source‐specific hard‐coded values.

Preclone will do the following:

• Convert Symbolic links

• Create templates : Any files under $ORACLE_HOME that contain system specific

information will be replicated and converted into a template located in $ORACLE_HOME/appsutil/template

• Create driver(s) : The driver file is created in $ORACLE_HOME/appsutil/driver

Prepare the source system database tier for cloning

Log on to the source system as the ORACLE user and run the following commands:

$ cd [RDBMS ORACLE_HOME]/appsutil/scripts/[CONTEXT_NAME]

$ perl adpreclone.pl dbTier

Prepare the source system application tier for cloning.

Log on to the source system as APPLMGR user and run the following commands:

$ cd [INST_TOP]/admin/scripts

$ perl adpreclone.pl appsTier

2. Put the source database in Begin Backup mode and copy the Database files.

a) Log in to database as sysdba user

$ sqlplus / as sysdba

SQL> alter database begin backup;

Copy all the datafiles in a Backup directory and zip it.

b) Backup the control file to trace

SQL> alter database backup control file to trace;

c)End the Begin Backup mode.

SQL> alter database end backup;

3.Copy the Application tier file system files

Log on to the source system application tier nodes as the APPLMGR user Copy and zip the following application tier directories from the source system to a Backup directory.

• [APPL_TOP]

• [COMMON_TOP]

• Applications Technology Stack

[OracleAS Tools ORACLE_HOME]

[OracleAS Web IAS_ORACLE_HOME]

4. Copy the Database and Application files from the Backup directory to the Target System location.

Zip the datafiles, backup control file, application file system and copy them from the source Backup directory to mount points in the Target System.

Cloning the TARGET system: 

Database Cloning:

We have to run dbtechstack for cloning the oracle home. dbtechstack will configure the target database ORACLE_HOME but will not create control files or open the database. We need to use dbtechstack when we clone the database using hot backup as we will need to create/open the database manually.

1. From $ORACLE_HOME/appsutil/clone/bin run:

$ perl adcfgclone.pl dbTechStack

Note: Before running adcfgclone.pl we have to create a new oraInventory(Path of

oraInventory located in /var/opt/oracle/oraInst.loc).

We have to create a new oraInventory folder :

$ mkdir oraInventory

Modify Oratab.

adcfgclone.pl will be asking some parameters for creating a new Database context file(we have to provide the suitable parameters necessary with respect to environments)

After completion of adcfgclone.pl we have to set the .profile environment correctly.

ORACLE_SID=TEST

Export ORACLE_SID

2. Creation of control files:

Every Oracle Database has a control file, which is a small binary file that records the physical structure of the database. The control file includes:

• The database name

• Names and locations of associated datafiles and redo log files

• The timestamp of the database creation

• The current log sequence number

• Checkpoint information

3. Before creating the control files all the copied temp datafiles should be deleted.

Now we have to open the database as the target system Change the db_name in the init file to the target database name (ex.TEST)We also have to re‐create the control file as the Target instance with the header of the control file as follows:

CREATE CONTROLFILE SET DATABASE ʺTESTʺ RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 5

MAXDATAFILES 512

MAXINSTANCES 1

MAXLOGHISTORY 7260

$sqlplus / as sysdba

SQL>startup nomount;

SQL>@<create_control_file>.sql; (Control file creation script)

SQL>recover database using backup until cancel;

SQL>alter database open resetlogs;

The database is now open. Check it by executing the command.

$ ps ‐ef | grep [user id] | grep pmon

4 . Bring up the database listeners

$ lsnrctl start [listener_name]

APPLICATION CLONE:

After the Database clone database and listener will be up and running. Now we have to clone the application tier of the target node. We have to run adcfgclone.pl from

$COMMON_TOP/clone/bin. It will configure the target application node and will create a new application context file.

Note : Before running adcfgclone.pl rename the existing oraInventory(location is in

/var/opt/oracle/oraInst.loc) and make an empty oraInventory folder.

Run perl adcfgclone.pl appsTier

$ cd $COMMON_TOP/clone/bin

$ export PATH=$PATH:/usr/ccs/bin

$ perl adcfgclone.pl appsTier

After completion of  apps tier clone, we will verify the status of all the services and will open browser and using the application URL will login from front end to verify the login page.

Very common post Clone Issues:

     1)      Getting no manager and Standard manager not up:

    

     ·         First check custom.env at appl_top and if not present create custome.env

     ·         source env. Again

     ·         restart all  the services of application

     ·         test the issue by checking the status of concurrent manager

      2)       Oracle APPS R12 Post Cloning issue - Form not launching :

Introduction:

After the successful cloning of R12.1.1, when opening the form, it was not launching.

After troubleshooting with the following steps, form started working fine.

1- Stopped the APPS Tier services.

2- rename the directory "tldcache" under following directories.

a- $ORA_CONFIG_HOME/10.1.3/j2ee/oafm/tldcache

b- $ORA_CONFIG_HOME/10.1.3/j2ee/oacore/tldcache

c- $ORA_CONFIG_HOME/10.1.3/j2ee/forms/tldcache

3- create the emplty directory with the name "tldcache" under the above directories.

4- restart the APPS Tier services.

5- test the issue with starting some forms.

     3)       log/out files are not getting generated:

For fixing this issue, need to do the following steps:

     ·         stop all concurrent services

     ·         login with functional administrator responsibility.

     ·         Navigate to profile/system

     ·         search for %RRA Enabled%

     ·         set the value to “yes” and save it

     ·         start the concurrent services

     ·         test the issue

Form Modes - Socket and Servlet

Form Modes in Oracle application:

1. Socket mode
2. Servlet mode

Oracle Applications Release12 by default configures Forms 10g stack in servlet mode, and this is the recommended deployment model for forms on the web. Java servlet called the Forms Listener Servlet manages the communication between the Forms Java Client and the OracleAS Forms Services. It uses the HTTP server port and does not need extra ports to handle the communication between client and the oracle application server.

There are circumstances, where we need to have socket mode. Socket mode allows desktop client to use Forms server directly. Hence, it has some positive impact as compared to servlet mode.

Servlet mode has high network traffic as it uses http protocol on each transaction between client and form server for exchanging cookies and https headers.

Listener executable name in 10g AS is frmsrv present in $ORACLE_HOME/bin.

$ ./adformsctl.sh status to check form status if it is in servlet mode.

$ ./adformsrvctl.sh status to check form status if it is in socket mode.

Manually Starting/stoping/status of Forms Server in socket mode:

$INST_TOP/admin/scripts/adformsrvctl.sh start

$INST_TOP/admin/scripts/adformsrvctl.sh stop

$INST_TOP/admin/scripts/adformsrvctl.sh status

Step to change the mode of forms as below:

1.Source the environment on the application tier.

2.Stop all the application tier services:

$ADMIN_SCRIPT_HOME/adstpall.sh

3.Run the following command to enable Forms Socket Mode:

$FND_TOP/bin/txkrun.pl -script=ChangeFormsMode \

[-contextfile=] \

-mode=socket \

[-port=] \

-runautoconfig= \

-appspass=

Note: appspass is required only if we choose runautoconfig=yes.

4.Start all the application tier services:

$INST_TOP/admin/scripts/adstrtal.sh

5.Check whether Forms Server is running:

On UNIX:

$INST_TOP/admin/scripts/adformsrvctl.sh status or INST_TOP/admin/scripts/adformsctl.sh status

Oracle Database Architecture

Oracle Database Architecture:

Overview of the System Global Area:

When instance is started SGA is allocated the memory from RAM. SGA memory allocation can not be more than the RAM size.Once instance is shut down the memory is given back to Operating system.

If more memory required than the allocated memory to SGA, In that case it will use the SWAP memory and this memory is compulsory component when ora
cle database is installed.

A system global area (SGA) is a group of shared memory structures that contain data and control information for one Oracle database instance. If multiple users are concurrently connected to the same instance, then the data in the instance's SGA is shared among the users. Consequently, the SGA is sometimes called the shared global area.

SGA and oracle processes(DBWR, LGWR, CHKPT, SMON, PMON REC, ARCH etc.) collectively called as instance of an oracle database.

The SGA components are as given below:

• Shared pool (Data dictionary cache and Library cache)
• Database buffer cache
• Redo log buffer
• Java pool
• Large pool 
• Streams pool

Instance is responsible for fulfilling the request through server process. Server process can be either shared server or dedicated server architecture. Server process takes the required data from database to SGA and then provide to user request.

Logical layer:

The logical layer comprises one or more tablespaces and the database schema. 

The database schema consists of tables, clusters, indexes, views, procedures, triggers, sequences, and so on. The database schema is a collection of schema objects.

The relationship between segments, extents, and data blocks:

• Tablespaces: These are at the highest level of Oracle disk-space management.
• Segments: Collection of Extents.
• Extents: One or more data block collectively called as Extents.
• Data blocks: These are at the lowest level of Oracle disk-space management.

The physical layer comprises the following files: 

Control File: Though it is very small file, it is the most crucial and critical file. This file is responsible for starting up the database into open mode and availabe for user request. It contains the requisite information to start the database. 

The names and locations of all the control files of the database can be obtained from the V$CONTROLFILE view.

It also contains the name of database, timestamp of database creation, information of data file and redolog files which helps in recovery.

Control file has two section. Fixed section and Rotation section. Rotation section keeps the information till 7 days by default.We can change it by changing the initialization parameter control_file_record_keep_time.

Data files: Data files are used to store data in blocks for each and every tables of a database.Size of datafiles may vary from kilobytes to terabytes(KB,MB,GB and TB). Most of the space is being occupied by data files in any database.

Redo Log files: Any changes made in data block are captured into redo log buffer and then into redo log files. These files are very helpful in recovery in case of system failure. The information in the Redo Log Buffer is written into Redo Log files by the LGWR background process. The Redo Log files are generally multiplexed and copied for recovery purposes. Sets of redo Log files are known as Redo Log groups. Each database has minimum of two Redo Log groups. Redo Log groups are used in a circular fashion. Redo log groups can be added as per our need.

Note: The V$LOGFILE dynamic performance view is used to obtain information about the names and locations of the Redo Log groups and their members.

SGA- It stands for System Global Area. SGA is allocated whenever an Oracle instance starts and gets de-allocated when the Oracle instance is shutdown.
Components of SGA: 

1. Database Buffer Cache: It contains the data read from data files and data updated.It  has 3 area namely: Free buffer: does not contain any useful data. Dirty buffer: Contains the modified data and ready to be written in data files(by DBWR). Pinned buffer: Currently being used by active user.
2. Redo Log Buffer: Contains the information modified in data buffer and ready to be written to redo log files(By LGWR).
3.  Shared Pool: It has two section as : Library cache: The library cache contains the current SQL execution plan information. It also holds stored procedures and trigger code. Data Dictionary Cache: The dictionary cache stores environmental information, which includes referential integrity, table definitions, indexing information, and other metadata stored within Oracle's internal tables.
4. Large Pool: This area is only used if shared server architecture, also called multi-threaded server (MTS), is used, or if parallel query is utilized. LARGE_POOL_SIZE = integer. Session memory for the shared server and the Oracle XA interface which is used where transactions interact with more than one database.  I/O server processes. Oracle backup and restore operations.
5. Java Pool: Contains all java files.
6. Stream Pool: Used for data stream from one system to another.

PGA- Program Global Area is a region in memory that contains data and control information for a single process. This can be either a server or a background process. A PGA is allocated whenever an Oracle database user connects to a database and a session is created for him/her.

PGA components:

1. Session Area- memory allocated to hold a session variables (login information) and other information related to the session. For a shared server, the session memory is shared and not private.
2. SQL work area- This is also called as sort area. All SQL operations like joining,sorting,groupby, orderby are done in this area.
3. Private SQL Area- A private SQL area contains data such as bind information and runtime memory structures. Each session that issues an SQL statement has a private SQL area.
4. Cursor area - Explicit cursor and Implicit Cursor

Background Process: These are the process which enable the databse to work continuously and are listed below:

1. DBWR : It writes data blocks from dirty buffer of data buffer cache to data files.
2. LGWR : It writes data from the Log Buffer to the redo log. LGWR writes to a Redo Log files in case of these events: #Whenever a user commits a transaction. #Whenever the Redo Log Buffer is one-third (1/3) full. #Every three seconds.
3. CKPT : It updates the header of data file and control file with current timestamp and SCN.
4. PMON : It stands for process monitor. It keeps track of database processes. It also cleans up the process that has died pre-maturely. The result is that all the cache and the resources are freed up. It also restarts those dispatcher processes that might have failed.
5. SMON : It stands for system monitor. It performs instance recovery at instance startup. It cleans up the segment which are no longer in use.

Oracle processes:

• User processes
• Server processes

The first interaction with the Oracle-based application comes from the user computer that creates a user process. The user process then communicates with the server process on the host computer. Here, PGA is used to store session specific information. 

Oracle instance:

The Oracle instance consists of SGA and all the Oracle background processes. To manage the size of SGA, two initialization parameter files known as PFILE and SPFILE are used. There are a total of 250-initialization parameters. PFILE holds 30 of those 250 initialization parameters. Oracle does not recommend modifying the rest of the 220 initialization parameters. 

Brief of Architecture:

SGA = Shared Pool + Data buffer Cache + Redo Log + LArge Pool + Stream Pool + Java Pool 

Oracle Instance = SGA + Background Process

Oracle database = Oracle Instance + datafiles + Controlfile + Redo log files