DBA

Export and import user level data

2009-10-24T17:25:00.003+05:30

Export :

exp user/user@orcl file=d:\backup\test.dmp log=d:\backup\test.log direct=y consistent=y "statistics="none"

Import :

imp system/manager@test fromuser=user touser=user file=d:\backup\test.dmp log=d:\backup\test_imp.log ignore=y

Disk Driver Full

2009-08-16T14:21:00.002+05:30

Disk Driver Full

My disk driver fulled so that i did follow steps..

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

SQL> create tablespace ts datafile 'c:\ts.dbf' size 1m;

Tablespace created.

SQL> select * from v$tablespace;

TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
0 SYSTEM YES NO YES
1 UNDOTBS1 YES NO YES
2 SYSAUX YES NO YES
4 USERS YES NO YES
3 TEMP NO NO YES
6 TS YES NO YES

6 rows selected.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------

C:\ORACLE\PRODUCT\10.2.0\ORADATA\DADA\SYSTEM01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DADA\UNDOTBS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DADA\SYSAUX01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DADA\USERS01.DBF
C:\TS.DBF

SQL> alter tablespace ts offline;

Tablespace altered.

SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrator>move c:\TS.DBF c:\TS1.dbf

C:\Documents and Settings\Administrator>exit

SQL> alter tablespace ts rename datafile 'c:\ts.dbf' to 'c:\ts1.dbf';

Tablespace altered.

SQL> alter tablespace ts online;

Tablespace altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------

C:\ORACLE\PRODUCT\10.2.0\ORADATA\DADA\SYSTEM01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DADA\UNDOTBS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DADA\SYSAUX01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\DADA\USERS01.DBF
C:\TS1.DBF

SQL>

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

2009-08-16T13:40:00.003+05:30

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

Whenever i change mode from noarchive to archive log mode, i got above error.i am looking why this errors occured finally i realized and resolved that problem

The problem occur due to this below reason

sql> archive log start

the above commands not need to run for oracle 10g version.bcz parameter LOG_ARCHIVE_START is obsolete in 10g.

Tuning Oracle's Buffer Cache

2009-07-05T13:06:00.000+05:30

Tuning Oracle's Buffer Cache
Roger Schrag, Database Specialists, Inc.
http://www.dbspecialists.com

Introduction

Oracle maintains its own buffer cache inside the system global area (SGA) for each instance. A properly sized buffer cache can usually yield a cache hit ratio over 90%, meaning that nine requests out of ten are satisfied without going to disk.

If a buffer cache is too small, the cache hit ratio will be small and more physical disk I/O will result. If a buffer cache is too big, then parts of the buffer cache will be under-utilized and memory resources will be wasted.

Checking The Cache Hit Ratio

Oracle maintains statistics of buffer cache hits and misses. The following query will show you the overall buffer cache hit ratio for the entire instance since it was started:

SELECT (P1.value + P2.value - P3.value) / (P1.value + P2.value)
FROM v$sysstat P1, v$sysstat P2, v$sysstat P3
WHERE P1.name = 'db block gets'
AND P2.name = 'consistent gets'
AND P3.name = 'physical reads'

You can also see the buffer cache hit ratio for one specific session since that session started:

SELECT (P1.value + P2.value - P3.value) / (P1.value + P2.value)
FROM v$sesstat P1, v$statname N1, v$sesstat P2, v$statname N2,
v$sesstat P3, v$statname N3
WHERE N1.name = 'db block gets'
AND P1.statistic# = N1.statistic#
AND P1.sid =
AND N2.name = 'consistent gets'
AND P2.statistic# = N2.statistic#
AND P2.sid = P1.sid
AND N3.name = 'physical reads'
AND P3.statistic# = N3.statistic#
AND P3.sid = P1.sid

You can also measure the buffer cache hit ratio between time X and time Y by collecting statistics at times X and Y and computing the deltas.

Adjusting The Size Of The Buffer Cache

The db_block_buffers parameter in the parameter file determines the size of the buffer cache for the instance. The size of the buffer cache (in bytes) is equal to the value of the db_block_buffers parameter multiplied by the data block size.

You can change the size of the buffer cache by editing the db_block_buffers parameter in the parameter file and restarting the instance.

Determining If The Buffer Cache Should Be Enlarged

If you set the db_block_lru_extended_statistics parameter to a positive number in the parameter file for an instance and restart the instance, Oracle will populate a dynamic performance view called v$recent_bucket. This view will contain the same number of rows as the setting of the db_block_lru_extended_statistics parameter. Each row will indicate how many additional buffer cache hits there might have been if the buffer cache were that much bigger.

For example, if you set db_block_lru_extended_statistics to 1000 and restart the instance, you can see how the buffer cache hit ratio would have improved if the buffer cache were one buffer bigger, two buffers bigger, and so on up to 1000 buffers bigger than its current size. Following is a query you can use, along with a sample result:

SELECT 250 * TRUNC (rownum / 250) + 1 ' to '
250 * (TRUNC (rownum / 250) + 1) "Interval",
SUM (count) "Buffer Cache Hits"
FROM v$recent_bucket
GROUP BY TRUNC (rownum / 250)

Interval Buffer Cache Hits
--------------- --------------------
1 to 250 16083
251 to 500 11422
501 to 750 683
751 to 1000 177

This result set shows that enlarging the buffer cache by 250 buffers would have resulted in 16,083 more hits. If there were about 30,000 hits in the buffer cache at the time this query was performed, then it would appear that adding 500 buffers to the buffer cache might be worthwhile. Adding more than 500 buffers might lead to under-utilized buffers and therefore wasted memory.

There is overhead involved in collecting extended LRU statistics. Therefore you should set the db_block_lru_extended_ statistics parameter back to zero as soon as your analysis is complete.

In Oracle7, the v$recent_bucket view was named X$KCBRBH. Only the SYS user can query X$KCBRBH. Also note that in X$KCBRBH the columns are called indx and count, instead of rownum and count.

Determining If The Buffer Cache Is Bigger Than Necessary

If you set the db_block_lru_statistics parameter to true in the parameter file for an instance and restart the instance, Oracle will populate a dynamic performance view called v$current_bucket. This view will contain one row for each buffer in the buffer cache, and each row will indicate how many of the overall cache hits have been attributable to that particular buffer.

By querying v$current_bucket with a GROUP BY clause, you can get an idea of how well the buffer cache would perform if it were smaller. Following is a query you can use, along with a sample result:

SELECT 1000 * TRUNC (rownum / 1000) + 1 ' to '
1000 * (TRUNC (rownum / 1000) + 1) "Interval",
SUM (count) "Buffer Cache Hits"
FROM v$current_bucket
WHERE rownum > 0
GROUP BY TRUNC (rownum / 1000)

Interval Buffer Cache Hits
------------ -----------------
1 to 1000 668415
1001 to 2000 281760
2001 to 3000 166940
3001 to 4000 14770
4001 to 5000 7030
5001 to 6000 959

This result set shows that the first 3000 buffers are responsible for over 98% of the hits in the buffer cache. This suggests that the buffer cache would be almost as effective if it were half the size; memory is being wasted on an oversized buffer cache.

There is overhead involved in collecting LRU statistics. Therefore you should set the db_block_lru_statistics parameter back to false as soon as your analysis is complete.

In Oracle7, the v$current_bucket view was named X$KCBCBH. Only the SYS user can query X$KCBCBH. Also note that in X$KCBCBH the columns are called indx and count, instead of rownum and count.

Full Table Scans

When Oracle performs a full table scan of a large table, the blocks are read into the buffer cache but placed at the least recently used end of the LRU list. This causes the blocks to be aged out quickly, and prevents one large full table scan from wiping out the entire buffer cache.

Full table scans of large tables usually result in physical disk reads and a lower buffer cache hit ratio. You can get an idea of full table scan activity at the data file level by querying v$filestat and joining to SYS.dba_data_files. Following is a query you can use and sample results:

SELECT A.file_name, B.phyrds, B.phyblkrd
FROM SYS.dba_data_files A, v$filestat B
WHERE B.file# = A.file_id
ORDER BY A.file_id

