Playing with Oracle Password Identified by values

We can see the hash value of password in the dba_users field by querying, select password from dba_users; Now how this value is generated. Will it work if I assign the same password to another user by hash value. Or is it database dependent or user name dependent. I will try to make you clear with examples.

A.Create User A with password A
---------------------------------------------------------------------
SQL> CREATE USER a IDENTIFIED BY a;
User created.

SQL> GRANT create session TO a;
Grant succeeded.

See the hash value of username a with password a.
SQL> select username, password from dba_users where username= 'A';
USERNAM PASSWORD
------- ------------------------------
A AFCC9478DFBF9029

B.Create another user B with this hash value.
-----------------------------------------------------------------------
SQL> CREATE USER b IDENTIFIED BY VALUES 'AFCC9478DFBF9029';
User created.

SQL> GRANT create session TO b;
Grant succeeded.

SQL> select username, password from dba_users where username= 'B';
USERNAM PASSWORD
------- ------------------------------
B AFCC9478DFBF9029


C.Try to connect to database with both user.
-------------------------------------------------------------------
SQL> CONN A/A
Connected.
SQL> CONN B/A
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.

So what we get is that the encrypted hash password can't work for another user. So hash value is dependent based on user name.

Now I will drop user A and will create user with above encrypted hash value and let's see the fact.

SQL> CONN ARJU/A
Connected.

SQL> DROP USER A;
User dropped.

SQL> CREATE USER A IDENTIFIED BY VALUES 'AFCC9478DFBF9029';
User created.

SQL> CONN A/A
Connected.

So we clearly can see that hash value of the password is user name dependent. We can experiment this on another database and will see that it does not depend on database name or OS.

Is it case sensitive. In following example I will create the user and password with different case and by default will see in neither case it is case sensitive.

SQL> CREATE USER "a" IDENTIFIED BY a;
User created.

SQL> select username, password from dba_users where UPPER(USERNAME)=UPPER('A');
USERNAM PASSWORD
------- ------------------------------
A AFCC9478DFBF9029
a AFCC9478DFBF9029

SQL> ALTER USER "a" IDENTIFIED BY VALUES 'AFCC9478DFBF9029';
User altered.

SQL> GRANT CREATE SESSION TO "a";
Grant succeeded.

SQL> CONN "a"/A
Connected.

SQL> ALTER USER "a" IDENTIFIED BY "a";
User altered.

SQL> select username, password from dba_users where UPPER(USERNAME)=UPPER('A');
USERNAM PASSWORD
------- ------------------------------
A AFCC9478DFBF9029
a AFCC9478DFBF9029

So we can conclude our experiment that we can use the encrypted password for the SAME user, but not for another user, the reason is that before calculating the hash value that is visible as the PASSWORD in DBA_USERS, Oracle adds the username to the mix and calculates the hash value on USERNAME plus PASSWORD. And neither username nor password is case sensitive in 10g.

In 11g it is case sensitive. You may have a look at,

Password is case sensitive in 11g

Related Documents

http://arjudba.blogspot.com/2008/05/password-verification-or-complexity.html

http://arjudba.blogspot.com/2008/04/password-is-case-sensitive-in-oracle.html

http://arjudba.blogspot.com/2008/07/solution-of-ora-28002-password-will.html

http://arjudba.blogspot.com/2008/07/ora-28003-ora-20002-while-changing.html

ORA-28000: the account is locked

Error Description:
-------------------------
Whenever you try connect to connect a user it failed with error message.
SQL> CONN INDIA/USA
ERROR:
ORA-28000: the account is locked
Warning: You are no longer connected to ORACLE.

And immediately the current user is disconnected from oracle.

Cause of The Problem:
-------------------------------
The user account is locked. This may be explicitly issued by dba user Like,
SQL> ALTER USER INDIA ACCOUNT UNLOCK; or it may be locked internally based on the profile resource limit. In order to know more about profile and resource limit have a look at,
User Resource Limit
Profile in Oracle

For example if I set FAILED_LOGON_ATTEMPTS of the assigned profile to a user set as 10 (which is default in 10.2)then after failed logon attempt 10 times the user account will be automatically locked.

Solution of The problem:
-------------------------------
Try to unlock the user account as a dba user by ALTER USER username ACCOUNT UNLOCK;
SQL> CONN INDIA/USA
ERROR:
ORA-28000: the account is locked
Warning: You are no longer connected to ORACLE.

SQL> CONN ARJU/A
Connected.

SQL> ALTER USER INDIA ACCOUNT UNLOCK;
User altered.

SQL> CONN INDIA/T
Connected.

There may be the reason that user is locked based on imposed resource limit. For example from application wrong password is set and for that the account is locked. You then have to know the assigned profile of the user by,
SQL> SELECT PROFILE FROM DBA_USERS WHERE USERNAME=
2 'INDIA';

PROFILE
----------
DEFAULT

SQL> SELECT RESOURCE_NAME,LIMIT FROM DBA_PROFILES WHERE PROFILE='DEFAULT';

RESOURCE_NAME LIMIT
-------------------------------- ----------------------------------------
COMPOSITE_LIMIT UNLIMITED
SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CPU_PER_CALL UNLIMITED
LOGICAL_READS_PER_SESSION UNLIMITED
LOGICAL_READS_PER_CALL UNLIMITED
IDLE_TIME UNLIMITED
CONNECT_TIME UNLIMITED
PRIVATE_SGA UNLIMITED
FAILED_LOGIN_ATTEMPTS 10
PASSWORD_LIFE_TIME UNLIMITED
PASSWORD_REUSE_TIME UNLIMITED
PASSWORD_REUSE_MAX UNLIMITED
PASSWORD_VERIFY_FUNCTION NULL
PASSWORD_LOCK_TIME UNLIMITED
PASSWORD_GRACE_TIME UNLIMITED

16 rows selected.


Here FAILED_LOGIN_ATTEMPTS resource is set to 10. You can make it unlimited by

SQL> ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;
Profile altered.

And then unlock the account.
SQL> ALTER USER INDIA ACCOUNT UNLOCK;

Related Documents
---------------------------
User Resource Limit in Oracle
Profile in Oracle

ORA-30041: Cannot grant quota on the tablespace

Error Description:
---------------------------
When I assign quota on the temporary tablespace it throws me an error below.

SQL> ALTER USER INDIA QUOTA 4M ON TEMP;
ALTER USER INDIA QUOTA 4M ON TEMP
*
ERROR at line 1:
ORA-30041: Cannot grant quota on the tablespace

Cause of the Problem:
--------------------------
This is expected behavior in oracle 10.2g. In version 10.2 oracle does not support quota on temporary tablespaces.

If your version is earlier than 10.2g then this is a bug.

Solution of The problem:
--------------------------------
Don't try to assign quota on the temporary tablespaces if your database version is 10.2 as it is expected behavior.

Related Documents

Changing a DBA user to a normal user in oracle

Create user in oracle

Minimum privilege needed to take data pump export

A user can do work in his schema with only Create Session Privilege.

ORA-01536: space quota exceeded for tablespace

