How to determine the name of the trace file to be generated

In many cases we need to find out the name of the latest trace file generated in the USER_DUMP_DEST directory. What we usually do is go to physically to the USER_DUMP_DEST with the operating system browser and sort all the files by date and look for latest files. We can remove this hassle easily if we know what would be the trace file name in advance.

For example issuing,
D:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 10 21:58:15 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter database backup controlfile to trace;

Database altered.

SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
will generated a trace file inside USER_DUMP_DEST. Let's see the location of USER_DUMP_DEST.

If you use Sql*plus then issue,
SQL> show parameter user_dump_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest string E:\ORACLE\PRODUCT\10.2.0\ADMIN
\ORCL\UDUMP

Querying from v$parameter,
SQL> select value from v$parameter where name='user_dump_dest';

VALUE
-------------------------------------------------------------------
E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP

On windows changes to directory E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP and issue,
E:\oracle\product\10.2.0\admin\orcl\udump>dir /OD

The latest files are for latest trace.

Now it would be quite easy task if we knew the name of the trace file to be generated by ALTER DATABASE command.

In advance we can get the trace file name by,

SQL> set linesize 130
SQL> COL trace_file FOR A60
SQL> SELECT s.sid,
           s.serial#,
           pa.value || '\' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||
           '_ora_' || p.spid || '.trc' AS trace_file
    FROM   v$session s,
           v$process p,
           v$parameter pa
    WHERE  pa.name = 'user_dump_dest'
    AND    s.paddr = p.addr
    AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

       SID    SERIAL# TRACE_FILE
---------- ---------- ------------------------------------------------------------
       146         11 E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP\orcl_ora_4968.trc

So the trace file to be generated now will be named as orcl_ora_4968.trc

So now issuing, "alter database backup controlfile to trace" will generate the file named E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP\orcl_ora_4968.trc.

On windows you can issue like this,
SQL> alter database backup controlfile to trace;

Database altered.

SQL> host notepad E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP\orcl_ora_4968.trc
Related Documents
http://arjudba.blogspot.com/2009/04/how-to-tracediagnosis-oracle-data-pump.html

ORA-00845: MEMORY_TARGET not supported on this system

Problem Description
While creating a startup database using dbca the database creation GUI gives error message in a pop up window,
ORA-00845: MEMORY_TARGET not supported on this system
from where you can ignore the error message.
The similar scenario also occur whenever you try to start your database then startup shows error message like below.

SQL> STARTUP
ORA-00845: MEMORY_TARGET not supported on this system

Cause of the Problem
•Starting from Oracle 11g the automatic memory management feature is now defined with parameter MEMORY_TARGET and MEMMORY_MAX_TARGET.

•On linux file system the shared memory need to be mounted on /dev/shm directory on the operating system.

•And the size of /dev/shm needs to be greater than MEMORY_TARGET or MEMMORY_MAX_TARGET.

•The AMM (Automatic Memory Management) now in 11g manages both SGA and PGA together by MMAN process.

•The MEMORY_TARGET parameter in 11g comes for (SGA_TARGET+PGA_AGGREGATE_TARGET) which was in 10g.

•And MEMORY_MAX_TARGET parameter in 11g comes instead of SGA_MAX_TARGET parameter which was in 10g.

•The ORA-00845:can arises for the following two reasons on linux system.

1)If the shared memory which is mapped to /dev/shm directory is less than the size of MEMORY_TARGET or MEMORY_MAX_TARGET.
or,
2)If the shared memory is not mapped to /dev/shm directory.

Solution of the Problem
Make sure /dev/shm is properly mounted. You can see it by,
#df -h or #df -k command.
The output should be similar like

$ df -k
Filesystem            Size  Used Avail Use% Mounted on
...
shmfs                 1G    512M 512M  50% /dev/shm

We see here for /dev/shm we have assigned 1G memory. Now if you set MEMORY_TARGET more than 1G then above ORA-845 will arise. For example if you have MEMORY_TARGET or MEMORY_MAX_TARGET set to 12G then you can mount shared memory to 13g like below.
As a root user,
# mount -t tmpfs shmfs -o size=13g /dev/shm
In order to make the settings persistence so that it will affect after restarting machine add an entry in /etc/fstab similar to the following:

shmfs /dev/shm tmpfs size=13g 0

ORA-32010: cannot find entry to delete in SPFILE