FILE_NAME PHYRDS PHYBLKRD
-------------------------------- ---------- ----------
/u01/oradata/PROD/system01.dbf 92832 130721
/u02/oradata/PROD/temp01.dbf 1136 7825
/u01/oradata/PROD/tools01.dbf 7994 8002
/u01/oradata/PROD/users01.dbf 214 214
/u03/oradata/PROD/rbs01.dbf 20518 20518
/u04/oradata/PROD/data01.dbf 593336 9441037
/u05/oradata/PROD/data02.dbf 4638037 4703454
/u06/oradata/PROD/index01.dbf 1007638 1007638
/u07/oradata/PROD/index02.dbf 1408270 1408270

PHYRDS shows the number of reads from the data file since the instance was started. PHYBLKRD shows the actual number of data blocks read. Usually blocks are requested one at a time. However, Oracle requests blocks in batches when performing full table scans. (The db_file_multiblock_read_count parameter controls this batch size.)

In the sample result set above, there appears to be quite a bit of full table scan activity in the data01.dbf data file, since 593,336 read requests have resulted in 9,441,037 actual blocks read.

Spotting I/O Intensive SQL Statements

The v$sqlarea dynamic performance view contains one row for each SQL statement currently in the shared SQL area of the SGA for the instance. v$sqlarea shows the first 1000 bytes of each SQL statement, along with various statistics. Following is a query you can use:

SELECT executions, buffer_gets, disk_reads,
first_load_time, sql_text
FROM v$sqlarea
ORDER BY disk_reads

EXECUTIONS indicates the number of times the SQL statement has been executed since it entered the shared SQL area. BUFFER_GETS indicates the collective number of logical reads issued by all executions of the statement. DISK_READS shows the collective number of physical reads issued by all executions of the statement. (A logical read is a read that resulted in a cache hit or a physical disk read. A physical read is a read that resulted in a physical disk read.)

You can review the results of this query to find SQL statements that perform lots of reads, both logical and physical. Consider how many times a SQL statement has been executed when evaluating the number of reads.

Conclusion

This brief document gives you the basic information you need in order to optimize the buffer cache size for your Oracle database. Also, you can zero in on SQL statements that cause a lot of I/O, and data files that experience a lot of full table scans.

Transaction Internals

2009-07-05T12:59:00.003+05:30

Here i hope this ppt document useful for know about oracle transaction

http://www.juliandyke.com/Presentations/TransactionInternals.ppt

Super fast Database Copying/Cloning

2009-07-01T17:54:00.002+05:30

Super fast Database Copying/Cloning

A database cloning procedure is especially useful for the DBA who wants to give his developers a full-sized TEST and DEV instance by cloning the PROD instance into the development server areas.

This Oracle clone procedure can be use to quickly migrate a system from one UNIX server to another. It clones the Oracle database and this Oracle cloning procedures is often the fastest way to copy a Oracle database.

STEP 1: On the old system, go into SQL*Plus, sign on as SYSDBA and issue: “alter database backup controlfile to trace”. This will put the create database syntax in the trace file directory. The trace keyword tells oracle to generate a script containing a create controlfile command and store it in the trace directory identified in the user_dump_dest parameter of the init.ora file. It will look something like this:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS
NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 240
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 ('/u03/oradata/oldlsq/log1a.dbf',
'/u03/oradata/olslsq/log1b.dbf') SIZE 30M,
GROUP 2 ('/u04/oradata/oldlsq/log2a.dbf',
'/u04/oradata/oldlsq/log2b.dbf') SIZE 30M
DATAFILE
'/u01/oradata/oldlsq/system01.dbf',
'/u01/oradata/oldlsq/mydatabase.dbf'
;
# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;

STEP 2: Shutdown the old database

STEP 3: Copy all data files into the new directories on the new server. You may change the file names if you want, but you must edit the controlfile to reflect the new data files names on the new server.

rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
rcp /u03/oradata/oldlsq/* newhost:/u03/oradata/newlsq
rcp /u04/oradata/oldlsq/* newhost:/u04/oradata/newlsq

STEP 4: Copy and Edit the Control file – Using the output syntax from STEP 1, modify the controlfile creation script by changing the following:

Old:

CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS

New:

CREATE CONTROLFILE SET DATABASE "NEWLSQ" RESETLOGS

STEP 5: Remove the “recover database” and “alter database open” syntax

# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;

STEP 6: Re-names of the data files names that have changed.

Save as db_create_controlfile.sql.

Old:

DATAFILE
'/u01/oradata/oldlsq/system01.dbf',
'/u01/oradata/oldlsq/mydatabase.dbf'

New:

DATAFILE
'/u01/oradata/newlsq/system01.dbf',
'/u01/oradata/newlsq/mydatabase.dbf'

STEP 7: Create the bdump, udump and cdump directories

cd $DBA/admin
mkdir newlsq
cd newlsq
mkdir bdump
mkdir udump
mkdir cdump
mkdir pfile

STEP 8: Copy-over the old init.ora file

rcp $DBA/admin/olslsq/pfile/*.ora newhost:/u01/oracle/admin/newlsq/pfile

STEP 9: Start the new database

@db_create_controlfile.sql

STEP 10: Place the new database in archivelog mode

Super fast Database Copying/Cloning tips by burleson consulting

Full Database Export

2009-07-01T16:51:00.001+05:30

When and why to perform a full database export.

* Export/import is only the possible way to construct a similar database on remote servers regardless of Operating System compatibility i.e. cross platform (Export on Linux and Import on Windows Systems).

* To migrate the database from one Operating System to another i.e. (Linux/Unix to Windows), and to upgrade the database from one version to another i.e. 8i-9i or 9i-10g, but not vice versa.

* If you are on Oracle 10g, above two points can be possible, using RMAN Convert Commands i.e. to construct or move a database from one OS to another OS i.e. Cross platform.

* When there is a requirement to clone a database on another test or development server, then take a full database mode export dump and create a database with the similar settings of target database, and import the dump in source database.

Simple Steps: how to perform a full database export using export utility.

* Use either system user or any other database user who has the EXP_FULL_DATABASE privilege.
* Set the NLS_LANG environment variable according the database character set and language details.
SQL> select * from nls_database_parameters
2 where parameter in ('NLS_LANGUAGE','NLS_TERRITORY','NLS_CHARACTERSET');

PARAMETER VALUE
------------------------------ ----------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CHARACTERSET WE8ISO8859P1

Windows (Dos Prompt):
C:\> set NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

Unix/Linux:
$ export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

* Start the export with following command and options.

exp system/password@mydb file=c:\exportdmp\exp_fulldb_MYDB_27Aug08.dmp
full=y log= c:\exportdmp\exp_fulldb_MYDB_27Aug08.log

Note: This is just a simple export command to perform the full database export. I would request and suggest you to refer Oracle Documentations on export/import and their options. Check the references.

Help on Export and Import:

Windows:
C:\> exp help=y
C:\> imp help=y

Change Oracle password

2009-06-30T13:49:00.008+05:30

Location of Oracle Password hashes

* Database -sys.user$ -password
*Oracle Password File
*Data File of the system Tablespace
*(full) Export Files
*archive logs

Show oracle password hashkey

sql>select username,password from dba_users;

sql>select name,password from sys.user$ where password is notnull.

How to change an Oracle Password?

You should always use the pasword because the password is sent unencrypted over the net If you use the alter user syntax.

* alter user myuser identified bu my!supersecretpassword;

*grant connect to myuser identified by my!supersecretpassword

*update sys.user$ set password='F894844C34402B67' WHERE name='SCOTT';restart the database necessary

*sql*plus command:password or password username.

How to change an Oracle password temporarily?

In Oracle it is possible to change a password temporarily. This can be useful for DBA which act as a different user.

SQL> select username,password from dba_users where username='SCOTT';

USERNAME PASSWORD-------- ----------------SCOTT F894844C34402B67

SQL> alter user scott identified by mypassword;

Now login with the following credentials: scott/tigerAfter doing your work you can change the password back by using an undocumented feature called "by values"

SQL> alter user scott identified by values 'F894844C34402B67';

http://www.red-database-security.com/whitepaper/oracle_passwords.html

Oracle Database created as 32bit or 64bit

2009-06-29T19:33:00.003+05:30

We can check the Database Created as 32bit Or 64bit Platform through below sql query

SQL> select metadata from sys.kopm$ ;METADATA
-----------------------------------------------------------0000006001240F050B0C030C0C0504050D0609070805050505050F05050505050A050505050504050607080823472323081123081141B023008300B207D00300000000000000000000000000000000000000000000000000000000000000000000000000

If above Colored Character Is "B023" means 32bit

Or
If it is "B047" means 64bit.

HWM -High Water Mark

2009-06-29T19:19:00.002+05:30

Oracle, stores data in segments. Segments has free and used space. The mark(a logical mark) between free and used space can be called as high water mark(HWM). When new data filled, the mark moves. On deleting records, oracle does not move back HWM. That is, you have unused data but, HWM sees as if it were filled data. When you shrink table such as alter table move command, this HWM refreshes itself.

When you delete from a table, a DML operation, oracle undos the changing. That is you can move back, with some additional works. But when you truncate a table, a DDL operation, oracle moves back the HWM. So this is faster than normal delete clause. With delete, you will fire existing triggers whereas truncate does not.

Truncate is especially is used instead of dropping and recreating tables. with truncate, no objects will become invalid and grants are remains the same

What Is Bottleneck ?

Bottleneck is the slowest operations of the set of operations. It is important to identify in the bottleneck in the system to know what needs to be turned to be better Performance and scalabilty.

Some Examples :-

The I/O system may become a bottleneck on large database system.

If a database used more memory than what’s available,paging and swapping will become a bottleneck.

In a RAC system the interconnect may be overloaded (cannot handle all data transfers

Connect sysdba

2009-06-29T19:09:00.003+05:30

There is two method to connect sysdba user :-

with password

without password

sql> conn / as sysdba ------ without password

* Oracle database user must have sysdba privileges.
* Os user must add in dba group.
* In sqlnet.ora file must content "NTS" in below line

sqlnet.authentication_services = (NTS)
****************************************************
with password

sql>conn sys/pwd as sysdba ------- with password

prevent to connect without password.
* remove os user from dba group.
* 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.
***************************** DBA ******************

IMPORT DUMPFILE WITHOUT KNOW EXPORT USER NAME

2009-06-29T18:45:00.003+05:30

cmd>imp file= log= full=y show=y indexfile=<> userid=system/password

for example

cmd>imp file=d:\test.dmp log=d:\tb\test.log full=y show=y indexfile=d:\tb\test.sql system/manager

after execute this commands,you should go to check indexfile and logfile.

Remove Linux Partition

2008-12-05T21:11:00.004+05:30

Hi..You would like to uninstall linux ... Do the follow steps

Before removing a partition, boot rescue mode
Start parted ,where /dev/hda is the device on which to remove the partition
In command mode
>list hard-drives
>parted /dev/hda or /dev/sda
>print
>rm 2
exit and then you can install another OS on your system like windows xp or any.

Common Oracle Database Recoveries

2008-11-22T17:09:00.002+05:30

1. Loss of datafile(s) System Tablespace :-

Action...

sql>shutdown immediate

Oracle Instance shutdown.

sql>host cp backupfile file

(Restore your data backup files from backup to related loss system’s datafiles)

SQL> CONNECT / AS SYSDBA

SQL> STARTUP MOUNT

SQL> RECOVER DATABASE;

SQL> ALTER DATABASE OPEN;

----------------------------------------------------------------------------------------------------------------------------------------

2.Loss of Non-system(s) Tablespace :-

Action...

Don't need to shutdown database.

sql>alter tablespace ......... offline

SQL> host cp backupfile file
(Restore your data backup files from backup to related loss system’s datafiles)

SQL> RECOVER TABLESPACE your_tablespace_name;

SQL> ALTER TABLESPACE your_tablespace_name ONLINE;

Or recover by using datafile(s)

SQL> ALTER DATAFILE ‘/u01/…’ OFFLINE;

SQL> host cp backupfile file
(Restore your data backup files from backup to related loss system’s datafiles)
SQL> RECOVER DATAFILE ‘/u01…’;

SQL> ALTER DATAFIL ‘/u01/…’ ONLINE;

Or shutdown database

SQL> SHUTDOWN IMMDIATE;

SQL> (Restore your data backup files from backup to related loss system’s datafiles)

SQL> STARTUP MOUNT;

SQL> RECOVER DATABASE;

SQL> ALTER DATABASE OPEN

----------------------------------------------------------------------------------------------------------------------------------------
3.Loss of data file(s) of rollback OR undo segments tablespace :-

A datafile in a rollback segment tablespace has been lost or damaged to a point that
Oracle cannot recognize it anymore.If you try to startup the database will result in ORA-1157, ORA-1110, and possibly an operating system level error such as ORA-7360. Trying to shut down the database
in normal or immediate mode will result in ORA-1116, ORA-1110, and possiblyan operating system level error such as ORA-7368.

PLEASE NOTE that before using _corrupted_rollback_segments parameter, you should
explore all other options. This will require extra caution. Call Oracle Customer Support if you have any questions or need any assistance

The Database is down:
----------------------------------------------------------------------------------------------------------------------------------------
Assuming that your database was cleanly shutdown (check your alert.log)

SQL> SHUTDOWN IMMEDIATE/NORMAL

SQL> STARTUP RESTRICT MOUNT

SQL> ALTER DATABASE DATAFILE '' OFFLINE DROP;

SQL> ALTER DATABASE OPEN

SQL> DROP TABLESPACE INCLUDING CONTENTS;

SQL> -- Recreate the rollback tablespace with all its rollback segments.
Remember to bring the rollbacks online after you create them.
----------------------------------------------------------------------------------------------------------------------------------------

Assuming that you cann't open your database and you get the following error

messages: ORA-604, ORA-376, and ORA-1110.
-- Comment out the ROLLBACK_SEGMENTS parameter and add the following line:
_corrupted_rollback_segments = ( ,...., )
with exact the same rollbacks.
WARNING: Use this parameter ONLY IN THIS SPECIFIC SCENARIO or as
instructed by Oracle Customer Support.

SQL> STARTUP RESTRICT

SQL> DROP TABLESPACE INCLUDING CONTENTS;

SQL> Recreate the rollback tablespace with all its rollback segments.

SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

-- put back your ROLLBACK_SEGMENTS parameter in the init.ora file.
-- remove _CORRUPTED_ROLLBACK_SEGMENTS PARAMETER

SQL> -- Rebuild the database.
----------------------------------------------------------------------------------------------------------------------------------------

Assuming that database was not cleanly shutdown:
-- Restore the lost file from a backup.

SQL> STARTUP MOUNT

SQL> SELECT FILE#, NAME, STATUS FROM V$DATAFILE;

SQL> ALTER DATABASE DATAFILE '' ONLINE;
(if datafile is offline)

SQL> SELECT V1.GROUP#, MEMBER, SEQUENCE#, FIRST_CHANGE#
FROM V$LOG V1, V$LOGFILE V2
WHERE V1.GROUP# = V2.GROUP# ;

SQL> SELECT FILE#, CHANGE# FROM V$RECOVER_FILE;

If NOARCHIVELOG Mode and the CHANGE# is GREATER than the minimum FIRST_CHANGE#
of your logs, the datafile can be recovered.

SQL> RECOVER DATAFILE '';

SQL> ALTER DATABASE OPEN;

----------------------------------------------------------------------------------------------------------------------------------------
Assuming that the database is up and it is in ARCHIVELOG mode:

SQL> ALTER DATABASE DATAFILE '' OFFLINE;

SQL> -- Restore it from your backup.

SQL> RECOVER DATAFILE '';

SQL> ALTER DATABASE DATAFILE '' ONLINE;

-OR-

SQL> ALTER SYSTEM KILL SESSION ' , ' -- The transaction in RBS involved.

SQL> ALTER ROLLBACK SEGMENT OFFLINE;
\
SQL> DROP ROLLBACK SEGMENT ;

SQL> DROP TABLESPACE INCLUDING CONTENTS;

SQL> CREATE TABLESPACE rbs ...;

SQL> CREATE ROLLBACK SEGMENT rbs01 ...;

SQL> ALTER ROLLBACK SEGMENT ... ONLINE;
----------------------------------------------------------------------------------------------------------------------------------------

In this case to access to data you will get a message like this:

ORA-00376: file 20 connot be read at this time
File 2 happens to be one of the data files that belongs to the rollback segment tablespace.

Solution:

SQL> select segment_name, status from dba_rollback_segs;
(If status are in “NEED RECOVERY”)

SQL> SHOW PARAMETER undo

SQL> CREATE UNDO TABLESPACE myundotbs DATAFILE ‘/u01/…’ SIZE 50M;

SQL> ALTER SYSTEM SET undo_tablespace=’myundotbs’;

SQL> DROP TABLESPACE undotbs;
(Remove all related OS datafiles)
----------------------------------------------------------------------------------------------------------------------------------------
Loss of Rollback segments while there were transaction activities;

For example:

SQL> CREATE TABLE mytest (col1 varchar2(20));

SQL> BEGIN
FOR this IN 1..100 LOOP
INSERT INTO mytest VALUSE (this);
END LOOP;
END;
/

SQL> /* Notice that we didn’t commit any of above transaction */