Create user in oracle

You can create an user named oracle with password bangladesh simply by,

SQL> CREATE USER ORACLE IDENTIFIED BY BANGLADESH;
User created.

You can create user with assigning more option like,
SQL> CREATE USER INDIA IDENTIFIED BY USA
DEFAULT TABLESPACE TEST_DF_DELETE
TEMPORARY TABLESPACE TEMP_T
QUOTA 200K ON TEST_DF_DELETE
PROFILE DEFAULT
PASSWORD EXPIRE
ACCOUNT UNLOCK;
User created.

Here IDENTIFIED BY indicates the password of the user.

DEFAULT TABLESPACE indicates where his created objects will be stored if he don't specify tablespace clause explicitly while creating objects.

TEMPORARY TABLESPACE indicates where his temporary data will be stored.
QUOTA indicates how much space he is allowed to use on the tablespace.

PROFILE indicates which profile he is assigned.

PASSWORD EXPIRE makes the user’s password expire when they first log in. When you use this option, you will give the user an initial password, but after the user logs in the user will have to change his password.

ACCOUNT UNLOCK makes he can log on to database. When the user’s account is locked, no one can use it until it is unlocked.

To know about user's quota on the tablespace issue,

SQL> SELECT tablespace_name,username,bytes,max_bytes from dba_ts_quotas ;
TABLESPACE_NAME USERNAME BYTES MAX_BYTES
------------------------------ -------------------- ---------- ----------
SYSAUX DMSYS 262144 209715200
SYSAUX OLAPSYS 16318464 -1
SYSAUX SYSMAN 56623104 -1
TEST_DF_DELETE INDIA 0 204800

-1 means no limit.
SQL> ALTER USER ORACLE QUOTA UNLIMITED ON USER_TBS;
User altered.

SQL> SELECT tablespace_name,username,bytes,max_bytes from dba_ts_quotas ;
TABLESPACE_NAME USERNAME BYTES MAX_BYTES
------------------------------ -------------------- ---------- ----------
SYSAUX DMSYS 262144 209715200
SYSAUX OLAPSYS 16318464 -1
SYSAUX SYSMAN 56623104 -1
TEST_DF_DELETE INDIA 0 204800
USER_TBS ORACLE 0 -1

In order to know users common information issue following command.
SQL> SET LINESIZE 200
SQL> COL USERNAME FORMAT A7
SQL> COL TEMPORARY_TABLESPACE FORMAT A10
SQL> COL DEFAULT_TABLESPACE FORMAT A10
SQL> COL ACCOUNT_STATUS FORMAT A10
SQL> COL PROFILE FORMAT A10

SQL> SELECT USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE,PROFILE,ACCOUNT_STATUS FROM DBA_USERS WHERE username in ('ORACLE','INDIA');

USERNAM DEFAULT_TA TEMPORARY_ PROFILE ACCOUNT_ST
------- ---------- ---------- ---------- ----------
ORACLE USER_TBS TEMP2 DEFAULT OPEN
INDIA TEST_DF_DE TEMP_T DEFAULT EXPIRED
LETE

To be able a user log on to database he must be granted CREATE SESSION privilege.

SQL> GRANT CREATE SESSION TO INDIA;
Grant succeeded.

SQL> CONN INDIA/USA
ERROR:
ORA-28001: the password has expired

Changing password for INDIA
New password:
Retype new password:
Password changed
Connected.

Since while creating user I added option "password expire" so it invoked me to give a new password.

Related Documents

ORA-30041: Cannot grant quota on the tablespace

Changing a DBA user to a normal user in oracle

Minimum privilege needed to take data pump export

A user can do work in his schema with only Create Session Privilege.

ORA-01536: space quota exceeded for tablespace

Performing Trial Recovery in Database

Whenever there is block corruption during recovery then you have to choice whether you will corrupt the block or will open the database with RESETLOGS options.

The general rule is if the block is unimportant and if the problem is isolated then corrupt the block. But if the problem is not isolated then open the database with RESETLOGS option.

Oracle database supports trial recovery in order to identify corrupt blocks. In case of trial recovery the database writes errors generated during trial recovery to alert files and marks the data block as corrupt in memory and thus allow recovery to proceed.

When trial recovery ends, the database removes all effects of the test run from the system—except the possible error messages in the alert files. If the instance fails during trial recovery, then the database removes all effects of trial recovery from the system because trial recovery never writes changes to disk.

You can perform trial recovery by, RECOVER ... TEST option.

Example:
--------------
RECOVER DATABASE TEST
RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL TEST
RECOVER TABLESPACE sysaux TEST
RECOVER DATABASE UNTIL CANCEL TEST

Related Documents:
-------------------------------
Block Media Recovery with RMAN

Extract data from corrupt block

Verify Physical data Structure

Allow recovery to corrupt blocks

Allow Recovery to Corrupt Blocks

Whenever during recovery database finds corrupt block then recovery will automatically stops. In order to do recovery with skipping corrupt blocks run the RECOVER command with the ALLOW n CORRUPTION clause, where n is the number of allowable corrupt blocks.

Then database will allow recovery and mark the problem block as corrupt. However it should not do if problem block is is under SYSTEM tablespace.

In order to allow a single corruption just enter,

SQL>RECOVER DATABASE ALLOW 1 CORRUPTION;

Whenever there is a problem in corrupt block then the object id will be recorded inside alert log file. You can usually query the database to find out which object or table owns this block. To do so, cancel recovery and open the database read only.

CANCEL
SQL>ALTER DATABASE OPEN READ ONLY;

Now determine the object name by getting data object id from alert log. Suppose is it 1230
SELECT OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_TYPE
FROM DBA_OBJECTS
WHERE DATA_OBJECT_ID =1230;

Now you should perform trial recovery RECOVER ... TEST and see whether problem is isolated or not. If it is not isolated and if it is isolated but not contain SYSTEM data blocks then you can open the database with RESETLOGS options.

Related Documents:
-------------------------------
Block Media Recovery with RMAN

Extract data from corrupt block

Verify Physical data Structure

Requirements of Tablespace Point-in-time Recovery

•You must have the backups of all datafiles of the recovery and auxiliary tablepspace set and your desired TSPITR time is after the time of the backups for those datafiles.

•You must have control file backup before the TSPITR time.

•The control file backup must be a binary one. That is ALTER DATABASE BACKUP CONTROLFILE TO '';

•Ensure that all files constituting the recovery set tablespaces are in the recovery set on the auxiliary database; otherwise, the export phase during tablespace transport fails.

•Enough disk space must be available on the auxiliary host to accommodate the auxiliary database.

•Provide enough real memory to start the auxiliary instance.

•If the tablespace to be recovered has been renamed, ensure that the target SCN for TSPITR is after the time when the file was renamed. You cannot TSPITR a renamed tablespace to a point in time earlier than the rename. However, you can perform a DBPITR to an SCN before the rename. In this case, the tablespace reverts to its name as of the target SCN.

Drop datafile in Oracle.

