One Can Succeed at Almost Anything For Which He Has Enthusiasm...

Tuesday, June 7, 2011

Difference Between Char,Varchar and Varchar2

The fact that a CHAR/NCHAR is really nothing more than a VARCHAR2/NVARCHAR2 in disguise makes me of the opinion that there are really only two character string types to ever consider, namely VARCHAR2 and NVARCHAR2. I have never found a use for the CHAR type in any application. Since a CHAR type always blank pads the resulting string out to a fixed width, we discover rapidly that it consumes maximum storage both in the table segment and any index segments. That would be bad enough, but there is another important reason to avoid CHAR/NCHAR types: they create confusion in applications that need to retrieve this information (many cannot “find” their data after storing it). The reason for this relates to the rules of character string comparison and the strictness with which they are performed .

Here is Demo which will clear our doubt about the char and varchar .

1.) CHAR : Char should be used for storing fix length character strings. String values will be space/blank padded before stored on disk. If this type is used to store varibale length strings, it will waste a lot of disk space.

SQL> create table char_test (col1 CHAR(10));
Table created.

SQL> insert into char_test values ('qwerty');
1 row created.
COL1 LENGTH(COL1) ASCII Dump
------ ----------- ----------------------------------------------------------
qwerty 10 Typ=96 Len=10: 113,119,101,114,116,121,32,32,32,32

2.) VARCHAR : Currently VARCHAR behaves exactly the same as VARCHAR2. However, this type should not be used as it is reserved for future usage.

SQL> create table varchar_test (col1 varchar2(10));
Table created.

SQL> insert into varchar_test values ('qwerty');
1 row created.

SQL> select col1, length(col1), dump(col1) "ASCII Dump" from varchar_test;
COL1 LENGTH(COL1) ASCII Dump
------- ------------ --------------------------------------------------
qwerty 6 Typ=1 Len=6: 113,119,101,114,116,121

3.) VARCHAR2 : Varchar2 is used to store variable length character strings. The string value's length will be stored on disk with the value itself.

SQL> create table varchar2_test (col1 varchar2(10));
Table created.

SQL> insert into varchar2_test values ('qwerty');
1 row created.

SQL> select col1, length(col1), dump(col1) "ASCII Dump" from varchar2_test;
COL1 LENGTH(COL1) ASCII Dump
---------- ------------------ ------------------------------------------
qwerty 6 Typ=1 Len=6: 113,119,101,114,116,121

Below is an another example of "char" which will help us to understand the concept :

SQL> select * from char_test where col1 = 'qwerty';
COL1
----------
qwerty

SQL> variable y varchar2(25);
SQL> exec :y := 'qwerty'
PL/SQL procedure successfully completed.

SQL> select * from char_test where col1 = :y;
no rows selected

SQL> select * from char_test where col1 = rpad(:y,10);
COL1
----------
qwerty

Notice how when doing the search with a varchar2 variable (almost every tool in the world uses this type), we have to rpad() it to get a hit. If the field is in fact always 10 bytes long, using a char will not hurt -- However, it will not help either.

The only time I personally use a CHAR type is for CHAR(1). And that is only because its faster to type char(1) then varchar2(1) -- it offers no advantages.(according to t kytes).

Enjoy :-)

Monday, June 6, 2011

Myth of Commit Causing Buffer to be Flushed to the Disk

Today I have gone through a post of one of the famous Oracle Expert Anup Nanda. The post is very useful and invaluable.This Post is regarding the commit statements. There are very few person who have the knowledge of the oracle internals and Arup is one of them . There is a general confusion that commit means that the data are written to the disk but it is not always. Below is the link regarding the commits statements and hope this will help u .

http://arup.blogspot.com/2010/12/100-things-you-probably-didnt-know.html

Enjoy :-)

Saturday, June 4, 2011

What is Checkpoint ?

A checkpoint is an operation that Oracle performs to ensure data file consistency. When a checkpoint occurs, Oracle ensures all modified buffers are written from the data buffer to disk files. Frequent checkpoints decrease the time necessary for recovery should the database crash, but may decrease overall database performance.

A checkpoint performs the following three operations:

1.) Every dirty block in the buffer cache is written to the data files. That is, it synchronizes the datablocks in the buffer cache with the datafiles on disk. It's the DBWR that writes all modified databaseblocks back to the datafiles.

2.) The latest SCN is written (updated) into the datafile header.

3.) The latest SCN is also written to the controlfiles.