Error Description
With ALTER SYSTEM SET ..... SID='*' we can set the parameter value to all instances of the database( Like in RAC). But to set parameter only to a single instance of a database we use ALTER SYSTEM SET ..... SID='instance_name';

Similarly to reset a parameter value from all instances we have to use ALTER SYSTEM RESET ... sid='*' and to reset a parameter value from a single instance we have to use ALTER SYSTEM RESET ..... sid='instance_name'.

Now while we reset value from a single instance it fails with error ORA-32010.
SQL> alter system reset open_cursors scope=both sid='arju';
alter system reset open_cursors scope=both sid='arju'
*
ERROR at line 1:
ORA-32010: cannot find entry to delete in SPFILE

Cause and Solution of The Problem
Inside the spfile if you notice the parameter is set for all instances. That is with *. Oracle try to finds/looks in spfile for parameter with name arju.open_cursors but could not find that and so can't reset that and error comes.
Here is that,

SQL> create pfile='/oradata2/mynewpfile' from spfile;
File created.

SQL> !vi /oradata2/mynewpfile
*.open_cursors=300

However if there is an entry inside spfile with arju.open_cursors then it would work fine and the ALTER SYSTEM RESET open_cursors scope=both sid='arju' would work fine.

We can test is by modifying pfile manually and then spfile.
SQL> shutdown abort
ORACLE instance shut down.

SQL> create spfile from pfile='/oradata2/mynewpfile';
File created.

SQL> startup
ORACLE instance started.

Total System Global Area 234881024 bytes
Fixed Size 2019800 bytes
Variable Size 121638440 bytes
Database Buffers 104857600 bytes
Redo Buffers 6365184 bytes
Database mounted.
Database opened.

SQL> alter system reset open_cursors scope=both sid='arju';
System altered.

We could also be able to do the test in the spfile itself. Like,

SQL> alter system reset open_cursors scope=both sid='arju';
alter system reset open_cursors scope=both sid='arju'
*
ERROR at line 1:
ORA-32010: cannot find entry to delete in SPFILE

SQL> alter system set open_cursors=400 scope=both sid='arju';
System altered.


SQL> alter system reset open_cursors scope=both sid='arju';
System altered.

However if you want to want to reset the memort value for instance * from instance arju then it will fail with error ORA-32009.

SQL> alter system reset open_cursors scope=both sid='*';
alter system reset open_cursors scope=both sid='*'
*
ERROR at line 1:
ORA-32009: cannot reset the memory value for instance * from instance arju

In that case we can be able to only reset it from spfile like,
SQL> alter system reset open_cursors scope=spfile sid='*';
System altered.

Related Documents
Pfile and Spfile in Oracle.
How to avoid of recreating pfile or spfile if any error in spfile

Why create between pfile and spfile works if shutdown database

Sometimes it may arises question that while database is shutdown then you can still connect to database as sysdba and now issue create spfile from pfile or create pfile from spfile and it works fine. But database is shutdown and then how oracle maintains where is spfile and pfile.

The answer is create pfile and spfile can only can done whenever you connect as sysdba. And if you connect as sysdba then a dedicated server is established which does that create operation.

You can easily find it. Suppose I am log on to my unix machine and invoke the status of process arju.
-bash-3.00$ ps -ef |grep arju
oracle 27060 1 0 04:58:09 ? 0:07 oraclearju (LOCAL=NO)
oracle 26970 1 0 04:54:43 ? 0:21 ora_ckpt_arju
oracle 26974 1 0 04:54:43 ? 0:00 ora_reco_arju
oracle 16423 16224 0 23:00:25 pts/3 0:00 grep arju
oracle 27024 1 0 04:55:00 ? 0:08 oraclearju (LOCAL=NO)
oracle 27028 1 0 04:55:15 ? 0:08 oraclearju (LOCAL=NO)
oracle 26972 1 0 04:54:43 ? 0:04 ora_smon_arju
oracle 26976 1 0 04:54:43 ? 0:21 ora_cjq0_arju
oracle 26982 1 0 04:54:44 ? 0:00 ora_d000_arju
oracle 27030 1 0 04:55:15 ? 0:37 oraclearju (LOCAL=NO)
oracle 26964 1 0 04:54:43 ? 0:02 ora_mman_arju
oracle 26994 1 0 04:54:49 ? 0:01 ora_arc1_arju
oracle 27002 1 0 04:54:50 ? 0:00 ora_qmnc_arju
oracle 27032 1 0 04:55:15 ? 0:10 oraclearju (LOCAL=NO)
oracle 26968 1 0 04:54:43 ? 0:09 ora_lgwr_arju
oracle 27022 1 0 04:55:00 ? 0:01 ora_q000_arju
oracle 26980 1 0 04:54:44 ? 0:35 ora_mmnl_arju
oracle 26984 1 0 04:54:44 ? 0:00 ora_s000_arju
oracle 26962 1 0 04:54:43 ? 0:03 ora_psp0_arju
oracle 26966 1 0 04:54:43 ? 0:06 ora_dbw0_arju
oracle 26978 1 0 04:54:43 ? 0:07 ora_mmon_arju
oracle 26960 1 0 04:54:43 ? 0:16 ora_pmon_arju
oracle 26992 1 0 04:54:49 ? 0:01 ora_arc0_arju
oracle 27040 1 0 04:55:52 ? 0:00 ora_q002_arju