With the statement ALTER TABLESPACE tablespace_name DROP DATAFILE {file_name|file_id} you can drop an empty datafile from a tablespace. This casues datafile will be deleted from data dictionary as well as from operating system. With ALTER TABLESPACE ... DROP TEMPFILE you can also delete any temporary data file.

Though we can drop datafile from tablespace. There is restriction on dropping.

•The file must be empty.
•Cannot be the first file that was created in the tablespace. In such cases,in order to drop the datafile drop the tablespace instead.
•Cannot be in a read-only tablespace.
•If there is only one file exist on the tablespace then that file can't be deleted which is analogous to file file of the tablespace.

With an example I will try to demonstrate the imposed restriction of dropping datafile.

1)Create Tablespace with Three Datafiles.

SQL> CREATE TABLESPACE TEST_DF_DELETE DATAFILE '/oradata1/arju/1.dbf' SIZE 500K, '/oradata1/arju/2.dbf' SIZE 600K, '/oradata1/arju/3.dbf' SIZE 1m;
Tablespace created.

2)Create a Table Under it.

SQL> CREATE TABLE TEST_TABLE TABLESPACE TEST_DF_DELETE AS SELECT LEVEL COL1 FROM DUAL CONNECT BY LEVEL<=1000;
Table created.

3)Check for in which datafile the Table span for.

SQL> SELECT FILE_NAME, FILE_ID FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='TEST_DF_DELETE';
FILE_NAME FILE_ID
---------------------------------------- ----------
/oradata1/arju/1.dbf 8
/oradata1/arju/2.dbf 9
/oradata1/arju/3.dbf 10

SQL> SELECT SEGMENT_NAME , FILE_ID FROM DBA_EXTENTS WHERE FILE_ID IN (8,9,10);
SEGMENT_NAME FILE_ID
-------------------- ----------
TEST_TABLE 8

4)Try to drop file_id 8.

Since it satisfy restriction so the file 8 can't be deleted. The restriction is not exmpty and first file of the tablespace. So error comes.

SQL> ALTER TABLESPACE TEST_DF_DELETE DROP DATAFILE 8;
ALTER TABLESPACE TEST_DF_DELETE DROP DATAFILE 8
*
ERROR at line 1:
ORA-03263: cannot drop the first file of tablespace TEST_DF_DELETE

5)Try to drop file_id 10.

Since it does not satisfy any of the restriction so it will be deleted.

SQL> ALTER TABLESPACE TEST_DF_DELETE DROP DATAFILE 10;
Tablespace altered.

6)Insert more rows into the table in order to span both file_id 8 and 9.

SQL> INSERT INTO TEST_TABLE SELECT LEVEL COL1 FROM DUAL CONNECT BY LEVEL<=20000;
20000 rows created.

SQL> COMMIT;
Commit complete.

SQL> SELECT DISTINCT SEGMENT_NAME , FILE_ID FROM DBA_EXTENTS WHERE FILE_ID IN (8,9);
SEGMENT_NAME FILE_ID
-------------------- ----------
TEST_TABLE 8
TEST_TABLE 9

7)Now try to drop file_id 9.

As now data is inside file_id 9 and so it is not empty which is the restriction of dropping datafile ans hence we can't drop file_id 9.

SQL> ALTER TABLESPACE TEST_DF_DELETE DROP DATAFILE 9;
ALTER TABLESPACE TEST_DF_DELETE DROP DATAFILE 9
*
ERROR at line 1:
ORA-03262: the file is non-empty

8)Drop the table and try to drop the data file 9.

SQL> DROP TABLE TEST_TABLE;
Table dropped.

SQL> SELECT DISTINCT SEGMENT_NAME , FILE_ID FROM DBA_EXTENTS WHERE FILE_ID IN (8,9);
no rows selected

SQL> ALTER TABLESPACE TEST_DF_DELETE DROP DATAFILE 9;
ALTER TABLESPACE TEST_DF_DELETE DROP DATAFILE 9
*
ERROR at line 1:
ORA-03262: the file is non-empty

After dropping it is failed because yet it is not empty though under dba_extents it is not showing. It is because the object resides in recyclebin.

9)Delete object from recyclebin and try to drop.

SQL> PURGE RECYCLEBIN;
Recyclebin purged.

SQL> ALTER TABLESPACE TEST_DF_DELETE DROP DATAFILE 9;
Tablespace altered.

It is succeed. But still you can't be able to drop datafile 8 since it does satisfy restriction.

SQL> ALTER TABLESPACE TEST_DF_DELETE DROP DATAFILE 8;
ALTER TABLESPACE TEST_DF_DELETE DROP DATAFILE 8
*
ERROR at line 1:
ORA-03261: the tablespace TEST_DF_DELETE has only one file

SQL> ALTER TABLESPACE TEST_DF_DELETE ADD DATAFILE '/oradata2/arju/new.dbf' size 1M;
Tablespace altered.

SQL> ALTER TABLESPACE TEST_DF_DELETE DROP DATAFILE 8;
ALTER TABLESPACE TEST_DF_DELETE DROP DATAFILE 8
*
ERROR at line 1:
ORA-03263: cannot drop the first file of tablespace TEST_DF_DELETE

Terminate Process on UNIX

If you want to terminate a process in unix or if you start a process and it shows it is already running then you need to kill the process. On unix in order to kill the process first find the process id. And then kill by kill -9.

Suppose in my computer my firefox was not responding so I right click on firefox title bar and kill them. Now whenever I tried to run process it showed me the process is already running.

What I did is
1)log on as a root user.

Arju@debian:~$ su
Password:

2)Now find the process id of firefox.
debian:/home/Arju# ps -ef |grep firefox
root 3060 1 14 08:03 pts/0 00:00:23 /usr/lib/iceweasel/firefox-bin -a firefox
root 3090 3088 0 08:06 pts/0 00:00:00 grep firefox

So it is 3060,1.
3)Kill by,
debian:/home/Arju# kill -9 3060 1

Now I started firefox and it is ok.

Error Vmware Failed to lock the file

Problem Description:
---------------------------
If you use vmware then while opening it it failed with following messages,

Cannot open the disk '/home/Arju/win_vmware_image/Windows XP Professional 2.vmdk' or one of the snapshot disks it depends on.
Reason: Failed to lock the file.

The problem can happen with many reasons. Try to solve one by one.

Solution of the Problem:
------------------------------
1)Is you vmware is improperly shutdown? If so look at the directory where vmware resides. Search file there named extention .WRITELOCK. Delete the writelock file and try to start the vmware. If you succeed then no further steps necessary.

2)Do you point to your vmware to another location or add another disk which is not currently available. If so, under poweroff menu >select edit virtual mechine settings >You will see the list of hardware. If you suspect the hardware/disk is not currently available select it and then remove it. Now try to start your vmware.

3)Look for other vmware process or any software is running and whether it use your current existing disk. If so shutdown that process and try to start your vmware.

Recovering After the Loss of Online Redo Log Files

