SPFILE and INIT.ORA

Hi Peers,


This is the article which will explain you about init parameter reposistory feature.
However this is basic level but helping us to understand stronger.


SPFILE and INIT.ORA

Oracle9i allows more and more parameters to be changed on-the-fly without having torestart the database. Consequently, the DBA has to remember, more and more often, to change the parameter file accordingly before the next restart of the database. Oracle9i enables dynamically altered parameters to be simultaneously written to the parameter file using server parameter files (SPFILE) so that changes are consistent with ALTER SYSTEM.


$ Up to version 8i, Oracle traditionally stored initialization parameters in a text file INIT.ORA (PFILE).
$ With Oracle9i, server parameter files (SPFILE) can also be used.
$ An SPFILE can be regarded as a repository for initialization parameters which is located on the database server.
$ SPFILEs are small binary files that cannot be edited.
$ Editing SPFILEs corrupts the file and either the instance fails to start or an active instance may crash.

eg ( Content ):


*.db_block_size=8192
*.db_domain='ttc.trivadis.com'
*.db_file_multiblock_read_count=16
*.db_files=1022
*.db_name='TVD901A'
...

*. means:


all instances of this database system. This is also valid syntax in conventional INIT.ORA files.



*. This is particularly interesting for Real Application Cluster (RAC) – see below for more information.


At database startup, if no PFILE is specified at the OS-dependent default location

($ORACLE_HOME/dbs under UNIX, $ORACLE_HOME\database under NT),


the startup command searches for:

1. spfile${ORACLE_SID}.ora

2. spfile.ora

3. init${ORACLE_SID}.ora



Of course, the option of explicitly specifying a PFILE is still available.
 
SQL> connect sys/manager as sysdba


Connected to an idle instance.

SQL> startup pfile=/tmp/initTVD901A.ora

ORACLE instance started.

Total System Global Area 172967504 bytes

...

SQL>

However, a SPFILE cannot be specified for STARTUP with PFILE :


SQL> startup pfile='tmp/spfileTVD901A.ora'

LRM-00101: unknown parameter name 's044'

ORA-01078: failure in processing system parameters

To facilitate this, the best option is to create a conventional INIT.ORA with default name:



init${ORACLE_SID}.ora


This file only contains the path of the SPFILE:

SPFILE = c:\oracle\admin\db1\pfile\spfileDB1.ora

Create SPFILE

A SPFILE is initially created from a conventional text initialization parameters file (PFILE,
INIT.ORA). This can be carried out without a started instance. SYSDBA or SYSOPER
privileges are required to create the SPFILE.


If SPFILE is available at the default location, STARTUP uses this SPFILE if no PFILE is specified.

Creating an SPFILE from a PFILE at the default location:

SQL> CREATE SPFILE FROM PFILE='/u00/app/oracle/admin/TVD901A/pfile/initTVD901A.ora';

File created.

SQL>

Creating an SPFILE from a PFILE at the non-default location:


SQL> CREATE SPFILE='/u00/app/oracle/admin/TVD901A/pfile/spfileTVD901A.ora'

FROM PFILE='/u00/app/oracle/admin/TVD901A/pfile/initTVD901A.ora';

File created.

SQL>
 
The best solution is to create a (conventional) INIT.ORA with default name in the default location,
which contains only the path to the SPFILE.
Working with links, if necessary, on UNIX:

sqlplus "/ as sysdba"

startup create spfile='/u00/app/oracle/admin/TDBT901A/pfile/spfileTVD901A.ora' from
pfile='/u00/app/oracle/admin/SID/pfile/initTVD901A.ora';

shutdown immediate

exit

cd $ORACLE_HOME/dbs

ln -s /u00/app/oracle/admin/TDBT901A/pfile/spfileTVD901A.ora .

sqlplus "/ as sysdba"

startup

Backing up an SPFILE




The CREATE PFILE command can be used to back up an SPFILE:



SQL> CREATE PFILE='/u00/app/oracle/admin/TVD901A/pfile/bck_init.ora' FROM SPFILE;



File created.



SQL>


This creates a directly usable INIT.ORA file:


