ORA-07446: sdnfy: bad value '/db/app/oracle/Db_1/admin/orcldg/udump' for parameter user_dump_dest.

ORA-07446: sdnfy: bad value '/db/app/oracle/Db_1/admin/orcldg/udump' for parameter user_dump_dest.

Reason: "udump" directory doesn't exist.
Solution: create directory.

ORA-02019: connection description for remote database not found

ORA-02019: connection description for remote database not found 

This error is related to database link.

what is database link?

Database link is a medium to connect from one database to another database, database link mostly use to access other database objects like (tables/synonym/view etc).

How to create database link?

create database link [database-link-name]

connect to [username]

identified by [password]

using [tnsname]

NOTE:

database-link-name --> name of the database link 

username --> username of the database from where we want to connect

password --> password of the username

tnsname --> tnsname of the database from where we want to connect

You can check the tnsnames in the tnsnames.ora file located under $ORACLE_HOME/network/admin directory

example:

database 1 == east

database 2 == west

now you want to access west database for employee tables from east database, in this case

first you need to connect to east database with database link privileged user

to create database link you need 'create database link' privilege.

--connect to db

1. conn scott/tiger@east

--create database link

2. create database link west

connect to fgc_east

identified by fgc_east

using 'west_db';

--access tables from west db

select count(*) from employee@west;

Oracle Database Block corruption

My Signature Article

Tips:
Oracle Certification - Tips

DBA Preparation - Tips

Practical DBA - Tips




Oracle Database Block corruption

“Block corruption is rare but it does happen. As databases get larger and larger – the probability of it happening at some point nears 100%.”--Mr. Tom Kyte Oracle Expert

Block corruption is while the data is being written to the data blocks, if the write to the block fails abruptly, I mean that there is a partial write in the block, may be because of power disruption or I/O problem, leaving no time for header to be updated, or row data to be populated, oracle leaves the block corrupt.In case of block corruption you can normally use the database unless you try to read that particular block, against which it shoots up the block corruption error.Generally block corruption occurs if write fails on the block, when the transaction is being committed