Now I connect as sysdba and see the status.
-bash-3.00$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 8 23:00:31 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> !ps -ef |grep arju
oracle 27060 1 0 04:58:09 ? 0:07 oraclearju (LOCAL=NO)
oracle 26970 1 0 04:54:43 ? 0:21 ora_ckpt_arju
oracle 26974 1 0 04:54:43 ? 0:00 ora_reco_arju
oracle 16433 1 0 23:00:33 ? 0:00 ora_m001_arju
oracle 16429 1 1 23:00:30 ? 0:00 ora_j000_arju
oracle 27024 1 0 04:55:00 ? 0:08 oraclearju (LOCAL=NO)
oracle 27028 1 0 04:55:15 ? 0:08 oraclearju (LOCAL=NO)
oracle 26972 1 0 04:54:43 ? 0:04 ora_smon_arju
oracle 26976 1 0 04:54:43 ? 0:21 ora_cjq0_arju
oracle 26982 1 0 04:54:44 ? 0:00 ora_d000_arju
oracle 27030 1 0 04:55:15 ? 0:37 oraclearju (LOCAL=NO)
oracle 26964 1 0 04:54:43 ? 0:02 ora_mman_arju
oracle 26994 1 0 04:54:49 ? 0:01 ora_arc1_arju
oracle 27002 1 0 04:54:50 ? 0:00 ora_qmnc_arju
oracle 27032 1 0 04:55:15 ? 0:10 oraclearju (LOCAL=NO)
oracle 26968 1 0 04:54:43 ? 0:09 ora_lgwr_arju
oracle 27022 1 0 04:55:00 ? 0:01 ora_q000_arju
oracle 26980 1 0 04:54:44 ? 0:35 ora_mmnl_arju
oracle 16431 16430 0 23:00:31 ? 0:00 oraclearju (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 26984 1 0 04:54:44 ? 0:00 ora_s000_arju
oracle 26962 1 0 04:54:43 ? 0:03 ora_psp0_arju
oracle 16436 16434 0 23:00:34 pts/3 0:00 grep arju
oracle 26966 1 0 04:54:43 ? 0:06 ora_dbw0_arju
oracle 26978 1 0 04:54:43 ? 0:07 ora_mmon_arju
oracle 26960 1 0 04:54:43 ? 0:16 ora_pmon_arju
oracle 26992 1 0 04:54:49 ? 0:01 ora_arc0_arju
oracle 27040 1 0 04:55:52 ? 0:00 ora_q002_arju
oracle 16434 16430 0 23:00:34 pts/3 0:00 /usr/bin/bash -c ps -ef |grep arju

Here you see a dedicated server is established as you connect as sysdba.
Now shutdown database and see the process status.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !ps -ef |grep arju
oracle 16540 16430 0 23:04:37 pts/3 0:00 /usr/bin/bash -c ps -ef |grep arju
oracle 16542 16540 0 23:04:37 pts/3 0:00 grep arju
oracle 16431 16430 1 23:00:31 ? 0:01 oraclearju (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))

We still see the dedicated server as we still connect to as sysdba.
Now exit from connecting as sysdba and observe the result.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
-bash-3.00$ ps -ef |grep arju
oracle 16544 16224 0 23:04:44 pts/3 0:00 grep arju

Nothing here now.

Related Documents
How to avoid of recreating pfile or spfile if any error in spfile

Recover database Issue after missing spfile or pfile

How to know My Database Start with Spfile/Pfile?

How to Restore spfile from backup

