Oracle DBA Training

RMAN (Recovery Manager)

2012-04-11T17:12:00.003+05:30

RMAN

(Recovery Manager)

Recovery Manager (RMAN) is a utility that can manage your entire Oracle backup and recovery activities.

Benefits:

1. Incremental backups that only copy data blocks that have changed since the last backup.
2. Tablespaces are not put in backup mode, thus there is noextra redo log generation during online backups.
3. Detection of corrupt blocks during backups.
4. Parallelization of I/O operations.
5. Automatic logging of all backup and recovery operations.
6. Built-in reporting and listing commands.

Components of RMAN:

1. RMAN executable
2.Server processes
3.Channels
4.Target database
5.Recovery catalog database (optional)
6.Media management layer (optional)
7.Backups, backup sets, and backup pieces

Architectural components:

1.RMAN executable
2.Server processes
3.Channels
4.Target database
5.Recovery catalog database (optional)
6.Media management layer (optional)
7.Backups, backup sets, and backup pieces

RMAN Executable:

The RMAN executable, usually named rman, is the program that manages all backup and recovery operations. You interact with the RMAN executable to specify backup and recovery operations you want to perform.

Server Processes:

RMAN server processes are background processes, started on the server, used to communicate between RMAN and the databases. When you connect the RMAN client to the target database server, RMAN allocates server sessions on the target instance and directs them to perform the backup and recovery operations. The RMAN client itself does not perform the backup, restore, or recovery.

Channels:

A channel is an RMAN server process started when there is a need to communicate with an I/O device, such as a disk or a tape. A channel is what reads and writes RMAN backup files. It is through the allocation of channels that you govern I/O characteristics such as:

Type of I/O device being read or written to, either a disk or an sbt_tape
Number of processes simultaneously accessing an I/O device
Maximum size of files created on I/O devices
Maximum rate at which database files are read
Maximum number of files open at a time

Target Database:

The target database is the database on which RMAN performs backup, restore, and recovery operations. This is the database that owns the datafiles, control files, and archived redo files that are backed up, restored, or recovered.

Note: That RMAN does not back up the online redo logs of the target database

Recovery Catalog Database:

The recovery catalog database is an optional repository used by RMAN to record information concerning backup and recovery activities performed on the target. This includes information such as:

Details about the physical structure of the target database
A log of backup operations performed on the target database's datafiles, control files, and archived redo log files
Stored scripts containing frequently used sequences of RMAN commands

Why is the catalog optional?

Because RMAN manages backup and recovery operations, it requires a place to store necessary information about the database. RMAN always stores this information in the target database control file. You can also store RMAN metadata in a recovery catalog schema contained in a separate database. The recovery catalog
schema must be stored in a database other than the target database.

Contents of the Recovery Catalog

the recovery catalog contains information about RMAN operations, including:

Datafile and archived redo log backup sets and backup pieces
Datafile copies
Archived redo logs and their copies
Tablespaces and datafiles on the target database
Stored scripts, which are named user-created sequences of RMAN commands
Persistent RMAN configuration settings

Backups, Backup Sets, and Backup Pieces

when you issue an RMAN backup command, RMAN creates backup sets, which are logical groupings of physical files. The physical files that RMAN creates on your backup media are called backup pieces.

RMAN backup

A backup of all or part of your database. This results from issuing an RMAN backup command. A backup consists of one or more backup sets.

Backup set

A logical grouping of backup files -- the backup pieces -- that are created when you issue an RMAN backup command. A backup set is RMAN's name for a collection of files associated with a backup. A backup set is composed of one or more backup pieces.

Backup piece

A physical binary file created by RMAN during a backup. Backup pieces are written to your backup medium, whether to disk or tape. They contain blocks from the target database's datafiles, archived redo log files, and control files. When RMAN constructs a backup piece from datafiles, there are a several rules that it follows:

 A datafile cannot span backup sets

 A datafile can span backup pieces as long as it stays within one backup set

 Data files and control files can coexist in the same backup sets

 Archived redo log files are never in the same backup set as datafiles or control files RMAN is the only tool that can operate on backup pieces. If you need to restore a file from an RMAN backup, you must use RMAN to do it. There's no way for you to manually reconstruct database files from the backup pieces. You must use RMAN to restore files from a backup piece.

Note: we can configure RMAN in two modes:

Catalog mode
Nocatalog mode

Oracle strongly recommends you to configure rman in catalog mode.

In no catalog mode information about configuration parameters and backupsets will be stored in target database control file.

In catalog mode information about configuration parameters and backupsets will be stored in separate database.

RMAN Configuration in catalog mode:

Configuration at Target database side:

Go to Target database (prd) (200.200.0.1)

$ cd $ORACLE_HOME/dbs

$ orapwd file = orapwprd password = ******* force = y

$cd $ORACLE_HOME/network/admin

$ vi listener.ora

:wq!

$lsnrctl start targetprd

Catalog database creation:

--> Create new database (catdb)

-->Create separate tablespace to hold rman metadata

-->Create separate user and grant connect, resource and recovery_catalog_owner to rman user

-->Create catalog

-->And register target database in to the catalog

Configuration at catalog database side (catdb) (200.200.0.2):

$export ORACLE_SID=catdb

$sqlplus ‘/as sysdba’

Sql>startup

Sql>create tablespace rman_ts datafile ‘/Oracle/oradata/catdb/rman.dbf’ size = 300m;

Sql>grant connect, resource, recovery_catalog_owner to rman identified by rman;

Sql>alter user rman default tablespace rman_ts;

Sql>conn rman/rman

Sql>select * from tab;

---no rows----

$ cd $ORACLE_HOME/netrowk/admin

$ vi tnsnames.ora

$ tnsping to_targetprd

$rman catalog rman/rman

Rman>create catalog;

Rman>exit

$ sqlplus rman/rman

Sql>select count(*) from tab;

--In 10g 90-99 tables and views created—

--In 11g 142 tables and views created—

Sql>select * from rc_database;

--no rows selected—

This query gives us what are all the database registered in catalog database

Registering target database (prd)