SQL> /* shutdown abort or any crash on rollback datafiles will cause

The following error messages if we start the database. */
ORA-01157: cannot identify/lock data file 20 – see DBWR trace file
ORA-01110: data file 20: ‘/u01/…’

Solution:

If you shutdown the server then you will get a message like this:
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: 'C:\MYUNDOTBS_01.DBF'

SQL> STARTUP MOUNT

SQL> ALTER DATABASE DATAFILE 'C:\MYUNDOTBS_01.DBF' OFFLINE;

SQL> ALTER DATABASE OPEN;

SQL> /* remember you can not access to that table with transaction */

SQL> SELECT segment_name, status FROM dba_rollback_segs;
(You will see that the segments are NEEDS RECOVERY)

SQL> /* replace your backup file and recover the tablespace */

SQL> RECOVER TABLESPACE your_tablespace_name;

SQL> ALTER TABLESPACE your_tablespace_name ONLINE;

SQL> ALTER ROLLBACK SEGMENT … ONLINE;
(Repeat this for all of your rollback segments)

If you are not shutdown:

SQL> ALTER DATABASE DATAFILE 'C:\MYUNDOTBS_01.DBF' OFFLINE;

SQL> /* remember you can not access to that table with transaction */

SQL> SELECT tablespace_name, segment_name, status FROM dba_rollback_segs;
(You will see that the segments are NEEDS RECOVERY)

SQL> /* replace your backup file and recover the tablespace */

SQL> RECOVER TABLESPACE your_tablespace_name;

SQL> ALTER TABLESPACE your_tablespace_name ONLINE;

SQL> ALTER ROLLBACK SEGMENT … ONLINE;
(Repeat this for all of your rollback segments)

----------------------------------------------------------------------------------------------------------------------------------------
. Loss of an UN-archived Online log file.

Solution:

SQL> /* Your database is done. */

SQL> /* Replace all your datafiles and online redo logfile, but not controlfiles. */

SQL> host cp … …

SQL> STARTUP MOUNT

SQL> RECOVER DATABASE UNTIL CANCEL; -- UNTIL TIME …

SQL> ALTER DATABASE OPEN RESETLOGS;
/* Since you did incomplete recovery, you must use ‘RESETLOGS’ option.

SQL> SHUTDOWN IMMEDIATE
/* Do your cold backup immediately */

Loss of your Online Redo-log files.

Solution:

SQL> /* If you loss your online redo-log files, your instance will hung or crash.
If not, try to shutdown abort. Restore all files from your cold backup
with exception of your control files. */

SQL> STARTUP MOUNT

SQL> /* Now do an incomplete recovery recover using CANCEL or TIME option */

SQL> RECOVER DATABASE UNTIL CANCEL;
/* you should apply up to your last archive and then cancel the recovery process */

SQL> ALTER DATABASE OPEN RESETLOGS;
/* Since you did incomplete recovery, you must use ‘RESETLOGS’ option.

SQL> SHUTDOWN IMMEDIATE
/* Do your cold backup immediately */
--------------------------------------------------------------------------------------------------------------------------------------- Loss of your control file.

Solution:

SQL> /* Assuming you have already backup controlfile to trace */

SQL> ALTER DATABASE BACKUP CONTROLFILE TO trace;

SQL> /* modify and run your trace file and your control file is up to date */

SQL> /* if not, then copy a old control file */

SQL> STARTUP MOUNT;

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;

SQL> ALTER DATABASE OPEN;

Database Opened.

Change Database Name

2008-11-22T16:20:00.004+05:30

SQL> SELECT NAME FROM V$DATABASE;

Name
-------
localdb

sql>shutdown immediate

Oracle instance shutdown.

sql>startup mount

database mounted.

NID: it’s a naming utility; you can able to change your database name and database id.

C:\>nid help=y

DBNEWID: Release 10.2.0.1.0 - Production on Mon May 14 10:48:33 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Keyword Description (Default)
----------------------------------------------------
TARGET Username/Password (NONE)

DBNAME New database name (NONE)

LOGFILE Output Log (NONE)

REVERT Revert failed change NO

SETNAME Set a new database name only NO

APPEND Append to output log NO

HELP Displays these messages NO

Here, I have change my database name using NID utility.

C:\>NID TARGET=SYS/sys@localdb DBNAME=local SETNAME=Y

DBNEWID: Release 10.2.0.1.0 - Production on Mon May 14 10:37:36 2007Copyright (c) 1982, 2005, Oracle.

All rights reserved.Connected to database TAJ (DBID=1565772587)Connected to server version 10.2.0Control Files in database:

F:\ORACLE\PRODUCT\10.2.0\ORADATA\localdb\CONTROL01.CTL

F:\ORACLE\PRODUCT\10.2.0\ORADATA\localdb\CONTROL02.CTL

F:\ORACLE\PRODUCT\10.2.0\ORADATA\localdb\CONTROL03.CTL
Change database name of database localdb to local ? (Y/[N]) => Y Proceeding with operationChanging database name from localdb to local
Control File F:\ORACLE\PRODUCT\10.2.0\ORADATA\localdb\CONTROL01.CTL - modifiedControl File

F:\ORACLE\PRODUCT\10.2.0\ORADATA\localdb\CONTROL02.CTL - modifiedControl File

F:\ORACLE\PRODUCT\10.2.0\ORADATA\localdb\CONTROL03.CTL - modifiedDatafile

F:\ORACLE\PRODUCT\10.2.0\ORADATA\localdb\SYSTEM01.DBF - wrote new nameDatafile

F:\ORACLE\PRODUCT\10.2.0\ORADATA\localdb\UNDOTBS01.DBF - wrote new nameDatafile

F:\ORACLE\PRODUCT\10.2.0\ORADATA\localdb\SYSAUX01.DBF - wrote new nameDatafile

F:\ORACLE\PRODUCT\10.2.0\ORADATA\localdb\USERS01.DBF - wrote new nameDatafile

F:\ORACLE\PRODUCT\10.2.0\ORADATA\localdb\EXAMPLE01.DBF - wrote new nameDatafile

F:\ORACLE\PRODUCT\10.2.0\ORADATA\localdb\RMAN01.DBF - wrote new nameDatafile

F:\ORACLE\PRODUCT\10.2.0\ORADATA\localdb\TEMP01.DBF - wrote new nameControl File

F:\ORACLE\PRODUCT\10.2.0\ORADATA\localdb\CONTROL01.CTL - wrote new nameControl File

F:\ORACLE\PRODUCT\10.2.0\ORADATA\localdb\CONTROL02.CTL - wrote new nameControl File

F:\ORACLE\PRODUCT\10.2.0\ORADATA\localdb\CONTROL03.CTL - wrote new nameInstance

