To change the Database name using NID utility

1)Make a whole database backup.

2)Login as a sys user..

sql>conn sys as sysdba

3)Check your database name

sql>SELECT nameFROM v$database;

NAME
———
ORCL

4)Make a proper shutdown of the database

here you can user immediate or transactional or normal

sql>SHUTDOWN IMMEDIATE

5) Invoke the newid utility

C:\mohd.dba>nid TARGET=system/manager@orcl DBNAME=newdb SETNAME=Y

DBNEWID: Release 10.2.0.1.0 – Production on Sat Jan 31 12:45:03 2009

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

Connected to database ORCL (DBID=1203749959)

Connected to server version 10.2.0

Control Files in database:
G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL
G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTL
G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03.CTL

Changing database name from ORCL to NEWDB
Control File G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL – modified
Control File G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTL – modified
Control File G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03.CTL – modified
Datafile G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF – wrote new name
Datafile G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF – wrote new name
Datafile G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF – wrote new name
Datafile G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF – wrote new name
Datafile G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF – wrote new name
Datafile G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEMP01.DBF – wrote new name
Control File G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL – wrote new name
Control File G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTL – wrote new name
Control File G:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03.CTL – wrote new name
Instance shut down

Database name changed to NEWDB.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID – Completed succesfully.

6)Edit the pfile to change the db_name:

change the db_name parameter in the pfile to the new database name

db_name=newdb

7)Create a new password file using the ORAPWD utility

C:\mohd.dba>orapwd file=orapw001 password=confidential entries=10

8)Start the database using pfile:

sql>conn sys as sysdba

sql>startup pfile =G:\oracle\product\10.2.0\admin\orcl\pfile\init.ora.0122009171812

9)Recreate the spfile from the pfile

sql> create spfile from pfile=’G:\oracle\product\10.2.0\admin\orcl\pfile\init.ora.0122009171812′;

10) start the database using spfile:

SQL> startup force;
ORACLE instance started.

Total System Global Area 289406976 bytes
Fixed Size 1248600 bytes
Variable Size 92275368 bytes
Database Buffers 192937984 bytes
Redo Buffers 2945024 bytes
Database mounted.
Database opened.

How to make an Index creation Faster

When an index is created on a table , a full table scan is
performed. Oracle fetches rows from the table into memory and sorts them prior  to creating the index.  For this task, Oracle requires sort area space in memory.  If memory areas for sorting are not large enough, Oracle will divide the data into smaller sections, sort each section individually, and then merge  together the results.  This is not as efficient as if memory allocated were large enough for the sort.

1). Increase “SORT_AREA_SIZE” parameter in the “init.ora”. 

 Sqlplus> alter session set sort_area_size=25000;
 

2)PARALLEL Option: While creating index oracle must first collect the symbolic key/ROWID pairs with a full-table scan. With Parallel option supplied to the create index script oracle will scan full table parallel based on the number of CPUs, table partitioning and disk configuration and thus do the tasks faster.

On a server that have 6 CPUs you may give parallel 5 as below.
create index emp_idx on emp(id,code) parallel 5;

3)NOLOGGING Option: With the NOLOGGING option provided while creating index you can restrict database to generate a large redo log. NOLOGGING option generates minimal redo. So you will achieve higher performance.

create index emp_idx on emp(id,code) parallel 5 nologging;

 4)Larger Block Size: You can create an index in a tablespace that uses Larger block size. If you have DSS environment then you can do it. This will improve performance while creating index.

You can do it by first creating a tablespace with 32k blocksize and then create index under it,

create tablespace index_ts
datafile ‘/u01/index_file.dbf’ size 1024m
blocksize 32k;

create index emp_idx on emp(id,code) parallel 5 nologging  tablespace index_ts;

Dead Connection Detection

TCP/IP is a connection-oriented protocol, and provides packet timeout and retransmission in order to guarantee the safe and sequenced order of data packets. If a timely acknowledgement is not received in response to the probe packet, the TCP/IP stack will retransmit the packet some number of times before timing out.
After TCP/IP gives up, then SQL*Net receives notification that the probe failed.

1. Dead Connection Detection

Dead Connection Detection (DCD) is a feature of SQL*Net 2.1 and later, including Oracle Net8. DCD detects when a partner in a SQL*Net V2 client/server or server/server connection has terminated unexpectedly, and releases the resources associated with it.

DCD is initiated on the server when a connection is established. At this time, SQL*Net reads the SQL*Net parameter files and sets a timer to generate an alarm. The timer interval is set by providing a non-zero value in minutes for the SQLNET.EXPIRE_TIME parameter in the sqlnet.ora file.

When the timer expires, SQL*Net on the server sends a “probe” packet to the client. The probe is an empty SQL*Net packet and does not represent any form of SQL*Net level data, but it creates data traffic on the underlying protocol.

