User Managed Backups

USER MANAGED BACKUPS

SQL>select name from v$controlfile;

SQL>select member from V$logfile;

SQL>select name from v$datafile;

SQL>shu immediate;

Now copy all the above files at a backup location manually using OS Command.

SQL>startup ;

• WHOLE DATABASE ONLINE / HOT / INCONSISTENT BACKUP

Database must be in archive log mode

SQL> alter database begin backup;

Copy all datafiles to backup location

SQL> alter database end backup;

Switch logfile

SQL> alter system switch logfile;

Create controlfile backup

SQL> alter database backup controlfile to 'e:\backup\control01.ctl';

Create spfile backup

SQL> create pfile='e:\backup\init.ora' from spfile;

Now user managed hot backup is complete.

• ONLINE TABLESPACE BACKUP

Database must be in archive log mode

SQL> alter tablespace example begin backup;

Copy the datafile of the tablespace example to a backup location

To check the status of the file which are left active in backup mode

SQL>select file#, status from v$backup;

SQL>alter tablespace example end backup;

• METHODS OF TAKING CONTROL FILE BACKUP

Two ways with which one you can create a binary file and with other a text file.

For Binary file creation

SQL>alter database backup controlfile to ‘D:\backup\ctrl.ctl’;

For Text File

SQL>alter database backup controlfile to trace;

( It will be created in UDUMP folder )

Archive Mode

ARCHIVE MODE MANAGEMENT

To change the log_mode of the database from noarchive to archive log_mode

First find out whether database is in which mode : two ways

SQL>archive log list;

Or

SQL>select log_mode from V$database;

If not in archive mode

SQL>shu immediate;

Edit the init.ora parameter file of the database and add or change atleast 4 parameters

1] Enable Archive

2] Archive Destination

3] Archive Format

4] Archive Maximum Processes

i.e.

log_archive_start=true

log_archive_dest_1=”e:\oracle\backup\db2\archive1\ MANDATORY”

log_archive_dest_2=”f:\oracle\backup\db2\archive2\ OPTIONAL”

log_archive_format= arch_%s.arc ( # %s stands for log sequence number )

log_archive_max_processes=2

After editing the parameter file

SQL>startup mount pfile=’d:\oracle\oradata\ora92\database\init.ora’;

Confirm the log_mode now

SQL>archive log list; or SQL>select log_mode from v$database;

List the parameters log_archive_dest, log_archive_start, & log_archive_format:

SQL>show parameter log_archive;

Or

SQL>select name, value from v$parameter where name like ‘log_archive_dest%’;

Now switch the database to archive log ( database should be in archive log mode )

SQL>alter database archivelog;

And to put into other mode alter database noarchivelog ofcourse after changing the parameters in the parameter file.

Now confirm again with archive log list command and finally

SQL>alter database open;

Redo Log

MANAGING REDO LOG GROUPS & MEMBERS

• ADDING NEW REDO LOG MEMEBERS

First display the name & location of present redo log members.

SQL>select member from v$logfile;

It is preferable to create all the members of all the group together in a separate location i.e all the first members of suppose group 1, group 2 in one location and all the second members of group 1 and group 2 in another location.

SQL>alter database add logfile member

‘d:\oracle\ora92\oradata\db2\log01b.rdo’ to group 1,

‘d:\oracle\ora92\oradata\db2\log02b.rdo’ to group 2;

• DROPING ONLINE REDO LOG MEMBER

SQL>alter database drop logfile member

‘d:\oracle\ora92\oradata\db2\log3a.rdo’;

Restrictions :

If the member you want to drop is the last valid member of the group, you cannot drop that member.

If a group is current you have to force a log file switch, to drop its member

Command to know whether the group is active or not

SQL>select group#, status from v$log;

Command to force a log file switch

SQL>Alter system switch logfile;

If the database is in archivelog mode and the log file group is not archived yet you cannot drop its member.

When an online redo log member is dropped, the operating system files are not deleted.

• ADDING REDOLOG GROUP

If already 2 groups are their with two members each.

SQL> alter database add logfile group 3

(‘d:\oracle\ora92\oradata\db2\log3a.rdo’,

‘e:\oracle\oradata\db2\log3b.rdo’) size 1m;

• DROPING REDO LOG GROUP

SQL>alter database drop logfile group 3;

Restrictions :

An instance requires at least two groups of online redo log files.

An active or current group cannot be dropped.

When an online redo log group is dropped, the operating system files are not deleted.

• CLEARING ONLINE REDO LOGFILE

Need arises when the redo log file is corrupted, dba can solve the problem by reinitializing these log files , the command is equivalent to adding and droping an online redo log file.

SQL>alter database clear logfile

‘d:\oracle\ora92\oradata\db2\log2a.rdo’;

• RELOCATING AND RENAMING REDO LOG FILES

The location can be changed or files can be renamed by drop the old log files and creating a new file.

Even though one more method is available i.e.

SQL>alter database rename file ……

But for this the database requires to be in mount mode.

Multiple Control File

MIRRORING / MULTIPLEXING THE CONTROLFILES.

SQL>create pfile from spfile;

Note : Pfile will be created using spfile in the default location of spfile

SQL>Shu immediate;

After shutting down use the OS command to make a copy of the controlfile in your desired location and by your desired name

After Coping make changes to add new name and location of control file or files in initialization parameter file i.e. PFILE

SQL>startup pfile=’path and name of pfile’;

Check the name and location of control files now..

SQL>select name from v$controlfile;

SQL>create spfile from pfile;

So that next time you don’t have to mention pfile at the time of startup.

Database Files

OS- Windows , Version – 9i

TO DISPLAY THE NAMES & LOCATION OF ALL FILES OF A DATABASE.

Database files consists of Datafiles, Controlfiles, Redologfiles.

SQL> select name from v$controlfile;

SQL> Select member from v$logfile;

SQL> Select name from V$datafile

Welcome

Dear Friends,
My name is Amjadali Amir Peshimam, a Oracle 9i Certified DBA presently working in a very reputed company in India.
Today I have started my Blog , to share whatever I learn in day to day working as a Professional DBA working in Industry or as a student working in my own testing environment, because I believe the more you share the more you educate yourself. To start with I want to share some thoughts which I believe in......

" One Who Dare to Dream, Has a Whole World To Win..."

" It is not Over, Until it is Over...And if you are true to Yourself , End is always Sweet.."

" Successful People Dont Do Different Things, They Do things Differently"

I will appreciate, any suggestion or corrections If you brought to my notice.
I hope the information which I will try to share will prove useful to all the new DBAs and prospective DBAs who are planning to become one...
Take Care ,
Thanks & Regards
Amjad Ali