shut downDatabase name changed to local.Modify parameter file and generate a new password file before restarting.Succesfully changed database name.DBNEWID - Completed succesfully

c:\>oradim -new -sid local
instance created.

c:\>net start oracle servicelocal
service started.

c:\>set oracle_sid=local

c:\>slplus /nolog

sql>conn sys as sysdba

enter new password: sys (choice)

connected to idle instane.

You should check your parameter file and modify it. and replace local instead of localdb

sql>startup mount

database mounted.

Note :- ora-02778 : Name given for the log directory is invalid. In this case, you should check your parameter file name

sql>alter database open;

database opened.

sql>select name from v$database;

Name

----------
local

2008-11-22T15:11:00.003+05:30

Ora -32004 --- obsolete and/or deprecated parameter(s) specified

Actions....

See alert log for of parameters that are obsolete or deprecated .remove them from spfileor the server side pfile.

sql>select name,isspecified from v$obsolete_parameter where is isspecified ='TRUE';

In my case somebody had been messing around with the parameter log_archive_start;

sql>alter system reset log_archive_start scope=spfile sid='*';
system altered.

sql>shut

sql>startup

2008-11-22T15:02:00.003+05:30

Ora-01092 - Oracle instance terminated

Examine the alert log files for more information.

Actions.......

Shutdown abort
Bad permissions of BDUMP, UDUMP or CDUMP Directories
Missing Datafiles
Renaming files when oracle is down
& many other os related causes

Check the parameter files whether datafiles or some files are located correct or not.

Recover

2008-11-22T14:57:00.002+05:30

Recover database....

recover database until cancel
recover database until time '2004-03-21:22:59:04'
recover database until change 123456

recover datafile 'filename' until cancel
recover datafile 'filename' until time '2004-03-21:22:59:04'
recover datafile 'filename' until change 123456

recover tablespace ts_name until cancel
recover tablespace ts_name until time '2004-03-21:22:59:04'
recover tablespace ts_name until change 123456

recover database using backup controlfile

Database Size

2008-11-22T14:44:00.002+05:30

I want to find the size....

sql>show parameter db_block_size;

Name Type Value
-----------------------------------------------------------
db_block_size integer 8192

How to check the size in Oracle 9i

sql>select segment_name ,bytes/1024 as totsize_bytes, (blocks * 8192)/1024 as totbytes from user_segments where segment_name = upper('&1') and segment_type='TABLE';

Enter value for 1:

If you put any segment name and then you will see that table size.

Online user managed backup

2008-11-22T12:21:00.003+05:30

Step 1 :-

Put the tablespace in backup mode
Copy the datafiles which tablespace in backup mode
Once copy the datafiles is completed putout from the backup mode
switch logfile
archive log mode is enabled

Step 2 :-

sql> select tablespace_name from dba_data_files;

tablespace_name

----------------------------------

system

sysaux

users

undo

temp

example

sql>alter tablespace system begin backup;

sql>host copy E:\oracle\product\10.2.0\oradata\localdb\system01.dbf e:\oracle\backup;

1 files copied

sql>select * from v$backup;

file status change# time

-----------------------------------------------

1 active 1134456 22-nov-08

2 not active 0

" " "

sql> alter tablespace system end backup;

system altered.

After exec, you should check the any datafiles in backup mode. If any datafiles in backup mode ,you should exec end backup condition after taken backup datafiles.

sql>alter system switch logfile;

system altered.

Cold Backup

2008-11-18T15:10:00.003+05:30

This is consistent backup that means all datafiles and controlfiles are consistent to point in time.database in no archive log mode.

Steps....

shutdown the database ,sql>shutdown normal
Copy the controlfiles and datafiles,parameter file and passwordfile os level and then paste it in another location.
startup database ,sql>startup

Example.....

shutdown the database, drop the all controlfiles and datafiles and then startup the database.
Error ora-00205-Error in identifying the control files for the location.
If you have 5 multiplexing controlfiles,2 multiplexing in different location and remain 3 controlfiles were dropped,you should modify the location in parameter file current controlfile and then save it.
startup the database using parameter files.
In mount database,error will appear.
Recover datafile system and then some datafile , in this case you should copy the backup files and paste it in default location.

sql>recover database;

media recovery complete.

sql>alter database open;

Database opened.

2008-11-17T16:16:00.004+05:30

ora-01031- insufficent privilege

Suppose these error occur means..your ora_dba group not configured in OS authentication.

Solve...

Control Panel -----------> Adminstrative tools -------> Computer management
Click Local groups and then users or groups
on right side administrative is appear.right click on this and click properties.

In this case You should find Ora_dba ..its not appear in this. so you go to add the Ora_dba privilege to OS.

run -------> cmd enter
> net localgroup ora_dba /add enter

And then you find the Ora_dba is there and then add ora_dba to your administrator groups.

>set oracle_sid=oracle
sqlplus
sys/sys@localdb as sysdba

database connected.........and then you can easily access it.

Ora-12514 TNS: listener does not currently know of service

2008-09-29T16:11:00.003+05:30

Due to error occur on Oracle Listner not properly mentioned

So.., you should check lisntener.ora file and modify

Example :-

(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)