If the client end of the connection is still active, the probe is discarded, and the timer mechanism is reset. If the client has terminated abnormally, the server will receive an error from the send call issued for the probe, and SQL*Net on the server will signal the operating system to release the connection’s resources.

The client may be running any supported SQL*Net V2 release. DCD is more resource-intensive than similar mechanisms at the protocol level.

With DCD enabled, if the connection is idle for the duration of the time interval specified in minutes by the SQLNET.EXPIRE_TIME parameter, the Server-side process sends a small 10-byte packet to the client. This packet is sent using TCP/IP.

To Configure Dead Connection Detection (DCD)

Implement by:

changing SQLNET.EXPIRE_TIME = 1 (Minutes) to the sqlnet.ora file

With DCD enabled, if the connection is idle for the duration of the time interval specified in minutes by the SQLNET.EXPIRE_TIME parameter, the Server-side process sends a small 10-byte packet to the client. This packet is sent using TCP/IP.

If a timely acknowledgement is not received in response to the probe packet, the TCP/IP stack will retransmit the packet some number of times before timing out. After TCP/IP gives up, then SQL*Net receives notification that the probe failed.

If the client side connection is still connected and responsive, the client sends a response packet back to the database server, resetting the timer, and another packet will be sent when the next interval expires , assuming no other activity on the connection

If the client fails to respond to the DCD probe packet:
• The Server side process is marked as a dead connection and
• The database Process Monitor (PMON) performs the clean-up of the database processes / resources

Dead Connection Detection:
• DCD initiates clean up of OS and database processes that have disconnected / terminated abnormally
• DCD will not initiate clean-up for sessions that are still connected

2. tnsnames.ora

Client side SQL*Net connections do not enable keepalive for TCP connections by default. However, it is possible to enable this by adding the ENABLE=BROKEN parameter to the SQL*Net connect string in the tnsnames.ora file on the server..

Sample TNS alias to enable keepalive (notice the ENABLE=BROKEN clause):

VIS_BALANCE =
(DESCRIPTION =
(ENABLE=BROKEN)
(ADDRESS_LIST =
(LOAD_BALANCE = ON)
(FAILOVER = ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = rh8)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rh6)(PORT = 1521)))

SCN TO Timestamp conversion and Vice Versa

Oracle 10g has new two built-in functions to check the scn of a particular time and vice versa.

these are the two functions

1) Timestamp_to_SCN

2) SCN_to_Timestamp

Examples:
SQL> select timestamp_to_scn(systimestamp) from dual;

TIMESTAMP_TO_SCN(SYSTIMESTAMP)
——————————
                       2880376

SQL> select current_scn from v$database;

CURRENT_SCN
———–
    2880392

SQL> select timestamp_to_scn(’09-JAN-09:12:00:10′) from dual;

TIMESTAMP_TO_SCN(’09-JAN-09:12:00:10′)
————————————–
                               2880066

SQL> select scn_to_timestamp(2880392)from dual;

SCN_TO_TIMESTAMP(2880392)
—————————————————————————
09-JAN-09 12.06.29.000000000 PM

How to Flashback a Logical error

We can able to flashback a database
to the previous point based on SCN,Timestamp and Log sequence number

Scenario 1: (with scn)
SQL> select current_scn from v$database;
 
CURRENT_SCN
———–
    2751136

SQL> select count(*) from boss.itemmast;

  COUNT(*)
———-
    124605

SQL> truncate table boss.itemmast;

Table truncated.

SQL> select current_scn from v$database;

CURRENT_SCN
———–
    2751924

SQL> flashback database to scn  2751136;
flashback database to scn  2751136
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.
SQL> shutdown immediate;
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  536870912 bytes
Fixed Size                  1250044 bytes
Variable Size             339741956 bytes
Database Buffers          192937984 bytes
Redo Buffers                2940928 bytes
Database mounted.

SQL> flashback database to scn  2751136;

Flashback complete.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;

Database altered.

SQL> select count(*) from boss.itemmast;

  COUNT(*)
———-
    124605
scenario 2: (with timestamp)

SQL> set time on
12:55:48 SQL> select flashback_on from v$database;

FLASHBACK_ON
——————
YES

12:56:06 SQL> select count(*) from boss.itemmast;

  COUNT(*)
———-
    124605

12:56:20 SQL> truncate table boss.itemmast;

Table truncated.

12:56:32 SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
12:58:01 SQL>
12:58:01 SQL> startup mount
ORACLE instance started.

Total System Global Area  536870912 bytes
Fixed Size                  1250044 bytes
Variable Size             343936260 bytes
Database Buffers          188743680 bytes
Redo Buffers                2940928 bytes
Database mounted.
13:00:22 SQL> FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP(‘2009-01-08 12:56:00’, ‘YYYY-MM-DD HH:MI:SS’);
  

