ORACLE RELEASE INFORMATION
GET INFORMATION ABOUT ORACLE RELEASE:-
9.0.1.1.2
9-Version number
0-New features release number
1-Maintenance release number
1-Generic patch set number
2-Platform specific patch set number
Checking Your Current Release Number
SELECT * FROM PRODUCT_COMPONENT_VERSION;
PRODUCT VERSION STATUS
--------------------------- --------------------------- ---------------------
NLSRTL 9.0.1.0.0 Production
Oracle9i Enterprise Edition 9.0.1.0.0 Production
PL/SQL 9.0.1.0.0 Production
TNS for Solaris: 9.0.1.0.0 Production
Optionally, you can query the V$VERSION view to see component-level
information.
Database Administrator Security and Privileges
Database Administrator Usernames
Two user accounts are automatically created with the database and granted the DBA role. These two user accounts are:
n SYS (initial password: CHANGE_ON_INSTALL)
n SYSTEM (initial password: MANAGER)
The DBA Role
A predefined role, named DBA, is automatically created with every Oracle database. This role contains most database system privileges. Therefore, it is very powerful and should be granted only to fully functional database administrators.
SYS
When any database is created, the user SYS is automatically created and granted the DBA role. All of the base tables and views for the database’s data dictionary are stored in the schema SYS. These base tables and views are critical for the operation of Oracle. To maintain the integrity of the data dictionary, tables in the SYS schema are manipulated only by Oracle. They should never be modified by any user or
database administrator, and no one should create any tables in the schema of user SYS. (However, you can change the storage parameters of the data dictionary settings if necessary.) Ensure that most database users are never able to connect using the SYS account.
SYSTEM
When a database is created, the user SYSTEM is also automatically created and granted the DBA role. The SYSTEM username is used to create additional tables and views that display administrative information, and internal tables and views used by various Oracle options and tools. Never create in the SYSTEM schema tables of interest to individual users.
Administrative Privileges
Administrative privileges that are required for an administrator to perform basic database operations are granted through two special system privileges, SYSDBA and SYSOPER. You must have one of these privileges granted to you, depending upon the level of authorization you require.
SYSDBA and SYSOPER
The following are the operations that are authorized by the SYSDBA and SYSOPER system privileges:
Note: The SYSDBA and SYSOPER system privileges allow access to a database instance even when the database is not open. Control
of these privileges is totally outside of the database itself.
System Privilege Operations Authorized
SYSDBA :-
n Perform STARTUP and SHUTDOWN operations
n ALTER DATABASE: open, mount, back up, or change character set
n CREATE DATABASE
n CREATE SPFILE
n ARCHIVELOG and RECOVERY
n Includes the RESTRICTED SESSION privilege
Effectively, this system privilege allows a user to connect as user SYS.
SYSOPER :-
n Perform STARTUP and SHUTDOWN operations
n CREATE SPFILE
n ALTER DATABASE OPEN/MOUNT/BACKUP
n ARCHIVELOG and RECOVERY
n Includes the RESTRICTED SESSION privilege
This privilege allows a user to perform basic operational tasks, but without the ability to look at user data.
Connecting with Administrative Privileges: Example
This example illustrates that a user is assigned another (SYS) schema when connecting with the SYSDBA system privilege.
Assume that user scott has issued the following statements:
CONNECT scott/tiger
CREATE TABLE scott_test(name VARCHAR2(20));
Later, scott issues these statements:
CONNECT scott/tiger AS SYSDBA
SELECT * FROM scott_test;
He now receives an error that scott_test does not exist. That is because scott now references the SYS schema by default. The table was created in the scott schema.
Using Password File Authentication
This section describes how to authenticate an administrative user using password file authentication.
Preparing to Use Password File Authentication
To enable authentication of an administrative user using password file authentication you must do the following:
1. Create an operating system account for the user.
2. If not already created, Create the password file using the ORAPWD utility:
ORAPWD FILE= filename PASSWORD= password ENTRIES= max_users
example:-orapwd file=$ORACLE_HOME/dbs/orapwPWD password=gorf entries=7.
location:-
UNIX $ORACLE_HOME/dbs
WINDOWS NT c:\oracle\ora81\Database
3. Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE.
4. Connect to the database as user SYS (or as another user with the administrative privilege).
5. If the user does not already exist in the database, create the user. Grant the SYSDBA or SYSOPER system privilege to the user:
GRANT SYSDBA to scott;
This statement adds the user to the password file, thereby enabling connection AS SYSDBA.
Connecting Using Password File Authentication
Users can be authenticated and connect to a local or remote database by using the SQL*Plus CONNECT command. They must connect using their username and password and with the AS SYSDBA or AS SYSOPER clause. For example, user scott has been granted the SYSDBA privilege, so he can connect as follows:
CONNECT scott/tiger AS SYSDBA
Setting REMOTE_LOGIN_ PASSWORDFILE
In addition to creating the password file, you must also set the initialization parameter REMOTE_LOGIN_PASSWORDFILE to the appropriate value. The values recognized are described as follows:
Value Description
NONE=Setting this parameter to NONE causes Oracle to behave as if the password file does not exist. That is, no privileged connections are allowed over non-secure connections. NONE is the default value for this parameter.
EXCLUSIVE=An EXCLUSIVE password file can be used with only one database. Only an EXCLUSIVE file can contain the names of users other than SYS. Using an EXCLUSIVE password file allows you to grant SYSDBA and SYSOPER system privileges to individual users and have them connect as themselves.
SHARED=A SHARED password file can be used by multiple databases.However, the only user recognized by a SHARED password file is SYS. You cannot add users to a SHARED password file. All users needing SYSDBA or SYSOPER system privileges must connect using the same name, SYS, and password. This option is useful if you have a single DBA administering multiple databases.
To Create a Password File and Add New Users to It
1. Follow the instructions for creating a password file as explained in "Using ORAPWD" on page 1-19.
2. Set the REMOTE_LOGIN_PASSWORDFILE initialization parameter to EXCLUSIVE.
3. Connect with SYSDBA privileges as shown in the following example:
CONNECT SYS/ password AS SYSDBA
4. Start up the instance and create the database if necessary, or mount and open an existing database.
5. Create users as necessary. Grant SYSDBA or SYSOPER privileges to yourself and other users as appropriate. See "Granting and Revoking SYSDBA and SYSOPER Privileges".
Viewing Password File Members
Use the V$PWFILE_USERS view to see the users who have been granted SYSDBA and/or SYSOPER system privileges for a database. The columns displayed by this view are as follows:
Removing a Password File
If you determine that you no longer require a password file to authenticate users, you can delete the password file and reset the REMOTE_LOGIN_PASSWORDFILE initialization parameter to NONE. After you remove this file, only those users who can be authenticated by the operating system can perform database administration operations.
STARTING UP AND STOPPING ORACLE
Starting Up Database with different options and their significance
1.STARTUP NOMOUNT(in the time of database creation, Processes will start but no file open)
2.STARTUP MOUNT(in the time of database maintainance,Processes will start and control file open)
3.STARTUP (Mounting and opening database)
Optional
1.STARTUP RESTRICT(Users with create session privilage can connect to open database)
2.STARTUP FORCE(The database will shutdown abort first and then open)
3.STARTUP OPEN RECOVER
STARTING UP DATABASE
C:\>sqlplus
SQL*Plus: Release 9.0.1.0.1 - Production on Fri Dec 2 20:45:29 2005
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Enter user-name: sys/sys as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 118255568 bytes
Fixed Size 282576 bytes
Variable Size 83886080 bytes
Database Buffers 33554432 bytes
Redo Buffers 532480 bytes
Database mounted.
Database opened.
SQL>exit
STARTING UP DATABASE (OTHER OPTION)
C:\>sqlplus /nolog
SQL*Plus: Release 9.0.1.0.1 - Production on Fri Dec 2 20:47:38 2005
(c) Copyright 2001 Oracle Corporation. All rights reserved.
SQL> connect / as sysdba
Connected.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 118255568 bytes
Fixed Size 282576 bytes
Variable Size 83886080 bytes
Database Buffers 33554432 bytes
Redo Buffers 532480 bytes
Database mounted.
Database opened.
SQL>
SHUTING DOWN DATABASE(NORMAL)
C:\>sqlplus /nolog
SQL*Plus: Release 9.0.1.0.1 - Production on Fri Dec 2 20:50:27 2005
(c) Copyright 2001 Oracle Corporation. All rights reserved.
SQL> connect / as sysdba
Connected.
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SHUTING DOWN DATABASE(IMMEDIATE)
C:\>sqlplus /nolog
SQL*Plus: Release 9.0.1.0.1 - Production on Fri Dec 2 20:52:46 2005
(c) Copyright 2001 Oracle Corporation. All rights reserved.
SQL> connect / as sysdba
Connected to an idle instance.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SHUTING DOWN DATABASE(ABORT)
C:\>sqlplus /nolog
SQL*Plus: Release 9.0.1.0.1 - Production on Fri Dec 2 20:55:06 2005
(c) Copyright 2001 Oracle Corporation. All rights reserved.
SQL> connect / as sysdba
Connected to an idle instance.
SQL> shutdown abort
ORACLE instance shut down.
STARTING UP DATABASE WITH PFILE
C:\>sqlplus /nolog
SQL*Plus: Release 9.0.1.0.1 - Production on Fri Dec 2 20:55:06 2005
(c) Copyright 2001 Oracle Corporation. All rights reserved.
SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup pfile='c:\oracle\admin\frame\pfile\init.ora';
ORACLE instance started.
Total System Global Area 118255568 bytes
Fixed Size 282576 bytes
Variable Size 83886080 bytes
Database Buffers 33554432 bytes
Redo Buffers 532480 bytes
Database mounted.
STARTING UP DATABASE WITH SPFILE
C:\>sqlplus /nolog
SQL*Plus: Release 9.0.1.0.1 - Production on Fri Dec 2 20:55:06 2005
(c) Copyright 2001 Oracle Corporation. All rights reserved.
SQL> connect / as sysdba
Connected to an idle instance.
SQL>create spfile from pfile='c:\oracle\admin\frame\pfile\init.ora';
SQL>startup
QUERY ABOUT V$PARAMETER
SQL> select NAME, ISDEFAULT, ISSES_MODIFIABLE, ISSYS_MODIFIABLE from v$parameter where name like 'log_archive%';
ISSES_MODIFABLE:-If the value is TRUE then it means that it can be changed by ALTER SESSION SET command.So it will be valid for that session only.
ISSES_MODIFABLE:-If the value is FALSE then it can not be modified by ALTER SYSTEM command.If value is IMMEDIATE then the change will take affect in that session.But if it is DEFERED ,then changes will take effect after new session
SQL>alter system set max_dump_file_size=2000 scope=spfile;
The two other options for scope clause are MEMORY(for the life of the current instance only) and BOTH(for the current instance and across shutdown and restart).The default is BOTH.
MANAGING SESSIONS
SQL>select username,sid,serial#,status from v$session ;
Killing a session
SQL>alter system kill session '&sid,&serial#' (option:=post_transaction);
CREATING DATABASE AND MANAGING ORACLE
The dictonery creation scripts all begin with catalog.sql will create all data dictonery views.
First create pfile in mbb directory.
STARTING UP WITH NOMOUNT OPTION
SQL>STARTUP NOMOUNT PFILE='c:\oracle\admin\mbb\pfile\init.ora';
THE CRATE DATABASE COMMAND
CREATE DATABASE "mbb"
CONTROLFILE REUSE
LOGFILE GROUP 1
('c:/oracle/oradata/mbb/redo01.log')
SIZE 5M REUSE,
GROUP 2
('c:/oracle/oradata/mbb/redo02.log') SIZE 5M REUSE
MAXLOGFILES 4
MAXLOGMEMBERS 2
MAXLOGHISTORY 0
MAXDATAFILES 254
MAXINSTANCES 1
NOARCHIVELOG
CHARACTER SET "WE8MSWIN1252"
NATIONAL CHARACTER SET "AL16UTF16"
DATAFILE 'c:/oracle/oradata/mbb/system01.dbf' SIZE 80m
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
UNDO TABLESPACE UNDOTBS
DATAFILE 'c:/oracle/oradata/mbb/undo01.dbf' SIZE 35m
DEFAULT TEMPORARY TABLESPACE TEMP
TEMPFILE 'c:/oracle/oradata/mbb/temp01.dbf' SIZE 20M;
CREATING DATA DICTONERY VIEWS
Run catalog.sql and catproc.sql,stored in $ORACLE_HOME/rdbms/admin
ADMINISISTERING STORED PROCEDURE AND PACKAGES
SQL>ALTER PROCEDURE
SQL>ALTER PACKAGE
MANAGING CONTROLFILES
What Is a Control File?
Every Oracle database has a control file. A control file is a small binary file that records the physical structure of the database and includes:
n The database name
n Names and locations of associated datafiles and online redo log files
n The timestamp of the database creation
n The current log sequence number
n Checkpoint information
The control file must be available for writing by the Oracle database server whenever the database is open. Without the control file, the database cannot be mounted and recovery is difficult. The control file of an Oracle database is created at the same time as the database. By
default, at least one copy of the control file is created during database creation. On some operating systems the default is to create multiple copies. You should create two or more copies of the control file during database creation. You might also need to create control files later, if you lose control files or want to change particular settings in the control files.
MULTIPLEXING CONTROL FILES USING INIT.ORA
1.shutdown the database.
2.copy the control file to move locations by using operating system command.
3.change the initialization parameter file to include new control files name in the CONTROL_FILES.
control_files=("d:\oracle\oradata\frame\CONTROL01.CTL", "d:\oracle\oradata\frame\CONTROL02.CTL", "d:\oracle\oradata\frame\CONTROL03.CTL","e:\backup\controlfile\CONTROL01.CTL")
4.startup database.
MULTIPLEXING CONTROL FILES USING SPFILES
1.alter system set control_files='d:\oracle\oradata\frame\CONTROL01.CTL','d:\oracle\oradata\frame\CONTROL02.CTL','d:\oracle\oradata\frame\CONTROL03.CTL','e:\backup\controlfile\CONTROL01.CTL' scope=spfile.
2.shutdown database.
3.copy the existing control file to new location.
4.start up instance
CREATING NEW CONTROL FILES
You can create new control file by using create controlfile command.you need to create it when
1.if you loose all the control files.
2.if you want to change any of the MAX clauses.
3.if you want to change database name.
You have to know data file names and redo log files name to create control file.
command:-
1.start the database in NOMOUNT mode.
SQL>STARTUP NOMOUNT
2.SQL>CREATE CONTROLFILE REUSE DATABASE "FRAME" NORESETLOGS ARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 'D:\ORACLE\ORADATA\FRAME\REDO01.LOG' SIZE 100M,
GROUP 2 'D:\ORACLE\ORADATA\FRAME\REDO02.LOG' SIZE 100M,
GROUP 3 'D:\ORACLE\ORADATA\FRAME\REDO03.LOG' SIZE 100M
DATAFILE
'D:\ORACLE\ORADATA\FRAME\SYSTEM01.DBF',
'D:\ORACLE\ORADATA\FRAME\UNDOTBS01.DBF',
'D:\ORACLE\ORADATA\FRAME\CWMLITE01.DBF',
'D:\ORACLE\ORADATA\FRAME\DRSYS01.DBF',
'D:\ORACLE\ORADATA\FRAME\EXAMPLE01.DBF',
'D:\ORACLE\ORADATA\FRAME\INDX01.DBF',
'D:\ORACLE\ORADATA\FRAME\TOOLS01.DBF',
'D:\ORACLE\ORADATA\FRAME\USERS01.DBF',
'D:\ORACLE\ORADATA\FRAME\FRAME.DBF'
CHARACTER SET WE8MSWIN1252
;
3.Start the database.
SQL>ALTER DATABASE OPEN.
Backing Up Control Files
1. Back up the control file to a binary file (duplicate of existing control file) using
the following statement:
ALTER DATABASE BACKUP CONTROLFILE TO '/oracle/backup/control.bkp' REUSE;
2. Produce SQL statements that can later be used to recreate your control file:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
Managing the Online Redo Log
Redo Log Contents
Online redo log files are filled with redo records. A redo record, also called a redo entry, is made up of a group of change vectors, each of which is a description of a change made to a single block in the database. For example, if you change a salary value in an employee table, you generate a redo record containing change vectors that describe changes to the data segment block for the table, the rollback segment data block, and the transaction table of the rollback segments.Redo entries record data that you can use to reconstruct all changes made to the database, including the rollback segments. Therefore, the online redo log also protects rollback data. When you recover the database using redo data, Oracle reads
the change vectors in the redo records and applies the changes to the relevant blocks. Redo records are buffered in a circular fashion in the redo log buffer of the SGA (see "How Oracle Writes to the Online Redo Log") and are written to one of the online redo log files by the Oracle background process Log Writer (LGWR). Whenever a transaction is committed, LGWR writes the transaction’s redo records from the redo
log buffer of the SGA to an online redo log file, and a system change number (SCN) is assigned to identify the redo records for each committed transaction. Only when all redo records associated with a given transaction are safely on disk in the online logs is the user process notified that the transaction has been committed.Redo records can also be written to an online redo log file before the corresponding transaction is committed. If the redo log buffer fills, or another transaction commits, LGWR flushes all of the redo log entries in the redo log buffer to an online redo log file, even though some redo records may not be committed. If necessary, Oracle can roll back these changes.
Oracle Writes to the Online Redo Log
n If archiving is disabled (NOARCHIVELOG mode), a filled online redo log file is available once the changes recorded in it have been written to the datafiles.
n If archiving is enabled (ARCHIVELOG mode), a filled online redo log file is available to LGWR once the changes recorded in it have been written to the datafiles and once the file has been archived.
Active (Current) and Inactive Online Redo Log Files
At any given time, Oracle uses only one of the online redo log files to store redo records written from the redo log buffer. The online redo log file that LGWR is actively writing to is called the current online redo log file.Online redo log files that are required for instance recovery are called active online redo log files. Online redo log files that are not required for instance recovery are called inactive.
If you have enabled archiving (ARCHIVELOG mode), Oracle cannot reuse or overwrite an active online log file until ARCn has archived its contents. If archiving is disabled (NOARCHIVELOG mode), when the last online redo log file fills writing continues by overwriting the first available active file.
Log Switches and Log Sequence Numbers
A log switch is the point at which Oracle ends writing to one online redo log file and begins writing to another. Normally, a log switch occurs when the current online redo log file is completely filled and writing must continue to the next online redo log file. However, you can specify that a log switch occurs in a time-based manner, regardless of whether the current online redo log file is completely filled. You can also force log switches manually.
Choosing the Number of Online Redo Log Files
The best way to determine the appropriate number of online redo log files for a database instance is to test different configurations. The optimum configuration has the fewest groups possible without hampering LGWR’s writing redo log information. In some cases, a database instance may require only two groups. In other situations, a database instance may require additional groups to guarantee that a recycled
group is always available to LGWR. During testing, the easiest way to determine if the current online redo log configuration is satisfactory is to examine the contents of the LGWR trace file and the database’s alert log. If messages indicate that LGWR frequently has to wait for a group because a checkpoint has not completed or a group has not been archived, add groups. Consider the parameters that can limit the number of online redo log files before setting up or altering the configuration of an instance’s online redo log. The following parameters limit the number of online redo log files that you can add to a database:
Creating Online Redo Log Groups and Members
Creating Online Redo Log Groups
To create a new group of online redo log files, use the SQL statement ALTER
DATABASE with the ADD LOGFILE clause.
The following statement adds a new group of redo logs to the database:
ALTER DATABASE ADD LOGFILE ('/oracle/dbs/log1c.rdo', '/oracle/dbs/log2c.rdo') SIZE 500K;
You can also specify the number that identifies the group using the GROUP option:
SQL>ALTER DATABASE ADD LOGFILE GROUP 3 '/ORACLE/ORADATA/FRAME/REDO03.LOG' SIZE 1M;
Using group numbers can make administering redo log groups easier. However, the group number must be between 1 and MAXLOGFILES. Do not skip redo log file group numbers (that is, do not number your groups 10, 20, 30, and so on), or you will consume space in the control files of the database.
Creating Online Redo Log Members
In some cases, it might not be necessary to create a complete group of online redo log files. A group could already exist, but not be complete because one or more members of the group were dropped (for example, because of a disk failure). In this case, you can add new members to an existing group.
To create new online redo log members for an existing group, use the SQL statement ALTER DATABASE with the ADD LOG MEMBER parameter. The following statement adds a new redo log member to redo log group number 2:
SQL> alter database add logfile member 'e:/backup/redolog/redo02.log' to group 2;
Note: Use fully specify filenames of new log members to indicate where the operating system file should be created. Otherwise, the files will be created in either the default or current directory of the database server, depending upon your operating system. Notice that filenames must be specified, but sizes need not be. The size of the new members is determined from the size of the existing members of the group. When using the ALTER DATABASE statement, you can alternatively identify the target group by specifying all of the other members of the group in the TO
parameter, as shown in the following example:
ALTER DATABASE ADD LOGFILE MEMBER '/oracle/dbs/log2c.rdo' TO
('/oracle/dbs/log2a.rdo', '/oracle/dbs/log2b.rdo');
To Rename Online Redo Log Members
1. Shutdown the database.
SHUTDOWN
2. Copy the online redo log files to the new location. Operating system files, such as online redo log members, must be copied using the appropriate operating system commands. See your operating system specific documentation for more information about copying files.
mv /diska/logs/log1a.rdo /diskc/logs/log1c.rdo
mv /diska/logs/log2a.rdo /diskc/logs/log2c.rdo
3. Startup the database, mount, but do not open it.
CONNECT / as SYSDBA
STARTUP MOUNT
4. Rename the online redo log members.Use the ALTER DATABASE statement with the RENAME FILE clause to rename the database’s online redo log files.
ALTER DATABASE RENAME FILE '/diska/logs/log1a.rdo', '/diska/logs/log2a.rdo'
TO '/diskc/logs/log1c.rdo', '/diskc/logs/log2c.rdo';
5. Open the database for normal operation.
The online redo log alterations take effect when the database is opened.
Dropping Log Groups
To drop an online redo log group, you must have the ALTER DATABASE system privilege. Before dropping an online redo log group, consider the following restrictions and precautions:
n An instance requires at least two groups of online redo log files, regardless of the number of members in the groups. (A group is one or more members.)
n You can drop an online redo log group only if it is inactive. If you need to drop the current group, first force a log switch to occur.
n Make sure an online redo log group is archived (if archiving is enabled) before dropping it. To see whether this has happened, use the V$LOG view.
SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;
GROUP# ARC STATUS
--------- --- ----------------
1 YES ACTIVE
2 NO CURRENT
3 YES INACTIVE
4 YES INACTIVE
Drop an online redo log group with the SQL statement ALTER DATABASE with the DROP LOGFILE clause. The following statement drops redo log group number 3:
ALTER DATABASE DROP LOGFILE GROUP 3;
Dropping Online Redo Log Members
To drop an online redo log member, you must have the ALTER DATABASE system privilege. Consider the following restrictions and precautions before dropping individual online redo log members:
n It is permissible to drop online redo log files so that a multiplexed online redo log becomes temporarily asymmetric. For example, if you use duplexed groups of online redo log files, you can drop one member of one group, even though all other groups have two members each. However, you should rectify this situation immediately so that all groups have at least two members, and thereby eliminate the single point of failure possible for the online redo log.
n An instance always requires at least two valid groups of online redo log files,regardless of the number of members in the groups. (A group is one or more members.) If the member you want to drop is the last valid member of the group, you cannot drop the member until the other members become valid. To see a redo log file’s status, use the V$LOGFILE view. A redo log file becomes INVALID if Oracle cannot access it. It becomes STALE if Oracle suspects that it is not complete or correct. A stale log file becomes valid again the next time its group is made the active group.
n You can drop an online redo log member only if it is not part of an active or current group. If you want to drop a member of an active group, first force a log switch to occur.
n Make sure the group to which an online redo log member belongs is archived (if archiving is enabled) before dropping the member. To see whether this has happened, use the V$LOG view.
To drop specific inactive online redo log members, use the ALTER DATABASE statement with the DROP LOGFILE MEMBER clause.
The following statement drops the redo log /oracle/dbs/log3c.rdo:
ALTER DATABASE DROP LOGFILE MEMBER '/oracle/dbs/log3c.rdo';
When an online redo log member is dropped from the database, the operating system file is not deleted from disk. Rather, the control files of the associated database are updated to drop the member from the database structure. After dropping an online redo log file, make sure that the drop completed successfully,and then use the appropriate operating system command to delete the dropped online redo log file. To drop a member of an active group, you must first force a log switch.
Forcing Log Switches
A log switch occurs when LGWR stops writing to one online redo log group and starts writing to another. By default, a log switch occurs automatically when the current online redo log file group fills.You can force a log switch to make the currently active group inactive and available for online redo log maintenance operations. For example, you want to drop the currently active group, but are not able to do so until the group is inactive. You may also wish to force a log switch if the currently active group needs to be archived at a specific time before the members of the group are completely filled. This option is useful in configurations with large online redo log files that take a long time to fill.
To force a log switch, you must have the ALTER SYSTEM privilege. Use the ALTER SYSTEM statement with the SWITCH LOGFILE clause. The following statement forces a log switch:
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM CHECKPOINT;
Clearing an Online Redo Log File
An online redo log file might become corrupted while the database is open, and ultimately stop database activity because archiving cannot continue. In this situation the ALTER DATABASE CLEAR LOGFILE statement can be used reinitialize the file without shutting down the database.The following statement clears the log files in redo log group number 3:
ALTER DATABASE CLEAR LOGFILE GROUP 3;
This statement overcomes two situations where dropping redo logs is not possible:
n If there are only two log groups
n The corrupt redo log file belongs to the current group
If the corrupt redo log file has not been archived, use the UNARCHIVED keyword in the statement.
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;
This statement clears the corrupted redo logs and avoids archiving them. The cleared redo logs are available for use even though they were not archived. If you clear a log file that is needed for recovery of a backup, then you can no longer recover from that backup. Oracle writes a message in the alert log describing the backups from which you cannot recover.
Viewing Online Redo Log Information
V$LOG Displays the redo log file information from the control file
V$LOGFILE Identifies redo log groups and members and member status
V$LOG_HISTORY Contains log history information
TABLESPACE MANAGEMENT
Creating Locally managed tablespace
Locally managed tablespaces track all extent information in the tablespace itself,using bitmaps, resulting in the following benefits:
n Improved concurrency and speed of space operations, because space allocations and deallocations predominantly modify locally managed resources (bitmaps stored in header files) rather than requiring centrally managed resources such as enqueues
n Improved performance, because recursive operations that are sometimes required during dictionary-managed space allocation are eliminated
n Readable standby databases are allowed, because locally managed temporary tablespaces (used, for example, for sorts) are locally managed and thus do not generate any undo or redo.
n Simplified space allocation-when the AUTOALLOCATE clause is specified, appropriate extent size is automatically selected
n Reduced user reliance on the data dictionary because necessary information is stored in file headers and bitmap blocks
sql>CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
sql>CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
Specifying Segment Space Management in Locally Managed Tablespaces
CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
Dictionary-Managed Tablespaces
Starting with Oracle9i, the default for extent management when creating a tablespace is locally managed. However, you can explicitly specify that you want to create a dictionary-managed tablespace. For dictionary-managed tablespaces, Oracle updates the appropriate tables in the data dictionary whenever an extent is allocated, or freed for reuse.
Creating a Dictionary-Managed Tablespace
As an example, the following statement creates the tablespace tbsa, with the following characteristics:
n The data of the new tablespace is contained in a single datafile, 50M in size.
n The tablespace is explicitly created as a dictionary-managed tablespace by specifying EXTENT MANAGEMENT DICTIONARY.
n The default storage parameters for any segments created in this tablespace are explicitly set.
Note:Whenever an extent is allocated or freed,the information is updated in the corresponding dictonery tables.Such updates also generates undo information.
The following statement creates the tablespace tbsb:
CREATE TABLESPACE tbsb DATAFILE '/u02/oracle/data/tbsa01.dbf' SIZE 50M EXTENT MANAGEMENT DICTIONARY DEFAULT STORAGE ( INITIAL 50K NEXT 50K MINEXTENTS 2 MAXEXTENTS 50 PCTINCREASE 0);
This next example creates tablespace tbsb, but this time a block size that differs from the standard database block size (as specified by the DB_BLOCK_SIZE initialization parameter).
CREATE TABLESPACE tbsb DATAFILE '/u02/oracle/data/tbsb01.dbf' SIZE 50M EXTENT MANAGEMENT DICTIONARY BLOCKSIZE 8K DEFAULT STORAGE ( INITIAL 50K NEXT 50K MINEXTENTS 2 MAXEXTENTS 50
PCTINCREASE 0);
Altering a Dictionary-Managed Tablespace
One reason for using an ALTER TABLESPACE statement is to add a datafile. The following statement creates a new datafile for the tbsa tablespace:
ALTER TABLESPACE tbsa ADD DATAFILE '/u02/oracle/data/tbsa02.dbf' SIZE 1M;
Temporary Tablespaces
To improve the concurrence of multiple sort operations, reduce their overhead, or avoid Oracle space management operations altogether, create temporary tablespaces. A temporary tablespace can be shared by multiple users and can be assigned to users with the CREATE USER statement when you create users in the database.
Creating a Locally Managed Temporary Tablespace
You also use different views for viewing information about tempfiles than you would for datafiles. The V$TEMPFILE and DBA_TEMP_FILES views are analogous to the V$DATAFILE and DBA_DATA_FILES views.
The following statement creates a temporary tablespace in which each extent is 16M. Each 16M extent (which is the equivalent of 8000 blocks when the standard block size is 2K) is represented by a bit in the bitmap for the file.
CREATE TEMPORARY TABLESPACE lmtemp TEMPFILE '/u02/oracle/data/lmtemp01.dbf' SIZE 20M REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;
Altering a Locally Managed Temporary Tablespace
ALTER TABLESPACE lmtemp ADD TEMPFILE '/u02/oracle/data/lmtemp02.dbf' SIZE 2M REUSE;
The following statements take offline and bring online temporary files:
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' OFFLINE;
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' ONLINE;
The following statement resizes a temporary file:
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' RESIZE 4M;
The following statement drops a temporary file and deletes the operating system file:
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' DROP INCLUDING DATAFILES;
Creating a Dictionary-Managed Temporary Tablespace
CREATE TABLESPACE sort DATAFILE '/u02/oracle/data/sort01.dbf' SIZE 50M DEFAULT STORAGE (
INITIAL 2M NEXT 2M MINEXTENTS 1 PCTINCREASE 0) EXTENT MANAGEMENT DICTIONARY TEMPORARY;
Altering a Dictionary-Managed Temporary Tablespace
ALTER TABLESPACE tbsa TEMPORARY;
Altering Storage Settings for Tablespaces
ALTER TABLESPACE users DEFAULT STORAGE (NEXT 100K MAXEXTENTS 20 PCTINCREASE 0);
Coalescing Free Space in Dictionary-Managed Tablespaces
ALTER TABLESPACE tabsp_4 COALESCE;
Monitoring Free Space
SELECT BLOCK_ID, BYTES, BLOCKS FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME = 'TABSP_4'ORDER BY BLOCK_ID;
Bringing Tablespaces Online
ALTER TABLESPACE users ONLINE;
Making a Tablespace Read-Only
ALTER TABLESPACE flights READ ONLY;
SELECT SQL_TEXT, SADDR FROM V$SQLAREA,V$SESSION WHERE V$SQLAREA.ADDRESS = V$SESSION.SQL_ADDRESS
AND SQL_TEXT LIKE 'alter tablespace%';
SQL_TEXT SADDR
---------------------------------------- --------
alter tablespace tbs1 read only 80034AF0
Making a Read-Only Tablespace Writable
ALTER TABLESPACE flights READ WRITE;
Dropping Tablespaces
DROP TABLESPACE users INCLUDING CONTENTS;
DROP TABLESPACE users INCLUDING CONTENTS AND DATAFILES;
MANAGING DATAFILES
Enabling and Disabling Automatic Extension for a Datafile
The following example enables automatic extension for a datafile added to the
users tablespace:
ALTER TABLESPACE users ADD DATAFILE '/u02/oracle/rbdb1/users03.dbf' SIZE 10M AUTOEXTEND ON
NEXT 512K MAXSIZE 250M;
The next example disables the automatic extension for the datafile.
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' AUTOEXTEND OFF;
Manually Resizing a Datafile
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' RESIZE 100M;
Bringing Datafiles Online or Taking Offline in ARCHIVELOG Mode
The following statement brings the specified datafile online:
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' ONLINE;
To take the same file offline, issue the following statement:
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' OFFLINE;
Taking Datafiles Offline in NOARCHIVELOG Mode
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' OFFLINE DROP;
Renaming and Relocating Datafiles
To rename datafiles from a single tablespace, complete the following steps:
1. Take the non-SYSTEM tablespace that contains the datafiles offline.
For example:
ALTER TABLESPACE users OFFLINE NORMAL;
2. Rename the datafiles using the operating system.
3. Use the ALTER TABLESPACE statement with the RENAME DATAFILE option to change the filenames within the database.
For example, the following statement renames the datafiles
/u02/oracle/rbdb1/user1.dbf and /u02/oracle/rbdb1/user2.dbf
to/u02/oracle/rbdb1/users01.dbf and /u02/oracle/rbdb1/users02.dbf,
respectively:
ALTER TABLESPACE users RENAME DATAFILE '/u02/oracle/rbdb1/user1.dbf',
'/u02/oracle/rbdb1/user2.dbf'
TO '/u02/oracle/rbdb1/users01.dbf','/u02/oracle/rbdb1/users02.dbf';
Relocating and Renaming Datafiles in a Single Tablespace
Complete the following steps:
1. Identify the datafile names of interest.The following query of the data dictionary view DBA_DATA_FILES lists the datafile names and respective sizes (in bytes) of the users tablespace:
SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'USERS';
FILE_NAME BYTES
------------------------------------------ ----------------
/U02/ORACLE/RBDB1/USERS01.DBF 102400000
/U02/ORACLE/RBDB1/USERS02.DBF 102400000
2. Take the tablespace containing the datafiles offline, or shut down the database and restart and mount it, leaving it closed. Either option closes the datafiles of the tablespace.
3. Copy the datafiles to their new locations and rename them using the operating system.
4. Rename the datafiles within Oracle.
The datafile pointers for the files that make up the users tablespace, recorded in the control file of the associated database, must now be changed from the old names to the new names.
If the tablespace is offline but the database is open, use the ALTER TABLESPACE ... RENAME DATAFILE statement. If the database is mounted but closed, use the ALTER DATABASE ... RENAME FILE statement.
ALTER TABLESPACE users RENAME DATAFILE '/u02/oracle/rbdb1/users01.dbf',
'/u02/oracle/rbdb1/users02.dbf'TO '/u03/oracle/rbdb1/users01.dbf',
'/u04/oracle/rbdb1/users02.dbf';
5. Bring the tablespace online, or open the database.If the users tablespace is offline and the database is open, bring the tablespace
back online. If the database is mounted but closed, open the database.
6. Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.
BACK UP AND RECOVERY PROCEDURE
BACK UP PROCEDURE:-
OFFLINE BACKUP OR COLD BACKUP:-(MOST SIMPLEST BACKUP)
The backup all datafiles, Redo Logs, Control files, Parameter files (init.ora), and the password file using the operating system backup utility. It is simplest to implement.You shutdown the database,copy the files to tape & then restart the database.So you have to afford down time.
STEP 1:-Shutdown the database in normal mode.
#sqlplus /nolog
sql>select / as sysdba;
sql>shutdown immediate;
STEP2:-copy all the control files,redo log files,data files,parameter files ,password files in different locations.
STEP3:-Startup database.
Sql>startup
Disadvantage:-It is required to close database while taking backup offline.
HOT BACKUP:-(ONLINE BACKUP)
The database should be opened and database must be running in archivelog mode.Generally it needs----
1.Backup the control files.
2.Backup the datafiles of all the tablespaces;
3.Archive the inactive and active redo log files.
4.Backup the paramater file.
Running the database in archive log mode
For Automatic Archiving when starting up the instance, follow these steps:
Setting the INIT.ORA parameter file as follows:
1. Log_Archive_Start = True
2. Log_Archive_Dest = filename
3. Log_Archive_Format = extension
NOTE: Uncommenting the line in INIT.ORA will cause automatic archiving if archiving has been enabled using ALTER DATABASE ARCHIVELOG.
1. log_archive_start = true
log_archive_dest_1 = "location=C:\Oracle\admin\FRAME\arch"
log_archive_dest_2=”location=E:\framedb\arch\arcredoFRAME”
log_archive_format = frame_%t_%s.arc
%s=log sequence number,%t=redo log thread number.
If you want to change the archive destination,for online database,issue
sql> alter system set log_archive_dest_1='location=path';(on the fly updation in spfile)
Next time you up the database,it will archive redo log files in the location set into parameter file.
Tuning
Options:=Add the parameter in init.ora ,
log_archive_min_succeed_dest=2 or 3
log_archive_duplex_dest=1 or 2
LOG_ARCHIVE_DEST_STATE_n
Add the 3rd destination ,so that even if two location filled up,it will archive in 3rd location.For doing that set
log_archive_dest_state_1=enable.
log_archive_dest_state_2=defer.
log_archive_dest_state_3=enable.
You can change the location as
sql>alter session set log_archive_dest_state_n=ENABLE.
ENABLE ARCHIVE MODE
Placing your database in archivelog mode
#sqlplus \nolog
sql>connect \ as sysdba
sql>shutdown(Do not use shutdown abort)
sql>create spfile from pfile='c:\oracle\admin\frame\pfile\init.ora';
sql>startup mount;
sql>alter database archivelog;
sql>archive log start;
sql>alter database open;
sql>archive log list
sql>alter system switch logfile;
Archiving Manually
sql>archive log all(It tells oracle to copy all log files that have been filled but that haven't been copied yet);
RECOVERY FROM LOST CONTROL FILE
=========================================================================================
case1:One controlfile control01.ctl has been corrupted/deleted from original location
1.D:\Documents and Settings\DEB>SQLPLUS
SQL*Plus: Release 9.0.1.0.1 - Production on Thu Dec 8 07:38:29 2005
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Enter user-name: sys/sys as sysdba
Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 160198428 bytes
Fixed Size 282396 bytes
Variable Size 125829120 bytes
Database Buffers 33554432 bytes
Redo Buffers 532480 bytes
ORA-00205: error in identifying controlfile, check alert log for more info
RECOVERY
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
A.1.Change the init.ora parameter:delete the particular entry of control01.ctl from control file parameter.
2.D:\Documents and Settings\DEB>SQLPLUS
SQL*Plus: Release 9.0.1.0.1 - Production on Thu Dec 8 07:44:00 2005
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Enter user-name: sys/sys as sysdba
Connected to an idle instance.
SQL> startup pfile='d:\oracle\admin\frame\pfile\init.ora';
ORACLE instance started.
Total System Global Area 160198428 bytes
Fixed Size 282396 bytes
Variable Size 125829120 bytes
Database Buffers 33554432 bytes
Redo Buffers 532480 bytes
Database mounted.
Database opened.
SQL>shutdown abort
SQL>create spfile from pfile ;
SQL>startup
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
B.1.If backup of controlfile is availabe then copy it from backup location
2.SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 160198428 bytes
Fixed Size 282396 bytes
Variable Size 125829120 bytes
Database Buffers 33554432 bytes
Redo Buffers 532480 bytes
Database mounted.
Database opened.
============================================================================================================================================
CASE 2:All the controlfiles are deleted and no backup is available.
D:\Documents and Settings\DEB>SQLPLUS
SQL*Plus: Release 9.0.1.0.1 - Production on Thu Dec 8 07:59:27 2005
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Enter user-name: sys/sys as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 160198428 bytes
Fixed Size 282396 bytes
Variable Size 125829120 bytes
Database Buffers 33554432 bytes
Redo Buffers 532480 bytes
ORA-00205: error in identifying controlfile, check alert log for more info
RECOVERY:
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 160198428 bytes
Fixed Size 282396 bytes
Variable Size 125829120 bytes
Database Buffers 33554432 bytes
Redo Buffers 532480 bytes
###############################The command to be executed as written in .trc file for NORESETLOGS MODE###################################################
SQL> CREATE CONTROLFILE REUSE DATABASE "FRAME" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 50
3 MAXLOGMEMBERS 5
4 MAXDATAFILES 100
5 MAXINSTANCES 1
6 MAXLOGHISTORY 113
7 LOGFILE
8 GROUP 1(
9 'D:\ORACLE\ORADATA\FRAME\REDO01.LOG',
10 'E:\BACKUP\REDOLOG\REDO01.LOG'
11 ) SIZE 100M,
12 GROUP 2(
13 'D:\ORACLE\ORADATA\FRAME\REDO02.LOG',
14 'E:\BACKUP\REDOLOG\REDO02.LOG'
15 ) SIZE 100M,
16 GROUP 3(
17 'D:\ORACLE\ORADATA\FRAME\REDO03.LOG',
18 'E:\BACKUP\REDOLOG\REDO03.LOG'
19 ) SIZE 100M
20 # STANDBY LOGFILE
SP2-0734: unknown command beginning "STANDBY LO..." - rest of line ignored.
20 DATAFILE
21 'D:\ORACLE\ORADATA\FRAME\SYSTEM01.DBF',
22 'D:\ORACLE\ORADATA\FRAME\UNDOTBS01.DBF',
23 'D:\ORACLE\ORADATA\FRAME\CWMLITE01.DBF',
24 'D:\ORACLE\ORADATA\FRAME\DRSYS01.DBF',
25 'D:\ORACLE\ORADATA\FRAME\EXAMPLE01.DBF',
26 'D:\ORACLE\ORADATA\FRAME\INDX01.DBF',
27 'D:\ORACLE\ORADATA\FRAME\TOOLS01.DBF',
28 'D:\ORACLE\ORADATA\FRAME\USERS01.DBF'
29 CHARACTER SET WE8MSWIN1252
30 ;
Control file created.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'D:\ORACLE\ORADATA\FRAME\SYSTEM01.DBF'
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
####################################The control files will be created in original location as well as backup location.#############################################
RECOVERY FROM LOST DATA FILE
=========================================================================================
CASE 1:The system01.dbf file has been deleted and online backup is available.
A.D:\Documents and Settings\DEB>SQLPLUS
SQL*Plus: Release 9.0.1.0.1 - Production on Thu Dec 8 08:20:16 2005
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Enter user-name: sys/sys as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 160198428 bytes
Fixed Size 282396 bytes
Variable Size 125829120 bytes
Database Buffers 33554432 bytes
Redo Buffers 532480 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'D:\ORACLE\ORADATA\FRAME\SYSTEM01.DBF'
RECOVERY
##############################################Copying system01.dbf from backup location###########################################################
D:\Documents and Settings\DEB>sqlplus
SQL*Plus: Release 9.0.1.0.1 - Production on Thu Dec 8 08:24:54 2005
(c) Copyright 2001 Oracle Corporation. All rights reserved.
Enter user-name: sys/sys as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 160198428 bytes
Fixed Size 282396 bytes
Variable Size 125829120 bytes
Database Buffers 33554432 bytes
Redo Buffers 532480 bytes
Database mounted.
SQL> select d.file#, d.name, d.status, h.status from v$datafile d, v$datafile_header h where d.file# = h.file#;
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> exit
=============================================================================================================================================
CASE 2:user01.dbf file has been deleted but backup available.
Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 118255568 bytes
Fixed Size 282576 bytes
Variable Size 83886080 bytes
Database Buffers 33554432 bytes
Redo Buffers 532480 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: 'D:\ORACLE\ORADATA\FRAME\USERS01.DBF'
RECOVERY
############################################Copying users01.dbf from backup location##############################################################
SQL> recover tablespace users;
Media recovery complete.
SQL> alter database open;
Database altered.
=============================================================================================================================================
CASE 3:The example01.dbf file deleted and no backup available;
SQL> startup
ORACLE instance started.
Total System Global Area 118255568 bytes
Fixed Size 282576 bytes
Variable Size 83886080 bytes
Database Buffers 33554432 bytes
Redo Buffers 532480 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: 'D:\ORACLE\ORADATA\FRAME\EXAMPLE01.DBF'
RECOVERY
SQL> alter database datafile 'D:\ORACLE\ORADATA\FRAME\EXAMPLE01.DBF' offline;(Making Datafile offline)
SQL>alter database open;
#######################################Log applied later as backup was made available################################################################
SQL> alter database datafile 'd:\ORACLE\ORADATA\FRAME\example01.DBF' online;
alter database datafile 'd:\ORACLE\ORADATA\FRAME\example01.DBF' online
*
ERROR at line 1:
ORA-01113: file 5 needs media recovery
ORA-01110: data file 5: 'D:\ORACLE\ORADATA\FRAME\EXAMPLE01.DBF'
SQL> recover tablespace example;
ORA-00279: change 271411 generated at 12/08/2005 10:06:50 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_14.ARC
ORA-00280: change 271411 for thread 1 is in sequence #14
Specify log: {
ORA-00279: change 271451 generated at 12/08/2005 10:12:41 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_15.ARC
ORA-00280: change 271451 for thread 1 is in sequence #15
ORA-00278: log file 'D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_14.ARC' no longer
needed for this recovery
Specify log: {
Log applied.
Media recovery complete.
RECOVERY FROM DELETED LOGFILE
=================================================================================
CASE 1:The inactive redolog file deleted but multiplexed.
RECOVERY
No recovery needed but you can generate a lost redo again by this command:
SQL> alter database clear logfile group 1;(Log sequence number will be reinitialized)
============================================================================================================================================
CASE 2:Both inactive redolog file deleted
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 118255568 bytes
Fixed Size 282576 bytes
Variable Size 83886080 bytes
Database Buffers 33554432 bytes
Redo Buffers 532480 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\ORADATA\FRAME\REDO03.LOG'
ORA-00312: online log 3 thread 1: 'E:\BACKUP\REDOLOG\REDO03.LOG'
RECOVERY
SQL>select * from v$log(To view the inactive group no)
SQL> alter database clear logfile group 3;(The redolog files will be generated automatically)
Database altered.
SQL> alter database open;
Database altered.
===========================================================================================================================================
CASE 3:The online current redolog file deleted but multiplexed.
RECOVERY
No recovery required.
===========================================================================================================================================
CASE 4:The online redolog file deleted but not multiplexed.
RECOVERY:
SQL>Shutdown abort;
####################################After copying all the .dbf files from backup location to original location##################################################
SQL>Startup mount;
SQL>recover database until cancel;
Specify log: {
ORA-00279: change 291455 generated at 12/08/2005 10:17:46 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_16.ARC
ORA-00280: change 291455 for thread 1 is in sequence #16
ORA-00278: log file 'D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_15.ARC' no longer
needed for this recovery
Specify log: {
ORA-00279: change 311674 generated at 12/08/2005 10:47:01 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_17.ARC
ORA-00280: change 311674 for thread 1 is in sequence #17
ORA-00278: log file 'D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_16.ARC' no longer
needed for this recovery
Specify log: {
ORA-00279: change 331858 generated at 12/08/2005 10:55:10 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_18.ARC
ORA-00280: change 331858 for thread 1 is in sequence #18
ORA-00278: log file 'D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_17.ARC' no longer
needed for this recovery
Specify log: {
ORA-00279: change 352077 generated at 12/08/2005 11:13:19 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_19.ARC
ORA-00280: change 352077 for thread 1 is in sequence #19
ORA-00278: log file 'D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_18.ARC' no longer
needed for this recovery
Specify log: {
ORA-00279: change 352263 generated at 12/08/2005 11:15:32 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_20.ARC
ORA-00280: change 352263 for thread 1 is in sequence #20
ORA-00278: log file 'D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_19.ARC' no longer
needed for this recovery
Specify log: {
ORA-00279: change 352264 generated at 12/08/2005 11:17:35 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_21.ARC
ORA-00280: change 352264 for thread 1 is in sequence #21
ORA-00278: log file 'D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_20.ARC' no longer
needed for this recovery
Specify log: {
ORA-00279: change 372272 generated at 12/08/2005 11:26:02 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_22.ARC
ORA-00280: change 372272 for thread 1 is in sequence #22
ORA-00278: log file 'D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_21.ARC' no longer
needed for this recovery
Specify log: {
ORA-00279: change 392458 generated at 12/08/2005 11:31:11 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_23.ARC
ORA-00280: change 392458 for thread 1 is in sequence #23
ORA-00278: log file 'D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_22.ARC' no longer
needed for this recovery
Specify log: {
ORA-00279: change 392642 generated at 12/08/2005 11:32:04 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_24.ARC
ORA-00280: change 392642 for thread 1 is in sequence #24
ORA-00278: log file 'D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_23.ARC' no longer
needed for this recovery
Specify log: {
ORA-00279: change 412645 generated at 12/08/2005 11:46:03 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_25.ARC
ORA-00280: change 412645 for thread 1 is in sequence #25
ORA-00278: log file 'D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_24.ARC' no longer
needed for this recovery
Specify log: {
ORA-00308: cannot open archived log
'D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_25.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01245: offline file 5 will be lost if RESETLOGS is done
ORA-01110: data file 5: 'D:\ORACLE\ORADATA\FRAME\EXAMPLE01.DBF'
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01245: offline file 5 will be lost if RESETLOGS is done
ORA-01110: data file 5: 'D:\ORACLE\ORADATA\FRAME\EXAMPLE01.DBF'
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01245: offline file 5 will be lost if RESETLOGS is done
ORA-01110: data file 5: 'D:\ORACLE\ORADATA\FRAME\EXAMPLE01.DBF'
SQL> alter database datafile 'd:\ORACLE\ORADATA\FRAME\example01.DBF' online;
Database altered.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01195: online backup of file 5 needs more recovery to be consistent
ORA-01110: data file 5: 'D:\ORACLE\ORADATA\FRAME\EXAMPLE01.DBF'
SQL> shutdown abort
ORACLE instance shut down.
#####################################################Again copying all the .dbf files to original location##################################################
SQL> recover database until cancel;
ORA-01034: ORACLE not available
SQL> startup mount
ORACLE instance started.
Total System Global Area 118255568 bytes
Fixed Size 282576 bytes
Variable Size 83886080 bytes
Database Buffers 33554432 bytes
Redo Buffers 532480 bytes
Database mounted.
SQL> recover database until cancel;
ORA-00279: change 271411 generated at 12/08/2005 10:06:50 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_14.ARC
ORA-00280: change 271411 for thread 1 is in sequence #14
Specify log: {
ORA-00279: change 271451 generated at 12/08/2005 10:12:41 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_15.ARC
ORA-00280: change 271451 for thread 1 is in sequence #15
ORA-00278: log file 'D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_14.ARC' no longer
needed for this recovery
Specify log: {
ORA-00279: change 291455 generated at 12/08/2005 10:17:46 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_16.ARC
ORA-00280: change 291455 for thread 1 is in sequence #16
ORA-00278: log file 'D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_15.ARC' no longer
needed for this recovery
Specify log: {
ORA-00279: change 311674 generated at 12/08/2005 10:47:01 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_17.ARC
ORA-00280: change 311674 for thread 1 is in sequence #17
ORA-00278: log file 'D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_16.ARC' no longer
needed for this recovery
Specify log: {
ORA-00279: change 331858 generated at 12/08/2005 10:55:10 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_18.ARC
ORA-00280: change 331858 for thread 1 is in sequence #18
ORA-00278: log file 'D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_17.ARC' no longer
needed for this recovery
Specify log: {
ORA-00279: change 352077 generated at 12/08/2005 11:13:19 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_19.ARC
ORA-00280: change 352077 for thread 1 is in sequence #19
ORA-00278: log file 'D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_18.ARC' no longer
needed for this recovery
Specify log: {
ORA-00279: change 352263 generated at 12/08/2005 11:15:32 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_20.ARC
ORA-00280: change 352263 for thread 1 is in sequence #20
ORA-00278: log file 'D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_19.ARC' no longer
needed for this recovery
Specify log: {
ORA-00279: change 352264 generated at 12/08/2005 11:17:35 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_21.ARC
ORA-00280: change 352264 for thread 1 is in sequence #21
ORA-00278: log file 'D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_20.ARC' no longer
needed for this recovery
Specify log: {
ORA-00279: change 372272 generated at 12/08/2005 11:26:02 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_22.ARC
ORA-00280: change 372272 for thread 1 is in sequence #22
ORA-00278: log file 'D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_21.ARC' no longer
needed for this recovery
Specify log: {
ORA-00279: change 392458 generated at 12/08/2005 11:31:11 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_23.ARC
ORA-00280: change 392458 for thread 1 is in sequence #23
ORA-00278: log file 'D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_22.ARC' no longer
needed for this recovery
Specify log: {
ORA-00279: change 392642 generated at 12/08/2005 11:32:04 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_24.ARC
ORA-00280: change 392642 for thread 1 is in sequence #24
ORA-00278: log file 'D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_23.ARC' no longer
needed for this recovery
Specify log: {
ORA-00279: change 412645 generated at 12/08/2005 11:46:03 needed for thread 1
ORA-00289: suggestion : D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_25.ARC
ORA-00280: change 412645 for thread 1 is in sequence #25
ORA-00278: log file 'D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_24.ARC' no longer
needed for this recovery
Specify log: {
ORA-00308: cannot open archived log
'D:\ORACLE\ORADATA\FRAME\ARCHIVE\FRAME_1_25.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
SQL> alter database open resetlogs;
Database altered.
SQL>EXIT
RMAN BACKUP
CREATING CATALOG
CREATE RECOVERY CATALOG:-
1.FIRST CREATE A DATABASE NAMELY RCAT
2.TYPE COMMAND
# ORACLE_SID=[RCAT]?
SQLPLUS /NOLOG
SQL>CONNECT / AS SYSDBA
SQL>CREATE TABLESPACE RCAT DATAFILE '/FRAMEDB1/DBS/FRAME.DBF' SIZE 20M;
SQL>CREATE USER RMAN IDENTIFIED BY RMAN DEFAULT TABLESPACE RCAT TEMPORARY TABLESPACE TEMP;
SQL>GRANT CONNECT,RESOURCE,RECOVERY_CATALOG_OWNER TO RMAN;
3.TYPE COMMAND
#RMAN
RMAN>CONNECT CATALOG RMAN/RMAN
RMAN>CREATE CATALOG TABLESPACE RCAT
REGISTERING DATABASE:-
RMAN TARGET / CATALOG RMAN/RMAN@RCAT
RMAN>REGISTER DATABASE
#SQLPLUS RMAN/RMAN@RCAT
SQL>SELECT * FROM DB;
UNREGISTERING
SQL>EXECUTE DBMS_RCVCAT.UNREGISTER DATABASE(504,3960695);
SQL>SELECT * FROM DB;(NO ROWS SELECTED)
RESETTING THE RECOVERY CATALOG
# RMAN TARGET / CATALOG RMAN/RMAN@RCAT
RMAN>RESET DATABASE;
IF ANY DATABASE IS ADDED LATER
# RMAN TARGET / CATALOG RMAN/RMAN@RCAT
RMAN>RESYNC CATALOG;
RMAN>REPORT SCHEMA;
GENERATING LISTS AND REPORTS
# RMAN TARGET / CATALOG RMAN/RMAN@RCAT
RMAN>LIST INCARNATION OF DATABASE;
RMAN>LIST BACKUP OF TABLESPACE RCAT;
RMAN>LIST BACKUP OF DATABASE;
RMAN>LIST COPY OF DATABASE ARCHIVELOG ALL;
RMAN>REPORT OBSOLETE;
RMAN>REPORT SCHEMA ST SCN 10000;
RMAN>REPORT OBSOLATE REDUNDANCY 2;
RMAN>REPORT NEED BACKUP INCREMENTAL 3 DATABASE;
DIFFERENT WAY OF CONNECTING RMAN
#RMAN TARGET / CATALOG RMAN/RMAN@RCAT(CONNECTION WITH RECOVERY CATALOG +OS AUTHENTICATION)
#RMAN TARGET INTERNAL/ORACLE@FRAME NOCATALOG(PASSWORD FILE AUTHENTICATION +NOCATALOG DATABASE)
#RMAN TARGET / NOCATALOG(OS AUTHENTICATION+NOCATALOG DATABASE)
#RMAN TARGET / CATALOG RMAN/RMAN@RCAT @COMPLETE_BACK LOG RMAN_LOG.F(RUNNING FROM COMMAND PROMPT)
MORE FEATURES OF RMAN
CREATING SCRIPTS
#RMAN CATALOG RMAN/RMAN@RCAT
RMAN>CREATE SCRIPT COMPLETE_BACK{
ALLOCATE CHANNEL CH1 TYPE DISK;
ALLOCATE CHANNEL CH2 TYPE DISK;
BACKUP DATABASE;
}
RUNNING SCRIPTS
RMAN>RUN { EXECUTE SCRIPT COMPLETE_BACK ;}
DELETING SCRIPTS
RMAN>DELETE SCRIPT 'COMPLETE_BACK';
PRINTING SCRIPTS
RMAN>PRINT SCRIPT 'COMPLETE_BACK';
HOST COMMAND
RMAN>HOST 'cp /oracle/rmanback/* /framedb1/rmanback';
CROSSCHECKING DATABASE
RMAN>CROSSCHECK BACKUP OF DATABASE BETWEEN 'SYSDATE-1' AND 'SYSDATE-2';
DELETING EXPIRED BACKUP
RMAN>DELETE EXPIRED BACKUP OF DATAFILE1 AFTER 'SYSDATE-1';
VALIDATING BACKUPSET
RUN {
ALLOCATE CHANNEL CH1 TYPE DISK;
VALIDATE BACKUPSET 12;
}
CHECKING FOR CORRUPTION
RUN {
SET MAXCORRUPT FOR DATAFILE 3 TO 2;
ALLOCATE CHANNEL CH1 TYPE DISK;
BACKUP CHECK LOGICAL DATAFILE 3;
}
BACKUP PROCEDURE
BACKING UP WHOLE DATABASE
RMAN>RUN{
ALLOCATE CHANNEL CH1 TYPE DISK;
ALLOCATE CHANNEL CH2 TYPE DISK;
BACKUP DATABASE FORMAT '/oracle/rmanback/db_%t_%s_%d';
}
BACKUP FORMAT
%c=SPECIFIES COPY NO OF BACKUP PIECE
%d=SPECIFIES THE DATABASE NAME
%n=SPECIFIES THE DATABASE NAME
%p=BACKUP PIECE NUMBER
%t=SPECIFIES BACKUP SET TIME STAMP
%s=BACKUP SET NUMBER
%U=THE FORMAT IS EQUIVALENT TO '%u_%p_%c'
SPECIFIES SIZE OF BACKUP SET
RMAN>RUN{
ALLOCATE CHANNEL CH1 TYPE DISK;
ALLOCATE CHANNEL CH2 TYPE DISK;
ALLOCATE CHANNEL CH3 TYPE DISK;
BACKUP
DATAFILE 1,2,3,4,5,6,7,8,9
FILEPERSET=3
}
SPECIFIES A BACKUP ACROSS MULTIPLE DISK DRIVES
RUN{
ALLOCATE CHANNEL CH1 TYPE DISK FORMAT '/oracle/rmanback/db_%u',
ALLOCATE CHANNEL CH1 TYPE DISK FORMAT '/framedb1/rmanback/db_%u',
BACKUP DATABASE;
}
SKIPPING FILES WHEN BACKING UP DATABASE
RUN{
ALLOCATE CHANNEL CH1 TYPE DISK ;
BACKUP DATABASE;
SKIP READONLY;
SKIP OFFLINE;
}
COPYING DATAFILES
#RMAN{
ALLOCATE CHANNEL CH1 TYPE DISK;
BACKUP DATAFILECOPY '/oracle/rman/*.dbf';
}
BACKUP INDIVIDUAL TABLESPACE
#RMAN{
ALLOCATE CHANNEL CH1 TYPE DISK;
BACKUP TABLESPACE SYSTEM;
}
COPYING INDIVIDUAL DATAFILE
#RMAN{
ALLOCATE CHANNEL CH1 TYPE DISK ;
COPY DATAFILE 1 TO '/oracle/backup/system01.dbf';
}
BACKUP ARCHIVED REDO FILE
RUN{
ALLOCATE CHANNEL CH1 TYPE DISK;
SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
BACKUP ARCHIVELOG FROM TIME 'SYSDATE-1';
BACKUP ARCHIVELOG FROM TIME 'SYSDATE-5' UNTIL TIME 'SYSDATE-1'
}
CONTROLFILE BACKUP
RUN{
ALLOCATE CHANNEL CH1 TYPE DISK ;
BACKUP CURRENT CONTROLFILE;
BACKUP CONTROLFILECOPY '/oracle/rmanback/cf.f';
}
RECOVERY FEATURE OF RMAN
USING RMAN TO RESTORE DATAFILES TO DIFFERENT LOCATIONS
RMAN>RUN{
SET NEWNAME FOR DATAFILE 8 TO '/oracle/oradata/system01.dbf';
RESTORE DATABASE;SWITCH DATAFILE ALL;
RECOVER DATABASE;
ALTER DATABASE OPEN;
}
RMAN>RUN
{
SQL 'ALTER DATABASE TOOLS OFFLINE IMMEDIATE';
SET NEWNAME FOR DATAFILE '/oracle/FRAME/tools01.dbf' to '/oracle/tools01.dbf';
RESTORE(TABLESPACE TOOLS);
SWITCH DATAFILE 5;
RECOVER TABLESPACE TOOL;
SQL 'ALTER TABLESPACE TOOLS ONLINE';
}
RESTORING CONTROLFILE
RMAN>RUN{
ALLOCATE CHANNEL CH1 TYPE DISK;
RESTORE CONTROLFILE;
ALTER DATABASE MOUNT;
}
TO RESTORE CONTROLFILE TO A NEW LOCATION WITHOUT A RECOVERY CATALOG
#RMAN TARGET / NOCATALOG
STARTUP NOMOUNT
RUN{
ALLOCATE CHANNEL CH1 TYPE DISK;
RESTORE CONTROLFILE TO '/oracle/dbs/ctl.ctl';
SHUTDOWN IMMEDIATE;
REPLICATE CONTROLFILE FROM '/oracle/back/ctlback.ctl';
STARTUP MOUNT;
}
NORMAL RECOVERY
RUN{
SHUTDOWN ABORT;
STARTUP MOUNT;
ALLOCATE CHANNEL CH1 TYPE DISK;
ALLOCATE CHANNEL CH2 TYPE DISK;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN;
}
RECOVER FROM LOSS OF CONTROL FILE
RUN{
SHUTDOWN ABORT;
STARTUP NOMOUNT;
ALLOCATE CHANNEL CH1 TYPE DISK;
RESTORE CONTROLFILE;
ALTER DATABASE MOUNT;
ALLOCATE CHANNEL CH2 TYPE DISK;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}
TO RECOVER DATABASE UNTIL SPECIFIED LOG SEQUENCE NUMBER
1.#RMAN TARGET / CATALOG RMAN/RMAN@RCAT LOG=RMAN.LOG
2.SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
3.SELECT * FROM V$LOG_HISTORY;
4.RUN{
SET UNTIL LOGSEQ 6 THREAD 1;
ALLOCATE CHANNEL CH2 TYPE DISK;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}
4.RESET DATABASE;
No comments:
Post a Comment