< (SID_DESC =
(SID_NAME = localdb )
(ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
(SID_NAME = localdb) >

Note : < ... > here check your service name either there or not?localdb -------- db name
And then go to
lsnrctl >stop

lsnrctl>start

Or

>set oracle_sid=localdb
>sqlplus
username : / as sysdba
>startup

& then you connect sys/sys@localdb as sysdba

Your service should be started
------------------------------------------------------------------------------------------
Thanks & Regards
S.Mohamed Azarudeen
azarmohds@gmail.com

Hit/Miss Ratios

2008-09-29T15:49:00.005+05:30

Buffer Hit Ratio :-

Buffer hit ratio notes:

Consistent Gets - The number of blocks accesses made to the block buffer to retrive data in a consistent mode.
DB Blk gets - The number of blocks accessed via single block gets.
Physical Reads - The cumulative number of blocks read from disk.
Logical reads are the sum of consisent gets and db block gets.
the db block gets statstic value is incremented when a block is read for update and when segment header blocks are accessed.
Hit ratio should be > 80% , else increase db_block_buffers in init.ora

sql>select sum(decode(name, 'consistent gets',value,0)) "consistent gets",

sum(decode(name,'db block gets',value,0)) "db block gets",

sum(decode(name,'physical reads',value,0)) " physical reads",

round((sum(decode(name,'consistent gets',value,0)) +

sum(decode(name,'db block gets',value,0)) - sum(decode(name,'physical reads',value,0))) /

(sum(decode(name,'consistent gets',value,0)) +

sum(decode(name,'db block gets',value,0))) * 100,2) "Hit Ratio" from v$sysstat;

Data Dict Hit Ratio :-

Gets - Total number of requests for information on the data object.
Cache Misses -- Number of data requests resulting in cache misses.
Hit ratio should be > 90%,else increse Shared_pool_size in init.ora

Sql> Select sum(gets),sum(getmisses), round((1-(sum(getmisses)/sum(gets))) * 100,2) from v$rowcache;

Library Cache Miss Ratio :-

Executions - The number of times a pin was requested for objects of this namespace.
Cache misses - any pin of an object that is not the first pin performed since the object handle was created and which requires loading the object from disk.
Hit ratio should be <>

Sql> Select sum(pins) executions,sum(reloads) cache_misses,sum(reloads) / sum(pins) miss_ratio from v$librarycache;

Undo_Retention

2008-09-25T15:18:00.004+05:30

Rollback Segments are renamed Undo logs.Traditionally transaction undo information stroed in rollback segments until a commit or rollback statment was issued.

Automatic undo management allows the dba to how long undo information should retained after commit.preventing 'snapshot-too old' error on long running queries.

This is done by undo_retention parameter.the default is 900 seconds(5minutes).you can set this parameter to gurantte that oracle keeps undo logs for extended period of time.

rather then having to define rollback segments,you can simply define an undo tablespace and oracle let take the rest and automatic tuning. Undo_management=AUTO

Calculate Undo_Retention:-

Actual undo size :-

sql> Select sum(a.bytes) "Undo_Size" from v$datafile a, v$tablespace b,dba_tablespaces c where c.contents='UNDO' and c.status='ONLINE' and b.name=c.tablespace_name and a.ts#=b.ts#;

Undo block per second :-

sql>select max(undoblks/((end_time-begin_time)*3600*24)) " Undo_block_per_second" from v$undostat;

Db_block_size:-

sql>select to_number(value) from 'db_block_size' from v$parameter where name='db_block_size';

**** 04/04/2008 Oracle 10g OCA 1z0-042 question

Your database is configured for automatic undo management.Undo_retention is set to three hours.You want to fashback a table that was created last year.how far back can the flash back query go?

a.3 hours

b.6months

c.until last year
d.until last commit

e.until the point when the undo tablespace was refreshed.

f.until the database is shutdown and the memory erased.

Ans : A .3 Hours

Interview

2008-07-16T15:12:00.004+05:30

How Do you move table from sys tablespace to user tablespace :-

sql>alter table tablename move tablespace tablespacename;

Finding out Table and Tablespace Location :-

sql>select owner,table_name,tablespace_name from dba_tables where table_name='tablename';

How many memory layers are in the shared pool?

Shared poll consists of two layers.

Library Cache ---- parsed sql staments and cursor information its a symmentric check.
Data Dictionary Cache --- user account,privilege,segments,datafile and extend information stored in the data dictionary cache.

User and Privilege and Roles

2008-07-12T15:35:00.000+05:30

Users Privilege :-

sql>create user azar idenitified by azar;

or

sql>create user azar idenitified by azar default tablespace temporary tablespace quota unlimited on password expire;

sql>grant create session,create table to azar;

or

sql>Grant connect,resource to azar;

sql>show user;
azar

sql>alter user azar account lock;

sql>alter user azar account unlock;

Password Change

sql>alter user azar identified by ;

sql>drop user azar cascade;

System Privilege

sql>Grant create any table to azar;

Revoke Privilege

sql>revoke create any table from azar;

View :- dba_sys_privs

sql>select * from dba_sys_privs where grantee= 'username';

Object Privilege :

sql>Grant select,update,insert,delete on to azar;

sql>Revoke select,update,insert,delete on from azar;

View :- dba_tab_privs

sql>grant dba to azar;

sql>revoke dba from azar;

sql>select * from dba_role_privs where grantee ='dba';

sql>select role from session_roles;

Managing Tablespace

2008-07-10T15:39:00.000+05:30

Tablespace :-

Logical

database ---- > tablespace --- > segment ---- > extent ---- > Oracle block

* An Oracle Database can be logically grouped into smaller logical areas of space Known as Tablespace

* Tablespace Consists of one or more datafiles.

* It consists of zero or more segments

Creating Tablespace

sql>create tablespace azar datafile '\oracle\oradata\azar01.dbf' size 20m;

Check your tablespace view dba_tablespaces

sql>select * from dba_tablespaces;

sql>alter tablespace azar read only;

Your tablespace in read only mode you can drop your tablespace contents.

sql>select tablespace_name,status from dba_tablespaces;

sql>alter tablespace azar read write;

sql>select tablespace_name,status from dba_tablespaces;

sql>alter tablespace azar offline;

sql>alter tablespace azar online;

Increase your size tablespace in three Ways :-

sql>alter tablespace azar datafile '\oracle\oradata\azar01.dbf' size 20m autoextend on;

sql>alter tablespace azar add datafile '\oracle\oradata\azar02.dbf' size 10m;

sql>alter database datafile '\oracle\oradata\azar01.dbf' resize 30m;

View:-

dba_data_files

dba_free_space

Moving tablespace from one location to another location

Offline your tablespace
Cut your datafile and paste datafile in target place
sql>alter tablespace azar rename datafile '\oracle\oradata\azar01.dbf' to 'any path' ;
Online your tablespace

Create temporary tablespace :-

Using for only sort operation

sql> Create temporary tablespace azardba tempfile '\oracle\oradata\temp02.dbf' size 2m;

If you want change your temporary tablespace to default tablespace

sql>alter database default temporay tablespace azardba;

View:-

dba_temp_files

v$tempfile

v$temp_space_header

Redo Log FIle

2008-07-07T18:24:00.000+05:30

Redo log File :-

* Redo log files record all changes made to data and provide a recovery mechanism from a system or media failure.

* Redo log files are organized into groups

* An Oracle database requires at least two groups

* Each redo log within a group is called a member.

Online Redo log groups :-

* A set of identical copies of online redo log file is called an Online redo log group.

* Log writer writes same information to all online redolog files in a group.

* Oracle Server Needs minimum two online redo log group.

Online Redo log Member :-

* Each online redol og file in a group called Online redo log member.

* Each member in a group has a identical log sequence number and same size.

* The log sequence number is assigned each time the oracle server starts writing to a log group to identify each redo log file uniquely.

* The current log sequence number is stored in the control file and header of all data files.

Redo logs Work :-

* When you redo log file is full, Logwriter will move to the next Log group is called Log switch

* Checkpoint occurs

* Information written to Control files

Logwriter Writes Under the Condition

When a transaction commits
Redolog buffer becomes one-third full
More than a megabytes of changed record in the redo log buffer
Before database writer writer to the datafile.

Forcing Log switch and Checkpoints :-

* Log switches and checkpoints are automatically done at certain operation point of the database.

but a DBA force to log switch or checkpoint occur.

sql> alter system switch logfile;

sql> alter system checkpoint;

Archive mode

sql> archive log list;

database log mode noarchive

automatic archival disable

Change your database from noarchive mode to archive mode

Mount stage

sql> shutdown immediate

sql> startup mount

sql> archive log list;

sql> alter database archivelog;

database altered

sql> archive log list;

database log mode archive mode

automatice archival disabled

sql> archive log start;

If you want set instance level

sql> show parameter log_archive_start

value

false

sql> alter system set log_archive_start=true scope=spfile

system altered

sql> shutdown immediate

sql> startup

sql> archive log list;

Change your archive destination

sql> alter system set log_archive_dest = '\oracle\admin\{sid}' scope=spfile

shutdown and startup

sql> archive log list;

Adding Group and Member :-

sql>alter database add logfile group 3

'\oracle\oradata\{sid}\redo03a.log' size 5m;

sql> alter database drop logfile group 3;

adding member

sql>alter database add logfile member '\oracle\oradata\{sid}\redo03b.dbf to group 3;

sql>alter database drop logfile member '\oracle\oradata\{sid}\redo03b.log' ;

View :-

V$log

v$logfile

Group and member status :-

active - archived ,instance required

Inactive -- archived but instance not required

curretn -- currently log writer writes data in a the group

********* drop your group only inactive state,you cannot drop current state.

If you want to drop current state you sholud log switch logfile.

sql>select archiver from v$instance;

started

Control File

2008-07-07T11:23:00.000+05:30

Control File :-

* Small Binary file necessry for the database to start and operate successfully.

* Each Control file associated with only one database.

* Before database opened, the control file is read to determine if the database is in a valid state to use.

* Control file is updated continously by the oracle server.

* It must be available for writing whenever the database is open.

* No databse administrator or end user can not edit this file.

* If all copies of a control file lost, the database must be recovered before database opened.

* Control file should be multiplexed upto eight times.

Control Files Contents :-

Database name and identifier
Time stamp of database creation
Tablespaces names
Names and locations of datafiles and redolog files.
Current redo log file sequence number.
Checkpoint information
Begin and end of undo segments
Redo log archive information
Backuop information.

sql> show parameter control_files;

Multiplex control files using pfile

(alreay have two control files and then)

sql> Alter system set control_files= '\oracle\oradata\{sid}\control01.ctl',

'\oracle\oradata\{sid}\control02.ctl','\oracle\oradata\{sid}\control03.ctl' scope = spfile ;

system altered.

sql> show parameter control_files;

***** Now not effet right now and so you shutdown the database and startup manually and copy the controlfile os level and paste it and rename it.

(control01.ctl (copy) and paste it same location (copy of control01.ctl ) and then rename it (control03.ctl))

sql>shutdown immediate

copy and paste and rename it

sql> startup

Sql> show parameter control_files;

Backup the control file :-

sql> Alter database backup controlfile to 'filename';

sql>alter database backup controlfile to trace;

View :-

v$controlfile
v$parameter
v$controlfile_record_section
show parameter control_files
v$backup
v$datafile
v$tempfile
v$tablespace
v$archive
v$log
v$logfile
v$loghist
v$archived_log
v$database

Data Dictionary

2008-07-03T16:42:00.000+05:30

Data Dictionary :-

* During Database creation, The oracle server crerates additional object structures within datafiles.

Data Dictionary Tables
Dynamic Performance Tables

Data Dictionary :-

* Set of read only tables and views that record,verify and provide information associated with database.

*Describe databases and its objects

Two types of objects

Base Tables

--- Store description of database

--- Created with create database

Data Dictionary Views

--- Summarize base table information

--- Created using catalog.sql script

Base Tables :-

* Base tables are the first objects created in any oracle database.

* They are automatically created when the Oracle server runs the sql.bsq script at the time database is created.

* Users rarely access them directly. because it stored in a cryptic format.

* Example -- IND$ table which contains information about the index in the database.

Create Database

2008-07-03T15:29:00.001+05:30

How to Create Database :-

When you install oracle ,database automatically created. If you want create manually doing following steps...( Windows )

SID ---> Any name that is your database name

Step 1 :

Copy the default parameter file and prepare it ( \Oracle\ora90\database)

Step 2 :

You should change sid name as like your database name (put in pfile folder in \oracle\admin\{sid}\pfile)
Put it five folder in \oracle\admin\{sid}\(put it and rename like create,pfile,udump,bdump,cdump)
Save the parameter file

Step 3 :

* create password file

type on cmd platform

Run ---> cmd Enter

cmd>orapwd file= \oracle\ora90\database\pwd{sid}.ora pwd=sys (as your choice)

Step 4:

* In windows create services for instance

cmd>oradim -new -sid {sid}

* Set Oracle Sid

cmd>set oracle_sid={sid}

Step 5:

cmd>sqlplus

Enter username : sys/sys as sysdba

Connected to an idle instance

sql>Startup nomount

sql>create database {sid}

Logfile

group 1 ('\oracle\oradata\{sid}\redo01.log') size 100m,

group 2 ('\oracle\oradata\{sid}\redo02.log') size 100m

datafile '\oracle\oradata\{sid}\system01.dbf' size 20m autoextend on

undo tablespace undotbs

datafile '\oracle\oradata\{sid}\undotbs01.dbf' size 20m

default temporary tablespace tempts1

tempfile '\oracle\oradata\{sid}\tempts01.dbf'

size 20m

Database created

sql>@c:\oracle\ora90\rdbms\admin\catalog.sql

sql>@c:\oracle\ora90\rdbms\admin\catproc.sql

****

http://www.adp-gmbh.ch/ora/admin/creatingdbmanually.html

Shutdown Database

2008-07-03T15:00:00.000+05:30

Shutdown Mode :-

Shutdown immediate
Shutdown Abort
Shutdown Transactional
Shutdown Normal

Sql> Shutdown [immediate Normal Abort Transactional ]

Database buffer cache written to the data files.
Uncommitted Changes Roll Back
Resources released

Shutdown Normal :-

No New Connections can be made.
The oracle server waits for all users to disconnect before completing shutdown
Database and redo buffers are written to disk
Bg processes are terminated. and the sga is removed from the meory.
Oracle closes and dismounts the database before shutting down the instance
The next startup does not require an instance recovery.

Shutdown Transactional :-

* Transactional shutdown prevents client from losing work.

No client can start a new transaction on this particular instance.
A client is disconnected when the client ends the transaction
When all transactions have finished ,a shutdown immediately occurs
The next startup does not require an instance recovery.

Shutdown immediate:-

Current sql statments being processed by oracle are not completed.
Oracle server does not wait for users currently connected to the database to disconnect.
Oracle rolls back active transactions.
Oracle closes and dismounts the database before shutting the instance.
The next startup does not require intance recovery.

Shutdown abort :-

sql statments by the oracle server immediately terminated.
oralce does not wait for users currently connected to the database to disconnect.
database and redo buffers are not written to disk.
Uncommited transactions are not rolled back.
the instance is terminated without closing the files.
database not closed or dismounted.
Next startup needs instance recovery. which occurs automatically.

Startup

2008-07-02T17:38:00.000+05:30

Starting up a database (Nomount) :-

* When starting the database, you select the states in which it starts.

Usually start an instance without mounting a database only during database creation or the recreation of control-files.

sql>Startup nomount

*Starting an instance include following tasks,

Reading the initializtion files
Allocating the SGA
Starting the background processes
Opening the alertSID.log file and the trace files.
The database must be named with the db_name parameter.

Starting up a database (Mount) :-

* The database must be mounted but not open during the following tasks,

Renaming datafiles
Enabling and disabling redo log archive options
Performing full database recovery
Locating and opening the control file specified initializtion parameters file.
Reading the control files to obtain the name and status of the redo log files and datafiles.

Starting up a database (Open) :-

* An instance started and database mounted and then datbase opened. During database open ,the user can be attempt to open database and performing.

*During the database open following tasks,

Datafiles and ONline redolog files are Available

Note :- When you open the database, a datafiles and online redo log file not avilable ,the oracle returns error. Mean time database needs recovery options.

*** If necessary,System Monitor background process (SMON) initiates instance recovery.

Startup Command :-

To startup an instance, use the following commands

Startup [force] [restrict] [pfile=filename] [open [recover] [database] Mount Nomount ]

Note :- This is not the Complex syntax.

Force ---> aborting the running instance before performing a startup
Restrict ---> Enables only users with restricted session privilege to access the database
Recover ---> can be media recovery when the database starts

**** Attempting to open Oracle utilities without start the service, Oracle returns a error Ora-12547 --TNS

The Alter Database Command :-

* Chang the states of the database from Nomount ot Mount stage,

sql> alter database db01 mount;

* Open the database read only mode

sql> alter database db01 open read only;

Alter database db01 open [read only read write]

where

Read Write ---> Opens the database read write mode so users can generate redo logs.
Read Only ----> Restrict users to read only transactions,preventing them from generating Redo log information.

Restrict Command :-

sql>startup restrict;

* Use the Alter system command in restriction mode,

sql> alter system enable restricted session;

**** When you perform struture maintanice or database export or import. The database can be restricted session so that it is available only to the restricted session privilige users.

Alter system [enable disable ] restricted session;

Enable ---> who have restircted privilige users
Disable ----> users who do not have the priviliges also log on.

Terminate Session :-

* After placing an instance in resticted mode, you may want to kill all current user sessions before performing administrative tasks.

Alter system kill session 'integer1,integer 2'

Where

Integer 1 ----> Value of the column in the v$session view
Integer 2 ----> value of the serail# in the v$session view

***** http://www.oracle-base.com/articles/misc/KillingOracleSessions.php

Efects of Terminating a Session :-

* Pmon perform following tasks,

Roll back the users current transaction
Release all currently held table or rows
Free all resources currently reserved by the user

Opening database in Read Only Mode :-

Executes query
Execute disk sort using locally managed tablespace
Take datafiles offline and Online ,.Not tablespace
Perform recovery offlines of datafile and tablespace.

Managing an Oracle Instance

2008-07-02T16:36:00.000+05:30

Initialization Parameter Files :-

sql>Connect / as sysdba

sql>startup

* To start an instance, the Oracle server must read on the Initializtion parameter Files.

* Changes to entries in the file take effect based on the type of initialization parameter file used.

Static Parameter file , PFILE ----> commonly refered as initSID.ora
Persistent Parameter file , SPFILE ----> Commonly refered as spfileSID.ora

Parameter File Contents,

A list of instance parameters.
The name of the database instance assoiciated with
Allocations for memory strutures of the System global area
what to do with filled Online redo log files
The names and locations of control files
Information on undo segments.

PFILE :-

The pfile is a text file .it maintained by standard operating system editor.
Paramerter file is read only during instance startup.
If the file is modified,the instance must be shutdown and restarted in order to make the new parameter effective.

SPFILE :-

Binary file
Maintained by oracle server
Alter system command using to changes made

sql> alter system set uno_tablespace='undo2';

sql> alter system set parameter = value [scope =memory spfile both]

Memory ---> Changes the parameter values only in the currently running file.
Spfile ----> changes the parameter values only in the spfile.
Both -----> Currently running the instnace and spfile.

Creating a spfile,

sql>create spfile from pfile;

Background Process

2008-07-02T15:12:00.000+05:30

Background Processes :-

* The relationship between physical (database) and memory sturture maintain and enforced by the Oracle's Background processes.

* Mandatory backgroun processes,

DBWR -- Database Writer
LGWR -- Log writer
PMON -- Process Monitor
SMON -- System Monitor
CKPT -- Checkpoint

Database Writer :-

* Database Writer writes when,

Checkpoint Occur
Dirty buffer thershold reached
No free Buffers
Timeout
RAC ping request
Tablespace offline
Tablespace read only
Table DROP or Truncate
Tablespace BEGIN BACKUP

*db writer writes dirty buffer from the database buffer cache to the datafiles.

What is Dirty Buffer?

* A buffer that is currently not in use, and contains data that has been committed but not written back to the disk by the buffer writer process is called a dirty buffer.

* If a user process finds a dirty buffer in the least recently used (LRU) list, it moves the dirty buffer to the write list and continues searching for more dirty buffers.

Log Writer :-

At commit
When one-third full
When there is 1MB of redo
Every 3 seconds
Befor DBWN writes

System Monitor :-

* Instance recovery

Rolls forward changes in the redo logs.
Opens the database for user access
Roll back uncommitted transactions.

* Coalesces free space ever 3 sec

* Deallocates temporary segments.

Process Monitor :-

* Cleans up after failed processes by

Rolling back the transaction
Releasing locks
Releasing Other Resources
Restarts dead dispatchers

What is Checkpoint?

* A checkpoint occurs when the db writer process writes all modified buffers in the sga buffer cache to the database datafiles.

* Data file headers are also updated with the latest checkpoint SCN, even if the file had no changed blocks.

* Checkpoints occur AFTER (not during) every redo log switch.

* set parameter log_checkpoints_to_alert=true to observe checkpoint start and end times in the database alert log.

* Checkpoints can be forced with the Alter system checkpoint command.

What is SCN?

* System Change Number ---> A number,internal to Oracle that is incremented Over time as change vectors are generated,applied and written to the redo log.

* System commit Number ---> A number,internal to oracle that is incremented with each database commit.

Memory Struture

2008-07-02T10:57:00.000+05:30

Memory Struture:-
* SGA (system global area) --- Allocated When instance started.
* PGA (program global Area) --- Allocated when server process Started.

Sytem Global Area ( Shared Global Area):-

Shared Pool ( Library Cache and Data Dictionary Cache)
Database buffer Cache
Redo log buffer cache

two optionally memory struture

Java Pool
Large Pool

* Sga is used to store database information that is shared by database process.It Contains data and control information for the database.

* Its allocated for the virtual memory of the computer where oracle resides.

sql> show sga; (this is view of sga).

Dynamic Sga :-

* It allows sga configuration changes without Shutdown the instance . It Can be shared pool ,database buffer cache ,redo log buffer cache.

buffer ( temporarly storage one,example browsing any page when click on any link its seeking that page very slowly ,when you enterd backside button,its coming very fastly).

* A granule is a unit of contiguous(in contact or sharing an edge) virtual memory allocation.

* one granule size 4MB means if estimated sga size is <128mb.

*One granule size is 16MB Otherwise. >128MB.

*buffer cache,large pool,shared pool are allowed to shrink and grow based on granules boundries.

*Allocation of granules via ALTER SYSTEM

*Target size in granules tracked and displayed by the V$BUFFER_POOL view.

Shared Pool :-

* Its used to store the most recently executed sal statments and data definitions.

Library cache
Data dictionary cache

sql> alter system set shared_pool_size=64MB;

* It Contains both fixed and variable size.

* Variable Struture ---- It grow and shrink depends on user programme requirments.

** Total sga size cannot exceed sga_max_size.

Library Cache :-

* the query or sql statment against the database.

* It stores information about most recently executed used shared sql and pl/sql statments.

* It managed by least recently used (LRU) algorithm.

* size determined by shared pool size.

* If the size of the shared pool too small,statments are reloaded into the library cache ,which affects performance.

* As the cache fills, Less recently used execution paths and parse trees removed from the library cache to make room for the new entries. They eventually aged out.

Data Dictionary Cache :-

* Its a collection of most recently used definitions in the database.

* It includes information about

Database files
Indexes
tables
columns
users
priviliges and other database objects.

* During the parse phase ,the server process looks at the data dictionary information to resolve object names and validate access.

* Caching the data dictionary information into memory improves response time on queries.

* Size determined by shared pool sizing.

Database Buffer Cache :-

* It stores copies of data block that retrived from the datafiles.

* It enables great performance gains when you obtain and update data.

* It is managed by LRU algorithm

* db_block_size determined by the primary block size.

query process --->server process looks ----> database buffer cache for any need blocks.
If not found, ----> server process reads the block -----> datafiles.and places copies in db cache.

* Consistent of independent of sub-caches

db_cache_size --> default buffer cache size only and cannot set to be zero.
db_keep_cache_size ---> To retain blocks in memory that are likely to be reused.
db_recycle_cache_size ---> eliminate block from the memory that have little reused.

* Db cache dynamically resized

sql>alter system set db_cache_size = 96M;

* Size of each buffer cache is equal to the size of an Oracle Block.

* Non-standard block size any power of two value between 2kb and 32kb.

Redolog Buffer Cache :-

* It records all changes made to the database data blocks.

* Its primary purpose recovery.

* Changes recorded within are called redo entries.

* Redo entries contain information to the reconstruct or redo changes.

* size is defined by Log_buffer.

Oracle Fundamentals I

2008-07-01T16:38:00.001+05:30

Oracle Architectural Components

Oracle Server:-\

* It contains Several files,process and memory strutures in an oracle server .Not all of them are used when executing a sql statment.

*The database can be recovered in event of software or hardware error.

*It consists of Oracle Instance and Database.

Client-application server-server
client-server
host-based

Client-application-server:-\ (three tiers) user access the database from local computer through an application server.

Client-Server:-\ (two tiers) User access the database from their personal computer(client) over a network.Database sits in a separate computer(server).

Host-Based :-\ Users directly connected to the same computer on which the database resides.

Oracle Instance:-\

* It combination of Background process and memory struture.

*Instance must be started to access the data in database.

*when instance start----> SGA(system global Area ) allocated & bg process are started.

*It perform functions on behalf of invoking processes.

*Always open one and only database.

Oracle Database :-\

*It consists of Operating system files and also known as datafiles.

*It provide actual storage information and data kept consistent and can be recovered in event of

software or hardware error.

* It consists of three files,

Datafiles--Actual data in a database.

Redo logs --- A record changes of made to the database to enable recovery of the data in case of failures.

Control files ---- containg information necessary to maintain and verify database integrity.

Other Key Files :-

Password file

Parameter file (characterstics of an Oracle instance.it contains size of the sga and so) and Archived redo log file (Offline copies of the redo log files).

Establishing a connection & Creating Session:-

Database user(user process)<------connection established----->server process<---->(session create) oracle server.

* Before users can submit Sql statments to an oracle database,they must connect to an instance.

* Such as sql*plus (tool) or Oracle Forms(Application) is excuted as s user process.

* When user logs on to the Oracle server, A process is created on the computer running the Oracle Server. This process is called a Server process.

Sessions:-

* The session starts when the user is validated by the oracle Server and it ends when the user logs out or when there is an abnormal condition.

Example: When you log on to any mail id,it created session and then if you are not using particular time ,the session will be ended and then you again login.

* One to one correspondance between a user and server process is called a dedicated server.

* Possible for multiple user processes to share server processes.