Flashback complete.

or

13:00:22 SQL>FLASHBACK DATABASE TO TIMESTAMP (SYSDATE-1/24);————-it will take to 1 hour before

13:02:50 SQL> alter database open resetlogs;

Database altered.

13:04:23 SQL> select count(*) from boss.itemmast;

  COUNT(*)
———-
    124605

 

References:

http://www.coug.ab.ca/Resources/TomKyte_FlashBackQ_Mar-05.zip

http://download.oracle.com/oowsf2005/317.pdf

http://www.oracle.com/technology/deploy/availability/pdf/TWP_HA_FlashbackOverview_10g_111503.pdf

Metalink notes:

Note 249319.1 – Configure flashback database
Note 270060.1 – Use Flashback Table Feature and Resolve errors
Note 270535.1 – Restrictions on Flashback Table Feature
Note:435998.1 – What Do All 10g Flashback Features Rely on and what are their Limitations
Note 317499.1 – 10G Oracle Flashback Transaction Query – Introduction and usage
Note 369755.1 – Flashback Logs-Space management

How to enable the Flashback Database feature

Flashback Database allows you to quickly revert the entire database to its state as of a previous
point in time. Rather than restoring an older copy of each datafile and performing incomplete
recovery, the starting point is the present and recent changes are backed out of the database.

 Whenever Flashback Database is enabled, the new background process RVWR (RECOVERY WRITER) is started in order to write data from the flashback buffer in the System Global Area (SGA) to flashback logsin the flash recovery area. The flashback buffer – a  part of the SGA – depends on the sizing of the log buffer, once more it is recommended for databases with high transactional activity to have an appropriate log buffer sizing.
Here we discuss how  to turn on the flashback feature:

SQL> select flashback_on from v$database;

FLASHBACK
———
NO

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

SQL> startup mount exclusive;
ORACLE instance started.

Total System Global Area   209715200 bytes
Fixed Size                                    788524 bytes
Variable Size                               162887636 bytes
Database Buffers                       41943040 bytes
Redo Buffers                                4096000 bytes
Database mounted.
SQL> alter database flashback on;
Database altered.

SQL> select flashback_on from v$database;

FLASHBACK
———
YES

SQL> alter database open;
Database altered.

Let’s check the parameter associated with  the  flashback,

db_flashback_retention_target which is in  minutes ..

SQL> show parameter db_flashback_retention_target

NAME TYPE
———————————— ———————————

VALUE
——————————
db_flashback_retention_target integer
1440

Let us alter this retention target. This retention target is a retention goal , however not a guaranteed interval. Flashback logs are generated in the flash recovery area, the physical destination is set with db_recovery_file_dest and the maximum size can be controlled with db_recovery_file_dest_size. In periods of peak load the flashback logs are reused and the retention period might decrease. As well rman backups -if taken to disk- are by default written in the flash recovery area, as such an appropriate sizing of the db_recovery_file_dest_size need to be supervised.
SQL> alter system set db_flashback_retention_target=2880 scope=both;
System altered.

_kgl_large_heap_warning_threshold

hi,
Today Morning i have found that the  message “Memory Notification: Library Cache Object Loaded Into SGA” is been written  in alert log file.Last week this database has been  upgraded to 10g.

To  avoid these messages being generated, we need to adjust the size of a hidden initialization parameter “_kgl_large_heap_warning_threshold” to a high value or zero.

To find out a hidden parameter value in the database.

SQL> SELECT a.ksppinm “Parameter”,
  2  b.ksppstvl “Session Value”,
  3  c.ksppstvl “Instance Value”
  4  FROM x$ksppi a, x$ksppcv b, x$ksppsv c
  5  WHERE a.indx = b.indx AND
  6  a.indx = c.indx AND
  7  a.ksppinm LIKE ‘%kgl_large_heap%’;

Parameter
————————————————–
Session Value
————————————————–
Instance Value
————————————————–
_kgl_large_heap_warning_threshold
2097152
2097152

To modify this parameter:

login as sysdba

SQL> connect sys as sysdba
SQL> alter system set “_kgl_large_heap_warning_threshold”=8388608 scope=spfile ;

System altered.

 

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP;

SQL> SELECT a.ksppinm “Parameter”,
  2  b.ksppstvl “Session Value”,
  3  c.ksppstvl “Instance Value”
  4  FROM x$ksppi a, x$ksppcv b, x$ksppsv c
  5  WHERE a.indx = b.indx AND
  6  a.indx = c.indx AND
  7  a.ksppinm LIKE ‘%kgl_large_heap%’;

Parameter
————————————————–
Session Value
————————————————–
Instance Value
————————————————–
_kgl_large_heap_warning_threshold
8388608
8388608