$ rman catalog rman/rman target sys/****@to_targetprd

Rman>register database;

Rman>show all;

RMAN Cold Backup

$ rman catalog rman/rman target sys/****@to_targetprd

Connected to target database

Rman>show all;

Rman>list backup;

Rman>list backup summary;

Rman>shutdown immediate;

Rman>startup mount;

Rman>backup full database;

Rman>backup current controlfile;

Note: In Rman cold backup database must be in mount stage.

RMAN Restore using RMAN COLD Backup

I have taken RMAN COLD Backup in my last post, In this post I am going to delete datafile and then I will try to open a database. I will write some steps to recover/restore datafile/database.

At Server Side: (prd)

$sqlplus ‘/as sysdba’

Sql>startup

ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 226495364 bytes
Database Buffers 377487360 bytes
Redo Buffers 7135232 bytes
Database mounted.

ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/ORACLE/ORADATA/PRD/INDX.DBF'

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

At Client side:

$ rman catalog rman/rman target sys/****@to_targetprd

Connected to target database (not started)

RMAN> startup nomount;
Oracle instance started
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 226495364 bytes
Database Buffers 377487360 bytes
Redo Buffers 7135232 bytes

RMAN> sql 'alter database mount';

RMAN> restore tablespace indx;

RMAN> recover tablespace indx;

RMAN> recover database;

RMAN> alter database open resetlogs;

Database opened

RMAN Hot Backup

As the name implies, an online backup allows you to backup the database while users are working. Some newer DBA’s are a little afraid of online backups, and that somehow they might not work. We assure you that I’ve yet to see a correctly done online backup fail to be recoverable. Most shops these days use online backups at least for their production systems. For a large number of shops online backups are the only backups they ever make.

In this section we will walk you through doing an online backup. First, you will need to put your database in ARCHIVELOG mode as discussed earlier in chapter three of this book.

Then you should configure your database and RMAN as we describe in the above section of this book titled “Configure the Database and RMAN for our Backup”. Once that is done, you are ready to do an online backup with RMAN.

RMAN>backup database plus archivelog;

Clone/Refresh database using Rman:

Edit the pfile with new database name and edit directories and create directories

Configuration of SQLNET Protocol

2012-04-10T20:35:00.002+05:30

Configuration of SQLNET Protocol

SQLNET is a built-in protocol in oracle. It is available in client machines and database server machines. This network protocol contains three files.

listener.ora
tnsnames.ora
sqlnet.ora

Listener.ora:

Listener.ora must be configured at server side. This file can be created by using NETCA tool. This file must contain database SID name. Once the listener service is started, then user request can be accessed by database server.

How to start listener service:

$ lsnrctl start LISTENER

Tnsnames.ora:

This file must be configured or created at client machine. Every client machine require a tnsnames.ora file.

“TNS” means = transparent net service

This file also can be created by NETCA tool means NETWORK CONFIGURATION ASSISTANT

Configuration of listener.ora at server side: (200.200.0.2)

$ xhost + (this command disables access control list and allow users to access NETCA tool)

$ su – oracle

$cd $ORACLE_HOME/network/admin

$ pwd

$ ls

$ rm *

$ ls

$ netca

$ lsnrctl status = to check the status of listener

$ lsnrctl stop = to stop the listener

$ lsnrctl start = to start the listener

$ lsnrctl reload = refresh the listener

$ vi listener.ora

$:wq

$ lsnrctl stop

$ lsnrctl start

$ lsnrctl status

Configuration of tnsnames.ora at client side: (200.200.0.1)

$ ping 200.200.0.2

$ xhost +

$ su – oracle

$ cd $ORACLE_HOME/network/admin/

$netca

$ ls

$ vi tnsnames.ora

How to test the physical connectivity between the oracle client and database machine:

$tnsping hcp

$sqlplus system/********@hcp

Sql>select * from tab;

Note: If listener service is down at server side then client cannot able to access server.

Oracle Data Pump

2012-04-09T15:46:00.001+05:30

Oracle Data Pump

Oracle data pump:

Oracle data pump is a new feature available from oracle 10g onwards. expdp and impdp tools are for logical backups.

To configure oracle data pump need to set the following parameter in the pfile.

Streams_pool_size = 10M;

Configuration of Oracle data pump:

$ cd $ORACLE_HOME/dbs

$ vi initSID.ora

Streams_pool_size = 10m

:wq

$ pwd

$ cd /Oracle/logicalbackup

$mkdir dpump

$ sqlplus ‘/as sysdba’

Sql>startup

Sql>select * from dba_directories;

Sql>create directory dpump as ‘/Oracle/logicalbackup/dpump’;

Sql>select * from dba_directories;

Sql>grant read,write on directory dpump to user1;

Sql>exit

How to take full database export by using expdp tool:

$ expdp directory = dpump dumpfile = dbfull.dmp full = y

Username: system/manager as sysdba

$pwd

$ cd dpump

$ ls

How to export user level tables by using expdp tool:

$ expdp directory = dpump dumpfile = user1.dmp

Username: user1/user1

$ cd dpump

$ ls

How to export individual tables by using expdp tool:

$ expdp directory = dpump dumpfile = user1emp.dmp tables = emp

Username: user1/user1

How to import full database by using impdp tool:

$ sqlplus ‘/as sysdba’

Sql> drop user user1 cascade;

$impdp directory = dpump dumpfile = dbfull.dmp full = y

Username: system/manager as sysdba

How to import tables from user level backup:

$ sqlplus ‘/as sysdba’

Sql>select * from tab;

Sql>select * from emp;

Sql>drop table emp;

Sql>purge recyclebin;

Sql>select * from tab;

Sql>exit

$ impdp directory = dpump dumpfile = user1.dmp tables = emp

Username: user1/user1

How to drop dump directory from database level:

$ sqlplus ‘/as sysdba’

Sql>grant read,write on directory dpump to user1;

Sql>drop directory dpump;

Sql>select * from dba_directories;

Export and Import

2012-04-09T15:13:00.000+05:30

Export and Import

Oracle's export (exp) and import (imp) utilities are used to perform logical database backup and recovery. When exporting (exp), database objects are dumped to a binary file which can then be imported (imp) into another Oracle database.

These utilities can be used to move data between different machines, databases or schema. However, as they use a proprietary binary file format, they can only be used between Oracle databases. One cannot export data and expect to import it into a non-Oracle database.

Various parameters are available to control what objects are exported or imported. To get a list of available parameters, run the exp or imp utilities with the help=yes parameter.

Note: From Ver. 10g Oracle is recommending to use Data Pump Export and Import tools, which are enhanced versions of original Export and Import tools.

Logical Backup can be taken in three methods:

Full database level
User level
Table level(Individual)

Logical Backup provides following advantages:

It requires very less disk space to export the data
It is useful to migrate data from one database to another database
It is useful to improve the database performance by using database reorganization
It is useful to migrate the data from one oracle version other oracle version
It is useful to migrate data from one operating system to another os
It is useful to move tables from one user to another user

Logical backups can be performed by exp and imp tools, and expdp and impdp tools.

Exp & imp tools:

Note: before performing logical backup database instance must be started

$ su – oracle

$ sqlplus ‘/as sysdba’

Sql>startup

Sql>select * from all_users;

Sql>select * from dba_users;

Sql>create user user1 identified by user1;

Sql>grant connect, resource to user1;

How to export complete full database backup:

$exp file = /Oracle/logicalbackup/dbfull.dmp log=dbfull.log full=y

Username : system/manager as sysdba

$ ls dbfull.dmp

How to export user level backup:

$exp file = /Oracle/logicalbackup/user1.dmp log=user1.log

Username = user1/user1

$ ls user1.dmp

How to export individual tables:

$exp file = /Oracle/logicalbackup/user1emp.dmp tables = emp log= emp.log

(OR)

How to export multiple tables

$ exp file=/Oracle/logicalbackup/user1tables.dmp tables = emp,dept,person

Username = user1/user1

$ ls user1tables.dmp

How to import full database:

$ sqlplus ‘/as sysdba’

Sql>startup

Sql>drop user user1 cascade;

$imp file=/Oracle/logicalbackup/dbfull.dmp full = y ignore = y

Note: ignore = y this option cannot export table if already table is available.

Username = system/manager as sysdba

$sqlplus ‘/as sysdba’

Sql>select * from all_users;

How to import user1 objects into user2:

$ imp file = /Oracle/logicalbackup/user1.dmp from user = user1 to user = user2

Username = system/manager as sysdba

How to import user1 objects into user2 without records:

$ imp file = /Oracle/logicalbackup/user1.dmp from user = user1 to user = user2 rows = n

How to import individual tables:

$ sqlplus user1/user1

Sql>drop table emp;

Sql>select * from tab;

Sql>host

$ imp file = /Oracle/logicalbackup/user1emp.dmp tables = emp

Username = user1/user1

$ sqlplus user1/user

Sql>select * from emp;

Oracle Database Cloning

2012-04-02T22:19:00.002+05:30

Oracle Database Cloning

* A database clone is an activity/procedure which is performed by every DBA on regular basis or when there is a requirement or request to do so from the different departments i.e. Test/Development teams.

* Cloning is nothing but creating a copy of production system in to a test or development environment. i.e., having an exact image of production database in test area.

* Cloning is a procedure for preparing and creating a test or development servers with the copy of Oracle production database for testing upgrades, migrating an existing system to new hardware.

* A cloning process includes a copy of Oracle Home (Directories and Binaries) backup and Database (Database related files) backup to prepare the instance on another server.

* Though, it is possible to clone a database on the same server, Oracle doesn’t suggest to clone a database on the same server, where the production database is running.

Here we can clone database using two methods they are

1. Cloning using cold backup

2. Cloning using hot backup

Cloning using cold backup

Clone an Oracle Database using Cold Physical Backup

Database Name: PROD

Source Database side: (PROD database)
Cold Backup Steps:
1. Get the file path information using below query

Select name from v$datafile;
select member from v$logfile;
select name from v$controlfile;

2. Parameter file backup
If PROD database running on spfile
Create pfile=’/u01/backup/inittroy.ora’ from spfile;

If database running in pfile using os command to copy the pfile and placed in backup path.
3. Taken the control file backup
sql>Alter database backup controlfile to trace as ‘/u01/backup/control01.ora’;
4.Shutdown immediate

5.Copy all the data files/log files using os command & placed in backup path.

6.Startup the database.
Clone Database side: (Clone database)

Database Name: TEST
Clone Database Steps:
1. Create the appropriate folder in corresponding path & placed the backup files in corresponding folder. (bdump,udump,create,pfile,cdump,oradata)

2. Change the init.ora parameter like control file path, dbname, instance name etc...

3. Create the password file using orapwd utility.

(Database in windows we need to create the service id using oradim utility)

4. Startup the Database in NOMOUNT stage.

5. Create the control file for cloning database.

Using backup controlfile trace to generate the create controlfile scripts.
Change the Database name & files path, also change 'REUSE' needs to be changed to 'SET'.

CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS FORCE LOGGING NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 '/U01/oradata/clone/redo01.log' SIZE 200M,
GROUP 2 '/U01/oradata/clone/redo02.log' SIZE 200M,
GROUP 3 '/U01/oradata/clone/redo03.log' SIZE 200M
DATAFILE
'/U01/oradata/clone/system01.dbf',
'/U01/oradata/clone/undotbs01.dbf',
'/U01/oradata/clone/users01.dbf',
CHARACTER SET WE8ISO8859P1;

Note: placed the script in sql prompt. Now controlfile created

6. Now open the database.

sql>Alter database open resetlogs;

Note: Check the logfile, datafiles & instance status

Cloning using Hot backup

Database Name: PROD

Database must be in Archive log mode.

Source Database side: (PROD database)

Hot Backup Steps:
1.Get the file path information using below query.
Select tablespace_name, file_name from dba_data_files order by 1;

2. Parameter file backup

If troy database running on spfile

Create pfile=’/u01/backup/inittroy.ora’ from spfile;

If database running in pfile using os command to copy the pfile and placed in backup path.

3.Put the database in begin backup mode Using os command to copy the datafiles, logfiles, archives belongs to begin backup mode database& placed in backup path. (Refer below example)
4.Once copied the datafile,logfiles, archives release the database from begin backup mode to end backup
5.Taken the controlfile backup
sql >Alter database backup controlfile to trace as ‘/u01/backup/control01.ora’;

6.Backup all your archive log files between the previous backup and the new backup as well.

Example:
steps:
2.Alter database begin backup;
3.copy entire datafiles, logfiles, archives.
4. Alter database end backup;
Clone Database side: (TEST database)

Database Name: TEST

Clone Database Steps:
1. Create the appropriate folder in corresponding path & placed the backup files in corresponding folder.(bdump,udump,create,pfile,cdump,oradata)

2.Change the init.ora parameter like control file path, dbname, instance name etc...

3. Create the password file using orapwd utility.

(Database in windows we need to create the service id using oradim utility)

4. Startup the Database in NOMOUNT stage.

5. Create the control file for cloning database.

Using backup controlfile trace to generate the create controlfile scripts.
Change the Database name & files path, also change 'REUSE' needs to be changed to 'SET'.

CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS FORCE LOGGING NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 '/U01/oradata/clone/redo01.log' SIZE 200M,
GROUP 2 '/U01/oradata/clone/redo02.log' SIZE 200M,
GROUP 3 '/U01/oradata/clone/redo03.log' SIZE 200M
DATAFILE
'/U01/oradata/clone/system01.dbf',
'/U01/oradata/clone/undotbs01.dbf',
'/U01/oradata/clone/users01.dbf',
CHARACTER SET WE8ISO8859P1;
Note: placed the script in sql prompt. Now controlfile created.
6. Recover the database using controlfile.

Recover database using backup controlfile until cancel;

7.Now open the database.

Alter database open resetlogs;

Note: Check the logfile, datafiles status.

USER MANAGED BACKUP AND RECOVERY

2012-04-02T22:18:00.003+05:30

User Managed Backup

Backup means taking copy of our database which is overcome for database failures and with that backup we can recover database whenever it crash. Here we have two types of backups are there, they are

· Physical Backup

1. Cold Backup (Consistent Backup)

2. Hot Backup (Inconsistent Backup)

· Logical Backup

1. Export / Import

2. Expdp /Impdp (available on oracle10g onwards)

Here first we see about physical backup, later we discuss about logical backup

Physical Backup

Cold Backup: (Consistent Backup)

A cold (or off-line) backup is a backup performed while the database is off-line and unavailable to its users. Cold backups can be taken regardless if the database is in ARCHIVELOG or NOARCHIVELOG mode. It is easier to restore from off-line backups as no recovery (from archived logs) would be required to make the database consistent.

Why you say cold backup is consistent backup?

After shutdown the database we have taken the cold backup. During this time all datafile headers SCN are same. When we restore the cold backup, no redo is needed in case of recovery. We had taken backup datafile, logfile, controlfile, parameter file & password file.

Cold Backup Steps:

1. Get the file path information using below query

Sql> Select name from v$datafile;

Sql> select member from v$logfile;

Sql> select name from v$controlfile;

2. Taken the password file & parameter file backup ( Optional)

3. Sql>Alter system switch logfile;

4. Sql>Shutdown immediate;

5. Copy all the data files /control files /log files using os command & placed in backup path(in any secure location)

6. Startup the database ( sql> startup)

Hot Backup: (Inconsistent Backup)

A hot (or on-line) backup is a backup performed while the database is open and available for use (read and write activity). Except for Oracle exports, one can only do on-line backups when the database is ARCHIVELOG mode.

Why you say Hot backup is inconsistent backup?

While database running we have taken the hot backup. During this time backup tablespace datafile headers SCN are not same with another tablespace datafiles. When we restore the hot backup, redo is needed for recovery.

Hot Backup Steps:

1 First we have to check our database is in archive log mode or not

Sql > archive log list;

If database is in archive log then it shows archive log enable and location of archive log files, if not it shows archive log mode disable

If archive log mode is disable then we have to shutdown the database and enable the archive log mode using below commands

Sql>shutdown immediate;

Open parameter file of that database to edit the location of archive logs

$ Vi initTEST.ora

Log_archive_dest=’/Oracle/product/10.2.0/db_1/admin/arch’

Save and exit(:wq)

Sql>startup mount

Sql>alter database archivelog;

Sql>alter database open;

And check database is in archive log mode or not

Sql>archive log list;

2. Now we can take the complete database backup or tablespace backup

Let us suppose we want to take online backup of “USERS" tablespace. You can query the V$DATAFILE view to find out the name of datafiles associated with this tablespace. Let’s suppose the file is ‘/u01/oradata/usr1.dbf’

Sql>alter tablespace USERS begin backup;

Sql> host cp /u01/oradata/usr1.dbf /u02/backup

Sql>alter tablespace USERS end backup;

Sql>exit;

Similarly for complete database backup

Sql>alter database begin backup;

Copy datafiles, control files, redo logfiles, archive log files to secure location

After completion of copying

Sql>alter database end backup;

Sql>exit;

RECOVERING THE DATABASE IF IT IS RUNNING IN NOARCHIVELOG MODE

Option 1: When you don’t have a backup

If you have lost one datafile and if you don't have any backup and if the datafile does not contain important objects then, you can drop the damaged datafile and open the database. You will loose all information contained in the damaged datafile.

The following are the steps to drop a damaged datafile and open the database.

STEP 1: First take full backup of database for safety.

STEP 2: Start the sqlplus and give the following commands.

$sqlplus

Enter User: / as sysdba

SQL> STARTUP MOUNT

SQL> ALTER DATABASE DATAFILE ‘/u01/ica/usr1.dbf ‘offline drop;

SQL>alter database open;

Option 2: When you have the Backup

If the database is running in Noarchivelog mode and if you have a full backup. Then there are two options for you.

i . Either you can drop the damaged datafile, if it does not contain important information which you can afford to loose.

ii . Or you can restore from full backup. You will loose all the changes made to the database since last full backup.

To drop the damaged datafile follow the steps shown previously.

To restore from full database backup. Do the following.

STEP 1: Take a full backup of current database.

STEP 2: Restore from full database backup i.e. copy all the files from backup to their original locations.

Suppose the backup is in “/u2/oracle/backup" directory. Then do the following

$cp /u02/backup/* /u01/ica

This will copy all the files from backup directory to original destination. Also remember to copy the control files to all the mirrored locations.

RECOVERING FROM LOST OF CONTROL FILE

If you have lost the control file and if it is mirrored. Then simply copy the control file from mirrored location to the damaged location and open the database. If you have lost all the mirrored control files and all the datafiles and logfiles are intact. Then you can recreate a control file.

If you have already taken the backup of control file creation statement by giving this command. " ALTER DATABASE BACKUP CONTROLFILE TO TRACE; " and if you have not added any tablespace since then, just create the controlfile by executing the statement

Buf If you have added any new tablespace after generating create controlfile statement. Then you have to alter the script and include the filename and size of the file in script file.

If your script file containing the control file creation statement is "CR.SQL"

Then just do the following.

STEP 1: Start sqlplus

STEP 2: connect / as sysdba

STEP 3: Start and do not mount a database like this.

SQL> STARTUP NOMOUNT

STEP 4: Run the "CR.SQL" script file.

STEP 5: Mount and Open the database.

SQL>alter database mount;

SQL>alter database open;

If you do not have a backup of Control file creation statement. Then you have to manually give the CREATE CONTROL FILE statement. You have to write the file names and sizes of all the datafiles. You will lose any datafiles which you do not include.

Recovering Database when the database is running in ARCHIVELOG Mode

Recovering from the lost of Damaged Datafile

If you have lost one datafile. Then follow the steps shown below

STEP 1: Shutdown the Database if it is running.

STEP 2: Restore the datafile from most recent backup.

STEP 3: Then Start sqlplus and connect as SYSDBA.

$sqlplus

Enter User:/ as sysdba

SQL>Startup mount;

SQL>Set autorecovery on;

SQL>alter database recover;

If all archive log files are available then recovery should go on smoothly. After you get the "Media Recovery completely" statement. Go on to next step.

STEP 4: Now open the database

SQL>alter database open;

Recovering from the Lost Archived Files:

If you have lost the archived files. Then Immediately shutdown the database and take a full offline backup.

Time Based Recovery (INCOMPLETE RECOVERY)

Suppose a user has a dropped a crucial table accidentally and you have to recover the dropped table.

You have taken a full backup of the database on Monday 13-Aug-2007 and the table was created on Tuesday 14-Aug-2007 and thousands of rows were inserted into it. Some user accidently drop the table on Thursday 16-Aug-2007 and nobody notice this until Saturday.

Now to recover the table follows these steps.

STEP 1: Shutdown the database and take a full offline backup.

STEP 2: Restore all the datafiles, logfiles and control file from the full offline backup which was taken on Monday.

STEP 3: Start SQLPLUS and start and mount the database.

STEP 4: Then give the following command to recover database until specified time.

SQL> recover database until time '2007:08:16:13:55:00'

using backup controlfile;

STEP 5: Open the database and reset the logs. Because you have performed a Incomplete Recovery, like this

SQL> alter database open resetlogs;

STEP 6: After database is open. Export the table to a dump file using Export Utility.

STEP 7: Restore from the full database backup which you have taken on Saturday.

STEP 8: Open the database and Import the table.

Note: In Oracle 10g you can easily recover drop tables by using Flashback feature.

Managing the UNDO TABLESPACE

2012-04-02T22:17:00.002+05:30

Managing the UNDO TABLESPACE

Every Oracle Database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo.

Undo records are used to:

Roll back transactions when a ROLLBACK statement is issued
Recover the database
Provide read consistency
Analyze data as of an earlier point in time by using Flashback Query
Recover from logical corruptions using Flashback features

Earlier releases of Oracle Database used rollback segments to store undo. Oracle9i introduced automatic undo management, which simplifies undo space management by eliminating the complexities associated with rollback segment management. Oracle strongly recommends that you use undo tablespace to manage undo rather than rollback segments.

Switching to Automatic Management of Undo Space

To go for automatic management of undo space set the following parameter.

Steps:-

If you have not created an undo tablespace at the time of creating a database then, create an undo tablespace by typing the following command

SQL>create undo tablespace myundo datafile
‘/u01/oracle/ica/undo_tbs.dbf’ size 500M
autoextend ON next 5M ;

When the system is first running in the production environment, you may be unsure of the space requirements of the undo tablespace. In this case, you can enable automatic extension for datafiles of the undo tablespace so that they automatically increase in size when more space is needed

Shutdown the Database and set the following parameters in parameter file.

UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=myundo

Start the Database.

Now Oracle Database will use Automatic Undo Space Management.

Calculating the Space Requirements For Undo Retention

You can calculate space requirements manually using the following formula:

UndoSpace = UR * UPS + overhead

where:

UndoSpace is the number of undo blocks
UR is UNDO_RETENTION in seconds. This value should take into consideration long-running queries and any flashback requirements.
UPS is undo blocks for each second
overhead is the small overhead for metadata (transaction tables, bitmaps, and so forth)

As an example, if UNDO_RETENTION is set to 3 hours, and the transaction rate (UPS) is 100 undo blocks for each second, with a 8K block size, the required undo space is computed as follows:

(3 * 3600 * 100 * 8K) = 8.24GBs

To get the values for UPS, Overhead query the V$UNDOSTAT view. By giving the following statement

SQL> Select * from V$UNDOSTAT;

Altering UNDO Tablespace

If the Undo tablespace is full, you can resize existing datafiles or add new datafiles to it

The following example extends an existing datafile

SQL> alter database datafile ‘/u01/oracle/ica/undo_tbs.dbf’ resize 700M

The following example adds a new datafile to undo tablespace

SQL> ALTER TABLESPACE myundo

ADD DATAFILE '/u01/oracle/ica/undo02.dbf' SIZE 200M AUTOEXTEND ON
NEXT 1M MAXSIZE UNLIMITED;

Dropping an Undo Tablespace

Use the DROP TABLESPACE statement to drop an undo tablespace. The following example drops the undo tablespace undotbs_01:

SQL> DROP TABLESPACE myundo;

An undo tablespace can only be dropped if it is not currently used by any instance. If the undo tablespace contains any outstanding transactions (for example, a transaction died but has not yet been recovered), the DROP TABLESPACE statement fails.

Switching Undo Tablespaces

You can switch from using one undo tablespace to another. Because the UNDO_TABLESPACE initialization parameter is a dynamic parameter, the ALTER SYSTEM SET statement can be used to assign a new undo tablespace.

The following statement switches to a new undo tablespace:

ALTER SYSTEM SET UNDO_TABLESPACE = myundo2;

Assuming myundo is the current undo tablespace, after this command successfully executes, the instance uses myundo2 in place of myundo as its undo tablespace.

Viewing Information about Undo Tablespace

To view statistics for tuning undo tablespace query the following dictionary

SQL>select * from v$undostat;

To see how many active Transactions are there and to see undo segment information give the following command

SQL>select * from v$transaction;

To see the sizes of extents in the undo tablespace give the following query

SQL>select * from DBA_UNDO_EXTENTS;

Managing Tablespaces and Data files

2012-04-02T22:16:00.000+05:30

Managing Tablespaces and Data files

A small database might need only the SYSTEM tablespace; however, Oracle recommends that you create additional tablespaces to store user data, user indexes, undo segments, and temporary segments separate from data dictionary. This gives you more flexibility in various database administration operations and reduces contention among dictionary objects and schema objects for the same data files.

The DBA can create new tablespaces, resize data files, add data files to tablespaces, set and alter default segment storage settings for segments created in a tablespace, make a tablespace read-only or read-write, make a tablespace temporary or permanent, and drop tablespaces.

Creating New Tablespaces

You can create Locally Managed or Dictionary Managed Tablespaces. In prior versions of Oracle only Dictionary managed Tablespaces were available but from Oracle ver. 8i you can also create Locally managed tablespaces. The advantages of locally managed tablespaces are

Locally managed tablespaces track all extent information in the tablespace itself by using bitmaps, resulting in the following benefits:

Concurrency and speed of space operations is improved, because space allocations and deallocations modify locally managed resources (bitmaps stored in header files) rather than requiring centrally managed resources such as enqueues
Performance is improved, because recursive operations that are sometimes required during dictionary-managed space allocation are eliminated

To create a locally managed tablespace give the following command

SQL> CREATE TABLESPACE usertbs DATAFILE '/u02/oracle/ica/ica01.dbf' SIZE 50M

EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

AUTOALLOCATE causes the tablespace to be system managed with a minimum extent size of 64K.

SQL> CREATE TABLESPACE usertbs DATAFILE '/u02/oracle/ica/ica01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K;

To Create Dictionary Managed Tablespace

SQL> CREATE TABLESPACE usertbs DATAFILE '/u02/oracle/ica/ica01.dbf' SIZE 50M EXTENT MANAGEMENT DICTIONARY;

Bigfile Tablespaces (Introduced in Oracle Ver. 10g)

A bigfile tablespace is a tablespace with a single, but very large (up to 4G blocks) datafile. Traditional smallfile tablespaces, in contrast, can contain multiple datafiles, but the files cannot be as large. Bigfile tablespaces can reduce the number of datafiles needed for a database.

To create a bigfile tablespace give the following command

SQL> CREATE BIGFILE TABLESPACE ica_bigtbs

DATAFILE '/u02/oracle/ica/bigtbs01.dbf' SIZE 50G;

To Extend the Size of a tablespace

Option 1

You can extend the size of a tablespace by increasing the size of an existing datafile by typing the following command

SQL> alter database usertbs datafile ‘/u01/oracle/data/icatbs01.dbf’ resize 100M;

This will increase the size from 50M to 100M

Option 2

You can also extend the size of a tablespace by adding a new datafile to a tablespace. This is useful if the size of existing datafile is reached o/s file size limit or the drive where the file is existing does not have free space. To add a new datafile to an existing tablespace give the following command.

SQL> alter tablespace add datafile ‘/u02/oracle/ica/icatbs02.dbf’ size 50M;

Option 3

You can also use auto extend feature of datafile. In this, Oracle will automatically increase the size of a datafile whenever space is required. You can specify by how much size the file should increase and Maximum size to which it should extend.

To make a existing datafile auto extendable give the following command

SQL> alter database datafile ‘/u01/oracle/ica/icatbs01.dbf’ auto extend ON next 5M maxsize 500M;

To decrease the size of a tablespace

You can decrease the size of tablespace by decreasing the datafile associated with it. You decrease a datafile only up to size of empty space in it. To decrease the size of a datafile give the following command

SQL> alter database datafile ‘/u01/oracle/ica/icatbs01.dbf’ resize 30M;

Taking tablespaces Offline or Online

You can take an online tablespace offline so that it is temporarily unavailable for general use. The rest of the database remains open and available for users to access data. Conversely, you can bring an offline tablespace online to make the schema objects within the tablespace available to database users. The database must be open to alter the availability of a tablespace.

To alter the availability of a tablespace, use the ALTER TABLESPACE statement. You must have the ALTER TABLESPACE or MANAGE TABLESPACE system privilege.

To Take a Tablespace Offline give the following command

SQL>alter tablespace usertbs offline;

To again bring it back online give the following command.

SQL>alter tablespace usertbs online;

To take individual datafile offline type the following command

SQL>alter database datafile ‘/u01/oracle/ica/ica_tbs01.dbf’ offline;

Again to bring it back online give the following command

SQL> alter database datafile ‘/u01/oracle/ica/ica_tbs01.dbf’ online;

Making a Tablespace Read only

Making a tablespace read-only prevents write operations on the datafiles in the tablespace. The primary purpose of read-only tablespaces is to eliminate the need to perform backup and recovery of large, static portions of a database. Read-only tablespaces also provide a way to protecting historical data so that users cannot modify it. Making a tablespace read-only prevents updates on all tables in the tablespace, regardless of a user's update privilege level.

To make a tablespace read only

SQL>alter tablespace ica read only

Again to make it read write

SQL>alter tablespace ica read write;

Renaming Tablespaces

Using the RENAME TO clause of the ALTER TABLESPACE, you can rename a permanent or temporary tablespace. For example, the following statement renames the users’ tablespace:

Sql>ALTER TABLESPACE users RENAME TO usersts;

Dropping Tablespaces

You can drop a tablespace and its contents (the segments contained in the tablespace) from the database if the tablespace and its contents are no longer required. You must have the `DROP` `TABLESPACE` system privilege to drop a tablespace.

Caution: Once a tablespace has been dropped, the data in the tablespace is not recoverable. Therefore, make sure that all data contained in a tablespace to be dropped will not be required in the future. Also immediately before and after dropping a tablespace from a database, back up the database completely.

To drop a tablespace give the following command.

SQL> drop tablespace usertbs;

This will drop the tablespace only if it is empty. If it is not empty and if you want to drop it anyhow then add the following keyword

SQL>drop tablespace usertbs including contents;

This will drop the tablespace even if it is not empty. But the datafiles will not be deleted you have to use operating system command to delete the files.

But If you include datafiles keyword then, the associated datafiles will also be deleted from the disk.

SQL>drop tablespace usertbs including contents and datafiles;

Temporary Tablespace

Temporary tablespace is used for sorting large tables. Every database should have one temporary tablespace. To create temporary tablespace give the following command.

SQL>create temporary tablespace temp tempfile ‘/u01/oracle/data/ica_temp.dbf’ size 100M extent management local uniform size 5M;

Increasing or Decreasing the size of a Temporary Tablespace

You can use the resize clause to increase or decrease the size of a temporary tablespace. The following statement resizes a temporary file:

SQL>ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' RESIZE 18M;

The following statement drops a temporary file and deletes the operating system file:

SQL> ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' DROP INCLUDING DATAFILES;

Default Temporary Tablespace

After database creation, a default temporary tablespace can be set by creating a temporary

tablespace and then altering the database.

Sql>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Viewing Information about Tablespaces and Data files

Oracle has provided many Data dictionaries to view information about tablespaces and datafiles. Some of them are:

To view information about Tablespaces in a database give the following query

SQL>select * from dba_tablespaces
SQL>select * from v$tablespace;

To view information about Datafiles

SQL>select * from dba_data_files;
SQL>select * from v$datafile;

To view information about Tempfiles

SQL>select * from dba_temp_files;
SQL>select * from v$tempfile;

To view information about free space in datafiles

SQL>select * from dba_free_space;

To view information about free space in tempfiles

SQL>select * from V$TEMP_SPACE_HEADER;

Relocating or Renaming Datafiles

You can rename datafiles to either change their names or relocate them.

Renaming or Relocating Datafiles belonging to a Single Tablespace

To rename or relocate datafiles belonging to a Single Tablespace do the following.

1. Take the tablespace offline

2. Rename or Relocate the datafiles using operating system command

3. Give the ALTER TABLESPACE with RENAME DATAFILE option to change the filenames within the Database.

4. Bring the tablespace Online

For Example suppose you have a tablespace users with the following datafiles
/u01/oracle/ica/usr01.dbf’
/u01/oracle/ica/usr02.dbf’
Now you want to relocate /u01/oracle/ica/usr01.dbf’ to ‘/u02/oracle/ica/usr01.dbf’ and want to rename ‘/u01/oracle/ica/usr02.dbf’ to ‘/u01/oracle/ica/users02.dbf’ then follow the given the steps

1. Bring the tablespace offline

SQL> alter tablespace users offline;

2. Copy the file to new location using o/s command.

$cp /u01/oracle/ica/usr01.dbf /u02/oracle/ica/usr01.dbf’

Rename the file ‘/u01/oracle/ica/usr02.dbf’ to ‘/u01/oracle/ica/users02.dbf’ using o/s command.

$mv /u01/oracle/ica/usr02.dbf /u01/oracle/ica/users02.dbf

3. Now start SQLPLUS and type the following command to rename and relocate these files

SQL> alter tablespace users rename file
‘/u01/oracle/ica/usr01.dbf’, ‘/u01/oracle/ica/usr02.dbf’ to
‘/u02/oracle/ica/usr01.dbf’,’/u01/oracle/ica/users02.dbf’;

4. Now bring the tablespace Online

SQL> alter tablespace users online;

Procedure for Renaming and Relocating Datafiles in Multiple Tablespaces

You can rename and relocate datafiles in one or more tablespaces using the

ALTER DATABASE RENAME
FILE

statement. This method is the only choice if you want to rename or relocate datafiles of several tablespaces in one operation. You must have the ALTER DATABASE system privilege

To rename datafiles in multiple tablespaces, follow these steps.

1. Ensure that the database is mounted but closed.

2. Copy the datafiles to be renamed to their new locations and new names, using the operating system..

3. Use ALTER DATABASE to rename the file pointers in the database control file.

For example, the following statement renames the datafiles/u02/oracle/rbdb1/sort01.dbf and /u02/oracle/rbdb1/user3.dbf to /u02/oracle/rbdb1/temp01.dbf and /u02/oracle/rbdb1/users03.dbf, respectively:

ALTER DATABASE

    RENAME FILE '/u02/oracle/rbdb1/sort01.dbf',

                '/u02/oracle/rbdb1/user3.dbf'

             TO '/u02/oracle/rbdb1/temp01.dbf',

                '/u02/oracle/rbdb1/users03.dbf;

 Always provide complete filenames (including their paths) to properly identify the old and new datafiles. In particular, specify the old datafile names exactly as they appear in the DBA_DATA_FILES view.

4. Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.

5. Start the Database

Managing Archived Redo Logs

2012-04-02T22:13:00.003+05:30

Managing Archived Redo Logs

Oracle allows you to save filled groups of online redo log files, known as archived redo logs, to one or more offline destinations. Archiving is the processes of turning online redo logs into archived redo logs. The background process ARCn automates archiving operations. You can use archived logs to:

-->Recover a database

-->Update a standby database

-->Gain information about the history of a database via the LogMiner utility

Choosing Between NOARCHIVELOG and ARCHIVELOG Mode

Running a Database in NOARCHIVELOG Mode

When you run your database in NOARCHIVELOG mode, you disable the archiving of the online redo log. The database's control file indicates that filled groups are not required to be archived. Therefore, when a filled group becomes inactive after a log switch, the group is available for reuse by LGWR.

The choice of whether to enable the archiving of filled groups of online redo log files depends on the availability and reliability requirements of the application running on the database. If you cannot afford to lose any data in your database in the event of a disk failure, use ARCHIVELOG mode. Note that the archiving of filled online redo log files can require you to perform extra administrative operations.

Running a Database in ARCHIVELOG Mode

When you run a database in ARCHIVELOG mode, you enable the archiving of the online redo log. The database control file indicates that a group of filled online redo log files cannot be used by LGWR until the group is archived. A filled group is immediately available for archiving after a redo log switch occurs.

The archiving of filled groups has these advantages:

A database backup, together with online and archived redo log files, guarantees that you can recover all committed transactions in the event of an operating system or disk failure.
You can use a backup taken while the database is open and in normal system use if you keep an archived log.
You can keep a standby database current with its original database by continually applying the original's archived redo logs to the standby.

Enabling Archive log

By default when creating database, database is in no archive mode to enable database archive mode, first we have to startup the database in mount state , in mount state only we can enable the archive mode

sql> shutdown

sql> startup mount

sql> alter database archivelog;

sql> alter database open;

to check whether the database is in archive log or not

sql>archive log list;

To Set the Destination for Archived Redo Logs Using LOG_ARCHIVE_DEST

Sql> shutdown immediate

Edit the parameter file

$ vi initTEST.ora

LOG_ARCHIVE_DEST = ‘/Oracle/product/10.2.0/db_1/admin/TEST/arch’

LOG_ARCHIVE_FORMAT = arch_%t_%s.arc

Maintaining Redo Log Files

2012-04-02T22:11:00.002+05:30

Maintaining Redo Log Files

Purposes of the Redo Log Files

Redo log files provide the means to redo transactions in the event of a database failure. Every transaction is written synchronously to the redo log files in order to provide a recovery mechanism in case of media failure. (With exceptions such as: direct loads and direct reads done with the NOLOGGING option.) This includes transactions that have not yet been committed, undo segment information, and schema and object management statements. Redo log files are used in a situation such as an instance failure to recover committed data that has not been written to the data files. The redo log files are used only for recovery.

Structure of the Redo Log Files

The database administrator can set up the Oracle database to maintain copies of online redo log files to avoid losing database information due to a single point of failure.

Online Redo Log Groups

-->A set of identical copies of online redo log files is called an online redo log group

-->The LGWR background process concurrently writes the same information to all online redo log files in a group.

-->The Oracle server needs a minimum of two online redo log file groups for the normal operation of a database.

Online Redo Log Members

-->Each online redo log file in a group is called a member.

-->Each member in a group has identical log sequence numbers and the same size. The log sequence number is assigned each time the Oracle server starts writing to a log group to identify each redo log file uniquely. The current log sequence number is stored in the control file and in the header of all data files.

Creating Initial Redo Log Files

The initial set of online redo log groups and members are created during the database creation.

How Redo Logs Work

The Oracle server sequentially records all changes made to the database in the redo log buffer. The redo entries are written from the redo log buffer to one of the online redo log groups called the current online redo log group by the LGWR process. LGWR writes under the following situations:

• When a transaction commits

• When the redo log buffer becomes one-third full

• When there is more than a megabyte of changed records in the redo log buffer

• Before the DBWn writes modified blocks in the database buffer cache to the data files

Redo logs are used in a cyclic fashion. Each redo log file group is identified by a log sequence number that is overwritten each time the log is reused.

Log Switches

LGWR writes to the online redo log files sequentially. When the current online redo log group is filled, LGWR begins writing to the next group. This is called a log switch. When the last available online redo log file is filled, LGWR returns to the first online redo log group and starts writing again.

Forcing Log Switches

Log switches are automatically done at certain points in the operation of the database as identified previously, but a DBA can force a log switch to occur.

Sql> ALTER SYSTEM SWITCH LOGFILE;

Adding New Redo Log Groups

To add new Redo Logfile groups to the database give the following command

SQL>alter database add logfile group 3
‘/u01/oracle/ica/log3.ora’ size 10M;

Note: You can add groups to a database up to the MAXLOGFILES setting you have specified at the time of creating the database. If you want to change MAXLOGFILE setting you have to create a new controlfile.

Adding Members to an existing group

To add new member to an existing group give the following command

SQL>alter database add logfile member
‘/u01/oracle/ica/log11.ora’ to group 1;

Note: You can add members to a group up to the MAXLOGMEMBERS setting you have specified at the time of creating the database. If you want to change MAXLOGMEMBERS setting you have create a new controlfile

Important: Is it strongly recommended that you multiplex logfiles i.e. have at least two log members, one member in one disk and another in second disk, in a database.

Dropping Logfile Group

You can drop logfile group only if the database is having more than two groups and if it is not the current group.

SQL>alter database drop logfile group 3;

Note: When you drop logfiles the files are not deleted from the disk. You have to use O/S command to delete the files from disk.

Dropping Members from a group

You can drop member from a log group only if the group is having more than one member and if it is not the current group. If you want to drop members from the current group, force a log switch or wait so that log switch occurs and another group becomes current. To force a log switch give the following command

SQL>alter system switch logfile;

The following command can be used to drop a logfile member

SQL>alter database drop logfile member ‘/u01/oracle/ica/log11.ora’;

Note: When you drop logfiles the files are not deleted from the disk. You have to use O/S command to delete the files from disk.

Resizing Logfiles

You cannot resize logfiles. If you want to resize a logfile create a new logfile group with the new size and subsequently drop the old logfile group.

Renaming or Relocating Logfiles

To Rename or Relocate Logfiles perform the following steps

For Example, suppose you want to move a logfile from ‘/u01/oracle/ica/log1.ora’ to ‘/u02/oracle/ica/log2.ora’, then do the following

-->Shutdown the database

SQL>shutdown immediate;

-->Move the logfile from Old location to new location using operating system command

$mv /u01/oracle/ica/log1.ora /u02/oracle/ica/log2.ora

-->Start and mount the database

SQL>startup mount

-->Now give the following command to change the location in controlfile

SQL>alter database rename file ‘/u01/oracle/ica/log1.ora’ to ‘/u02/oracle/ica/log2.ora’;

-->Open the database

SQL>alter database open;

Clearing REDO LOGFILES

A redo log file might become corrupted while the database is open, and ultimately stop database activity because archiving cannot continue. In this situation the ALTER DATABASE CLEAR LOGFILE statement can be used reinitialize the file without shutting down the database.

The following statement clears the log files in redo log group number 3:

Sql>ALTER DATABASE CLEAR LOGFILE GROUP 3;

This statement overcomes two situations where dropping redo logs are not possible:

If there are only two log groups
The corrupt redo log file belongs to the current group

If the corrupt redo log file has not been archived, use the UNARCHIVED keyword in the statement.

Sql>ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;

Viewing Information About Logfiles

To See how many logfile groups are there and their status type the following query.

SQL>SELECT * FROM V$LOG;

To See how many members are there and where they are located give the following query

SQL>SELECT * FROM V$LOGFILE;

Maintaining the Control File

2012-04-02T22:03:00.001+05:30

Maintaining the Control File

Control File

The control file is a small binary file necessary for the database to start and operate successfully. Each control file is associated with only one Oracle database. Before a database is opened, the control file is read to determine if the database is in a valid state to use. A control file is updated continuously by the Oracle server during database use, so it must be available for writing whenever the database is open. The information in the control file can be modified only by the Oracle server; no database administrator or end user can edit the control file. If for some reason the control file is not accessible, the database does not function properly. If all copies of a database’s control files are lost, the database must be recovered before it can be opened. At least one control file is required, but control files can be multiplexed up to eight times.

Control File Contents

• Database name and identifier

• Time stamp of database creation

• Tablespace names

• Names and locations of data files and redo log files

• Current redo log file sequence number

• Checkpoint information

• Begin and end of undo segments

• Redo log archive information

• Backup information

Multiplexing

To safeguard against a single point of failure of the control file, it is strongly recommended that the control file be multiplexed, storing each copy on a different physical disk. If a control file is lost, a copy of the control file can be used to restart the instance without database recovery. Control files can be multiplexed up to eight times. The Oracle server creates and maintains all files listed in this parameter when the instance is started.

Here we can Multiplex controlfile in two ways, if our database using spfile then we can multiplex controlfile using spfile, if our database using pfile then we can multiplex control file using pfile.

Multiplexing the controlfile using spfile

-->Database is in up (Database open and running)
-->If our database consists of 1 control file in the location

Control_file = ‘/Oracle/product/10.2.0/oradata/TEST/ctrl1.ctl’ then we are going to multiplex another control file in another location like this show below.
-->Sql> alter system set control_files = ‘/Oracle/product/10.2.0/oradata/TEST/ctrl1.ctl’,’/u01/TEST/ctrl2.ctl’ SCOPE=SPFILE;
-->Sql>shutdown
-->By issuing alter system set command we can modify that spfile dynamically, because spfile is a binary file we cannot modify that file manually, so we have to modify that spfile dynamically. We just modify that file but physically ctrl2.ctl doesn’t exist at that location /u01/TEST. We have to copy the old controlfile to that location.
-->$ cp /Oracle/product/10.2.0/oradata/TEST/ctrl1.ctl /u01/TEST/ctrl2.ctl
-->After copying the old control file to new control file then we have to startup the database
-->Sql>startup

è Then automatically new controlfile also updated by oracle server.

è Like that we can multiplex up to eight control files

Multiplexing the controlfile using pfile(init.ora)

-->If our database using pfile then we can easily edit that pfile. To edit that pfile first we have to shutdown the database.
-->Sql>shutdown
-->Edit the pfile

$ vi initTEST.ora

In that parameter file

Control_files = ‘/Oracle/product/10.2.0/oradata/TEST/ctrl1.ctl’,’/u01/TEST/ctrl2.ctl’

Save and exit (:wq)
-->We just modify the parameter, but physically new control file doesn’t exist so we have to copy the old control file to new control file in another new location
-->$ cp /Oracle/ product/10.2.0/oradata/TEST/ctrl1.ctl /u01/TEST/ctrl2.ctl
-->After copying the control file we have to run the database or startup the database then new controlfile is updated by oracle server automatically
-->Sql>startup

How to create controlfile

we can create controlfile using the command

sql>alter database backup controlfile to trace;

-->After giving this statement oracle will write the CREATE CONTROLFILE statement in a trace file. The trace file will be randomly named something like ORA23212.TRC and it is created in USER_DUMP_DEST directory.

-->Go to the USER_DUMP_DEST directory and open the latest trace file in text editor. This file will contain the CREATE CONTROLFILE statement. It will have two sets of statement one with RESETLOGS and another without RESETLOGS. Since we are changing the name of the Database we have to use RESETLOGS option of CREATE CONTROLFILE statement. Now copy and paste the statement in a file. Let it be c.sql

Obtaining Control File Information

To obtain the location and names of the control files, use the dynamic performance view V$CONTROLFILE.

Sql>SELECT name FROM V$CONTROLFILE;

NAME

------------------------------------

/u01/home/db03/ORADATA/u01/ctrl01.ctl

2 rows selected.

The V$PARAMETER view can also be used.

Sql>SELECT name, value from V$PARAMETER

WHERE name = 'control_files';

NAME Value

------------- -------------------------------------

control_files /u01/home/db03/ORADATA/u01/ctrl01.ctl

sql>show parameters control_files;

NAME TYPE VALUE

------------ ------- ------------------------------

control_files string /u01/home/db03/ORADATA/u01/ctrl01.ctl

To obtain information about the different sections of the control files, query the

V$CONTROLFILE_RECORD_SECTION dynamic performance view.

Sql>SELECT type, record_size, records_total, records_used

FROM v$controlfile_record_section

WHERE type=’DATAFILE’;

TYPE RECORD_SIZ RECORDS_TO RECORDS_US

------- ---------- -------- ------------------

DATAFILE 180 30 4

1 row selected.

Creating a Database

2012-04-02T22:00:00.003+05:30

Creating a Database

Managing and Organizing a Database

• Creating a database is the first step in managing a database system

• A database may have been created automatically as part of Oracle9i Server installation, or you can create a new one later

• Oracle Data Migration Assistant is used to migrate from an earlier version of the database

Creation of Database:

Here we can create Database in two methods:

· Manually

· DBCA(Database Configuration Assistant

Creation of Database using Manually

Step 1:

First create all the necessary directories. Followings are my directories:

For eg : now I’m going to create new database i.e., TEST then we have to create new directories in

$cd /Oracle/product/10.2.0/db_1/admin/

Create TEST directory in that location

$mkdir TEST

Go to TEST directory

$cd TEST

And create remaining directories

$mkdir adump bdump cdump udump arch

Similarly we have to create new directory in oradata

$cd /Oracle/product/10.2.0/oradata

Create TEST directory here

$mkdir TEST

Step 2:

Next prepare the database creation script. Following is my script "TEST.sql"

CREATE DATABASE TEST

MAXLOGFILES 5

MAXLOGMEMBERS 5

MAXDATAFILES 100

MAXINSTANCES 1

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '/Oracle/product/10.2.0/oradata/TEST/redo1.log' SIZE 10M,

GROUP 2 '/Oracle/product/10.2.0/oradata/TEST/redo2.log' SIZE 10M,

GROUP 3 '/Oracle/product/10.2.0/oradata/TEST/redo3.log' SIZE 10M

DATAFILE

'/Oracle/product/10.2.0/oradata/TEST/system.dbf' size 100m,

'/Oracle/product/10.2.0/oradata/TEST/usr04.dbf' size 10m

sysaux datafile '/Oracle/product/10.2.0/oradata/TEST/sysaux.dbf' size 100m

undo tablespace undotbs

datafile '/Oracle/product/10.2.0/oradata/TEST/undo.dbf' size 50m

CHARACTER SET US7ASCII;

Save this TEST.sql in any location your wish here I’m going to save this file in $ORACLE_HOME/dbs location

Step 3:

Prepare the init file. Like this one [initTEST.ora]

For eg I have old parameter file initPROD.ora, then I need to copy that file as

$cp initPROD.ora initTEST.ora

After copying we need to modify that file by replacing PROD as TEST

$vi initTEST.ora

*.audit_file_dest='/Oracle/product/10.2.0/db_1/admin/TEST/adump'

*.background_dump_dest='/Oracle/product/10.2.0/db_1/admin/TEST/bdump'

*.compatible='10.2.0.3.0'

*.control_files='/Oracle/product/10.2.0/oradata/TEST/control01.ctl', '/Oracle/product/10.2.0/oradata/TEST/control02.ctl','/Oracle/product/10.2.0/oradata/TEST/control03.ctl'

*.core_dump_dest='/Oracle/product/10.2.0/db_1/admin/TEST/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='TEST'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=indiaXDB)'

*.job_queue_processes=10

*.log_archive_dest_1='LOCATION=’/Oracle/product/10.2.0/db_1/admin/TEST/arch’

*.log_archive_format='%t_%s_%r.dbf'

*.open_cursors=300

*.pga_aggregate_target=200278016

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=601882624

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS'

*.user_dump_dest='/Oracle/product/10.2.0/db_1/admin/TEST/udump'

*.db_recovery_file_dest='/Oracle/product/10.2.0/db_1/flash_recovery_area’

*.db_recovery_file_dest_size=2147483648

Step 4:

Now perform the following steps:

Export env variables

$ export ORACLE_SID=TEST

$ export ORACLE_HOME=/Oracle/product/10.2.0/db_1

$ export PATH=/Oracle/product/10.2.0/db_1/bin:$PATH

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Thu May 22 17:35:28 2008

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 603979776 bytes

Fixed Size 1263176 bytes

Variable Size 167774648 bytes

Database Buffers 427819008 bytes

Redo Buffers 7122944 bytes

SQL> @TEST.sql

Database created.

Step 5:

So your database is create. Now just run the catalog.sql, catproc.sql and pupbld.sql scripts.

You will find the in $ cd $ORACLE_HOME/rdbms/admin

SQL> @/Oracle/product/10.2.0/db_1/rdbms/admin/catalog.sql

SQL> @/Oracle/product/10.2.0/db_1/rdbms/admin/catproc.sql

SQL>conn system/manager

Connected

SQL>@/Oracle/product/10.2.0/db_1/sqlplus/admin/pupbld.sql

SQL> select name from v$database;

NAME

---------

TEST

Okay, now your database is ready to use.

Creation of Database using DBCA

To create database using DBCA we need to execute this command using root user

$ xhost +

After that logon to our oracle os user

$ su – Oracle

Start the database creation process by running the command dbca. Before that, please ensure that the Oracle Home is set propely.

$ export ORACLE_HOME=/Oracle/product/10.2.0/db_1

$ export PATH=/Oracle/product/10.2.0/db_1/bin:$PATH

$ dbca

The DBCA appears on the screen as below.

Choose the option to create a database.

Opt for custom database creation where you can choose to create a custom database.

Give an appropriate name to the database.

If you have a grid control setup, let the dbca register the database to the grid.

The choice of file system or an ASM solely depends on the kind of infrastructure available.

This is the location where you can specify the name of all the files. The location of files can be further controlled by when we get the tablespace, redolog and controlfiles option.

The use of the Flash Recovery Area or Archive log destination depends on for what purpose you plan to use the database. If it is a system testing or a development environment, then the option might be skipped.

Depending on the need, you can configure the components for the database. If you need XA components, then JVM also needs to be made available. If you need a vanilla database, simply check out all the options.

Often, with DBCA, the components like XML, JVM get installed. These options are considered as the standard options. Click on the "Standard Database Components" tab as show above. This will take you to the options below.

The next section is the database sizing where memory parameter, storage parameters, the charachter sets may be taylored. If your system uses multiple connections and a OLTP kind of an environment, then you may go for a shared server connections. However, in most of the cases, the dedicated server connection will be the preferred one.

You can customize your database content with the type of charachterset you prefer.

This section results into the location/sizing of datafiles, controlfiles and the redolog files.

The final part would be to get a confirmation as to what you have done so far.

These are the simple steps to create the database with DBCA. This also happens to be one of the first chapters you need to study for your Oracle Certifications(Oracle Certified Associate(OCA)).

The choice of DBCA mainly depends on the type of environment you prefer.. It is mostly used for development and testing environments where the purpose of the database is to be a simple back end database with optimum performance. DBCA may be used a standard build tool to create a database once it is determined what what standards are acceptable.

Managing an Oracle Instance

2012-04-02T21:40:00.001+05:30

Managing an Oracle Instance

Initialization Parameter Files

To start an instance, the Oracle server must read the initialization parameter file. Two types of

Initialization parameter files exist:

• Static parameter file, PFILE, commonly referred to as initSID.ora

• Persistent parameter file, SPFILE, commonly referred to as spfileSID.ora

Parameter File Contents

• A list of instance parameters

• The name of the database the instance is associated with

• Allocations for memory structures of the System Global Area (SGA)

• What to do with filled online redo log files

• The names and locations of control files

• Information on undo segments

PFILE

The PFILE is a text file that can be maintained using a standard operating system editor. The parameter file is read only during instance startup. If the file is modified, the instance must be shut down and restarted in order to make the new parameter values effective. Some parameters are dynamic, which means that they can be modified while the instance is running. Changes to dynamic parameters are not reflected in the PFILE. By default, the PFILE is located in the $ORACLE_HOME/dbs directory on a Unix machine and named initSID.ora. A sample init.ora file is created by the Universal Installer during installation. This sample init.ora file can be used to create an instance-specific initSID.ora.

# Initialization Parameter File: initdb01.ora

db_name = db01

instance_name = db01

control_files = ( /u03/oradata/db01/control01db01.ctl,

/u03/oradata/db01/control02db01.ctl)

db_block_size = 4096

db_block_buffers = 500

shared_pool_size = 31457280 # 30M Shared Pool

db_files = 1024

max_dump_file_size = 10240

background_dump_dest = /u05/oracle9i/admin/db01/bdump

user_dump_dest = /u05/oracle9i/admin/db01/udump

core_dump_dest = /u05/oracle9i/admin/db01/cdump

undo_management = auto

undo_tablespace = undtbs

SPFILE

SPFILE, new to Oracle9i, is a binary file. The file is not meant to be modified manually and must always reside on the server side. By default, the file is located in $ORACLE_HOME/dbs and has a default name in the format of spfileSID.ora. Once the file is created it is maintained by the Oracle server. The SPFILE provides the ability to make changes to the database persistent across shutdown and startup.

The ALTER SYSTEM command is used to change the value of instance parameters. The SCOPE setting determines the scope of the change.

• MEMORY: Changes the parameter value only in the currently running instance

• SPFILE: Changes the parameter value in the SPFILE only

• BOTH: Changes the parameter value in the currently running instance and the SPFILE

ALTER SYSTEM SET parameter = value [SCOPE = MEMORY|SPFILE|BOTH]

The SPFILE can be modified with the ALTER SYSTEM command when the instance starts

Using the SPFILE or using the PFILE with the parameter SPFILE.

Creating an SPFILE

An SPFILE is created from an initSID.ora file using the CREATE SPFILE command.

This can be executed before or after the database is open.

CREATE SPFILE FROM PFILE;

*.background_dump_dest='$ORACLE_HOME/admin/db01/bdump'

*.compatible='9.0.0'

*.control_files='/u03/oradata/db01/ctrl01db01.ctl','/u03/orad

ata/db01/ctrl02db01.ctl'

*.core_dump_dest='$ORACLE_HOME/admin/db01/cdump'

*.db_block_buffers=500

*.db_block_size=4096

*.db_files=40

*.db_name='db01'

*.instance_name='db01'

*.remote_login_passwordfile='exclusive'

*.shared_pool_size=31457280 # 30M Shared Pool

*.undo_management='AUTO'

db01.undo_tablespace='UNDOTBS01'

db02.undo_tablespace='UNDOTBS02'

Starting Up a Database

When starting the database, you select the state in which it starts. The following scenarios

describe different stages of starting up an instance.

Starting up a database (NOMOUNT)

Usually you would start an instance without mounting a database only during database creation or the re-creation of control files.

Starting an instance includes the following tasks:

• Reading the initialization file from $ORACLE_HOME/dbs in the following order:

– First spfileSID.ora. If not found then

– spfile.ora

– initSID.ora

• Specifying the PFILE parameter with STARTUP overrides the default behavior

• Allocating the SGA

• Starting the background processes

• Opening the alertSID.log file and the trace files

The database must be named with the DB_NAME parameter either in the initialization file or

in the STARTUP command.

Starting up a database (MOUNT)

To perform specific maintenance operations, you start an instance and mount a database but do not open the database.

For example, the database must be mounted but not open during the following tasks:

• Renaming data files

• Enabling and disabling redo log archiving options

• Performing full database recovery

Mounting a database includes the following tasks:

• Associating a database with a previously started instance

• Locating and opening the control files specified in the parameter file

• Reading the control files to obtain the names and status of the datafiles and redo log

files. (However, no checks are performed to verify the existence of the data files and online redo log files at this time.)

Starting up a database (OPEN)

Normal database operation means that an instance is started and the database is mounted and open; with normal database operation, any valid user can connect to the database and perform typical data access operations. Opening the database includes the following tasks:

• Opening the online data files

• Opening the online redo log files

If any of the data files or online redo log files are not present when you attempt to open the Database, the Oracle server returns an error. During this final stage, the Oracle server verifies that all the data files and online redo log files can be opened and checks the consistency of the database. If necessary, the System Monitor background process (SMON) initiates instance recovery.

FORCE --> aborts the running instance before performing a normal startup

RESTRICT --> enables only users with RESTRICTED SESSION privilege to access the database

RECOVER --> begins media recovery when the database starts

The ALTER DATABASE Command

• Change the state of the database from NOMOUNT to MOUNT:

ALTER DATABASE db01 MOUNT;

• Open the database as a read-only database:

ALTER DATABASE db01 OPEN READ ONLY;

READ WRITE --> opens the database in read-write mode, so that users can generate redo logs

READ ONLY --> restricts users to read-only transactions, preventing them from generating redo log information

Opening a Database in Restricted Mode

• Use the STARTUP command to restrict access to a database:

STARTUP RESTRICT

• Use the ALTER SYSTEM command to place an instance in restricted mode:

ALTER SYSTEM ENABLE RESTRICTED SESSION;

Shutdown Options

Shutting Down the Database

Shutdown Mode	A	I	T	N
Allow new connections	x	x	x	x
Wait until current sessions end	x	x	x	o
Wait until current transactions end	x	x	o	o
Force a checkpoint and close files	x	o	o	o

Shutdown Mode:

NO • NORMAL --> N

YES • TRANSACTIONAL --> T

• IMMEDIATE --> I

• ABORT --> A

Managing an Instance by Monitoring Diagnostic Files

Diagnostic files contain information about significant events encountered while the instance is operational.

• Used to resolve problems or to better manage the database on a day-to-day basis.

• Several types of diagnostic files exist:

– alertSID.log file -->Information for day-to-day operation of the database, Each entry has a time stamp associated with it. Its location is defined by BACKGROUND_DUMP_DEST

– Background trace files -->Vital information when background processes, such as SMON,

PMON, DBWn, and others fail. Their location is defined by BACKGROUND_DUMP_DEST

– User trace files --> Vital information for fatal user errors or user forced traced files. Its location is defined by USER_DUMP_DEST

Oracle Server Installation

2012-03-26T22:23:00.000+05:30

Installation of Oracle 10gR2(10.2.0.1) on OEL(Oracle Enterprise Linux)

On Linux

Create Partitions
Formating using ext3
Mount points on a file system /u01 /u02 /u03 /opt /var

Steps to Install Oracle

Step 1

Creation of Group and User

$groupadd dba
$useradd -g dba -d /home/oracle

Step 2

Creation of Directory Structure

$mkdir -p /u01/Oracle/product/10.2.0/db_1

here option -p indicates --> it creates directories recursively

change ownership and permissions of that directory structure

$chown -R Oracle:dba /u01/Oracle

Step 3

Setting Kernel Parameters

$ vi /etc/sysctl.conf

if we modify kernel parameters then we have to restart the system or execute this command

/sbin/sysctl

Step 4

Assign read write execute permissions of Oracle 10g software to Oracle user because to install software we need permissions

Step 5

xhost + (This command enables gui display settings)

su - Oracle

cd /Oracle10gsoftware/database

./runInstaller

Step 6

Post Installation Script - After successful installation we must execute the following script from root level

/u01/Oracle/product/10.2.0/db_1/root.sh

***********************************************************************************

Oracle Architectural Components

2012-03-26T21:47:00.000+05:30

This topic introduces the Oracle server architecture by examining the physical, memory, process, and logical structures involved in establishing a database connection, creating a session, and executing SQL commands.

Here we use Oracle 10g R2 (10.2.0.1)

10.2.0.1 --> means Patchset

10-->Major Version
2 -->Maintainance Release number
0 -->Application Server Release number
1 -->Component specific number

Overview of Primary Components:

What is Oracle Server ?

There are several files, processes, and memory structures in an Oracle server; however, not all of them are used when processing a SQL statement. Some are used to improve the performance of the database, ensure that the database can be recovered in the event of a software or hardware error, or perform other tasks necessary to maintain the database. The Oracle server consists of an Oracle instance and an Oracle Database

What is Oracle Instance ?

An Oracle instance is the combination of the background processes and memory structures. The instance must be started to access the data in the database. Every time an instance is started, a System Global Area (SGA) is allocated and Oracle background processes are started. Background processes perform functions on behalf of the invoking process. They consolidate functions that would otherwise be handled by multiple Oracle programs running for each user. The background processes perform input/output (I/O) and monitor other Oracle processes to provide increased parallelism for better performance and reliability.

What is Oracle Database ?

An Oracle database consists of operating system files, also known as database files, that provide the actual physical storage for database information. The database files are used to ensure that the data is kept consistent and can be recovered in the event of a failure of the instance.

An Oracle database consists of three file types

Data files containing the actual data in the database
Redo logs containing a record of changes made to the database to enable recovery of the data in case of failures
Control files containing information necessary to maintain and verify database integrity

Other Key File Structures

The Oracle server also uses other files that are not part of the database:
• The parameter file defines the characteristics of an Oracle instance. For example, it contains parameters that size some of the memory structures in the SGA.
• The password file authenticates users privileged to start up and shut down an Oracle instance.
• Archived redo log files are offline copies of the redo log files that may be necessary to recover from media failures.

System Global Area (SGA)

The SGA is also called the shared global area. It is used to store database information that is shared by database processes. It contains data and control information for the Oracle server and is allocated in the virtual memory of the computer where Oracle resides. The following statement can be used to view SGA memory allocations:

SHOW SGA;

Total System Global Area    36437964 bytes
Fixed Size                              6543794 bytes
Variable Size 19521536 bytes
Database Buffers    16777216 bytes
Redo Buffers                         73728 bytes

• The SGA consists of several memory structures:

– Shared pool
– Database buffer cache
– Redo log buffer
– Other structures (e.g. lock and latch management, statistical data)

• There are two optional memory structures that can be configured within the SGA:

– Large pool
– Java pool

Shared pool

The shared pool environment contains both fixed and variable structures. The fixed structures remain relatively the same size, whereas the variable structures grow and shrink based on user and program requirements. The actual sizing for the fixed and variable structures is based on an initialization parameter and the work of an Oracle internal algorithm.

It consists of two key performance-related memory structures:
– Library cache
– Data dictionary cache

Library cache

The library cache stores information about the most recently used SQL and PL/SQL statements. The library cache:
• Enables the sharing of commonly used statements
• Is managed by a least recently used (LRU) algorithm
• Consists of two structures:
– Shared SQL area
– Shared PL/SQL area
• Has its size determined by the shared pool sizing

Data Dictionary Cache

The data dictionary cache is a collection of the most recently used definitions in the database.
• It includes information about database files, tables, indexes, columns, users, privileges, and
other database objects.
• During the parse phase, the server process looks at the data dictionary for information to resolve
object names and validate access.
• Caching the data dictionary information into memory improves response time on queries.
• Size is determined by the shared pool sizing.

Database Buffer Cache

The database buffer cache stores copies of data blocks that have been retrieved from the data files.
• It enables great performance gains when you obtain and update data.
• It is managed through a least recently used (LRU) algorithm.
• DB_BLOCK_SIZE determines the primary block size

Redo Log Buffer Cache

The redo log buffer cache records all changes made to the database data blocks.
• Its primary purpose is recovery.
• Changes recorded within are called redo entries.
• Redo entries contain information to reconstruct or redo changes.
• Size is defined by LOG_BUFFER.

Large Pool

The large pool is an optional area of memory in the SGA configured only in a shared server environment.
• It relieves the burden placed on the shared pool.
• This configured memory area is used for session memory (UGA), I/O slaves, and backup and restore operations.
• Unlike the shared pool, the large pool does not use an LRU list.
• Sized by LARGE_POOL_SIZE.
ALTER SYSTEM SET LARGE_POOL_SIZE = 64M;

Java Pool

The Java pool services the parsing requirements for Java commands.
• Required if installing and using Java.
• It is stored much the same way as PL/SQL in database tables.
• It is sized by the JAVA_POOL_SIZE parameter.

Program Global Area(PGA)

The Program Global Area or Process Global Area (PGA) is a memory region that contains data and control information for a single server process or a single background process. The PGA is allocated when a process is created and deallocated when the process is terminated. In contrast to the SGA, which is shared by several processes, the PGA is an area that is used by only one process.

Background Processes

The relationship between the physical and memory structures is maintained and enforced by Oracle’s background processes.
• Mandatory background processes
DBWn

PMON

CKPT
LGWR

SMON

RECO
• Optional background processes
ARCn

LMON

Snnn
QMNn

LMDn
CJQ0

Pnnn
LCKn

Dnnn

Database Writer (DBWn)

The server process records changes to rollback and data blocks in the buffer cache. Database Writer (DBWn) writes the dirty buffers from the database buffer cache to the data files. It ensures that a sufficient number of free buffers—buffers that can be overwritten when server processes need to read in blocks from the data files—are available in the database buffer cache. Database performance is improved because server processes make changes only in the buffer cache.
DBWn defers writing to the data files until one of the following events occurs:
• Incremental or normal checkpoint
• The number of dirty buffers reaches a threshold value
• A process scans a specified number of blocks when scanning for free buffers and cannot fine any.
• Timeout occurs.
• A ping request in Real Application Clusters environment.
• Placing a normal or temporary tablespace offline.
• Placing a tablespace in read only mode.
• Dropping or Truncating a table.
• ALTER TABLESPACE tablespace name BEGIN BACKUP

Log Writer (LGWR)

LGWR performs sequential writes from the redo log buffer cache to the redo log file under the following situations:
• When a transaction commits
• When the redo log buffer cache is one-third full
• When there is more than a megabyte of changes records in the redo log buffer cache
• Before DBWn writes modified blocks in the database buffer cache to the data files
• Every 3 seconds.
Because the redo is needed for recovery, LGWR confirms the commit only after the redo is
written to disk.
LGWR can also call on DBWn to write to the data files.
Note: DBWn does not write to the online redo logs.

System Monitor (SMON)

If the Oracle instance fails, any information in the SGA that has not been written to disk is lost. For example, the failure of the operating system causes an instance failure. After the loss of the instance, the background process SMON automatically performs instance recovery when the database is reopened. Instance recovery consists of the following steps:

1. Rolling forward to recover data that has not been recorded in the data files but that has been recorded in the online redo log. This data has not been written to disk because of the loss of the SGA during instance failure. During this process, SMON reads the redo log files and applies the changes recorded in the redo log to the data blocks. Because all committed transactions have been written to the redo logs, this process completely recovers these transactions.
2. Opening the database so that users can log on. Any data that is not locked by unrecovered transactions is immediately available.
3. Rolling back uncommitted transactions. They are rolled back by SMON or by the individual server processes as they access locked data. SMON also performs some space maintenance functions:
• It combines, or coalesces, adjacent areas of free space in the data files.
• It deallocates temporary segments to return them as free space in data files. Temporary segments are used to store data during SQL statement processing.

Process Monitor (PMON)

The background process PMON cleans up after failed processes by:
• Rolling back the user’s current transaction
• Releasing all currently held table or row locks
• Freeing other resources currently reserved by the user

Archiver (ARCn)

All other background processes are optional, depending on the configuration of the database; however, one of them, ARCn, is crucial to recovering a database after the loss of a disk. As online redo log files fill, the Oracle server begins writing to the next online redo log file. The process of switching from one redo log to another is called a log switch. The ARCn process initiates backing up, or archiving, of the filled log group at every log switch. It automatically archives the online redo log before the log can be reused, so that all of the changes made to the database are preserved. This enables the DBA to recover the database to the point of failure, even if a disk drive is damaged.

Archiving Redo Log Files

One of the important decisions that a DBA has to make is whether to configure the database to operate in ARCHIVELOG or in NOARCHIVELOG mode.

NOARCHIVELOG Mode: In NOARCHIVELOG mode, the online redo log files are overwritten each time a log switch occurs. LGWR does not overwrite a redo log group until the checkpoint for that group is complete. This ensures that committed data can be recovered if there is an instance crash. During the instance crash, only the SGA is lost. There is no loss of disks, only memory. For example, an operating system crash causes an instance crash.

ARCHIVELOG Mode: If the database is configured to run in ARCHIVELOG mode, inactive groups of filled online redo log files must be archived before they can be used again. Since changes made to the database are recorded in the online redo log files, the database administrator can use the physical backup of the data files and the archived online redo log files to recover the database without losing any committed data because of any single point of failure, including the loss of a disk. Usually, a production database is configured to run in ARCHIVELOG mode.

Logical Structure

A logical structure hierarchy exists as follows:
• An Oracle database is a group of tablespaces.
• A tablespace may consist of one or more segments.
• A segment is made up of extents.
• An extent is made up of logical blocks.
• A block is the smallest unit for read and write operations.

Upgrade Database from Oracle 10.2.0.4 To Oracle 11gR1

2012-03-26T20:34:00.000+05:30

Upgradation of Oracle 10g(10.2.0.4.0) to Oracle 11g (11.1.0.6.0):

We cannot upgrade Oracle software from 10g to 11g like upgrading 10.2.0.1.0 to 10.2.0.4.0 , because it is not a patchset.

So here we need to upgrade database from 10g to 11g.

below steps are for upgrading database from 10g to11g

Assume that we have already installed Oracle 10g s/w in our system with database PROD.

Oracle 10g home -->/u01/Oracle/product/10.2.0/db_1

(here Oracle 10g os user is oracle)

then

Step1:

we have to download Oracle 11gR1 software from metalink site using username and password.

unzip that Oracle 11g r1 s/w

Unzip linux_11gr1.zip

Then it generates database directory. Then we have to change the ownership of that s/w or set the appropriate permissions to our user

Chown –R oracle:dba /Oracle11g/database

Before installing software ,we need to check prerequisites for installing Oracle 11g. so we need to verify the installation guide

-->we have to check the hardware requirements

-->we need to create appropriate directories

-->we need to check kernel parameters

xhost +

Then su – oracle

Cd /database

./runInstaller

We need to select directory where to install s/w and install s/w only without database

-->assume that oracle11g home --> /u01/Oracle/product/11.1.0/db_1

After successful completion of installation

Step 2: Prerequisites for Oracle 10g to 11g Upgradation

In Oracle 11g home --> /uo1/Oracle/product/11.1.0/db_1/rdbms/admin/

There is one script i.e., utlu111i.sql(which is pre upgrade information tool)

Then run that script from Oracle 10g database

Export ORACLE_HOME=/u01/Oracle/product/10.2.0/db_1

Export ORACLE_SID=PROD

Export PATH=/u01/Oracle/product/10.2.0/db_1/bin:$PATH

After exporting env variables

-->sqlplus ‘/as sysdba’

Startup

@u01/Oracle/product/11.1.0/db_1/rdbms/admin/utlu111i.sql

Then it generates the pre upgrade information tool.. inorder to upgrade the database we need to solve those warnings which are generated after running the preupgrade information tool

Here after running utlu111i.sql script I got some warnings

Warning: Data Timezon version error

Means if the data timezone version is <4 at that time only this timezone warning is generated.

So to solve this issue we need to download utltzpv4.sql from metalink

And run that script

Sql>@utltzpv4.sql

Warning: Obsolete Parameters

Background_dump_dest -à diagnostic_dest

User_dump_dest-àdiagnostic_dest

Core_dump_destàdiagnostic_dest

To solve this problem we need to remove those adump,bdump,cdump and udump from the parameter file and replace with single diagnostic_dest

Warning: Database contains stale optimizer statistics

Component schemas with stale statistics..

SYS

SYSMAN

To solve this problem

Connect as sys user and gather statistics

Conn /as sysdba

Connected

Sql> Exec DBMS_STATS.GATHER_DICTIONARY_STATS;

Sql>Exec DBMS_STATS.GATHER_SCHEMA_STATS(‘SYS);

Sql>Exec DBMS_STATS.GATHER_SCHEMA_STATS(‘SYSMAN’);

Warning: Invalid objects

To solve this issue run the utlrp.sql (recompile)

Sql>@?/rdbms/admin/utlrp.sql

It recompiles the objects

To verify that there is invalid objects are not

Sql>select count(*) from dba_object where status = ‘INVALID’

These are some warnings. We need to solve those problems to perform upgradation. Because it reduces the time when upgrading.

Step 3:

After successful completion of prerequisites of upgradation.

We need to copy the parameter file (pfile ) of Oracle 10 database prod to Oracle 11g HOME/dbs location

And edit that pfile

In pfile

Db_compatible = ’11.1.0’

Remove adump,bdump,cdump,udump

Replace with diagnostic_dest=’/u01/Oracle/product/diagns’

Export ORACLE_HOME=/u01/Oracle/product/11.1.0/db_1

Export ORACLE_SID=PROD

Export PATH=/u01/Oracle/product/11.1.0/db_1/bin:$PATH

After exporting the env variables

Sqlplus ‘/as sysdba’

Sql>startup upgrade;

Sql>@?/rdbms/admin/catupgrd.sql

This script upgrades the database it takes lot of time

For me it takes 49 minutes because my database is very small so it takes 49 minutes

After successfully completion of this script

Sql>@?/rdbms/admin/utlu111s.sql

This script shows the post upgrade status

It shows the entire info after upgradations and how much time it takes to upgrade the database

Sql>@?/rdbms/admin/catuppst.sql

Sql>@?/rdbms/admin/utlrp.sql

Sql>select comp_name, version, status from sys.dba_registry;

Sql>select count(*) from dba_objects where status = ‘INVALID’;

Sql>select * from v$version;

---------------------------------------This is the Upgradations Steps --------------------------------------

Upgrade Database from Oracle 10.2.0.1 To 10.2.0.4

2012-03-26T20:18:00.000+05:30

Upgrade Database from Oracle 10.2.0.1 To 10.2.0.4

Requirements:

Oracle database 10g patch set 10.2.0.4 download from

www.metalink.oracle.com

Step 1: Prerequisites check to before applying the patch set (6810189).

Check your dba_registry view oracle database components status is

valid or not, version is 10.2.0.1. Have a look at the below output

To Check database components status is valid or not and version is 10.2.0.1

sql>Column comp_name format a40

sql>Column version format a12

sql>Column status format a6

sql>Select comp_name, version, status from sys.dba_registry;

COMP_NAME VERSION STATUS

———————————————– ———— ——

Oracle Database Catalog Views 10.2.0.1.0 VALID

Oracle Database Packages and Types 10.2.0.1.0 VALID

Oracle Workspace Manager 10.2.0.1.0 VALID

JServer JAVA Virtual Machine 10.2.0.1.0 VALID

Oracle XDK 10.2.0.1.0 VALID

Oracle Database Java Packages 10.2.0.1.0 VALID

Oracle Expression Filter 10.2.0.1.0 VALID

Oracle Data Mining 10.2.0.1.0 VALID

Oracle Text 10.2.0.1.0 VALID

Oracle XML Database 10.2.0.1.0 VALID

Oracle Rules Manager 10.2.0.1.0 VALID

Oracle interMedia 10.2.0.1.0 VALID

OLAP Analytic Workspace 10.2.0.1.0 VALID

Oracle OLAP API 10.2.0.1.0 VALID

OLAP Catalog 10.2.0.1.0 VALID

Spatial 10.2.0.1.0 VALID

Oracle Enterprise Manager 10.2.0.1.0 VALID

17 rows selected.

Check your v$version view

SQL> select * from v$version;

BANNER

—————————————————————-

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod

PL/SQL Release 10.2.0.1.0 – Production

CORE 10.2.0.1.0 Production

TNS for Linux: Version 10.2.0.1.0 – Production

NLSRTL Version 10.2.0.1.0 – Production

Check your database objects are valid or not

SQL> select object_name,status from dba_objects where status=’INVALID’;

No rows selected

In my environment there are no invalid objects.

If you have any invalid objects then you have to really Validate your database objects through the below scripts

SQL> exec utl_recomp.recomp_serial (); -> this is for all user’s objects across the database

Then stop all the process like sql, emctl, tnslistener and shutdown your database.

$ sqlplus / as sysdba

SQL> shutdown immediate

Database closed

Database dismounted.

ORACLE instance shut down.

$ isqlplusctl stop

iSQL*Plus 10.2.0.1.0

Stopping iSQL*Plus …

iSQL*Plus stopped.

$ emctl stop dbconsole

TZ set to US/Eastern

Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0

http://ora10.home.com:1158/em/console/aboutApplication

Stopping Oracle Enterprise manager 10g database Control

….Stoped

$ lsnrctl stop

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))

The command completed successfully

Step 2:

Cold Backup of your oracle database 10g and $ORACLE_HOME using Operating system

Command

Step 3:

Manage your data with Time Zone before upgrade

In my environment there are no data and scheduler jobs with Time Zone

information that’s why I skip the step. If you have huge volume of data and

scheduler jobs means please follow the below steps

SQL> select version from v$timezone_file;

Version

———-

If this gives 4 then you may simply proceed with the upgrade even if you have TZ data.
If this gives higher then 4 look at the Meta link note: Note 553812.1
If this gives lower then 4 perform the following steps:

Download utltzpv4.sql from www.metalink.oracle.com and run it.

SQL> @utltzpv4.sql

DROP TABLE sys.sys_tzuv2_temptab CASCADE CONSTRAINTS

ERROR at line 1:

ORA-00942: table or view does not exist

Table created.

DROP TABLE sys.sys_tzuv2_affected_regions CASCADE CONSTRAINTS

ERROR at line 1:

ORA-00942: table or view does not exist

Table created.

Your current timezone version is 2!

Do a select * from sys.sys_tzuv2_temptab; to see if any TIMEZONE

data is affected by version 4 transition rules.

Any table with YES in the nested_tab column (last column) needs

a manual check as these are nested tables.

PL/SQL procedure successfully completed.

Commit complete.

Once the script finishes successfully execute the following query

sql>column table_owner format a4

sql>column column_name format a18

sql>select * from sys_tzuv2_temptab;

TABL TABLE_NAME COLUMN_NAME ROWCOUNT NES

---- ------------------------------ ------------------ ----------

SYS SCHEDULER$_JOB LAST_ENABLED_TIME 3

SYS SCHEDULER$_JOB LAST_END_DATE 1

SYS SCHEDULER$_JOB LAST_START_DATE 1

SYS SCHEDULER$_JOB NEXT_RUN_DATE 1

SYS SCHEDULER$_JOB START_DATE 1

SYS SCHEDULER$_JOB_RUN_DETAILS REQ_START_DATE 1

SYS SCHEDULER$_JOB_RUN_DETAILS START_DATE 1

SYS SCHEDULER$_WINDOW LAST_START_DATE 2

SYS SCHEDULER$_WINDOW NEXT_START_DATE 2

9 rows selected.

Step 3: Applying Oracle database 10g patch set (Patch number 6810189)

After downloaded the patch set, you have login as a root user and execute the following

# xhost +SI:localuser:oracle

Install the patch set 10.2.0.4

$ cd /home/oracle

$ unzip p6810189_10204_Linux-x86.zip

$ cd Disk1/

$ ./runInstaller

Click Next to proceed the next step

Here you should provide your $ORACLE_HOME(10.2.0.1) detail

Click Next

Click Install

Click OK and run the above script as a root user

Then startup database with upgrade option otherwise the below error will

come and you can see the error in the alert log file

$ sqlplus / as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 608174080 bytes

Fixed Size 1268896 bytes

Variable Size 171967328 bytes

Database Buffers 427819008 bytes

Redo Buffers 7118848 bytes

Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

SQL> exit

$ tail -f /opt/oracle/admin/test/bdump/alert_ora10g.log

SMON: enabling cache recovery

Fri Jul 2 15:30:15 2010

Errors in file /u01/apps/oracle/admin/ora10g/udump/ora10g_ora_12856.trc:

ORA-00704: bootstrap process failure

ORA-39700: database must be opened with UPGRADE option

Fri Jul 2 15:30:15 2010

Error 704 happened during db open, shutting down database

USER: terminating instance due to error 704

Instance terminated by USER, pid = 12856

ORA-1092 signalled during: ALTER DATABASE OPEN…

Now startup the database with upgrade option and run the pre-upgrade information tool to see if there are any recommendation. If there is any recommendation then you have to really follow the recommendations.

Let’s start the upgrade process.

$ cd $ORACLE_HOME/rdbms/admin

SQL> spool upgrade.log

SQL> @catupgrd.sql

[output trimmed]

Oracle Database 10.2 Upgrade Status Utility 07-02-2010 18:13:40

Component Status Version HH:MM:SS

Oracle Database Server VALID 10.2.0.4.0 00:09:32

JServer JAVA Virtual Machine VALID 10.2.0.4.0 00:03:34

Oracle XDK VALID 10.2.0.4.0 00:00:29

Oracle Database Java Packages VALID 10.2.0.4.0 00:00:39

Oracle Text VALID 10.2.0.4.0 00:00:20

Oracle XML Database VALID 10.2.0.4.0 00:01:29

Oracle Workspace Manager VALID 10.2.0.4.3 00:00:39

Oracle Data Mining VALID 10.2.0.4.0 00:00:18

OLAP Analytic Workspace VALID 10.2.0.4.0 00:00:19

OLAP Catalog VALID 10.2.0.4.0 00:00:50

Oracle OLAP API VALID 10.2.0.4.0 00:00:45

Oracle interMedia VALID 10.2.0.4.0 00:03:48

Spatial VALID 10.2.0.4.0 00:01:29

Oracle Expression Filter VALID 10.2.0.4.0 00:00:09

Oracle Enterprise Manager VALID 10.2.0.4.0 00:01:16

Oracle Rule Manager VALID 10.2.0.4.0 00:00:07

Total Upgrade Time: 00:25:52

DOC>#####################################

DOC>######################################

DOC>

DOC> The above PL/SQL lists the SERVER components in the upgraded

DOC> database, along with their current version and status.

DOC>

DOC> Please review the status and version columns and look for

DOC> any errors in the spool log file. If there are errors in the spool

DOC> file, or any components are not VALID or not the current version,

DOC> consult the Oracle Database Upgrade Guide for troubleshooting

DOC> recommendations.

DOC>

DOC> Next shutdown immediate, restart for normal operation, and then

DOC> run utlrp.sql to recompile any invalid application objects.

DOC>

DOC>##########################################

DOC>######################################

DOC>#

SQL> spool off

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

When the upgrade script catupgrd.sql finishes shutdown the database and open the spool of the upgrade process and try to find out if any thing failed. If you see something failed try to fix it and re-run the upgrade process.

The upgrade process may leave many objects invalid in the database. Perform a normal startup and run the utlrp.sql script to recompile any invalid objects.

$ sqlplus / as sysdba

Connected to an idle instance.

SQL> startup

SQL> spool recompile.log

SQL> @utlrp.sql

TIMESTAMP

==============

COMP_TIMESTAMP UTLRP_BGN 2010-07-02 18:23:116

DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid

DOC> objects in the database. Recompilation time is proportional to the

DOC> number of invalid objects in the database, so this command may take

DOC> a long time to execute on a database with a large number of invalid

DOC> objects.

DOC>

DOC> Use the following queries to track recompilation progress:

DOC>

DOC> 1. Query returning the number of invalid objects remaining. This

DOC> number should decrease with time.

DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);

DOC>

DOC> 2. Query returning the number of objects compiled so far. This number

DOC> should increase with time.

DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;

DOC>

DOC> This script automatically chooses serial or parallel recompilation

DOC> based on the number of CPUs available (parameter cpu_count)

multiplied

DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).

DOC> On RAC, this number is added across all RAC nodes.

DOC>

DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel

DOC> recompilation. Jobs are created without instance affinity so that they

DOC> can migrate across RAC nodes. Use the following queries to verify

DOC> whether UTL_RECOMP jobs are being created and run correctly:

DOC>

DOC> 1. Query showing jobs created by UTL_RECOMP

DOC> SELECT job_name FROM dba_scheduler_jobs

DOC> WHERE job_name like ‘UTL_RECOMP_SLAVE_%’;

DOC>

DOC> 2. Query showing UTL_RECOMP jobs that are running

DOC> SELECT job_name FROM dba_scheduler_running_jobs

DOC> WHERE job_name like ‘UTL_RECOMP_SLAVE_%’;

DOC>#

PL/SQL procedure successfully completed.

TIMESTAMP

=============

COMP_TIMESTAMP UTLRP_END 2010-07-02 18:23:50

PL/SQL procedure successfully completed.

DOC> The following query reports the number of objects that have compiled

DOC> with errors (objects that compile with errors have status set to 3 in

DOC> obj$). If the number is higher than expected, please examine the error

DOC> messages reported with each object (using SHOW ERRORS) to see if they

DOC> point to system misconfiguration or resource constraints that must be

DOC> fixed before attempting to recompile these objects.

DOC>#

OBJECTS WITH ERROR

===============

DOC> The following query reports the number of errors caught during

DOC> recompilation. If this number is non-zero, please query the error

DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors

DOC> are due to misconfiguration or resource constraints that must be

DOC> fixed before objects can compile successfully.

DOC>#

ERRORS DURING RECOMPILATION

---------------------------

PL/SQL procedure successfully completed.

SQL> spool off

Step 5: Post installation steps

sql>set lines 10000

sql>set pages 1000

sql>column comp_name format a40

sql>column version format a12

sql>column status format a6

sql>select comp_name, version, status from sys.dba_registry;

COMP_NAME VERSION STATUS

============ =========== =====

Oracle Database Catalog Views 10.2.0.4.0 VALID

Oracle Database Packages and Types 10.2.0.4.0 VALID

Oracle Workspace Manager 10.2.0.4.3 VALID

JServer JAVA Virtual Machine 10.2.0.4.0 VALID

Oracle XDK 10.2.0.4.0 VALID

Oracle Database Java Packages 10.2.0.4.0 VALID

Oracle Expression Filter 10.2.0.4.0 VALID

Oracle Data Mining 10.2.0.4.0 VALID

Oracle Text 10.2.0.4.0 VALID

Oracle XML Database 10.2.0.4.0 VALID

Oracle Rule Manager 10.2.0.4.0 VALID

Oracle interMedia 10.2.0.4.0 VALID

OLAP Analytic Workspace 10.2.0.4.0 VALID

Oracle OLAP API 10.2.0.4.0 VALID

OLAP Catalog 10.2.0.4.0 VALID

Spatial 10.2.0.4.0 VALID

Oracle Enterprise Manager 10.2.0.4.0 VALID

17 rows selected.

SQL> select * from v$version;

BANNER

—————————————————————-

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Prod

PL/SQL Release 10.2.0.4.0 – Production

CORE 10.2.0.4.0 Production

TNS for Linux: Version 10.2.0.4.0 – Production

NLSRTL Version 10.2.0.4.0 – Production

Check your database objects are valid or not

SQL> select object_name,status from dba_objects where status=’INVALID’;

No rows selected.

***********************************************************************************

Oracle DBA Course Contents

2012-03-26T19:41:00.001+05:30

Oracle DBA Course

Oracle Architectural Components
Oracle Server Installation
Managing an Oracle Instance
Creating a Database
Database Startup Process
Maintaining the Control files
Maintaining the Redo log files
Managing Tablespaces and Data files
Managing Profiles
Managing Roles
Managing Users
Oracle Networking
Database Clone
User Managed Backup & Recovery
RMAN
Upgrade Database from Oracle 10.2.0.1 To 10.2.0.4
Upgrade Database from Oracle 10.2.0.4 To Oracle 11gR1/R2

Oracle DBA Training

RMAN (Recovery Manager)

RMAN Restore using RMAN COLD Backup

I have taken RMAN COLD Backup in my last post, In this post I am going to delete datafile and then I will try to open a database. I will write some steps to recover/restore datafile/database.

At Server Side: (prd)

$sqlplus ‘/as sysdba’

Sql>startup

ORACLE instance started. Total System Global Area 612368384 bytes Fixed Size 1250428 bytes Variable Size 226495364 bytes Database Buffers 377487360 bytes Redo Buffers 7135232 bytes Database mounted.

ORA-01157: cannot identify/lock data file 7 - see DBWR trace file ORA-01110: data file 7: '/ORACLE/ORADATA/PRD/INDX.DBF'

SQL> shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

Configuration of SQLNET Protocol

Oracle Data Pump

Export and Import

Oracle Database Cloning

Oracle Database Cloning

USER MANAGED BACKUP AND RECOVERY

Option 1: When you don’t have a backup

Option 2: When you have the Backup

RECOVERING FROM LOST OF CONTROL FILE

Recovering Database when the database is running in ARCHIVELOG Mode

Recovering from the lost of Damaged Datafile

If you have lost one datafile. Then follow the steps shown below

If all archive log files are available then recovery should go on smoothly. After you get the "Media Recovery completely" statement. Go on to next step.

Time Based Recovery (INCOMPLETE RECOVERY)

Managing the UNDO TABLESPACE

Managing Tablespaces and Data files

Option 1

You can extend the size of a tablespace by increasing the size of an existing datafile by typing the following command

SQL> alter database usertbs datafile ‘/u01/oracle/data/icatbs01.dbf’ resize 100M;

This will increase the size from 50M to 100M

Option 2

SQL> alter tablespace add datafile ‘/u02/oracle/ica/icatbs02.dbf’ size 50M;

Option 3

To decrease the size of a tablespace

You can drop a tablespace and its contents (the segments contained in the tablespace) from the database if the tablespace and its contents are no longer required. You must have the DROP TABLESPACE system privilege to drop a tablespace.

Temporary tablespace is used for sorting large tables. Every database should have one temporary tablespace. To create temporary tablespace give the following command.

You can use the resize clause to increase or decrease the size of a temporary tablespace. The following statement resizes a temporary file:

SQL>ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' RESIZE 18M;

Viewing Information about Tablespaces and Data files

Managing Archived Redo Logs

Choosing Between NOARCHIVELOG and ARCHIVELOG Mode

Running a Database in NOARCHIVELOG Mode

Running a Database in ARCHIVELOG Mode

Enabling Archive log

To Set the Destination for Archived Redo Logs Using LOG_ARCHIVE_DEST

Maintaining Redo Log Files

Dropping Logfile Group

You can drop logfile group only if the database is having more than two groups and if it is not the current group.

SQL>alter database drop logfile group 3;

Note: When you drop logfiles the files are not deleted from the disk. You have to use O/S command to delete the files from disk.

SQL>alter system switch logfile;

The following command can be used to drop a logfile member

SQL>alter database drop logfile member ‘/u01/oracle/ica/log11.ora’;

Note: When you drop logfiles the files are not deleted from the disk. You have to use O/S command to delete the files from disk.

You cannot resize logfiles. If you want to resize a logfile create a new logfile group with the new size and subsequently drop the old logfile group.

To Rename or Relocate Logfiles perform the following steps

For Example, suppose you want to move a logfile from ‘/u01/oracle/ica/log1.ora’ to ‘/u02/oracle/ica/log2.ora’, then do the following

-->Shutdown the database

SQL>shutdown immediate;

-->Move the logfile from Old location to new location using operating system command

$mv /u01/oracle/ica/log1.ora /u02/oracle/ica/log2.ora

-->Start and mount the database

SQL>startup mount

-->Now give the following command to change the location in controlfile

SQL>alter database rename file ‘/u01/oracle/ica/log1.ora’ to ‘/u02/oracle/ica/log2.ora’;

-->Open the database

SQL>alter database open;

A redo log file might become corrupted while the database is open, and ultimately stop database activity because archiving cannot continue. In this situation the ALTER DATABASE CLEAR LOGFILE statement can be used reinitialize the file without shutting down the database.

The following statement clears the log files in redo log group number 3:

Sql>ALTER DATABASE CLEAR LOGFILE GROUP 3;

Viewing Information About Logfiles

Maintaining the Control File

Creating a Database

Managing an Oracle Instance

Oracle Server Installation

Installation of Oracle 10gR2(10.2.0.1) on OEL(Oracle Enterprise Linux)

On Linux

Steps to Install Oracle

Step 1

Step 2

ORACLE instance started.
Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 226495364 bytes
Database Buffers 377487360 bytes
Redo Buffers 7135232 bytes
Database mounted.

ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/ORACLE/ORADATA/PRD/INDX.DBF'

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

You can drop a tablespace and its contents (the segments contained in the tablespace) from the database if the tablespace and its contents are no longer required. You must have the `DROP` `TABLESPACE` system privilege to drop a tablespace.