The checkpoint process (CKPT) is responsible for writing checkpoints to the data file headers and control file. Checkpoints occur in a variety of situations. For example, Oracle Database uses the following types of checkpoints:

1.) Thread checkpoints : The database writes to disk all buffers modified by redo in a specific thread before a certain target. The set of thread checkpoints on all instances in a database is a database checkpoint. Thread checkpoints occur in the following situations:

Consistent database shutdown .
ALTER SYSTEM CHECKPOINT statement .
Online redo log switch .
ALTER DATABASE BEGIN BACKUP statement

2.) Tablespace and data file checkpoints : The database writes to disk all buffers modified by redo before a specific target. A tablespace checkpoint is a set of data file checkpoints, one for each data file in the tablespace. These checkpoints occur in a variety of situations, including making a tablespace read-only or taking it offline normal, shrinking a data file, or executing ALTER TABLESPACE BEGIN BACKUP.

3.) Incremental checkpoints : An incremental checkpoint is a type of thread checkpoint partly intended to avoid writing large numbers of blocks at online redo log switches. DBWn checks at least every three seconds to determine whether it has work to do. When DBWn writes dirty buffers, it advances the checkpoint position, causing CKPT to write the checkpoint position to the control file, but not to the data file headers.

Other types of checkpoints include instance and media recovery checkpoints and checkpoints when schema objects are dropped or truncated.

Importance of Checkpoints for Instance Recovery :

Instance recovery uses checkpoints to determine which changes must be applied to the data files. The checkpoint position guarantees that every committed change with an SCN lower than the checkpoint SCN is saved to the data files.

Checkpoint Position in Online Redo Log File

During instance recovery, the database must apply the changes that occur between the checkpoint position and the end of the redo thread. As shown in Figure, some changes may already have been written to the data files. However, only changes with SCNs lower than the checkpoint position are guaranteed to be on disk.

Time and SCN of last checkpoint :

The date and time of the last checkpoint can be retrieved through checkpoint_time in v$datafile_header view

The SCN of the last checkpoint can be found in v$database.

Enjoy J J J

Tuesday, May 31, 2011

Post Checking After Database Clonning

As I have already covered the clonning through different method .Here are some Post checklist to check the cloned database environment.

1.) Check Database Name and DBID :

After successful creation of clonning on same or different server we should try to change the database name and id.(in case of same server, clone has different name) .It is suggested to changed the DBID of clone database because Recovery Manager (RMAN) distinguishes databases by DBID. For more details click here

2.) Edit the Listener.ora and tnsname.ora files :

Edit the listener file to include the new entry of the clonned database.Invoke the lsnrctl utility and reload the listener. Similarly check the service name in the tnsnames.ora file. Make sure the connection should be connected to cloned database.

3.) Check all the schedule jobs :

If we have scheduled any scripts ,then make sure that all the jobs are enabled for the clonned database.

4.) Verify the parameter file :

Make sure the location mention in the pfile of the clonned database should be correct. The location and values of the parameter should be valid.

5.) Check tempfile :

After the successful creation of the clone database ,check the tempfile. If there is no tempfile then add the new tempfile in clone database.

6.) Check the archivelog :

Generally archivelog mode is disabled for UAT/Cloned databases. In case our production database is in archivelog mode, make sure that the clonned database archiving is disable.

7.) Check for database link :

Check for database link present in the cloned environment. Ensure that these are select only dblinks and will not perform any DML in production databases. If find any ,then you can either drop these or recreate them to point to any UAT or simply remove/hash out tnsnames entry corresponding to these hosts. Also check for any hard coded IP address in host column in DBA_DB_LINKS.

Enjoy :-)

How To Change DBID and DB NAME

DBNEWID is a database utility that can change the internal database Identifier(DBID) and database name (DBNAME). The DBNEWID utility solves this problem by allowing us to change any of the following:

1.) Only the DBID of a database

2.) Only the DBNAME of a database

3.) Both the DBNAME and DBID of a database

1.) Only the DBID of a database : The DBID is an internal, unique identifier for a database. Because Recovery Manager (RMAN) distinguishes databases by DBID, we could not register a seed database and a manually copied database together in the same RMAN repository.

Changing the DBID of a database is a serious procedure. When the DBID of a database is changed, all previous backups and archived logs of the database become unusable. This is similar to creating a database except that the data is already in the datafiles. After we change the DBID, backups and archive logs that were created prior to the change can no longer be used because they still have the original DBID, which does not match the current DBID. we must open the database with the RESETLOGS option, which re-creates the online redo logs and resets their sequence to 1 .