Case A: Losing a Member of a Multiplex online Redo log group.
------------------------------------------------------------------------
In this case your redo log file is multiplex that means you have more than one log member within log groups. Now if you have at least one redo log member of the group exist then database continues functional as usual. In alert log error message will be written.
In order to solve the problem,

i)Identify the redo log file that is invalid.
SQL> SELECT GROUP#, STATUS, MEMBER
FROM V$LOGFILE
WHERE STATUS='INVALID';
GROUP# STATUS MEMBER
------- ----------- ---------------------
0002 INVALID /oracle/arju/redo02.log

ii)Drop the damaged member.
ALTER DATABASE DROP LOGFILE MEMBER '/oracle/arju/redo02.log/arju/redo02.log'

iii)Add a new member to the group.
ALTER DATABASE ADD LOGFILE MEMBER '/oracle/arju/redo02b.log' TO GROUP 1; --FOr example to group 1.

If the file you want to add already exists, you must specify REUSE. For example:
ALTER DATABASE ADD LOGFILE MEMBER '/oracle/arju/redo02b.log' REUSE TO GROUP 2;

Case B:Recovering After the Loss of All Members of an Online Redo Log Group
------------------------------------------------------------------------------
If a media failure damages all members of an online redo log group, then different scenarios can occur depending on the type of online redo log group affected by the failure and the archiving mode of the database.

-If the group is inactive then it is not needed for crash recovery. Clear the archive or unarchived group.

-If the group is active then it is needed for crash recovery. In order to solve the problem attempt to issue a checkpoint and clear the log; if impossible, then you must restore a backup and perform incomplete recovery up to the most recent available redo log.

-If the group is current then it is the log that the database is currently writing. In order to solve the problem attempt to clear the log; if impossible, then you must restore a backup and perform incomplete recovery up to the most recent available redo log.

Troubleshooting:
--------------------

Determine whether the damaged group is active or inactive.

See the file name and corresponding group name of the log file.

SQL>SELECT GROUP#, STATUS, MEMBER FROM V$LOGFILE;

Determine which groups are active.

SQL> SELECT GROUP#, MEMBERS, STATUS, ARCHIVED
FROM V$LOG;
Losing an Inactive Online Redo Log Group
-----------------------------------------------
If the damaged redo log group is inactive and archived then issue,
SQL>ALTER DATABASE CLEAR LOGFILE GROUP 2;
It will reinitialize the damaged log group.

b)If the damaged redo log group is inactive and not yet archived issue,

SQL>ALTER DATABASE CLEAR LOGFILE UNARCHIVED GROUP 2;

If there is an offline datafile that requires the cleared log to bring it online, then the keywords UNRECOVERABLE DATAFILE are required. The datafile and its entire tablespace have to be dropped because the redo necessary to bring it online is being cleared, and there is no copy of it. For example, enter:

SQL>ALTER DATABASE CLEAR LOGFILE UNARCHIVED GROUP 2 UNRECOVERABLE DATAFILE;

Immediately backup full database.

Losing an Active Online Redo Log Group
--------------------------------------------
If the database is still running and the lost active redo log is not the current log, then issue the ALTER SYSTEM CHECKPOINT statement. If successful, then the active redo log becomes inactive.
And follow the procedure above as how you will respond to an inactive online redo log group.

If your ALTER SYSTEM CHEKPOINT statement failed then follow the following procedure.

a)If your database is in noarchivelog state restore database from whole consistent backup and perform incomplete recovery.