Pfile and Spfile in Oracle.

How to restore an Spfile from autobackup older than 7 days

How to Start your database with non default spfile

How to avoid of recreating pfile or spfile if any error in spfile

Let us some talk about pfile and spfile. Suppose I have done some wrong-edit inside spfile using ALTER SYSTEM SET ... SCOPE=SPFILE from database. Now the next startup process of my database will fail with oracle errors. The conventional approach of solution to this problem is,
1)Connect to SQL*plus as sysdba
2)Create pfile from the spfile.
3)Edit the pfile and correct the parameter settings.
4)Create spfile from the edited correct pfile.
5)Startup your database with the spfile.

But we can omit these steps easily by just creating a pfile and invoke the name of the spfile inside the pfile. After invoking spfile parameter inside pfile in the next line specify the name of the parameter which is cause problem of startup. In this case we gave multiple values of the parameter and oracle will use the last one. So override the invalid parameter value with the valid one and thus will startup the create. After startup we can set valid value in the spfile.

The following example will make you a clear idea.

Step 1:
I have set background_dump_dest parameter to an invalid location in the spfile.
SQL> alter system set background_dump_dest='/oradata2/testing' scope=spfile;
System altered.

Now shutdown the database and start it up.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-00444: background process "MMAN" failed while starting
ORA-07446: sdnfy: bad value '' for parameter .

Step 2:
Startup fails as we set invalid location to background_dump_dest. Now create a pfile with an entry of spfile and give the location of the spfile. After specify specify the parameter which cause problem(background_dump_dest) and use the valid value. This setting will override the setting inside spfile and thus will help to startup the database.

SQL> !vi /oradata2/mypfile.ora
spfile=/oracle/app/oracle/product/10.2.0/db_1/dbs/spfilearju.ora
background_dump_dest='/oradata1'

Step 3:
Now start the database with newly created pfile. In fact spfile.
SQL> startup pfile=/oradata2/mypfile.ora
ORACLE instance started.

Total System Global Area 234881024 bytes
Fixed Size 2019800 bytes
Variable Size 113249832 bytes
Database Buffers 113246208 bytes
Redo Buffers 6365184 bytes
Database mounted.
Database opened.

Step 4:
And it worked!!! Now set valid value to the parameter background_dump_dest inside spfile.

SQL>  show parameter background_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest                 string      /oradata1

SQL> alter system set background_dump_dest='/oradata1' scope=spfile;
System altered.

In this way we can minimize our steps as well as downtime.

Related Documents
Recover database Issue after missing spfile or pfile

How to know My Database Start with Spfile/Pfile?

How to Restore spfile from backup

Pfile and Spfile in Oracle.

How to restore an Spfile from autobackup older than 7 days

How to Start your database with non default spfile

List of Parameters that must have identical in RAC database

In a RAC database there may contain several instances and the initialization parameters of all of the instances should not have identical setting. In fact the initialization parameters that are critical for database creation and affect certain database operations must have the same value for every instance in an Oracle RAC database.

Below is the list of initialization parameters that must have identical settings for all instances in a RAC database.

1)ACTIVE_INSTANCE_COUNT

2)ARCHIVE_LAG_TARGET

3)CLUSTER_DATABASE

4)CLUSTER_DATABASE_INSTANCES

5)COMPATIBLE

6)CONTROL_FILES

7)DB_BLOCK_SIZE

8)DB_DOMAIN

9)DB_FILES

10)DB_NAME

11)DB_RECOVERY_FILE_DEST

12)DB_RECOVERY_FILE_DEST_SIZE

13)DB_UNIQUE_NAME

14)DML_LOCKS (Only if set to zero).

15)INSTANCE_TYPE (RDBMS or ASM)

16)PARALLEL_MAX_SERVERS

17)REMOTE_LOGIN_PASSWORD_FILE

18)UNDO_MANAGEMENT

Related Documents

Pre-Installation RAC environement setup check with CVU

Configure Network for oracle RAC installation

Various Tools for Installing, Configuring Oracle RAC

Overview of Oracle Clusterware and Oracle RAC

Configure Shared Storage in Oracle RAC installation

Oracle RAC Software Components

Configure Raw Devices for ASM in RAC.

Requirements to check before install Oracle RAC

Configure server to install Oracle RAC

Difference between V$parameter and v$spparameter

V$PARAMETER
It displays the information about initialization parameters that are currently in effect for the session.