Below are steps to change the dbid of the database.

C:\>set ORACLE_SID=test

C:\>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 30 14:16:48 2011

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 263639040 bytes

Fixed Size 1373964 bytes

Variable Size 205523188 bytes

Database Buffers 50331648 bytes

Redo Buffers 6410240 bytes

Database mounted.

Database opened.

SQL> select name,dbid from v$database;

NAME DBID

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

TEST 2050645904

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 10

Next log sequence to archive 12

Current log sequence 12

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 263639040 bytes

Fixed Size 1373964 bytes

Variable Size 205523188 bytes

Database Buffers 50331648 bytes

Redo Buffers 6410240 bytes

Database mounted.

SQL> host nid target=sys/test@test

DBNEWID: Release 11.2.0.1.0 - Production on Mon May 30 14:25:13 2011

Connected to database TEST (DBID=2050645904)

Connected to server version 11.2.0

Control Files in database:

D:\APP\NEERAJS\ORADATA\TEST\CONTROL01.CTL

D:\APP\NEERAJS\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL

Change database ID of database TEST? (Y/[N]) => Y

Proceeding with operation

Changing database ID from 2050645904 to 2050636234

Control File D:\APP\NEERAJS\ORADATA\TEST\CONTROL01.CTL - modified

Control FileD:\APP\NEERAJS\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL - modified

Datafile D:\APP\NEERAJS\ORADATA\TEST\SYSTEM01.DB - dbid changed

Datafile D:\APP\NEERAJS\ORADATA\TEST\SYSAUX01.DB - dbid changed

Datafile D:\APP\NEERAJS\ORADATA\TEST\UNDOTBS01.DB - dbid changed

Datafile D:\APP\NEERAJS\ORADATA\TEST\USERS01.DB - dbid changed

Datafile D:\APP\NEERAJS\ORADATA\TEST\EXAMPLE01.DB - dbid changed

Datafile D:\APP\NEERAJS\ORADATA\TEST\TEMP01.DB - dbid changed

Control File D:\APP\NEERAJS\ORADATA\TEST\CONTROL01.CTL - dbid changed

Control File D:\APP\NEERAJS\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL

dbid changed

Instance shut down

Database ID for database TEST changed to 2050636234. All previous backups and archived redo logs for this database are unusable.Database is not aware of previous backups and archived logs in Recovery Area.Database has been shutdown, open database with RESETLOGS option.

Succesfully changed database ID.

DBNEWID - Completed succesfully.

C:\>sqlplus sys/test@test as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 30 14:26:13 2011

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 263639040 bytes

Fixed Size 1373964 bytes

Variable Size 205523188 bytes

Database Buffers 50331648 bytes

Redo Buffers 6410240 bytes

Database mounted.

SQL> alter database open resetlogs;

Database altered.

SQL> select name,dbid from v$database;

NAME DBID

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

TEST 2050636234

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 1

Next log sequence to archive 1

Current log sequence 1

2.) Only the DBNAME of a database : Changing the DBNAME without changing the DBID does not require us to open with the RESETLOGS option, so database backups and archived logs are not invalidated. However, changing the DBNAME does have consequences. We must change the DB_NAME initialization parameter after a database name change to reflect the new name. Also, we may have to re-create the Oracle password file. If we restore an old backup of the control file (before the name change), then we should use the initialization parameter file and password file from before the database name change.

Below are steps to change the database name of the database :

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 263639040 bytes

Fixed Size 1373964 bytes

Variable Size 209717492 bytes

Database Buffers 46137344 bytes

Redo Buffers 6410240 bytes

Database mounted.

SQL> host nid TARGET=SYS/test@test DBNAME=newtest SETNAME=YES

DBNEWID: Release 11.2.0.1.0 - Production on Mon May 30 14:35:45 2011

Connected to database TEST (DBID=2050636234)

Connected to server version 11.2.0

Control Files in database:

D:\APP\NEERAJS\ORADATA\TEST\CONTROL01.CTL

D:\APP\NEERAJS\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL

Change database name of database TEST to NEWTEST? (Y/[N]) => Y

Proceeding with operation

Changing database name from TEST to NEWTEST

Control File D:\APP\NEERAJS\ORADATA\TEST\CONTROL01.CTL - modified

Control File D:\APP\NEERAJS\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL - modified

Datafile D:\APP\NEERAJS\ORADATA\TEST\SYSTEM01.DB - wrote new name

Datafile D:\APP\NEERAJS\ORADATA\TEST\SYSAUX01.DB - wrote new name

