1. Introduction:
This article is the first in a series of three, introducing Oracle's RMAN (Recovery MANager) backup and recovery tool to novice DBAs. The articles focus on the Windows operating system, but can be easily adapted to other platforms. RMAN will work in the same manner on all operating systems (OS) that Oracle runs on. However, OS functions such as script scheduling etc. obviously vary from platform to platform. The present article describes the implementation of a simple Oracle backup strategy using RMAN. The next article will discuss some recovery scenarios within this backup framework, and the final article will deal with disaster recovery.
We begin with a statement of assumptions regarding the database environment and business requirements regarding data recoverability. This serves to anchor the subsequent discussion in a definite context. We then briefly discuss the files relevant to operation of Oracle. Finally, we move on to the main topic - a discussion of RMAN and how it can be used to backup a database.
Following are the assumptions pertaining to the database environment and business expectations regarding data recovery:
The database is hosted on a Windows NT / 2000 / 2003 server.
The database software is Oracle 9i.
Users expect the database to be available round the clock, so the database cannot be shutdown for backups. This makes an online (hot) backup mandatory.
In case of a disaster, where the server is destroyed, the business expects us to be able to recover all transactions up to the previous working day - i.e. a maximum of 24 hours data loss is acceptable. (We will discuss options for doing better than this in the third article of this series)
The database is relatively small - say 1 to 30 GB in size
Specified directories on the host server are backed up to to tape every night using an OS backup utility.
We will configure RMAN to backup to disk. These backups will then be copied to tape by the OS backup utility. For completeness we note that RMAN can be configured to backup the database directly to tape, via an interface called MML (Media Management Library) which integrates third party backup products with RMAN. MML can be somewhat fiddly to setup, and the details depend on the third-party product used. We will not discuss RMAN backups to tape in the present article.
The server has three independent disks - named C:, D: and E:. The contents of the drives are as follows:
C: - The operating system and database software (Oracle home is c:\oracle\ora92. The oracle home directory is normally referred to as ORACLE_HOME). We will also keep a copy of the controlfile and a group of online redo logs on this disk. This isn't ideal, but will have to do because of the limited number of disks we have. Database files and their function are described in the next section.
D: - All datafiles, a copy of the controlfile and one group of online redo logs.
E: - A copy of the controlfile, all archived logs and database backups. RMAN will be configured to backup to this drive. Note that the backups could reside on D: instead.
All disks should be mirrored using some form of RAID technology. Note that the above layout isn't ideal - we're limited by the number of disks available. If you have more disks you could, and probably should, configure a better layout.
2. Oracle database files:
In order to perform backups effectively, it is necessary to understand a bit about the various files that comprise a database. This section describes the files that make up an Oracle database. The descriptions given here are very brief. Please check the Oracle Administrator's Guide for further details.
Oracle requires the following files for its operation:
Datafiles: These hold logical units (called blocks) that make up the database. The SYSTEM tablespace datafile, which holds the data dictionary and other system-owned database objects, is required for database operation. The database can operate without non-SYSTEM datafiles as long as no data is requested from or modified in blocks within those files. In our case all datafiles are located on D:.
Data files can be backed up by RMAN.
Online redo logs: These files hold transaction information that is necessary for transaction rollback, and for instance recovery in case of a database crash. Since these files hold critical information, it is wise to maintain two or more identical copies of these files on independent disks (multiplexing). Each set of copies is known as a redo log group. The members of a group should be on separate disks. In our case we maintain three groups with two members per group - each group has one member D: and the other on E: (instead of E:, one could maintain the second set on C:). When writing to the logs, Oracle cycles through a group at a time in a circular manner - i.e. once all groups have been written to, the first one is reused, overwriting its previous contents. Hence the importance of archiving filled logs as the system switches from one log to the next. - see item (4) below.
Note that online logs should NEVER be backed up in a hot backup. If you do back them up, there is a danger that you may overwrite your current (valid) logs with the backed up ones, which are out of date and therefore invalid. Never ever backup your redo logs in a hot backup scenario..
Control file: This file holds, among other things, the physical structure of the database (location of all files), current database state (via a system change number, or SCN - which is a monotonically increasing number that is incremented on every commit), and information regarding backups taken by RMAN. The control files, being critical to database operation, should also be multiplexed. We will maintain three copies, one each on C:, D: and E: drive.
Controlfiles can be optionally backed up by RMAN.
Archived redo logs: These are archived copies of online redo logs, created after a switch occurs from one online log to the next one in the group. Archiving ensures that a complete transaction history is maintained. Once an online log is archived, it can be overwritten without any loss of transaction history. Archive logs are required for online backups, so they are mandatory in our scenario. We maintain a single copy of these on E: drive. This is a weak point in the present backup strategy (only one copy of archive logs). There are ways to do better - one can maintain up to ten independent sets of archive logs at different physical locations - check the documentation for details.
Note that online logs are archived only when the database operates in ARCHIVELOG mode. The default operation mode is NOARCHIVELOG - where online redo logs are not archived before being overwritten. It would take us too far afield to discuss this any further - please check the Oracle Administrator's Guide for instructions on configuring your database to archivelog mode.
Archive logs can be optionally backed up by RMAN.
Server parameter file (also known as Stored parameter file): This file contains initialization parameters that are used to configure the Oracle instance on startup. We maintain this file in its default location (ORACLE_HOME\database).
The server parameter file can be optionally backed up by RMAN.
Password file: This file stores credentials of users who are allowed to log on to Oracle as privileged (sysdba) users without having to supply an Oracle password. All users belonging to the Windows OS group ORA_DBA are included in the password file. Users with sysdba privileges are allowed to perform database startup, shutdown and backups (among other administrative operations). Please check the Oracle Administrator's guide for details on sysdba and sysoper privileges. We maintain the password file in its default location (ORACLE_HOME\database).
This file is not backed up by RMAN, and must be backed up via OS commands.
Networking files: These files (tnsnames.ora, listener.ora and sqlnet.ora) are Oracle network configuration files. They are maintained in their standard location - ORACLE_HOME\network\admin.
These files are not backed up by RMAN, and must be backed up via OS commands.
Additionally, it is a good idea to store a copy of all database creation scripts in the backup directory. This will help in determining the correct database file directory structure in case of a disaster. This, however, is not absolutely necessary as file placement information can be retrieved from the backup controlfile. More on this in the third article of this series.
3. OS backup utilities vs. RMAN- a brief discussion:
OS Backup utilities copy OS files from disk to tape. By themselves they are not useful for database backups, unless the database is closed. The reason they cannot be used to backup open databases is as follows: If the database is open, it is possible that contents of a datafile block are being modified at the very instant that the block is being copied by the utility. In such a situation the copy of the block would be inconsistent, and hence useless for recovery. The way to avoid this is to put a tablespace into a special "backup mode" before copying the datafiles associated with the tablespace. Such OS level backups, also called user managed backups, are the traditional (non-RMAN) way to backup Oracle databases. When a tablespace is in backup mode, the SCN, which is marked in the header of each datafile in the tablespace, is frozen until the tablespace is taken out of backup mode. Additionally, whenever a data block in the tablespace is modified, the entire block is copied to the online redo log (in contrast to only modified rows being copied when the tablespace is not in backup mode). This causes a huge increase in the redo generated, which is a major disadvantage of user managed backups.
One can perform user managed backups of a database using homegrown scripts. Such a script would cycle through all tablespaces in the database, putting each tablespace in backup mode, copying the associated datafiles and finally switching the tablespace out of backup mode. A fragment of a user managed hot backup script for Windows might read as follows:
--put USERS tablespace in backup mode
alter tablespace users begin backup;
--copy files belonging to USERS tablespace
host copy d:\oracle\ora92\orcl\users.dbf e:\backup;
--take USERS tablespace out of backup mode
alter tablespace users end backup;
--continue with other tablespaces and then copy other oracle files...
The above would be invoked from sqlplus, via an appropriately scripted batch file.
Most OS backup utility vendors provide optional add-ons that automate the process of user managed backups. These add-ons, which usually do no more than the script shown above, are sold as extra cost add-ons to the base backup software.
RMAN is a database backup utility that comes with the Oracle database, at no extra cost. As such, it is aware of the internal structure of Oracle datafiles and controlfiles, and knows how to take consistent copies of data blocks even as they are being written to. Furthermore, it does this without putting tablespaces in backup mode. Therefore RMAN does not cause a massive increase in generated redo. Another advantage of using RMAN is that it backs up only those blocks that have held or currently hold data. Hence RMAN backups of datafiles are generally smaller than the datafiles themselves. In contrast, OS copies of datafiles have the same size as the original datafiles. Finally, with RMAN backups it is possible to recover individual blocks in case of block corruption of datafiles. Considering the above, it makes good sense to use RMAN instead of vendor supplied add-ons or homegrown user managed backup scripts.
4. Configuring RMAN:
RMAN is a command line utility that is installed as a part of a standard database installation. Note that RMAN is only a command interface to the database - the actual backup is performed by a dedicated server process on the Oracle database.
RMAN can invoked from the command line on the database host machine like so:
C:\>rman target /
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: ORCL (DBID=1036216947)
RMAN>
The first line is the one we type and the remaining ones are feedback from execution of the command. The net result is to leave us connected to the target database - the database we want to back up - with the RMAN> prompt, indicating that RMAN is ready for further work. Here we have invoked RMAN on the server, and have logged on to the server using an account that belongs to the ORA_DBA OS group. As described earlier, this enables us to connect to the target database (as sysdba - this is implicit) without having to supply a password. Note that on Windows, one must also set SQLNET.AUTHENTICATION_SERVICES=(NTS) in the sqlnet.ora file order to connect using OS authentication as above.
At this point a digression is in order. RMAN can be run in two modes - catalog and nocatalog. In the former, backup information and RMAN scripts are stored in another database known as the RMAN catalog. In the latter, RMAN stores backup information in the target database controlfile. Catalog mode is more flexible, but requires the maintenance of a separate database on another machine (there's no point in creating the RMAN catalog on the database to be backed up!). Nocatalog mode has the advantage of not needing a separate database, but places more responsibility on the controlfile. We will use nocatalog mode in our discussion, as this is a perfectly valid choice for sites with a small number of databases.
RMAN can be configured through various persistent parameters. Note that persistent parameters can be configured only for Oracle versions 9i and better. The current configuration can be seen via the "show all" command:
RMAN> show all;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'e:\backup\ctl_sp_bak_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT 'e:\backup\%U.bak' MAXPIECESIZE 4G;
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT 'e:\backup\%U.bak' MAXPIECESIZE 4G;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\ORACLE\ORA92\DATABASE\SNCFORCL.ORA'; # default
RMAN>
The reader is referred to the RMAN documentation for a detailed explanation of the options attached to each of these parameters. Here we will discuss only those of relevance to our backup requirements.
Retention Policy: This instructs RMAN on the backups that are eligible for deletion. For example: A retention policy with redundancy 2 would mean that two backups - the latest and the one prior to that - should be retained. All other backups are candidates for deletion. Retention policy can also be configured based on time - check the docs for details on this option.
Default Device Type: This can be "disk" or "sbt" (system backup to tape). We will backup to disk and then have our OS backup utility copy the completed backup, and other supporting files, to tape.
Controlfile Autobackup: This can be set to "on" or "off". When set to "on", RMAN takes a backup of the controlfile AND server parameter file each time a backup is performed. Note that "off" is the default.
Controlfile Autobackup Format: This tells RMAN where the controlfile backup is to be stored. The "%F" in the file name instructs RMAN to append the database identifier and backup timestamp to the backup filename. The database identifier, or DBID, is a unique integer identifier for the database. We have configured RMAN to store controlfile backups in the directory e:\backup.
Parallelism: This tells RMAN how many server processes you want dedicated to performing the backups.
Device Type Format: This specifies the location and name of the backup files. We need to specify the format for each channel. The "%U" ensures that Oracle appends a unique identifier to the backup file name. The MAXPIECESIZE attribute sets a maximum file size for each file in the backup set. We have configured RMAN to store backups in the directory e:\backup.
Any of the above parameters can be changed using the commands displayed by the "show all" command. For example, one can turn off controlfile autobackups by issuing:
RMAN> configure controlfile autobackup off;
using target database controlfile instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters are successfully stored
RMAN>
5. Scripting the backup:
With the background stuff out of the way, we now move on to the actual backup. We will write a simple script that will backup our database, verify that the backup can be restored and then delete all obsolete backups and archive logs (based on a redundancy of 2, as discussed above). The Windows scheduler will be used to run the script at a time of our choosing.
An Aside: Before we move on it is worth stating that RMAN can perform full or incremental backups. Full backups, as their name suggests, are backups of every data block in the datafiles. In contrast, Incremental backups backup only those database blocks that have changed since the last higher level backup. It would take us too far afield to detail the intricacies of incremental backups - we refer you to the Oracle documentation for more details on this. For the case at hand, we can afford to perform full backups every night as the database is relatively small.
The backup script, which we store in a file named "rman_backup.rcv", is very simple:
#contents of rman_backup.rcv. "#" denotes a comment line, and will be ignored by RMAN.
backup database plus archivelog;
restore database validate;
delete noprompt obsolete;
host 'copy C:\oracle\ora92\database\pwdorcl.ora e:\backup';
host 'copy C:\oracle\ora92\network\admin\tnsnames.ora e:\backup';
host 'copy C:\oracle\ora92\network\admin\listener.ora e:\backup';
exit;
The script backs up the database and all archive logs and then checks that the backup can be restored. After that it deletes backups according to the configured retention policy - the "noprompt" in the delete command instructs RMAN not to prompt us before deleting files. Finally it does an OS copy of the password file and the relevant network configuration files. The RMAN "host" command enables us to execute any operating system command (on Linux, for instance, we would use "cp" instead of "copy"). In the above script the database name is ORCL, hence the password file is pwdORCL.ora. You will need to adapt each of the "host 'copy..." commands in the script to your specific directory structure and filenames. As an aside, it is worth pointing out that SQL commands can be executed from RMAN. A couple of examples:
sql 'alter system archive log current';
sql "create pfile=''e:\backup\initORCL.ora'' from spfile";
The "sql" keyword tells RMAN what follows is to be interpreted as an SQL command. The actual SQL should be enclosed in single or double quotes. The latter is useful if the command contains single quotes, as in the second example above. Note: In the second example, the quotes enclosing the pfile path are two single quotes, and the quotes enclosing the entire command are double quotes.
The script, rman_backup.rcv, is invoked by the following one line batch file:
REM contents of rman_backup.bat
rman target / cmdfile rman_backup.rcv log rman_backup.log
The "target /" indicates that the script logs on to Oracle as sysdba via an OS account that belongs to the ORA_DBA group. The "cmdfile" option indicates the name of the command file that RMAN should execute, in this case it is rman_backup.rcv. The "log" option tells rman that we want a transcript of the RMAN session to be stored in the file that follows the option - rman_backup.log in this case. Remember to check the log file once between each backup for any errors that may have occurred. The log file is overwritten on each execution of the batch file so it may be worth changing the name to include a unique identifier (such as a timestamp). The backup scripts could reside anywhere on the server, but it may be best to keep them in e:\backup so that they are archived off to tape along with the backups.
The next step is to schedule our batch file (rman_backup.bat) to run at the desired interval. This is done by scheduling the batch file via the Window Scheduler wizard, which is accessed through Control Panel>Scheduled Tasks>Add Scheduled Task>.
Finally, it should be ensured that the entire backup directory (e:\backup) is copied to tape nightly, after the database backup has been completed. There is no need to backup any other Oracle related directory. The tapes must be stored offsite so that they aren't destroyed in case the site is struck by disaster. In a disaster situation, we can recreate the database and then restore and recover data files (with up to a 24 hour data loss), using the backups that are on tape. The procedure for recovering from a disaster will be covered in the third article of this series. In case the database fails (say due to datafile corruption, for example) but the host server remains available, we can recover right up to the instant of failure using the backup on disk together with all archive logs since the backup and the current online redo logs. Some of these scenarios will be covered in the next article of this series.
6. Summary and Further Reading:
This article provides steps on setting up automated RMAN based backups of Oracle databases on Windows. As with all critical DBA tasks, scripts and commands described above should be customised to your requirements and tested thoroughly before implementation on your production systems.
In the interest of brevity, we have had to rush through some of the detail that is relevant to backup and recovery. The reader is therefore urged to read the pertinent Oracle documentation for complete coverage. The books of interest are:
Oracle 9i Backup and Recovery Concepts - This book discusses basics of Oracle backup and recovery.
Oracle 9i Recovery Manager User's Guide - This book discusses backup and recovery using RMAN.
Oracle 9i Administrator's Guide - discusses backup related issues such as how to put the database in ARCHIVELOG mode.
These books can be downloaded, free of charge, from Oracle's documentation site. You will need to register with the Oracle Technology Network (OTN) to gain access to the documentation. Membership of OTN is free, and well worth it for Oracle professionals.
Thursday, October 15, 2009
Wednesday, October 14, 2009
Most informative blog from my friend(oracle)
http://www.saivamsi.blogspot.com/
http://www.dbspecialists.com/index.html
http://www.dbspecialists.com/index.html
Recovery from missing or corrupted control file-Controlfile
Case 1: A multiplexed copy of the control file is available.
On startup Oracle must read the control file in order to find out where the datafiles and online logs are located. Oracle expects to find control files at locations specified in the CONTROL_FILE initialisation parameter. The instance will fail to mount the database if any one of the control files are missing or corrupt. A brief error message will be displayed, with further details recorded in the alert log. The exact error message will vary depending on what has gone wrong. Here's an example:
SQL> startup
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
ORA-00205: error in identifying controlfile, check alert log for more info
SQL>
On checking the alert log, as suggested, we find the following:
ORA-00202: controlfile: 'e:\oracle_dup_dest\controlfile\ORCL\control02.ctl'
ORA-27046: file size is not a multiple of logical block size
OSD-04012: file size mismatch (OS 5447783)
The above corruption was introduced by manually editing the control file when the database was closed.
The solution is simple, provided you have at least one uncorrupted control file - replace the corrupted control file with a copy using operating system commands. Remember to rename the copied file. The database should now start up without any problems.
Case 2: All control files lost
What if you lose all your control files? In that case you have no option but to use a backup control file. The recovery needs to be performed from within RMAN, and requires that all logs (archived and current online logs) since the last backup are available. The logs are required because all datafiles must also be restored from backup. The database will then have to be recovered up to the time the control files went missing. This can only be done if all intervening logs are available. Here's an annotated transcript of a recovery session (as usual, lines in bold are commands to be typed, lines in italics are explanatory comments, other lines are RMAN feedback):
-- Connect to RMAN
C:\>rman target /
Recovery Manager: Release 9.2.0.4.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: ORCL (not mounted)
-- set DBID - get this from the name of the controlfile autobackup.
-- For example, if autobackup name is
-- CTL_SP_BAK_C-1507972899-20050124-00 the the DBID is
-- 1507972899. This step will not be required if the instance is
-- started up from RMAN
RMAN> set dbid 1507972899
executing command: SET DBID
--restore controlfile from autobackup. The backup is not at the default
--location so the path must be specified
RMAN> restore controlfile from 'e:\backup\CTL_SP_BAK_C-1507972899-20050124-00';
Starting restore at 26/JAN/05
using channel ORA_DISK_1
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=D:\ORACLE_DATA\CONTROLFILE\ORCL\CONTROL01.CTL
output filename=E:\ORACLE_DUP_DEST\CONTROLFILE\ORCL\CONTROL02.CTL
output filename=C:\ORACLE_DUP_DEST\CONTROLFILE\ORCL\CONTROL03.CTL
Finished restore at 26/JAN/05
-- Now that control files have been restored, the instance can mount the
-- database.
RMAN> mount database;
database mounted
-- All datafiles must be restored, since the controlfile is older than the current
-- datafiles. Datafile restore must be followed by recovery up to the current log.
RMAN> restore database;
Starting restore at 26/JAN/05
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE_DATA\DATAFILES\ORCL\SYSTEM01.DBF
restoring datafile 00004 to D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=E:\BACKUP\0DGB0I79_1_1.BAK tag=TAG20050124T115832 params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to D:\ORACLE_DATA\DATAFILES\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to D:\ORACLE_DATA\DATAFILES\ORCL\TOOLS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=E:\BACKUP\0CGB0I78_1_1.BAK tag=TAG20050124T115832 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 26/JAN/05
--Database must be recovered because all datafiles have been restored from
-- backup
RMAN> recover database;
Starting recover at 26/JAN/05
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 2 is already on disk as file E:\ORACLE_ARCHIVE\ORCL\1_2.ARC
archive log thread 1 sequence 4 is already on disk as file D:\ORACLE_DATA\LOGS\ORCL\REDO02A.LOG
archive log thread 1 sequence 5 is already on disk as file D:\ORACLE_DATA\LOGS\ORCL\REDO01A.LOG
archive log thread 1 sequence 6 is already on disk as file D:\ORACLE_DATA\LOGS\ORCL\REDO03A.LOG
archive log filename=E:\ORACLE_ARCHIVE\ORCL\1_2.ARC thread=1 sequence=2
archive log filename=E:\ORACLE_ARCHIVE\ORCL\1_3.ARC thread=1 sequence=3
archive log filename=E:\ORACLE_DATA\LOGS\ORCL\REDO02A.LOG thread=1 sequence=4
archive log filename=E:\ORACLE_DATA\LOGS\ORCL\REDO01A.LOG thread=1 sequence=5
archive log filename=E:\ORACLE_DATA\LOGS\ORCL\REDO03A.LOG thread=1 sequence=6
media recovery complete
Finished recover at 26/JAN/05
-- Recovery completed. The database must be opened with RESETLOGS
-- because a backup control file was used. Can also use
-- "alter database open resetlogs" instead.
RMAN> open resetlogs database;
database opened
Several points are worth emphasising.
Recovery using a backup controlfile should be done only if a current control file is unavailable.
All datafiles must be restored from backup. This means the database will need to be recovered using archived and online redo logs. These MUST be available for recovery until the time of failure.
As with any database recovery involving RESETLOGS, take a fresh backup immediately.
Technically the above is an example of complete recovery - since all committed transactions were recovered. However, some references consider this to be incomplete recovery because the database log sequence had to be reset.
After recovery using a backup controlfile, all temporary files associated with locally-managed tablespaces are no longer available. You can check that this is so by querying the view V$TEMPFILE - no rows will be returned. Therefore tempfiles must be added (or recreated) before the database is made available for general use. In the case at hand, the tempfile already exists so we merely add it to the temporary tablespace. This can be done using SQLPlus or any tool of your choice:
SQL> alter tablespace temp add tempfile
'D:\oracle_data\datafiles\ORCL\TEMP01.DBF';
Tablespace altered.
SQL>
Check that the file is available by querying v$TEMPFILE.
6. Wrap up:
In an article this size it is impossible to cover all possible recovery scenarios that one might encounter in real life. The above examples will, I hope, provide you with some concrete situations to try out on your test box. The best preparation for real-life recovery is practice. Simulate as many variations of the above situations, and others, as you can think up. Then try recovering from them. The exercise will improve your recovery skills, clarify conceptual issues and highlight deficiencies in your backup strategy.
On startup Oracle must read the control file in order to find out where the datafiles and online logs are located. Oracle expects to find control files at locations specified in the CONTROL_FILE initialisation parameter. The instance will fail to mount the database if any one of the control files are missing or corrupt. A brief error message will be displayed, with further details recorded in the alert log. The exact error message will vary depending on what has gone wrong. Here's an example:
SQL> startup
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
ORA-00205: error in identifying controlfile, check alert log for more info
SQL>
On checking the alert log, as suggested, we find the following:
ORA-00202: controlfile: 'e:\oracle_dup_dest\controlfile\ORCL\control02.ctl'
ORA-27046: file size is not a multiple of logical block size
OSD-04012: file size mismatch (OS 5447783)
The above corruption was introduced by manually editing the control file when the database was closed.
The solution is simple, provided you have at least one uncorrupted control file - replace the corrupted control file with a copy using operating system commands. Remember to rename the copied file. The database should now start up without any problems.
Case 2: All control files lost
What if you lose all your control files? In that case you have no option but to use a backup control file. The recovery needs to be performed from within RMAN, and requires that all logs (archived and current online logs) since the last backup are available. The logs are required because all datafiles must also be restored from backup. The database will then have to be recovered up to the time the control files went missing. This can only be done if all intervening logs are available. Here's an annotated transcript of a recovery session (as usual, lines in bold are commands to be typed, lines in italics are explanatory comments, other lines are RMAN feedback):
-- Connect to RMAN
C:\>rman target /
Recovery Manager: Release 9.2.0.4.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: ORCL (not mounted)
-- set DBID - get this from the name of the controlfile autobackup.
-- For example, if autobackup name is
-- CTL_SP_BAK_C-1507972899-20050124-00 the the DBID is
-- 1507972899. This step will not be required if the instance is
-- started up from RMAN
RMAN> set dbid 1507972899
executing command: SET DBID
--restore controlfile from autobackup. The backup is not at the default
--location so the path must be specified
RMAN> restore controlfile from 'e:\backup\CTL_SP_BAK_C-1507972899-20050124-00';
Starting restore at 26/JAN/05
using channel ORA_DISK_1
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=D:\ORACLE_DATA\CONTROLFILE\ORCL\CONTROL01.CTL
output filename=E:\ORACLE_DUP_DEST\CONTROLFILE\ORCL\CONTROL02.CTL
output filename=C:\ORACLE_DUP_DEST\CONTROLFILE\ORCL\CONTROL03.CTL
Finished restore at 26/JAN/05
-- Now that control files have been restored, the instance can mount the
-- database.
RMAN> mount database;
database mounted
-- All datafiles must be restored, since the controlfile is older than the current
-- datafiles. Datafile restore must be followed by recovery up to the current log.
RMAN> restore database;
Starting restore at 26/JAN/05
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE_DATA\DATAFILES\ORCL\SYSTEM01.DBF
restoring datafile 00004 to D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=E:\BACKUP\0DGB0I79_1_1.BAK tag=TAG20050124T115832 params=NULL
channel ORA_DISK_1: restore complete
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to D:\ORACLE_DATA\DATAFILES\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to D:\ORACLE_DATA\DATAFILES\ORCL\TOOLS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=E:\BACKUP\0CGB0I78_1_1.BAK tag=TAG20050124T115832 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 26/JAN/05
--Database must be recovered because all datafiles have been restored from
-- backup
RMAN> recover database;
Starting recover at 26/JAN/05
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 2 is already on disk as file E:\ORACLE_ARCHIVE\ORCL\1_2.ARC
archive log thread 1 sequence 4 is already on disk as file D:\ORACLE_DATA\LOGS\ORCL\REDO02A.LOG
archive log thread 1 sequence 5 is already on disk as file D:\ORACLE_DATA\LOGS\ORCL\REDO01A.LOG
archive log thread 1 sequence 6 is already on disk as file D:\ORACLE_DATA\LOGS\ORCL\REDO03A.LOG
archive log filename=E:\ORACLE_ARCHIVE\ORCL\1_2.ARC thread=1 sequence=2
archive log filename=E:\ORACLE_ARCHIVE\ORCL\1_3.ARC thread=1 sequence=3
archive log filename=E:\ORACLE_DATA\LOGS\ORCL\REDO02A.LOG thread=1 sequence=4
archive log filename=E:\ORACLE_DATA\LOGS\ORCL\REDO01A.LOG thread=1 sequence=5
archive log filename=E:\ORACLE_DATA\LOGS\ORCL\REDO03A.LOG thread=1 sequence=6
media recovery complete
Finished recover at 26/JAN/05
-- Recovery completed. The database must be opened with RESETLOGS
-- because a backup control file was used. Can also use
-- "alter database open resetlogs" instead.
RMAN> open resetlogs database;
database opened
Several points are worth emphasising.
Recovery using a backup controlfile should be done only if a current control file is unavailable.
All datafiles must be restored from backup. This means the database will need to be recovered using archived and online redo logs. These MUST be available for recovery until the time of failure.
As with any database recovery involving RESETLOGS, take a fresh backup immediately.
Technically the above is an example of complete recovery - since all committed transactions were recovered. However, some references consider this to be incomplete recovery because the database log sequence had to be reset.
After recovery using a backup controlfile, all temporary files associated with locally-managed tablespaces are no longer available. You can check that this is so by querying the view V$TEMPFILE - no rows will be returned. Therefore tempfiles must be added (or recreated) before the database is made available for general use. In the case at hand, the tempfile already exists so we merely add it to the temporary tablespace. This can be done using SQLPlus or any tool of your choice:
SQL> alter tablespace temp add tempfile
'D:\oracle_data\datafiles\ORCL\TEMP01.DBF';
Tablespace altered.
SQL>
Check that the file is available by querying v$TEMPFILE.
6. Wrap up:
In an article this size it is impossible to cover all possible recovery scenarios that one might encounter in real life. The above examples will, I hope, provide you with some concrete situations to try out on your test box. The best preparation for real-life recovery is practice. Simulate as many variations of the above situations, and others, as you can think up. Then try recovering from them. The exercise will improve your recovery skills, clarify conceptual issues and highlight deficiencies in your backup strategy.
Recovery from missing or corrupted redo log group-Rman
Case 1: A multiplexed copy of the missing log is available.
If a redo log is missing, it should be restored from a multiplexed copy, if possible. This is the only way to recover without any losses. Here's an example, where I attempt to startup from SQLPlus when a redo log is missing:
SQL> startup
ORACLE instance started.
Total System Global Area 131555128 bytes
Fixed Size 454456 bytes
Variable Size 88080384 bytes
Database Buffers 41943040 bytes
Redo Buffers 1077248 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: 'D:\ORACLE_DATA\LOGS\ORCL\REDO03A.LOG'
SQL>
To fix this we simply copy REDO03A.LOG from its multiplexed location on E: to the above location on E:. After copying the file, we issue an "alter database open" from the above SQLPlus session:
SQL> alter database open;
Database altered.
SQL>
That's it - the database is open for use.
Case 2: All members of a log group lost.
In this case an incomplete recovery is the best we can do. We will lose all transactions from the missing log and all subsequent logs. We illustrate using the same example as above. The error message indicates that members of log group 3 are missing. We don't have a copy of this file, so we know that an incomplete recovery is required. The first step is to determine how much can be recovered. In order to do this, we query the V$LOG view (when in the mount state) to find the system change number (SCN) that we can recover to (Reminder: the SCN is a monotonically increasing number that is incremented whenever a commit is issued):
--The database should be in the mount state for v$log access
SQL> select first_change# from v$log where group#=3 ;
FIRST_CHANGE#
-------------
370255
SQL>
The FIRST_CHANGE# is the first SCN stamped in the missing log. This implies that the last SCN stamped in the previous log is 370254 (FIRST_CHANGE#-1). This is the highest SCN that we can recover to. In order to do the recovery we must first restore ALL datafiles to this SCN, followed by recovery (also up to this SCN). This is an incomplete recovery, so we must open the database resetlogs after we're done. Here's a transcript of the recovery session (typed commands in bold, comments in italics, all other lines are RMAN feedback):
C:\>rman target /
Recovery Manager: Release 9.2.0.4.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: ORCL (DBID=1507972899)
--Restore ENTIRE database to determined SCN
RMAN> restore database until scn 370254;
Starting restore at 26/JAN/05
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE_DATA\DATAFILES\ORCL\SYSTEM01.DBF
restoring datafile 00004 to D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF
channel ORA_DISK_2: starting datafile backupset restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
restoring datafile 00002 to D:\ORACLE_DATA\DATAFILES\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to D:\ORACLE_DATA\DATAFILES\ORCL\TOOLS01.DBF
channel ORA_DISK_2: restored backup piece 1
piece handle=E:\BACKUP\13GB14IB_1_1.BAK tag=TAG20050124T171139 params=NUL
channel ORA_DISK_2: restore complete
channel ORA_DISK_1: restored backup piece 1
piece handle=E:\BACKUP\14GB14IB_1_1.BAK tag=TAG20050124T171139 params=NUL
channel ORA_DISK_1: restore complete
Finished restore at 26/JAN/05
--Recover database
RMAN> recover database until scn 370254;
Starting recover at 26/JAN/05
using channel ORA_DISK_1
using channel ORA_DISK_2
starting media recovery
archive log thread 1 sequence 9 is already on disk as file E:\ORACLE_ARCHIVE\ORCL\1_9.ARC
archive log thread 1 sequence 10 is already on disk as file E:\ORACLE_ARCHIVE\ORCL\1_10.ARC
archive log thread 1 sequence 11 is already on disk as file E:\ORACLE_ARCHIVE\ORCL\1_11.ARC
archive log thread 1 sequence 12 is already on disk as file E:\ORACLE_ARCHIVE\ORCL\1_12.ARC
archive log filename=E:\ORACLE_ARCHIVE\ORCL\1_9.ARC thread=1 sequence=9
archive log filename=E:\ORACLE_ARCHIVE\ORCL\1_10.ARC thread=1 sequence=10
media recovery complete
Finished recover at 26/JAN/05
--open database with RESETLOGS (see comments below)
RMAN> alter database open resetlogs;
database opened
RMAN>
The following points should be noted:
The entire database must be restored to the SCN that has been determined by querying v$log.
All changes beyond that SCN are lost. This method of recovery should be used only if you are sure that you cannot do better. Be sure to multiplex your redo logs, and (space permitting) your archived logs!
The database must be opened with RESETLOGS, as a required log has not been applied. This resets the log sequence to zero, thereby rendering all prior backups worthless. Therefore, the first step after opening a database RESETLOGS is to take a fresh backup. Note that the RESETLOGS option must be used for any incomplete recovery.
If a redo log is missing, it should be restored from a multiplexed copy, if possible. This is the only way to recover without any losses. Here's an example, where I attempt to startup from SQLPlus when a redo log is missing:
SQL> startup
ORACLE instance started.
Total System Global Area 131555128 bytes
Fixed Size 454456 bytes
Variable Size 88080384 bytes
Database Buffers 41943040 bytes
Redo Buffers 1077248 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: 'D:\ORACLE_DATA\LOGS\ORCL\REDO03A.LOG'
SQL>
To fix this we simply copy REDO03A.LOG from its multiplexed location on E: to the above location on E:. After copying the file, we issue an "alter database open" from the above SQLPlus session:
SQL> alter database open;
Database altered.
SQL>
That's it - the database is open for use.
Case 2: All members of a log group lost.
In this case an incomplete recovery is the best we can do. We will lose all transactions from the missing log and all subsequent logs. We illustrate using the same example as above. The error message indicates that members of log group 3 are missing. We don't have a copy of this file, so we know that an incomplete recovery is required. The first step is to determine how much can be recovered. In order to do this, we query the V$LOG view (when in the mount state) to find the system change number (SCN) that we can recover to (Reminder: the SCN is a monotonically increasing number that is incremented whenever a commit is issued):
--The database should be in the mount state for v$log access
SQL> select first_change# from v$log where group#=3 ;
FIRST_CHANGE#
-------------
370255
SQL>
The FIRST_CHANGE# is the first SCN stamped in the missing log. This implies that the last SCN stamped in the previous log is 370254 (FIRST_CHANGE#-1). This is the highest SCN that we can recover to. In order to do the recovery we must first restore ALL datafiles to this SCN, followed by recovery (also up to this SCN). This is an incomplete recovery, so we must open the database resetlogs after we're done. Here's a transcript of the recovery session (typed commands in bold, comments in italics, all other lines are RMAN feedback):
C:\>rman target /
Recovery Manager: Release 9.2.0.4.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: ORCL (DBID=1507972899)
--Restore ENTIRE database to determined SCN
RMAN> restore database until scn 370254;
Starting restore at 26/JAN/05
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE_DATA\DATAFILES\ORCL\SYSTEM01.DBF
restoring datafile 00004 to D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF
channel ORA_DISK_2: starting datafile backupset restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
restoring datafile 00002 to D:\ORACLE_DATA\DATAFILES\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to D:\ORACLE_DATA\DATAFILES\ORCL\TOOLS01.DBF
channel ORA_DISK_2: restored backup piece 1
piece handle=E:\BACKUP\13GB14IB_1_1.BAK tag=TAG20050124T171139 params=NUL
channel ORA_DISK_2: restore complete
channel ORA_DISK_1: restored backup piece 1
piece handle=E:\BACKUP\14GB14IB_1_1.BAK tag=TAG20050124T171139 params=NUL
channel ORA_DISK_1: restore complete
Finished restore at 26/JAN/05
--Recover database
RMAN> recover database until scn 370254;
Starting recover at 26/JAN/05
using channel ORA_DISK_1
using channel ORA_DISK_2
starting media recovery
archive log thread 1 sequence 9 is already on disk as file E:\ORACLE_ARCHIVE\ORCL\1_9.ARC
archive log thread 1 sequence 10 is already on disk as file E:\ORACLE_ARCHIVE\ORCL\1_10.ARC
archive log thread 1 sequence 11 is already on disk as file E:\ORACLE_ARCHIVE\ORCL\1_11.ARC
archive log thread 1 sequence 12 is already on disk as file E:\ORACLE_ARCHIVE\ORCL\1_12.ARC
archive log filename=E:\ORACLE_ARCHIVE\ORCL\1_9.ARC thread=1 sequence=9
archive log filename=E:\ORACLE_ARCHIVE\ORCL\1_10.ARC thread=1 sequence=10
media recovery complete
Finished recover at 26/JAN/05
--open database with RESETLOGS (see comments below)
RMAN> alter database open resetlogs;
database opened
RMAN>
The following points should be noted:
The entire database must be restored to the SCN that has been determined by querying v$log.
All changes beyond that SCN are lost. This method of recovery should be used only if you are sure that you cannot do better. Be sure to multiplex your redo logs, and (space permitting) your archived logs!
The database must be opened with RESETLOGS, as a required log has not been applied. This resets the log sequence to zero, thereby rendering all prior backups worthless. Therefore, the first step after opening a database RESETLOGS is to take a fresh backup. Note that the RESETLOGS option must be used for any incomplete recovery.
Recovery from missing or corrupted datafile(s)-Rman
Case 1: Recovery from corrupted or missing datafile
This scenario deals with a situation where a datafile has gone missing, or is corrupted beyond repair. For concreteness, we look at a case where a datafile is missing. Below is a transcript of an SQL Plus session that attempts to open a database with a missing datafile (typed commands in bold, lines in italics are my comments, all other lines are feedback from SQL Plus):
--open SQL Plus from the command line without
--logging on to database
C:\>sqlplus /nolog
SQL*Plus: Release 9.2.0.4.0 - Production on Tue Jan 25 14:52:41 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
--Connect to the idle Oracle process as a privileged user and start up instance
SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 131555128 bytes
Fixed Size 454456 bytes
Variable Size 88080384 bytes
Database Buffers 41943040 bytes
Redo Buffers 1077248 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF'
SQL>
The error message tells us that file# 4 is missing. Note that although the startup command has failed, the database is in the mount state. Thus, the database control file, which is also the RMAN repository can be accessed by the instance and by RMAN. We now recover the missing file using RMAN. The transcript of the recovery session is reproduced below (bold lines are typed commands, comments in italics, the rest is feedback from RMAN):
--logon to RMAN
C:\>rman target /
Recovery Manager: Release 9.2.0.4.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: ORCL (DBID=1507972899)
--restore missing datafile
RMAN> restore datafile 4;
Starting restore at 26/JAN/05
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=14 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=15 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\BACKUP\0QGB0UEC_1_1.BAK tag=TAG20050124T152708 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 26/JAN/05
--recover restored datafile - RMAN applies all logs automatically
RMAN> recover datafile 4;
Starting recover at 26/JAN/05 using channel ORA_DISK_1
using channel ORA_DISK_2
starting media recovery
archive log thread 1 sequence 4 is already on disk as file E:\ORACLE_ARCHIVE\ORCL\1_4.ARC
archive log thread 1 sequence 5 is already on disk as file C:\ORACLE_ARCHIVE\ORCL\1_5.ARC
archive log thread 1 sequence 6 is already on disk as file E:\ORACLE_ARCHIVE\ORCL\1_6.ARC
archive log thread 1 sequence 7 is already on disk as file E:\ORACLE_ARCHIVE\ORCL\1_7.ARC
archive log filename=E:\ORACLE_ARCHIVE\ORCL\1_4.ARC thread=1 sequence=4
archive log filename=C:\ORACLE_ARCHIVE\ORCL\1_5.ARC thread=1 sequence=5
media recovery complete
Finished recover at 26/JAN/05
--open database for general use
RMAN> alter database open;
database opened
RMAN>
In the above scenario, the database is already in the mount state before the RMAN session is initiated. If the database is not mounted, you should issue a "startup mount" command before attempting to restore the missing datafile. The database must be mounted before any datafile recovery can be done.
If the database is already open when datafile corruption is detected, you can recover the datafile without shutting down the database. The only additional step is to take the relevant tablespace offline before starting recovery. In this case you would perform recovery at the tablespace level. The commands are:
C:\>rman target /
Recovery Manager: Release 9.2.0.4.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: ORCL (DBID=1507972899)
--offline affected tablespace
RMAN> sql 'alter tablespace USERS offline immediate';
using target database controlfile instead of recovery catalog
sql statement: alter tablespace USERS offline immediate
--recover offlined tablespace
RMAN> recover tablespace USERS;
Starting recover at 26/JAN/05
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=14 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=12 devtype=DISK
starting media recovery
media recovery complete
Finished recover at 26/JAN/05
--online recovered tablespace
RMAN> sql 'alter tablespace USERS online';
sql statement: alter tablespace USERS online
RMAN>
Here we have used the SQL command, which allows us to execute arbitrary SQL from within RMAN.
Case 2: Recovery from block corruption
It is possible to recover corrupted blocks using RMAN backups. This is a somewhat exotic scenario, but it can be useful in certain circumstances, as illustrated by the following example. Here's the situation: a user connected to SQLPlus gets a data block corruption error when she queries a table. Here's a part of the session transcript:
SQL> connect testuser/testpassword
Connected.
SQL> select count(*) from test_table;
select count(*) from test_table
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 2015)
ORA-01110: data file 4: 'D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF'
Since we know the file and block number, we can perform block level recovery using RMAN. This is best illustrated by example:
C:\>rman target /
Recovery Manager: Release 9.2.0.4.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: ORCL (DBID=1507972899)
--restore AND recover specific block
RMAN> blockrecover datafile 4 block 2015;
Starting blockrecover at 26/JAN/05
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=19 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=20 devtype=DISK
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00004
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=E:\BACKUP\0QGB0UEC_1_1.BAK tag=TAG20050124T152708 params=NULL
channel ORA_DISK_1: block restore complete
starting media recovery
media recovery complete
Finished blockrecover at 26/JAN/05
RMAN>
Now our user should be able to query the table from her SQLPlus session. Here's her session transcript after block recovery.
SQL> select count(*) from test_table;
COUNT(*)
----------
217001
SQL>
A couple of important points regarding block recovery:
Block recovery can only be done using RMAN.
The entire database can be open while performing block recovery.
Check all database files for corruption. This is important - there could be other corrupted blocks. Verification of database files can be done using RMAN or the dbverify utility. To verify using RMAN simply do a complete database backup with default settings. If RMAN detects block corruption, it will exit with an error message pointing out the guilty file/block.
This scenario deals with a situation where a datafile has gone missing, or is corrupted beyond repair. For concreteness, we look at a case where a datafile is missing. Below is a transcript of an SQL Plus session that attempts to open a database with a missing datafile (typed commands in bold, lines in italics are my comments, all other lines are feedback from SQL Plus):
--open SQL Plus from the command line without
--logging on to database
C:\>sqlplus /nolog
SQL*Plus: Release 9.2.0.4.0 - Production on Tue Jan 25 14:52:41 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
--Connect to the idle Oracle process as a privileged user and start up instance
SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 131555128 bytes
Fixed Size 454456 bytes
Variable Size 88080384 bytes
Database Buffers 41943040 bytes
Redo Buffers 1077248 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: 'D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF'
SQL>
The error message tells us that file# 4 is missing. Note that although the startup command has failed, the database is in the mount state. Thus, the database control file, which is also the RMAN repository can be accessed by the instance and by RMAN. We now recover the missing file using RMAN. The transcript of the recovery session is reproduced below (bold lines are typed commands, comments in italics, the rest is feedback from RMAN):
--logon to RMAN
C:\>rman target /
Recovery Manager: Release 9.2.0.4.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: ORCL (DBID=1507972899)
--restore missing datafile
RMAN> restore datafile 4;
Starting restore at 26/JAN/05
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=14 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=15 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\BACKUP\0QGB0UEC_1_1.BAK tag=TAG20050124T152708 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 26/JAN/05
--recover restored datafile - RMAN applies all logs automatically
RMAN> recover datafile 4;
Starting recover at 26/JAN/05 using channel ORA_DISK_1
using channel ORA_DISK_2
starting media recovery
archive log thread 1 sequence 4 is already on disk as file E:\ORACLE_ARCHIVE\ORCL\1_4.ARC
archive log thread 1 sequence 5 is already on disk as file C:\ORACLE_ARCHIVE\ORCL\1_5.ARC
archive log thread 1 sequence 6 is already on disk as file E:\ORACLE_ARCHIVE\ORCL\1_6.ARC
archive log thread 1 sequence 7 is already on disk as file E:\ORACLE_ARCHIVE\ORCL\1_7.ARC
archive log filename=E:\ORACLE_ARCHIVE\ORCL\1_4.ARC thread=1 sequence=4
archive log filename=C:\ORACLE_ARCHIVE\ORCL\1_5.ARC thread=1 sequence=5
media recovery complete
Finished recover at 26/JAN/05
--open database for general use
RMAN> alter database open;
database opened
RMAN>
In the above scenario, the database is already in the mount state before the RMAN session is initiated. If the database is not mounted, you should issue a "startup mount" command before attempting to restore the missing datafile. The database must be mounted before any datafile recovery can be done.
If the database is already open when datafile corruption is detected, you can recover the datafile without shutting down the database. The only additional step is to take the relevant tablespace offline before starting recovery. In this case you would perform recovery at the tablespace level. The commands are:
C:\>rman target /
Recovery Manager: Release 9.2.0.4.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: ORCL (DBID=1507972899)
--offline affected tablespace
RMAN> sql 'alter tablespace USERS offline immediate';
using target database controlfile instead of recovery catalog
sql statement: alter tablespace USERS offline immediate
--recover offlined tablespace
RMAN> recover tablespace USERS;
Starting recover at 26/JAN/05
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=14 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=12 devtype=DISK
starting media recovery
media recovery complete
Finished recover at 26/JAN/05
--online recovered tablespace
RMAN> sql 'alter tablespace USERS online';
sql statement: alter tablespace USERS online
RMAN>
Here we have used the SQL command, which allows us to execute arbitrary SQL from within RMAN.
Case 2: Recovery from block corruption
It is possible to recover corrupted blocks using RMAN backups. This is a somewhat exotic scenario, but it can be useful in certain circumstances, as illustrated by the following example. Here's the situation: a user connected to SQLPlus gets a data block corruption error when she queries a table. Here's a part of the session transcript:
SQL> connect testuser/testpassword
Connected.
SQL> select count(*) from test_table;
select count(*) from test_table
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 2015)
ORA-01110: data file 4: 'D:\ORACLE_DATA\DATAFILES\ORCL\USERS01.DBF'
Since we know the file and block number, we can perform block level recovery using RMAN. This is best illustrated by example:
C:\>rman target /
Recovery Manager: Release 9.2.0.4.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: ORCL (DBID=1507972899)
--restore AND recover specific block
RMAN> blockrecover datafile 4 block 2015;
Starting blockrecover at 26/JAN/05
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=19 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=20 devtype=DISK
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00004
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=E:\BACKUP\0QGB0UEC_1_1.BAK tag=TAG20050124T152708 params=NULL
channel ORA_DISK_1: block restore complete
starting media recovery
media recovery complete
Finished blockrecover at 26/JAN/05
RMAN>
Now our user should be able to query the table from her SQLPlus session. Here's her session transcript after block recovery.
SQL> select count(*) from test_table;
COUNT(*)
----------
217001
SQL>
A couple of important points regarding block recovery:
Block recovery can only be done using RMAN.
The entire database can be open while performing block recovery.
Check all database files for corruption. This is important - there could be other corrupted blocks. Verification of database files can be done using RMAN or the dbverify utility. To verify using RMAN simply do a complete database backup with default settings. If RMAN detects block corruption, it will exit with an error message pointing out the guilty file/block.
Oracle Backup and Recovery --Rman introduction
1. Introduction:This article is the second in a series of three, introducing Oracle's RMAN (Recovery MANager) backup and recovery tool to novice DBAs. The first article dealt with taking RMAN backups. The aim in the present piece is to cover some recovery scenarios. In all the scenarios it is assumed that:
The database host server is still up and running. Recovery in a situation where the server is unavailable will be the subject of the next article.
The last full backup is available on disk.
All archived logs since the last backup are available on disk. Most of the examples here deal with complete recovery, where all committed transactions up to the point of failure are recovered. We also discuss some cases of incomplete recovery, where the database is recovered to a time prior to failure. Generally one would perform an incomplete recovery only when some of the required logs (archived or online) are missing. One of the examples below deals with just this situation. There are also other valid reasons for performing incomplete recovery - example: to recover a table that has been accidentally dropped. We will not go into these incomplete recovery situations in this article. The following scenarios are discussed:
Recovery from corrupted or missing datafile(s).
Recovery from corrupted or missing online redo logs.
Recovery from corrupted or missing control files. In each of the examples discussed it is assumed that RMAN is invoked on the database host machine using an OS account belonging to the ORA_DBA group. This permits connections to the database without a password, i.e. using "/ as sysdba", as discussed in the first article of this series.2. Instance and database In order to understand some of the recovery procedures below, it is necessary to appreciate the difference between an instance and a database.An instance is made up of Oracle process(es) and associated memory. On Windows operating systems there is a single, multithreaded Oracle process which is associated with a Windows service. The service is normally created when the database is first created, so, as far as this discussion is concerned, the service already exists (for completeness we point out that the service is created and manipulated using the oradim utility - check the Oracle Administrator's Guide for details on oradim). For our purposes then, an Oracle instance refers to the memory associated with the pre-existing Oracle process. The instance is created when a startup command is issued from the command line (SQL Plus or RMAN for example) or via a GUI tool such as Oracle Enterprise Manager. More on this below. A database , on the other hand, refers to the files comprising the database. These files exist independent of any instance. An instance requires that the computer be powered on and also requires that the Oracle service exists. The database, in contrast, exists even if the computer is powered down.An instance can be started up in various modes from SQL Plus or RMAN using the startup command. Three variants of the startup command, relevant to the present discussion are:
startup nomount: The instance is started up - i.e. the required memory structures are associated with the pre-existing Oracle process. At this point the instance is not associated with any database.
startup mount: The instance is started up and the database control file is read. At this point Oracle knows the locations of all files that make up the database. However, the database is not open. It is possible to go from the nomount state to the mount state using the SQL command "alter database mount".
startup: The instance is started up, the control file is read and the database opened for general use. At this point the instance is fully associated with the database. It is possible to go from nomount / mount to the open state using the SQL command "alter database open". There are other options to the startup command which we will not go into here. See the Oracle Administrator's Guide for further details. Note that startup is not a standard SQL command; it can only be executed from an Oracle tool such as SQL Plus or RMAN.
The database host server is still up and running. Recovery in a situation where the server is unavailable will be the subject of the next article.
The last full backup is available on disk.
All archived logs since the last backup are available on disk. Most of the examples here deal with complete recovery, where all committed transactions up to the point of failure are recovered. We also discuss some cases of incomplete recovery, where the database is recovered to a time prior to failure. Generally one would perform an incomplete recovery only when some of the required logs (archived or online) are missing. One of the examples below deals with just this situation. There are also other valid reasons for performing incomplete recovery - example: to recover a table that has been accidentally dropped. We will not go into these incomplete recovery situations in this article. The following scenarios are discussed:
Recovery from corrupted or missing datafile(s).
Recovery from corrupted or missing online redo logs.
Recovery from corrupted or missing control files. In each of the examples discussed it is assumed that RMAN is invoked on the database host machine using an OS account belonging to the ORA_DBA group. This permits connections to the database without a password, i.e. using "/ as sysdba", as discussed in the first article of this series.2. Instance and database In order to understand some of the recovery procedures below, it is necessary to appreciate the difference between an instance and a database.An instance is made up of Oracle process(es) and associated memory. On Windows operating systems there is a single, multithreaded Oracle process which is associated with a Windows service. The service is normally created when the database is first created, so, as far as this discussion is concerned, the service already exists (for completeness we point out that the service is created and manipulated using the oradim utility - check the Oracle Administrator's Guide for details on oradim). For our purposes then, an Oracle instance refers to the memory associated with the pre-existing Oracle process. The instance is created when a startup command is issued from the command line (SQL Plus or RMAN for example) or via a GUI tool such as Oracle Enterprise Manager. More on this below. A database , on the other hand, refers to the files comprising the database. These files exist independent of any instance. An instance requires that the computer be powered on and also requires that the Oracle service exists. The database, in contrast, exists even if the computer is powered down.An instance can be started up in various modes from SQL Plus or RMAN using the startup command. Three variants of the startup command, relevant to the present discussion are:
startup nomount: The instance is started up - i.e. the required memory structures are associated with the pre-existing Oracle process. At this point the instance is not associated with any database.
startup mount: The instance is started up and the database control file is read. At this point Oracle knows the locations of all files that make up the database. However, the database is not open. It is possible to go from the nomount state to the mount state using the SQL command "alter database mount".
startup: The instance is started up, the control file is read and the database opened for general use. At this point the instance is fully associated with the database. It is possible to go from nomount / mount to the open state using the SQL command "alter database open". There are other options to the startup command which we will not go into here. See the Oracle Administrator's Guide for further details. Note that startup is not a standard SQL command; it can only be executed from an Oracle tool such as SQL Plus or RMAN.
Tuesday, October 13, 2009
TO GET THE DDLS
set heading off; set echo off; Set pages 999; set long 90000;
-------TO GET THE DDLS OF A PARTICULAR TABLE OR A INDEX FROM A USER---------
SELECT DBMS_METADATA.GET_DDL('TABLE','EMP','ORACLE9I') FROM DUAL;
CREATE TABLE "ORACLE9I"."EMP" ( "NO" NUMBER, "NAME" VARCHAR2(10) ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"
select dbms_metadata.get_ddl('INDEX','DEPT_IDX','SCOTT') from dual;
--------------TO GET THE DDLS OF ALL THE TABLES AND INDEXS IN A USER---
select dbms_metadata.get_ddl('TABLE',u.table_name)from user_tables u;
SELECT DBMS_METADATA.GET_DDL('INDEX',U.INDEX_NAME)FROM USER_INDEXES U;
----------TO GET THE DDLS OF A PARTICULAR TABLESPACE-----------------------
select dbms_metadata.get_ddl('TABLESPACE','USERS')FROM DUAL;
CREATE TABLESPACE "USERS" DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS1.DBF' SIZE 5242880 LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT MANUAL
-----TO GET THE DDLS OF A ALL TABLESPACES IN THE DATABASE-------------
select dbms_metadata.get_ddl('TABLESPACE',u.tablespace_name) FROM USER_tablespaces U;
-------TO GET THE DDLS OF A PARTICULAR TABLE OR A INDEX FROM A USER---------
SELECT DBMS_METADATA.GET_DDL('TABLE','EMP','ORACLE9I') FROM DUAL;
CREATE TABLE "ORACLE9I"."EMP" ( "NO" NUMBER, "NAME" VARCHAR2(10) ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS"
select dbms_metadata.get_ddl('INDEX','DEPT_IDX','SCOTT') from dual;
--------------TO GET THE DDLS OF ALL THE TABLES AND INDEXS IN A USER---
select dbms_metadata.get_ddl('TABLE',u.table_name)from user_tables u;
SELECT DBMS_METADATA.GET_DDL('INDEX',U.INDEX_NAME)FROM USER_INDEXES U;
----------TO GET THE DDLS OF A PARTICULAR TABLESPACE-----------------------
select dbms_metadata.get_ddl('TABLESPACE','USERS')FROM DUAL;
CREATE TABLESPACE "USERS" DATAFILE 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS1.DBF' SIZE 5242880 LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT MANUAL
-----TO GET THE DDLS OF A ALL TABLESPACES IN THE DATABASE-------------
select dbms_metadata.get_ddl('TABLESPACE',u.tablespace_name) FROM USER_tablespaces U;
Upgrade Oracle 10.1.0.1 to 10.2.0.1
Upgrade Oracle 10.1.0.1 to 10.2.0.1
1. Install software 10.1.0.1 in 1st ORACLE_HOME
Install oracle 10.1.0.1 before upgrading this to 10.2.0.1.
You must have 10.1.0.1 version installed along with database.2. RequirementsOracle DB: Oracle 10.1.0.1 (later) Operating System: Windows 2000 (Service Pack 2 or higher) 3. Pre - Installation Tasks Identify prior installationBefore upgrading to new version you must have installed oracle 10.1.0.1 and database created.
5. Download 10.2.0.1 softwareDownload and extract the oracle_10201_WINNT.zip software set installation archive to a directory that is not the Oracle home directory or under the Oracle home directory.
6. Backup your database.Oracle recommends that you create a backup of the database before you start upgradation.
I.e., (datafile, redologfile)
7. Check SYSTEM Tablespace Size Ensure that there is at least 50 MB of free space allocated to the SYSTEM tablespace.
query to check tablespace size and freespace
col "Tablespace" for a22col "Used MB" for 99,999,999col "Free MB" for 99,999,999col "Total MB" for 99,999,999select df.tablespace_name "Tablespace",totalusedspace "Used MB",(df.totalspace - tu.totalusedspace) "Free MB",df.totalspace "Total MB",round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))"Pct. Free"from(select tablespace_name,round(sum(bytes) / 1048576) TotalSpacefrom dba_data_files group by tablespace_name) df,(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_namefrom dba_segments group by tablespace_name) tuwhere df.tablespace_name = tu.tablespace_name ;
Resize the system datafile if free space below 50 MB
ALTER DATABASE DATAFILE 'c:\oracle\oradata\orabase\system01.dbf' RESIZE 150M;
Check for free space from the above query again.
8. Set the SHARED_POOL_SIZE and JAVA_POOL_SIZE Initialization Parameters to minimum 150 MB.
Users who have JVM (Java enabled) or JVM and XDB installed on their 10.1.0.1 databases should make sure that the init.ora parameters SHARED_POOL_SIZE and JAVA_POOL_SIZE are each 150 MB or more before running the catpatch.sql upgrade script. Failure to do so could result in an unrecoverable memory failure while running of the script. Please note that JVM and XML DB was shipped as part of the default 10.1.0.1 seed database, and will be present unless the user explicitly installed a 10.1.0.1 instance without them. Set the value of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters as follows:
Note: If the system uses an initialization parameter file, if necessary change the values of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters to at least 150 MB in the initialization parameter file (initsid.ora).
If the database uses sp-file use the below command.
SQL> ALTER SYSTEM SET JAVA_POOL_SIZE=150M SCOPE=SPFILE; System altered.SQL> ALTER SYSTEM SET SHARED_POOL_SIZE=150M SCOPE=SPFILE; System altered.
9. Shutdown oracle database.C:\>set oracle_sid=orclC:\>sqlplus /nologSQL*plus: Release 10.1.0.1.0 - Production on Tue Nov 13 10:49:26 2007Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.SQL> connect sys@orcl as sysdbaEnter password: Connected.SQL> SHUTDOWNDatabase closed.Database dismounted.ORACLE instance shut down.
10. Stop all servicesC:\>set oracle_sid=orclC:\>net stop OracleDBConsoleORCLThe OracleDBConsoleORCLvice is stopping................The OracleDBConsoleORCLservice was stopped successfully.C:\>net stops OracleServiceORCLThe OracleServiceORCL is stopping.The OracleServiceORCLservice was stopped successfully.C:\>net stop OracleOraDB10g_Home1iSQL*Plus...The OracleOraDb10g_home1iSQL*Plus service was stopped successfully.C:\>net stop OracleOraDB10g_Home1TNSListenerThe OracleOraDb10g_home1TNSListener service is stopping.The OracleOraDb10g_home1TNSListener service was stopped successfully.
C:\>net stop msdtcThe Distributed Transaction Coordinator service is stopping.The Distributed Transaction Coordinator service was stopped successfully
OR CAN STOP ALL THE ORACLE SERVICES FROM SERVICES.MSC FROM THE RUN CONSOLE ON THE START BUTTON.
11. Install 10.2.0.1 software in new ORACLE_HOME.
On welcome screen click Next1) On Specify file location screen, select the Name field and Path where you wish to Install new software (If you have multiple database installed on your machine you will see drop down menu select new database oracle_home)
2) On summay screen, click Install
After the software is installed on the new ORACLE_HOME
12. Upgrade the Database After you install the software in new ORACLE_HOME, you must perform the following steps on every database associated with the old ORACLE_HOME
1. Copy the p-file from old ORACLE_HOME and paste it into new ORACLE_HOME and rename it to associate the new SID (ie, 10.2.0.1) and change the parameter to compatible=10.2.0.1.0 in the p-file.
2. Create and Start NEW oracle services
oradim -new -sid orcl2 -intpwd orcl2 -pfile 'E:\oracle\product\10.2.0\db_2\database\INITorcl2.ora' -startmode auto
3 set ORACLE_SID=ORCL2
4. Connect sys userC :\> sqlplus /NOLOGSQL> CONNECT SYS/SYS_password AS SYSDBA5. Enter the following SQL*Plus commands:SQL> STARTUP UPGRADE pfile=’New path where p-file is located\p-file.ora’
SQL> SPOOL upgrade.logSQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\catupgrd.sqlSQL> SPOOL OFFReview the upgrade.log file for errors and inspect the list of components that is displayed at the end of catupgrd.sql script. This list provides the version and status of each SERVER component in the database.
If necessary, rerun the catupgrd.sql script after correcting any problems. 6. Restart the database: SQL> SHUTDOWNSQL> STARTUP7. Compile Invalid ObjectsRun the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time. This step is optional but recommended. SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\utlrp.sqlSQL> select * from v$version;BANNER----------------------------------------------------------------Oracle Database 10g Release 10.2.0.1.0 - ProductionPL/SQL Release 10.2.0.1.0 - ProductionCORE 10.2.0.1.0 ProductionTNS for 32-bit Windows: Version 10.2.0.1.0 - ProductionNLSRTL Version 10.2.0.1.0 - Production
8. Clone the Database associated old ORACLE_HOME to new ORACLE_HOME if you want to remove the old ORACLE_HOME.
9. Delete the old oracle services by using the following command
oradim -delete –sid SID (associated with the previous database ie,10.1.0.1)
10. Remove the old Installation of oracle software (i.e., 10.1.0.1)
------END OF ORACLE UPGRADATION FROM 10.1.0.1 TO 10.2.0.2-----
1. Install software 10.1.0.1 in 1st ORACLE_HOME
Install oracle 10.1.0.1 before upgrading this to 10.2.0.1.
You must have 10.1.0.1 version installed along with database.2. RequirementsOracle DB: Oracle 10.1.0.1 (later) Operating System: Windows 2000 (Service Pack 2 or higher) 3. Pre - Installation Tasks Identify prior installationBefore upgrading to new version you must have installed oracle 10.1.0.1 and database created.
5. Download 10.2.0.1 softwareDownload and extract the oracle_10201_WINNT.zip software set installation archive to a directory that is not the Oracle home directory or under the Oracle home directory.
6. Backup your database.Oracle recommends that you create a backup of the database before you start upgradation.
I.e., (datafile, redologfile)
7. Check SYSTEM Tablespace Size Ensure that there is at least 50 MB of free space allocated to the SYSTEM tablespace.
query to check tablespace size and freespace
col "Tablespace" for a22col "Used MB" for 99,999,999col "Free MB" for 99,999,999col "Total MB" for 99,999,999select df.tablespace_name "Tablespace",totalusedspace "Used MB",(df.totalspace - tu.totalusedspace) "Free MB",df.totalspace "Total MB",round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))"Pct. Free"from(select tablespace_name,round(sum(bytes) / 1048576) TotalSpacefrom dba_data_files group by tablespace_name) df,(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_namefrom dba_segments group by tablespace_name) tuwhere df.tablespace_name = tu.tablespace_name ;
Resize the system datafile if free space below 50 MB
ALTER DATABASE DATAFILE 'c:\oracle\oradata\orabase\system01.dbf' RESIZE 150M;
Check for free space from the above query again.
8. Set the SHARED_POOL_SIZE and JAVA_POOL_SIZE Initialization Parameters to minimum 150 MB.
Users who have JVM (Java enabled) or JVM and XDB installed on their 10.1.0.1 databases should make sure that the init.ora parameters SHARED_POOL_SIZE and JAVA_POOL_SIZE are each 150 MB or more before running the catpatch.sql upgrade script. Failure to do so could result in an unrecoverable memory failure while running of the script. Please note that JVM and XML DB was shipped as part of the default 10.1.0.1 seed database, and will be present unless the user explicitly installed a 10.1.0.1 instance without them. Set the value of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters as follows:
Note: If the system uses an initialization parameter file, if necessary change the values of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters to at least 150 MB in the initialization parameter file (initsid.ora).
If the database uses sp-file use the below command.
SQL> ALTER SYSTEM SET JAVA_POOL_SIZE=150M SCOPE=SPFILE; System altered.SQL> ALTER SYSTEM SET SHARED_POOL_SIZE=150M SCOPE=SPFILE; System altered.
9. Shutdown oracle database.C:\>set oracle_sid=orclC:\>sqlplus /nologSQL*plus: Release 10.1.0.1.0 - Production on Tue Nov 13 10:49:26 2007Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.SQL> connect sys@orcl as sysdbaEnter password: Connected.SQL> SHUTDOWNDatabase closed.Database dismounted.ORACLE instance shut down.
10. Stop all servicesC:\>set oracle_sid=orclC:\>net stop OracleDBConsoleORCLThe OracleDBConsoleORCLvice is stopping................The OracleDBConsoleORCLservice was stopped successfully.C:\>net stops OracleServiceORCLThe OracleServiceORCL is stopping.The OracleServiceORCLservice was stopped successfully.C:\>net stop OracleOraDB10g_Home1iSQL*Plus...The OracleOraDb10g_home1iSQL*Plus service was stopped successfully.C:\>net stop OracleOraDB10g_Home1TNSListenerThe OracleOraDb10g_home1TNSListener service is stopping.The OracleOraDb10g_home1TNSListener service was stopped successfully.
C:\>net stop msdtcThe Distributed Transaction Coordinator service is stopping.The Distributed Transaction Coordinator service was stopped successfully
OR CAN STOP ALL THE ORACLE SERVICES FROM SERVICES.MSC FROM THE RUN CONSOLE ON THE START BUTTON.
11. Install 10.2.0.1 software in new ORACLE_HOME.
On welcome screen click Next1) On Specify file location screen, select the Name field and Path where you wish to Install new software (If you have multiple database installed on your machine you will see drop down menu select new database oracle_home)
2) On summay screen, click Install
After the software is installed on the new ORACLE_HOME
12. Upgrade the Database After you install the software in new ORACLE_HOME, you must perform the following steps on every database associated with the old ORACLE_HOME
1. Copy the p-file from old ORACLE_HOME and paste it into new ORACLE_HOME and rename it to associate the new SID (ie, 10.2.0.1) and change the parameter to compatible=10.2.0.1.0 in the p-file.
2. Create and Start NEW oracle services
oradim -new -sid orcl2 -intpwd orcl2 -pfile 'E:\oracle\product\10.2.0\db_2\database\INITorcl2.ora' -startmode auto
3 set ORACLE_SID=ORCL2
4. Connect sys userC :\> sqlplus /NOLOGSQL> CONNECT SYS/SYS_password AS SYSDBA5. Enter the following SQL*Plus commands:SQL> STARTUP UPGRADE pfile=’New path where p-file is located\p-file.ora’
SQL> SPOOL upgrade.logSQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\catupgrd.sqlSQL> SPOOL OFFReview the upgrade.log file for errors and inspect the list of components that is displayed at the end of catupgrd.sql script. This list provides the version and status of each SERVER component in the database.
If necessary, rerun the catupgrd.sql script after correcting any problems. 6. Restart the database: SQL> SHUTDOWNSQL> STARTUP7. Compile Invalid ObjectsRun the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time. This step is optional but recommended. SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\utlrp.sqlSQL> select * from v$version;BANNER----------------------------------------------------------------Oracle Database 10g Release 10.2.0.1.0 - ProductionPL/SQL Release 10.2.0.1.0 - ProductionCORE 10.2.0.1.0 ProductionTNS for 32-bit Windows: Version 10.2.0.1.0 - ProductionNLSRTL Version 10.2.0.1.0 - Production
8. Clone the Database associated old ORACLE_HOME to new ORACLE_HOME if you want to remove the old ORACLE_HOME.
9. Delete the old oracle services by using the following command
oradim -delete –sid SID (associated with the previous database ie,10.1.0.1)
10. Remove the old Installation of oracle software (i.e., 10.1.0.1)
------END OF ORACLE UPGRADATION FROM 10.1.0.1 TO 10.2.0.2-----
Upgrade Oracle 10.2.0.1 to 10.2.0.2
Upgrade Oracle 10.2.0.1 to 10.2.0.2
1. Patch Set OverviewPatch set release 10.2.0.2. Before installing this patch set you must be need 10.2.0.1 version.2. RequirementsOracle DB: Oracle 10.2.0.1 (later)Operating System: Windows 2000 (Service Pack 2 or higher)3. Pre - Installation Tasks4. Identify prior installationBefore installing this patch you must install oracle 10.2.0.1 (or later version)5. Download Patch setDownload and extract the p4547817_10202_WINNT.zip patch set installation archive to a directory that is not the Oracle home directory or under the Oracle home directory.
6. Backup your database.Oracle recommends that you create a backup of the installed Oracle 10g software before you install the patch set and the database.
I.e., (datafile, redologfile)
7. Check SYSTEM Tablespace Size Ensure that there is at least 50 MB of free space allocated to the SYSTEM tablespace.
query to check tablespace size and freespace
col "Tablespace" for a22col "Used MB" for 99,999,999col "Free MB" for 99,999,999col "Total MB" for 99,999,999select df.tablespace_name "Tablespace",totalusedspace "Used MB",(df.totalspace - tu.totalusedspace) "Free MB",df.totalspace "Total MB",round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))"Pct. Free"from(select tablespace_name,round(sum(bytes) / 1048576) TotalSpacefrom dba_data_files group by tablespace_name) df,(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_namefrom dba_segments group by tablespace_name) tuwhere df.tablespace_name = tu.tablespace_name ;
Resize the system datafile if free space below 50 MB
ALTER DATABASE DATAFILE 'c:\oracle\oradata\orabase\system01.dbf' RESIZE 150M;
Check for free space from the above query again.
8. Set the SHARED_POOL_SIZE and JAVA_POOL_SIZE Initialization Parameters to minimum 150 MB.
Users who have JVM (Java enabled) or JVM and XDB installed on their 10.2.0.1 databases should make sure that the init.ora parameters SHARED_POOL_SIZE and JAVA_POOL_SIZE are each 150 MB or more before running the catpatch.sql upgrade script. Failure to do so could result in an unrecoverable memory failure while running of the script. Please note that JVM and XML DB was shipped as part of the default 10.2.0.1 seed database, and will be present unless the user explicitly installed a 10.2.0.1 instance without them. Set the value of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters as follows:
Note: If the system uses an initialization parameter file, if necessary change the values of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters to at least 150 MB in the initialization parameter file (initsid.ora).
If the system uses sp-file use the below command.
SQL> ALTER SYSTEM SET JAVA_POOL_SIZE=150M SCOPE=SPFILE; System altered.SQL> ALTER SYSTEM SET SHARED_POOL_SIZE=150M SCOPE=SPFILE; System altered.
9. Shutdown oracle database.C:\>set oracle_sid=orclC:\>sqlplus /nologSQL*plus: Release 10.2.0.1.0 - Production on Tue Nov 13 10:49:26 2007Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.SQL> connect sys@orcl as sysdbaEnter password: Connected.SQL> SHUTDOWNDatabase closed.Database dismounted.ORACLE instance shut down.
10. Stop all servicesC:\>set oracle_sid=orclC:\>net stop OracleDBConsoleORCLThe OracleDBConsoleORCLvice is stopping................The OracleDBConsoleORCLservice was stopped successfully.C:\>net stops OracleServiceORCLThe OracleServiceORCL is stopping.The OracleServiceORCLservice was stopped successfully.C:\>net stop OracleOraDB10g_Home1iSQL*Plus...The OracleOraDb10g_home1iSQL*Plus service was stopped successfully.C:\>net stop OracleOraDB10g_Home1TNSListenerThe OracleOraDb10g_home1TNSListener service is stopping.The OracleOraDb10g_home1TNSListener service was stopped successfully.
Note: while patch installation time if you face Distributed Transaction Coordinator still running. You should be stop this serviceC:\>net stop msdtcThe Distributed Transaction Coordinator service is stopping.The Distributed Transaction Coordinator service was stopped successfully
OR CAN STOP ALL THE ORACLE SERVICES FROM SERVICES.MSC FROM THE RUN CONSOLE ON THE START BUTTON.
11. Apply the patch after extracting the zip file (ie, p4547817_10202_WINNT.zip ) to the ORACLE_HOME where 10.2.0.1 is installed.
On welcome screen click Next1) On Specify file location screen, in Name field and Path select Database on which you wish to apply this patch (If you have multiple database installed on your machine you will see drop down menu to select database oracle_home)
2) On summay screen, click Install
Arter the patch is installed on the ORACLE_HOME
12. Upgrade the Database After you install the patch set, you must perform the following steps on every database associated with the upgraded Oracle home:1. Start all oracle services by commands or by services.msc from run console.2. Connect sys userC :\> sqlplus /NOLOGSQL> CONNECT SYS/SYS_password AS SYSDBA3. Enter the following SQL*Plus commands:SQL> STARTUP UPGRADESQL> SPOOL patch. logSQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\catupgrd.sqlSQL> SPOOL OFFReview the patch.log file for errors and inspect the list of components that is displayed at the end of catupgrd.sql script. This list provides the version and status of each SERVER component in the database.
If necessary, rerun the catupgrd.sql script after correcting any problems. 4. Restart the database: SQL> SHUTDOWNSQL> STARTUP5. Compile Invalid ObjectsRun the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time. This step is optional but recommended. SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\utlrp.sqlSQL> select * from v$version;BANNER----------------------------------------------------------------Oracle Database 10g Release 10.2.0.3.0 - ProductionPL/SQL Release 10.2.0.3.0 - ProductionCORE 10.2.0.3.0 ProductionTNS for 32-bit Windows: Version 10.2.0.3.0 - ProductionNLSRTL Version 10.2.0.3.0 - Production
------END OF ORACLE UPGRADATION FROM 10.2.0.1 TO 10.2.0.2-----
Thanks
Harshavardhan_srinivas
Oracle Database administrator
MindTree Consulting Pvt. Ltd.
1. Patch Set OverviewPatch set release 10.2.0.2. Before installing this patch set you must be need 10.2.0.1 version.2. RequirementsOracle DB: Oracle 10.2.0.1 (later)Operating System: Windows 2000 (Service Pack 2 or higher)3. Pre - Installation Tasks4. Identify prior installationBefore installing this patch you must install oracle 10.2.0.1 (or later version)5. Download Patch setDownload and extract the p4547817_10202_WINNT.zip patch set installation archive to a directory that is not the Oracle home directory or under the Oracle home directory.
6. Backup your database.Oracle recommends that you create a backup of the installed Oracle 10g software before you install the patch set and the database.
I.e., (datafile, redologfile)
7. Check SYSTEM Tablespace Size Ensure that there is at least 50 MB of free space allocated to the SYSTEM tablespace.
query to check tablespace size and freespace
col "Tablespace" for a22col "Used MB" for 99,999,999col "Free MB" for 99,999,999col "Total MB" for 99,999,999select df.tablespace_name "Tablespace",totalusedspace "Used MB",(df.totalspace - tu.totalusedspace) "Free MB",df.totalspace "Total MB",round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))"Pct. Free"from(select tablespace_name,round(sum(bytes) / 1048576) TotalSpacefrom dba_data_files group by tablespace_name) df,(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_namefrom dba_segments group by tablespace_name) tuwhere df.tablespace_name = tu.tablespace_name ;
Resize the system datafile if free space below 50 MB
ALTER DATABASE DATAFILE 'c:\oracle\oradata\orabase\system01.dbf' RESIZE 150M;
Check for free space from the above query again.
8. Set the SHARED_POOL_SIZE and JAVA_POOL_SIZE Initialization Parameters to minimum 150 MB.
Users who have JVM (Java enabled) or JVM and XDB installed on their 10.2.0.1 databases should make sure that the init.ora parameters SHARED_POOL_SIZE and JAVA_POOL_SIZE are each 150 MB or more before running the catpatch.sql upgrade script. Failure to do so could result in an unrecoverable memory failure while running of the script. Please note that JVM and XML DB was shipped as part of the default 10.2.0.1 seed database, and will be present unless the user explicitly installed a 10.2.0.1 instance without them. Set the value of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters as follows:
Note: If the system uses an initialization parameter file, if necessary change the values of the SHARED_POOL_SIZE and the JAVA_POOL_SIZE initialization parameters to at least 150 MB in the initialization parameter file (initsid.ora).
If the system uses sp-file use the below command.
SQL> ALTER SYSTEM SET JAVA_POOL_SIZE=150M SCOPE=SPFILE; System altered.SQL> ALTER SYSTEM SET SHARED_POOL_SIZE=150M SCOPE=SPFILE; System altered.
9. Shutdown oracle database.C:\>set oracle_sid=orclC:\>sqlplus /nologSQL*plus: Release 10.2.0.1.0 - Production on Tue Nov 13 10:49:26 2007Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.SQL> connect sys@orcl as sysdbaEnter password: Connected.SQL> SHUTDOWNDatabase closed.Database dismounted.ORACLE instance shut down.
10. Stop all servicesC:\>set oracle_sid=orclC:\>net stop OracleDBConsoleORCLThe OracleDBConsoleORCLvice is stopping................The OracleDBConsoleORCLservice was stopped successfully.C:\>net stops OracleServiceORCLThe OracleServiceORCL is stopping.The OracleServiceORCLservice was stopped successfully.C:\>net stop OracleOraDB10g_Home1iSQL*Plus...The OracleOraDb10g_home1iSQL*Plus service was stopped successfully.C:\>net stop OracleOraDB10g_Home1TNSListenerThe OracleOraDb10g_home1TNSListener service is stopping.The OracleOraDb10g_home1TNSListener service was stopped successfully.
Note: while patch installation time if you face Distributed Transaction Coordinator still running. You should be stop this serviceC:\>net stop msdtcThe Distributed Transaction Coordinator service is stopping.The Distributed Transaction Coordinator service was stopped successfully
OR CAN STOP ALL THE ORACLE SERVICES FROM SERVICES.MSC FROM THE RUN CONSOLE ON THE START BUTTON.
11. Apply the patch after extracting the zip file (ie, p4547817_10202_WINNT.zip ) to the ORACLE_HOME where 10.2.0.1 is installed.
On welcome screen click Next1) On Specify file location screen, in Name field and Path select Database on which you wish to apply this patch (If you have multiple database installed on your machine you will see drop down menu to select database oracle_home)
2) On summay screen, click Install
Arter the patch is installed on the ORACLE_HOME
12. Upgrade the Database After you install the patch set, you must perform the following steps on every database associated with the upgraded Oracle home:1. Start all oracle services by commands or by services.msc from run console.2. Connect sys userC :\> sqlplus /NOLOGSQL> CONNECT SYS/SYS_password AS SYSDBA3. Enter the following SQL*Plus commands:SQL> STARTUP UPGRADESQL> SPOOL patch. logSQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\catupgrd.sqlSQL> SPOOL OFFReview the patch.log file for errors and inspect the list of components that is displayed at the end of catupgrd.sql script. This list provides the version and status of each SERVER component in the database.
If necessary, rerun the catupgrd.sql script after correcting any problems. 4. Restart the database: SQL> SHUTDOWNSQL> STARTUP5. Compile Invalid ObjectsRun the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time. This step is optional but recommended. SQL> @ORACLE_BASE\ORACLE_HOME\rdbms\admin\utlrp.sqlSQL> select * from v$version;BANNER----------------------------------------------------------------Oracle Database 10g Release 10.2.0.3.0 - ProductionPL/SQL Release 10.2.0.3.0 - ProductionCORE 10.2.0.3.0 ProductionTNS for 32-bit Windows: Version 10.2.0.3.0 - ProductionNLSRTL Version 10.2.0.3.0 - Production
------END OF ORACLE UPGRADATION FROM 10.2.0.1 TO 10.2.0.2-----
Thanks
Harshavardhan_srinivas
Oracle Database administrator
MindTree Consulting Pvt. Ltd.
Sql statement tracing using tkprof utility
How to get queries, which is executed by particular user:-
1.Change the timed_statistics parameter to TRUE:-
SQL> alter system set TIMED_STATISTICS=TRUE;
2.Turn tracing on user for session level:-
SQL> alter session set SQL_TRACE=TRUE;
Or
3.As a DBA, execute the below command to enable sql trace for particular user:-
Get the sid and serial# from the V$session view.
eg:-
select sid,serial#,username from v$session
where machine='A4MD08060';
select sid,serial#,username from v$session
where username='NAVPROD';
select sid,serial#,username from v$session
where username not in ('SYS');
Enabling trace:
SQL> exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,true);
eg:-
exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(93, 24638, true);
exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(81,64882, true);
exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(96,20312,true);
Disabling trace:
SQL> exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,false);
eg:-
exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(93, 24638, false);
exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(81,64882, false);
exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(96,20312,false);
3.Then, see the user dump destination for trace file.
sqlplus sys as sysdba
sho parameter dump;
4.Execute the below command to analyze and to create insert script for generating sql statements from trace file:-
$ tkprof insert=tkprof_table.sql sys=no
eg:-
tkprof orap_ora_12523.trc orap_ora_12523.txt insert= orap_ora_12523.sql sys=no
tkprof orap_ora_19752.trc orap_ora_19752.txt insert= orap_ora_19752.sql sys=no
It will create in text file and script for to create tkprof_table.
5.Execute the tkprof_table.sql file in any user schema.
6.It will create tkprof_table, with contents.
7.find out the user_id for that particular user from dba_users
eg:-select user_id from dba_users
where username='NAVPROD';
8.Execute the below query to get the queries of particular user.
eg:-
SQL>set long 9999
set heading off
set pages 0
spool sql_statements.txt
select sql_statement from tkprof_table where user_id =;
Note:-
if you want it in .cvs or .xls use dbvisualiser or other tools and if you dont have those tools you can use command line to do the same.
For more info on command line check the posting "Oracle tables to Excel".
1.Change the timed_statistics parameter to TRUE:-
SQL> alter system set TIMED_STATISTICS=TRUE;
2.Turn tracing on user for session level:-
SQL> alter session set SQL_TRACE=TRUE;
Or
3.As a DBA, execute the below command to enable sql trace for particular user:-
Get the sid and serial# from the V$session view.
eg:-
select sid,serial#,username from v$session
where machine='A4MD08060';
select sid,serial#,username from v$session
where username='NAVPROD';
select sid,serial#,username from v$session
where username not in ('SYS');
Enabling trace:
SQL> exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,true);
eg:-
exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(93, 24638, true);
exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(81,64882, true);
exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(96,20312,true);
Disabling trace:
SQL> exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid,serial#,false);
eg:-
exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(93, 24638, false);
exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(81,64882, false);
exec DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(96,20312,false);
3.Then, see the user dump destination for trace file.
sqlplus sys as sysdba
sho parameter dump;
4.Execute the below command to analyze and to create insert script for generating sql statements from trace file:-
$ tkprof
eg:-
tkprof orap_ora_12523.trc orap_ora_12523.txt insert= orap_ora_12523.sql sys=no
tkprof orap_ora_19752.trc orap_ora_19752.txt insert= orap_ora_19752.sql sys=no
It will create in text file and script for to create tkprof_table.
5.Execute the tkprof_table.sql file in any user schema.
6.It will create tkprof_table, with contents.
7.find out the user_id for that particular user from dba_users
eg:-select user_id from dba_users
where username='NAVPROD';
8.Execute the below query to get the queries of particular user.
eg:-
SQL>set long 9999
set heading off
set pages 0
spool sql_statements.txt
select sql_statement from tkprof_table where user_id =
Note:-
if you want it in .cvs or .xls use dbvisualiser or other tools and if you dont have those tools you can use command line to do the same.
For more info on command line check the posting "Oracle tables to Excel".
Cloning Database From Hot Backup
Cloning Database From Hot Backup
To clone a db on another machine from hot backup, follow these steps:
1. Install Oracle server on new machine
2. Create directories, init p-file, data files, redo log files, trace of control file, archive from the original database on to second server where you need to clone
3. Add database name to tnsnames.ora, listener.ora on second server
4. Create database service with ORADIM (if OS is Windows)
Eg: oradim -new -sid-intpwd -pfile -startmode auto
5. On original db:
ALTER DATABASE BACKUP CONTROL FILE TO TRACE RESETLOGS;
6. Rename trace file to create_control.sql, edit the contents as follows:
STARTUP NOMOUNT CREATE CONTROLFILE SET DATABASE "" RESETLOGS ARCHIVELOG ... ;
7. On original db Then do:
ALTER SYSTEM ARCHIVE LOG CURRENT;
8. Copy the ORADATA directory including archived logs to second server
9. Go to second server, set SID, and use sqlplus to connect as SYSDBA
10. Delete the control files already copied over using OS commands
11.set the SID
If oracle: set $ORACLE_SID=
If windows: set ORACLE_SID=
12. Run the CREATE CONTROL file script shown above
create_control.sql
13. Issue: RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
---------------------------------------------------------------------
select ' alter tablespace ' tablespace_name ' begin backup ;' from dba_tablespaces;
select ' alter tablespace ' tablespace_name ' end backup ;' from dba_tablespaces;
To clone a db on another machine from hot backup, follow these steps:
1. Install Oracle server on new machine
2. Create directories, init p-file, data files, redo log files, trace of control file, archive from the original database on to second server where you need to clone
3. Add database name to tnsnames.ora, listener.ora on second server
4. Create database service with ORADIM (if OS is Windows)
Eg: oradim -new -sid
5. On original db:
ALTER DATABASE BACKUP CONTROL FILE TO TRACE RESETLOGS;
6. Rename trace file to create_control.sql, edit the contents as follows:
STARTUP NOMOUNT CREATE CONTROLFILE SET DATABASE "
7. On original db Then do:
ALTER SYSTEM ARCHIVE LOG CURRENT;
8. Copy the ORADATA directory including archived logs to second server
9. Go to second server, set SID, and use sqlplus to connect as SYSDBA
10. Delete the control files already copied over using OS commands
11.set the SID
If oracle: set $ORACLE_SID=
If windows: set ORACLE_SID=
12. Run the CREATE CONTROL file script shown above
create_control.sql
13. Issue: RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
---------------------------------------------------------------------
select ' alter tablespace ' tablespace_name ' begin backup ;' from dba_tablespaces;
select ' alter tablespace ' tablespace_name ' end backup ;' from dba_tablespaces;
Using Network_Link in Datapump
Using Network_Link in Datapump
===============================
Oracle's export and import utilities have historically used a disk file as intermediate storage when unloading or reloading the database. For large databases, this "dump file" was an issue because operating system limits on file size could be exceeded, making export impossible.
Creative DBAs have used file compression utilities, such as compress on UNIX, to get the most capacity from the dump file. Later versions of import and export allowed the use of multiple dump files to get around the limits.
In Oracle 10g, the Data Pump version of import can eliminate the dump file entirely by importing directly from another database instance.
Example:
--------
1. Create tnsnames for remote database from where the database has to be imported.[Target]
conn_151 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.200.151)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = wisedba.com)
)
)
2. Create a directory for user to access during Datapump job[Target]
SYS> create directory defdir as '/home/ez10g/wisedba';
SYS> grant read, write on directory defdir to scott;
3. Grant user to create database link[Target}
SYS> grant create database link to scott;
4. Create database link as Scott user (to the remote database)[Target]
SCOTT> Create database link conn_151
2 connect to scott identified by tiger
3 using 'conn_151';
5. Checking tables in scott user (in local database)[Target]
SCOTT> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
6. Import 'EMP' table from remote database (without creating any dump file)[Target]
$ impdp scott/tiger tables=emp directory=defdir network_link=conn_151
Import: Release 10.2.0.1.0 - Production on Monday, 02 April, 2007 18:04:53
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** tables=emp directory=defdir network_link=conn_151
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "SCOTT"."EMP" 114688 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 18:05:12
7. Check whether the table has been imported to local database
SCOTT> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
EMP TABLE <<-- Table has been imported
BONUS TABLE
-----------------------------------FULL LEVEL IMPORT---------------------------------------
Example:
--------
1. Create tnsnames for remote database from where the database has to be imported.[Target]
qadb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.200.151)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = qadb)
)
)
2. Create a directory for user to access during Datapump job[Target]
SYS> create directory data_pump_dir as '/home/ez10g/wisedba';
SYS> grant read, write on directory defdir to system;
3. Grant user to create database link[Target}
SYS> grant create database link to system;
4. Create database link as Scott user (to the remote database)[Target]
SCOTT> Create database link qadb
2 connect to system identified by qadb
3 using 'qadb';
5. Checking users in the DB (in local database)
SYS> select * from all_users;
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
CTXSYS 36 03-JUL-08
EXFSYS 34 03-JUL-08
WMSYS 25 03-JUL-08
DBSNMP 24 03-JUL-08
TSMSYS 21 03-JUL-08
DMSYS 35 03-JUL-08
DIP 19 03-JUL-08
OUTLN 11 03-JUL-08
SYSTEM 5 03-JUL-08
SYS 0 03-JUL-08
6. Import 'EMP' table from remote database (without creating any dump file)[Target]
$ impdp system/esbgui2008 directory=data_pump_dir network_link=qadb full=y
NOTE: The tablespace should be existing as same in the target DB (local db from where u need to import).
-----------------SCHEMA LEVEL-----------------------
1. Create tnsnames for remote database from where the database has to be imported.[Target]
qadb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.200.151)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = qadb)
)
)
2. Create a directory for user to access during Datapump job[Target]
SYS> create directory data_pump_dir as '/home/ez10g/wisedba';
SYS> grant read, write on directory defdir to scott;
3. Grant user to create database link [Target}
SYS> grant create database link to scott;
4. Create database link as Scott user (to the remote database)[Target]
SCOTT> Create database link qadb
2 connect to scott identified by tiger
3 using 'qadb';
5. Checking tables in scott schema(in local database)
SCOTT> select * from tab;
6. Import all table from remote database (without creating any dump file)[Target]
$ impdp scott/tiger directory=data_pump_dir network_link=qadb remap_schema=scott:scott
View
Purpose
DBA_DB_LINKS
Lists all database links in the database.
ALL_DB_LINKS
Lists all database links accessible to the connected user.
USER_DB_LINKS
Lists all database links owned by the connected user
===============================
Oracle's export and import utilities have historically used a disk file as intermediate storage when unloading or reloading the database. For large databases, this "dump file" was an issue because operating system limits on file size could be exceeded, making export impossible.
Creative DBAs have used file compression utilities, such as compress on UNIX, to get the most capacity from the dump file. Later versions of import and export allowed the use of multiple dump files to get around the limits.
In Oracle 10g, the Data Pump version of import can eliminate the dump file entirely by importing directly from another database instance.
Example:
--------
1. Create tnsnames for remote database from where the database has to be imported.[Target]
conn_151 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.200.151)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = wisedba.com)
)
)
2. Create a directory for user to access during Datapump job[Target]
SYS> create directory defdir as '/home/ez10g/wisedba';
SYS> grant read, write on directory defdir to scott;
3. Grant user to create database link[Target}
SYS> grant create database link to scott;
4. Create database link as Scott user (to the remote database)[Target]
SCOTT> Create database link conn_151
2 connect to scott identified by tiger
3 using 'conn_151';
5. Checking tables in scott user (in local database)[Target]
SCOTT> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
6. Import 'EMP' table from remote database (without creating any dump file)[Target]
$ impdp scott/tiger tables=emp directory=defdir network_link=conn_151
Import: Release 10.2.0.1.0 - Production on Monday, 02 April, 2007 18:04:53
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** tables=emp directory=defdir network_link=conn_151
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "SCOTT"."EMP" 114688 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 18:05:12
7. Check whether the table has been imported to local database
SCOTT> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
EMP TABLE <<-- Table has been imported
BONUS TABLE
-----------------------------------FULL LEVEL IMPORT---------------------------------------
Example:
--------
1. Create tnsnames for remote database from where the database has to be imported.[Target]
qadb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.200.151)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = qadb)
)
)
2. Create a directory for user to access during Datapump job[Target]
SYS> create directory data_pump_dir as '/home/ez10g/wisedba';
SYS> grant read, write on directory defdir to system;
3. Grant user to create database link[Target}
SYS> grant create database link to system;
4. Create database link as Scott user (to the remote database)[Target]
SCOTT> Create database link qadb
2 connect to system identified by qadb
3 using 'qadb';
5. Checking users in the DB (in local database)
SYS> select * from all_users;
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
CTXSYS 36 03-JUL-08
EXFSYS 34 03-JUL-08
WMSYS 25 03-JUL-08
DBSNMP 24 03-JUL-08
TSMSYS 21 03-JUL-08
DMSYS 35 03-JUL-08
DIP 19 03-JUL-08
OUTLN 11 03-JUL-08
SYSTEM 5 03-JUL-08
SYS 0 03-JUL-08
6. Import 'EMP' table from remote database (without creating any dump file)[Target]
$ impdp system/esbgui2008 directory=data_pump_dir network_link=qadb full=y
NOTE: The tablespace should be existing as same in the target DB (local db from where u need to import).
-----------------SCHEMA LEVEL-----------------------
1. Create tnsnames for remote database from where the database has to be imported.[Target]
qadb =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.200.151)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = qadb)
)
)
2. Create a directory for user to access during Datapump job[Target]
SYS> create directory data_pump_dir as '/home/ez10g/wisedba';
SYS> grant read, write on directory defdir to scott;
3. Grant user to create database link [Target}
SYS> grant create database link to scott;
4. Create database link as Scott user (to the remote database)[Target]
SCOTT> Create database link qadb
2 connect to scott identified by tiger
3 using 'qadb';
5. Checking tables in scott schema(in local database)
SCOTT> select * from tab;
6. Import all table from remote database (without creating any dump file)[Target]
$ impdp scott/tiger directory=data_pump_dir network_link=qadb remap_schema=scott:scott
View
Purpose
DBA_DB_LINKS
Lists all database links in the database.
ALL_DB_LINKS
Lists all database links accessible to the connected user.
USER_DB_LINKS
Lists all database links owned by the connected user
Rman cloning
Introduction
Duplicate Database/Cloning Database using RMAN
You can use the RMAN DUPLICATE command create a duplicate database from target database backups while still retaining the original target database. A duplicate database is a copy of the target database that you can run independently for a variety of purposes. For example, you can use it to:
Test backup and recovery procedures
Export data such as a table that was inadvertently dropped from the production database, and then importing it back into the production database.
A duplicate database is distinct from a standby database, although both types of databases are created with the DUPLICATE command. A standby database is a copy of the primary database that you can update continually or periodically by using archived logs from the primary database. If the primary database is damaged or destroyed, then you can perform failover to the standby database and effectively transform it into the new primary database. A duplicate database, on the other hand, cannot be used in this way: it is not intended for failover scenarios and does not support the various standby recovery and failover options.
Note: Configuration and Backup must be understood prior to starting duplication using RMAN.
Steps for duplicating database using RMAN.
01. From the command prompt create a password file for the AUX database.
orapwd file=d:\oracle\ora92\database\PWDaux.ora password=oracle entries=5
2. In SQL*Plus connect as SYS create a parameter file from the spfile of the target database for the AUX database.
connect sys/oracle@orcl.world as sysdba
create pfile='d:\oracle\ora92\database\INITaux.ORA' from spfile;
3. Edit the file INITaux.ORA, found in the D:\ORALCE\ORA92\DATABASE;
change all occurrences of \orcl\ to \auxorcl\.
Next change db_name and instance_name to aux. Then add the following two lines to the parameter file:
db_file_name_convert=('D:\ORACLE\ORADATA\ORCL','D:\ORACLE\ORADATA\AUXORCL')
log_file_name_convert=('D:\ORACLE\ORADATA\ORCL','D:\ORACLE\ORADATA\AUXORCL'
4. Run script cre_dir from the command prompt to create the subdirectories for the duplicate database.
cre_dir
or create directories (bdump,cdump,udump,archive,adump) manually as path specified in the pfile.
5. Add an entry in the tnsnames.ora and listener.ora for the AUX database. Use Net Manager.
Start > Programs > Oracle - OraHome92 > Configuration and Migration Tools > Net Manager
TNSNAMES.ORA
Select Oracle Net Configuration > Local > Service Name. Then from the menu select Edit > Create. Use the following values to create the entry.
Net Service Name: aux.world
Protocol: TCP/IP (Internet Protocol)
Host:
Port: 1521
Service Name: aux.world
Since the database has not been created you can not successfully test the configuration.
LISTENER.ORA
Select Oracle Net Configuration > Local > Listeners > LISTENER. From the drop down menu on the right and side change Listener Location to Database Services. Click Add Database at the bottom. Use the following values:
Global Database Name: aux.net
Oracle Home: D:\oracle\ora92
SID: aux
Select from the top menu: File > Save Network Configuration followed by File > Exit.
6. Shutdown and startup the listener.ora. From the command prompt type the follow:
lsnrctl stop
lsnrctl start
7. Create a service for the database. From the command prompt type:
oradim -new -sid AUX -startmode m –pfile d:\oracle\ora92\database\INITaux.ORA
8. From RMAN, type the following commands.
RMAN
CONNECT AUXILIARY SYS/ORACLE@AUX.WORLD
CONNECT TARGET SYS/ORACLE@ORCL.WORLD
STARTUP CLONE NOMOUNT FORCE;
DUPLICATE TARGET DATABASE TO AUX;
9.Connect to the new database
connect hr/hr@aux.world
SELECT first_name
FROM employees;
Duplicate Database/Cloning Database using RMAN
You can use the RMAN DUPLICATE command create a duplicate database from target database backups while still retaining the original target database. A duplicate database is a copy of the target database that you can run independently for a variety of purposes. For example, you can use it to:
Test backup and recovery procedures
Export data such as a table that was inadvertently dropped from the production database, and then importing it back into the production database.
A duplicate database is distinct from a standby database, although both types of databases are created with the DUPLICATE command. A standby database is a copy of the primary database that you can update continually or periodically by using archived logs from the primary database. If the primary database is damaged or destroyed, then you can perform failover to the standby database and effectively transform it into the new primary database. A duplicate database, on the other hand, cannot be used in this way: it is not intended for failover scenarios and does not support the various standby recovery and failover options.
Note: Configuration and Backup must be understood prior to starting duplication using RMAN.
Steps for duplicating database using RMAN.
01. From the command prompt create a password file for the AUX database.
orapwd file=d:\oracle\ora92\database\PWDaux.ora password=oracle entries=5
2. In SQL*Plus connect as SYS create a parameter file from the spfile of the target database for the AUX database.
connect sys/oracle@orcl.world as sysdba
create pfile='d:\oracle\ora92\database\INITaux.ORA' from spfile;
3. Edit the file INITaux.ORA, found in the D:\ORALCE\ORA92\DATABASE;
change all occurrences of \orcl\ to \auxorcl\.
Next change db_name and instance_name to aux. Then add the following two lines to the parameter file:
db_file_name_convert=('D:\ORACLE\ORADATA\ORCL','D:\ORACLE\ORADATA\AUXORCL')
log_file_name_convert=('D:\ORACLE\ORADATA\ORCL','D:\ORACLE\ORADATA\AUXORCL'
4. Run script cre_dir from the command prompt to create the subdirectories for the duplicate database.
cre_dir
or create directories (bdump,cdump,udump,archive,adump) manually as path specified in the pfile.
5. Add an entry in the tnsnames.ora and listener.ora for the AUX database. Use Net Manager.
Start > Programs > Oracle - OraHome92 > Configuration and Migration Tools > Net Manager
TNSNAMES.ORA
Select Oracle Net Configuration > Local > Service Name. Then from the menu select Edit > Create. Use the following values to create the entry.
Net Service Name: aux.world
Protocol: TCP/IP (Internet Protocol)
Host:
Port: 1521
Service Name: aux.world
Since the database has not been created you can not successfully test the configuration.
LISTENER.ORA
Select Oracle Net Configuration > Local > Listeners > LISTENER. From the drop down menu on the right and side change Listener Location to Database Services. Click Add Database at the bottom. Use the following values:
Global Database Name: aux.net
Oracle Home: D:\oracle\ora92
SID: aux
Select from the top menu: File > Save Network Configuration followed by File > Exit.
6. Shutdown and startup the listener.ora. From the command prompt type the follow:
lsnrctl stop
lsnrctl start
7. Create a service for the database. From the command prompt type:
oradim -new -sid AUX -startmode m –pfile d:\oracle\ora92\database\INITaux.ORA
8. From RMAN, type the following commands.
RMAN
CONNECT AUXILIARY SYS/ORACLE@AUX.WORLD
CONNECT TARGET SYS/ORACLE@ORCL.WORLD
STARTUP CLONE NOMOUNT FORCE;
DUPLICATE TARGET DATABASE TO AUX;
9.Connect to the new database
connect hr/hr@aux.world
SELECT first_name
FROM employees;
Subscribe to:
Posts (Atom)