ORA-01578:
ORACLE data block corrupted (file # string, block # string)
Whenever we encounter above error message mean we have BLOCK CORRUPTION.

NOTE: We can find detail information about block corruption in alert.log file

Two types of block corruption can happens

- Physical corruption (media corrupt)
- Logical corruption (soft corrupt)

Physical corruption can be caused by defected memory boards, controllers or broken sectors on a hard disk;

Logical corruption can among other reasons be caused by an attempt to recover through a NOLOGGING action.

Difference between logical and physical corruption

Logical corruption is header - footer - that is one of the checks, yes (it is looking for fractured blocks and when it hits one, it'll re-read it, that is why there is no need for "alter tablespace begin backup" with rman)

Physical corruption is "we cannot read the block from disk, something is physically preventing us from doing so”.

How to detect block corruption?
1. DBVERIFY utility

DBVERIFY is an external command-line utility that performs a physical data structure integrity check. It can be used on offline or online databases, as well on backup files. You use DBVERIFY primarily when you need to ensure that a backup database (or datafile) is valid before it is restored.

http://dbataj.blogspot.com/2007/04/offline-database-verification-utility.html

2. Block checking parameters

There are two initialization parameters for dealing with block corruption:- DB_BOCK_CHECKSUM (calculates a checksum for each block before it is written to disk, every time)causes 1-2% performance overhead- DB_BLOCK_CHECKING (serverprocess checks block for internal consistency after every DML)causes 1-10% performance overhead

Note: In10g db_block_checksum value TYPICAL is implying TRUE and db_block_checking value FULL implying TRUE.

DB_BLOCK_CHECKING Initialization Parameter
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/repair.htm#sthref3176

3. ANALYZE TABLE tablename VALIDATE STRUCTURE CASCADE SQL statement

Validate the structure of an index or index partition, table or table partition, index-organized table, cluster, or object reference (REF).

ANALYZE: Reporting Corruption
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/repair.htm#sthref3173

4. RMAN BACKUP command with THE VALIDATE option

You can use the VALIDATE option of the BACKUP command to verify that database files exist and are in the correct locations, and have no physical or logical corruptions that would prevent RMAN from creating backups of them. When performing a BACKUP... VALIDATE, RMAN reads the files to be backed up in their entirety, as it would during a real backup. It does not, however, actually produce any backup sets or image copies.

Detection of Logical Block Corruption

Besides testing for media corruption, the database can also test data and index blocks for logical corruption, such as corruption of a row piece or index entry. If RMAN finds logical corruption, then it logs the block in the alert.log. If CHECK LOGICAL was used, the block is also logged in the server session trace file. By default, error checking for logical corruption is disabled.
For BACKUP commands the MAXCORRUPT parameter sets the total number of physical and logical corruptions permitted in a file. If the sum of physical and logical corruptions for a file is less than its MAXCORRUPT setting, the RMAN command completes successfully. If MAXCORRUPT is exceeded, the command terminates and RMAN does not read the rest of the file. V$DATABASE_BLOCK_CORRUPTION is populated with corrupt block ranges if the command succeeds. Otherwise, you must set MAXCORRUPT higher and re-run the backup to find out the corrupt block ranges.
RMAN found any block corruption in database then following Data Dictionary view populated.

V$COPY_CORRUPTION
V$BACKUP_CORRUPTION
V$DATABASE_BLOCK_CORRUPTION

Using RMAN to Validate Database Files
http://download.oracle.com/docs/cd/B19306_01/backup.102/b14192/bkup005.htm#i1006673

5. EXPORT/IMPORT command line utility

Full database EXPORT/IMPORT show=y is another method.

. about to export SCOTT's tables via Conventional Path ...
. . exporting table BONUS
EXP-00056: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 4, block # 43)
ORA-01110: data file 4: 'C:\ORA10GHOME\ORADATA\ORCL10G\USERS01.DBF'

6. DBMS_REPAIR package

dbms_repair is a utility that can detect and repair block corruption within Oracle. It is provided by Oracle as part of the standard database installation.

http://www.oracleutilities.com/Packages/dbms_repair.html

How to Repair & Fix block corruption?

We can recover everything but we have valid database backup.
Whenever we found block corruption then first need to find out which type of block corruption occurred because block corruption recovery depends on block corruption type.

Like Corrupted block related to TABLE segment, INDEX segment, TABLE
PARTITION segment, INDEX PARTITION segment, ROLLBACK segment, LOB segment.

Through below query we can find out corrupted block type

select segment_type,owner'.'segment_name
from dba_extents
where file_id = [&file_id] and [&block] between block_id and block_id+blocks -1;

Below is example with RMAN BLOCK MEDIA RECOVERY.

SQL> conn scott/tiger
Connected.
SQL> select * from test;
select * from test
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 11)
ORA-01110: data file 5: 'C:\INDEXDATA01.DBF'

First check which type of block corruption happened through above mentioned query.

RMAN> blockrecover datafile 5 block 11;
Starting recover at 29-APR-08using channel
ORA_DISK_1 starting media recoverymedia recovery complete,
elapsed time: 00:00:00
Finished recover at 29-APR-08

If you are not using rman then applying below procedure
- if it is index then drop and recreate index
- if it is table and you have backup of that table then restore backup on another database and exp/imp the table.

Same Reference:
http://sysdba.wordpress.com/2006/04/05/how-to-check-for-and-repair-block-corruption-with-rman-in-oracle-9i-and-oracle-10g/

NOTE: Find more information “Handling Oracle Block Corruptions in Oracle7/8/8i/9i/10g” Metalink Note: 28814.1

How to corrupt database block for practice purpose?
On Unix:
Use dd command
$man dd

On Windows:
Use Editor and open datafile write some junk character at middle of file and save it.

ORA-01996 & ORA-01994

ORA-01996 & ORA-01994

This error occurred when password file “entries” value is reached to maximum size.

First understand what “ENTRIES” parameter is.
Entries parameter is use for specify numeric value multiple of four.
We can assign any value but it is always use multiple of four.
Like 4,8,12 so on

Password files use for SYSDBA password. And ENTRIES clause use for HOW many database users can connect with SYSDBA privilege or how many times we can grant SYSDBA privilege to any database user.

SQL> grant sysdba to md;
grant sysdba to md
*
ERROR at line 1:
ORA-01996: GRANT failed: password file
'D:\oracle\product\10.1.0\Db_1\DATABASE\PWDorcl.ORA' is full

We will get above error message when REMOTE_LOGIN_PASSWORDFILE=exclusive is set.

SQL> grant sysdba to md;
grant sysdba to md
*
ERROR at line 1:
ORA-01994: GRANT failed: cannot add users to public password file

We will get above error message when REMOTE_LOGIN_PASSWORDFILE=shared is set.

Solution:
1. Recreate PASSWORD FILE with MAXIMUM ENTRIES=N VALUE.
2. Restart Oracle Service After recreate PWD file.

Connecting with sys user with Or without pwd

As we know "SYS" & "SYSDBA" is superuser privilege in oracle database.

There is two method to connect SYSDBA user.

1. WITH PASSWORD

2. WITHOUT PASSWORD

For example:

SQL> CONN / AS SYSDBA ---without password



For without password need follow thing to be done.

1. Oracle Database User must have SYSDBA privileges.

2. Operating System User must add in DBA group.

On windows: ORA_DBA group

On linux: DBA group

3. In sqlnet.ora file must content "NTS" in below line

SQLNET.AUTHENTICATION_SERVICES=(NTS)

For example:
SQL> CONN SYS/PWD AS SYSDBA --- with password or prevent to connect without password.

1. Remove OS user from DBA group

2. Edit sqlnet.ora file and change "NONE" to "NTS" in below line

SQLNET.AUTHENTICATION_SERVICES=(NONE)

Default Value is NTS when sqlnet.ora file created.

How to Drop Datafile from tablespace

Tips:
Oracle Certification - Tips

DBA Preparation - Tips

Practical DBA - Tips

How to Drop Datafile from tablespace.

Datafile is physical part of database and it is take storage device /file system to store/save oracle data.

Whenever datafile grow more space needed, sametime unwanted datafile created, sametime datafile created in wrong tablespace then we need to drop datafile.

Below is overview to drop a datafile from tablespace
NOTE: Use below procedure after test on TESTING database.

Oracle 8i
There is no direct sql command to drop datafile from tablespace.
In that case we need to drop tablespace after move all data to new tablespace.
1. create new tablespace
2. move all table to new tablespace
3. move all index to new tablespace
4. move all other objects to new tablespace
5. drop old tablespace with including contents;
6. through OS command remove all datafiles belongs to droped tablespace.

Oracle 9ir1
Again There is no direct sql command to drop datafile from tablespace.
In that case we need to drop tablespace after move all data to new tablespace.
1. create new tablespace
2. move all table to new tablespace
3. move all index to new tablespace
4. move all other objects to new tablespace
5. drop old tablespace with including contents and datafiles;

NOTE: Oracle9ir1 is introduce “and datafiles” clause with drop tablespace statement which remove datafiles from file system.

Oracle 9ir2
Again There is no direct sql command to drop datafile from tablespace.
In that case we need to drop tablespace after move all data to new tablespace.
1. create new tablespace
2. move all table to new tablespace
3. move all index to new tablespace
4. move all other objects to new tablespace
5. drop old tablespace with including contents and datafiles;

But we can drop TEMPFILE through below statement.
SQL> alter database tempfile ‘/u02/oracle/temp01.dbf’ drop including datafiles;
[not tested]

Oracle 10gr1
Same like Oracle 9ir2

Oracle 10gr2
Now in 10gr2 oracle introduce to drop datafile or tempfile from tablespace through single sql command with few restrictions.

From Oracle Documentation:

You use the DROP DATAFILE and DROP TEMPFILE clauses of the ALTER TABLESPACE command to drop a single datafile or tempfile. The datafile must be empty. (A datafile is considered to be empty when no extents remain allocated from it.) When you drop a datafile or tempfile, references to the datafile or tempfile are removed from the data dictionary and control files, and the physical file is deleted from the file system or Automatic Storage Management (ASM) disk group.

Datafiles:
Alter tablespace data DROP DATAFILE ‘/u02/data01.dbf’;

Tempfiles:
Alter tablespace temp DROP TEMPFILE ‘/u02/temp01.dbf’;

Restrictions for drop datafiles
The following are restrictions for dropping datafiles and tempfiles:
• The database must be open.
• If a datafile is not empty, it cannot be dropped.
If you must remove a datafile that is not empty and that cannot be made empty by dropping schema objects, you must drop the tablespace that contains the datafile.
• You cannot drop the first or only datafile in a tablespace.
This means that DROP DATAFILE cannot be used with a bigfile tablespace.
• You cannot drop datafiles in a read-only tablespace.
• You cannot drop datafiles in the SYSTEM tablespace.
• If a datafile in a locally managed tablespace is offline, it cannot be dropped
Oracle 11gr1
Same like Oracle 10gr2

Reference:
http://www.akadia.com/services/ora_remove_datafile.html
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/dfiles.htm#sthref1396

Autotrace in SQLPLUS

What is AUTOTRACE

In SQL*Plus you can automatically get a report on the execution path used by the SQL optimizer and the statement execution statistics. The report is generated after a successful SQL DML statement, such as SELECT, DELETE, UPDATE or INSERT. It is useful for monitoring and tuning the performance of these DML statements.

How to configure AUTOTRACE in SQLPLUS for database user

1. Change directory path and connect with SYSTEM user to database
C:\>cd c:\oracle\product\10.1.0\db_1\rdbms\admin

C:\Oracle\product\10.1.0\Db_1\RDBMS\ADMIN>sqlplus /nolog

SQL*Plus: Release 9.0.1.0.1 - Production on Tue Oct 16 17:08:20 2007

(c) Copyright 2001 Oracle Corporation. All rights reserved.

SQL> conn system/manager
Connected.

2. run UTLXPLAN.SQL script for plan table.
SQL> @utlxplan.sql

3. Create public synonym for plan table or grant all privilege.
SQL> create public synonym plan_table for plan_table;

SQL> grant all on plan_table to public;

Grant succeeded.

4. Exit and again change directory path and connect with SYS user to database
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

C:\Oracle\product\10.1.0\Db_1\RDBMS\ADMIN>cd\

C:\>cd c:\oracle\product\10.1.0\db_1\sqlplus\admin

C:\Oracle\product\10.1.0\Db_1\sqlplus\admin>sqlplus /nolog

SQL*Plus: Release 9.0.1.0.1 - Production on Tue Oct 16 17:12:07 2007

(c) Copyright 2001 Oracle Corporation. All rights reserved.

SQL> conn sys as sysdba
Enter password:
Connected.

5. run plustrce script and script must be run with SYS user.
SQL> @plustrce
SQL>
SQL> drop role plustrace;

Role dropped.

SQL> create role plustrace;

Role created.

SQL>
SQL> grant select on v_$sesstat to plustrace;

Grant succeeded.

SQL> grant select on v_$statname to plustrace;

Grant succeeded.

SQL> grant select on v_$mystat to plustrace;

Grant succeeded.

SQL> grant plustrace to dba with admin option;

Grant succeeded.

SQL> set echo off

6. Grant plustrace role to public.
SQL> grant plustrace to public;

Grant succeeded.

Configuring the Autotrace Report

SET AUTOTRACE OFF - No AUTOTRACE report is generated. This is the
default.
SET AUTOTRACE ON EXPLAIN - The AUTOTRACE report shows only the optimizer
execution path.
SET AUTOTRACE ON STATISTICS - The AUTOTRACE report shows only the SQL
statement execution statistics.
SET AUTOTRACE ON - The AUTOTRACE report includes both the
optimizer execution path and the SQL
statement execution statistics.
SET AUTOTRACE TRACEONLY - Like SET AUTOTRACE ON, but suppresses the
printing of the user's query output, if any.

Database Statistics for SQL Statements

recursive calls
Number of recursive calls generated at both the user and system level. Oracle maintains tables used for internal processing. When Oracle needs to make a change to these tables, it internally generates an internal SQL statement, which in turn generates a recursive call.

db block gets
Number of times a CURRENT block was requested.

consistent gets
Number of times a consistent read was requested for a block.

physical reads
Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache.

redo size
Total amount of redo generated in bytes.

bytes sent via SQL*Net to client
Total number of bytes sent to the client from the foreground processes.

bytes received via SQL*Net from client
Total number of bytes received from the client over Oracle Net.

SQL*Net roundtrips to/from client
Total number of Oracle Net messages sent to and received from the client.

sorts (memory)
Number of sort operations that were performed completely in memory and did not require any disk writes.

sorts (disk)
Number of sort operations that required at least one disk write.

rows processed
Number of rows processed during the operation.

EXAMPLE

SQL> set autotrace on
SQL> select * from dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DEPT'




Statistics
----------------------------------------------------------
0 recursive calls
2 db block gets
2 consistent gets
0 physical reads
0 redo size
702 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed

SQL> set autotrace traceonly
SQL> select * from dept;


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DEPT'




Statistics
----------------------------------------------------------
0 recursive calls
2 db block gets
2 consistent gets
0 physical reads
0 redo size
702 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed

SQL> set autotrace trace explain
SQL> select * from dept;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'DEPT'



SQL> set autotrace trace statistics
SQL> select * from dept;


Statistics
----------------------------------------------------------
0 recursive calls
2 db block gets
2 consistent gets
0 physical reads
0 redo size
702 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed

CURSOR_SHARING parameter

CURSOR_SHARING

What is cursor_sharing parameters ?

CURSOR_SHARING determines what kind of SQL statements can share the same cursors.

What is possible values for this parameter ?

1. EXACT (default)
Only allows statements with identical text to share the same cursor.

2. FORCE
Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.

3. SIMILAR
Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.

When we have to / should use this parameter ?

Whenever you have lots of some sql statement to execute but differ in liternal and your application is not design to use BIND VARIABLE that time you can use CURSOR_SHARING=FORCE to share cursor for every sql statement which differ only in literal.

Consider setting CURSOR_SHARING to SIMILAR or FORCE if both of the following questions are true:

Are there statements in the shared pool that differ only in the values of literals?
Is the response time low due to a very high number of library cache misses?


Tom Kyte said "cursor_sharing=force" is not permanent solution for performance it is use for temporary basis during developer fix there bug in application... instead of this parameter use BIND VARIABLE.



Performance improvement when we set cursor_sharing=force ?

When your application use lots of similar sql statement but differ in literal then yes performance will improve when you set cursor_sharing=force.

Side Effects on database when set cursor_sharing=FORCE/SIMILAR

Forcing cursor sharing among similar (but not identical) statements can have unexpected results in some DSS applications, or applications that use stored outlines.

Oracle does not recommend setting CURSOR_SHARING to FORCE in a DSS environment or if you are using complex queries. Also, star transformation is not supported with CURSOR_SHARING set to either SIMILAR or FORCE. For more information, see the "OPTIMIZER_FEATURES_ENABLE Parameter".

BUG with cursor_sharing=FORCE/SIMILAR

In Oracle Version 8i there is bug when set cursor_sharing=force/similar.

We need to down our database to set this parameter

No, we can set this parameter when our database is open.

alter system set CURSOR_SHARING=force SCOPE=both;

"SMON: Parallel transaction recovery tried"

Wed Sep 26 11:34:23 2007
SMON: Parallel transaction recovery tried

We found above message in alert_sid.log file.

No need to worry about it. it is information message ...SMON start recovery in parrallel but failed and done in serial mode.

"kccrsz: expanded controlfile section"

kccrsz: expanded controlfile section 11 from 224 to 252 records
requested to grow by 2 record(s); added 1 block(s) of records
Controlfile has resized from 190 to 192 blocks.

If we found above message in alert_sid.log file then no need to warry about it.
it is just information message for controlfile is increase in size.

we can check "v$controlfile_record_section" view or "controlfile_record_keep_time" parameters

Eg:
kccrsz: expanded controlfile section 11 from 224 to 252 records
Note: Section 11 is belong to log_history.

When controlfile possibly extended

1. Any upgrade or migration on database
2. Any Phyiscal changes in database

Sending-email-from-an-Oracle-10g-Server

Database 10gr1

When we configure Oracle E-MAIL notification method through OEM then it is not include notification for DB shutdown or startup events. For these two events we have to configure manually.


Connect sys as sysdba user and run two scripts for install and configure utl_mail package

SQL> conn sys@orcl as sysdba
Enter password: ******
Connected.
SQL> @c:\oracle\product\10.1.0\db_1\rdbms\admin\utlmail.sql

Package created.


Synonym created.

SQL> @c:\oracle\product\10.1.0\db_1\rdbms\admin\prvtmail.plb;

Package body created.

No errors.

Set SMTP_OUT_SERVER parameter for smtp_exchange_server. This parameter is not modifiable means we have to bounce our database to set this parameter

SQL> alter system set smtp_out_server = 'smtp_exchange_server_name' scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup

Grant EXECUTE privs to user which use utl_mail package.

SQL> grant execute on utl_mail to scott;

Grant succeeded.

Create two trigger for startup and shutdown event on database which send us email notification

SQL> create or replace trigger scott.db_shutdown
2 before shutdown on database
3 begin
4 sys.utl_mail.send (
5 sender =>'star_taj@yahoo.com',
6 recipients =>'star_taj@yahoo.com',
7 subject => 'Oracle Database Server DOWN',
8 message => 'May be DB Server Down for maintenance’||
‘ but also contact to DBA for further details. ’
9 );
10 end;
11 /

Trigger created.

SQL> create or replace trigger scott.db_startup
2 after startup on database
3 begin
4 sys.utl_mail.send (
5 sender =>'star_taj@yahoo.com',
6 recipients =>'star_taj@yahoo.com',
7 subject => 'Oracle Database Server UP',
8 message => 'DB Server OPEN for normal use.'
9 );
10 end;
11 /

Trigger created.

Control file Backup Option

Controlfile Backup Option

when database running in NO ARCHIVE LOG MODE


1. Shutdown database with immediate,transactional,normal option.
2. through OS copy command copy controlfiles to backup location
3. startup database.


when database running in ARCHIVELOG MODE

Text based controlfile backup

SQL> alter session set tracefile_identifier = ctrlbkp;

Session altered.

SQL> alter database backup controlfile to trace;

Database altered.

Note
1. Tracefile created with tracefile_identifier name like "ORA_SID_XXX_ctrlbkp.trc".
2. Above created backup controlfile is text file.

Exact copy or binary controlfile backup

alter database backup controlfile to 'PATH';

Note
Above statement created binary format controlfile backup.

dbms_job is not working in Oracle 9i

JOB_QUEUE_PROCESSES specifies the maximum number of processes that can be created for the execution of jobs. It specifies the number of job queue processes per instance (J000, ... J999).

Oracle 9i

Default value for job_queue_processes is "0". so whenever we create any job through dbms_job we have to also modify job_queue_processes parameter > '0'.
job_queue_processes


Oracle 10g

In Oracle 10g Default value for job_queue_processes is "10". so whenever we create any job through dbms_job then we no need to modify job_queue_processes parameter. but in documentation show "0" is default value.
job_queue_processes

How to Enable ARCHIVELOG mode

My Signature Article

Tips:
Oracle Certification - Tips

DBA Preparation - Tips

Practical DBA - Tips

Benefits of ARCHIVELOG mode.

1. We can able to take database backup ONLINE.
2. No Need to Down Oracle DB Server
3. We can better Manage our Backup Policy through RMAN
4. Limited DBA task
5. We can able to RECOVER our database point-in-time

How can enable ARCHIVELOG MODE in 9iR1.

When we create new database then database is created NOARCHIVELOG mode by default.

shutdown database


SQL>shutdown immediate;


Edit below parameters in pfile(init.ora) file.


1. LOG_ARCHIVE_START=TRUE


Create New SPFILE from Modified pfile(init.ora)


SQL> create SPFILE from PFILE;


Startup Database in Mount Mode


SQL> startup mount;


Change Database log mode from NOARCHIVELOG to ARCHIVELOG


SQL> alter database archivelog;


Open DB for normal use


SQL> alter database open;


check archive log status


Database log mode Archive Mode
Automatic archival Enabled
Archive destination C:\Oracle\product\RDBMS
Oldest online log sequence 0
Next log sequence to archive 1
Current log sequence 1



Default Archivelog destination is :$ORACLE_HOME/rdbms.

How can enable ARCHIVELOG MODE in 10gr1

In 10g Archivelog mode enabling is easy becuase we need to just turn DB log mode and archive log is automatic start.


shutdown database


SQL> shutdown immediate;


startup database in mount mode


SQL> startup mount;


Change DB log mode


SQL> alter database archivelog;


Open DB for normal use


SQL> alter database open;


check archivelog status


SQL> archive log list


Default Archive log destination.


1. 10g introduce new feature called FRA (flashback recovery area) so all archivelog files created in FRA.

2. If your not using FRA then all files created in $ORACLE_HOME/database


We can check database archivelog mode through below query


SQL> select log_mode from v$database;
or
SQL> archive log list

Dropping a Database

Dropping a database involves removing its datafiles, redo log files, control files, and initialization parameter files. The DROP DATABASE statement deletes all control files and all other database files listed in the control file. To use the DROP DATABASE statement successfully, all of the following conditions must apply:

The database must be mounted and closed.


SQL> select status from v$instance;

STATUS
------------
MOUNTED


The database must be mounted as RESTRICTED.


SQL> select logins from v$instance;

LOGINS
----------
RESTRICTED

The DROP DATABASE statement has no effect on archived log files, nor does it have any effect on copies or backups of the database. It is best to use RMAN to delete such files. If the database is on raw disks, the actual raw disk special files are not deleted.

SQL> drop database;

Database dropped.


Note: Manually delete below folder.
1. $ORACLE_BASE/oradata/[sid]
2. $ORACLE_BASE/admin/[sid]
3. $ORACLE_BASE/flash_recovery_area/[sid]
4. If database created through DBCA then you have to also delete ORACLE_SID through DBCA otherwise you will get error message when you create new database with same ORACLE_SID.

remote_login_passwordfile

What is "remote_login_passwordfile" parameter and why we use" ?

We have two options for Super User "SYS" password authentication

1. Operating System authentication
2. Password file authentication

Password file authentication

When we use password file authentication then we have to create PASSWORD FILE for SYS user.

How to create password file ?

commandpromt>orapwd file=$ORACLE_HOME/database/orapwd.ora password=pwd entries=n force=y/n

Note:
entries=n "maximum number of distinct DBAs and OPERs (opt)"
force=y ""y" if password file already created"

When use "REMOTE_LOGIN_PASSWORDFILE" file parameter

When we using Password file authentication for "sys" user then we have to use "remote_login_password" parameter.

There are three value for this parameters

1. EXCLUSIVE

This is default value. if parameter set "exclusive" then we can use password file for authentication and connect to remote machine with sys user.Only an EXCLUSIVE file can contain the names of users other than SYS. Using an EXCLUSIVE password file lets you grant SYSDBA and SYSOPER system privileges to individual users and have them connect as themselves.


2. NONE

If this parameter set "NONE" means passwordfile doesn't exists


3. SHARED

A SHARED password file can be used by multiple databases running on the same server. However, the only user recognized by a SHARED password file is SYS. You cannot add users to a SHARED password file. All users needing SYSDBA or SYSOPER system privileges must connect using the same name, SYS, and password. This option is useful if you have a single DBA administering multiple databases.

Note: In documentation "remote_login_passwordfile" default value is 'NONE' but when we create database through DBCA then it will take 'EXCLUSIVE' as default value.

Initialization Parameter File

Database: 10.1.0.5.0
OS: Windows


Parameter file is use for instance startup time.
Three types of parameters file

1. spfile (Server Parameter File)
Name: spfile.ora
Located: $ORACLE_HOME/dbs/spfile.ora (*nix platform)
Located: $ORACLE_HOME/database/spfile.ora(win platform)


Note: Never ever edit directly SPFILE file otherwise it will corrupted.


2. pfile (ASCII text file)
Name: init.ora
Located: $ORACLE_HOME/dbs/spfile.ora (*nix platform)
Located: $ORACLE_HOME/database/spfile.ora(win platform)


Note: you can edit this file in default TEXT EDITOR


3. pfile (text file)
Name: init.ora
Located: $ORACLE_BASE\admin\pfile\init.ora


Note: you can directly edit this file in any editor.

Parameter File Precedence

During instance time Oracle Server check SPFILE if found then using this file instance started. If SPFILE is not found then check PFILE if found then using this file instance stared. if both files is not found then you will get below error
message.

ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'C:\ORACLE\PRODUCT\10.1.0\DB_1\DATABASE
\INITORCL.ORA'

How to know which file is database currently using ?

SQL> show parameter spfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string C:\ORACLE\PRODUCT\10.1.0\DB_1\
DATABASE\SPFILEORCL.ORA

Note: if parameter showing value mean spfile is currently used.


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


Note: if parameter showing NULL in value column then PFILE(init.ora) is used.

How can edit parameter in spfile

1. alter system set [parameter_name] SCOPE=SPFILE
2. shutdown
3. startup

or

1. shutdown
2. edit parameter in pfile [init.ora]
3. create spfile from pfile
4. startup

How to compile invalid objects

My Signature Article

Tips:
Oracle Certification - Tips

DBA Preparation - Tips

Practical DBA - Tips



There are five ways to recompile invalid objects in schema.

1. DBMS_DDL
2. DBMS_UTILITY
3. UTL_RECOMP
4. UTLRP.SQL
5. Manually Recompile

DBMS_DDL.ALTER_COMPILE

Definition

This procedure is equivalent to the following SQL statement:
ALTER PROCEDUREFUNCTIONPACKAGE [.] COMPILE [BODY]

Syntax

Exec dbms_ddl.alter_compile ( type , schema, name);
Type : Must be either PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY or TRIGGER.
Schema : Database Username

Name : Objects name

Example

SQL> exec dbms_ddl.alter_compile ('PROCEDURE','SCOTT','TEST');

PL/SQL procedure successfully completed.

DBMS_UTILITY.COMPILE_SCHEMA

Definition

This procedure compiles all procedures, functions, packages, and triggers in the specified schema.

Syntax

Exec dbms_utility.compile_schema ( schema,compile all)

Schema : Database Username

Compile All : Object type ( procedure, function, packages,trigger)

Example

SQL> exec dbms_utility.compile_schema('SCOTT');

PL/SQL procedure successfully completed.

UTL_RECOMP

Definition

This script is particularly useful after a major-version upgrade that typically invalidates all PL/SQL and Java objects.

Syntax

Exec UTL_RECOMP.RECOMP_SERIAL ();

Example

SQL> Exec UTL_RECOMP.RECOMP_SERIAL ();

PL/SQL procedure successfully completed.


Note: Required SYS user to run this package.

UTLRP.SQL scripts

Definition

Recompiles all existing PL/SQL modules that were previously in an INVALID state, such as packages, procedures, and types.

Syntax
Located: $ORACLE_HOME/rdbms/admin

Example
SQL> @c:\oracle\product\10.1.0\db_1\rdbms\admin\UTLRP.SQL

TIMESTAMP
-----------------------------------------------------------------------

COMP_TIMESTAMP UTLRP_BGN 2007-08-04 12:47:21


PL/SQL procedure successfully completed.


TIMESTAMP
-----------------------------------------------------------------------

COMP_TIMESTAMP UTLRP_END 2007-08-04 12:47:26


PL/SQL procedure successfully completed.


Note: Required SYS user to run this script.
Recommended: After upgrade or migrate database.

Best Approach is manually recompiling all Invalid Objects

Spool recompile.sql

Select ‘alter ‘object_type’ ’object_name’ compile;’
From user_objects
Where status <> ‘VALID’
And object_type IN (‘VIEW’,’SYNONYM’,
‘PROCEDURE’,’FUNCTION’,
‘PACKAGE’,’TRIGGER’);

Spool off
@recompile.sql


Note: VIEW,SYNONYM,PROCEDURE,PACKAGE,FUNCTION,TRIGGER


Spool pkg_body.sql

Select ‘alter package ’object_name’ compile body;’
From user_objects
where status <> ‘VALID’
And object_type = ‘PACKAGE BODY’;

Spool off
@pkg_body.sql


Spool undefined.sql

select ‘alter materizlized view ’object_name’ compile;’
From user_objects
where status <> ‘VALID’
And object_type =‘UNDEFINED’;

Spool off
@undefined.sql


Spool javaclass.sql

Select ‘alter java class ’object_name’ resolve;’
from user_objects
where status <> ‘VALID’
And object_type =‘JAVA CLASS’;

Spool off
@javaclass.sql


Spool typebody.sql

Select ‘alter type ‘object_name’ compile body;’
From user_objects
where status <> ‘VALID’
And object_type =‘TYPE BODY’;

Spool off
@typebody.sql


Spool public_synonym.sql

Select ‘alter public synonym ‘object_name’ compile;’
From user_objects
Where status <> ‘VALID’
And owner = ‘PUBLIC’
And object_type = ‘SYNONYM’;

Spool off
@public_synonym.sql

Objects need to recompile are:
VIEW, SYNONYM, PUBLIC SYNONYM, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, UNDEFINED (MATERIALIZED VIEW), JAVA CLASS, TYPE, TYPE BODY


Please send me feedback at star_taj@yahoo.com

SHUTDOWN: waiting for active calls to complete

Below message found in "alert.log" file when shutdown immediate hung for long time

Thu Jul 19 21:08:00 2007
Active call for process 4448 user 'SYSTEM' program 'ORACLE.EXE (J001)'
SHUTDOWN: waiting for active calls to complete.

Oracle Database : 10.1.0.2.0
OS : Windows XP sp2


when SHUTDOWN IMMEDIATE HUNG with above message means PMON process is unable to clear any uncommited transaction and oracle hung for long time.


Suggestion :

1. connect to SYS user with new terminal window



SQL> conn / sysdba
Connected to idle instance.


2. shutdown database with ABORT option

SQL> shutdown abort;
ORACLE instance shut down.


3. open database for normal shutdown with RESTRICT option

SQL> startup restrict
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 788496 bytes
Variable Size 149682160 bytes
Database Buffers 16777216 bytes
Redo Buffers 524288 bytes
Database mounted.
Database opened.


4. now shutdown database with NORMAL option

SQL> shutdown normal;
Database closed.
Database dismounted.
ORACLE instance shut down.

Enabling iSQL*Plus DBA Access

My Signature Article

Fellow below steps to enable iSQL*Plus DBA Access

Create users

List users

Grant the webDba role

Remove users

Revoke the webDba role

Change user passwords

Open Command Prompt

start ----> run ----> cmd [[hit enter]]

Change Directory

C:\>set ORACLE_HOME=c:\oracle\product\10.1.0\db_1

C:\>cd %ORACLE_HOME%\oc4j\j2ee\isqlplus\application-deployments\isqlplus

C:\Oracle\product\10.1.0\Db_1\oc4j\j2ee\isqlplus\application-deployments\isqlplus>

Note: change ORACLE_HOME variable according your enviourment


open JAZN shell to configure OC4J setup

C:\Oracle\product\10.1.0\Db_1\oc4j\j2ee\isqlplus\application-deployments\isqlplus>

c:\oracle\product\10.1.0\db_1\jdk\bin\java -Djava.security.properties=

c:\oracle\product\10.1.0\db_1\sqlplus\admin\iplus\provider -jar

c:\oracle\product\10.1.0\db_1\oc4j\j2ee\home\jazn.jar

-user "iSQL*Plus DBA/admin" -password welcome -shell

Note:
Above command description.

On command prompt
[[start]]
c:\> cd %ORACLE_HOME%\oc4j\j2ee\isqlplus\application-deployments\isqlplus [[continue]]
c:\oracle\product\10.1.0\db_1\jdk\bin\java
[[continue]]
-Djava.security.properties=c:\oracl
e\product\10.1.0\db_1\sqlplus\admin\iplus\provider -jar
[[continue]]
c:\oracle\product\10.1.0
\db_1\oc4j\j2ee\home\jazn.jar
[[continue]]
-user "iSQL*Plus DBA/admin" -password welcome -shell
[[end]]

Default username : "iSQL*Plus DBA/admin" admin
Default password : welcome (small case)



Create New User for "iSQL*Plus" DBA Access

JAZN:> adduser "iSQL*Plus DBA" taj oracle

Check Existing User and Verify new user created.

JAZN:> listusers "iSQL*Plus DBA"
admin
taj

Grant "webDba" role to Above Created user.

JAZN:> grantrole webDba "iSQL*Plus DBA" taj

JAZN:> exit

Restart isqlplus services

C:\Oracle\product\10.1.0\Db_1\oc4j\j2ee\isqlplus\application-deployments\isqlplu
s>isqlplusctl stop
iSQL*Plus 10.1.0.5.0
Copyright (c) 2005 Oracle. All rights reserved.
Stopping iSQL*Plus ...
iSQL*Plus stopped.

C:\Oracle\product\10.1.0\Db_1\BIN>isqlplusctl start
iSQL*Plus 10.1.0.5.0
Copyright (c) 2005 Oracle. All rights reserved.
Starting iSQL*Plus ...
iSQL*Plus started.