V$SYSTEM_PARAMETER
In this view it displays what will be the value if a new session inherits parameter values from the instance-wide values.

V$SPPARAMETER
It displays the information about contents of the server parameter file. If a server parameter file was not used to start the instance, then ISSPECIFIED column contains FALSE value.

V$SYSTEM_PARAMETER2
It displays the information about initialization parameters that are currently in effect for the instance, with each list parameter value appearing as a row in the view.

You may mess up in this stage. An example will make you clear through these parameter as well as ALTER SYSTEM SET value settings.

Let's experiment over audit_file_dest parameter.

SQL> show parameter audit_file_dest

NAME                        TYPE           VALUE
------------------          ------        --------------------------------------
audit_file_dest             string /oracle/app/oracle/product/10.2.0/db_1/admin/ARJU/adump

Only setting this in memory.
SQL> alter system set audit_file_dest='/oradata2' DEFERRED scope=memory;
System altered.

SQL> select value from V$SYSTEM_PARAMETER where name='audit_file_dest';
VALUE
----------------------------------------------------------------------
/oradata2

SQL> select value from V$SYSTEM_PARAMETER2 where name='audit_file_dest';
VALUE
----------------------------------------------------------------------
/oradata2

SQL> select value from v$parameter where name='audit_file_dest';
VALUE
----------------------------------------------------------------------
/oracle/app/oracle/product/10.2.0/db_1/admin/ARJU/adump

SQL> select value from v$spparameter where name='audit_file_dest';
VALUE
----------------------------------------------------------------------
/oracle/app/oracle/product/10.2.0/db_1/admin/ARJU/adump

Change only in spfile.
SQL> alter system set audit_file_dest='/backup1' scope=spfile;
System altered.

SQL> select value from V$SYSTEM_PARAMETER where name='audit_file_dest';
VALUE
----------------------------------------------------------------------
/oradata2

SQL> select value from V$SYSTEM_PARAMETER2 where name='audit_file_dest';

VALUE
----------------------------------------------------------------------
/oradata2

SQL> select value from v$parameter where name='audit_file_dest';
VALUE
----------------------------------------------------------------------
/oracle/app/oracle/product/10.2.0/db_1/admin/ARJU/adump

SQL> select value from v$spparameter where name='audit_file_dest';
VALUE
----------------------------------------------------------------------
/backup1

Related Documents
Using ALTER SYSTEM to Change Initialization Parameter Values

Database Startup Fails With Errors ORA-01078 And ORA-27046 Or ORA-01078

Error Description:
-----------------------
Database startup fails with error

SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '......'

or,

SQL> startup
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '.......'
ORA-27046: file size is not a multiple of logical block size
OSD-04012: file size mismatch (OS 2558)

Cause of The problem:
------------------------------
The error can be happened in many scenarios.

1)The ORACLE_SID environmental variable is set improperly.

2)The error occurred as database could not find the spfile and pfile on the default location or specified location.(if startup pfile= is specified)

3)The spfile exists in default location but it is corrupted and hence reported ORA-01078.

4)If spfile exists in non-default location and we started by STARTUP pfile= where inside pfile it holds the location of spfile=location then error reported ORA-1078 along with ORA-27046. This scenario is explained in How to start your database with non-default spfile.

Solution of The problem;
------------------------------
At first check whether your environment variable ORACLE_SID is set properly or not. On unix it is case sensitive. So dbase and Dbase is not same.

Is there is no way to repair or modify an spfile so try to solve the problem in following order.

1)If you have good backup of spfile then restore it. From RMAN you can easily do it if you have autobackup of controlfile. It is described in How to restore spfile by RMAN

2)You can get your pfile at the location $ORACLE_HOME/admin/$ORACLE_SID/pfile/. A typical file name is init.ora.418200821147. From that location first copy to another location like in /oradata2/pfile and then edit the pfile as necessary and try to make a spfile from that.
$SQL / as sysdba
$CREATE SPFILE FROM PFILE='/oradata2/pfile';

3)If the spfile backup does not exist then look at the database alert log file which is located in $ORACLE_HOME/admin/$ORACLE_SID/bdump. In the alert log file the list of non-default parameters listed. So create a pfile from spfile and later create spfile.

4)If you have corrupted spfile then from that, using binary utilities like strings available on unix open that and create pfile. And then create spfile from that. Like,

i)Follow either a or b part.

