Password Verification or Complexity function in Oracle
In database you may wish to set up rules how a user password will be. You can make password complex as you like. Suppose a common rule is username and password must not be same. To do so you have to do following tasks.
1)Create a password verification function.
2)Assign this verification function to your desired profile or system default profile.
3)Assign this profile to the users to whom you want to impose rules.
Oracle itself has a sample PL/SQL scripts by which we can impose password complexity. The script UTLPWDMG.SQL sets the default profile parameters. The oracle sample password verification function ensures that the password meets the following requirements.
•Is at least four characters long
•Differs from the user name
•Has at least one alpha, one numeric, and one punctuation mark character
•Is not simple or obvious, such as welcome, account, database, or user
•Differs from the previous password by at least 3 characters
The default complexity function can be seen as $ORACLE_HOME/rdbms/admin/utlpwdmg.sql location. Just open the file with any editor and modify you need any.
Also look at the last few lines of the script. It also assign your default profile DEFAULT to a password verification function verify_function. However you can imply to verify other profile rather than DEFAULT by comment out the last paragraph of the script. Then execute the script. You must be a SYS user in order to execute password verification function.
With an example I have demonsrate the scenario.
1)Connect as a SYS user.
SQL> CONN / AS SYSDBA
Connected.
2)Execute the utlpwdmg.sql script.
SQL> @$ORACLE_HOME/rdbms/admin/utlpwdmg.sql
Function created.
3)Create the profile with this function. Like,
SQL> CREATE PROFILE TEST_PROFILE LIMIT PASSWORD_VERIFY_FUNCTION verify_function;
Profile created.
4)Assign this profile to a user.
SQL> ALTER USER INDIA PROFILE TEST_PROFILE;
User altered.
5)Connct the user to which profile is assigned.
SQL> CONN INDIA/T
Connected.
6)Check wether password complexity function work or not by.
SQL> PASSWORD
Changing password for INDIA
Old password:
New password:
Retype new password:
ERROR:
ORA-28003: password verification for the specified password failed
ORA-20002: Password length less than 4
Password unchanged
Suppose I gave here 1!ab for new password and it worked ok.
SQL> passw
Changing password for INDIA
Old password:
New password:
Retype new password:
Password changed
Wednesday, May 28, 2008
ORA-01536
ORA-01536
Oracle 10.1.0.2.0
Windows Xp
Archive Log Disable
Suddenly We get ORA-01536 error.But our tablespace already have 12 GB Free Space.
ORA-01536: space quota exceeded for tablespace 'string'
Cause: The space quota for the segment owner in the tablespace has been exhausted and the operation attempted the creation of a new segment extent in the tablespace.
Action: Either drop unnecessary objects in the tablespace to reclaim space or have a privileged user increase the quota on this tablespace for the segment owner
Suggestion :
1.You have to first check your tablespace have sufficient space.
select sum(bytes/1024/1024)
from dba_free_space
where tablespace_name = '&tbs_name';
2.Second check user have sufficient space on TABLESPACE.
select sum(bytes/1024/1024),tablespace_name
from dba_ts_quotas
where username = '&USERNAME'
group by tablespace_name;
3.Sometime happen due to "There may be a dependency object on this table.
Insert on this object may need to update the dependant object, which really exhausted the quota."
A.Find for any dependant object over that table.
select NAME,TYPE from dba_dependencies where REFERENCED_NAME='table name';
B.If found, find the owner of that object.
select OWNER,OBJECT_NAME from dba_objects where OBJECT_NAME='dependant object name';
C.Grant unlimited tablespace to that user.
grant unlimited tablespace to [dependant object owner name];
4.If all above point is not help then just revoke "unlimited tablespace" from user and grant "unlimited tablespace" to user again. and try your operation.
Oracle 10.1.0.2.0
Windows Xp
Archive Log Disable
Suddenly We get ORA-01536 error.But our tablespace already have 12 GB Free Space.
ORA-01536: space quota exceeded for tablespace 'string'
Cause: The space quota for the segment owner in the tablespace has been exhausted and the operation attempted the creation of a new segment extent in the tablespace.
Action: Either drop unnecessary objects in the tablespace to reclaim space or have a privileged user increase the quota on this tablespace for the segment owner
Suggestion :
1.You have to first check your tablespace have sufficient space.
select sum(bytes/1024/1024)
from dba_free_space
where tablespace_name = '&tbs_name';
2.Second check user have sufficient space on TABLESPACE.
select sum(bytes/1024/1024),tablespace_name
from dba_ts_quotas
where username = '&USERNAME'
group by tablespace_name;
3.Sometime happen due to "There may be a dependency object on this table.
Insert on this object may need to update the dependant object, which really exhausted the quota."
A.Find for any dependant object over that table.
select NAME,TYPE from dba_dependencies where REFERENCED_NAME='table name';
B.If found, find the owner of that object.
select OWNER,OBJECT_NAME from dba_objects where OBJECT_NAME='dependant object name';
C.Grant unlimited tablespace to that user.
grant unlimited tablespace to [dependant object owner name];
4.If all above point is not help then just revoke "unlimited tablespace" from user and grant "unlimited tablespace" to user again. and try your operation.
Thursday, May 22, 2008
Logical_bkp_EXPORT_linux
Logical_bkp_EXPORT_linux
Oracle Version : 10.1.3.0.0
OS System : Linux Ver 3
-----------------------------
Export_bkp_scheduling on linux server.
-------------------------------------
shell script for backup.
-----------------------
oracle@oracle]$vi expbkp.sh
#press i or a ( for insert mode)
#!/bin/bash
ORACLE_HOME= /db/app/oracle/orahome #put_oracle_home_path
export ORACLE_HOME
export PATH=$ORACLE_HOME/bin:$PATH
#for system date with dmp file.
value= ` date '%d%m%y'`
exp userid/pwd file=file_name$value.dmp log=log_name$value.log
#type shift + zz for file closing.
shift+ZZ
-----------------------------------------
oracle@oracle]$cat expbkp.sh # check expbkp.sh file contents.
--------------------------------------------------------
Schedule ur backup according ur time Eg> every night 10 pm. for scheduling use "CRONTAB" utility.
-------------------------------------
oracle@oracle]$crontab -e #for create new crontab file.
#press i or a for insert mode
0 20 * * * /db/app/expbkp.sh #give compete path of ur .sh ( bkp script file).
------------------------------------------
oracle@oracle]$crontab -l ( for check crontab file)
oracle@oracle]$crontab -r ( for delete crontab file entry )
-------------------------------------------------------
Oracle Version : 10.1.3.0.0
OS System : Linux Ver 3
-----------------------------
Export_bkp_scheduling on linux server.
-------------------------------------
shell script for backup.
-----------------------
oracle@oracle]$vi expbkp.sh
#press i or a ( for insert mode)
#!/bin/bash
ORACLE_HOME= /db/app/oracle/orahome #put_oracle_home_path
export ORACLE_HOME
export PATH=$ORACLE_HOME/bin:$PATH
#for system date with dmp file.
value= ` date '%d%m%y'`
exp userid/pwd file=file_name$value.dmp log=log_name$value.log
#type shift + zz for file closing.
shift+ZZ
-----------------------------------------
oracle@oracle]$cat expbkp.sh # check expbkp.sh file contents.
--------------------------------------------------------
Schedule ur backup according ur time Eg> every night 10 pm. for scheduling use "CRONTAB" utility.
-------------------------------------
oracle@oracle]$crontab -e #for create new crontab file.
#press i or a for insert mode
0 20 * * * /db/app/expbkp.sh #give compete path of ur .sh ( bkp script file).
------------------------------------------
oracle@oracle]$crontab -l ( for check crontab file)
oracle@oracle]$crontab -r ( for delete crontab file entry )
-------------------------------------------------------
Table Copy From OneDB to AnotherDB
Table Copy From OneDB to AnotherDB
You can use Three Method.
1.Database link
2.Export or Import
3.Copy Command.
------------------------------------------
1.Database link
sql>conn u1/u1@db2
sql>create table a as select * from all_objects where rownum <= 10; table created. For database link you have "database link" or "create session" privs. sql>conn u1/u1@db1
connected
sql>create database link db2
connect to u1
identified by u1
using 'db2';
database link created
sql>create table t as select * from a@db2;
table created
sql>select count(*) from t;
----------------------------------------------------------
2.Through Export or Import Utilities.
------------------------------------
SQL> conn a1/a1@db1
Connected.
SQL> create table d as select * from all_objects where rownum <= 10; Table created. SQL> host exp a1/a1@db1 tables=d file=d:\oracle\d.dmp log=d:\oracle\d.log
Export: Release 10.1.0.2.0 - Production on Sat Dec 23 18:24:52 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table D 10 rows exported
Export terminated successfully without warnings.
SQL> conn a1/a1@db2
Connected.
SQL> host imp a1/a1@db2 fromuser=a1 touser=a1 file=d:\oracle\d.dmp log=d:\oracle
\d.log ignore=y
Import: Release 10.1.0.2.0 - Production on Sat Dec 23 18:25:58 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.01.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. . importing table "D" 10 rows imported
Import terminated successfully without warnings.
------------------------------------------------------
3.Through Copy Command.
----------------------
SQL> conn a1/a1@db1
Connected.
SQL> create table m as select * from all_objects where rownum <= 10; Table created. SQL> con a1/a1@db2
Connected.
SQL> copy from a1/a1@db1 to a1/a1@db2 -
> create m -
> using select * from m;
Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table M created.
10 rows selected from a1@db1.
10 rows inserted into M.
10 rows committed into M at a1@db2.
You can use Three Method.
1.Database link
2.Export or Import
3.Copy Command.
------------------------------------------
1.Database link
sql>conn u1/u1@db2
sql>create table a as select * from all_objects where rownum <= 10; table created. For database link you have "database link" or "create session" privs. sql>conn u1/u1@db1
connected
sql>create database link db2
connect to u1
identified by u1
using 'db2';
database link created
sql>create table t as select * from a@db2;
table created
sql>select count(*) from t;
----------------------------------------------------------
2.Through Export or Import Utilities.
------------------------------------
SQL> conn a1/a1@db1
Connected.
SQL> create table d as select * from all_objects where rownum <= 10; Table created. SQL> host exp a1/a1@db1 tables=d file=d:\oracle\d.dmp log=d:\oracle\d.log
Export: Release 10.1.0.2.0 - Production on Sat Dec 23 18:24:52 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table D 10 rows exported
Export terminated successfully without warnings.
SQL> conn a1/a1@db2
Connected.
SQL> host imp a1/a1@db2 fromuser=a1 touser=a1 file=d:\oracle\d.dmp log=d:\oracle
\d.log ignore=y
Import: Release 10.1.0.2.0 - Production on Sat Dec 23 18:25:58 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.01.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. . importing table "D" 10 rows imported
Import terminated successfully without warnings.
------------------------------------------------------
3.Through Copy Command.
----------------------
SQL> conn a1/a1@db1
Connected.
SQL> create table m as select * from all_objects where rownum <= 10; Table created. SQL> con a1/a1@db2
Connected.
SQL> copy from a1/a1@db1 to a1/a1@db2 -
> create m -
> using select * from m;
Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table M created.
10 rows selected from a1@db1.
10 rows inserted into M.
10 rows committed into M at a1@db2.
Control file
Control file
All Controlfile or one of them are corrupted then we have to create new controlfile.
through control file statement.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "taj"
NORESETLOGS [archivelog/noarchivelog]
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 10
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TAJ\REDO01.LOG' SIZE 10M,
GROUP 2 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TAJ\REDO02.LOG' SIZE 10M,
GROUP 3 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TAJ\REDO03.LOG' SIZE 10M
DATAFILE
'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TAJ\SYSTEM01.DBF' SIZE xxx,
'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TAJ\USERS01.DBF' SIZE
xxx,
.
.
.
.
CHARACTER SET [chracater_set]
================================================================
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 7271
LOGFILE
GROUP 1 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO01.LOG' SIZE 10M,
GROUP 2 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO02.LOG' SIZE 10M,
GROUP 3 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO03.LOG' SIZE 10M
-- STANDBY LOGFILE
DATAFILE
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF',
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBF',
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBF',
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF',
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBF',
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS02.DBF'
CHARACTER SET WE8MSWIN1252
;
All Controlfile or one of them are corrupted then we have to create new controlfile.
through control file statement.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "taj"
NORESETLOGS [archivelog/noarchivelog]
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 10
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TAJ\REDO01.LOG' SIZE 10M,
GROUP 2 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TAJ\REDO02.LOG' SIZE 10M,
GROUP 3 'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TAJ\REDO03.LOG' SIZE 10M
DATAFILE
'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TAJ\SYSTEM01.DBF' SIZE xxx,
'D:\ORACLE\PRODUCT\10.1.0\ORADATA\TAJ\USERS01.DBF' SIZE
xxx,
.
.
.
.
CHARACTER SET [chracater_set]
================================================================
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 7271
LOGFILE
GROUP 1 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO01.LOG' SIZE 10M,
GROUP 2 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO02.LOG' SIZE 10M,
GROUP 3 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO03.LOG' SIZE 10M
-- STANDBY LOGFILE
DATAFILE
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF',
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBF',
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBF',
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF',
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBF',
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS02.DBF'
CHARACTER SET WE8MSWIN1252
;
ORA-12520
ORA-12520
Oracle Version : 10.1.0.2.0
OS : Windows XP sp2
--------------------------------------------------
SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
processes integer 50
SQL> conn scott/tiger@test
ERROR:
ORA-12520: TNS:listener could not find available handler for requested type of
server
Clause : Process parameter value is LOW. we have to increase.
on Listner.log file
04-MAR-2007 10:23:38 * service_update * test * 0
04-MAR-2007 10:23:39 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=test)(CID=(PROGRAM=C:\oracle\product\10.1.0\Db_1\bin\sqlplus.exe)(HOST=0A49914)(USER=m.taj))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.64)(PORT=1199)) * establish * test * 12520
TNS-12520: TNS:listener could not find available handler for requested type of server
Lsnrctl services
Service "test" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:29 refused:0 state:blocked
LOCAL SERVER
Suggestion:
1.You should wait for some minutes and try to connect.
( but it is temporary solution)
2.You should increase PROCESSES parameter values.
( increase PROCESSES parameter and SHUTDOWN + STARTUP DATABASE)
Oracle Version : 10.1.0.2.0
OS : Windows XP sp2
------------------------------------
Database configure in Shared Server Mode.
--------------------------------------------
SQL> show parameter dispatchers
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------dispatchers string (PROTOCOL=TCP)
max_dispatchers integer
SQL> show parameter shared_servers
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------max_shared_servers integer
shared_servers integer 1
------------------------------------------------------------------------------------------------
after change shared server = 0 then i got error "ora-12520".
SQL> alter system set shared_servers = 0;
System altered.
SQL> conn scott/tiger@sh
ERROR:ORA-12520: TNS:listener could not find available handler for requested type ofserver
C:\>lsnrctl services
Service "sh" has 1 instance(s).
Instance "sh", status READY, has 2 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
"D000" established:3 refused:0 current:2 max:1002 state:blocked
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=taj.domainname.local)(PORT=3348))
-------------------------------------------------------------------
For above error you have to check configure two parameters.
1.Shared_servers
2.Dispatchers
SQL> alter system set shared_servers = 1;
System altered.
SQL> alter system set dispatchers = "(PROTOCOL=TCP)" ;
System altered.
Service "sh" has 1 instance(s).
Instance "sh", status READY, has 2 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
"D000" established:4 refused:0 current:2 max:1002 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=taj.domainname.local)(PORT=3348))
The command completed successfully
Oracle Version : 10.1.0.2.0
OS : Windows XP sp2
--------------------------------------------------
SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
processes integer 50
SQL> conn scott/tiger@test
ERROR:
ORA-12520: TNS:listener could not find available handler for requested type of
server
Clause : Process parameter value is LOW. we have to increase.
on Listner.log file
04-MAR-2007 10:23:38 * service_update * test * 0
04-MAR-2007 10:23:39 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=test)(CID=(PROGRAM=C:\oracle\product\10.1.0\Db_1\bin\sqlplus.exe)(HOST=0A49914)(USER=m.taj))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.100.64)(PORT=1199)) * establish * test * 12520
TNS-12520: TNS:listener could not find available handler for requested type of server
Lsnrctl services
Service "test" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:29 refused:0 state:blocked
LOCAL SERVER
Suggestion:
1.You should wait for some minutes and try to connect.
( but it is temporary solution)
2.You should increase PROCESSES parameter values.
( increase PROCESSES parameter and SHUTDOWN + STARTUP DATABASE)
Oracle Version : 10.1.0.2.0
OS : Windows XP sp2
------------------------------------
Database configure in Shared Server Mode.
--------------------------------------------
SQL> show parameter dispatchers
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------dispatchers string (PROTOCOL=TCP)
max_dispatchers integer
SQL> show parameter shared_servers
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------max_shared_servers integer
shared_servers integer 1
------------------------------------------------------------------------------------------------
after change shared server = 0 then i got error "ora-12520".
SQL> alter system set shared_servers = 0;
System altered.
SQL> conn scott/tiger@sh
ERROR:ORA-12520: TNS:listener could not find available handler for requested type ofserver
C:\>lsnrctl services
Service "sh" has 1 instance(s).
Instance "sh", status READY, has 2 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
"D000" established:3 refused:0 current:2 max:1002 state:blocked
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=taj.domainname.local)(PORT=3348))
-------------------------------------------------------------------
For above error you have to check configure two parameters.
1.Shared_servers
2.Dispatchers
SQL> alter system set shared_servers = 1;
System altered.
SQL> alter system set dispatchers = "(PROTOCOL=TCP)" ;
System altered.
Service "sh" has 1 instance(s).
Instance "sh", status READY, has 2 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
"D000" established:4 refused:0 current:2 max:1002 state:ready
DISPATCHER
(ADDRESS=(PROTOCOL=tcp)(HOST=taj.domainname.local)(PORT=3348))
The command completed successfully
Managing Backup Windows and Performance
Managing Backup Windows and Performance
Database : 10.2.0.1.0
OS : Linux
A backup window is a period of time during which a backup activity must complete
The BACKUP command supports a DURATION argument which lets you specify how long a given backup job is allowed to run.
Example :
BACKUP DURATION 1:00 TABLESPACE users;
Note : RMAN backs up the specified data at the maximum possible speed. If the backup is not complete in one hours, the backup is interrupted. Any completed backupsets are retained and can be used in restore operations, even if the entire backup is not complete. Any incomplete backupsets are discarded.
By default, when a BACKUP... DURATION command runs out of time before the backup completes, RMAN reports an error. (The effect of this is that if the command is running in a RUN block, the RUN block terminates.)
You can control this behavior by adding the PARTIAL option to the BACKUP... DURATION command.
BACKUP DURATION 1:00 PARTIAL TABLESPACE users FILESPERSET 1;
When PARTIAL is used, no error is reported when a backup command is interrupted due to the end of the backup window. Instead, a message showing which files could not be backed will be displayed. If the BACKUP command is part of a RUN block, then the remaining commands in the RUN block will continue to execute.
When using DURATION the least recently backed up files are backed up first.
Managing Backup Performance with MINIMIZE TIME and MINIMIZE LOAD
When using DURATION you can run the backup with the maximum possible performance, or run as slowly as possible while still finishing within the allotted time, to minimize the performance impact of backup tasks. To maximize performance, use the MINIMIZE TIME option with DURATION.
BACKUP DURATION 1:00 PARTIAL MINIMIZE TIME DATABASE FILESPERSET 1;
To extend the backup to use the full time available, use the MINIMIZE LOAD option.
BACKUP DURATION 4:00 PARTIAL MINIMIZE LOAD DATABASE FILESPERSET 1;
RMAN monitors the progress of the running backup, and periodically estimates how long the backup will take to complete at its present rate. If RMAN estimates that the backup will finish before the end of the backup window, it slows down the rate of backup so that the full available duration will be used. This reduces the overhead on the database associated with the backup.
Database : 10.2.0.1.0
OS : Linux
A backup window is a period of time during which a backup activity must complete
The BACKUP command supports a DURATION argument which lets you specify how long a given backup job is allowed to run.
Example :
BACKUP DURATION 1:00 TABLESPACE users;
Note : RMAN backs up the specified data at the maximum possible speed. If the backup is not complete in one hours, the backup is interrupted. Any completed backupsets are retained and can be used in restore operations, even if the entire backup is not complete. Any incomplete backupsets are discarded.
By default, when a BACKUP... DURATION command runs out of time before the backup completes, RMAN reports an error. (The effect of this is that if the command is running in a RUN block, the RUN block terminates.)
You can control this behavior by adding the PARTIAL option to the BACKUP... DURATION command.
BACKUP DURATION 1:00 PARTIAL TABLESPACE users FILESPERSET 1;
When PARTIAL is used, no error is reported when a backup command is interrupted due to the end of the backup window. Instead, a message showing which files could not be backed will be displayed. If the BACKUP command is part of a RUN block, then the remaining commands in the RUN block will continue to execute.
When using DURATION the least recently backed up files are backed up first.
Managing Backup Performance with MINIMIZE TIME and MINIMIZE LOAD
When using DURATION you can run the backup with the maximum possible performance, or run as slowly as possible while still finishing within the allotted time, to minimize the performance impact of backup tasks. To maximize performance, use the MINIMIZE TIME option with DURATION.
BACKUP DURATION 1:00 PARTIAL MINIMIZE TIME DATABASE FILESPERSET 1;
To extend the backup to use the full time available, use the MINIMIZE LOAD option.
BACKUP DURATION 4:00 PARTIAL MINIMIZE LOAD DATABASE FILESPERSET 1;
RMAN monitors the progress of the running backup, and periodically estimates how long the backup will take to complete at its present rate. If RMAN estimates that the backup will finish before the end of the backup window, it slows down the rate of backup so that the full available duration will be used. This reduces the overhead on the database associated with the backup.
Switch Undo Tablespace
Switch Undo Tablespace
SQL> select name from v$tablespace where name like 'UNDO%';
NAME
------------------------------
UNDOTBS1
UNDOTBS2
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> alter system set undo_tablespace = 'UNDOTBS2';
System altered.
On another session which connect as scott user
SQL> insert into test select * from all_objects;
47327 rows created.
SQL> select status from v$rollstat;
STATUS
---------------
ONLINE
PENDING OFFLINE
PENDING OFFLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
STATUS
---------------
PENDING OFFLINE
ONLINE
ONLINE
14 rows selected.
On another session which connect as scott user
SQL> commit;
Commit complete.
SQL> select status from v$rollstat;
STATUS
---------------
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
11 rows selected.
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS2
Note: When you change Undo Tbs say undotbs1 to undotbs2 then all existing trasaction they using UNDOTBS1 they still using undotbs1 and status show "OFFLINE PENDING".and all new transaction after changed undotbs "alter system undo_tablespace" statements issued used New Undo Tablespace.
You cann't drop or offline if status show offline pending.
when all transaction which using old undotbs are COMMIT;
then you can drop or offline old undotbs.
SQL> select status from v$rollstat;
STATUS
---------------
ONLINE
PENDING OFFLINE
PENDING OFFLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
STATUS
---------------
PENDING OFFLINE
ONLINE
ONLINE
14 rows selected.
SQL> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use
SQL> alter tablespace undotbs1 offline;
alter tablespace undotbs1 offline
*
ERROR at line 1:
ORA-30042: Cannot offline the undo tablespace
SQL> select name from v$tablespace where name like 'UNDO%';
NAME
------------------------------
UNDOTBS1
UNDOTBS2
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> alter system set undo_tablespace = 'UNDOTBS2';
System altered.
On another session which connect as scott user
SQL> insert into test select * from all_objects;
47327 rows created.
SQL> select status from v$rollstat;
STATUS
---------------
ONLINE
PENDING OFFLINE
PENDING OFFLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
STATUS
---------------
PENDING OFFLINE
ONLINE
ONLINE
14 rows selected.
On another session which connect as scott user
SQL> commit;
Commit complete.
SQL> select status from v$rollstat;
STATUS
---------------
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
11 rows selected.
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS2
Note: When you change Undo Tbs say undotbs1 to undotbs2 then all existing trasaction they using UNDOTBS1 they still using undotbs1 and status show "OFFLINE PENDING".and all new transaction after changed undotbs "alter system undo_tablespace" statements issued used New Undo Tablespace.
You cann't drop or offline if status show offline pending.
when all transaction which using old undotbs are COMMIT;
then you can drop or offline old undotbs.
SQL> select status from v$rollstat;
STATUS
---------------
ONLINE
PENDING OFFLINE
PENDING OFFLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
ONLINE
STATUS
---------------
PENDING OFFLINE
ONLINE
ONLINE
14 rows selected.
SQL> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use
SQL> alter tablespace undotbs1 offline;
alter tablespace undotbs1 offline
*
ERROR at line 1:
ORA-30042: Cannot offline the undo tablespace
Recovery Manager Architecture
Recovery Manager Architecture
RMAN Enviourment
Recovery Manager (RMAN) is a client application that performs backup and recovery operations
Components of the RMAN Environment
1.Target database
2.RMAN client
3.Recovery catalog database
4.Recovery catalog schema
5.Standby database
6.Duplicate database
7.Media management application
8.Media management catalog
9.Enterprise Manager
Using RMAN with Command Files
E:\>c:\oracle\product\10.1.0\db_1\bin\RMAN TARGET /@db02 CATALOG=rman/cat@catdb
CMDFILE=d:\test.rcv LOG=d:\test.txt
RMAN> 2> 3> 4> 5> 6> 7>
E:\>
Content of CMDFILE.
configure controlfile autobackup on;
backup database;
backup database archivelog all;
Controlling RMAN Output
v$RMAN_OUTPUT
We can also see job status completed and currently running.
v$RMAN_STATUS
Contents of the Recovery Catalog
1.Datafile and archived redo log backup sets and backup pieces
2.Datafile copies
3.Archived redo logs and their copies
4.Tablespaces and datafiles on the target database
5.Stored scripts, which are named user-created sequences of RMAN commands
6.Persistent RMAN configuration settings
Resynchronization of the Recovery Catalog
When Should We Resynchronize?
1.Resynchronizing When the Recovery Catalog is Unavailable
2.Resynchronizing in ARCHIVELOG Mode When You Back Up Infrequently
3.Resynchronizing After Physical Database Changes
--Adding or dropping a tablespace
--Adding a new datafile to an existing tablespace
--Adding or dropping a rollback segment
RMAN Enviourment
Recovery Manager (RMAN) is a client application that performs backup and recovery operations
Components of the RMAN Environment
1.Target database
2.RMAN client
3.Recovery catalog database
4.Recovery catalog schema
5.Standby database
6.Duplicate database
7.Media management application
8.Media management catalog
9.Enterprise Manager
Using RMAN with Command Files
E:\>c:\oracle\product\10.1.0\db_1\bin\RMAN TARGET /@db02 CATALOG=rman/cat@catdb
CMDFILE=d:\test.rcv LOG=d:\test.txt
RMAN> 2> 3> 4> 5> 6> 7>
E:\>
Content of CMDFILE.
configure controlfile autobackup on;
backup database;
backup database archivelog all;
Controlling RMAN Output
v$RMAN_OUTPUT
We can also see job status completed and currently running.
v$RMAN_STATUS
Contents of the Recovery Catalog
1.Datafile and archived redo log backup sets and backup pieces
2.Datafile copies
3.Archived redo logs and their copies
4.Tablespaces and datafiles on the target database
5.Stored scripts, which are named user-created sequences of RMAN commands
6.Persistent RMAN configuration settings
Resynchronization of the Recovery Catalog
When Should We Resynchronize?
1.Resynchronizing When the Recovery Catalog is Unavailable
2.Resynchronizing in ARCHIVELOG Mode When You Back Up Infrequently
3.Resynchronizing After Physical Database Changes
--Adding or dropping a tablespace
--Adding a new datafile to an existing tablespace
--Adding or dropping a rollback segment
RMAN-06054:media recovery requesting unknown
RMAN-06054: media recovery requesting unknown log: thread x seq xxx lowscn xxxxxx
SQL> conn / as sysdba
Connected.
SQL> archive log list
Database log mode
Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 23
Next log sequence to archive 25
Current log sequence 25
**********************************************************
SQL> host rman target=sys
Recovery Manager: Release 10.1.0.2.0 - ProductionCopyright (c) 1995, 2004,
Oracle. All rights reserved.
target database
Password:
connected to target database: ORCL (DBID=1134976990)
RMAN> show all;
using target database controlfile instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\SNCFORCL.ORA'; # default
****************************
RMAN> configure controlfile autobackup on;new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
*************************************
RMAN> run
2> {
3> allocate channel c1 device type disk;
4> backup database plus archivelog;
5> }
allocated channel: c1channel c1: sid=200 devtype=DISKStarting backup at 23-NOV-06current log archivedStarting backup at 23-NOV-06current log archivedchannel c1: starting archive log backupsetchannel c1: specifying archive log(s) in backup setinput archive log thread=1 sequence=25 recid=1 stamp=607282901channel c1: starting piece 1 at 23-NOV-06channel c1: finished piece 1 at 23-NOV-06piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2006_11_23\O1_MF_ANNNN_TAG20061123T174141_2PC98Y2P_.BKP comment=NONEchannel c1: backup set complete, elapsed time: 00:00:11
Finished backup at 23-NOV-06Starting backup at 23-NOV-06channel c1: starting full datafile backupsetchannel c1: specifying datafile(s) in backupsetinput datafile fno=00001 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBFinput datafile fno=00003 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBFinput datafile fno=00005 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBFinput datafile fno=00002 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBFinput datafile fno=00004 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBFchannel c1: starting piece 1 at 23-NOV-06channel c1: finished piece 1 at 23-NOV-06piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2006_11_23\O1_MF_NNNDF_TAG20061123T174153_2PC992LS_.BKP comment=NONEchannel c1: backup set complete, elapsed time: 00:01:33Finished backup at 23-NOV-06Starting backup at 23-NOV-06current log archivedchannel c1: starting archive log backupsetchannel c1: specifying archive log(s) in backup setinput archive log thread=1 sequence=26 recid=2 stamp=607283007channel c1: starting piece 1 at 23-NOV-06channel c1: finished piece 1 at 23-NOV-06piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2006_11_23\O1_MF_ANNNN_TAG20061123T174328_2PC9D278_.BKP comment=NONEchannel c1: backup set complete, elapsed time: 00:00:02Finished backup at 23-NOV-06Starting Control File and SPFILE Autobackup at 23-NOV-06piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2006_11_23\O1_MF_S_607283011_2PC9D4X7_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 23-NOV-06released channel: c1
********************************************************
C:\>del d:\oracle\product\10.1.0\oradata\orcl\*.dbf
C:\>del d:\oracle\product\10.1.0\oradata\orcl\*.ctl
C:\>del d:\oracle\product\10.1.0\oradata\orcl\*.log
**********************************************************
C:\>sqlplus "sys as sysdba"
SQL*Plus: Release 10.1.0.2.0 - Production on Thu Nov 23 17:47:56 2006Copyright (c) 1982, 2004,
Oracle. All rights reserved.
Enter password:
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 171966464
bytesFixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
ORA-00205: error in identifying controlfile, check alert log for more info
*************************************
SQL> host rman target=sys
Recovery Manager: Release 10.1.0.2.0 - ProductionCopyright (c) 1995, 2004,
Oracle. All rights reserved.
target database Password:connected to target database: orcl (not mounted)
**********************************************
RMAN> restore controlfile from autobackup;
Starting restore at 23-NOV-06using channel ORA_DISK_1recovery area destination: D:\oracle\product\10.1.0\flash_recovery_areadatabase name (or lock name space) used for search: ORCLchannel ORA_DISK_1: autobackup found in the recovery areachannel ORA_DISK_1: autobackup found: D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2006_11_23\O1_MF_S_607283011_2PC9D4X7_.BKPchannel ORA_DISK_1: controlfile restore from autobackup completeoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL01.CTLoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL02.CTLoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL03.CTLFinished restore at 23-NOV-06
****************************************
RMAN> alter database mount;database mountedreleased channel: ORA_DISK_1
****************************************
RMAN> run
2> {
3> restore database;
4> recover database;
5> }
Starting restore at 23-NOV-06Starting implicit crosscheck backup at 23-NOV-06allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=214 devtype=DISKCrosschecked 3 objectsFinished implicit crosscheck backup at 23-NOV-06Starting implicit crosscheck copy at 23-NOV-06using channel ORA_DISK_1Finished implicit crosscheck copy at 23-NOV-06searching for all files in the recovery areacataloging files...cataloging done
List of Cataloged Files
=======================
File Name: D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2006_11_23\O1_MF_S_607283011_2PC9D4X7_.BKPusing channel ORA_DISK_1channel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBFrestoring datafile 00002 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBFrestoring datafile 00003 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBFrestoring datafile 00004 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBFrestoring datafile 00005 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2006_11_23\O1_MF_NNNDF_TAG20061123T174153_2PC992LS_.BKP tag=TAG20061123T174153channel ORA_DISK_1: restore completeFinished restore at 23-NOV-06Starting recover at 23-NOV-06using channel ORA_DISK_1starting media recoveryarchive log thread 1 sequence 26 is already on disk as file D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2006_11_23\O1_MF_1_26_2PC9CZF4_.ARCarchive log filename=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2006_11_23\O1_MF_1_26_2PC9CZF4_.ARC thread=1 sequence=26unable to find archive logarchive log thread=1 sequence=27RMAN-00571: ===========================================================
RMAN-00569:
=============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/23/2006 17:53:44RMAN-06054: media recovery requesting unknown log: thread 1 seq 27 lowscn 434810
**************************************
Note : RMAN-06054: media recovery requesting unknown log: thread string seq string lowscn string Cause: Media recovery is requesting a log whose existance is not recorded in the recovery catalog or control file.Action: If a copy of the log is available, then add it to the recovery catalog and/or control file via a CATALOG command and then retry the RECOVER command. If not, then a point in time recovery up to the missing log is the only alternative and database can be opened using ALTER DATABASE OPEN RESETLOGS command.
******************************************************
RMAN> run
2> {
3> SET UNTIL SEQUENCE 27 THREAD 1;
4> RESTORE DATABASE;
5> RECOVER DATABASE;
6> ALTER DATABASE OPEN RESETLOGS;
7> }
executing command: SET until clauseStarting restore at 23-NOV-06using channel ORA_DISK_1channel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBFrestoring datafile 00002 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBFrestoring datafile 00003 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBFrestoring datafile 00004 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBFrestoring datafile 00005 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2006_11_23\O1_MF_NNNDF_TAG20061123T174153_2PC992LS_.BKP tag=TAG20061123T174153channel ORA_DISK_1: restore completeFinished restore at 23-NOV-06Starting recover at 23-NOV-06using channel ORA_DISK_1starting media recoveryarchive log thread 1 sequence 26 is already on disk as file D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2006_11_23\O1_MF_1_26_2PC9CZF4_.ARCarchive log filename=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2006_11_23\O1_MF_1_26_2PC9CZF4_.ARC thread=1 sequence=26media recovery completeFinished recover at 23-NOV-06database opened
*************************************
RMAN> QUIT
Recovery Manager complete.
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> archive log list
Database log mode Archive Mode
Automatic archival EnabledArchive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1
SQL> --take complete consistent bkp of ur database.
SQL> conn / as sysdba
Connected.
SQL> archive log list
Database log mode
Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 23
Next log sequence to archive 25
Current log sequence 25
**********************************************************
SQL> host rman target=sys
Recovery Manager: Release 10.1.0.2.0 - ProductionCopyright (c) 1995, 2004,
Oracle. All rights reserved.
target database
Password:
connected to target database: ORCL (DBID=1134976990)
RMAN> show all;
using target database controlfile instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\SNCFORCL.ORA'; # default
****************************
RMAN> configure controlfile autobackup on;new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
*************************************
RMAN> run
2> {
3> allocate channel c1 device type disk;
4> backup database plus archivelog;
5> }
allocated channel: c1channel c1: sid=200 devtype=DISKStarting backup at 23-NOV-06current log archivedStarting backup at 23-NOV-06current log archivedchannel c1: starting archive log backupsetchannel c1: specifying archive log(s) in backup setinput archive log thread=1 sequence=25 recid=1 stamp=607282901channel c1: starting piece 1 at 23-NOV-06channel c1: finished piece 1 at 23-NOV-06piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2006_11_23\O1_MF_ANNNN_TAG20061123T174141_2PC98Y2P_.BKP comment=NONEchannel c1: backup set complete, elapsed time: 00:00:11
Finished backup at 23-NOV-06Starting backup at 23-NOV-06channel c1: starting full datafile backupsetchannel c1: specifying datafile(s) in backupsetinput datafile fno=00001 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBFinput datafile fno=00003 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBFinput datafile fno=00005 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBFinput datafile fno=00002 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBFinput datafile fno=00004 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBFchannel c1: starting piece 1 at 23-NOV-06channel c1: finished piece 1 at 23-NOV-06piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2006_11_23\O1_MF_NNNDF_TAG20061123T174153_2PC992LS_.BKP comment=NONEchannel c1: backup set complete, elapsed time: 00:01:33Finished backup at 23-NOV-06Starting backup at 23-NOV-06current log archivedchannel c1: starting archive log backupsetchannel c1: specifying archive log(s) in backup setinput archive log thread=1 sequence=26 recid=2 stamp=607283007channel c1: starting piece 1 at 23-NOV-06channel c1: finished piece 1 at 23-NOV-06piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2006_11_23\O1_MF_ANNNN_TAG20061123T174328_2PC9D278_.BKP comment=NONEchannel c1: backup set complete, elapsed time: 00:00:02Finished backup at 23-NOV-06Starting Control File and SPFILE Autobackup at 23-NOV-06piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2006_11_23\O1_MF_S_607283011_2PC9D4X7_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 23-NOV-06released channel: c1
********************************************************
C:\>del d:\oracle\product\10.1.0\oradata\orcl\*.dbf
C:\>del d:\oracle\product\10.1.0\oradata\orcl\*.ctl
C:\>del d:\oracle\product\10.1.0\oradata\orcl\*.log
**********************************************************
C:\>sqlplus "sys as sysdba"
SQL*Plus: Release 10.1.0.2.0 - Production on Thu Nov 23 17:47:56 2006Copyright (c) 1982, 2004,
Oracle. All rights reserved.
Enter password:
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 171966464
bytesFixed Size 787988 bytes
Variable Size 145488364 bytes
Database Buffers 25165824 bytes
Redo Buffers 524288 bytes
ORA-00205: error in identifying controlfile, check alert log for more info
*************************************
SQL> host rman target=sys
Recovery Manager: Release 10.1.0.2.0 - ProductionCopyright (c) 1995, 2004,
Oracle. All rights reserved.
target database Password:connected to target database: orcl (not mounted)
**********************************************
RMAN> restore controlfile from autobackup;
Starting restore at 23-NOV-06using channel ORA_DISK_1recovery area destination: D:\oracle\product\10.1.0\flash_recovery_areadatabase name (or lock name space) used for search: ORCLchannel ORA_DISK_1: autobackup found in the recovery areachannel ORA_DISK_1: autobackup found: D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2006_11_23\O1_MF_S_607283011_2PC9D4X7_.BKPchannel ORA_DISK_1: controlfile restore from autobackup completeoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL01.CTLoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL02.CTLoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL03.CTLFinished restore at 23-NOV-06
****************************************
RMAN> alter database mount;database mountedreleased channel: ORA_DISK_1
****************************************
RMAN> run
2> {
3> restore database;
4> recover database;
5> }
Starting restore at 23-NOV-06Starting implicit crosscheck backup at 23-NOV-06allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=214 devtype=DISKCrosschecked 3 objectsFinished implicit crosscheck backup at 23-NOV-06Starting implicit crosscheck copy at 23-NOV-06using channel ORA_DISK_1Finished implicit crosscheck copy at 23-NOV-06searching for all files in the recovery areacataloging files...cataloging done
List of Cataloged Files
=======================
File Name: D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2006_11_23\O1_MF_S_607283011_2PC9D4X7_.BKPusing channel ORA_DISK_1channel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBFrestoring datafile 00002 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBFrestoring datafile 00003 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBFrestoring datafile 00004 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBFrestoring datafile 00005 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2006_11_23\O1_MF_NNNDF_TAG20061123T174153_2PC992LS_.BKP tag=TAG20061123T174153channel ORA_DISK_1: restore completeFinished restore at 23-NOV-06Starting recover at 23-NOV-06using channel ORA_DISK_1starting media recoveryarchive log thread 1 sequence 26 is already on disk as file D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2006_11_23\O1_MF_1_26_2PC9CZF4_.ARCarchive log filename=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2006_11_23\O1_MF_1_26_2PC9CZF4_.ARC thread=1 sequence=26unable to find archive logarchive log thread=1 sequence=27RMAN-00571: ===========================================================
RMAN-00569:
=============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/23/2006 17:53:44RMAN-06054: media recovery requesting unknown log: thread 1 seq 27 lowscn 434810
**************************************
Note : RMAN-06054: media recovery requesting unknown log: thread string seq string lowscn string Cause: Media recovery is requesting a log whose existance is not recorded in the recovery catalog or control file.Action: If a copy of the log is available, then add it to the recovery catalog and/or control file via a CATALOG command and then retry the RECOVER command. If not, then a point in time recovery up to the missing log is the only alternative and database can be opened using ALTER DATABASE OPEN RESETLOGS command.
******************************************************
RMAN> run
2> {
3> SET UNTIL SEQUENCE 27 THREAD 1;
4> RESTORE DATABASE;
5> RECOVER DATABASE;
6> ALTER DATABASE OPEN RESETLOGS;
7> }
executing command: SET until clauseStarting restore at 23-NOV-06using channel ORA_DISK_1channel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBFrestoring datafile 00002 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBFrestoring datafile 00003 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBFrestoring datafile 00004 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBFrestoring datafile 00005 to D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2006_11_23\O1_MF_NNNDF_TAG20061123T174153_2PC992LS_.BKP tag=TAG20061123T174153channel ORA_DISK_1: restore completeFinished restore at 23-NOV-06Starting recover at 23-NOV-06using channel ORA_DISK_1starting media recoveryarchive log thread 1 sequence 26 is already on disk as file D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2006_11_23\O1_MF_1_26_2PC9CZF4_.ARCarchive log filename=D:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2006_11_23\O1_MF_1_26_2PC9CZF4_.ARC thread=1 sequence=26media recovery completeFinished recover at 23-NOV-06database opened
*************************************
RMAN> QUIT
Recovery Manager complete.
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> archive log list
Database log mode Archive Mode
Automatic archival EnabledArchive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1
SQL> --take complete consistent bkp of ur database.
Change Server Mode from Dedicated to Shared
Change Server Mode from Dedicated to Shared.
How can find in which mode database is running ?
1. If "SHARED_SERVERS" parameter value is "0". then database running on dedicated server mode.
2. select username,status,server from v$session;
Here SERVER column give server mode 1. dedicated or 2. Shared
3. LISTNER.LOG file
04-SEP-2007 18:46:10 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)
We have to configure two parameters for change server mode from Dedicated to Shared.
SHARED_SERVERS & DISPATCHERS parameters.
------------------------------------------------------------
SQL>show parameter shared_servers
NAME VALUE TYPE
----------------------------------------------
shared_servers integer 0
SQL> alter system set shared_servers = 1;
System altered.
SQL>show parameter shared_servers
NAME VALUE TYPE
----------------------------------------------
shared_servers integer 1
SQL>show parameter dispatchers
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------dispatchers string (PROTOCOL=TCP)
max_dispatchers integer
SQL> alter system set dispatchers = "(PROTOCOL=TCP) (DISPATCHERS=1) (SERVICE=ORCL1)";
System altered.
or you can edit tnsnames.ora file like
ORCL1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.64)(PORT = 1521)) (CONNECT_DATA =
(SERVICE_NAME = orcl1)
(SERVER=SHARED)
)
)
Note : For Backward compatibility, if shared_server is not included in init parameter file or dispatchers is included and it specify aleast 1 dispatcher. then shared_server mode is enable.
but if not shared_server or nor dispatcher specified in init parameter file . then you have to set shared_server parameter aleast greater than 0 for enable shared server mode.
If We set only shared_server parameter but dispatchers parameters is not set then oracle server automatically create one dispatcher for shared server.
dispatchers string (PROTOCOL=TCP)
How can find in which mode database is running ?
1. If "SHARED_SERVERS" parameter value is "0". then database running on dedicated server mode.
2. select username,status,server from v$session;
Here SERVER column give server mode 1. dedicated or 2. Shared
3. LISTNER.LOG file
04-SEP-2007 18:46:10 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)
We have to configure two parameters for change server mode from Dedicated to Shared.
SHARED_SERVERS & DISPATCHERS parameters.
------------------------------------------------------------
SQL>show parameter shared_servers
NAME VALUE TYPE
----------------------------------------------
shared_servers integer 0
SQL> alter system set shared_servers = 1;
System altered.
SQL>show parameter shared_servers
NAME VALUE TYPE
----------------------------------------------
shared_servers integer 1
SQL>show parameter dispatchers
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------dispatchers string (PROTOCOL=TCP)
max_dispatchers integer
SQL> alter system set dispatchers = "(PROTOCOL=TCP) (DISPATCHERS=1) (SERVICE=ORCL1)";
System altered.
or you can edit tnsnames.ora file like
ORCL1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.64)(PORT = 1521)) (CONNECT_DATA =
(SERVICE_NAME = orcl1)
(SERVER=SHARED)
)
)
Note : For Backward compatibility, if shared_server is not included in init parameter file or dispatchers is included and it specify aleast 1 dispatcher. then shared_server mode is enable.
but if not shared_server or nor dispatcher specified in init parameter file . then you have to set shared_server parameter aleast greater than 0 for enable shared server mode.
If We set only shared_server parameter but dispatchers parameters is not set then oracle server automatically create one dispatcher for shared server.
dispatchers string (PROTOCOL=TCP)
Interview Questions
Database Security Interview Questions
What are Roles ?www.nirudhyog.com
What is Statement Auditing ?
How can we implement roles ?
What is a profile ?www.nirudhyog.com
What is Tablespace Quota ?
What is Object Auditing ?
What is default tablespace ?
What are the responsibilities of a nirudhyog
What are the roles and user accounts created
What is a trace file and how is it created ?
What are the nirudhyog administrators utilitieswww.nirudhyog.com
What are the dictionary tables used to monitor
What are the use of Roles ?
What is the use of ANALYZE command ?
How can we specify the Archived log file namewww.nirudhyog.com
What is Privilege Auditing ?
What are the different Levels of Auditing ?
What are the minimum parameters shouldwww.nirudhyog.com
What is Auditing ?
What are the system resources that can be controlled
What are Roles ?www.nirudhyog.com
What is Statement Auditing ?
How can we implement roles ?
What is a profile ?www.nirudhyog.com
What is Tablespace Quota ?
What is Object Auditing ?
What is default tablespace ?
What are the responsibilities of a nirudhyog
What are the roles and user accounts created
What is a trace file and how is it created ?
What are the nirudhyog administrators utilitieswww.nirudhyog.com
What are the dictionary tables used to monitor
What are the use of Roles ?
What is the use of ANALYZE command ?
How can we specify the Archived log file namewww.nirudhyog.com
What is Privilege Auditing ?
What are the different Levels of Auditing ?
What are the minimum parameters shouldwww.nirudhyog.com
What is Auditing ?
What are the system resources that can be controlled
Default Tablespace in Oracle.
Default Tablespace in Oracle.
•A default tablespace in oracle is the tablespace which will be used as default tablespace whenever a new user is created that user is implicitly assigned with that tablespace. So if that new user create any objects the objects will be by default created in his default tablespace.
•If default tablespace is not specified when creating a database or when created schema/schema objects, then the SYSTEM tablespace is the default permanent tablespace for non-SYSTEM users.
•A default tablespace can be specified one of two ways at the database level:
1) during database creation via the CREATE DATABASE command
or
2) after database creation via the ALTER DATABASE command.
•The default tablespace can only be locally managed.
•In order to see the database default tablespace issue,
SQL> SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE property_name = 'DEFAULT_PERMANENT_ TABLESPACE' ;
PROPERTY_VALUE
------------ --------- --------- --------- --------- --------- --------- --------- -----
USERS
•In order to see a particular user default tablespace use,
SQL> SELECT DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME='ARJU' ;
DEFAULT_TABLESPACE
------------ --------- ---------
USER_TBS
•A default tablespace in oracle is the tablespace which will be used as default tablespace whenever a new user is created that user is implicitly assigned with that tablespace. So if that new user create any objects the objects will be by default created in his default tablespace.
•If default tablespace is not specified when creating a database or when created schema/schema objects, then the SYSTEM tablespace is the default permanent tablespace for non-SYSTEM users.
•A default tablespace can be specified one of two ways at the database level:
1) during database creation via the CREATE DATABASE command
or
2) after database creation via the ALTER DATABASE command.
•The default tablespace can only be locally managed.
•In order to see the database default tablespace issue,
SQL> SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE property_name = 'DEFAULT_PERMANENT_ TABLESPACE' ;
PROPERTY_VALUE
------------ --------- --------- --------- --------- --------- --------- --------- -----
USERS
•In order to see a particular user default tablespace use,
SQL> SELECT DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME='ARJU' ;
DEFAULT_TABLESPACE
------------ --------- ---------
USER_TBS
Monday, May 19, 2008
Database & Patch set Upgrade
Database & Patch set Upgrade
Database & patch upgrade is one of the most important work for Database Administrator.
Before going further first understand what are “upgrade” & “migration” & “patch set” & “CPU patch” word.
Upgrade:
Usually use for oracle database upgrade.
Eg: upgrade from oracle 9i to oracle 10g.
Migration:
Usually use for migrate non oracle database to oracle database.
Eg: From SQL server database to oracle database
Patch set:
There are two types of patch upgrade
1. Patch set update
From one oracle version to another oracle version
Eg: oracle 9i to oracle 10g
NOTE: Patch set based on oracle base release
Eg: oracle 10gr1 (10.1.0.2.0) and for 10gr2 (10.2.0.1.0)
2. CPU patch (Critical Patch update)
Patches is apply for fix database bug suppose after apply latest patch set for current release if there is any bug occur then oracle release cpu patches in regular interval to fix those bug.
Eg: oracle 10gr1 base rel: 10.1.0.2.0, Latest Patch set: 10.1.0.5.0, Latest CPU patch: Jan-08 CPU patch for 10.1.0.5.0
NOTE: CPU patch based on latest patch set.
For more info: http://www.oracle.com/technology/deploy/security/alerts.htm
How to upgrade database to oracle 10gr2?
Determine the Upgrade Path to the New Oracle Database 10g Release
http://download.oracle.com/docs/cd/B19306_01/server.102/b14238/preup.htm#i1007814
Before upgrading process we need to define upgrade method
There are four methods to upgrade our database
1. DBUA (Database upgrade assistant)
2. Manually upgrade
3. Export/Import
4. Data copying
http://download.oracle.com/docs/cd/B19306_01/server.102/b14238/preup.htm#i694345
Patch set upgrade example:
1. From oracle 9ir2 (9.2.0.2 to 9.2.0.8) on Windows http://babudba.blogspot.com/2007/11/patch-installation.html
2. From oracle 9ir2 (9.2.0.1 to 9.2.0.7) on Solaris
http://sabdarsyed.blogspot.com/2007/02/upgrade-oracle-database-9201-to-9207-on.html
3. From oracle 10gr1 (10.1.0.2 to 10.1.0.5) on windows
http://dbataj.blogspot.com/2007/06/upgrading-database-from-101020-to.html
4. From oracle 10gr2 (10.2.0.2 to 10.2.0.3) on windows
http://babudba.blogspot.com/2007/11/upgrade-oracle-10202-to-10203.html
5. From oracle 10gr2 (10.2.0.1 to 10.2.0.3) on Linux
http://sabdarsyed.blogspot.com/2007/03/upgrading-oracle-database-server-10g.html
Critical Patch update example:
On Oracle 10gr1 (10.1.0.5.0) patch set
http://dbataj.blogspot.com/2007/06/critical-patch-update.html
Database & patch upgrade is one of the most important work for Database Administrator.
Before going further first understand what are “upgrade” & “migration” & “patch set” & “CPU patch” word.
Upgrade:
Usually use for oracle database upgrade.
Eg: upgrade from oracle 9i to oracle 10g.
Migration:
Usually use for migrate non oracle database to oracle database.
Eg: From SQL server database to oracle database
Patch set:
There are two types of patch upgrade
1. Patch set update
From one oracle version to another oracle version
Eg: oracle 9i to oracle 10g
NOTE: Patch set based on oracle base release
Eg: oracle 10gr1 (10.1.0.2.0) and for 10gr2 (10.2.0.1.0)
2. CPU patch (Critical Patch update)
Patches is apply for fix database bug suppose after apply latest patch set for current release if there is any bug occur then oracle release cpu patches in regular interval to fix those bug.
Eg: oracle 10gr1 base rel: 10.1.0.2.0, Latest Patch set: 10.1.0.5.0, Latest CPU patch: Jan-08 CPU patch for 10.1.0.5.0
NOTE: CPU patch based on latest patch set.
For more info: http://www.oracle.com/technology/deploy/security/alerts.htm
How to upgrade database to oracle 10gr2?
Determine the Upgrade Path to the New Oracle Database 10g Release
http://download.oracle.com/docs/cd/B19306_01/server.102/b14238/preup.htm#i1007814
Before upgrading process we need to define upgrade method
There are four methods to upgrade our database
1. DBUA (Database upgrade assistant)
2. Manually upgrade
3. Export/Import
4. Data copying
http://download.oracle.com/docs/cd/B19306_01/server.102/b14238/preup.htm#i694345
Patch set upgrade example:
1. From oracle 9ir2 (9.2.0.2 to 9.2.0.8) on Windows http://babudba.blogspot.com/2007/11/patch-installation.html
2. From oracle 9ir2 (9.2.0.1 to 9.2.0.7) on Solaris
http://sabdarsyed.blogspot.com/2007/02/upgrade-oracle-database-9201-to-9207-on.html
3. From oracle 10gr1 (10.1.0.2 to 10.1.0.5) on windows
http://dbataj.blogspot.com/2007/06/upgrading-database-from-101020-to.html
4. From oracle 10gr2 (10.2.0.2 to 10.2.0.3) on windows
http://babudba.blogspot.com/2007/11/upgrade-oracle-10202-to-10203.html
5. From oracle 10gr2 (10.2.0.1 to 10.2.0.3) on Linux
http://sabdarsyed.blogspot.com/2007/03/upgrading-oracle-database-server-10g.html
Critical Patch update example:
On Oracle 10gr1 (10.1.0.5.0) patch set
http://dbataj.blogspot.com/2007/06/critical-patch-update.html
Efficient Tips for Renaming Oracle Instance!!.
Efficient Tips for Renaming Oracle Instance!!.
Today we will discuss some efficient tips for renaming Oracle Instance.
Finding ORACLE_SID of Oracle Database:
There are a number of options available for finding ORACLE_SID for an Oracle database.
• You can find the current values of ORACLE_SID by querying Oracle data dictionary V$THREAD view.
• As ORACLE_SID is the instance name so you can look into init.ora file for instance_name parameter.
• It may be or may not be possible that your ORACLE_SID name is the same as your database name. Therefore you can check init.ora for db_name parameter.
• You can check /etc/oratab file for a list of instances on the server. Depending on your Oracle version this can be /var/opt/oracle/oratab
Changing ORACLE_SID after Creating Database:
In order to change ORACLE_SID after creating database you will have to recreate the Control file.
Generate Trace file for Control File Creation Script:
First of all you will need to generate an ASCII Trace file in $USER_DUMP_DEST directory. This trace file will contain the control file creation script.
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
Shutdown Database:
Now shutdown the database.
Backup:
Now backup all datafiles, controlfiles, redolog files, archived redo log files etc.
Rename files:
Now you will need to rename the Init.ora and config.ora to Init.ora and Config.ora files in $ORACLE_HOME/DBS. Simialrly you will need to rename the old controlfiles (control01.old to control01.new).
Edit the Control File creation Script:
Now you will have to edit the Control File creation Script as follows.
STARTUP NOMOUNT;
CREATE CONTROLFILE SET DATABASE 'NEW_SID' RESETLOGS
;
Open Database:
Finally you will have to open your database with RESETLOGS.
ALTER DATABASE OPEN RESETLOGS;
Changing ORACLE_SID without recreating Database:
Shutdown Instance:
First of all shutdown the instance.
Backup Files:
Now backup all datafiles, controlfiles, redolog files, archived redo log files etc.
Re-define ORACLE_SID:
Now re-define the ORACLE_SID variable so that it now has a new value.
Change Locations:
Now change the locations to the DBS directory and rename the files init.ora file (Init.ora to Init.ora), control files (control01.old to control01.new), crdb.sql, crdb2.sql. If you have renamed old control files then make sure that the control_files parameter does not point to old file names. Similarly you will need to change the locations to RDBMS/ADMIN directory and rename the startup.sql file. Although this is optional but you must ensure that the contents of this file do not reference old init.ora files that have been renamed.
Rename Files:
Now you will need to rename the database files and redo log files
Change ORACLE_SID value:
Now change the ORACLE_SID to new value.
Startup Database:
If password file is enabled in ORACLE_HOME/DBS and ORAPW file exists then you will need to first issue ORAPWD FILE=ORAPW PASSWORD=?? ENTRIES= to create a new SID. Otherwise you can just start up the database and verify that it works.
Backup:
Finally you will have to shutdown the database and take backup of all control files, redo files and data files.
Start Instance:
Now when your instance is started, you will find your control file updated with the current ORACLE_SID.
Oracle DBNEWID Utility:
You can also make use of DBNEWID utility in Oracle versions 9i and beyond for this purpose.
Backup:
First of all you will have to backup the database.
Shutdown:
Now shutdown your database.
SHUTDOWN IMMEDIATE
Mount:
Now you will have to mount the database.
STARTUP MOUNT
Invoke the DBNEWID Utility:
Now you will have to invoke the DBNEWID utility (nid) specifying the new DBNAME.
NID TARGET=SYS/PASSWORD@TSH1 DBNAME=TSH2
Assuming the validation is successful the utility prompts for confirmation before performing the actions.
Shutdown:
Now again shutdown your database.
SHUTDOWN IMMEDIATE
Modify DB_NAME Parameter:
Now you will have to modify DB_NAME parameter in initialization parameter file. If will give an error but proceed with it.
STARTUP MOUNT
ALTER SYSTEM SET DB_NAME=TSH2 SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
Creating Password File:
Now we will have to create a new password file.
ORAPWD FILE=C:\ORACLE\920\DATABASE\PWDTSH2.ORA PASSWORD=PASSWORD ENTRIES=10
Rename SPFILE:
Now you will have to rename the SPFILE. Make sure that it matches the new DBNAME.
Re-create Services:
On Windows environment you will have to recreate the service so as to make sure that it uses correct name and parameter file.
ORADIM -DELETE -SID TSH1
ORADIM -NEW -SID TSH2 -INTPWD PASSWORD -STARTMODE A -PFILE C:\ORACLE\920\DATABASE\SPFILETSH2.ORA
In UNIX/Linux environment you will have to just reset ORACLE_SID environment variable.
ORACLE_SID=TSH2;
export ORACLE_SID
Alter .ora File Setting:
Now you will have to alter the listener.ora and tnsnames.ora setting so that it matches the new database name.
Restart Listener:
Now restart the listener:
LSNRCTL RELOAD
Open Database:
Now you will have to open your database with RESETLOGS.
STARTUP MOUNT
ALTER DATABASE OPEN RESETLOGS;
Backup:
Finally you will have to backup your database.
Today we will discuss some efficient tips for renaming Oracle Instance.
Finding ORACLE_SID of Oracle Database:
There are a number of options available for finding ORACLE_SID for an Oracle database.
• You can find the current values of ORACLE_SID by querying Oracle data dictionary V$THREAD view.
• As ORACLE_SID is the instance name so you can look into init.ora file for instance_name parameter.
• It may be or may not be possible that your ORACLE_SID name is the same as your database name. Therefore you can check init.ora for db_name parameter.
• You can check /etc/oratab file for a list of instances on the server. Depending on your Oracle version this can be /var/opt/oracle/oratab
Changing ORACLE_SID after Creating Database:
In order to change ORACLE_SID after creating database you will have to recreate the Control file.
Generate Trace file for Control File Creation Script:
First of all you will need to generate an ASCII Trace file in $USER_DUMP_DEST directory. This trace file will contain the control file creation script.
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
Shutdown Database:
Now shutdown the database.
Backup:
Now backup all datafiles, controlfiles, redolog files, archived redo log files etc.
Rename files:
Now you will need to rename the Init
Edit the Control File creation Script:
Now you will have to edit the Control File creation Script as follows.
STARTUP NOMOUNT;
CREATE CONTROLFILE SET DATABASE 'NEW_SID' RESETLOGS
;
Open Database:
Finally you will have to open your database with RESETLOGS.
ALTER DATABASE OPEN RESETLOGS;
Changing ORACLE_SID without recreating Database:
Shutdown Instance:
First of all shutdown the instance.
Backup Files:
Now backup all datafiles, controlfiles, redolog files, archived redo log files etc.
Re-define ORACLE_SID:
Now re-define the ORACLE_SID variable so that it now has a new value.
Change Locations:
Now change the locations to the DBS directory and rename the files init.ora file (Init
Rename Files:
Now you will need to rename the database files and redo log files
Change ORACLE_SID value:
Now change the ORACLE_SID to new value.
Startup Database:
If password file is enabled in ORACLE_HOME/DBS and ORAPW file exists then you will need to first issue ORAPWD FILE=ORAPW PASSWORD=?? ENTRIES= to create a new SID. Otherwise you can just start up the database and verify that it works.
Backup:
Finally you will have to shutdown the database and take backup of all control files, redo files and data files.
Start Instance:
Now when your instance is started, you will find your control file updated with the current ORACLE_SID.
Oracle DBNEWID Utility:
You can also make use of DBNEWID utility in Oracle versions 9i and beyond for this purpose.
Backup:
First of all you will have to backup the database.
Shutdown:
Now shutdown your database.
SHUTDOWN IMMEDIATE
Mount:
Now you will have to mount the database.
STARTUP MOUNT
Invoke the DBNEWID Utility:
Now you will have to invoke the DBNEWID utility (nid) specifying the new DBNAME.
NID TARGET=SYS/PASSWORD@TSH1 DBNAME=TSH2
Assuming the validation is successful the utility prompts for confirmation before performing the actions.
Shutdown:
Now again shutdown your database.
SHUTDOWN IMMEDIATE
Modify DB_NAME Parameter:
Now you will have to modify DB_NAME parameter in initialization parameter file. If will give an error but proceed with it.
STARTUP MOUNT
ALTER SYSTEM SET DB_NAME=TSH2 SCOPE=SPFILE;
SHUTDOWN IMMEDIATE
Creating Password File:
Now we will have to create a new password file.
ORAPWD FILE=C:\ORACLE\920\DATABASE\PWDTSH2.ORA PASSWORD=PASSWORD ENTRIES=10
Rename SPFILE:
Now you will have to rename the SPFILE. Make sure that it matches the new DBNAME.
Re-create Services:
On Windows environment you will have to recreate the service so as to make sure that it uses correct name and parameter file.
ORADIM -DELETE -SID TSH1
ORADIM -NEW -SID TSH2 -INTPWD PASSWORD -STARTMODE A -PFILE C:\ORACLE\920\DATABASE\SPFILETSH2.ORA
In UNIX/Linux environment you will have to just reset ORACLE_SID environment variable.
ORACLE_SID=TSH2;
export ORACLE_SID
Alter .ora File Setting:
Now you will have to alter the listener.ora and tnsnames.ora setting so that it matches the new database name.
Restart Listener:
Now restart the listener:
LSNRCTL RELOAD
Open Database:
Now you will have to open your database with RESETLOGS.
STARTUP MOUNT
ALTER DATABASE OPEN RESETLOGS;
Backup:
Finally you will have to backup your database.
ASM Command-Line Utility (ASMCMD)
ASM Command-Line Utility (ASMCMD)
ASMCMD is a comman-line utility that helps us to view,manage,manipulate,perform search,display space utilization of files and directories within ASM disk groups.
Can ASMCMD be used to manipulate the disks in a disk group?
No. We can't use ASMCMD to manipulate disks in a disk group. Manipulation includes operations such as addition and deletion of disks in a disk group/creating and dropping of disk groups. We can use SQL commands for that purpose.
What is a system-generated filename?
A system-generated file name is also called as fully qualified filename.
Every file created in ASM gets this system-generated name.
This is same as the complete path name in normal file system.(say $ORACLE_HOME=/u01/app/product/oracle/...)
Does fully qualified filename/system generated filename follow any standard?
Yes.Fully qualified filename follows the following standard:
+diskGroupName/databaseName/fileType/fileTypeTag.fileNumber.incarnation
Example:
+dgroup6/sample/CONTROLFILE/Current.256.541952343
In this example,
dgroup6 - diskgroup
sample - database
CONTROLFILE - type of file
current - file type tag
256 - file number
541952343 - incarnation
ASMCMD supports only forward slash(\).
Filenames are not case sensitive.
What is an ASM directory?
An ASM directory is a container of files.It is same as directories in file system.An ASM directory is autogenerated.A plus sign(+) represents a root directory.A fully qualifiedname or a system-generated name represents a hierarchy of directories.
An ASM automatically generates directories for files in fully qualified name and these directories are called as system-generated directories.
Following ASM commands can be used to manage these directories:
cd - move up and down between these directories
pwd - prints the name of the current directory
ls - lists the contents of the current directory
mkdir - We can create our own directories as sub-directories of system-generated directories(Note:ALTER DISKGROUP command can be used for the same purpose)
mkalias - create file alias
An ASM directory can be a part of the tree structure of other directories.
What is the use of -p option of ASMCMD?
We can start ASMCMD with -p option(ASMCMD -p) to include current directory as a part of ASMCMD prompt.
What is an alias?
Aliases are filenames that are pointers or references to system-generated filenames.They are user-friendly and are same as symbolic names in UNIX/Linux.They are used to simplify filename administration.Following commands can be used to create aliases:
mkalias,ALTER DISKGROUP with ADD ALIAS clause :
Syntax:
ALTER DISKGROUP dgroup1 ADD ALIAS '(aliasname)'
FOR '(system-generated filename of file)';
We can create alias:
1) At system-generated subdirectory
2) At user-generated subdirectory
3) At disk group level
An alias name has at minimum diskgroup as a part of its filename.
To get the system-generated file name and its alias use the following ASMCMD command:
ls -l
What is the difference between absolute path and relative path?
An absolute path is the system-generated filename that starts from the name of a diskgroup(+).A relative pathname specifies path relative to present working directory.Say a user is in diskgroup(+dgroup1),the relative path can be sample/CTLFILE/CURRENT.123.2345678.
What is a pseudo-directory?
A pseudo-directory is a name that can be specified along with the cd command. They are:
. - current working directory (single-dot)
.. - parent directory of the current directory(two dots without space)
What is a wildcard character?
The characters '*' and '%' are referred to as wildcard characters that match zero or more characters within an absolute or relative path ,which saves typing of full directory or filename.Both these characters behave identically.ASMCMD commands that accept wildcard characters are cd, du, find, ls, lsdsk, and rm.
Running ASMCMD Utility
ASMCMD utility can be run in interactive and non-interactive mode. We need certain preparations before running ASMCMD utility.
Preparation before running ASMCMD utility :
Check the following before running the ASMCMD utility.
1) Check that ASM instance is started and disk groups are mounted.
2) Loginto host in which ASM instance is running.We must login as an user with SYSASM or SYSDBA system privileges through Operating system(OS) authentication.SYSASM is the preferred connection privilege.
3) Set the ORACLE_HOME and ORACLE_SID variables to refer to the ASM instance.
4) Set the operating system variables correctly to connect to the ASM instance.
5) The default value of ASM SID in a single instance database is +ASM.In case of a oracle RAC environment the value of ASM SID is +ASMnode#
6) Ensure that PATH variable is set properly.Issue echo $PATH command to check if bin directory of the ORACLE_HOME is in $PATH.
Running ASMCMD in Interactive-mode :
The interactive mode of the ASMCMD utility provides a shell-like environment where we are prompted to enter ASMCMD commands.
1. Enter the following at the operating system command prompt:
asmcmd
Oracle displays an ASMCMD command prompt as follows:
ASMCMD>
2. Enter an ASMCMD command and press Enter. The command runs and displays its output, if any, and then ASMCMD prompts for the next command.
3. Continue entering ASMCMD commands. Enter the command exit to exit ASMCMD.
We can specify the -v option when starting asmcmd to displays the asmcmd version number. After displaying the version number, asmcmd immediately exits.
$ asmcmd -v
asmcmd version 11.1.0.3.0
We can specify the -a option to choose the type of connection, either SYSASM or SYSDBA. The default value is SYSASM.
$ asmcmd -a sysasm
Running ASMCMD in Non-interactive Mode:
This mode is useful is automating the process using scripts. The format is asmcmd (command) (option),where command is the ASMCMD command to be run and option is the option with which the command has to be run.
For example asmcmd ls -l invokes asmcmd in non-interactive mode and lists the files in current directory along with its alias.
ASMCMD is a comman-line utility that helps us to view,manage,manipulate,perform search,display space utilization of files and directories within ASM disk groups.
Can ASMCMD be used to manipulate the disks in a disk group?
No. We can't use ASMCMD to manipulate disks in a disk group. Manipulation includes operations such as addition and deletion of disks in a disk group/creating and dropping of disk groups. We can use SQL commands for that purpose.
What is a system-generated filename?
A system-generated file name is also called as fully qualified filename.
Every file created in ASM gets this system-generated name.
This is same as the complete path name in normal file system.(say $ORACLE_HOME=/u01/app/product/oracle/...)
Does fully qualified filename/system generated filename follow any standard?
Yes.Fully qualified filename follows the following standard:
+diskGroupName/databaseName/fileType/fileTypeTag.fileNumber.incarnation
Example:
+dgroup6/sample/CONTROLFILE/Current.256.541952343
In this example,
dgroup6 - diskgroup
sample - database
CONTROLFILE - type of file
current - file type tag
256 - file number
541952343 - incarnation
ASMCMD supports only forward slash(\).
Filenames are not case sensitive.
What is an ASM directory?
An ASM directory is a container of files.It is same as directories in file system.An ASM directory is autogenerated.A plus sign(+) represents a root directory.A fully qualifiedname or a system-generated name represents a hierarchy of directories.
An ASM automatically generates directories for files in fully qualified name and these directories are called as system-generated directories.
Following ASM commands can be used to manage these directories:
cd - move up and down between these directories
pwd - prints the name of the current directory
ls - lists the contents of the current directory
mkdir - We can create our own directories as sub-directories of system-generated directories(Note:ALTER DISKGROUP command can be used for the same purpose)
mkalias - create file alias
An ASM directory can be a part of the tree structure of other directories.
What is the use of -p option of ASMCMD?
We can start ASMCMD with -p option(ASMCMD -p) to include current directory as a part of ASMCMD prompt.
What is an alias?
Aliases are filenames that are pointers or references to system-generated filenames.They are user-friendly and are same as symbolic names in UNIX/Linux.They are used to simplify filename administration.Following commands can be used to create aliases:
mkalias,ALTER DISKGROUP with ADD ALIAS clause :
Syntax:
ALTER DISKGROUP dgroup1 ADD ALIAS '(aliasname)'
FOR '(system-generated filename of file)';
We can create alias:
1) At system-generated subdirectory
2) At user-generated subdirectory
3) At disk group level
An alias name has at minimum diskgroup as a part of its filename.
To get the system-generated file name and its alias use the following ASMCMD command:
ls -l
What is the difference between absolute path and relative path?
An absolute path is the system-generated filename that starts from the name of a diskgroup(+).A relative pathname specifies path relative to present working directory.Say a user is in diskgroup(+dgroup1),the relative path can be sample/CTLFILE/CURRENT.123.2345678.
What is a pseudo-directory?
A pseudo-directory is a name that can be specified along with the cd command. They are:
. - current working directory (single-dot)
.. - parent directory of the current directory(two dots without space)
What is a wildcard character?
The characters '*' and '%' are referred to as wildcard characters that match zero or more characters within an absolute or relative path ,which saves typing of full directory or filename.Both these characters behave identically.ASMCMD commands that accept wildcard characters are cd, du, find, ls, lsdsk, and rm.
Running ASMCMD Utility
ASMCMD utility can be run in interactive and non-interactive mode. We need certain preparations before running ASMCMD utility.
Preparation before running ASMCMD utility :
Check the following before running the ASMCMD utility.
1) Check that ASM instance is started and disk groups are mounted.
2) Loginto host in which ASM instance is running.We must login as an user with SYSASM or SYSDBA system privileges through Operating system(OS) authentication.SYSASM is the preferred connection privilege.
3) Set the ORACLE_HOME and ORACLE_SID variables to refer to the ASM instance.
4) Set the operating system variables correctly to connect to the ASM instance.
5) The default value of ASM SID in a single instance database is +ASM.In case of a oracle RAC environment the value of ASM SID is +ASMnode#
6) Ensure that PATH variable is set properly.Issue echo $PATH command to check if bin directory of the ORACLE_HOME is in $PATH.
Running ASMCMD in Interactive-mode :
The interactive mode of the ASMCMD utility provides a shell-like environment where we are prompted to enter ASMCMD commands.
1. Enter the following at the operating system command prompt:
asmcmd
Oracle displays an ASMCMD command prompt as follows:
ASMCMD>
2. Enter an ASMCMD command and press Enter. The command runs and displays its output, if any, and then ASMCMD prompts for the next command.
3. Continue entering ASMCMD commands. Enter the command exit to exit ASMCMD.
We can specify the -v option when starting asmcmd to displays the asmcmd version number. After displaying the version number, asmcmd immediately exits.
$ asmcmd -v
asmcmd version 11.1.0.3.0
We can specify the -a option to choose the type of connection, either SYSASM or SYSDBA. The default value is SYSASM.
$ asmcmd -a sysasm
Running ASMCMD in Non-interactive Mode:
This mode is useful is automating the process using scripts. The format is asmcmd (command) (option),where command is the ASMCMD command to be run and option is the option with which the command has to be run.
For example asmcmd ls -l invokes asmcmd in non-interactive mode and lists the files in current directory along with its alias.
ASM Disk Discovery
ASM Disk Discovery
Disk discovery is the mechanism used to find the operating system names for disks ASM can access. It is used to find all the disks that comprise a disk group to be mounted, the disks an administrator wants to add to a disk group, or the disks the administrator might consider adding to a disk group.How A Disk is Discovered :While an ASM instance is initialized, ASM discovers and examines the contents of all of the disks that are in the paths that we designated with values in the ASM_DISKSTRING initialization parameter.Disk discovery also occurs when:1) Run the ALTER DISKGROUP...ADD DISK and ALTER DISKGROUP...RESIZE DISK commands2) Query the V$ASM_DISKGROUP and V$ASM_DISK views :After ASM successfully discovers a disk, the disk appears in the V$ASM_DISK view.Disks that belong to a disk group, that is, disks that have a disk group name in the disk header, show a status of MEMBER. Disks that were discovered, but that have not yet been assigned to a disk group, have a status of either CANDIDATE or PROVISIONED.The PROVISIONED status implies that an additional platform-specific action has been taken by an administrator to make the disk available for ASM. For example, on Windows computers, the administrator might have used asmtool or asmtoolg to stamp the disk with a header. On Linux computers, the administrator might have used ASMLIB to prepare the disk for ASM.The following SQL query shows one candidate and six member disks:SQL>SELECT name, header_status, path FROM V$ASM_DISK;NAME HEADER_STATUS PATH--------- ------------- ---------------------CANDIDATE /dev/rdsk/disk07DISK06 MEMBER /dev/rdsk/disk06DISK05 MEMBER /dev/rdsk/disk05DISK04 MEMBER /dev/rdsk/disk04DISK03 MEMBER /dev/rdsk/disk03DISK02 MEMBER /dev/rdsk/disk02DISK01 MEMBER /dev/rdsk/disk017 rows selected.Disk Discovery Rules :The rules for discovering ASM disks are as follows:1) ASM can discover up to 10,000 disks. That is, if more than 10,000 disks match the ASM_DISKSTRING initialization parameter, then ASM discovers only the first 10,000.2) ASM only discovers disks that contain a partition table.ASM does not discover a disk that contains an operatingsystem partition table, even if the disk is in an ASM disk string search path and ASM has read and write permission for the disk.3) When adding a disk, the FORCE option must be used if ASM recognizes that the disk was managed by Oracle. Such a disk appears in the V$ASM_DISK view with a status of FOREIGN. In this case, we can only add the disk to a disk group by using the FORCE keyword.In addition, ASM identifies the following configuration errors during discovery:1) Multiple paths to the same diskIn this case, if the disk is part of a disk group, then disk group mount fails. If the disk is being added to a disk group with the ADD DISK or CREATE DISKGROUP command, then the command fails. To correct the error, adjust theASM_DISKSTRING value so that ASM will not discover multiple paths to the same disk. Or if we are using multipathing software, then ensure that we include only the pseudo-device name in the ASM_DISKSTRING value.2) Multiple ASM disks with the same disk headerThis can be caused by having copied one disk onto another. In this case, the disk group mount operation fails.Improving Disk Discovery Time :The value for the ASM_DISKSTRING initialization parameter is an operating system–dependent value that ASM uses to limit the set of paths that the discovery process uses to search for disks.When a new disk is added to a disk group, each ASM instance that has the disk group mounted must be able to discover the new disk using its ASM_DISKSTRING.In many cases, the default value (NULL) is sufficient. Using a more restrictive value might reduce the time required for ASM to perform discovery, and thus improve disk group mount time or the time for adding a disk to a disk group.Oracle might need to dynamically change the ASM_DISKSTRING before adding a disk so that the new disk will be discovered through this parameter.The default value of ASM_DISKSTRING might not find all disks in all situations. If our site is using a third-party vendor ASMLIB, then the vendor might have discovery string conventions that we must use for ASM_DISKSTRING.If our installation uses multipathing software, then the software might place pseudo-devices in a path that is different from the operating system default.
Disk discovery is the mechanism used to find the operating system names for disks ASM can access. It is used to find all the disks that comprise a disk group to be mounted, the disks an administrator wants to add to a disk group, or the disks the administrator might consider adding to a disk group.How A Disk is Discovered :While an ASM instance is initialized, ASM discovers and examines the contents of all of the disks that are in the paths that we designated with values in the ASM_DISKSTRING initialization parameter.Disk discovery also occurs when:1) Run the ALTER DISKGROUP...ADD DISK and ALTER DISKGROUP...RESIZE DISK commands2) Query the V$ASM_DISKGROUP and V$ASM_DISK views :After ASM successfully discovers a disk, the disk appears in the V$ASM_DISK view.Disks that belong to a disk group, that is, disks that have a disk group name in the disk header, show a status of MEMBER. Disks that were discovered, but that have not yet been assigned to a disk group, have a status of either CANDIDATE or PROVISIONED.The PROVISIONED status implies that an additional platform-specific action has been taken by an administrator to make the disk available for ASM. For example, on Windows computers, the administrator might have used asmtool or asmtoolg to stamp the disk with a header. On Linux computers, the administrator might have used ASMLIB to prepare the disk for ASM.The following SQL query shows one candidate and six member disks:SQL>SELECT name, header_status, path FROM V$ASM_DISK;NAME HEADER_STATUS PATH--------- ------------- ---------------------CANDIDATE /dev/rdsk/disk07DISK06 MEMBER /dev/rdsk/disk06DISK05 MEMBER /dev/rdsk/disk05DISK04 MEMBER /dev/rdsk/disk04DISK03 MEMBER /dev/rdsk/disk03DISK02 MEMBER /dev/rdsk/disk02DISK01 MEMBER /dev/rdsk/disk017 rows selected.Disk Discovery Rules :The rules for discovering ASM disks are as follows:1) ASM can discover up to 10,000 disks. That is, if more than 10,000 disks match the ASM_DISKSTRING initialization parameter, then ASM discovers only the first 10,000.2) ASM only discovers disks that contain a partition table.ASM does not discover a disk that contains an operatingsystem partition table, even if the disk is in an ASM disk string search path and ASM has read and write permission for the disk.3) When adding a disk, the FORCE option must be used if ASM recognizes that the disk was managed by Oracle. Such a disk appears in the V$ASM_DISK view with a status of FOREIGN. In this case, we can only add the disk to a disk group by using the FORCE keyword.In addition, ASM identifies the following configuration errors during discovery:1) Multiple paths to the same diskIn this case, if the disk is part of a disk group, then disk group mount fails. If the disk is being added to a disk group with the ADD DISK or CREATE DISKGROUP command, then the command fails. To correct the error, adjust theASM_DISKSTRING value so that ASM will not discover multiple paths to the same disk. Or if we are using multipathing software, then ensure that we include only the pseudo-device name in the ASM_DISKSTRING value.2) Multiple ASM disks with the same disk headerThis can be caused by having copied one disk onto another. In this case, the disk group mount operation fails.Improving Disk Discovery Time :The value for the ASM_DISKSTRING initialization parameter is an operating system–dependent value that ASM uses to limit the set of paths that the discovery process uses to search for disks.When a new disk is added to a disk group, each ASM instance that has the disk group mounted must be able to discover the new disk using its ASM_DISKSTRING.In many cases, the default value (NULL) is sufficient. Using a more restrictive value might reduce the time required for ASM to perform discovery, and thus improve disk group mount time or the time for adding a disk to a disk group.Oracle might need to dynamically change the ASM_DISKSTRING before adding a disk so that the new disk will be discovered through this parameter.The default value of ASM_DISKSTRING might not find all disks in all situations. If our site is using a third-party vendor ASMLIB, then the vendor might have discovery string conventions that we must use for ASM_DISKSTRING.If our installation uses multipathing software, then the software might place pseudo-devices in a path that is different from the operating system default.
Undo vs Rollback Segment
Oracle: Undo vs Rollback Segment
There might be confusion while undo and rollback segment terms are used interchangeably in db world. It is due to the compatibility issue of oracle.UndoOracle Database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo.Undo records are used to:* Roll back transactions when a ROLLBACK statement is issued* Recover the database* Provide read consistency* Analyze data as of an earlier point in time by using Flashback QueryWhen a ROLLBACK statement is issued, undo records are used to undo changes that were made to the database by the uncommitted transaction. During database recovery, undo records are used to undo any uncommitted changes applied from the redo log to the datafiles. Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it.Undo vs RollbackEarlier releases of Oracle Database used rollback segments to store undo. Oracle9i introduced automatic undo management, which simplifies undo space management by eliminating the complexities associated with rollback segment management. Oracle strongly recommends (Oracle 9i and on words) to use undo tablespace (automatic undo management) to manage undo rather than rollback segments.To see the undo management mode and other undo related information of database-SQL> show parameter undoNAME TYPE VALUE———————————— ———– ——————————undo_management string AUTOundo_retention integer 900undo_tablespace string UNDOTBS1Since the advent of Oracle9i, less time-consuming and suggested way is—using Automatic Undo Management—in which Oracle Database creates and manages rollback segments (now called “undo segments”) in a special-purpose undo tablespace. Unlike with rollback segments, we don’t need to create or manage individual undo segments—Oracle Database does that for you when you create the undo tablespace. All transactions in an instance share a single undo tablespace. Any executing transaction can consume free space in the undo tablespace, and when the transaction completes, its undo space is freed (depending on how it’s been sized and a few other factors, like undo retention). Thus, space for undo segments is dynamically allocated, consumed, freed, and reused—all under the control of Oracle Database, rather than manual management by someone.Switching Rollback to Undo1. We have to create an Undo tablespace. Oracle provides a function that provides information on how to size new undo tablespace based on the configuration and usage of the rollback segments in the system.DECLAREutbsiz_in_MB NUMBER;BEGINutbsiz_in_MB ;= DBMS_UNDO_ADV.RBU_MIGRATION;end;/CREATE UNDO TABLESPACE UNDOTBSDATAFILE ‘/oradata/dbf/undotbs_1.dbf’SIZE 100M AUTOEXTEND ON NEXT 10MMAXSIZE UNLIMITED RETENTION NOGUARANTEE;Note: In undo tablespace creation, “SEGMENT SPACE MANAGEMENT AUTO” can not be set2.Change system parametersSQL> alter system set undo_retention=900 scope=both;SQL> alter system set undo_tablespace=UNDOTBS scope=both;SQL> alter system set undo_management=AUTO scope=spfile;SQL> shutdown immediateSQL> startup
UNDO_MANAGEMENT is a static parameter. So database needs to be restarted.
There might be confusion while undo and rollback segment terms are used interchangeably in db world. It is due to the compatibility issue of oracle.UndoOracle Database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo.Undo records are used to:* Roll back transactions when a ROLLBACK statement is issued* Recover the database* Provide read consistency* Analyze data as of an earlier point in time by using Flashback QueryWhen a ROLLBACK statement is issued, undo records are used to undo changes that were made to the database by the uncommitted transaction. During database recovery, undo records are used to undo any uncommitted changes applied from the redo log to the datafiles. Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it.Undo vs RollbackEarlier releases of Oracle Database used rollback segments to store undo. Oracle9i introduced automatic undo management, which simplifies undo space management by eliminating the complexities associated with rollback segment management. Oracle strongly recommends (Oracle 9i and on words) to use undo tablespace (automatic undo management) to manage undo rather than rollback segments.To see the undo management mode and other undo related information of database-SQL> show parameter undoNAME TYPE VALUE———————————— ———– ——————————undo_management string AUTOundo_retention integer 900undo_tablespace string UNDOTBS1Since the advent of Oracle9i, less time-consuming and suggested way is—using Automatic Undo Management—in which Oracle Database creates and manages rollback segments (now called “undo segments”) in a special-purpose undo tablespace. Unlike with rollback segments, we don’t need to create or manage individual undo segments—Oracle Database does that for you when you create the undo tablespace. All transactions in an instance share a single undo tablespace. Any executing transaction can consume free space in the undo tablespace, and when the transaction completes, its undo space is freed (depending on how it’s been sized and a few other factors, like undo retention). Thus, space for undo segments is dynamically allocated, consumed, freed, and reused—all under the control of Oracle Database, rather than manual management by someone.Switching Rollback to Undo1. We have to create an Undo tablespace. Oracle provides a function that provides information on how to size new undo tablespace based on the configuration and usage of the rollback segments in the system.DECLAREutbsiz_in_MB NUMBER;BEGINutbsiz_in_MB ;= DBMS_UNDO_ADV.RBU_MIGRATION;end;/CREATE UNDO TABLESPACE UNDOTBSDATAFILE ‘/oradata/dbf/undotbs_1.dbf’SIZE 100M AUTOEXTEND ON NEXT 10MMAXSIZE UNLIMITED RETENTION NOGUARANTEE;Note: In undo tablespace creation, “SEGMENT SPACE MANAGEMENT AUTO” can not be set2.Change system parametersSQL> alter system set undo_retention=900 scope=both;SQL> alter system set undo_tablespace=UNDOTBS scope=both;SQL> alter system set undo_management=AUTO scope=spfile;SQL> shutdown immediateSQL> startup
UNDO_MANAGEMENT is a static parameter. So database needs to be restarted.
Releasing Oracle Unused Space
Releasing Oracle Unused Space: Quick & Efficient Solution!!
Oracle provides a number of options to help you reclaim unused space for database objects. In this article we will discuss a number of options that will help you release unused space in your Oracle databases.
DELETE Command:
Many times we delete records in a table due to space problem. However it should be noted that delete do not reclaim free space from deleted rows. Instead Oracle keeps this space for future insertions.
ALTER TABLE --- DEALLOCATE UNUSED Command:
Oracle DEALLOCATE UNUSED can be used to explicitly de-allocate unused space at the end of the table so that the space can be used by other segments in the tablespace. If your high water mark is above MINEXTENTS then this command will free all unused space for reuse in table. DEALLOCATE UNUSED would be the fastest. It will only release unused blocks above the high level mark. Use below command to de-allocate unused extents.
ALTER TABLE TABLE_NAME DEALLOCATE UNUSED KEEP INTEGER; ALTER INDEX INDEX_NAME DEALLOCATE UNUSED KEEP INTEGER; ALTER CLUSTER CLUSTER_NAME DEALLOCATE UNUSED KEEP INTEGER;
You can run the DBMS_SPACE package's UNUSED_SPACE procedure prior to deallocation. This will give you information about the position of the high water mark and the amount of unused space in a segment. You can also use the optional KEEP clause to specify the amount of space retained in the segment of table, index or cluster. You can examine the DBA_FREE_SPACE view to verify that the deallocated space is freed.
ALTER TABLE --- SHRINK SPACE Command:
Oracle SHRINT SPACE cab be used to manually shrink space in a table.
ALTER TABLE TABLE_NAME SHRINK SPACE;
Oracle SHRINT SPACE command can be used only for segments in tablespaces with automatic segment management. You should disable any ROWID based triggers before using Shrink. You will also need to enable row movement for heap-organized segments.
ALTER TABLE TABLE_NAME ENABLE ROW MOVEMENT;
You can not shrink the tables that are part of a cluster, tables with LONG columns, tables with function-based indexes, certain types of materialized views and certain types of IOTs. Oracle 10.2 allows you to shrink LOB Segments, function based indexes and IOT overflow segments.
Usually attempting shrink in one longer step takes longer time depending upon pre delete table size. However you can use the SHRINK SPACE COMPACT clause to accomplish this task in two shorter steps. This command requires less block accesses and hence full table scans run faster.
ALTER TABLE TABLE_NAME SHRINK SPACE COMPACT;
COMPACT makes Oracle database only de-fragment the segment space and compact the table rows for subsequent release. Neither the HWM is readjusted nor is the space released immediately. You can complete the operation by running another ALTER TABLE ... SHRINK SPACE command later.
Using Oracle SHRINK SPACE COMPACT CASCADE makes Oracle perform the same operations on all dependent objects of table including secondary indexes on index-organized tables.
ALTER TABLE TABLE_NAME SHRINK SPACE COMPACT CASCADE;
Table Recreation:
You can solve space problem by using CREATE TABLE AS SELECT ..... for re-creating table with a different . After that you will need to check constraints and dependencies etc and then put database in suspension for few moments. Now you will have to rename old table and rename new table to the name of old table. It is advisable to plan a maintenance window to attempt this option.
Table Reorganization:
Sometimes specially in ETL projects a lot of data loading, reloading and deletion is done due to which table size grew considerably even if it has less data. In such scenarios you can re-organize table and released unused table space.
First of all you will have to load data with direct path method. The direct-path INSERT appends data to the end of the table. This avoids the usage of existing space currently allocated to the table.
INSERT /*+ APPEND */
Now if you load data into table and use DELETE command then delete operation will not release the allocated space. Therefore the space will not be used by any direct load operation until the space is released.
Now you can reorganize the table in the same tablespace by using below command. This will allocate new space needed for the amount of data table has. The previously allocated space that doesn't have any more data is released. All the physical attributes of the table remain the same. You can also use the ONLINE option. The MOVE ONLINE option will take longer but it will release ALL unused blocks in the table.
ALTER TABLE TABLE_NAME MOVE; ALTER TABLE TABLE_NAME MOVE ONLINE;
Contracting Oracle Tablespaces:
Many times it happens that we end up with a 20GB datafile even if it has only 20MB of data in it. Oracle tablespaces supports a great feature to auto-extend but unfortunately we can not auto-contract it. Below SQL*Plus script can be used to identify the shrinkable datafiles. It will display each tablespace, its space and how much of it is being used. You can mark those tablespace as shrinkable that have high percentage free + high byte count.
SET LINESIZE 150COLUMN MYFILE FORMAT A45 COLUMN FREESPACE FORMAT 99.00
CREATE VIEW MY_FREESPACE AS (
SELECT SUM(BYTES) FREE_BYTES, TABLESPACE_NAME FROM DBA_MY_FREESPACE GROUP BY TABLESPACE_NAME
);
SELECT D.TABLESPACE_NAME, D.BYTES OVERALL_SIZE, F.FREE_BYTES/D.BYTES*100 FREESPACE, D.MYFILE MY_FREESPACE F, DBA_DATA_FILES DWHERE F.TABLESPACE_NAME = D.TABLESPACE_NAME;
DROP VIEW MY_FREESPACE;
Once you have identified the path to the shrinkable datafile then what you just need to do is to run ALTER DATABASE command to resize it. If you try to resize a tablespace to a size smaller than its contents then you will get an error.
ALTER DATABASE DATAFILE ‘C:\ORACLE\ORADATA\DEV2\MY_DATAFILE.ORA' RESIZE 600M;
Oracle Automatic Segment Advisor:
Oracle Automatic Segment Advisor is an automated maintenance task that is configured to run during all maintenance windows. Automatic Segment Advisor is implemented by the AUTO_SPACE_ADVISOR_JOB job which executes the DBMS_SPACE.AUTO_SPACE_ADVISOR_JOB_PROC procedure at predefined points in time. This job output contains the space problems found and the recommendations.
Oracle Automatic Segment Advisor examines database statistics and samples segment data. It then selects the tablespaces that have exceeded a critical or warning space threshold, the segments with most activity and the segments with highest growth rate for analysis.
Oracle DBA_AUTO_SEGADV_SUMMARY view can be used to display the information specific to Automatic Segment Advisor. Each row of this view summarizes one Automatic Segment Advisor run.
Oracle provides a number of options to help you reclaim unused space for database objects. In this article we will discuss a number of options that will help you release unused space in your Oracle databases.
DELETE Command:
Many times we delete records in a table due to space problem. However it should be noted that delete do not reclaim free space from deleted rows. Instead Oracle keeps this space for future insertions.
ALTER TABLE --- DEALLOCATE UNUSED Command:
Oracle DEALLOCATE UNUSED can be used to explicitly de-allocate unused space at the end of the table so that the space can be used by other segments in the tablespace. If your high water mark is above MINEXTENTS then this command will free all unused space for reuse in table. DEALLOCATE UNUSED would be the fastest. It will only release unused blocks above the high level mark. Use below command to de-allocate unused extents.
ALTER TABLE TABLE_NAME DEALLOCATE UNUSED KEEP INTEGER; ALTER INDEX INDEX_NAME DEALLOCATE UNUSED KEEP INTEGER; ALTER CLUSTER CLUSTER_NAME DEALLOCATE UNUSED KEEP INTEGER;
You can run the DBMS_SPACE package's UNUSED_SPACE procedure prior to deallocation. This will give you information about the position of the high water mark and the amount of unused space in a segment. You can also use the optional KEEP clause to specify the amount of space retained in the segment of table, index or cluster. You can examine the DBA_FREE_SPACE view to verify that the deallocated space is freed.
ALTER TABLE --- SHRINK SPACE Command:
Oracle SHRINT SPACE cab be used to manually shrink space in a table.
ALTER TABLE TABLE_NAME SHRINK SPACE;
Oracle SHRINT SPACE command can be used only for segments in tablespaces with automatic segment management. You should disable any ROWID based triggers before using Shrink. You will also need to enable row movement for heap-organized segments.
ALTER TABLE TABLE_NAME ENABLE ROW MOVEMENT;
You can not shrink the tables that are part of a cluster, tables with LONG columns, tables with function-based indexes, certain types of materialized views and certain types of IOTs. Oracle 10.2 allows you to shrink LOB Segments, function based indexes and IOT overflow segments.
Usually attempting shrink in one longer step takes longer time depending upon pre delete table size. However you can use the SHRINK SPACE COMPACT clause to accomplish this task in two shorter steps. This command requires less block accesses and hence full table scans run faster.
ALTER TABLE TABLE_NAME SHRINK SPACE COMPACT;
COMPACT makes Oracle database only de-fragment the segment space and compact the table rows for subsequent release. Neither the HWM is readjusted nor is the space released immediately. You can complete the operation by running another ALTER TABLE ... SHRINK SPACE command later.
Using Oracle SHRINK SPACE COMPACT CASCADE makes Oracle perform the same operations on all dependent objects of table including secondary indexes on index-organized tables.
ALTER TABLE TABLE_NAME SHRINK SPACE COMPACT CASCADE;
Table Recreation:
You can solve space problem by using CREATE TABLE AS SELECT ..... for re-creating table with a different . After that you will need to check constraints and dependencies etc and then put database in suspension for few moments. Now you will have to rename old table and rename new table to the name of old table. It is advisable to plan a maintenance window to attempt this option.
Table Reorganization:
Sometimes specially in ETL projects a lot of data loading, reloading and deletion is done due to which table size grew considerably even if it has less data. In such scenarios you can re-organize table and released unused table space.
First of all you will have to load data with direct path method. The direct-path INSERT appends data to the end of the table. This avoids the usage of existing space currently allocated to the table.
INSERT /*+ APPEND */
Now if you load data into table and use DELETE command then delete operation will not release the allocated space. Therefore the space will not be used by any direct load operation until the space is released.
Now you can reorganize the table in the same tablespace by using below command. This will allocate new space needed for the amount of data table has. The previously allocated space that doesn't have any more data is released. All the physical attributes of the table remain the same. You can also use the ONLINE option. The MOVE ONLINE option will take longer but it will release ALL unused blocks in the table.
ALTER TABLE TABLE_NAME MOVE; ALTER TABLE TABLE_NAME MOVE ONLINE;
Contracting Oracle Tablespaces:
Many times it happens that we end up with a 20GB datafile even if it has only 20MB of data in it. Oracle tablespaces supports a great feature to auto-extend but unfortunately we can not auto-contract it. Below SQL*Plus script can be used to identify the shrinkable datafiles. It will display each tablespace, its space and how much of it is being used. You can mark those tablespace as shrinkable that have high percentage free + high byte count.
SET LINESIZE 150COLUMN MYFILE FORMAT A45 COLUMN FREESPACE FORMAT 99.00
CREATE VIEW MY_FREESPACE AS (
SELECT SUM(BYTES) FREE_BYTES, TABLESPACE_NAME FROM DBA_MY_FREESPACE GROUP BY TABLESPACE_NAME
);
SELECT D.TABLESPACE_NAME, D.BYTES OVERALL_SIZE, F.FREE_BYTES/D.BYTES*100 FREESPACE, D.MYFILE MY_FREESPACE F, DBA_DATA_FILES DWHERE F.TABLESPACE_NAME = D.TABLESPACE_NAME;
DROP VIEW MY_FREESPACE;
Once you have identified the path to the shrinkable datafile then what you just need to do is to run ALTER DATABASE command to resize it. If you try to resize a tablespace to a size smaller than its contents then you will get an error.
ALTER DATABASE DATAFILE ‘C:\ORACLE\ORADATA\DEV2\MY_DATAFILE.ORA' RESIZE 600M;
Oracle Automatic Segment Advisor:
Oracle Automatic Segment Advisor is an automated maintenance task that is configured to run during all maintenance windows. Automatic Segment Advisor is implemented by the AUTO_SPACE_ADVISOR_JOB job which executes the DBMS_SPACE.AUTO_SPACE_ADVISOR_JOB_PROC procedure at predefined points in time. This job output contains the space problems found and the recommendations.
Oracle Automatic Segment Advisor examines database statistics and samples segment data. It then selects the tablespaces that have exceeded a critical or warning space threshold, the segments with most activity and the segments with highest growth rate for analysis.
Oracle DBA_AUTO_SEGADV_SUMMARY view can be used to display the information specific to Automatic Segment Advisor. Each row of this view summarizes one Automatic Segment Advisor run.
RMAN versus User Managed Backup
RMAN versus User Managed Backup-Part-I
We have two ONLINE backup options for taking Oracle database backupRMANUser ManagedBoth options are needed ARCHIVELOG mode to take ONLINE database backup.But why Oracle recommended using RMAN to take oracle database backup?Because there is too many other option for database backup which are not exist in User Managed or if I say we can’t use those options with User Managed backup.Many of newbie is scare about RMAN (Recovery Manager) backup procedure and thinking it is too complicated but actually it is not.How we take ONLINE database backup through User Managed method?SQL> alter database begin backup;Database altered.SQL> ---copy all datafiles to backup locationSQL> alter database end backup;Database altered.SQL> --switch logfileSQL> alter system switch logfile;SQL> --create controlfile backupSQL> alter database backup controlfile to 'e:\backup\control01.ctl';Database altered.SQL> --create spfile backupSQL> create pfile='e:\backup\init.ora' from spfile;File created.NOW user managed backup is complete.For above 5 steps we need to create scripts and run it.We can also use above 5 steps through RMAN with single command and take database backupRMAN> configure controlfile autobackup on;RMAN> configure controlfile autobackup format for device type disk to 'e:\backup\%F';RMAN> run2> {3> backup database format 'e:\backup\%U';4> SQL 'alter system switch logfile';5> backup archivelog all delete input format 'e:\backup\%U';6> }Now RMAN backup is complete.
Just above command will take database backup and also DELETE all archivelog file which are backed up.Advantage of RMAN
We no need to COPY data files manually to backup location
We can directly take/transfer backup on TAPE drive.
We don’t put database in backup mode
We can take backup in single RUN command
RMAN is fast then User Managed backup
RMAN also take backup of archivelog and delete all archivelog which backed up.
We no need to take backup of spfile or controlfile separately RMAN will take backup of both.
RMAN versus User Managed Backup-Part-II
In PART-I we learn how to take backup of oracle database using User Managed or RMAN.Now backup is useful in case of any failure if backup is VALID to restore means we need to also verify database backup.For this thing oracle will provide utility “dbverify” which check physical corruption online or offline datafiles backup set.So first we check User Managed backup through dbv verify utility which was taken in PART-I.E:\backup>dbv file=system01.dbf feedback=10000DBVERIFY: Release 10.1.0.2.0 - Production on Sat May 10 16:46:15 2008Copyright (c) 1982, 2004, Oracle. All rights reserved.DBVERIFY - Verification starting: FILE = system01.dbf......DBVERIFY - Verification completeTotal Pages Examined : 56320Total Pages Processed (Data) : 33386Total Pages Failing (Data) : 0Total Pages Processed (Index): 6077Total Pages Failing (Index): 0Total Pages Processed (Other): 1524Total Pages Processed (Seg) : 0Total Pages Failing (Seg) : 0Total Pages Empty : 15333Total Pages Marked Corrupt : 0Total Pages Influx : 0NOTE: we can check all files like above one by oneNow User Managed backup testing is complete.For RMAN oracle provide database validate command to check database or archivelog file backup set after backup.We can not use DBVerify utility for RMAN backup so that RMAN will provide VALIDATE command with BACKUP or RESTORE for physical or logical corruption in backupset or ONLINE database files.RMAN> run2> {3> allocate channel c1 device type disk format 'e:\backup\%U';4> restore validate check logical database archivelog all;5> }allocated channel: c1channel c1: sid=147 devtype=DISKStarting restore at 10-MAY-08channel c1: starting validation of datafile backupsetchannel c1: restored backup piece 1piece handle=E:\BACKUP\01JG1QT1_1_1 tag=TAG20080510T175545channel c1: validation completechannel c1: starting validation of archive log backupsetchannel c1: restored backup piece 1piece handle=E:\BACKUP\03JG1QTV_1_1 tag=TAG20080510T175615channel c1: validation completeFinished restore at 10-MAY-08released channel: c1NOTE: check logical keyword check logical corruption in backupset.If we want to check ONLINE database files for physical or logical corruption so we can use backup keyword instead of restore.RMAN> run2> {3> backup validate check logical database archivelog all;4> }Starting backup at 10-MAY-08allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=147 devtype=DISKchannel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBFinput datafile fno=00003 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBFinput datafile fno=00005 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBFinput datafile fno=00002 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBFinput datafile fno=00004 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBFchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:07channel ORA_DISK_1: starting archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1 sequence=9 recid=3 stamp=654372512channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 10-MAY-08Read more about Database block corruption In above utility we just check physical or logical database corruption of backup but best practice is restore and recover database backup to check completely because suppose if disaster happen and we lost everything then how we can rebuild database from backup set.So In this Part, I will show you how to restore backup was taken by User managed or RMAN
User managed backup restore and recover procedure from backup.1. Create file directory structure for datafiles,tracefiles from alertlog file location
2. Create oracle services through oradimC:\>oradim -new -sid ORCL -startmode aInstance created.3. Create password file if database authentication method is passwordfileC:\>orapwd file=d:\oracle\product\10.1.0\db_1\database\pwdorcl.ora password=oracle entries=5 force=y4. Connect to sqlplus with sysdba privilegesC:\>sqlplus sys/oracle as sysdbaSQL*Plus: Release 10.1.0.2.0 - Production on Sat May 10 16:59:57 2008Copyright (c) 1982, 2004, Oracle. All rights reserved.Connected to an idle instance.5. Create spfile from backup of pfileSQL> create spfile from pfile='e:\backup\init.ora';File created.6. startup database with nomount stageSQL> startup nomountORACLE instance started.Total System Global Area 293601280 bytesFixed Size 788572 bytesVariable Size 98826148 bytesDatabase Buffers 192937984 bytesRedo Buffers 1048576 bytes7. restore controlfile from backup and mount the databaseSQL> alter database mount;Database altered.8. restore all datafiles and archivelog file from backup and perform incomplete recovery with UNTIL CANCEL clauseSQL> set autorecovery onSQL> recover database using backup controlfile until cancel;open database with resetlogsSQL> alter database open resetlogs;RMAN restore and recover procedure from backup.1. Create file directory structure for datafiles or trace file from alertlog2. Create Oracle Service from oradim utilityC:\>oradim -new -sid ORCLInstance created. 3. Create password file4. Connect to RMAN with SYS userC:\>rman target=sysRecovery Manager: Release 10.1.0.2.0 - ProductionCopyright (c) 1995, 2004, Oracle. All rights reserved.target database Password:connected to target database (not started)5. Set DBID and restore controlfile or spfile from autobackupNOTE: you can check DBID from auto controlfile name in this scenario controlfile name is ‘e:\backup\ C-1182151585-20080510-01RMAN> set DBID=1182151585executing command: SET DBIDRMAN> set DBID=1182151585executing command: SET DBIDRMAN> startup nomount force;startup failed: ORA-01078: failure in processing system parametersLRM-00109: could not open parameter file 'D:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\INITORCL.ORA'trying to start the Oracle instance without parameter files ...Oracle instance startedTotal System Global Area 113246208 bytesFixed Size 787708 bytesVariable Size 61078276 bytesDatabase Buffers 50331648 bytesRedo Buffers 1048576 bytes6. Restore SPFILE from backupRMAN> restore spfile to pfile 'e:\backup\init.ora' from 'e:\backup\C-1182151585-20080510-01';Starting restore at 10-MAY-08using target database controlfile instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=29 devtype=DISKchannel ORA_DISK_1: autobackup found: e:\backup\C-1182151585-20080510-01channel ORA_DISK_1: SPFILE restore from autobackup completeFinished restore at 10-MAY-087. Now shutdown the database and startup and create spfileRMAN> startup nomount pfile 'e:\backup\init.ora';connected to target database (not started)Oracle instance startedTotal System Global Area 293601280 bytesFixed Size 788572 bytesVariable Size 94631844 bytesDatabase Buffers 197132288 bytesRedo Buffers 1048576 bytes8. Restore controlfile from backup and mount the databaseRMAN> restore controlfile from 'e:\backup\C-1182151585-20080510-01';Starting restore at 10-MAY-08allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=160 devtype=DISKchannel ORA_DISK_1: restoring controlfilechannel ORA_DISK_1: restore completeoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL01.CTLoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL02.CTLoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL03.CTLFinished restore at 10-MAY-08RMAN> alter database mount;database mountedreleased channel: ORA_DISK_19. If File directories system is different then original then you need to rename or set newname for all datafiles or redologs.
SQL> column name format a55
SQL> select file#,name from v$datafile
2 union
3 select null,member from v$logfile;
FILE# NAME
---------- -------------------------------------------------1 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF
2 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBF
3 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBF
4 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO01.LOG D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO02.LOG D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO03.LOG
7 rows selected.
10. Recover and open database with resetlogs
RMAN> run2> {3> set newname for datafile 1 to 'e:\orcl\oradata\system01.dbf';4> set newname for datafile 2 to 'e:\orcl\oradata\undotbs01.dbf';5> set newname for datafile 3 to 'e:\orcl\oradata\sysaux01.dbf';6> set newname for datafile 4 to 'e:\orcl\oradata\users01.dbf';7> SQL "alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo01.log'' to ''e:\orcl\oradata\redo01.log'' ";8> SQL "alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo02.log'' to ''e:\orcl\oradata\redo02.log'' ";9> SQL "alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo03.log'' to ''e:\orcl\oradata\redo03.log'' ";10> restore database;11> switch datafile all;12> recover database;13> }executing command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEsql statement: alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo01.log'' to ''e:\orcl\oradata\redo01.log''sql statement: alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo02.log'' to ''e:\orcl\oradata\redo02.log''sql statement: alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo03.log'' to ''e:\orcl\oradata\redo03.log''Starting restore at 10-MAY-08allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=159 devtype=DISKchannel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to E:\ORCL\ORADATA\SYSTEM01.DBFrestoring datafile 00002 to E:\ORCL\ORADATA\UNDOTBS01.DBFrestoring datafile 00003 to E:\ORCL\ORADATA\SYSAUX01.DBFrestoring datafile 00004 to E:\ORCL\ORADATA\USERS01.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=E:\BACKUP\05JG23BO_1_1 tag=TAG20080510T202008channel ORA_DISK_1: restore completeFinished restore at 10-MAY-08datafile 1 switched to datafile copyinput datafilecopy recid=5 stamp=654381217 filename=E:\ORCL\ORADATA\SYSTEM01.DBFdatafile 2 switched to datafile copyinput datafilecopy recid=6 stamp=654381217 filename=E:\ORCL\ORADATA\UNDOTBS01.DBFdatafile 3 switched to datafile copyinput datafilecopy recid=7 stamp=654381217 filename=E:\ORCL\ORADATA\SYSAUX01.DBFdatafile 4 switched to datafile copyinput datafilecopy recid=8 stamp=654381217 filename=E:\ORCL\ORADATA\USERS01.DBFStarting recover at 10-MAY-08using channel ORA_DISK_1starting media recoveryunable to find archive logarchive log thread=1 sequence=8RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 05/10/2008 20:33:39RMAN-06054: media recovery requesting unknown log: thread 1 seq 8 lowscn 327780RMAN> run2> {3> SQL 'alter database open resetlogs';4> }sql statement: alter database open resetlogs11. All datafiles and redologs file switch perfectly :)
SQL> select file#,name from v$datafile2 union3 select null,member from v$logfile;FILE# NAME---------- ------------------------------------------------- 1 E:\ORCL\ORADATA\SYSTEM01.DBF2 E:\ORCL\ORADATA\UNDOTBS01.DBF3 E:\ORCL\ORADATA\SYSAUX01.DBF4 E:\ORCL\ORADATA\USERS01.DBFE:\ORCL\ORADATA\REDO01.LOGE:\ORCL\ORADATA\REDO02.LOGE:\ORCL\ORADATA\REDO03.LOG7 rows selected.
12. Create SPFILE from PFILE
SQL> create spfile from pfile='e:\backup\init.ora';File created.
RMAN versus User Managed Backup-Part-I
We have two ONLINE backup options for taking Oracle database backupRMANUser ManagedBoth options are needed ARCHIVELOG mode to take ONLINE database backup.But why Oracle recommended using RMAN to take oracle database backup?Because there is too many other option for database backup which are not exist in User Managed or if I say we can’t use those options with User Managed backup.Many of newbie is scare about RMAN (Recovery Manager) backup procedure and thinking it is too complicated but actually it is not.How we take ONLINE database backup through User Managed method?SQL> alter database begin backup;Database altered.SQL> ---copy all datafiles to backup locationSQL> alter database end backup;Database altered.SQL> --switch logfileSQL> alter system switch logfile;SQL> --create controlfile backupSQL> alter database backup controlfile to 'e:\backup\control01.ctl';Database altered.SQL> --create spfile backupSQL> create pfile='e:\backup\init.ora' from spfile;File created.NOW user managed backup is complete.For above 5 steps we need to create scripts and run it.We can also use above 5 steps through RMAN with single command and take database backupRMAN> configure controlfile autobackup on;RMAN> configure controlfile autobackup format for device type disk to 'e:\backup\%F';RMAN> run2> {3> backup database format 'e:\backup\%U';4> SQL 'alter system switch logfile';5> backup archivelog all delete input format 'e:\backup\%U';6> }Now RMAN backup is complete.
Just above command will take database backup and also DELETE all archivelog file which are backed up.Advantage of RMAN
We no need to COPY data files manually to backup location
We can directly take/transfer backup on TAPE drive.
We don’t put database in backup mode
We can take backup in single RUN command
RMAN is fast then User Managed backup
RMAN also take backup of archivelog and delete all archivelog which backed up.
We no need to take backup of spfile or controlfile separately RMAN will take backup of both.
RMAN versus User Managed Backup-Part-II
In PART-I we learn how to take backup of oracle database using User Managed or RMAN.Now backup is useful in case of any failure if backup is VALID to restore means we need to also verify database backup.For this thing oracle will provide utility “dbverify” which check physical corruption online or offline datafiles backup set.So first we check User Managed backup through dbv verify utility which was taken in PART-I.E:\backup>dbv file=system01.dbf feedback=10000DBVERIFY: Release 10.1.0.2.0 - Production on Sat May 10 16:46:15 2008Copyright (c) 1982, 2004, Oracle. All rights reserved.DBVERIFY - Verification starting: FILE = system01.dbf......DBVERIFY - Verification completeTotal Pages Examined : 56320Total Pages Processed (Data) : 33386Total Pages Failing (Data) : 0Total Pages Processed (Index): 6077Total Pages Failing (Index): 0Total Pages Processed (Other): 1524Total Pages Processed (Seg) : 0Total Pages Failing (Seg) : 0Total Pages Empty : 15333Total Pages Marked Corrupt : 0Total Pages Influx : 0NOTE: we can check all files like above one by oneNow User Managed backup testing is complete.For RMAN oracle provide database validate command to check database or archivelog file backup set after backup.We can not use DBVerify utility for RMAN backup so that RMAN will provide VALIDATE command with BACKUP or RESTORE for physical or logical corruption in backupset or ONLINE database files.RMAN> run2> {3> allocate channel c1 device type disk format 'e:\backup\%U';4> restore validate check logical database archivelog all;5> }allocated channel: c1channel c1: sid=147 devtype=DISKStarting restore at 10-MAY-08channel c1: starting validation of datafile backupsetchannel c1: restored backup piece 1piece handle=E:\BACKUP\01JG1QT1_1_1 tag=TAG20080510T175545channel c1: validation completechannel c1: starting validation of archive log backupsetchannel c1: restored backup piece 1piece handle=E:\BACKUP\03JG1QTV_1_1 tag=TAG20080510T175615channel c1: validation completeFinished restore at 10-MAY-08released channel: c1NOTE: check logical keyword check logical corruption in backupset.If we want to check ONLINE database files for physical or logical corruption so we can use backup keyword instead of restore.RMAN> run2> {3> backup validate check logical database archivelog all;4> }Starting backup at 10-MAY-08allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=147 devtype=DISKchannel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBFinput datafile fno=00003 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBFinput datafile fno=00005 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBFinput datafile fno=00002 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBFinput datafile fno=00004 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBFchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:07channel ORA_DISK_1: starting archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1 sequence=9 recid=3 stamp=654372512channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 10-MAY-08Read more about Database block corruption In above utility we just check physical or logical database corruption of backup but best practice is restore and recover database backup to check completely because suppose if disaster happen and we lost everything then how we can rebuild database from backup set.So In this Part, I will show you how to restore backup was taken by User managed or RMAN
User managed backup restore and recover procedure from backup.1. Create file directory structure for datafiles,tracefiles from alertlog file location
2. Create oracle services through oradimC:\>oradim -new -sid ORCL -startmode aInstance created.3. Create password file if database authentication method is passwordfileC:\>orapwd file=d:\oracle\product\10.1.0\db_1\database\pwdorcl.ora password=oracle entries=5 force=y4. Connect to sqlplus with sysdba privilegesC:\>sqlplus sys/oracle as sysdbaSQL*Plus: Release 10.1.0.2.0 - Production on Sat May 10 16:59:57 2008Copyright (c) 1982, 2004, Oracle. All rights reserved.Connected to an idle instance.5. Create spfile from backup of pfileSQL> create spfile from pfile='e:\backup\init.ora';File created.6. startup database with nomount stageSQL> startup nomountORACLE instance started.Total System Global Area 293601280 bytesFixed Size 788572 bytesVariable Size 98826148 bytesDatabase Buffers 192937984 bytesRedo Buffers 1048576 bytes7. restore controlfile from backup and mount the databaseSQL> alter database mount;Database altered.8. restore all datafiles and archivelog file from backup and perform incomplete recovery with UNTIL CANCEL clauseSQL> set autorecovery onSQL> recover database using backup controlfile until cancel;open database with resetlogsSQL> alter database open resetlogs;RMAN restore and recover procedure from backup.1. Create file directory structure for datafiles or trace file from alertlog2. Create Oracle Service from oradim utilityC:\>oradim -new -sid ORCLInstance created. 3. Create password file4. Connect to RMAN with SYS userC:\>rman target=sysRecovery Manager: Release 10.1.0.2.0 - ProductionCopyright (c) 1995, 2004, Oracle. All rights reserved.target database Password:connected to target database (not started)5. Set DBID and restore controlfile or spfile from autobackupNOTE: you can check DBID from auto controlfile name in this scenario controlfile name is ‘e:\backup\ C-1182151585-20080510-01RMAN> set DBID=1182151585executing command: SET DBIDRMAN> set DBID=1182151585executing command: SET DBIDRMAN> startup nomount force;startup failed: ORA-01078: failure in processing system parametersLRM-00109: could not open parameter file 'D:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\INITORCL.ORA'trying to start the Oracle instance without parameter files ...Oracle instance startedTotal System Global Area 113246208 bytesFixed Size 787708 bytesVariable Size 61078276 bytesDatabase Buffers 50331648 bytesRedo Buffers 1048576 bytes6. Restore SPFILE from backupRMAN> restore spfile to pfile 'e:\backup\init.ora' from 'e:\backup\C-1182151585-20080510-01';Starting restore at 10-MAY-08using target database controlfile instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=29 devtype=DISKchannel ORA_DISK_1: autobackup found: e:\backup\C-1182151585-20080510-01channel ORA_DISK_1: SPFILE restore from autobackup completeFinished restore at 10-MAY-087. Now shutdown the database and startup and create spfileRMAN> startup nomount pfile 'e:\backup\init.ora';connected to target database (not started)Oracle instance startedTotal System Global Area 293601280 bytesFixed Size 788572 bytesVariable Size 94631844 bytesDatabase Buffers 197132288 bytesRedo Buffers 1048576 bytes8. Restore controlfile from backup and mount the databaseRMAN> restore controlfile from 'e:\backup\C-1182151585-20080510-01';Starting restore at 10-MAY-08allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=160 devtype=DISKchannel ORA_DISK_1: restoring controlfilechannel ORA_DISK_1: restore completeoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL01.CTLoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL02.CTLoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL03.CTLFinished restore at 10-MAY-08RMAN> alter database mount;database mountedreleased channel: ORA_DISK_19. If File directories system is different then original then you need to rename or set newname for all datafiles or redologs.
SQL> column name format a55
SQL> select file#,name from v$datafile
2 union
3 select null,member from v$logfile;
FILE# NAME
---------- -------------------------------------------------1 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF
2 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBF
3 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBF
4 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO01.LOG D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO02.LOG D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO03.LOG
7 rows selected.
10. Recover and open database with resetlogs
RMAN> run2> {3> set newname for datafile 1 to 'e:\orcl\oradata\system01.dbf';4> set newname for datafile 2 to 'e:\orcl\oradata\undotbs01.dbf';5> set newname for datafile 3 to 'e:\orcl\oradata\sysaux01.dbf';6> set newname for datafile 4 to 'e:\orcl\oradata\users01.dbf';7> SQL "alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo01.log'' to ''e:\orcl\oradata\redo01.log'' ";8> SQL "alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo02.log'' to ''e:\orcl\oradata\redo02.log'' ";9> SQL "alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo03.log'' to ''e:\orcl\oradata\redo03.log'' ";10> restore database;11> switch datafile all;12> recover database;13> }executing command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEsql statement: alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo01.log'' to ''e:\orcl\oradata\redo01.log''sql statement: alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo02.log'' to ''e:\orcl\oradata\redo02.log''sql statement: alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo03.log'' to ''e:\orcl\oradata\redo03.log''Starting restore at 10-MAY-08allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=159 devtype=DISKchannel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to E:\ORCL\ORADATA\SYSTEM01.DBFrestoring datafile 00002 to E:\ORCL\ORADATA\UNDOTBS01.DBFrestoring datafile 00003 to E:\ORCL\ORADATA\SYSAUX01.DBFrestoring datafile 00004 to E:\ORCL\ORADATA\USERS01.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=E:\BACKUP\05JG23BO_1_1 tag=TAG20080510T202008channel ORA_DISK_1: restore completeFinished restore at 10-MAY-08datafile 1 switched to datafile copyinput datafilecopy recid=5 stamp=654381217 filename=E:\ORCL\ORADATA\SYSTEM01.DBFdatafile 2 switched to datafile copyinput datafilecopy recid=6 stamp=654381217 filename=E:\ORCL\ORADATA\UNDOTBS01.DBFdatafile 3 switched to datafile copyinput datafilecopy recid=7 stamp=654381217 filename=E:\ORCL\ORADATA\SYSAUX01.DBFdatafile 4 switched to datafile copyinput datafilecopy recid=8 stamp=654381217 filename=E:\ORCL\ORADATA\USERS01.DBFStarting recover at 10-MAY-08using channel ORA_DISK_1starting media recoveryunable to find archive logarchive log thread=1 sequence=8RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 05/10/2008 20:33:39RMAN-06054: media recovery requesting unknown log: thread 1 seq 8 lowscn 327780RMAN> run2> {3> SQL 'alter database open resetlogs';4> }sql statement: alter database open resetlogs11. All datafiles and redologs file switch perfectly :)
SQL> select file#,name from v$datafile2 union3 select null,member from v$logfile;FILE# NAME---------- ------------------------------------------------- 1 E:\ORCL\ORADATA\SYSTEM01.DBF2 E:\ORCL\ORADATA\UNDOTBS01.DBF3 E:\ORCL\ORADATA\SYSAUX01.DBF4 E:\ORCL\ORADATA\USERS01.DBFE:\ORCL\ORADATA\REDO01.LOGE:\ORCL\ORADATA\REDO02.LOGE:\ORCL\ORADATA\REDO03.LOG7 rows selected.
12. Create SPFILE from PFILE
SQL> create spfile from pfile='e:\backup\init.ora';File created.
RMAN versus User Managed Backup-Part-I
We have two ONLINE backup options for taking Oracle database backupRMANUser ManagedBoth options are needed ARCHIVELOG mode to take ONLINE database backup.But why Oracle recommended using RMAN to take oracle database backup?Because there is too many other option for database backup which are not exist in User Managed or if I say we can’t use those options with User Managed backup.Many of newbie is scare about RMAN (Recovery Manager) backup procedure and thinking it is too complicated but actually it is not.How we take ONLINE database backup through User Managed method?SQL> alter database begin backup;Database altered.SQL> ---copy all datafiles to backup locationSQL> alter database end backup;Database altered.SQL> --switch logfileSQL> alter system switch logfile;SQL> --create controlfile backupSQL> alter database backup controlfile to 'e:\backup\control01.ctl';Database altered.SQL> --create spfile backupSQL> create pfile='e:\backup\init.ora' from spfile;File created.NOW user managed backup is complete.For above 5 steps we need to create scripts and run it.We can also use above 5 steps through RMAN with single command and take database backupRMAN> configure controlfile autobackup on;RMAN> configure controlfile autobackup format for device type disk to 'e:\backup\%F';RMAN> run2> {3> backup database format 'e:\backup\%U';4> SQL 'alter system switch logfile';5> backup archivelog all delete input format 'e:\backup\%U';6> }Now RMAN backup is complete.
Just above command will take database backup and also DELETE all archivelog file which are backed up.Advantage of RMAN
We no need to COPY data files manually to backup location
We can directly take/transfer backup on TAPE drive.
We don’t put database in backup mode
We can take backup in single RUN command
RMAN is fast then User Managed backup
RMAN also take backup of archivelog and delete all archivelog which backed up.
We no need to take backup of spfile or controlfile separately RMAN will take backup of both.
RMAN versus User Managed Backup-Part-II
In PART-I we learn how to take backup of oracle database using User Managed or RMAN.Now backup is useful in case of any failure if backup is VALID to restore means we need to also verify database backup.For this thing oracle will provide utility “dbverify” which check physical corruption online or offline datafiles backup set.So first we check User Managed backup through dbv verify utility which was taken in PART-I.E:\backup>dbv file=system01.dbf feedback=10000DBVERIFY: Release 10.1.0.2.0 - Production on Sat May 10 16:46:15 2008Copyright (c) 1982, 2004, Oracle. All rights reserved.DBVERIFY - Verification starting: FILE = system01.dbf......DBVERIFY - Verification completeTotal Pages Examined : 56320Total Pages Processed (Data) : 33386Total Pages Failing (Data) : 0Total Pages Processed (Index): 6077Total Pages Failing (Index): 0Total Pages Processed (Other): 1524Total Pages Processed (Seg) : 0Total Pages Failing (Seg) : 0Total Pages Empty : 15333Total Pages Marked Corrupt : 0Total Pages Influx : 0NOTE: we can check all files like above one by oneNow User Managed backup testing is complete.For RMAN oracle provide database validate command to check database or archivelog file backup set after backup.We can not use DBVerify utility for RMAN backup so that RMAN will provide VALIDATE command with BACKUP or RESTORE for physical or logical corruption in backupset or ONLINE database files.RMAN> run2> {3> allocate channel c1 device type disk format 'e:\backup\%U';4> restore validate check logical database archivelog all;5> }allocated channel: c1channel c1: sid=147 devtype=DISKStarting restore at 10-MAY-08channel c1: starting validation of datafile backupsetchannel c1: restored backup piece 1piece handle=E:\BACKUP\01JG1QT1_1_1 tag=TAG20080510T175545channel c1: validation completechannel c1: starting validation of archive log backupsetchannel c1: restored backup piece 1piece handle=E:\BACKUP\03JG1QTV_1_1 tag=TAG20080510T175615channel c1: validation completeFinished restore at 10-MAY-08released channel: c1NOTE: check logical keyword check logical corruption in backupset.If we want to check ONLINE database files for physical or logical corruption so we can use backup keyword instead of restore.RMAN> run2> {3> backup validate check logical database archivelog all;4> }Starting backup at 10-MAY-08allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=147 devtype=DISKchannel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBFinput datafile fno=00003 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBFinput datafile fno=00005 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBFinput datafile fno=00002 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBFinput datafile fno=00004 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBFchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:07channel ORA_DISK_1: starting archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1 sequence=9 recid=3 stamp=654372512channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 10-MAY-08Read more about Database block corruption In above utility we just check physical or logical database corruption of backup but best practice is restore and recover database backup to check completely because suppose if disaster happen and we lost everything then how we can rebuild database from backup set.So In this Part, I will show you how to restore backup was taken by User managed or RMAN
User managed backup restore and recover procedure from backup.1. Create file directory structure for datafiles,tracefiles from alertlog file location
2. Create oracle services through oradimC:\>oradim -new -sid ORCL -startmode aInstance created.3. Create password file if database authentication method is passwordfileC:\>orapwd file=d:\oracle\product\10.1.0\db_1\database\pwdorcl.ora password=oracle entries=5 force=y4. Connect to sqlplus with sysdba privilegesC:\>sqlplus sys/oracle as sysdbaSQL*Plus: Release 10.1.0.2.0 - Production on Sat May 10 16:59:57 2008Copyright (c) 1982, 2004, Oracle. All rights reserved.Connected to an idle instance.5. Create spfile from backup of pfileSQL> create spfile from pfile='e:\backup\init.ora';File created.6. startup database with nomount stageSQL> startup nomountORACLE instance started.Total System Global Area 293601280 bytesFixed Size 788572 bytesVariable Size 98826148 bytesDatabase Buffers 192937984 bytesRedo Buffers 1048576 bytes7. restore controlfile from backup and mount the databaseSQL> alter database mount;Database altered.8. restore all datafiles and archivelog file from backup and perform incomplete recovery with UNTIL CANCEL clauseSQL> set autorecovery onSQL> recover database using backup controlfile until cancel;open database with resetlogsSQL> alter database open resetlogs;RMAN restore and recover procedure from backup.1. Create file directory structure for datafiles or trace file from alertlog2. Create Oracle Service from oradim utilityC:\>oradim -new -sid ORCLInstance created. 3. Create password file4. Connect to RMAN with SYS userC:\>rman target=sysRecovery Manager: Release 10.1.0.2.0 - ProductionCopyright (c) 1995, 2004, Oracle. All rights reserved.target database Password:connected to target database (not started)5. Set DBID and restore controlfile or spfile from autobackupNOTE: you can check DBID from auto controlfile name in this scenario controlfile name is ‘e:\backup\ C-1182151585-20080510-01RMAN> set DBID=1182151585executing command: SET DBIDRMAN> set DBID=1182151585executing command: SET DBIDRMAN> startup nomount force;startup failed: ORA-01078: failure in processing system parametersLRM-00109: could not open parameter file 'D:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\INITORCL.ORA'trying to start the Oracle instance without parameter files ...Oracle instance startedTotal System Global Area 113246208 bytesFixed Size 787708 bytesVariable Size 61078276 bytesDatabase Buffers 50331648 bytesRedo Buffers 1048576 bytes6. Restore SPFILE from backupRMAN> restore spfile to pfile 'e:\backup\init.ora' from 'e:\backup\C-1182151585-20080510-01';Starting restore at 10-MAY-08using target database controlfile instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=29 devtype=DISKchannel ORA_DISK_1: autobackup found: e:\backup\C-1182151585-20080510-01channel ORA_DISK_1: SPFILE restore from autobackup completeFinished restore at 10-MAY-087. Now shutdown the database and startup and create spfileRMAN> startup nomount pfile 'e:\backup\init.ora';connected to target database (not started)Oracle instance startedTotal System Global Area 293601280 bytesFixed Size 788572 bytesVariable Size 94631844 bytesDatabase Buffers 197132288 bytesRedo Buffers 1048576 bytes8. Restore controlfile from backup and mount the databaseRMAN> restore controlfile from 'e:\backup\C-1182151585-20080510-01';Starting restore at 10-MAY-08allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=160 devtype=DISKchannel ORA_DISK_1: restoring controlfilechannel ORA_DISK_1: restore completeoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL01.CTLoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL02.CTLoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL03.CTLFinished restore at 10-MAY-08RMAN> alter database mount;database mountedreleased channel: ORA_DISK_19. If File directories system is different then original then you need to rename or set newname for all datafiles or redologs.
SQL> column name format a55
SQL> select file#,name from v$datafile
2 union
3 select null,member from v$logfile;
FILE# NAME
---------- -------------------------------------------------1 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF
2 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBF
3 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBF
4 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO01.LOG D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO02.LOG D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO03.LOG
7 rows selected.
10. Recover and open database with resetlogs
RMAN> run2> {3> set newname for datafile 1 to 'e:\orcl\oradata\system01.dbf';4> set newname for datafile 2 to 'e:\orcl\oradata\undotbs01.dbf';5> set newname for datafile 3 to 'e:\orcl\oradata\sysaux01.dbf';6> set newname for datafile 4 to 'e:\orcl\oradata\users01.dbf';7> SQL "alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo01.log'' to ''e:\orcl\oradata\redo01.log'' ";8> SQL "alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo02.log'' to ''e:\orcl\oradata\redo02.log'' ";9> SQL "alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo03.log'' to ''e:\orcl\oradata\redo03.log'' ";10> restore database;11> switch datafile all;12> recover database;13> }executing command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEsql statement: alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo01.log'' to ''e:\orcl\oradata\redo01.log''sql statement: alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo02.log'' to ''e:\orcl\oradata\redo02.log''sql statement: alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo03.log'' to ''e:\orcl\oradata\redo03.log''Starting restore at 10-MAY-08allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=159 devtype=DISKchannel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to E:\ORCL\ORADATA\SYSTEM01.DBFrestoring datafile 00002 to E:\ORCL\ORADATA\UNDOTBS01.DBFrestoring datafile 00003 to E:\ORCL\ORADATA\SYSAUX01.DBFrestoring datafile 00004 to E:\ORCL\ORADATA\USERS01.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=E:\BACKUP\05JG23BO_1_1 tag=TAG20080510T202008channel ORA_DISK_1: restore completeFinished restore at 10-MAY-08datafile 1 switched to datafile copyinput datafilecopy recid=5 stamp=654381217 filename=E:\ORCL\ORADATA\SYSTEM01.DBFdatafile 2 switched to datafile copyinput datafilecopy recid=6 stamp=654381217 filename=E:\ORCL\ORADATA\UNDOTBS01.DBFdatafile 3 switched to datafile copyinput datafilecopy recid=7 stamp=654381217 filename=E:\ORCL\ORADATA\SYSAUX01.DBFdatafile 4 switched to datafile copyinput datafilecopy recid=8 stamp=654381217 filename=E:\ORCL\ORADATA\USERS01.DBFStarting recover at 10-MAY-08using channel ORA_DISK_1starting media recoveryunable to find archive logarchive log thread=1 sequence=8RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 05/10/2008 20:33:39RMAN-06054: media recovery requesting unknown log: thread 1 seq 8 lowscn 327780RMAN> run2> {3> SQL 'alter database open resetlogs';4> }sql statement: alter database open resetlogs11. All datafiles and redologs file switch perfectly :)
SQL> select file#,name from v$datafile2 union3 select null,member from v$logfile;FILE# NAME---------- ------------------------------------------------- 1 E:\ORCL\ORADATA\SYSTEM01.DBF2 E:\ORCL\ORADATA\UNDOTBS01.DBF3 E:\ORCL\ORADATA\SYSAUX01.DBF4 E:\ORCL\ORADATA\USERS01.DBFE:\ORCL\ORADATA\REDO01.LOGE:\ORCL\ORADATA\REDO02.LOGE:\ORCL\ORADATA\REDO03.LOG7 rows selected.
12. Create SPFILE from PFILE
SQL> create spfile from pfile='e:\backup\init.ora';File created.
RMAN versus User Managed Backup-Part-I
We have two ONLINE backup options for taking Oracle database backupRMANUser ManagedBoth options are needed ARCHIVELOG mode to take ONLINE database backup.But why Oracle recommended using RMAN to take oracle database backup?Because there is too many other option for database backup which are not exist in User Managed or if I say we can’t use those options with User Managed backup.Many of newbie is scare about RMAN (Recovery Manager) backup procedure and thinking it is too complicated but actually it is not.How we take ONLINE database backup through User Managed method?SQL> alter database begin backup;Database altered.SQL> ---copy all datafiles to backup locationSQL> alter database end backup;Database altered.SQL> --switch logfileSQL> alter system switch logfile;SQL> --create controlfile backupSQL> alter database backup controlfile to 'e:\backup\control01.ctl';Database altered.SQL> --create spfile backupSQL> create pfile='e:\backup\init.ora' from spfile;File created.NOW user managed backup is complete.For above 5 steps we need to create scripts and run it.We can also use above 5 steps through RMAN with single command and take database backupRMAN> configure controlfile autobackup on;RMAN> configure controlfile autobackup format for device type disk to 'e:\backup\%F';RMAN> run2> {3> backup database format 'e:\backup\%U';4> SQL 'alter system switch logfile';5> backup archivelog all delete input format 'e:\backup\%U';6> }Now RMAN backup is complete.
Just above command will take database backup and also DELETE all archivelog file which are backed up.Advantage of RMAN
We no need to COPY data files manually to backup location
We can directly take/transfer backup on TAPE drive.
We don’t put database in backup mode
We can take backup in single RUN command
RMAN is fast then User Managed backup
RMAN also take backup of archivelog and delete all archivelog which backed up.
We no need to take backup of spfile or controlfile separately RMAN will take backup of both.
RMAN versus User Managed Backup-Part-II
In PART-I we learn how to take backup of oracle database using User Managed or RMAN.Now backup is useful in case of any failure if backup is VALID to restore means we need to also verify database backup.For this thing oracle will provide utility “dbverify” which check physical corruption online or offline datafiles backup set.So first we check User Managed backup through dbv verify utility which was taken in PART-I.E:\backup>dbv file=system01.dbf feedback=10000DBVERIFY: Release 10.1.0.2.0 - Production on Sat May 10 16:46:15 2008Copyright (c) 1982, 2004, Oracle. All rights reserved.DBVERIFY - Verification starting: FILE = system01.dbf......DBVERIFY - Verification completeTotal Pages Examined : 56320Total Pages Processed (Data) : 33386Total Pages Failing (Data) : 0Total Pages Processed (Index): 6077Total Pages Failing (Index): 0Total Pages Processed (Other): 1524Total Pages Processed (Seg) : 0Total Pages Failing (Seg) : 0Total Pages Empty : 15333Total Pages Marked Corrupt : 0Total Pages Influx : 0NOTE: we can check all files like above one by oneNow User Managed backup testing is complete.For RMAN oracle provide database validate command to check database or archivelog file backup set after backup.We can not use DBVerify utility for RMAN backup so that RMAN will provide VALIDATE command with BACKUP or RESTORE for physical or logical corruption in backupset or ONLINE database files.RMAN> run2> {3> allocate channel c1 device type disk format 'e:\backup\%U';4> restore validate check logical database archivelog all;5> }allocated channel: c1channel c1: sid=147 devtype=DISKStarting restore at 10-MAY-08channel c1: starting validation of datafile backupsetchannel c1: restored backup piece 1piece handle=E:\BACKUP\01JG1QT1_1_1 tag=TAG20080510T175545channel c1: validation completechannel c1: starting validation of archive log backupsetchannel c1: restored backup piece 1piece handle=E:\BACKUP\03JG1QTV_1_1 tag=TAG20080510T175615channel c1: validation completeFinished restore at 10-MAY-08released channel: c1NOTE: check logical keyword check logical corruption in backupset.If we want to check ONLINE database files for physical or logical corruption so we can use backup keyword instead of restore.RMAN> run2> {3> backup validate check logical database archivelog all;4> }Starting backup at 10-MAY-08allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=147 devtype=DISKchannel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBFinput datafile fno=00003 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBFinput datafile fno=00005 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBFinput datafile fno=00002 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBFinput datafile fno=00004 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBFchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:07channel ORA_DISK_1: starting archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1 sequence=9 recid=3 stamp=654372512channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 10-MAY-08Read more about Database block corruption In above utility we just check physical or logical database corruption of backup but best practice is restore and recover database backup to check completely because suppose if disaster happen and we lost everything then how we can rebuild database from backup set.So In this Part, I will show you how to restore backup was taken by User managed or RMAN
User managed backup restore and recover procedure from backup.1. Create file directory structure for datafiles,tracefiles from alertlog file location
2. Create oracle services through oradimC:\>oradim -new -sid ORCL -startmode aInstance created.3. Create password file if database authentication method is passwordfileC:\>orapwd file=d:\oracle\product\10.1.0\db_1\database\pwdorcl.ora password=oracle entries=5 force=y4. Connect to sqlplus with sysdba privilegesC:\>sqlplus sys/oracle as sysdbaSQL*Plus: Release 10.1.0.2.0 - Production on Sat May 10 16:59:57 2008Copyright (c) 1982, 2004, Oracle. All rights reserved.Connected to an idle instance.5. Create spfile from backup of pfileSQL> create spfile from pfile='e:\backup\init.ora';File created.6. startup database with nomount stageSQL> startup nomountORACLE instance started.Total System Global Area 293601280 bytesFixed Size 788572 bytesVariable Size 98826148 bytesDatabase Buffers 192937984 bytesRedo Buffers 1048576 bytes7. restore controlfile from backup and mount the databaseSQL> alter database mount;Database altered.8. restore all datafiles and archivelog file from backup and perform incomplete recovery with UNTIL CANCEL clauseSQL> set autorecovery onSQL> recover database using backup controlfile until cancel;open database with resetlogsSQL> alter database open resetlogs;RMAN restore and recover procedure from backup.1. Create file directory structure for datafiles or trace file from alertlog2. Create Oracle Service from oradim utilityC:\>oradim -new -sid ORCLInstance created. 3. Create password file4. Connect to RMAN with SYS userC:\>rman target=sysRecovery Manager: Release 10.1.0.2.0 - ProductionCopyright (c) 1995, 2004, Oracle. All rights reserved.target database Password:connected to target database (not started)5. Set DBID and restore controlfile or spfile from autobackupNOTE: you can check DBID from auto controlfile name in this scenario controlfile name is ‘e:\backup\ C-1182151585-20080510-01RMAN> set DBID=1182151585executing command: SET DBIDRMAN> set DBID=1182151585executing command: SET DBIDRMAN> startup nomount force;startup failed: ORA-01078: failure in processing system parametersLRM-00109: could not open parameter file 'D:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\INITORCL.ORA'trying to start the Oracle instance without parameter files ...Oracle instance startedTotal System Global Area 113246208 bytesFixed Size 787708 bytesVariable Size 61078276 bytesDatabase Buffers 50331648 bytesRedo Buffers 1048576 bytes6. Restore SPFILE from backupRMAN> restore spfile to pfile 'e:\backup\init.ora' from 'e:\backup\C-1182151585-20080510-01';Starting restore at 10-MAY-08using target database controlfile instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=29 devtype=DISKchannel ORA_DISK_1: autobackup found: e:\backup\C-1182151585-20080510-01channel ORA_DISK_1: SPFILE restore from autobackup completeFinished restore at 10-MAY-087. Now shutdown the database and startup and create spfileRMAN> startup nomount pfile 'e:\backup\init.ora';connected to target database (not started)Oracle instance startedTotal System Global Area 293601280 bytesFixed Size 788572 bytesVariable Size 94631844 bytesDatabase Buffers 197132288 bytesRedo Buffers 1048576 bytes8. Restore controlfile from backup and mount the databaseRMAN> restore controlfile from 'e:\backup\C-1182151585-20080510-01';Starting restore at 10-MAY-08allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=160 devtype=DISKchannel ORA_DISK_1: restoring controlfilechannel ORA_DISK_1: restore completeoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL01.CTLoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL02.CTLoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL03.CTLFinished restore at 10-MAY-08RMAN> alter database mount;database mountedreleased channel: ORA_DISK_19. If File directories system is different then original then you need to rename or set newname for all datafiles or redologs.
SQL> column name format a55
SQL> select file#,name from v$datafile
2 union
3 select null,member from v$logfile;
FILE# NAME
---------- -------------------------------------------------1 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF
2 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBF
3 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBF
4 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO01.LOG D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO02.LOG D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO03.LOG
7 rows selected.
10. Recover and open database with resetlogs
RMAN> run2> {3> set newname for datafile 1 to 'e:\orcl\oradata\system01.dbf';4> set newname for datafile 2 to 'e:\orcl\oradata\undotbs01.dbf';5> set newname for datafile 3 to 'e:\orcl\oradata\sysaux01.dbf';6> set newname for datafile 4 to 'e:\orcl\oradata\users01.dbf';7> SQL "alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo01.log'' to ''e:\orcl\oradata\redo01.log'' ";8> SQL "alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo02.log'' to ''e:\orcl\oradata\redo02.log'' ";9> SQL "alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo03.log'' to ''e:\orcl\oradata\redo03.log'' ";10> restore database;11> switch datafile all;12> recover database;13> }executing command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEsql statement: alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo01.log'' to ''e:\orcl\oradata\redo01.log''sql statement: alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo02.log'' to ''e:\orcl\oradata\redo02.log''sql statement: alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo03.log'' to ''e:\orcl\oradata\redo03.log''Starting restore at 10-MAY-08allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=159 devtype=DISKchannel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to E:\ORCL\ORADATA\SYSTEM01.DBFrestoring datafile 00002 to E:\ORCL\ORADATA\UNDOTBS01.DBFrestoring datafile 00003 to E:\ORCL\ORADATA\SYSAUX01.DBFrestoring datafile 00004 to E:\ORCL\ORADATA\USERS01.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=E:\BACKUP\05JG23BO_1_1 tag=TAG20080510T202008channel ORA_DISK_1: restore completeFinished restore at 10-MAY-08datafile 1 switched to datafile copyinput datafilecopy recid=5 stamp=654381217 filename=E:\ORCL\ORADATA\SYSTEM01.DBFdatafile 2 switched to datafile copyinput datafilecopy recid=6 stamp=654381217 filename=E:\ORCL\ORADATA\UNDOTBS01.DBFdatafile 3 switched to datafile copyinput datafilecopy recid=7 stamp=654381217 filename=E:\ORCL\ORADATA\SYSAUX01.DBFdatafile 4 switched to datafile copyinput datafilecopy recid=8 stamp=654381217 filename=E:\ORCL\ORADATA\USERS01.DBFStarting recover at 10-MAY-08using channel ORA_DISK_1starting media recoveryunable to find archive logarchive log thread=1 sequence=8RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 05/10/2008 20:33:39RMAN-06054: media recovery requesting unknown log: thread 1 seq 8 lowscn 327780RMAN> run2> {3> SQL 'alter database open resetlogs';4> }sql statement: alter database open resetlogs11. All datafiles and redologs file switch perfectly :)
SQL> select file#,name from v$datafile2 union3 select null,member from v$logfile;FILE# NAME---------- ------------------------------------------------- 1 E:\ORCL\ORADATA\SYSTEM01.DBF2 E:\ORCL\ORADATA\UNDOTBS01.DBF3 E:\ORCL\ORADATA\SYSAUX01.DBF4 E:\ORCL\ORADATA\USERS01.DBFE:\ORCL\ORADATA\REDO01.LOGE:\ORCL\ORADATA\REDO02.LOGE:\ORCL\ORADATA\REDO03.LOG7 rows selected.
12. Create SPFILE from PFILE
SQL> create spfile from pfile='e:\backup\init.ora';File created.
We have two ONLINE backup options for taking Oracle database backupRMANUser ManagedBoth options are needed ARCHIVELOG mode to take ONLINE database backup.But why Oracle recommended using RMAN to take oracle database backup?Because there is too many other option for database backup which are not exist in User Managed or if I say we can’t use those options with User Managed backup.Many of newbie is scare about RMAN (Recovery Manager) backup procedure and thinking it is too complicated but actually it is not.How we take ONLINE database backup through User Managed method?SQL> alter database begin backup;Database altered.SQL> ---copy all datafiles to backup locationSQL> alter database end backup;Database altered.SQL> --switch logfileSQL> alter system switch logfile;SQL> --create controlfile backupSQL> alter database backup controlfile to 'e:\backup\control01.ctl';Database altered.SQL> --create spfile backupSQL> create pfile='e:\backup\init.ora' from spfile;File created.NOW user managed backup is complete.For above 5 steps we need to create scripts and run it.We can also use above 5 steps through RMAN with single command and take database backupRMAN> configure controlfile autobackup on;RMAN> configure controlfile autobackup format for device type disk to 'e:\backup\%F';RMAN> run2> {3> backup database format 'e:\backup\%U';4> SQL 'alter system switch logfile';5> backup archivelog all delete input format 'e:\backup\%U';6> }Now RMAN backup is complete.
Just above command will take database backup and also DELETE all archivelog file which are backed up.Advantage of RMAN
We no need to COPY data files manually to backup location
We can directly take/transfer backup on TAPE drive.
We don’t put database in backup mode
We can take backup in single RUN command
RMAN is fast then User Managed backup
RMAN also take backup of archivelog and delete all archivelog which backed up.
We no need to take backup of spfile or controlfile separately RMAN will take backup of both.
RMAN versus User Managed Backup-Part-II
In PART-I we learn how to take backup of oracle database using User Managed or RMAN.Now backup is useful in case of any failure if backup is VALID to restore means we need to also verify database backup.For this thing oracle will provide utility “dbverify” which check physical corruption online or offline datafiles backup set.So first we check User Managed backup through dbv verify utility which was taken in PART-I.E:\backup>dbv file=system01.dbf feedback=10000DBVERIFY: Release 10.1.0.2.0 - Production on Sat May 10 16:46:15 2008Copyright (c) 1982, 2004, Oracle. All rights reserved.DBVERIFY - Verification starting: FILE = system01.dbf......DBVERIFY - Verification completeTotal Pages Examined : 56320Total Pages Processed (Data) : 33386Total Pages Failing (Data) : 0Total Pages Processed (Index): 6077Total Pages Failing (Index): 0Total Pages Processed (Other): 1524Total Pages Processed (Seg) : 0Total Pages Failing (Seg) : 0Total Pages Empty : 15333Total Pages Marked Corrupt : 0Total Pages Influx : 0NOTE: we can check all files like above one by oneNow User Managed backup testing is complete.For RMAN oracle provide database validate command to check database or archivelog file backup set after backup.We can not use DBVerify utility for RMAN backup so that RMAN will provide VALIDATE command with BACKUP or RESTORE for physical or logical corruption in backupset or ONLINE database files.RMAN> run2> {3> allocate channel c1 device type disk format 'e:\backup\%U';4> restore validate check logical database archivelog all;5> }allocated channel: c1channel c1: sid=147 devtype=DISKStarting restore at 10-MAY-08channel c1: starting validation of datafile backupsetchannel c1: restored backup piece 1piece handle=E:\BACKUP\01JG1QT1_1_1 tag=TAG20080510T175545channel c1: validation completechannel c1: starting validation of archive log backupsetchannel c1: restored backup piece 1piece handle=E:\BACKUP\03JG1QTV_1_1 tag=TAG20080510T175615channel c1: validation completeFinished restore at 10-MAY-08released channel: c1NOTE: check logical keyword check logical corruption in backupset.If we want to check ONLINE database files for physical or logical corruption so we can use backup keyword instead of restore.RMAN> run2> {3> backup validate check logical database archivelog all;4> }Starting backup at 10-MAY-08allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=147 devtype=DISKchannel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBFinput datafile fno=00003 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBFinput datafile fno=00005 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBFinput datafile fno=00002 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBFinput datafile fno=00004 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBFchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:07channel ORA_DISK_1: starting archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1 sequence=9 recid=3 stamp=654372512channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 10-MAY-08Read more about Database block corruption In above utility we just check physical or logical database corruption of backup but best practice is restore and recover database backup to check completely because suppose if disaster happen and we lost everything then how we can rebuild database from backup set.So In this Part, I will show you how to restore backup was taken by User managed or RMAN
User managed backup restore and recover procedure from backup.1. Create file directory structure for datafiles,tracefiles from alertlog file location
2. Create oracle services through oradimC:\>oradim -new -sid ORCL -startmode aInstance created.3. Create password file if database authentication method is passwordfileC:\>orapwd file=d:\oracle\product\10.1.0\db_1\database\pwdorcl.ora password=oracle entries=5 force=y4. Connect to sqlplus with sysdba privilegesC:\>sqlplus sys/oracle as sysdbaSQL*Plus: Release 10.1.0.2.0 - Production on Sat May 10 16:59:57 2008Copyright (c) 1982, 2004, Oracle. All rights reserved.Connected to an idle instance.5. Create spfile from backup of pfileSQL> create spfile from pfile='e:\backup\init.ora';File created.6. startup database with nomount stageSQL> startup nomountORACLE instance started.Total System Global Area 293601280 bytesFixed Size 788572 bytesVariable Size 98826148 bytesDatabase Buffers 192937984 bytesRedo Buffers 1048576 bytes7. restore controlfile from backup and mount the databaseSQL> alter database mount;Database altered.8. restore all datafiles and archivelog file from backup and perform incomplete recovery with UNTIL CANCEL clauseSQL> set autorecovery onSQL> recover database using backup controlfile until cancel;open database with resetlogsSQL> alter database open resetlogs;RMAN restore and recover procedure from backup.1. Create file directory structure for datafiles or trace file from alertlog2. Create Oracle Service from oradim utilityC:\>oradim -new -sid ORCLInstance created. 3. Create password file4. Connect to RMAN with SYS userC:\>rman target=sysRecovery Manager: Release 10.1.0.2.0 - ProductionCopyright (c) 1995, 2004, Oracle. All rights reserved.target database Password:connected to target database (not started)5. Set DBID and restore controlfile or spfile from autobackupNOTE: you can check DBID from auto controlfile name in this scenario controlfile name is ‘e:\backup\ C-1182151585-20080510-01RMAN> set DBID=1182151585executing command: SET DBIDRMAN> set DBID=1182151585executing command: SET DBIDRMAN> startup nomount force;startup failed: ORA-01078: failure in processing system parametersLRM-00109: could not open parameter file 'D:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\INITORCL.ORA'trying to start the Oracle instance without parameter files ...Oracle instance startedTotal System Global Area 113246208 bytesFixed Size 787708 bytesVariable Size 61078276 bytesDatabase Buffers 50331648 bytesRedo Buffers 1048576 bytes6. Restore SPFILE from backupRMAN> restore spfile to pfile 'e:\backup\init.ora' from 'e:\backup\C-1182151585-20080510-01';Starting restore at 10-MAY-08using target database controlfile instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=29 devtype=DISKchannel ORA_DISK_1: autobackup found: e:\backup\C-1182151585-20080510-01channel ORA_DISK_1: SPFILE restore from autobackup completeFinished restore at 10-MAY-087. Now shutdown the database and startup and create spfileRMAN> startup nomount pfile 'e:\backup\init.ora';connected to target database (not started)Oracle instance startedTotal System Global Area 293601280 bytesFixed Size 788572 bytesVariable Size 94631844 bytesDatabase Buffers 197132288 bytesRedo Buffers 1048576 bytes8. Restore controlfile from backup and mount the databaseRMAN> restore controlfile from 'e:\backup\C-1182151585-20080510-01';Starting restore at 10-MAY-08allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=160 devtype=DISKchannel ORA_DISK_1: restoring controlfilechannel ORA_DISK_1: restore completeoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL01.CTLoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL02.CTLoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL03.CTLFinished restore at 10-MAY-08RMAN> alter database mount;database mountedreleased channel: ORA_DISK_19. If File directories system is different then original then you need to rename or set newname for all datafiles or redologs.
SQL> column name format a55
SQL> select file#,name from v$datafile
2 union
3 select null,member from v$logfile;
FILE# NAME
---------- -------------------------------------------------1 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF
2 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBF
3 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBF
4 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO01.LOG D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO02.LOG D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO03.LOG
7 rows selected.
10. Recover and open database with resetlogs
RMAN> run2> {3> set newname for datafile 1 to 'e:\orcl\oradata\system01.dbf';4> set newname for datafile 2 to 'e:\orcl\oradata\undotbs01.dbf';5> set newname for datafile 3 to 'e:\orcl\oradata\sysaux01.dbf';6> set newname for datafile 4 to 'e:\orcl\oradata\users01.dbf';7> SQL "alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo01.log'' to ''e:\orcl\oradata\redo01.log'' ";8> SQL "alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo02.log'' to ''e:\orcl\oradata\redo02.log'' ";9> SQL "alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo03.log'' to ''e:\orcl\oradata\redo03.log'' ";10> restore database;11> switch datafile all;12> recover database;13> }executing command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEsql statement: alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo01.log'' to ''e:\orcl\oradata\redo01.log''sql statement: alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo02.log'' to ''e:\orcl\oradata\redo02.log''sql statement: alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo03.log'' to ''e:\orcl\oradata\redo03.log''Starting restore at 10-MAY-08allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=159 devtype=DISKchannel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to E:\ORCL\ORADATA\SYSTEM01.DBFrestoring datafile 00002 to E:\ORCL\ORADATA\UNDOTBS01.DBFrestoring datafile 00003 to E:\ORCL\ORADATA\SYSAUX01.DBFrestoring datafile 00004 to E:\ORCL\ORADATA\USERS01.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=E:\BACKUP\05JG23BO_1_1 tag=TAG20080510T202008channel ORA_DISK_1: restore completeFinished restore at 10-MAY-08datafile 1 switched to datafile copyinput datafilecopy recid=5 stamp=654381217 filename=E:\ORCL\ORADATA\SYSTEM01.DBFdatafile 2 switched to datafile copyinput datafilecopy recid=6 stamp=654381217 filename=E:\ORCL\ORADATA\UNDOTBS01.DBFdatafile 3 switched to datafile copyinput datafilecopy recid=7 stamp=654381217 filename=E:\ORCL\ORADATA\SYSAUX01.DBFdatafile 4 switched to datafile copyinput datafilecopy recid=8 stamp=654381217 filename=E:\ORCL\ORADATA\USERS01.DBFStarting recover at 10-MAY-08using channel ORA_DISK_1starting media recoveryunable to find archive logarchive log thread=1 sequence=8RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 05/10/2008 20:33:39RMAN-06054: media recovery requesting unknown log: thread 1 seq 8 lowscn 327780RMAN> run2> {3> SQL 'alter database open resetlogs';4> }sql statement: alter database open resetlogs11. All datafiles and redologs file switch perfectly :)
SQL> select file#,name from v$datafile2 union3 select null,member from v$logfile;FILE# NAME---------- ------------------------------------------------- 1 E:\ORCL\ORADATA\SYSTEM01.DBF2 E:\ORCL\ORADATA\UNDOTBS01.DBF3 E:\ORCL\ORADATA\SYSAUX01.DBF4 E:\ORCL\ORADATA\USERS01.DBFE:\ORCL\ORADATA\REDO01.LOGE:\ORCL\ORADATA\REDO02.LOGE:\ORCL\ORADATA\REDO03.LOG7 rows selected.
12. Create SPFILE from PFILE
SQL> create spfile from pfile='e:\backup\init.ora';File created.
RMAN versus User Managed Backup-Part-I
We have two ONLINE backup options for taking Oracle database backupRMANUser ManagedBoth options are needed ARCHIVELOG mode to take ONLINE database backup.But why Oracle recommended using RMAN to take oracle database backup?Because there is too many other option for database backup which are not exist in User Managed or if I say we can’t use those options with User Managed backup.Many of newbie is scare about RMAN (Recovery Manager) backup procedure and thinking it is too complicated but actually it is not.How we take ONLINE database backup through User Managed method?SQL> alter database begin backup;Database altered.SQL> ---copy all datafiles to backup locationSQL> alter database end backup;Database altered.SQL> --switch logfileSQL> alter system switch logfile;SQL> --create controlfile backupSQL> alter database backup controlfile to 'e:\backup\control01.ctl';Database altered.SQL> --create spfile backupSQL> create pfile='e:\backup\init.ora' from spfile;File created.NOW user managed backup is complete.For above 5 steps we need to create scripts and run it.We can also use above 5 steps through RMAN with single command and take database backupRMAN> configure controlfile autobackup on;RMAN> configure controlfile autobackup format for device type disk to 'e:\backup\%F';RMAN> run2> {3> backup database format 'e:\backup\%U';4> SQL 'alter system switch logfile';5> backup archivelog all delete input format 'e:\backup\%U';6> }Now RMAN backup is complete.
Just above command will take database backup and also DELETE all archivelog file which are backed up.Advantage of RMAN
We no need to COPY data files manually to backup location
We can directly take/transfer backup on TAPE drive.
We don’t put database in backup mode
We can take backup in single RUN command
RMAN is fast then User Managed backup
RMAN also take backup of archivelog and delete all archivelog which backed up.
We no need to take backup of spfile or controlfile separately RMAN will take backup of both.
RMAN versus User Managed Backup-Part-II
In PART-I we learn how to take backup of oracle database using User Managed or RMAN.Now backup is useful in case of any failure if backup is VALID to restore means we need to also verify database backup.For this thing oracle will provide utility “dbverify” which check physical corruption online or offline datafiles backup set.So first we check User Managed backup through dbv verify utility which was taken in PART-I.E:\backup>dbv file=system01.dbf feedback=10000DBVERIFY: Release 10.1.0.2.0 - Production on Sat May 10 16:46:15 2008Copyright (c) 1982, 2004, Oracle. All rights reserved.DBVERIFY - Verification starting: FILE = system01.dbf......DBVERIFY - Verification completeTotal Pages Examined : 56320Total Pages Processed (Data) : 33386Total Pages Failing (Data) : 0Total Pages Processed (Index): 6077Total Pages Failing (Index): 0Total Pages Processed (Other): 1524Total Pages Processed (Seg) : 0Total Pages Failing (Seg) : 0Total Pages Empty : 15333Total Pages Marked Corrupt : 0Total Pages Influx : 0NOTE: we can check all files like above one by oneNow User Managed backup testing is complete.For RMAN oracle provide database validate command to check database or archivelog file backup set after backup.We can not use DBVerify utility for RMAN backup so that RMAN will provide VALIDATE command with BACKUP or RESTORE for physical or logical corruption in backupset or ONLINE database files.RMAN> run2> {3> allocate channel c1 device type disk format 'e:\backup\%U';4> restore validate check logical database archivelog all;5> }allocated channel: c1channel c1: sid=147 devtype=DISKStarting restore at 10-MAY-08channel c1: starting validation of datafile backupsetchannel c1: restored backup piece 1piece handle=E:\BACKUP\01JG1QT1_1_1 tag=TAG20080510T175545channel c1: validation completechannel c1: starting validation of archive log backupsetchannel c1: restored backup piece 1piece handle=E:\BACKUP\03JG1QTV_1_1 tag=TAG20080510T175615channel c1: validation completeFinished restore at 10-MAY-08released channel: c1NOTE: check logical keyword check logical corruption in backupset.If we want to check ONLINE database files for physical or logical corruption so we can use backup keyword instead of restore.RMAN> run2> {3> backup validate check logical database archivelog all;4> }Starting backup at 10-MAY-08allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=147 devtype=DISKchannel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBFinput datafile fno=00003 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBFinput datafile fno=00005 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBFinput datafile fno=00002 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBFinput datafile fno=00004 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBFchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:07channel ORA_DISK_1: starting archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1 sequence=9 recid=3 stamp=654372512channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 10-MAY-08Read more about Database block corruption In above utility we just check physical or logical database corruption of backup but best practice is restore and recover database backup to check completely because suppose if disaster happen and we lost everything then how we can rebuild database from backup set.So In this Part, I will show you how to restore backup was taken by User managed or RMAN
User managed backup restore and recover procedure from backup.1. Create file directory structure for datafiles,tracefiles from alertlog file location
2. Create oracle services through oradimC:\>oradim -new -sid ORCL -startmode aInstance created.3. Create password file if database authentication method is passwordfileC:\>orapwd file=d:\oracle\product\10.1.0\db_1\database\pwdorcl.ora password=oracle entries=5 force=y4. Connect to sqlplus with sysdba privilegesC:\>sqlplus sys/oracle as sysdbaSQL*Plus: Release 10.1.0.2.0 - Production on Sat May 10 16:59:57 2008Copyright (c) 1982, 2004, Oracle. All rights reserved.Connected to an idle instance.5. Create spfile from backup of pfileSQL> create spfile from pfile='e:\backup\init.ora';File created.6. startup database with nomount stageSQL> startup nomountORACLE instance started.Total System Global Area 293601280 bytesFixed Size 788572 bytesVariable Size 98826148 bytesDatabase Buffers 192937984 bytesRedo Buffers 1048576 bytes7. restore controlfile from backup and mount the databaseSQL> alter database mount;Database altered.8. restore all datafiles and archivelog file from backup and perform incomplete recovery with UNTIL CANCEL clauseSQL> set autorecovery onSQL> recover database using backup controlfile until cancel;open database with resetlogsSQL> alter database open resetlogs;RMAN restore and recover procedure from backup.1. Create file directory structure for datafiles or trace file from alertlog2. Create Oracle Service from oradim utilityC:\>oradim -new -sid ORCLInstance created. 3. Create password file4. Connect to RMAN with SYS userC:\>rman target=sysRecovery Manager: Release 10.1.0.2.0 - ProductionCopyright (c) 1995, 2004, Oracle. All rights reserved.target database Password:connected to target database (not started)5. Set DBID and restore controlfile or spfile from autobackupNOTE: you can check DBID from auto controlfile name in this scenario controlfile name is ‘e:\backup\ C-1182151585-20080510-01RMAN> set DBID=1182151585executing command: SET DBIDRMAN> set DBID=1182151585executing command: SET DBIDRMAN> startup nomount force;startup failed: ORA-01078: failure in processing system parametersLRM-00109: could not open parameter file 'D:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\INITORCL.ORA'trying to start the Oracle instance without parameter files ...Oracle instance startedTotal System Global Area 113246208 bytesFixed Size 787708 bytesVariable Size 61078276 bytesDatabase Buffers 50331648 bytesRedo Buffers 1048576 bytes6. Restore SPFILE from backupRMAN> restore spfile to pfile 'e:\backup\init.ora' from 'e:\backup\C-1182151585-20080510-01';Starting restore at 10-MAY-08using target database controlfile instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=29 devtype=DISKchannel ORA_DISK_1: autobackup found: e:\backup\C-1182151585-20080510-01channel ORA_DISK_1: SPFILE restore from autobackup completeFinished restore at 10-MAY-087. Now shutdown the database and startup and create spfileRMAN> startup nomount pfile 'e:\backup\init.ora';connected to target database (not started)Oracle instance startedTotal System Global Area 293601280 bytesFixed Size 788572 bytesVariable Size 94631844 bytesDatabase Buffers 197132288 bytesRedo Buffers 1048576 bytes8. Restore controlfile from backup and mount the databaseRMAN> restore controlfile from 'e:\backup\C-1182151585-20080510-01';Starting restore at 10-MAY-08allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=160 devtype=DISKchannel ORA_DISK_1: restoring controlfilechannel ORA_DISK_1: restore completeoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL01.CTLoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL02.CTLoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL03.CTLFinished restore at 10-MAY-08RMAN> alter database mount;database mountedreleased channel: ORA_DISK_19. If File directories system is different then original then you need to rename or set newname for all datafiles or redologs.
SQL> column name format a55
SQL> select file#,name from v$datafile
2 union
3 select null,member from v$logfile;
FILE# NAME
---------- -------------------------------------------------1 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF
2 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBF
3 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBF
4 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO01.LOG D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO02.LOG D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO03.LOG
7 rows selected.
10. Recover and open database with resetlogs
RMAN> run2> {3> set newname for datafile 1 to 'e:\orcl\oradata\system01.dbf';4> set newname for datafile 2 to 'e:\orcl\oradata\undotbs01.dbf';5> set newname for datafile 3 to 'e:\orcl\oradata\sysaux01.dbf';6> set newname for datafile 4 to 'e:\orcl\oradata\users01.dbf';7> SQL "alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo01.log'' to ''e:\orcl\oradata\redo01.log'' ";8> SQL "alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo02.log'' to ''e:\orcl\oradata\redo02.log'' ";9> SQL "alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo03.log'' to ''e:\orcl\oradata\redo03.log'' ";10> restore database;11> switch datafile all;12> recover database;13> }executing command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEsql statement: alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo01.log'' to ''e:\orcl\oradata\redo01.log''sql statement: alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo02.log'' to ''e:\orcl\oradata\redo02.log''sql statement: alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo03.log'' to ''e:\orcl\oradata\redo03.log''Starting restore at 10-MAY-08allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=159 devtype=DISKchannel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to E:\ORCL\ORADATA\SYSTEM01.DBFrestoring datafile 00002 to E:\ORCL\ORADATA\UNDOTBS01.DBFrestoring datafile 00003 to E:\ORCL\ORADATA\SYSAUX01.DBFrestoring datafile 00004 to E:\ORCL\ORADATA\USERS01.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=E:\BACKUP\05JG23BO_1_1 tag=TAG20080510T202008channel ORA_DISK_1: restore completeFinished restore at 10-MAY-08datafile 1 switched to datafile copyinput datafilecopy recid=5 stamp=654381217 filename=E:\ORCL\ORADATA\SYSTEM01.DBFdatafile 2 switched to datafile copyinput datafilecopy recid=6 stamp=654381217 filename=E:\ORCL\ORADATA\UNDOTBS01.DBFdatafile 3 switched to datafile copyinput datafilecopy recid=7 stamp=654381217 filename=E:\ORCL\ORADATA\SYSAUX01.DBFdatafile 4 switched to datafile copyinput datafilecopy recid=8 stamp=654381217 filename=E:\ORCL\ORADATA\USERS01.DBFStarting recover at 10-MAY-08using channel ORA_DISK_1starting media recoveryunable to find archive logarchive log thread=1 sequence=8RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 05/10/2008 20:33:39RMAN-06054: media recovery requesting unknown log: thread 1 seq 8 lowscn 327780RMAN> run2> {3> SQL 'alter database open resetlogs';4> }sql statement: alter database open resetlogs11. All datafiles and redologs file switch perfectly :)
SQL> select file#,name from v$datafile2 union3 select null,member from v$logfile;FILE# NAME---------- ------------------------------------------------- 1 E:\ORCL\ORADATA\SYSTEM01.DBF2 E:\ORCL\ORADATA\UNDOTBS01.DBF3 E:\ORCL\ORADATA\SYSAUX01.DBF4 E:\ORCL\ORADATA\USERS01.DBFE:\ORCL\ORADATA\REDO01.LOGE:\ORCL\ORADATA\REDO02.LOGE:\ORCL\ORADATA\REDO03.LOG7 rows selected.
12. Create SPFILE from PFILE
SQL> create spfile from pfile='e:\backup\init.ora';File created.
RMAN versus User Managed Backup-Part-I
We have two ONLINE backup options for taking Oracle database backupRMANUser ManagedBoth options are needed ARCHIVELOG mode to take ONLINE database backup.But why Oracle recommended using RMAN to take oracle database backup?Because there is too many other option for database backup which are not exist in User Managed or if I say we can’t use those options with User Managed backup.Many of newbie is scare about RMAN (Recovery Manager) backup procedure and thinking it is too complicated but actually it is not.How we take ONLINE database backup through User Managed method?SQL> alter database begin backup;Database altered.SQL> ---copy all datafiles to backup locationSQL> alter database end backup;Database altered.SQL> --switch logfileSQL> alter system switch logfile;SQL> --create controlfile backupSQL> alter database backup controlfile to 'e:\backup\control01.ctl';Database altered.SQL> --create spfile backupSQL> create pfile='e:\backup\init.ora' from spfile;File created.NOW user managed backup is complete.For above 5 steps we need to create scripts and run it.We can also use above 5 steps through RMAN with single command and take database backupRMAN> configure controlfile autobackup on;RMAN> configure controlfile autobackup format for device type disk to 'e:\backup\%F';RMAN> run2> {3> backup database format 'e:\backup\%U';4> SQL 'alter system switch logfile';5> backup archivelog all delete input format 'e:\backup\%U';6> }Now RMAN backup is complete.
Just above command will take database backup and also DELETE all archivelog file which are backed up.Advantage of RMAN
We no need to COPY data files manually to backup location
We can directly take/transfer backup on TAPE drive.
We don’t put database in backup mode
We can take backup in single RUN command
RMAN is fast then User Managed backup
RMAN also take backup of archivelog and delete all archivelog which backed up.
We no need to take backup of spfile or controlfile separately RMAN will take backup of both.
RMAN versus User Managed Backup-Part-II
In PART-I we learn how to take backup of oracle database using User Managed or RMAN.Now backup is useful in case of any failure if backup is VALID to restore means we need to also verify database backup.For this thing oracle will provide utility “dbverify” which check physical corruption online or offline datafiles backup set.So first we check User Managed backup through dbv verify utility which was taken in PART-I.E:\backup>dbv file=system01.dbf feedback=10000DBVERIFY: Release 10.1.0.2.0 - Production on Sat May 10 16:46:15 2008Copyright (c) 1982, 2004, Oracle. All rights reserved.DBVERIFY - Verification starting: FILE = system01.dbf......DBVERIFY - Verification completeTotal Pages Examined : 56320Total Pages Processed (Data) : 33386Total Pages Failing (Data) : 0Total Pages Processed (Index): 6077Total Pages Failing (Index): 0Total Pages Processed (Other): 1524Total Pages Processed (Seg) : 0Total Pages Failing (Seg) : 0Total Pages Empty : 15333Total Pages Marked Corrupt : 0Total Pages Influx : 0NOTE: we can check all files like above one by oneNow User Managed backup testing is complete.For RMAN oracle provide database validate command to check database or archivelog file backup set after backup.We can not use DBVerify utility for RMAN backup so that RMAN will provide VALIDATE command with BACKUP or RESTORE for physical or logical corruption in backupset or ONLINE database files.RMAN> run2> {3> allocate channel c1 device type disk format 'e:\backup\%U';4> restore validate check logical database archivelog all;5> }allocated channel: c1channel c1: sid=147 devtype=DISKStarting restore at 10-MAY-08channel c1: starting validation of datafile backupsetchannel c1: restored backup piece 1piece handle=E:\BACKUP\01JG1QT1_1_1 tag=TAG20080510T175545channel c1: validation completechannel c1: starting validation of archive log backupsetchannel c1: restored backup piece 1piece handle=E:\BACKUP\03JG1QTV_1_1 tag=TAG20080510T175615channel c1: validation completeFinished restore at 10-MAY-08released channel: c1NOTE: check logical keyword check logical corruption in backupset.If we want to check ONLINE database files for physical or logical corruption so we can use backup keyword instead of restore.RMAN> run2> {3> backup validate check logical database archivelog all;4> }Starting backup at 10-MAY-08allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=147 devtype=DISKchannel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBFinput datafile fno=00003 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBFinput datafile fno=00005 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBFinput datafile fno=00002 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBFinput datafile fno=00004 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBFchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:07channel ORA_DISK_1: starting archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1 sequence=9 recid=3 stamp=654372512channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 10-MAY-08Read more about Database block corruption In above utility we just check physical or logical database corruption of backup but best practice is restore and recover database backup to check completely because suppose if disaster happen and we lost everything then how we can rebuild database from backup set.So In this Part, I will show you how to restore backup was taken by User managed or RMAN
User managed backup restore and recover procedure from backup.1. Create file directory structure for datafiles,tracefiles from alertlog file location
2. Create oracle services through oradimC:\>oradim -new -sid ORCL -startmode aInstance created.3. Create password file if database authentication method is passwordfileC:\>orapwd file=d:\oracle\product\10.1.0\db_1\database\pwdorcl.ora password=oracle entries=5 force=y4. Connect to sqlplus with sysdba privilegesC:\>sqlplus sys/oracle as sysdbaSQL*Plus: Release 10.1.0.2.0 - Production on Sat May 10 16:59:57 2008Copyright (c) 1982, 2004, Oracle. All rights reserved.Connected to an idle instance.5. Create spfile from backup of pfileSQL> create spfile from pfile='e:\backup\init.ora';File created.6. startup database with nomount stageSQL> startup nomountORACLE instance started.Total System Global Area 293601280 bytesFixed Size 788572 bytesVariable Size 98826148 bytesDatabase Buffers 192937984 bytesRedo Buffers 1048576 bytes7. restore controlfile from backup and mount the databaseSQL> alter database mount;Database altered.8. restore all datafiles and archivelog file from backup and perform incomplete recovery with UNTIL CANCEL clauseSQL> set autorecovery onSQL> recover database using backup controlfile until cancel;open database with resetlogsSQL> alter database open resetlogs;RMAN restore and recover procedure from backup.1. Create file directory structure for datafiles or trace file from alertlog2. Create Oracle Service from oradim utilityC:\>oradim -new -sid ORCLInstance created. 3. Create password file4. Connect to RMAN with SYS userC:\>rman target=sysRecovery Manager: Release 10.1.0.2.0 - ProductionCopyright (c) 1995, 2004, Oracle. All rights reserved.target database Password:connected to target database (not started)5. Set DBID and restore controlfile or spfile from autobackupNOTE: you can check DBID from auto controlfile name in this scenario controlfile name is ‘e:\backup\ C-1182151585-20080510-01RMAN> set DBID=1182151585executing command: SET DBIDRMAN> set DBID=1182151585executing command: SET DBIDRMAN> startup nomount force;startup failed: ORA-01078: failure in processing system parametersLRM-00109: could not open parameter file 'D:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\INITORCL.ORA'trying to start the Oracle instance without parameter files ...Oracle instance startedTotal System Global Area 113246208 bytesFixed Size 787708 bytesVariable Size 61078276 bytesDatabase Buffers 50331648 bytesRedo Buffers 1048576 bytes6. Restore SPFILE from backupRMAN> restore spfile to pfile 'e:\backup\init.ora' from 'e:\backup\C-1182151585-20080510-01';Starting restore at 10-MAY-08using target database controlfile instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=29 devtype=DISKchannel ORA_DISK_1: autobackup found: e:\backup\C-1182151585-20080510-01channel ORA_DISK_1: SPFILE restore from autobackup completeFinished restore at 10-MAY-087. Now shutdown the database and startup and create spfileRMAN> startup nomount pfile 'e:\backup\init.ora';connected to target database (not started)Oracle instance startedTotal System Global Area 293601280 bytesFixed Size 788572 bytesVariable Size 94631844 bytesDatabase Buffers 197132288 bytesRedo Buffers 1048576 bytes8. Restore controlfile from backup and mount the databaseRMAN> restore controlfile from 'e:\backup\C-1182151585-20080510-01';Starting restore at 10-MAY-08allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=160 devtype=DISKchannel ORA_DISK_1: restoring controlfilechannel ORA_DISK_1: restore completeoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL01.CTLoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL02.CTLoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL03.CTLFinished restore at 10-MAY-08RMAN> alter database mount;database mountedreleased channel: ORA_DISK_19. If File directories system is different then original then you need to rename or set newname for all datafiles or redologs.
SQL> column name format a55
SQL> select file#,name from v$datafile
2 union
3 select null,member from v$logfile;
FILE# NAME
---------- -------------------------------------------------1 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF
2 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBF
3 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBF
4 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO01.LOG D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO02.LOG D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO03.LOG
7 rows selected.
10. Recover and open database with resetlogs
RMAN> run2> {3> set newname for datafile 1 to 'e:\orcl\oradata\system01.dbf';4> set newname for datafile 2 to 'e:\orcl\oradata\undotbs01.dbf';5> set newname for datafile 3 to 'e:\orcl\oradata\sysaux01.dbf';6> set newname for datafile 4 to 'e:\orcl\oradata\users01.dbf';7> SQL "alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo01.log'' to ''e:\orcl\oradata\redo01.log'' ";8> SQL "alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo02.log'' to ''e:\orcl\oradata\redo02.log'' ";9> SQL "alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo03.log'' to ''e:\orcl\oradata\redo03.log'' ";10> restore database;11> switch datafile all;12> recover database;13> }executing command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEsql statement: alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo01.log'' to ''e:\orcl\oradata\redo01.log''sql statement: alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo02.log'' to ''e:\orcl\oradata\redo02.log''sql statement: alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo03.log'' to ''e:\orcl\oradata\redo03.log''Starting restore at 10-MAY-08allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=159 devtype=DISKchannel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to E:\ORCL\ORADATA\SYSTEM01.DBFrestoring datafile 00002 to E:\ORCL\ORADATA\UNDOTBS01.DBFrestoring datafile 00003 to E:\ORCL\ORADATA\SYSAUX01.DBFrestoring datafile 00004 to E:\ORCL\ORADATA\USERS01.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=E:\BACKUP\05JG23BO_1_1 tag=TAG20080510T202008channel ORA_DISK_1: restore completeFinished restore at 10-MAY-08datafile 1 switched to datafile copyinput datafilecopy recid=5 stamp=654381217 filename=E:\ORCL\ORADATA\SYSTEM01.DBFdatafile 2 switched to datafile copyinput datafilecopy recid=6 stamp=654381217 filename=E:\ORCL\ORADATA\UNDOTBS01.DBFdatafile 3 switched to datafile copyinput datafilecopy recid=7 stamp=654381217 filename=E:\ORCL\ORADATA\SYSAUX01.DBFdatafile 4 switched to datafile copyinput datafilecopy recid=8 stamp=654381217 filename=E:\ORCL\ORADATA\USERS01.DBFStarting recover at 10-MAY-08using channel ORA_DISK_1starting media recoveryunable to find archive logarchive log thread=1 sequence=8RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 05/10/2008 20:33:39RMAN-06054: media recovery requesting unknown log: thread 1 seq 8 lowscn 327780RMAN> run2> {3> SQL 'alter database open resetlogs';4> }sql statement: alter database open resetlogs11. All datafiles and redologs file switch perfectly :)
SQL> select file#,name from v$datafile2 union3 select null,member from v$logfile;FILE# NAME---------- ------------------------------------------------- 1 E:\ORCL\ORADATA\SYSTEM01.DBF2 E:\ORCL\ORADATA\UNDOTBS01.DBF3 E:\ORCL\ORADATA\SYSAUX01.DBF4 E:\ORCL\ORADATA\USERS01.DBFE:\ORCL\ORADATA\REDO01.LOGE:\ORCL\ORADATA\REDO02.LOGE:\ORCL\ORADATA\REDO03.LOG7 rows selected.
12. Create SPFILE from PFILE
SQL> create spfile from pfile='e:\backup\init.ora';File created.
RMAN versus User Managed Backup-Part-I
We have two ONLINE backup options for taking Oracle database backupRMANUser ManagedBoth options are needed ARCHIVELOG mode to take ONLINE database backup.But why Oracle recommended using RMAN to take oracle database backup?Because there is too many other option for database backup which are not exist in User Managed or if I say we can’t use those options with User Managed backup.Many of newbie is scare about RMAN (Recovery Manager) backup procedure and thinking it is too complicated but actually it is not.How we take ONLINE database backup through User Managed method?SQL> alter database begin backup;Database altered.SQL> ---copy all datafiles to backup locationSQL> alter database end backup;Database altered.SQL> --switch logfileSQL> alter system switch logfile;SQL> --create controlfile backupSQL> alter database backup controlfile to 'e:\backup\control01.ctl';Database altered.SQL> --create spfile backupSQL> create pfile='e:\backup\init.ora' from spfile;File created.NOW user managed backup is complete.For above 5 steps we need to create scripts and run it.We can also use above 5 steps through RMAN with single command and take database backupRMAN> configure controlfile autobackup on;RMAN> configure controlfile autobackup format for device type disk to 'e:\backup\%F';RMAN> run2> {3> backup database format 'e:\backup\%U';4> SQL 'alter system switch logfile';5> backup archivelog all delete input format 'e:\backup\%U';6> }Now RMAN backup is complete.
Just above command will take database backup and also DELETE all archivelog file which are backed up.Advantage of RMAN
We no need to COPY data files manually to backup location
We can directly take/transfer backup on TAPE drive.
We don’t put database in backup mode
We can take backup in single RUN command
RMAN is fast then User Managed backup
RMAN also take backup of archivelog and delete all archivelog which backed up.
We no need to take backup of spfile or controlfile separately RMAN will take backup of both.
RMAN versus User Managed Backup-Part-II
In PART-I we learn how to take backup of oracle database using User Managed or RMAN.Now backup is useful in case of any failure if backup is VALID to restore means we need to also verify database backup.For this thing oracle will provide utility “dbverify” which check physical corruption online or offline datafiles backup set.So first we check User Managed backup through dbv verify utility which was taken in PART-I.E:\backup>dbv file=system01.dbf feedback=10000DBVERIFY: Release 10.1.0.2.0 - Production on Sat May 10 16:46:15 2008Copyright (c) 1982, 2004, Oracle. All rights reserved.DBVERIFY - Verification starting: FILE = system01.dbf......DBVERIFY - Verification completeTotal Pages Examined : 56320Total Pages Processed (Data) : 33386Total Pages Failing (Data) : 0Total Pages Processed (Index): 6077Total Pages Failing (Index): 0Total Pages Processed (Other): 1524Total Pages Processed (Seg) : 0Total Pages Failing (Seg) : 0Total Pages Empty : 15333Total Pages Marked Corrupt : 0Total Pages Influx : 0NOTE: we can check all files like above one by oneNow User Managed backup testing is complete.For RMAN oracle provide database validate command to check database or archivelog file backup set after backup.We can not use DBVerify utility for RMAN backup so that RMAN will provide VALIDATE command with BACKUP or RESTORE for physical or logical corruption in backupset or ONLINE database files.RMAN> run2> {3> allocate channel c1 device type disk format 'e:\backup\%U';4> restore validate check logical database archivelog all;5> }allocated channel: c1channel c1: sid=147 devtype=DISKStarting restore at 10-MAY-08channel c1: starting validation of datafile backupsetchannel c1: restored backup piece 1piece handle=E:\BACKUP\01JG1QT1_1_1 tag=TAG20080510T175545channel c1: validation completechannel c1: starting validation of archive log backupsetchannel c1: restored backup piece 1piece handle=E:\BACKUP\03JG1QTV_1_1 tag=TAG20080510T175615channel c1: validation completeFinished restore at 10-MAY-08released channel: c1NOTE: check logical keyword check logical corruption in backupset.If we want to check ONLINE database files for physical or logical corruption so we can use backup keyword instead of restore.RMAN> run2> {3> backup validate check logical database archivelog all;4> }Starting backup at 10-MAY-08allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=147 devtype=DISKchannel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBFinput datafile fno=00003 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBFinput datafile fno=00005 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBFinput datafile fno=00002 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBFinput datafile fno=00004 name=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBFchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:07channel ORA_DISK_1: starting archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1 sequence=9 recid=3 stamp=654372512channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 10-MAY-08Read more about Database block corruption In above utility we just check physical or logical database corruption of backup but best practice is restore and recover database backup to check completely because suppose if disaster happen and we lost everything then how we can rebuild database from backup set.So In this Part, I will show you how to restore backup was taken by User managed or RMAN
User managed backup restore and recover procedure from backup.1. Create file directory structure for datafiles,tracefiles from alertlog file location
2. Create oracle services through oradimC:\>oradim -new -sid ORCL -startmode aInstance created.3. Create password file if database authentication method is passwordfileC:\>orapwd file=d:\oracle\product\10.1.0\db_1\database\pwdorcl.ora password=oracle entries=5 force=y4. Connect to sqlplus with sysdba privilegesC:\>sqlplus sys/oracle as sysdbaSQL*Plus: Release 10.1.0.2.0 - Production on Sat May 10 16:59:57 2008Copyright (c) 1982, 2004, Oracle. All rights reserved.Connected to an idle instance.5. Create spfile from backup of pfileSQL> create spfile from pfile='e:\backup\init.ora';File created.6. startup database with nomount stageSQL> startup nomountORACLE instance started.Total System Global Area 293601280 bytesFixed Size 788572 bytesVariable Size 98826148 bytesDatabase Buffers 192937984 bytesRedo Buffers 1048576 bytes7. restore controlfile from backup and mount the databaseSQL> alter database mount;Database altered.8. restore all datafiles and archivelog file from backup and perform incomplete recovery with UNTIL CANCEL clauseSQL> set autorecovery onSQL> recover database using backup controlfile until cancel;open database with resetlogsSQL> alter database open resetlogs;RMAN restore and recover procedure from backup.1. Create file directory structure for datafiles or trace file from alertlog2. Create Oracle Service from oradim utilityC:\>oradim -new -sid ORCLInstance created. 3. Create password file4. Connect to RMAN with SYS userC:\>rman target=sysRecovery Manager: Release 10.1.0.2.0 - ProductionCopyright (c) 1995, 2004, Oracle. All rights reserved.target database Password:connected to target database (not started)5. Set DBID and restore controlfile or spfile from autobackupNOTE: you can check DBID from auto controlfile name in this scenario controlfile name is ‘e:\backup\ C-1182151585-20080510-01RMAN> set DBID=1182151585executing command: SET DBIDRMAN> set DBID=1182151585executing command: SET DBIDRMAN> startup nomount force;startup failed: ORA-01078: failure in processing system parametersLRM-00109: could not open parameter file 'D:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE\INITORCL.ORA'trying to start the Oracle instance without parameter files ...Oracle instance startedTotal System Global Area 113246208 bytesFixed Size 787708 bytesVariable Size 61078276 bytesDatabase Buffers 50331648 bytesRedo Buffers 1048576 bytes6. Restore SPFILE from backupRMAN> restore spfile to pfile 'e:\backup\init.ora' from 'e:\backup\C-1182151585-20080510-01';Starting restore at 10-MAY-08using target database controlfile instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=29 devtype=DISKchannel ORA_DISK_1: autobackup found: e:\backup\C-1182151585-20080510-01channel ORA_DISK_1: SPFILE restore from autobackup completeFinished restore at 10-MAY-087. Now shutdown the database and startup and create spfileRMAN> startup nomount pfile 'e:\backup\init.ora';connected to target database (not started)Oracle instance startedTotal System Global Area 293601280 bytesFixed Size 788572 bytesVariable Size 94631844 bytesDatabase Buffers 197132288 bytesRedo Buffers 1048576 bytes8. Restore controlfile from backup and mount the databaseRMAN> restore controlfile from 'e:\backup\C-1182151585-20080510-01';Starting restore at 10-MAY-08allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=160 devtype=DISKchannel ORA_DISK_1: restoring controlfilechannel ORA_DISK_1: restore completeoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL01.CTLoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL02.CTLoutput filename=D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL03.CTLFinished restore at 10-MAY-08RMAN> alter database mount;database mountedreleased channel: ORA_DISK_19. If File directories system is different then original then you need to rename or set newname for all datafiles or redologs.
SQL> column name format a55
SQL> select file#,name from v$datafile
2 union
3 select null,member from v$logfile;
FILE# NAME
---------- -------------------------------------------------1 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF
2 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBF
3 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBF
4 D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO01.LOG D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO02.LOG D:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\REDO03.LOG
7 rows selected.
10. Recover and open database with resetlogs
RMAN> run2> {3> set newname for datafile 1 to 'e:\orcl\oradata\system01.dbf';4> set newname for datafile 2 to 'e:\orcl\oradata\undotbs01.dbf';5> set newname for datafile 3 to 'e:\orcl\oradata\sysaux01.dbf';6> set newname for datafile 4 to 'e:\orcl\oradata\users01.dbf';7> SQL "alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo01.log'' to ''e:\orcl\oradata\redo01.log'' ";8> SQL "alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo02.log'' to ''e:\orcl\oradata\redo02.log'' ";9> SQL "alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo03.log'' to ''e:\orcl\oradata\redo03.log'' ";10> restore database;11> switch datafile all;12> recover database;13> }executing command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEsql statement: alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo01.log'' to ''e:\orcl\oradata\redo01.log''sql statement: alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo02.log'' to ''e:\orcl\oradata\redo02.log''sql statement: alter database rename file ''d:\oracle\product\10.1.0\oradata\orcl\redo03.log'' to ''e:\orcl\oradata\redo03.log''Starting restore at 10-MAY-08allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=159 devtype=DISKchannel ORA_DISK_1: starting datafile backupset restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to E:\ORCL\ORADATA\SYSTEM01.DBFrestoring datafile 00002 to E:\ORCL\ORADATA\UNDOTBS01.DBFrestoring datafile 00003 to E:\ORCL\ORADATA\SYSAUX01.DBFrestoring datafile 00004 to E:\ORCL\ORADATA\USERS01.DBFchannel ORA_DISK_1: restored backup piece 1piece handle=E:\BACKUP\05JG23BO_1_1 tag=TAG20080510T202008channel ORA_DISK_1: restore completeFinished restore at 10-MAY-08datafile 1 switched to datafile copyinput datafilecopy recid=5 stamp=654381217 filename=E:\ORCL\ORADATA\SYSTEM01.DBFdatafile 2 switched to datafile copyinput datafilecopy recid=6 stamp=654381217 filename=E:\ORCL\ORADATA\UNDOTBS01.DBFdatafile 3 switched to datafile copyinput datafilecopy recid=7 stamp=654381217 filename=E:\ORCL\ORADATA\SYSAUX01.DBFdatafile 4 switched to datafile copyinput datafilecopy recid=8 stamp=654381217 filename=E:\ORCL\ORADATA\USERS01.DBFStarting recover at 10-MAY-08using channel ORA_DISK_1starting media recoveryunable to find archive logarchive log thread=1 sequence=8RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 05/10/2008 20:33:39RMAN-06054: media recovery requesting unknown log: thread 1 seq 8 lowscn 327780RMAN> run2> {3> SQL 'alter database open resetlogs';4> }sql statement: alter database open resetlogs11. All datafiles and redologs file switch perfectly :)
SQL> select file#,name from v$datafile2 union3 select null,member from v$logfile;FILE# NAME---------- ------------------------------------------------- 1 E:\ORCL\ORADATA\SYSTEM01.DBF2 E:\ORCL\ORADATA\UNDOTBS01.DBF3 E:\ORCL\ORADATA\SYSAUX01.DBF4 E:\ORCL\ORADATA\USERS01.DBFE:\ORCL\ORADATA\REDO01.LOGE:\ORCL\ORADATA\REDO02.LOGE:\ORCL\ORADATA\REDO03.LOG7 rows selected.
12. Create SPFILE from PFILE
SQL> create spfile from pfile='e:\backup\init.ora';File created.
Subscribe to:
Posts (Atom)