Datafile D:\APP\NEERAJS\ORADATA\TEST\UNDOTBS01.DB - wrote new name

Datafile D:\APP\NEERAJS\ORADATA\TEST\USERS01.DB - wrote new name

Datafile D:\APP\NEERAJS\ORADATA\TEST\EXAMPLE01.DB - wrote new name

Datafile D:\APP\NEERAJS\ORADATA\TEST\TEMP01.DB - wrote new name

Control File D:\APP\NEERAJS\ORADATA\TEST\CONTROL01.CTL - wrote new name

Control File D:\APP\NEERAJS\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL - wrote new name

Instance shut down

Database name changed to NEWTEST. Modify parameter file and generate a new password file before restarting.Succesfully changed database name.

DBNEWID - Completed succesfully.

SQL> exit

C:\>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 30 14:36:39 2011

Connected to an idle instance.

SQL> create pfile='c:\inittest.ora' from spfile;

File created.

Now open the pfile inittest.ora and replace db_name parameter value "test" to "newtest"

SQL> create spfile from pfile='c:\inittest.ora' ;

File created.

SQL> startup

ORACLE instance started.

Total System Global Area 263639040 bytes

Fixed Size 1373964 bytes

Variable Size 209717492 bytes

Database Buffers 46137344 bytes

Redo Buffers 6410240 bytes

Database mounted.

Database opened.

SQL> select open_mode ,name from v$database;

OPEN_MODE NAME

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

READ WRITE NEWTEST

3.) Both the DBNAME and DBID of a database

C:\>sqlplus sys/test@newtest as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 30 15:54:45 2011

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select dbid, name,open_mode from v$database;

DBID NAME OPEN_MODE

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

2050636234 NEWTEST READ WRITE

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 30 16:01:03 2011

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 263639040 bytes

Fixed Size 1373964 bytes

Variable Size 213911796 bytes

Database Buffers 41943040 bytes

Redo Buffers 6410240 bytes

Database mounted.

SQL> host nid target=sys/test@newtest dbname=prod

DBNEWID: Release 11.2.0.1.0 - Production on Mon May 30 16:11:17 2011

Connected to database NEWTEST (DBID=2050636234)

Connected to server version 11.2.0

Control Files in database:

D:\APP\NEERAJS\ORADATA\TEST\CONTROL01.CTL

D:\APP\NEERAJS\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL

Change database ID and database name NEWTEST to PROD? (Y/[N]) => Y

Proceeding with operation

Changing database ID from 2050636234 to 164330150

Changing database name from NEWTEST to PROD

Control File D:\APP\NEERAJS\ORADATA\TEST\CONTROL01.CTL - modified

Control FileD:\APP\NEERAJS\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL modifd

Datafile D:\APP\NEERAJS\ORADATA\TEST\SYSTEM01.DB - dbid changed, wrote new name

Datafile D:\APP\NEERAJS\ORADATA\TEST\SYSAUX01.DB - dbid changed, wrote new name

Datafile D:\APP\NEERAJS\ORADATA\TEST\UNDOTBS01.DB-dbid changed,wrote new name

Datafile D:\APP\NEERAJS\ORADATA\TEST\USERS01.DB - dbid changed, wrote new name

Datafile D:\APP\NEERAJS\ORADATA\TEST\EXAMPLE01.DB - dbid changed, wrote new name

Datafile D:\APP\NEERAJS\ORADATA\TEST\TEMP01.DB - dbid changed, wrote new name

Control File D:\APP\NEERAJS\ORADATA\TEST\CONTROL01.CTLdbid changd,wrote new name Control File D:\APP\NEERAJS\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL - dbid changed, wrote new name

Instance shut down

Database name changed to PROD. Modify parameter file and generate a new password file before restarting. Database ID for database PROD changed to 164330150. All previous backups and archived redo logs for this database are unusable.Database is not aware of previous backups and archived logs in Recovery Area. Database has been shutdown, open database with RESETLOGS option. Succesfully changed database name and ID.

DBNEWID - Completed succesfully.

SQL> exit

C:\>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 30 16:43:18 2011

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area 263639040 bytes

Fixed Size 1373964 bytes

Variable Size 209717492 bytes

Database Buffers 46137344 bytes

Redo Buffers 6410240 bytes

Database mounted.

SQL> alter database open resetlogs;

Database altered.

SQL> select name,dbid,open_mode from v$database;

NAME DBID OPEN_MODE

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

PROD 164330150 READ WRITE

Enjoy :-)