a)$strings /oracle/app/oracle/product/10.2.0/db_1/dbs/spfiledbase.ora >/export/home/pfile.ora

Now edit the /export/home/pfile.ora if any wrong character and then create spfile from that.
SQL>!vi /export/home/pfile.ora
SQL>create spfile from pfile='/export/home/pfile.ora';
SQL>startup

b)$strings /oracle/app/oracle/product/10.2.0/db_1/dbs/spfiledbase.ora
copy the usable contents inside it Open another file and paste contents into it.

SQL>!vi /export/home/oracle/test.txt

Create spfile from pfile.

SQL> create spfile from pfile='/export/home/oracle/test.txt';
File created.

Then start the database.

Related Documents:

http://arjudba.blogspot.com/2008/04/how-to-know-my-database-start-with.html

http://arjudba.blogspot.com/2008/04/pfile-and-spfile-in-oracle.html
http://arjudba.blogspot.com/2008/04/how-to-start-your-database-with-no.html

What is REMOTE_LOGIN_PASSWORDFILE?

•The remote REMOTE_LOGIN_PASSWORDFILE instance parameter controls whether it is possible to connect to the instance as a user with the SYSDBA privilege over the network.

•This parameter is string type and can have two values either NONE or SHARED. The default value is shared. In fact the value EXCLUSIVE is supported for backward compatibility. It now has the same behavior as the value SHARED.

•When this parameter is set to NONE the only way to get a SYSDBA connection is to log on to the operating system of the server machine as a member of the operating system group that owns the Oracle software.

•Setting this parameter to either EXCLUSIVE or SHARED gives users another way in. They can log on to the server as SYSDBA even if they are not a member of the Oracle software owning group, or even if they are coming in across the network, they can still connect as SYSDBA.

•The passwords are embedded, in encrypted form, in an operating system file in the location $ORACLE_HOME/dbs/orapw on Unix, or %ORACLE_HOME%\pwd.ora database on Windows.

•The V$PWFILE_USERS view shows you which users have their passwords entered in the password file, and whether they have the SYSOPER privilege, the SYSDBA privilege, or both.

Related Documents:
Database Authentication

What is O7_DICTIONARY_ACCESSIBILITY and effect of it

•O7_DICTIONARY_ACCESSIBILITY is a boolean type parameter in Oracle.

•The default value of this parameter is false.

•If O7_DICTIONARY_ACCESSIBILITY is set to false, then the SELECT ANY TABLE privilege allows access to views or tables in any schema except the SYS schema (data dictionary tables cannot be accessed). The system privilege EXECUTE ANY PROCEDURE allows access on the procedures in any schema except the SYS schema.

•If you change the parameter to TRUE, then ANY really does mean ANY, and SELECT ANY TABLE privilege allows access to see the data dictionary as well as all user data including SYS schema.

•If this parameter is set to false and you need to access objects in the SYS schema, then you must be granted explicit object privileges.

•To allow to select data dictionary views and packages SELECT_CATALOG_ROLE roles in explicitly needed.

•SELECT ANY DICTIONARY privilege lets you selectively override the default FALSE setting of the O7_DICTIONARY_ACCESSIBILITY initialization parameter.

•O7_DICTIONARY_ACCESSIBILITY is a static parameter. So if you use spfile then after setting it by
SQL>ALTER SYSTEM SET O7_DICTIONARY_ACCESSIBILITY=TRUE SCOPE=SPFILE; you need to restart your database in order to take affect.

Related Documents:
Difference between select any dictionary and select any Table

Using ALTER SYSTEM to Change Initialization Parameter Values

Whenever you use ALTER SYSTEM SET parameter_name=value what will happened to the parameter? Will the setting is permanent or temporary or will the settings will be affected after restarting the database instance? I will try to make clear this point in my next section.

1)The first thing is need to remember that whenever we issue ALTER SYSTEM command nothing to do with pfile.That is if you start your database with pfile the settings using ALTER SYSTEM can never be permanent. From database pfile can't be modified.

2)If you use ALTER SYSTEM SET command only without SCOPE then an scope is automatically appended with the command.The settings of the SCOPE is as follows.

-Scope parameter can have three values, BOTH,MEMORY,SPFILE.

-The default is SCOPE=BOTH if a server parameter file was used to start up the instance.

-The default is SCOPE=MEMORY if a text initialization parameter file was used to start up the instance.

3)If you explicitly assign SCOPE value then their persistence is as follows.