$scp .. /oracle/target/*
SQL>STARTUP MOUNT
SQL>RECOVER DATABASE UNTIL CANCEL
CANCEL
SQL>ALTER DATABASE OPEN RESETLOGS

b)If you loss an active online redo log in archivelog mode then follow the following procedure.

ALTER DATABASE RENAME FILE '/oradata/trgt/redo01.log' TO '/tmp/redo01.log';
.
.
ALTER DATABASE OPEN RESETLOGS;

Related Documents

Startup fails with oracle error ORA-00119, ORA-00132

What will be the Archived Redo Log Destination in Oracle

Redo Log File Status

Relocating and Renaming Redo Log Members

ORA-00354 ORA-00353 ORA-00312 Corrupt redo block header

ORA-00313 ,ORA-00312 open failed for members of log group

User Managed Recover of Datafile when Backup is not Available

If a datafile is damaged and you don't have any backup of the datafile then still you can recover your data file if the following conditions met.

1)You have all archived redo logs available since the data file creation.

2)The control file contains the name of the lost file. That means either the control file is current or it is taken after the data file creation.

3)The datafile does not belong to SYSTEM tablespace.

To illustrate the scenario I will demonstrate the whole procedure with an example.

A)Add a datafile to a tablespace.

SQL> ALTER TABLESPACE USER_TBS ADD DATAFILE '/oradata2/data1/dbase/datafile03.dbf' SIZE 1M;
Tablespace altered.

B)Drop the datafile.

SQL> !rm /oradata2/data1/dbase/datafile03.dbf

C)Make the affect data file offline if the database is open.

SQL> ALTER DATABASE CREATE DATAFILE '/oradata2/data1/dbase/datafile03.dbf' AS '/oradata1/arju/created_new.dbf';
ALTER DATABASE CREATE DATAFILE '/oradata2/data1/dbase/datafile03.dbf' AS '/oradata1/arju/created_new.dbf'
*
ERROR at line 1:
ORA-01182: cannot create database file 7 - file is in use or recovery
ORA-01110: data file 7: '/oradata2/data1/dbase/datafile03.dbf'

It can't rename as database currently using this file. So, make it offline.

SQL> alter database datafile '/oradata2/data1/dbase/datafile03.dbf' offline;
Database altered.

D) a new, empty datafile to replace a damaged datafile that has no corresponding backup. Here I craeted the damaged file 7 as to new location '/oradata1/arju/created_new.dbf'.

SQL> ALTER DATABASE CREATE DATAFILE '/oradata2/data1/dbase/datafile03.dbf' AS '/oradata1/arju/created_new.dbf';
Database altered.

E)Perform media recovery on the empty datafile.
SQL> RECOVER DATAFILE '/oradata1/arju/created_new.dbf';
Media recovery complete.

F)Make the datafile online.

SQL> alter database datafile '/oradata1/arju/created_new.dbf' ONLINE;
Database altered.

Now we will look at the scenario if while starting database it can't find datafile.

SQL> !rm /oradata1/arju/created_new.dbf

Shutdown immediate will fail as it can't find one datafile.

SQL> shutdown immediate;
ORA-01116: error in opening database file 7
ORA-01110: data file 7: '/oradata1/arju/created_new.dbf'
ORA-27041: unable to open file
Solaris-AMD64 Error: 2: No such file or directory
Additional information: 3

SQL> shutdown abort
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 88080424 bytes
Database Buffers 71303168 bytes
Redo Buffers 6369280 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/oradata1/arju/created_new.dbf'


SQL> ALTER DATABASE CREATE DATAFILE '/oradata1/arju/created_new.dbf' AS '/oradata2/data1/dbase/datafile03.dbf';

Database altered.

SQL> RECOVER DATAFILE 7;
Media recovery complete.

SQL> ALTER DATABASE OPEN;
Database altered.

Recovering Through an Added Datafile with a Backup Control File

If database recovery with a backup control file rolls forward through a CREATE TABLESPACE or an ALTER TABLESPACE ADD DATAFILE operation, then the database stops recovery when applying the redo record for the added files and lets you confirm the filenames.

In this example I illustrate this behavior and how to solve the problem.

A)Backup The Data file.
SQL> SELECT FILE_NAME FROM DBA_DATA_FILES;
FILE_NAME
----------------------------------------
/oradata2/data1/dbase/sysaux01.dbf
/oradata2/data1/dbase/undotbs01.dbf
/oradata2/data1/dbase/system01.dbf
/oradata2/data1/dbase/user01.dbf

SQL> ALTER DATABASE BEGIN BACKUP;
Database altered.

SQL> !cp /oradata2/data1/dbase/sysaux01.dbf /oradata2/arju/
SQL> !cp /oradata2/data1/dbase/undotbs01.dbf /oradata2/arju/
SQL> !cp /oradata2/data1/dbase/system01.dbf /oradata2/arju/
SQL> !cp /oradata2/data1/dbase/user01.dbf /oradata2/arju/

SQL> ALTER DATABASE END BACKUP;
Database altered.


B)Backup the control file.

SQL> alter database backup controlfile to '/oradata2/arju/control.ctl';
Database altered.

Archive the unarchived redo logs so that the redo required to recover the tablespace backup is archived. For example, enter:

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

C)Create a Tablespace with two datafiles.

SQL> CREATE TABLESPACE TBS_AFTER_BACKUP DATAFILE '/oradata2/data1/dbase/datafile01.dbf' size 1M, '/oradata2/data1/dbase/datafile02.dbf' size 1M;

Tablespace created.

D)Remove the All copies of control files.
SQL> show parameter control_files

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /oradata1/arju/control01.ctl,
/oradata1/arju/control02.ctl
SQL> !rm /oradata1/arju/control01.ctl

SQL> !rm /oradata1/arju/control02.ctl


E)Shutdown and Restore Control file from Backup

SQL> shutdown abort
ORACLE instance shut down.
SQL> !cp /oradata2/arju/control.ctl /oradata1/arju/control01.ctl

SQL> !cp /oradata2/arju/control.ctl /oradata1/arju/control02.ctl

Since these restored control files are before adding tablespace so the datafile information of the latest added tablespace will not be here. So when it will try to apply redo of the new datafile it will generate error.

F)Startup the instance and Try to recover database.

SQL> startup mount
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 83886120 bytes
Database Buffers 75497472 bytes
Redo Buffers 6369280 bytes
Database mounted.

Here I am performing automatic recovery.

SQL> RECOVER AUTOMATIC DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to control file by media recovery
ORA-01110: data file 6: '/oradata2/data1/dbase/datafile02.dbf'
ORA-01110: data file 4: '/oradata2/data1/dbase/datafile01.dbf'

G)View the files added by selecting from V$DATAFILE.

SQL> SELECT FILE#, NAME FROM V$DATAFILE;
FILE# NAME
--------------------------------------------------------------------------
1 /oradata2/data1/dbase/system01.dbf
2 /oradata2/data1/dbase/undotbs01.dbf
3 /oradata2/data1/dbase/sysaux01.dbf
4 /oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00004
5 /oradata2/data1/dbase/user01.dbf
6 /oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00006
6 rows selected.

H)Rename unnamed datafiles to original names.


If multiple versions of unnamed file exists the identify them from alert log or derive the original file location of each unnamed file from the error message and V$DATAFILE.


SQL> ALTER DATABASE RENAME FILE '/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00004' TO '/oradata2/data1/dbase/datafile01.dbf';
Database altered.


SQL> ALTER DATABASE RENAME FILE '/oracle/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00006' TO '/oradata2/data1/dbase/datafile02.dbf';
Database altered.


I)Now perform recovery operations
SQL> RECOVER AUTOMATIC DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL
ORA-00279: change 1667619 generated at 05/21/2008 03:13:49 needed for thread 1
ORA-00289: suggestion :
/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_21/o1_mf_1_4_%u_.arc
ORA-00280: change 1667619 for thread 1 is in sequence #4
ORA-00278: log file
'/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_21/o1_mf_1_4_%u_.arc'
no longer needed for this recovery
ORA-00308: cannot open archived log
'/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_21/o1_mf_1_4_%u_.arc'
ORA-27037: unable to obtain file status
Solaris-AMD64 Error: 2: No such file or directory
Additional information: 3

Specify log: {=suggested | filename | AUTO | CANCEL}
/oradata2/data1/dbase/redo01.log---SPECIFY REDO LOG MEMBER HERE. QUERY FROM V$LOGFILE
ORA-00310: archived log contains sequence 3; sequence 4 required
ORA-00334: archived log: '/oradata2/data1/dbase/redo01.log'

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oradata2/data1/dbase/system01.dbf'

As one member fail so provide another member.

SQL> RECOVER AUTOMATIC DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL
ORA-00279: change 1667619 generated at 05/21/2008 03:13:49 needed for thread 1
ORA-00289: suggestion :
/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_21/o1_mf_1_4_%u_.arc
ORA-00280: change 1667619 for thread 1 is in sequence #4
ORA-00278: log file
'/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_21/o1_mf_1_4_%u_.arc'
no longer needed for this recovery
ORA-00308: cannot open archived log
'/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_21/o1_mf_1_4_%u_.arc'
ORA-27037: unable to obtain file status
Solaris-AMD64 Error: 2: No such file or directory
Additional information: 3


Specify log: {=suggested | filename | AUTO | CANCEL}
/oradata2/data1/dbase/redo03.log---SPECIFY REDO LOG MEMBER HERE. QUERY FROM V$LOGFILE
Log applied.
Media recovery complete.

J)Open the database with RESETLOGS option
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.

Related Documents:
User Managed Restore Operation

What you will do if spfile lost Look at Solution of The problem Section

How to solve problem of inconsistent control file.

How to re-create Control file

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


•In order to set database default tablespace compatible parameter must be greater than 10.0 To see current compatiblity settings issue,
SQL> show parameter compatible

If current compatibility settings is less than 10.0 the following error will come,

SQL> ALTER DATABASE DEFAULT TABLESPACE new_tbs;
*
ERROR at line 1:
ORA-12916: Cannot use default permanent tablespace with this release

•Database default tablespace can't be dropped. if you are going to drop the following error will arise.

SQL> drop tablespace users including contents;
drop tablespace users including contents
*
ERROR at line 1:
ORA-12919: Can not drop the default permanent tablespace

In order to drop a database default tablespace assign other tablespace as database default tablespace and then drop.

SQL> alter database default tablespace user_tbs;
Database altered.

SQL> drop tablespace users including contents;
Tablespace dropped.

Recovering a Database in NOARCHIVELOG Mode

If a media failure damages datafiles in a NOARCHIVELOG database, then the only option for recovery is usually to restore a consistent whole database backup. As you are in noarchivelog mode so you have to understand that changes after taken backup is lost.

If you logical backup that is export file you can import that also.

In order to recover database in noarchivelog mode you have to follow the following procedure.

1)If the database is open shutdown it.
SQL>SHUTDOWN IMMEDIATE;

2)If possible, correct the media problem so that the backup database files can be restored to their original locations.

3)Copy all of the backup control files, datafiles to the default location if you corrected media failure. However you can restore to another location. Remember that all of the files not only the damaged files.

4)Because online redo logs are not backed up, you cannot restore them with the datafiles and control files. In order to allow the database to reset the online redo logs, you must have to do incomplete recovery:

RECOVER DATABASE UNTIL CANCEL
CANCEL

5)Open the database in RESETLOGS mode:
ALTER DATABASE OPEN RESETLOGS;

In order to rename your control files or in case of media damage you can copy it to another location and then by setting (if spfile)
STARTUP NOMOUNT
ALTER SYSTEM SET CONTROL_FILES='/new/1.ctl','/new/2.ctl' scope=spfile;
STARTUP FORCE MOUNT;

In order to rename data files or online redo log files first copy it to new location and then point control file to new location by,

ALTER DATABASE RENAME FILE '/old/system01.dbf' TO '/new_disk/system01.dbf';

Related Documents:
---------------------------
User Managed Restore Operation
What you will do if spfile lost Look at Solution of The problem Section
How to solve problem of inconsistent control file.

How to re-create Control file

User manged Recovery After the Loss of Datafile

Losing Datafiles Whenever you are in NoArchivelog Mode:
----------------------------------------------------------------
If you are in noarchivelog mode and you loss any datafile then whether it is temporary or permanent media failure, the database will automatically shut down. If failure is temporary then correct the underline hardware and start the database. Usually crash recovery will perform recovery of the committed transaction of the database from online redo log files. If you have permanent media failure then restore a whole database from a good backup. How to restore a database in archivelog is discussed on
http://arjudba.blogspot.com/2008/05/recovering-database-in-noarchivelog.html.

Losing Datafiles Whenever you are in Archivelog Mode:
-----------------------------------------------------------------
If the datafile that is lost is under SYSTEM tablespace or if it is a datafile contain active undo segments then database shuts down. If the failure is temporary then correct the underline hardware and start the database. Usually crash recovery will perform recovery of the committed transaction of the database from online redo log files.

If the datafile that is lost in not under SYSTEM tablespace and not contain active undo segments then the affected datafile is gone to offline. The database remains open. In order to fix the problem take the affected tablespace offline and then recover the tablespace.

About Opening database with the RESETLOGS Option

In case of incomplete recovery using backup control file in order to open the database you must have issue ALTER DATABASE OPEN RESETLOGS option. Now question is what RESETLOGS does? In the following section it is demonstrate what it does.

1)It creates a new incarnation of the database. You can say a new version of database life is produced. To know more about incarnation search within my blog.

2)If the current online redo logs are accessible then archive those, erases the contents of the online redo logs and resets the log sequence number to 1. You can see current log sequence number from V$LOG. SQL> SELECT SEQUENCE#, GROUP# FROM v$log;

3)If online redo log files are not exist then create online redo log files.

4)Re initializes the control file metadata about online redo logs and redo threads.

5)Updates all current datafiles and online redo logs and all subsequent archived redo logs with a new RESETLOGS SCN and time stamp.

What is Debit Card, Credit Card, ATM Card, Charge Card

Debit card also known as bank card or check card.

An ATM card also known as a bank card, client card, key card or cash card based on country location.

Credit card and debit card examples are VISA, MasterCard.
Example of Charge card are American Express, Diners Club cards.

For more information have a looked at,
Debit Card, Credit Card, ATM Card, Charge Card

User Managed database recovery when all database files lost.

In this example , you have only hot backup of the database. You have lost all datafiles , control files and redo log files. But you have archived redo log file. The backup of the database is taken as in example, User managed Hot Backup. Now you want to perform recovery of whole database. This is discussed in the following sections.

1)Restore the spfile: If you have backup then restore from that. If you don't have then I have shown different ways in order to create spfile in my topic. Search around my blog.

2)Start the database in nomount stage.
SQL>STARTUP NOMOUNT

3)Edit the spfile by Alter system if any modification needed.

show the control_files parameter in the spfile by,

SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /oradata2/data1/dbase/control0
1.ctl, /oradata2/data1/dbase/c
ontrol02.ctl, /oradata2/data1/
dbase/control03.ctl

Copy the backup of control files to these location. If there is disk corruption then you can copy it in another place and change the spfile accordingly. Like I copied spfile to the location /oradata1/arju and then point that place in spfile.

SQL> !mkdir /oradata1/arju

Copy from backup location to /oradata1/arju/*
SQL> !scp /oradata2/arju/control.ctl /oradata1/arju/control01.ctl

SQL> !scp /oradata2/arju/control.ctl /oradata1/arju/control02.ctl

Point spfile about the new location of control files.
SQL> alter system set control_files='/oradata1/arju/control01.ctl', '/oradata1/arju/control02.ctl' scope=spfile;
System altered.

Since it is static parameter so start the database with modified spfile in order to make affect.
SQL> startup force nomount
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 100663336 bytes
Database Buffers 58720256 bytes
Redo Buffers 6369280 bytes

SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /oradata1/arju/control01.ctl,
/oradata1/arju/control02.ctl

4)Mount the database and see the location of the datafile.


SQL> alter database mount
2 ;

Database altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oradata2/data1/dbase/system01.dbf
/oradata2/data1/dbase/undotbs01.dbf
/oradata2/data1/dbase/sysaux01.dbf
/oradata2/data1/dbase/users01.dbf
/oradata2/7.dbf

6 rows selected.

5)Copy the backup datafile to these location. However you can change any location. Suppose here I am changing /oradata2/data1/dbase/users01.dbf to /oradata1/arju/user.dbf

SQL> !scp /oradata2/arju/system01.dbf /oradata2/data1/dbase

SQL> !scp /oradata2/arju/sysaux01.dbf /oradata2/data1/dbase

SQL> !scp /oradata2/arju/undotbs01.dbf /oradata2/data1/dbase

SQL> !scp /oradata2/arju/users01.dbf /oradata1/arju/user.dbf

SQL> !scp /oradata2/arju/7.dbf /oradata2/7.dbf

SQL> ALTER DATABASE RENAME FILE '/oradata2/data1/dbase/users01.dbf' to '/oradata1/arju/user.dbf';
Database altered.

6)At time time restore is complete. So recovery is needed. As we have no online redo logs so need to do incomplete recovery.

SQL>RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;
SQL> RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;
ORA-00279: change 1541029 generated at 05/19/2008 03:37:20 needed for thread 1
ORA-00289: suggestion :
/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_20/o1_mf_1_123_%u_.arc
ORA-00280: change 1541029 for thread 1 is in sequence #123


Specify log: {=suggested | filename | AUTO | CANCEL}
-----Here I pressed ENTER.
ORA-00279: change 1565452 generated at 05/19/2008 05:27:53 needed for thread 1
ORA-00289: suggestion :
/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_20/o1_mf_1_124_%u_.arc
ORA-00280: change 1565452 for thread 1 is in sequence #124
ORA-00278: log file
'/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_19/o1_mf_1_123_432l0s40_
.arc' no longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

SQL> alter database open resetlogs;
Database altered.

If you don't have backup control file then you can do as follows,

CREATE CONTROLFILE REUSE DATABASE "DBASE" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oradata2/data1/dbase/redo01.log' SIZE 4M,
GROUP 3 '/oradata2/data1/dbase/redo03.log' SIZE 50M
DATAFILE
'/oradata2/data1/dbase/system01.dbf',
'/oradata2/data1/dbase/undotbs01.dbf',
'/oradata2/data1/dbase/sysaux01.dbf',
'/oradata1/arju/user.dbf',
'/oradata2/7.dbf'
CHARACTER SET WE8ISO8859P1
;

SQL> recover database until cancel using BACKUP CONTROLFILE;
ORA-00279: change 1541029 generated at 05/19/2008 03:37:20 needed for thread 1
ORA-00289: suggestion :
/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_20/o1_mf_1_123_%u_.arc
ORA-00280: change 1541029 for thread 1 is in sequence #123


Specify log: {=suggested | filename | AUTO | CANCEL}
---JUST I PRESS ENTER HERE
ORA-00279: change 1565452 generated at 05/19/2008 05:27:53 needed for thread 1
ORA-00289: suggestion :
/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_20/o1_mf_1_124_%u_.arc
ORA-00280: change 1565452 for thread 1 is in sequence #124
ORA-00278: log file
'/oradata2/flash_recovery_area/DBASE/archivelog/2008_05_19/o1_mf_1_123_432l0s40_
.arc' no longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.

Related Documents:
---------------------------
User Managed Restore Operation

What you will do if spfile lost Look at Solution of The problem Section

How to solve problem of inconsistent control file.

How to re-create Control file

Automatic Recovery During Applying logs

With the command RECOVER(which is SQL*Plus command) or ALTER DATABASE RECOVER you can perform media recovery. It is always good to use SQL*plus while recovery of database because with it you can perform automatic recovery. Whenever we use RECOVER command it displays a prompt in order to supply archived redo log. But if they logs are in a location in the disk then you can perform automatic recovery where it will not prompt to provide any location.

There are two ways to perform automatic recovery.

1)Issuing SET AUTORECOVERY ON before issuing the RECOVER command.

SQL>SET AUTORECOVERY ON;
SQL>RECOVER DATABASE;

2)Specifying the AUTOMATIC keyword as an option of the RECOVER command.

SQL>RECOVER AUTOMATIC DATABASE;


Recovering When Archived Logs Are in the Default Location
-------------------------------------------------------------------
Whenever you issue RECOVER command then it will prompt as,

ORA-00279: Change #### generated at DD/MM/YY HH:MM:SS needed for thread#
ORA-00289: Suggestion : logfile
ORA-00280: Change #### for thread # is in sequence #
Specify log: [ for suggested | AUTO | FROM logsource | CANCEL ]

If you have not changed your archived log destination and the format is in tact then if you specify AUTO it will automatically constructs suggested archived log filenames by concatenating the current values of the initialization parameters LOG_ARCHIVE_DEST_n (where n is the highest
value among all enabled, local destinations) and LOG_ARCHIVE_FORMAT and using log history data from the control file.

Recovering When Archived Logs Are in a Nondefault Location
--------------------------------------------------------------------
When archived redo logs is not their default location then you have to following any options from these two.

1) Edit the LOG_ARCHIVE_DEST_n parameter that specifies the location of the archived redo logs, then recover as usual.

2) Use the SET statement in SQL*Plus to specify the nondefault log location before recovery, or the LOGFILE parameter of the RECOVER command.

1) Edit the LOG_ARCHIVE_DEST_n parameter
-----------------------------------

i)$ cp /backup/arch/* /tmp/
ii)LOG_ARCHIVE_DEST_1 = 'LOCATION=/tmp/'
iii)LOG_ARCHIVE_FORMAT = arcr_%t_%s.arc
iv)STARTUP MOUNT
v)RECOVER DATABASE

2)SET statement in SQL*Plus(Override the arhived redo log source)
---------------------------------------
i)$ cp $ORACLE_HOME/oradata/trgt/arch/* /tmp
ii)SET LOGSOURCE "/tmp"
iii)RECOVER AUTOMATIC TABLESPACE users

you can avoid running SET LOGSOURCE and simply run:
RECOVER AUTOMATIC TABLESPACE users FROM "/tmp"

Overriding the redo log source does not affect the archive redo log destination for online redo logs groups being archived.

What you do if all copies of your control files are lost

What you will do if you lost of your all copies of control file? Will you re-create a new one or will you restore a controlfile from backup? What will be your backup command? In the following section I have depicted in which scenario what we need to do.


1)Both Data files and Online Redo logs are in tact: In this case you have only lost your control file and every other things are ok. If the online logs contain redo necessary for recovery, then restore a backup control file and apply the logs during recovery. You must specify the filename of the online logs containing the changes in order to open the database. After recovery, open the database with RESETLOGS.

2)Data files are intact but Online Redo logs are lost: In this case you have lost your online redo log file also. If the online logs contain redo necessary for recovery, then re-create the control file. Because the online redo logs are inaccessible, open the database with RESETLOGS options. But when the online logs are accessible it is not necessary to OPEN RESETLOGS after recovery with a created control file.

3)Data files are lost but online redo logs are available: In this restore a backup control file , perform complete recovery and open that database with resetlogs option.

4)Both Data files and redo logs file are lost: In this scenario restore a backup control file , perform incomplete recovery and open the database with resetlogs option.


Related Documents:
-------------------------
User Managed Restore Operation
What you will do if spfile lost Look at Solution of The problem Section
How to solve problem of inconsistent control file.
How to re-create Control file

User Managed Hot Backup and Recovery of lost of data file

Example of User Managed Hot Backup:
-----------------------------------------

1)Determine which data file you need to backup.
SQL> select file_name ,tablespace_name from dba_data_files
2 /

FILE_NAME TABLESPACE_NAME
---------------------------------------- ------------------------------
/oradata2/7.dbf USERS
/oradata2/data1/dbase/users01.dbf USERS
/oradata2/data1/dbase/sysaux01.dbf SYSAUX
/oradata2/data1/dbase/undotbs01.dbf UNDOTBS1
/oradata2/data1/dbase/system01.dbf SYSTEM
/oradata2/6.dbf USERS
6 rows selected.

2)Make the tablespace in backup mode one by one and copy the associated datafile to backup location.
SQL> ALTER TABLESPACE USERS BEGIN BACKUP;

Tablespace altered.

SQL> !cp /oradata2/data1/dbase/users01.dbf /oradata2/arju

SQL> !cp /oradata2/7.dbf /oradata2/arju

SQL> ALTER TABLESPACE USERS END BACKUP;

Tablespace altered.

SQL> ALTER TABLESPACE SYSAUX BEGIN BACKUP;

Tablespace altered.

SQL> !cp /oradata2/data1/dbase/sysaux01.dbf /oradata2/arju

SQL> ALTER TABLESPACE SYSAUX END BACKUP;

Tablespace altered.

SQL> ALTER TABLESPACE UNDOTBS1 BEGIN BACKUP;

Tablespace altered.

SQL> !cp /oradata2/data1/dbase/undotbs01.dbf /oradata2/arju

SQL> ALTER TABLESPACE UNDOTBS1 END BACKUP;

Tablespace altered.

SQL> ALTER TABLESPACE SYSTEM BEGIN BACKUP;

Tablespace altered.

SQL> !cp /oradata2/data1/dbase/system01.dbf /oradata2/arju

SQL> ALTER TABLESPACE SYSTEM END BACKUP;

Tablespace altered.

SQL> ALTER TABLESPACE USERS BEGIN BACKUP;

Tablespace altered.

SQL> !cp /oradata2/6.dbf /oradata2/arju

SQL> ALTER TABLESPACE USERS END BACKUP;

Tablespace altered.

3)Take a backup of your control file also.
SQL> ALTER DATABASE BACKUP CONTROLFILE TO '/oradata2/arju/control.ctl';
Database altered.


Now, I have taken backup.With this backup I will work for next recovery purpose. In this example I will show if someone has accidentally drop one datafile what will be my procedure.

Suppose someone accidentally two files.

SQL> !rm /oradata2/data1/dbase/users01.dbf
SQL> !rm /oradata2/6.dbf

Whenever you want to do any operation it will raise error like following,

SQL> alter tablespace users offline;
alter tablespace users offline
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/oradata2/data1/dbase/users01.dbf'
ORA-27041: unable to open file
Solaris-AMD64 Error: 2: No such file or directory
Additional information: 3


User Managed Recovery of Datafile when your database is online (Archive Log Mode)
---------------------------------------------------------------------------
1)Make the affected tablespace offline.
SQL> ALTER TABLESPACE USERS OFFLINE IMMEDIATE;
Tablespace altered.

2)Copy the lost datafile from backup into the location of the datafile.

SQL> !cp /oradata2/arju/users01.dbf /oradata2/data1/dbase/
SQL> !cp /oradata2/arju/6.dbf /oradata2

3)Recover the tablespace.
SQL> RECOVER TABLESPACE USERS;
Media recovery complete.

4)Make the Tablespace online.
SQL> ALTER TABLESPACE USERS ONLINE;
Tablespace altered.

While database is offline and can't start it will show following message.

SQL> !rm /oradata2/data1/dbase/users01.dbf

SQL> startup
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 100663336 bytes
Database Buffers 58720256 bytes
Redo Buffers 6369280 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/oradata2/data1/dbase/users01.dbf'

1)In order to see which files need media recovery issue,

SQL> SELECT r.FILE# AS df#, d.NAME AS df_name, t.NAME AS tbsp_name,
d.STATUS, r.ERROR, r.CHANGE#, r.TIME
FROM V$RECOVER_FILE r, V$DATAFILE d, V$TABLESPACE t
WHERE t.TS# = d.TS#
AND d.FILE# = r.FILE#
; 2 3 4 5 6

DF# DF_NAME TBSP_NA STATUS ERROR CHANGE#
---- ----------------------------------- ------- ------- ---------- ---------
4 /oradata2/data1/dbase/users01.dbf USERS ONLINE FILE NOT 0
FOUND

2)Copy the lost file from backup to destination.

SQL> !cp /oradata2/arju/users01.dbf /oradata2/data1/dbase/users01.dbf

3)Recover the file and open the database.

SQL> RECOVER DATAFILE 4;
Media recovery complete.
SQL> ALTER DATABASE OPEN;

Database altered.


Note: If the location is damaged permamnent to make datafile in another location use,
SQL>ALTER DATABASE RENAME FILE '/oradata2/data1/dbase/users01.dbf' TO
'/disk2/users01.dbf';

If you do not have a backup of a specific datafile but you have archived log since the datafile creation then you can recover that datafile by creating an empty file by ALTER DATABASE CREATE DATAFILE and then simply perform recovery of the datafile.

Related Documents:
---------------------------
User Managed Restore Operation
What you will do if spfile lost Look at Solution of The problem Section
How to solve problem of inconsistent control file.
How to re-create Control file

User Managed Restore Operation in oracle Loss datafile loss controlfile loss spfile

Restore a datafile from backup is necessary whenever you disk is crushed or whenever there is user errors or someone has delete datafile. Remember that user managed restore operation is identical of the RMAN command RESTORE. If you like to restore any datafile then just copy it to the location in the parameter specified in control_files. If you like to restore any control file then just use copy command or any GUI tool in the destination which spfile or pfile points in control_files parameter.

So, in case of RMAN, RMAN>RESTORE datafile 2;
is same as of user managed $scp backup_of_the_file real_destination

In the following section I wrote in which circumstances what you have to do if you choose user managed recovery mechanism.

A)If you loss one or more datafile: If you lose one or more datafiles then copy the datafiles to the location where it was.(Equivalent to RMAN RESTORE) Or alternatively you can copy it in another location and point the control file to the new location by ALTER DATABASE RENAME FILE ' ' TO ' ';(Equivalent to RMAN SET NEWNAME ..) After copying recovery is required whenever the checkpoint SCN in the datafile header does not match the checkpoint SCN for the datafile that is recorded in the control file. Same as of RECOVER in RMAN.

B)If All copies of the current control file Lost: If all copies of your control files are lost then if you have backup of the control file then copy it to the location as it is located in CONTROL_FILES parameter in pfile/spfile. After copied to desired location you might have to open your database with RESETLOGS option.

If you don't have backup of control file then create a new one. This is described on
How to re-create Control file

C)If a copy of a multiplexed control file Lost: If one or two copies of control file are lost and in least one copy of the control file is intact then copy the intact control file to other locations as it was in CONTROL_FILES parameter inside pfile/spfile. If you have one good copy and you want to copy-this procedure is described on How to solve problem of inconsistent control file.If you cannot copy the control file to its original location, then edit the initialization parameter file to reflect a new location. Then, open the database.

D)If archived log lost that is needed for media recovery: If you have backup of archived log that is needed for recovery copy it to the location and perform recovery from there. The deleted archive logs that is not needed for media recovery(i.e the archived logs that were generated before taking backup) has no affect. But if the deleted archived log that is needed for media recovery and you have no backup then you must perform incomplete recovery (UNTIL SCN) and open the database with RESETLOGS open.


E)Spfile is Lost: If you loss your current spfile then using user managed recovery you can recover it. The detail procedure is discussed on What you will do if spfile lost Look at Solution of The problem Section

Related Documents:
-----------------------
What you will do if spfile lost Look at Solution of The problem Section
How to solve problem of inconsistent control file.
How to re-create Control file