•If we simply use RESTORE command then RMAN directs a server session to restore the file to default location that is overwrite the existing file.
•If we use RESTORE command with SET NEWNAME option then RMAN restore the file with specified location.
Example: RUN {
SET NEWNAME FOR DATAFILE '/oradata/datafile/1.dbf' TO '/tmp/1.dbf';
RESTORE DATAFILE '/oradata/datafile/1.dbf';
}
In this case, RMAN restore /oradata/datafile/1.dbf to /tmp/1.dbf
•RMAN always restore data file as image copy.
•After restoring a datafile to a new location if you want to use that then use the SWITCH command in order to update control file. RMAN SWITCH command is equivalent to SQL statement ALTER DATABASE RENAME FILE command.
Example: SWITCH DATAFILE '/oradata/datafile/1.dbf' TO DATAFILECOPY '/tmp/1.dbf';
•RMAN always select the most recent backup while restoring. If two backups are in the same point then RMAN prefers to use image copies over backup sets because RMAN can restore more quickly from image copies than from backup sets.
•During RESTORE operation if RMAN fails the restore one copy then i automatically searches other usable copy. If there is no backup exist then RMAN try to re-create the datafile.
•RMAN uses restore optimization to avoid restoring datafiles from backup when possible. If a datafile is already present in the correct location and its header contains the expected information, then RMAN does not restore the datafile from backup. However you can override this behavior by FORCE option of RESTORE command.
•If RMAN has a choice between archived redo logs and incremental backups then RMAN always choice incremental backups during recovery.If overlapping levels of incremental backups are available then RMAN choose the longest covering one.
Saturday, May 3, 2008
About RMAN channels
•An RMAN channel corresponds to one server session and it represents one stream of data to a device type.
•When you run a command that requires to use any server session then RMAN automatically allocates channel with the option specified in CONFIGURE command, if you don't explicitly allocate any channel.
•In the settings for which RMAN automatically allocate channel are.
CONFIGURE DEVICE TYPE ... PARALLELISM
CONFIGURE DEFAULT DEVICE TYPE
CONFIGURE CHANNEL
•You can override automatically allocated channel by explicitly specifying ALLOCATE CHANNEL within run block as follows,
RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE sbt;
BACKUP DATABASE;
}
•If you have any three above types of CONFIGURE settings then RMAN automatically allocates channel when you perform BACKUP, RESTORE, DELETE command or command within RUN block.
•The number of channels allocation is done by CONFIGURE DEVICE TYPE ... PARALLELISM is based on parallelism settings. If setttings is
CONFIGURE DEVICE TYPE DISK PARALLELISM 3;
then RMAN allocate 3 channels.
•Channel name is defined by ORA_devicetype_n, where
devicetype refers to the user's device type (such as DISK or sbt_tape) and
n refers to the channel number.
The thing need to know that RMAN output always displays sbt_tape whether the input is sbt or sbt_tape. If I have CONFIGURE DEVICE TYPE sbt PARALLELISM 2; then channel 1 will be named as ORA_SBT_TAPE_1 and channel 2 will be named as ORA_SBT_TAPE_2.
•Channel name prefix by ORA_ are reserved for RMAN. You cannot manually allocate a channel with a name that begins with ORA_.
•When you run a command that requires to use any server session then RMAN automatically allocates channel with the option specified in CONFIGURE command, if you don't explicitly allocate any channel.
•In the settings for which RMAN automatically allocate channel are.
CONFIGURE DEVICE TYPE ... PARALLELISM
CONFIGURE DEFAULT DEVICE TYPE
CONFIGURE CHANNEL
•You can override automatically allocated channel by explicitly specifying ALLOCATE CHANNEL within run block as follows,
RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE sbt;
BACKUP DATABASE;
}
•If you have any three above types of CONFIGURE settings then RMAN automatically allocates channel when you perform BACKUP, RESTORE, DELETE command or command within RUN block.
•The number of channels allocation is done by CONFIGURE DEVICE TYPE ... PARALLELISM is based on parallelism settings. If setttings is
CONFIGURE DEVICE TYPE DISK PARALLELISM 3;
then RMAN allocate 3 channels.
•Channel name is defined by ORA_devicetype_n, where
devicetype refers to the user's device type (such as DISK or sbt_tape) and
n refers to the channel number.
The thing need to know that RMAN output always displays sbt_tape whether the input is sbt or sbt_tape. If I have CONFIGURE DEVICE TYPE sbt PARALLELISM 2; then channel 1 will be named as ORA_SBT_TAPE_1 and channel 2 will be named as ORA_SBT_TAPE_2.
•Channel name prefix by ORA_ are reserved for RMAN. You cannot manually allocate a channel with a name that begins with ORA_.
Friday, May 2, 2008
Basic Steps of RMAN media Recovery
Step 1: Place the database in mount stage when you like to perform whole database recovery or open the database and offline the specified tablespace offline when performing tablespace recovery.
Step 2: Use SET UNTIL clause to perform incomplete recovery, that is recovery terminated up to the past time,SCN,restore point or log sequence number. Also you can use UNTIL clause with RESTORE and RECOVER command to perform incomplete recovery.
Step 3: Restore the files with RESTORE command.
Step 4: Recover the data files with RECOVER command.
Step5: Place the database in open stage if you did operation in MOUNT stage , or make the intended tablespace online if you placed tablespaces offline.
Step 2: Use SET UNTIL clause to perform incomplete recovery, that is recovery terminated up to the past time,SCN,restore point or log sequence number. Also you can use UNTIL clause with RESTORE and RECOVER command to perform incomplete recovery.
Step 3: Restore the files with RESTORE command.
Step 4: Recover the data files with RECOVER command.
Step5: Place the database in open stage if you did operation in MOUNT stage , or make the intended tablespace online if you placed tablespaces offline.
Thursday, May 1, 2008
Backup Retention Policy of RMAN
•Setting the RETENTION POLICY in RMAN keep track about the backup files and tells the report whether they are obsoleted and whether it is needed to backup the database or datafile.
•If you have flash recovery configured then the database automatically deletes unnecessary files from the flash recovery area based on its internal disk quota rules. The disk quota rules are distinct from the backup retention policy rules, but the database will never delete files in violation of the retention policy to satisfy the disk quota.
•If you don't have flash recovery configured then the database does not delete any file even they are obsolete.
•The configuration parameter RETENTION POLICY can be set by CONFIGURE RETENTION POLICY ....
•A backup becomes obsolete based on retention policy, that it is not needed for recovery.A backup becomes expired only when RMAN perform CROSSCHECK and can't find the file. (A most common is file is deleted by OS). Obsolete means "not needed," whereas expired means "not found."
•There is two mutually exclusive options for setting a retention policy; redundancy and recovery window.
To set Recovery Window of 3 days,
RMAN>CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;
To set Recovery Window of redundancy 2 copies,
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
Whenever retention policy is set to recovery window of 3 days then rman retains all information and backup data though which it can go to any point within 3 days from current date. The backup data which is not needed to go back any point within 3 days from current date are termed as obsolete. Suppose before 5 days ago I took an incremental level 0 backup and then I took incremental level 1 backup, now the backup before 5 days ago level 0 backup is not obsolete. Because this is base backup and needed if I want to back any point within 3 days. However, if I took a backup right 4 days ago then backup before 5 days ago become obsolete.
Whenever retention policy is set to redundancy of 2 copies the rman at least retains latest 2 copies of each datafile. If I took 3rd backup of datafile 3 then 1st backup of datafile 3 become obsolete.
•The default retention policy is REDUNDANCY = 1
•You can disable the retention policy by setting,
CONFIGURE RETENTION POLICY TO NONE;
If the retention policy is configured to NONE, then REPORT OBSOLETE and DELETE OBSOLETE do not consider any backups to be obsolete.
•If you have flash recovery configured then the database automatically deletes unnecessary files from the flash recovery area based on its internal disk quota rules. The disk quota rules are distinct from the backup retention policy rules, but the database will never delete files in violation of the retention policy to satisfy the disk quota.
•If you don't have flash recovery configured then the database does not delete any file even they are obsolete.
•The configuration parameter RETENTION POLICY can be set by CONFIGURE RETENTION POLICY ....
•A backup becomes obsolete based on retention policy, that it is not needed for recovery.A backup becomes expired only when RMAN perform CROSSCHECK and can't find the file. (A most common is file is deleted by OS). Obsolete means "not needed," whereas expired means "not found."
•There is two mutually exclusive options for setting a retention policy; redundancy and recovery window.
To set Recovery Window of 3 days,
RMAN>CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;
To set Recovery Window of redundancy 2 copies,
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
Whenever retention policy is set to recovery window of 3 days then rman retains all information and backup data though which it can go to any point within 3 days from current date. The backup data which is not needed to go back any point within 3 days from current date are termed as obsolete. Suppose before 5 days ago I took an incremental level 0 backup and then I took incremental level 1 backup, now the backup before 5 days ago level 0 backup is not obsolete. Because this is base backup and needed if I want to back any point within 3 days. However, if I took a backup right 4 days ago then backup before 5 days ago become obsolete.
Whenever retention policy is set to redundancy of 2 copies the rman at least retains latest 2 copies of each datafile. If I took 3rd backup of datafile 3 then 1st backup of datafile 3 become obsolete.
•The default retention policy is REDUNDANCY = 1
•You can disable the retention policy by setting,
CONFIGURE RETENTION POLICY TO NONE;
If the retention policy is configured to NONE, then REPORT OBSOLETE and DELETE OBSOLETE do not consider any backups to be obsolete.
Use SELECT ANY DICTIONARY or SELECT_CATALOG_ROLE or SELECT ANY TABLE
In this topic I will try to make you understand the differences between SELECT ANY DICTIONARY privilege, SELECT ANY TABLE privilege and SELECT_CATALOG_ROLE.
Before proceed it is nice if you remember that ,
•If you have O7_DICTIONARY_ACCESSIBILITY=TRUE then SELECT ANY TABLE privilege provides access to all SYS and non-SYS objects.
•If you have O7_DICTIONARY_ACCESSIBILITY=FALSE then SELECT ANY TABLE privilege provides access only to non-SYS objects.
•If only SELECT_CATALOG_ROLE is enabled then it provides access to all SYS views only.
•If only SELECT ANY DICTIONARY privilege is enabled then it provides access to SYS schema objects only.
•If both SELECT ANY TABLE and SELECT any DICTIONARY privilege is enabled then it allow access to all SYS and non-SYS objects.
•SELECT ANY DICTIONARY privilege and SELECT_CATALOG_ROLE has no affect over O7_DICTIONARY_ACCESSIBILITY settings.
To make the scenario more clear I will demonstrate an example over
1)ARJU schmea table named A. And over two
2)SYS schema objects OBJ$ Table and
3)SYS schema DBA_USERS view.
SQL> select object_type , object_name from dba_objects where object_name in ('OBJ$' ,'DBA_USERS') and owner='SYS';
OBJECT_TYPE OBJECT_NAME
------------------- --------------------
VIEW DBA_USERS
TABLE OBJ$
Workaround Example:
----------------------
A)Secnario 1:(When O7_DICTIONARY_ACCESSIBILITY is set to FALSE)
------------------
SQL> create user t identified by t;
User created.
SQL> grant create session to t;
Grant succeeded.
Have only Create Session Privilege
-------------------------------------
SQL> conn t/t
Connected.
SQL> select * from user_tables;
no rows selected
SQL> select * from arju.a;
select * from arju.a
*
ERROR at line 1:
ORA-00942: table or view does not exist
Have only Select Any Table Privilege
-----------------------------------
SQL> conn arju/a
Connected.
SQL> grant select any table to t;
Grant succeeded.
SQL> conn t/t
Connected.
User T can select Arju schema's obejct but failed on SYS schema objects.
SQL> select count(*) from arju.a;
COUNT(*)
----------
1
SQL> select count(*) from dba_users;
select count(*) from dba_users
*
ERROR at line 1:
ORA-00942: table or view does not exist
Have select_catalog_role only
---------------------------------
SQL> conn arju/a
Connected.
SQL> revoke select any table from t;
Revoke succeeded.
SQL> grant select_catalog_role to t;
Grant succeeded.
SQL> conn t/t
Connected.
User T can only select SYS schema Views.
SQL> select count(*) from dba_users;
COUNT(*)
----------
23
SQL> select * from arju.t;
select * from arju.t
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select count(*) from sys.obj$;
select count(*) from sys.obj$
*
ERROR at line 1:
ORA-00942: table or view does not exist
Have only Select Any Dictionary Privilege
-----------------------------------------------
SQL> conn arju/a
Connected.
SQL> revoke select_catalog_role from t;
Revoke succeeded.
SQL> grant select any dictionary to t;
Grant succeeded.
SQL> conn t/t
Connected.
User T can only select SYS schema objects.
SQL> select count(*) from dba_users;
COUNT(*)
----------
23
SQL> select count(*) from sys.obj$;
COUNT(*)
----------
51053
SQL> select * from arju.a;
select * from arju.a
*
ERROR at line 1:
ORA-00942: table or view does not exist
Have both SELECT ANY TABLE and SELECT ANY DICTIONARY Privilege
---------------------------------------------------------------------
Both system privileges together allow access to all SYS and non-SYS objects.
SQL> grant select any table , select any dictionary to t;
Grant succeeded.
SQL> conn t/t
Connected.
SQL> select count(*) from sys.obj$;
COUNT(*)
----------
51053
SQL> select count(*) from dba_users;
COUNT(*)
----------
23
SQL> select count(*) from arju.a;
COUNT(*)
----------
1
B)Scenario 2:(When O7_DICTIONARY_ACCESSIBILITY is set to TRUE)
----------------------------------------------------------------
Has only SELECT ANY TABLE privilege
-----------------------------------------
User T can now select all SYS and NO-SYS objects.
Related Documents:
---------------------
What is O7_DICTIONARY_ACCESSIBILITY
Before proceed it is nice if you remember that ,
•If you have O7_DICTIONARY_ACCESSIBILITY=TRUE then SELECT ANY TABLE privilege provides access to all SYS and non-SYS objects.
•If you have O7_DICTIONARY_ACCESSIBILITY=FALSE then SELECT ANY TABLE privilege provides access only to non-SYS objects.
•If only SELECT_CATALOG_ROLE is enabled then it provides access to all SYS views only.
•If only SELECT ANY DICTIONARY privilege is enabled then it provides access to SYS schema objects only.
•If both SELECT ANY TABLE and SELECT any DICTIONARY privilege is enabled then it allow access to all SYS and non-SYS objects.
•SELECT ANY DICTIONARY privilege and SELECT_CATALOG_ROLE has no affect over O7_DICTIONARY_ACCESSIBILITY settings.
To make the scenario more clear I will demonstrate an example over
1)ARJU schmea table named A. And over two
2)SYS schema objects OBJ$ Table and
3)SYS schema DBA_USERS view.
SQL> select object_type , object_name from dba_objects where object_name in ('OBJ$' ,'DBA_USERS') and owner='SYS';
OBJECT_TYPE OBJECT_NAME
------------------- --------------------
VIEW DBA_USERS
TABLE OBJ$
Workaround Example:
----------------------
A)Secnario 1:(When O7_DICTIONARY_ACCESSIBILITY is set to FALSE)
------------------
SQL> create user t identified by t;
User created.
SQL> grant create session to t;
Grant succeeded.
Have only Create Session Privilege
-------------------------------------
SQL> conn t/t
Connected.
SQL> select * from user_tables;
no rows selected
SQL> select * from arju.a;
select * from arju.a
*
ERROR at line 1:
ORA-00942: table or view does not exist
Have only Select Any Table Privilege
-----------------------------------
SQL> conn arju/a
Connected.
SQL> grant select any table to t;
Grant succeeded.
SQL> conn t/t
Connected.
User T can select Arju schema's obejct but failed on SYS schema objects.
SQL> select count(*) from arju.a;
COUNT(*)
----------
1
SQL> select count(*) from dba_users;
select count(*) from dba_users
*
ERROR at line 1:
ORA-00942: table or view does not exist
Have select_catalog_role only
---------------------------------
SQL> conn arju/a
Connected.
SQL> revoke select any table from t;
Revoke succeeded.
SQL> grant select_catalog_role to t;
Grant succeeded.
SQL> conn t/t
Connected.
User T can only select SYS schema Views.
SQL> select count(*) from dba_users;
COUNT(*)
----------
23
SQL> select * from arju.t;
select * from arju.t
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select count(*) from sys.obj$;
select count(*) from sys.obj$
*
ERROR at line 1:
ORA-00942: table or view does not exist
Have only Select Any Dictionary Privilege
-----------------------------------------------
SQL> conn arju/a
Connected.
SQL> revoke select_catalog_role from t;
Revoke succeeded.
SQL> grant select any dictionary to t;
Grant succeeded.
SQL> conn t/t
Connected.
User T can only select SYS schema objects.
SQL> select count(*) from dba_users;
COUNT(*)
----------
23
SQL> select count(*) from sys.obj$;
COUNT(*)
----------
51053
SQL> select * from arju.a;
select * from arju.a
*
ERROR at line 1:
ORA-00942: table or view does not exist
Have both SELECT ANY TABLE and SELECT ANY DICTIONARY Privilege
---------------------------------------------------------------------
Both system privileges together allow access to all SYS and non-SYS objects.
SQL> grant select any table , select any dictionary to t;
Grant succeeded.
SQL> conn t/t
Connected.
SQL> select count(*) from sys.obj$;
COUNT(*)
----------
51053
SQL> select count(*) from dba_users;
COUNT(*)
----------
23
SQL> select count(*) from arju.a;
COUNT(*)
----------
1
B)Scenario 2:(When O7_DICTIONARY_ACCESSIBILITY is set to TRUE)
----------------------------------------------------------------
Has only SELECT ANY TABLE privilege
-----------------------------------------
User T can now select all SYS and NO-SYS objects.
Related Documents:
---------------------
What is O7_DICTIONARY_ACCESSIBILITY
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER
Symtompts of the Problem:
---------------------------
Whenever you try to conenct to database by providing SYS user name and password it retuens error.
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER
Cause of The Problem:
--------------------
This is because of the parameter O7_DICTIONARY_ACCESSIBILITY settings to FALSE.
Access to dictionary objects is restricted to the users with the system privileges SYSDBA and SYSOPER. Connecting as SYSDBA gives a user unrestricted privileges to perform any operation on a database or the objects within a database. Data dictionary objects is under SYS schema and is protected by O7_DICTIONARY_ACCESSIBILITY to FALSE settings.
Workaround Example:
---------------------
1)Try to connect by user sys without sysdba privilege.
SQL> conn sys/a
ERROR:
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER
Warning: You are no longer connected to ORACLE.
2)Connect as sysdba and change O7_DICTIONARY_ACCESSIBILITY
SQL> conn / as sysdba
Connected.
SQL> SHOW PARAMETER O7_DICTIONARY_ACCESSIBILITY
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY boolean FALSE
SQL> ALTER SYSTEM SET O7_DICTIONARY_ACCESSIBILITY=TRUE scope=spfile;
System altered.
3)Since O7_DICTIONARY_ACCESSIBILITY is static parameter restart is necessary.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 109051944 bytes
Database Buffers 50331648 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.
4)Now connect as sys with only password.
SQL> conn sys/a
Connected.
SQL> show parameter O7_DICTIONARY_ACCESSIBILITY
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY boolean TRUE
5)Though you are SYS user but you have not currently have SYSDBA privilege. So, you can't do SYSDBA privilege tasks.
SQL> shutdown immediate;
ORA-01031: insufficient privileges
SQL> show user
USER is "SYS"
Caution:
-----------
Oracle Strongly recommends not to use O7_DICTIONARY_ACCESSIBILITY to TRUE.
Related Documents:
-------------------
What is O7_DICTIONARY_ACCESSIBILITY
---------------------------
Whenever you try to conenct to database by providing SYS user name and password it retuens error.
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER
Cause of The Problem:
--------------------
This is because of the parameter O7_DICTIONARY_ACCESSIBILITY settings to FALSE.
Access to dictionary objects is restricted to the users with the system privileges SYSDBA and SYSOPER. Connecting as SYSDBA gives a user unrestricted privileges to perform any operation on a database or the objects within a database. Data dictionary objects is under SYS schema and is protected by O7_DICTIONARY_ACCESSIBILITY to FALSE settings.
Workaround Example:
---------------------
1)Try to connect by user sys without sysdba privilege.
SQL> conn sys/a
ERROR:
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER
Warning: You are no longer connected to ORACLE.
2)Connect as sysdba and change O7_DICTIONARY_ACCESSIBILITY
SQL> conn / as sysdba
Connected.
SQL> SHOW PARAMETER O7_DICTIONARY_ACCESSIBILITY
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY boolean FALSE
SQL> ALTER SYSTEM SET O7_DICTIONARY_ACCESSIBILITY=TRUE scope=spfile;
System altered.
3)Since O7_DICTIONARY_ACCESSIBILITY is static parameter restart is necessary.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 109051944 bytes
Database Buffers 50331648 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.
4)Now connect as sys with only password.
SQL> conn sys/a
Connected.
SQL> show parameter O7_DICTIONARY_ACCESSIBILITY
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY boolean TRUE
5)Though you are SYS user but you have not currently have SYSDBA privilege. So, you can't do SYSDBA privilege tasks.
SQL> shutdown immediate;
ORA-01031: insufficient privileges
SQL> show user
USER is "SYS"
Caution:
-----------
Oracle Strongly recommends not to use O7_DICTIONARY_ACCESSIBILITY to TRUE.
Related Documents:
-------------------
What is O7_DICTIONARY_ACCESSIBILITY
ORA-01994: GRANT failed: password file missing or disabled
Symptoms of The Error:
-------------------------
In unix system whenever you tried to grant sysdba privilege to a user it returns with error.
ORA-01994: GRANT failed: password file missing or disabled.
Cause of The Problem:
------------------------
The oracle software owner is not the owner of the passwordfile.
Solutions of The Problem:
----------------------------
1)Log on to Unix box as the same user who owns the file $ORACLE_HOME and create the password file as follows.
$orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=password entries=4 force=y
2)Now grant sysdba privilege to the users that you need. Check V$PWFILE_USERS about the entry.
SQL>GRANT SYSDBA to SYSTEM;
3)Check the owner of $ORACLE_HOME/dbs/orapw$ORACLE_SID
This would typically,
SQL> !ls -l $ORACLE_HOME/dbs/orapw$ORACLE_SID
-rw-r----- 1 oracle oinstall 1536 Apr 23 16:31 /oracle/app/oracle/product/10.2.0/db_1/dbs/orapwdata1
4)If it is not to dba then change the ownership by
$chown oracle:dba $ORACLE_HOME/dbs/orapw$ORACLE_SID
Also change permission by,
$chmod 4640 $ORACLE_HOME/dbs/orapw$ORACLE_SID
Related Documents:
---------------------
What isRemote Login Passwordfile
Abour Database Authentication
-------------------------
In unix system whenever you tried to grant sysdba privilege to a user it returns with error.
ORA-01994: GRANT failed: password file missing or disabled.
Cause of The Problem:
------------------------
The oracle software owner is not the owner of the passwordfile.
Solutions of The Problem:
----------------------------
1)Log on to Unix box as the same user who owns the file $ORACLE_HOME and create the password file as follows.
$orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=password entries=4 force=y
2)Now grant sysdba privilege to the users that you need. Check V$PWFILE_USERS about the entry.
SQL>GRANT SYSDBA to SYSTEM;
3)Check the owner of $ORACLE_HOME/dbs/orapw$ORACLE_SID
This would typically,
SQL> !ls -l $ORACLE_HOME/dbs/orapw$ORACLE_SID
-rw-r----- 1 oracle oinstall 1536 Apr 23 16:31 /oracle/app/oracle/product/10.2.0/db_1/dbs/orapwdata1
4)If it is not to dba then change the ownership by
$chown oracle:dba $ORACLE_HOME/dbs/orapw$ORACLE_SID
Also change permission by,
$chmod 4640 $ORACLE_HOME/dbs/orapw$ORACLE_SID
Related Documents:
---------------------
What isRemote Login Passwordfile
Abour Database Authentication
Subscribe to:
Posts (Atom)