Oracle Tutorials {Very useful & Day today needs}
-----------------------------------------------------------------------------------------
Topics
-----------------------------------------------------------------------------------------
Why and when should I backup my database?
What strategies are available for backing-up an Oracle database?
What is the difference between online and offline backups?
What is the difference between restoring and recovering?
How does one backup a database using the export utility?
How does one do off-line database backups?
How does one do on-line database backups?
How does one backup a database using RMAN?
How does one put a database into ARCHIVELOG mode?
How does one backup archived log files?
Does Oracle write to data files in hot/begin backup mode?
My database was terminated while in BACKUP MODE, do I need to recover?
My database is down and I cannot restore. What now?
I've lost my REDOLOG files, how can I get my DB back?
I've lost some Rollback Segments, how can I get my DB back?
What are the differences between EBU and RMAN?
How does one create a RMAN recovery catalog?
What are the common RMAN errors (with solutions)?
What third party tools can be used with Oracle EBU/ RMAN?
Where can one get more info about Oracle Backup and Recovery?
--------------------------------------------------------------------------------
Back to Oracle DBA Topics
--------------------------------------------------------------------------------
Why and when should I backup my database?
Backup and recovery is one of the most important aspects of a DBAs job. If you lose your company's data,
you could very well lose your job. Hardware and software can always be replaced, but your data may be irreplaceable!
Normally one would schedule a hierarchy of daily, weekly and monthly backups,
however consult with your users before deciding on a backup schedule.
Backup frequency normally depends on the following factors:
Rate of data change/ transaction rate
Database availability/ Can you shutdown for cold backups?
Criticality of the data/ Value of the data to the company
Read-only tablespace needs backing up just once right after you make it read-only
If you are running in archivelog mode you can backup parts of a database over an extended cycle of days
If archive logging is enabled one needs to backup archived log files timeously to prevent database freezes
Etc.
Carefully plan backup retention periods. Ensure enough backup media (tapes) are available and that old backups are expired in-time to make media available for new backups. Off-site vaulting is also highly recommended.
Frequently test your ability to recover and document all possible scenarios. Remember, it's the little things that will get you. Most failed recoveries are a result of organizational errors and miscommunications.
--------------------------------------------------------------------------------
What strategies are available for backing-up an Oracle database?
The following methods are valid for backing-up an Oracle database:
Export/Import - Exports are "logical" database backups in that they extract logical definitions and data from the
database to a file. See the Import/ Export FAQ for more details.
Cold or Off-line Backups - Shut the database down and backup up ALL data, log, and control files.
Hot or On-line Backups - If the database is available and in ARCHIVELOG mode,
set the tablespaces into backup mode and backup their files.
Also remember to backup the control files and archived redo log files.
RMAN Backups - While the database is off-line or on-line, use the "rman" utility to backup the database.
It is advisable to use more than one of these methods to backup your database. For example, if you choose to do on-line database backups, also cover yourself by doing database exports. Also test ALL backup and recovery scenarios carefully. It is better to be save than sorry.
Regardless of your strategy, also remember to backup all required software libraries, parameter files, password files, etc. If your database is in ARCGIVELOG mode, you also need to backup archived log files.
--------------------------------------------------------------------------------
What is the difference between online and offline backups?
A hot backup is a backup performed while the database is online and available for read/write.
Except for Oracle exports, one can only do on-line backups when running in ARCHIVELOG mode.
A cold backup is a backup performed while the database is off-line and unavailable to its users.
--------------------------------------------------------------------------------
What is the difference between restoring and recovering?
Restoring involves copying backup files from secondary storage (backup media) to disk.
This can be done to replace damaged files or to copy/move a database to a new location.
Recovery is the process of applying redo logs to the database to roll it forward.
One can roll-forward until a specific point-in-time (before the disaster occurred),
or roll-forward until the last transaction recorded in the log files.
sql> connect SYS as SYSDBA
sql> RECOVER DATABASE UNTIL TIME '2001-03-06:16:00:00' USING BACKUP CONTROLFILE;
--------------------------------------------------------------------------------
How does one backup a database using the export utility?
Oracle exports are "logical" database backups (not physical) as they extract data and logical definitions from the database into a file. Other backup strategies normally back-up the physical data files.
One of the advantages of exports is that one can selectively re-import tables, however one cannot roll-forward from an restored export file. To completely restore a database from an export file one practically needs to recreate the entire database.
Always do full system level exports (FULL=YES). Full exports include more information about the database in the export file than user level exports. For more information about the Oracle export and import utilities, see the Import/ Export FAQ.
--------------------------------------------------------------------------------
How does one do off-line database backups?
Shut down the database from sqlplus or server manager. Backup all files to secondary storage (eg. tapes).
Ensure that you backup all data files, all control files and all log files. When completed, restart your database.
Do the following queries to get a list of all files that needs to be backed up:
select name from sys.v_$datafile;
select member from sys.v_$logfile;
select name from sys.v_$controlfile;
Sometimes Oracle takes forever to shutdown with the "immediate" option. As workaround to this problem,
shutdown using these commands:
alter system checkpoint;
shutdown abort
startup restrict
shutdown immediate
Note that if you database is in ARCHIVELOG mode, one can still use archived log files to roll forward from an off-line backup. If you cannot take your database down for a cold (off-line) backup at a convenient time, switch your database into ARCHIVELOG mode and perform hot (on-line) backups.
--------------------------------------------------------------------------------
How does one do on-line database backups?
Each tablespace that needs to be backed-up must be switched into backup mode before copying the files out to secondary storage (tapes). Look at this simple example.
ALTER TABLESPACE xyz BEGIN BACKUP;
! cp xyfFile1 /backupDir/
ALTER TABLESPACE xyz END BACKUP;
It is better to backup tablespace for tablespace than to put all tablespaces in backup mode.
Backing them up separately incurs less overhead. When done, remember to backup your control files. Look at this example:
ALTER SYSTEM SWITCH LOGFILE; -- Force log switch to update control file headers
ALTER DATABASE BACKUP CONTROLFILE TO '/backupDir/control.dbf';
NOTE: Do not run on-line backups during peak processing periods. Oracle will write complete database blocks instead of the normal deltas to redo log files while in backup mode. This will lead to excessive database archiving and even database freezes.
--------------------------------------------------------------------------------
How does one backup a database using RMAN?
The biggest advantage of RMAN is that it only backup used space in the database.
Rman doesn't put tablespaces in backup mode,
saving on redo generation overhead. RMAN will re-read database blocks until it gets a consistent image of it.
Look at this simple backup example.
rman target sys/*** nocatalog
run {
allocate channel t1 type disk;
backup
format '/app/oracle/db_backup/%d_t%t_s%s_p%p'
( database );
release channel t1;
}
Example RMAN restore:
rman target sys/*** nocatalog
run {
allocate channel t1 type disk;
# set until time 'Aug 07 2000 :51';
restore tablespace users;
recover tablespace users;
release channel t1;
}
The examples above are extremely simplistic and only useful for illustrating basic concepts. By default Oracle uses the database controlfiles to store information about backups. Normally one would rather setup a RMAN catalog database to store RMAN metadata in. Read the Oracle Backup and Recovery Guide before implementing any RMAN backups.
Note: RMAN cannot write image copies directly to tape. One needs to use a third-party media manager that integrates with RMAN to backup directly to tape. Alternatively one can backup to disk and then manually copy the backups to tape.
--------------------------------------------------------------------------------
How does one put a database into ARCHIVELOG mode?
The main reason for running in archivelog mode is that one can provide 24-hour availability and guarantee complete data recoverability. It is also necessary to enable ARCHIVELOG mode before one can start to use on-line database backups.
To enable ARCHIVELOG mode, simply change your database startup command script, and bounce the database:
SQLPLUS> connect sys as sysdba
SQLPLUS> startup mount exclusive;
SQLPLUS> alter database archivelog;
SQLPLUS> archive log start;
SQLPLUS> alter database open;
NOTE1: Remember to take a baseline database backup right after enabling archivelog mode. Without it one would not be able to recover. Also, implement an archivelog backup to prevent the archive log directory from filling-up.
NOTE2: ARCHIVELOG mode was introduced with Oracle V6, and is essential for database point-in-time recovery. Archiving can be used in combination with on-line and off-line database backups.
NOTE3: You may want to set the following INIT.ORA parameters when enabling ARCHIVELOG mode: log_archive_start=TRUE, log_archive_dest=..., and log_archive_format=...
NOTE4: You can change the archive log destination of a database on-line with the ARCHIVE LOG START TO 'directory'; statement. This statement is often used to switch archiving between a set of directories.
NOTE5: When running Oracle Real Application Server (RAC), you need to shut down all nodes before changing the database to ARCHIVELOG mode. See the RAC FAQ for more details.
--------------------------------------------------------------------------------
How does one backup archived log files?
One can backup archived log files using RMAN or any operating system backup utility. Remember to delete files after backing them up to prevent the archive log directory from filling up. If the archive log directory becomes full, your database will hang! Look at this simple RMAN backup script:
RMAN> run {
2> allocate channel dev1 type disk;
3> backup
4> format '/app/oracle/arch_backup/log_t%t_s%s_p%p'
5> (archivelog all delete input);
6> release channel dev1;
7> }
--------------------------------------------------------------------------------
Does Oracle write to data files in begin/hot backup mode?
Oracle will continue to write to the database data files even if a tablespace is in backup mode. If a tablespace is in backup mode, Oracle will write out complete changed blocks to the redo log files. Normally only deltas (changes) are logged to the redo log files. This is done to enable reconstruction of a block if only half of it was backed up (split blocks). Because of this, one should notice increased log activity and archiving during on-line backups.
--------------------------------------------------------------------------------
My database was terminated while in BACKUP MODE, do I need to recover?
If a database was terminated while one of its tablespaces was in BACKUP MODE (ALTER TABLESPACE xyz BEGIN BACKUP;), it will tell you that media recovery is required when you try to restart the database. The DBA is then required to recover the database and apply all archived logs to the database. However, from Oracle7.2, you can simply take the individual datafiles out of backup mode and restart the database.
ALTER DATABASE DATAFILE '/path/filename' END BACKUP;
One can select from V$BACKUP to see which datafiles are in backup mode. This normally saves a significant amount of database down time. See script end_backup2.sql in the script section of this FAQ.
--------------------------------------------------------------------------------
My database is down and I cannot restore. What now?
Recovery without any backup is normally not supported, however, Oracle Consulting can sometimes extract data from an offline database using a utility called DUL (Disk UnLoad). This utility reads data in the data files and unloads it into SQL*Loader or export dump files. DUL does not care about rollback segments, corrupted blocks, etc, and can thus not guarantee that the data is not logically corrupt. It is intended as an absolute last resort and will most likely cost your company a lot of money!!!
"Life is DUL without it!"
--------------------------------------------------------------------------------
I've lost my REDOLOG files, how can I get my DB back?
The following INIT.ORA parameter may be required if your current redologs are corrupted or blown away. Caution is advised when enabling this parameter as you might end-up losing your entire database. Please contact Oracle Support before using it.
_allow_resetlogs_corruption = true
--------------------------------------------------------------------------------
I've lost some Rollback Segments, how can I get my DB back?
Re-start your database with the following INIT.ORA parameter if one of your rollback segments is corrupted. You can then drop the corrupted rollback segments and create it from scratch. Caution is advised when enabling this parameter as uncommitted transactions will be marked as committed. One can very well end up with lost or inconsistent data!!! Please contact Oracle Support before using it.
_corrupted_rollback_segments = (rbs01,rbs01,rbs03,rbs04)
--------------------------------------------------------------------------------
What are the differences between EBU and RMAN?
Enterprise Backup Utility (EBU) is a functionally rich, high performance interface for backing up Oracle7 databases. It is sometimes referred to as OEBU for Oracle Enterprise Backup Utility.
The Oracle Recovery Manager (RMAN) utility that ships with Oracle8 and above is similar to Oracle7's EBU utility. However, there is no direct upgrade path from EBU to RMAN.
--------------------------------------------------------------------------------
How does one create a RMAN recovery catalog?
Start by creating a database schema (usually called rman).
Assign an appropriate tablespace to it and grant it the recovery_catalog_owner role. Look at this example:
sqlplus sys
SQL> create user rman identified by rman;
SQL> alter user rman default tablespace tools temporary tablespace temp;
SQL> alter user rman quota unlimited on tools;
SQL> grant connect, resource, recovery_catalog_owner to rman;
SQL> exit;
Next, log in to rman and create the catalog schema. Prior to Oracle 8i this was done by running the catrman.sql script.
rman catalog rman/rman
RMAN> create catalog tablespace tools;
RMAN> exit;
You can now continue by registering your databases in the catalog. Look at this example:
rman catalog rman/rman target backdba/backdba
RMAN> register database;
--------------------------------------------------------------------------------
What are the common RMAN errors (with solutions)?
Some of the common RMAN errors are:
RMAN-20242: Specification does not match any archivelog in the recovery catalog.
Add to RMAN script: sql 'alter system archive log current';
RMAN-06089: archived log xyz not found or out of sync with catalog
Execute from RMAN: change archivelog all validate;
--------------------------------------------------------------------------------
What third party tools can be used with Oracle EBU/ RMAN?
The following Media Management Software Vendors have integrated their media management software packages
with Oracle Recovery Manager and Oracle7 Enterprise Backup Utility.
The Media Management Vendors will provide first line technical support for the integrated backup/recover solutions.
Veritas NetBackup
EMC Data Manager (EDM)
HP OMNIBack II
IBM's Tivoli Storage Manager - formerly ADSM
Legato Networker
ManageIT Backup and Recovery
Sterling Software's SAMS:Alexandria - formerly from Spectralogic
Sun Solstice Backup
etc...
--------------------------------------------------------------------------------
Where can one get more info about Oracle Backup and Recovery?
This FAQ: Backup and Recovery Scripts
This FAQ: Oracle Backup and Recovery Handbook (Rama Velpuri, McGraw-Hill Professional Publishing)
Kevin Loney's New DBA FAQs: Part 1 - Backup Strategy
************************************************************************************************
PL/SQL, Stored Procedures and Triggers
--------------------------------------------------------------------------------
Writing your own Functions
What is the difference between a PL/SQL function and procedure? The same as the difference between a VB function and procedure. A function returns a result. This difference has to be reflected in the structure. Even here the difference is really minute.
Where a procedure was declared as:
PROCEDURE proc_name (parameterlist) IS
A function is declared as:
FUNCTION FUNC_NAME (ParameterList) RETURN TYPE IS
The difference is the reserved word RETURN and a variable TYPE such as NUMBER or VARCHAR or DATE.
Because the function returns a value, the function will also need TO declare a variable and a RETURN variable statement.
Again it is best to show an example:
CREATE OR REPLACE
FUNCTION ODDITY (Num_In IN INTEGER) RETURN INTEGER
IS
Odd_out INTEGER; -- declare the return variable
BEGIN
Odd_Out := 2 * Num_In - 1;
RETURN Odd_Out;
EXCEPTION
WHEN OTHERS THEN -- any error at all
RETURN 0;
END;
This function apparently returns the nth odd number given n. The function may be used within you schema like any other Oracle function.
An Interim Summary
We have learned how to build stored procedures, even those using cursors, and how to make VB use their results. It is no wonder that we let the DBA handle this troublesome (and then some) activity, but it is good to unveil the mysteries. Next we are going to handle triggers.
Triggers
Triggers are parameter-less procedures that are triggered (fired) either before or after inserting, updating or deleting rows from a table. Because they are fired by the event and not by choice they cannot have parameters.
Triggers are used for a variety of reasons, the simplest ones are to guarantee uniqueness by using sequences. This is the reason we use them in our sample programs. Other reasons include maintaining data integrity, and doing complex cascading updates and deletes which cannot be done directly as some of us who are SQL Server aficionados prefer to do them.
We need a few triggers to make sure that our little set of tables continues with unique keys, and to verify that all the people names in the database are properly capitalized (remember, Oracle, like Unix, thinks that Athens should be sorted after Xanadu).
Let us look at a trigger that sets the PERSON_ID in the People table:
DECLARE X NUMBER;
BEGIN
IF :new.PERSON_ID = 0 THEN
SELECT PERSON_ID_SEQ.NEXTVAL INTO X FROM dual;
:new.PERSON_ID := X;
END IF;
END;
Again you can see a declaration section and a trigger body. In our trigger I wanted the user to be able to enter a unique ID of his own, or use the system-generated sequence. Thus if a non-zero ID is asked for, the system will attempt to use it. Of course, if it is not unique, it will flunk because it is a primary key. The user is actually better off letting the sequence handle the uniqueness.
Note the term :new. In a trigger you may use the word new or old in conjunction with a field name. :New.field_name is the value that will be inserted. In an UPDATE, you may also inquire about what the value was. This is found in :old.field_name.
Other than these little differences a trigger obeys all the procedural rules.
There is a shorter way to write the trigger, a way that allows you to get rid of the declaration altogether:
BEGIN
IF :new.PERSON_ID = 0 THEN
SELECT PERSON_ID_SEQ.NEXTVAL INTO :new.PERSON_ID FROM DUAL;
END IF;
END;
You can choose what you prefer. The first code was so written in order to show you how declarations are made in the trigger.
You can create a trigger in SQL*PLUS or in the Navigator. In both cases, I compose it in a line editor. Again, the advantage of the Navigator is getting the error messages: the disadvantage is the fixed small window.
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Database Interview Questions (SQL, Oracle, Pro*C/C++)
Database Questions
What are the different types of joins?
Explain normalization with examples.
What cursor type do you use to retrieve multiple recordsets?
Diffrence between a "where" clause and a "having" clause
What is the difference between "procedure" and "function"?
How will you copy the structure of a table without copying the data?
How to find out the database name from SQL*PLUS command prompt?
Tadeoffs with having indexes
Talk about "Exception Handling" in PL/SQL?
What is the diference between "NULL in C" and "NULL in Oracle?"
What is Pro*C? What is OCI?
Give some examples of Analytical functions.
What is the difference between "translate" and "replace"?
What is DYNAMIC SQL method 4?
How to remove duplicate records from a table?
What is the use of ANALYZing the tables?
How to run SQL script from a Unix Shell?
What is a "transaction"? Why are they necessary?
Explain Normalizationa dn Denormalization with examples.
When do you get contraint violtaion? What are the types of constraints?
How to convert RAW datatype into TEXT?
Difference - Primary Key and Aggregate Key
How functional dependency is related to database table design?
What is a "trigger"?
Why can a "group by" or "order by" clause be expensive to process?
What are "HINTS"? What is "index covering" of a query?
What is a VIEW? How to get script for a view?
What are the Large object types suported by Oracle?
What is SQL*Loader?
Difference between "VARCHAR" and "VARCHAR2" datatypes.
What is the difference among "dropping a table", "truncating a table" and "deleting all records" from a table.
Difference between "ORACLE" and "MICROSOFT ACCESS" databases.
How to create a database link ?
********************************************************************************
COLD BACKUPS
Sitemap Keywords Most wanted News
--------------------------------------------------------------------------------
Cold backup in Oracle
Making a Cold Backup
A cold backup requires the database to be shut down. That means, as opposed to a hot backup, users cannot do anything on the database.
So, first, SHUTDOWN the database (not a shutdown abort).
Then copy all necessary files to a safe place. Those files are:
The datafiles:
select name from v$datafile
The control files:
select name from v$controlfile
The online redo logs:
select member from v$logfile
The parameter file (The parameterfile cannot be found using dynamic performance views) and the password file (if used).
Here is a shell script that shuts down the database, copies the files of the database to a safe place and then starts up the database again.
After we have copied these files to a safe place, we can safely delete the archived log files.
Simulating Media Failure
Now, it is time to simulate a Media Failure to prove that recovery is possible. STARTUP the database and create some tables, and insert something into the tables. And then delete the datafiles
create table after_backup (a number, b varchar2(40));
insert into after_backup values (1, 'before commit');
insert into after_backup values (2, 'also before commit');
commit;
insert into after_backup values (3, 'after commit');
make sure to not commit this session until you delete the datafiles. Open another session instead and create a user or something
create user user_after_backup identified by pw;
grant dba to user_after_backup;
Now, crash the instance and delete the database. Open the init.ora file at the backuped place and modify its control_file initializtion parameter to point to the backed up control files. Then startup mount pfile=c:\oracle\ora81\admin\backup\initadpdb.ora the database. Note, I am using the backed up initadpdb.ora file.
After you've mounted the database, rename the datafile (so that they point to the new files):
alter database rename file 'C:\ORACLE\ORA81\ADMIN\ADPDB\SYSTEM01.DBF'
to 'C:\ORACLE\ORA81\ADMIN\backup\SYSTEM01.DBF';
--------------------------------------------------------------------------------
I always try to improve my site. If you see something that could be better, or is simply wrong, feel free to send any suggestions, ideas, comments to sudhirsunil@gmail.com.
No comments:
Post a Comment