*.background_dump_dest='/u00/app/oracle/admin/TVD901A/bdump'
*.compatible='9.0.1'
*.control_file_record_keep_time=90
*.core_dump_dest='/u00/app/oracle/admin/TVD901A/cdump'


Continue.........

AD Utilities

What are AD Utilities?

AD Utilities are a group of tools designed to install, upgrade, maintain, and patch a specific set of products contained in a given release of Oracle Applications.

What is AutoPatch (or adpatch)?

AutoPatch (adpatch) is a utility that is used to apply individual patches, mini-packs, or maintenance packs to an existing Oracle Applications instance. For more information on the adpatch utility, see Note 109666.1

What is AD Administration (or adadmin)?

AD Administration (adadmin) performs maintenance tasks on an installed Oracle Applications system to ensure that it runs smoothly. The tasks performed with this utility fall into two categories: database and file system. For more information on the AD Administration utility, see Note 109667.1

What is AD Controller (or adctrl)?

AD Controller (or adctrl) is used in conjunction with other AD Utilities (such as AutoInstall/AutoUpgrade, adadmin or adpatch) to determine the status of workers and restart failed tasks.

What is AD Relink (or adrelink)?

AD Relink (or adrelink) allows you to relink Oracle Applications executable programs with the Oracle Server product libraries. You can run the adrelink utility manually to relink individual executable programs, or use the relink option in the AD Administration utility to relink all executable programs.

For additional AD Utility FAQs, check out the following articles:
Note 62418.1 Patching/Patchset FAQ
Note 60766.1 Patch Installation FAQ

Table ReOrg Analyzer

Table ReOrg Analyzer


Finding and repairing chained rows is an important part of Oracle administration. When an Oracle rows expands,
it sometimes chains onto multiple data blocks. Excessive row chaining can cause a dramatic increase in disk I/O
because several I/O’s are required to fetch the block instead of one single I/O.





Of course, row chaining can be prevented by setting the PCTFREE storage parameter high enough to keep space on each data block
for row expansion. In cases where the data columns contain RAW and LONG RAW columns, row chaining may be unavoidable because the average row
length may exceed the data block size. That is why the query below filters out tables with RAW data types.
Here is the code that we use to generate a report showing all tables with excessive chained rows. Note that the use of this script is
predicated on the use of Oracle’s ANALYZE command to populate the chain_cnt and num_rows columns of the DBA_TABLES data dictionary view.
Once identified, the tables should be reorganized using Create Table As Select (CTAS) or by using the Oracle export-import utilities.



spool chain.lst;
set pages 9999;
column c1 heading "Owner" format a9;
column c2 heading "Table" format a12;
column c3 heading "PCTFREE" format 99;
column c4 heading "PCTUSED" format 99;
column c5 heading "avg row" format 99,999;
column c6 heading "Rows" format 999,999,999;
column c7 heading "Chains" format 999,999,999;
column c8 heading "Pct" format .99;
set heading off;
select 'Tables with chained rows and no RAW columns.' from dual;
set heading on;
select
owner c1,
table_name c2,
pct_free c3,
pct_used c4,
avg_row_len c5,
num_rows c6,
chain_cnt c7,
chain_cnt/num_rows c8
from
dba_tables
where
owner not in ('SYS','SYSTEM')
and
table_name not in
(select table_name from dba_tab_columns
where
data_type in ('RAW','LONG RAW')
)
and
chain_cnt > 0
order by
chain_cnt desc ;
This will produce a nice report that shows tables that you will want to reorganize:
Owner Table PCTFREE PCTUSED avg row Rows Chains Pct
------ --------- ------- ------- ------- --------- --------- ----
SAPR3 ZG_TAB 10 40 80 5,003 1,487 .30
SAPR3 ZMM 10 40 422 18,309 509 .03
SAPR3 Z_Z_TBLS 10 40 43 458 53 .12
SAPR3 USR03 10 40 101 327 46 .14
SAPR3 Z_BURL 10 40 116 1,802 25 .01
SAPR3 ZGO_CITY 10 40 56 1,133 10 .01
6 rows selected.



Cheers,


SpicyOracle.