Database backup and recovery are among the most important and critical tasks that a DBA must perform. In Oracle you can take the backup of your database in two ways. These two ways are namely as Cold Backup and Hot Backup. Cold Backup is taken when the database is down or closed while Hot Backup is taken when the database is online or open. In order to take the Cold Backup DBA first shutdown the database and make it unavailable to the users and then manually copy the database files, control files, redo log files, network configuration files and parameter files to the database device and finally open the database for normal operations again.
Cold Backup is easy to take but is not ideal for organizations that cannot afford downtime. Most of the organizations today don’t want their database to be down for such a long time especially databases that support online businesses. The prime examples are Google, Yahoo, and Amazon etc. With that in mind Oracle database allows you to take the hot backup. While taking the Hot Backup your database remains open for the user operations. Hot Backup is taken tablespace by tablespace. This means that first you take Backup of one tablespace and then the other. The process continues until all the tablespaces are backed up.
Now suppose you want to take the backup of your “Users” tablespace which is automatically created when Oracle is installed. It is also the default tablespace for all users’ data unless you change it. First you will issue the following command:
SQL>alter tablespace users begin backup;
This command will put the tablespace in backup mode. When a tablespace is in backup mode no changes can be made on the data it contained. However users can still work because their changes are recorded in redo log files and when tablespace gets normal DBWR will apply the changes seamlessly.
The next step is to copy the datafiles belonging to “Users” tablespace. You can issue the following command to do this:
SQL>host copy ‘C:\oracle\product\10.1.0\oradata\orcl\users01.dbf’ ‘F:\backup\users01.dbf’;
This command will copy the datafile to the backup location. “Host” keyword is used to run OS commands from SQL*PLUS. This command will repeat for each datafile in tablespace. Further more you can manually copy and paste the datafile using Explorer instead of issuing this command.
The next command will bring the tablespace backup to normal mode:
SQL>alter tablespace users end backup;
The last command will insure that the database gets into consistent state by forcing a log switch.
SQL>alter system switch logfile;
Normally instead of issuing all these commands manually each time, DBA writes a script consisting of these commands and runs the script. For that purpose open a text editor like Notepad and write all the four commands in it. Be sure to write each command on a separate line and save the file as SQL script file (.sql). To run the script issue the following command:
SQL>@C:\HotBackup.sql
Thursday, August 30, 2007
Friday, August 17, 2007
Oracle Instance Startup
Instance and database are normally considered to be the same things and there is no harm in doing so. Although technically both are different but without each other both are meaningless, therefore terms are used interchangeably. Database is just a dump of files that contain the data while instance provides a mean to access that data. Instance consists of some background processes and memory structures which are collectively called SGA or Shared Global Area. The process through which we make the data stored in the database available to users is knows as startup which is actually Instance startup.
There are several stages through which an Instance passes during startup. The reason for the split of Instance startup into stages is to perform certain operations at certain stages. For example most of the administrative tasks are performed in No mount or mount state. When the Instance is in mount or No mount state, it is unavailable to users and only DBA can access it. The initial stage of the Instance is idle. When we give the command startup the Instance first reads the spfile (Server Parameter File) and gets into No mount stage. Server Parameter File is a parameter file that contains the Name/Value pairs of parameters which are necessary for Instance startup. After that Instance reads the control files and gets into the mount state. Finally it reads the data files and gets into open state.
You can also start the Instance step by step. For example if you want to get the instance into mount state then you can issue the command
SQL>startup mount; instead of
SQL>startup
(The command startup will open the database.) After you have mounted your database then you can open it with the following command
SQL>alter database open;
One point worth mentioning here is that you cannot go through the states in reverse order. Means that you cannot mount a database when it is open and hence cannot bring it to No mount state when it is in mount state. If you want to do this then first shutdown the database and then bring it to the desired state.
There are several stages through which an Instance passes during startup. The reason for the split of Instance startup into stages is to perform certain operations at certain stages. For example most of the administrative tasks are performed in No mount or mount state. When the Instance is in mount or No mount state, it is unavailable to users and only DBA can access it. The initial stage of the Instance is idle. When we give the command startup the Instance first reads the spfile (Server Parameter File) and gets into No mount stage. Server Parameter File is a parameter file that contains the Name/Value pairs of parameters which are necessary for Instance startup. After that Instance reads the control files and gets into the mount state. Finally it reads the data files and gets into open state.
You can also start the Instance step by step. For example if you want to get the instance into mount state then you can issue the command
SQL>startup mount; instead of
SQL>startup
(The command startup will open the database.) After you have mounted your database then you can open it with the following command
SQL>alter database open;
One point worth mentioning here is that you cannot go through the states in reverse order. Means that you cannot mount a database when it is open and hence cannot bring it to No mount state when it is in mount state. If you want to do this then first shutdown the database and then bring it to the desired state.
Subscribe to:
Posts (Atom)