SCOPE = SPFILE

-The change is applied in the server parameter file only. The effect is as follows:
-For dynamic parameters, the change is effective at the next startup and is persistent.
-For static parameters, the behavior is the same as for dynamic parameters. This is the only SCOPE specification allowed for static parameters.

SCOPE = MEMORY

-The change is applied in memory only.
-For dynamic parameters, the effect is immediate, but it is not persistent because the server parameter file is not updated.
-For static parameters, this specification is not allowed.

SCOPE = BOTH

-The change is applied in both the server parameter file and memory.
-For dynamic parameters, the effect is immediate and persistent.
-For static parameters, this specification is not allowed.

4)SID

The SID clause is relevant only in a RAC environment. With ALTER SYSTEM SET SID clause is specified to indicate in which database the settings will be invoked.

-Specify SID = '*' if you want Oracle Database to change the value of the parameter for all instances.

-Specify SID = 'sid_of_a_database' if you want Oracle Database to change the value of the parameter only for the instance sid.

5)For dynamic parameters, you can also specify the DEFERRED keyword. When specified, the change is effective only for future sessions. An optional COMMENT clause lets you associate a text string with the parameter update. When you specify SCOPE as SPFILE or BOTH, the comment is written to the server parameter file.

6)If you start your database by SPFILE then you have three option to choose, but if you start your database by PFILE then you have the only option to choose SCOPE=MEMORY which is default.

Example
To set in ALL instances in RAC of sga_taget to 160M both in memory and spfile issue,
SQL> ALTER SYSTEM SET SGA_TARGET=160M scope=both SID='*';

To set sga_target for the current instances only in memory to 160M issue,
SQL> ALTER SYSTEM SET SGA_TARGET=160M scope=memory
SID='dbase';

To set audit_file_dest for the subsequent session use,
alter system set audit_file_dest='/oradata2' DEFERRED ;


Related Link:
------------------
Know Database Initialization Parameter

Change NLS_DATE_FORMAT Parmanently

The parameter NLS_DATE_FORMAT specifies the default date format to use with the TO_CHAR and TO_DATE functions. The default value of this parameter is determined by NLS_TERRITORY.

This parameter is modifiable and can be set through ALTER SESSION parameter. If you start your database with spfile then you can set this parameter by ALTER SYSTEM SET command.

So the setting of this parameter by ALTER SESSION is transient. If we want to make it permanent then then following steps should be performed.

1)Create pfile='1.txt' from spfile;
2)Edit pfile 1.txt and entry NLS_DATE_FORMAT as you wish the format.
3.start the database with pfile and create spfile from pfile.
4)Shutdown and Start the database.
5)Now query. Date will be shown as of format specified in NLS_DATE_FORMAT.

Example:
----------
1)SQL> create pfile='/oradata/1.txt' from spfile;

On unix system,
2)SQL> !vi /oradata/1.txt and entry,
*.nls_date_format='DD-MM-YY'

On windows just open pfile with any editor software like notepad and give an entry of nls_date_format.
3)SQL> startup pfile='/oradata/1.txt'

4)SQL> select sysdate from dual;
SYSDATE
--------
15-04-08

5)SQL> create spfile from pfile='/oradata/1.txt';

6)SQL> shutdown imemdiate;

7)SQL> startup

8)SQL> select sysdate from dual;

SYSDATE
--------
15-04-08

How to Start your database with non default spfile

All of you know that whenever it is invoked startup of a database then

1)First search for a spfile on it's default location named spfile$ORACLE_SID.ora

2)If not found then it searches for spfile.ora on the default location

3)If also not found then it search pfile on the default location.

But it is also not found then error arises.

We all know then we can easily startup with a pfile, like
startup pfile='Pfilename';
But there is no parameter like, startup spfile=.......;
So if my spfile is not in the default location then how I can specify the location of spfile.
The solution is ,

a)Create a blank Pfile.
b)Put the location of the non default spfile inside the blank Pfile with parameter spfile=location.
c)startup the database with the Pfile.

The example below will clear the problem.
1)Create no-default spfile.
SQL> create spfile='/export/home/oracle/myspfile.txt' from pfile;
File created.

2)Create a Pfile that will location SPfile.
SQL> !vi /export/home/oracle/pfile.txt
SPFILE=/export/home/oracle/myspfile.txt

3)Shutdown the database.
4)Start the database with the pfile.
SQL> startup pfile=/export/home/oracle/pfile.txt
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.

5)Ensure that the database is started with no-default spfile.
SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /export/home/oracle/myspfile.txt

How to know My Database Start with Spfile/Pfile?

There is a parameter spfile which indicate the startup parameter of the database. If database is started with pfile then the parameter spfile contains null value. If database is stated with spfile then the parameter value contains the location of the spfile.

Like, if it is started with spfile then the output will like,

SQL> select decode(value,'','Your Database started with pfile','Your Database started with spfile') from v$parameter where name='spfile';

DECODE(VALUE,'','YOURDATABASESTAR
---------------------------------
Your Database started with spfile

SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /oracle/app/oracle/product/spfilearju.ora

If your database is started with pfile, it will show like,
SQL> select decode(value,'','Your Database started with pfile','Your Database started with spfile') from v$parameter where name='spfile';

DECODE(VALUE,'','YOURDATABASESTAR
---------------------------------
Your Database started with pfile

SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string


One think you need to remember that there is a parameter named spfile exist in database but no parameter pfile exist in database.

Related Documents:
----------------------------
How Spfile can be Recovered

ORA-07283/ORA-32004: obsolete and/or deprecated parameter(s) specified

Error Description:
while startup your database it raise an error like,

ORA-19905: log_archive_format must contain %s, %t and %r
Explanation of Error:
Could not startup the database using Spfile as Spfile had wrong format. Some/Al least one parameter in it is obsolete and/or deprecated.

Solution:
1)start the database in nomount state:
Try with startup nomount.

If you fail,then see section a).
If you succeed then follow section b)

a)If you fail to startup nomount stage(You will fail if static parameter is wrongly set.) like,
ORA-19905: log_archive_format must contain %s, %t and %r then,

i)Create a copy of the init.ora file. ( oracle_home/admin/$ORACLE_SID/pfile --> init.ora ) or you don't have a copy then you must have to create a new pfile.

ii)Edit the above init.ora to reflect the log_archive_format = 'arch_%r_%t_%s.arc'

iii)Shutdown immediate and Startup nomount with the above pfile.

SQL> shutdown immediate
SQL> startup nomount pfile = oracle_home/admin/$ORACLE_SID/pfile/init.ora

iv)Create spfile from pfile
SQL> create spfile from pfile ='oracle_home/admin/pfile/init.ora'

v) SQL> shutdown immediate
vi) SQL> startup

b)You succeed but with an error like,
ORA-32004: obsolete and/or deprecated parameter(s) specified

i)See alert log to see which parameter has been obsolete and/or deprecated parameter. Find the parameters. Set the parameter / reset the parameter.

Like,

SQL> alter system reset log_archive_start scope=spfile sid='*';

ii)shutdown immediate


iii)startup
2)Alter database mount.

3)Alter databse open.
If you face the error like

ORA-07283: sksaprd: invalid volume size for archive destination.
then,

SQL> alter system reset log_archive_dest scope=spfile sid='*';

Pfile and Spfile in Oracle.

There are two types of initialization parameter files in oracle:

1)Parameter Files (PFILES)
2)Server Parameter Files (SPFILES).

Comparison Between Pfile and Spfile:

A)Pfile is the text file that can be edited using a text editor. Spfile is binary file that cannot be edited directly by text editor.If you edit spfile it will corrupt.

B)When changes are made to the PFILE, the instance must be shut down and restarted before it takes effect.Most changes to the SPFILE can be made dynamically, while the instance is open and running.

C)Pfile is by default named as init{instance_name}.ora. Spfile is by default named as spfile{instance_name}.ora where instance_name will be replaced by your database name. By default spfile is located under $ORACLE_HOME/dbs in unix and windows and pfile will be located under $ORACLE_HOME\database location on windows.

D)Pfile can be created from an SPFILE using the create pfile from spfile command.Spfile can be created from a PFILE using the create spfile from pfile command.

E)In order to modify any parameter value in pfile you must open the pfile with any text editor and manually edit it. And in order to modify any parameter value inside spfile you must issue ALTER SYSTEM SET with SCOPE=SPFILE or SCOPE=BOTH. Pfile can never be edited with ALTER SYSTEM SET.

F)Spfile can be backed up by RMAN. pfile can't be backed up by RMAN.
In order to have clear idea about ALTER SYSTEM SET have a look at How to set initialization parameter using Alter System

Related Documents:
------------------------
How to set initialization parameter using Alter System