Oracle dba

Wednesday 30 November 2011

Migrating Oracle10g Database to Automatic Storage management (ASM)

Disable Block change tracking:

SQL> select * from v$block_change_tracking;

STATUS

----------

FILENAME

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

BYTES

----------

DISABLED

If not disabled then, disble using this command.

SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

Database altered.

SQL>

Shutdown Database Cleanly:

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Create pfile and add/modify the below parameters:

[oracle@node1-pub oracle]$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 21 12:17:50 2006

Connected to an idle instance.

SQL> create pfile from spfile;

File created.

Modify pfile with these parameters:

I have already created 2 ASM diskgroups DATA and FLASH.

*.control_files=(+DATA, +FLASH)

*.db_recovery_file_dest=+FLASH

*.db_recovery_file_dest_size=2147483648

*.db_create_file_dest=+DATA

*.db_create_online_log_dest_1=+FLASH

*.db_create_online_log_dest_2=+DATA -- optional if you want another online redo logs dest.

Create spfile back from modified pfile:

PS: take a copy of original spfile before you overwrite spfile using below command.

SQL> create spfile from pfile;

File created.

SQL> exit

Disconnected

Copy Database to ASM diskgroups using rman:
(1) start the instance on NOMOUNT state

(2) copy the controlfile from old location to ASM usin "resrore" rman command

(3) mount the database

(4) copy the datafiles to ASM disk group using rman "BACKUP AS COPY DATABASE" command

(5) Switch database to COPY and open the database.

[oracle@node1-pub oracle]$ $ORACLE_HOME/bin/rman

Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jul 21 10:03:10 2006

RMAN> connect target

connected to target database (not started)

RMAN> startup nomount

Oracle instance started

Total System Global Area 167772160 bytes

Fixed Size 1218316 bytes

Variable Size 83888372 bytes

Database Buffers 79691776 bytes

Redo Buffers 2973696 bytes

RMAN> restore controlfile from '/home/oracle/oradata/db10g/control01.ctl';

Starting restore at 21-JUL-06

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=157 devtype=DISK

channel ORA_DISK_1: copied control file copy

output filename=+DATA/db10g/controlfile/backup.256.596369129

output filename=+FLASH/db10g/controlfile/backup.256.596369131

Finished restore at 21-JUL-06

RMAN> startup mount

database is already started

database mounted

released channel: ORA_DISK_1

RMAN> configure device type disk parallelism 4;

new RMAN configuration parameters:

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;

new RMAN configuration parameters are successfully stored

RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';

Starting backup at 21-JUL-06

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=152 devtype=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: sid=151 devtype=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: sid=150 devtype=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: sid=149 devtype=DISK

channel ORA_DISK_1: starting datafile copy

input datafile fno=00001 name=/home/oracle/oradata/db10g/system01.dbf

channel ORA_DISK_2: starting datafile copy

input datafile fno=00003 name=/home/oracle/oradata/db10g/sysaux01.dbf

channel ORA_DISK_3: starting datafile copy

input datafile fno=00002 name=/home/oracle/oradata/db10g/undotbs01.dbf

channel ORA_DISK_4: starting datafile copy

input datafile fno=00004 name=/home/oracle/oradata/db10g/users01.dbf

output filename=+DATA/db10g/datafile/undotbs1.259.596369341 tag=TAG20060721T100858 recid=2 stamp=596369352

channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:16

channel ORA_DISK_3: starting datafile copy

copying current control file

output filename=+DATA/db10g/datafile/users.260.596369341 tag=TAG20060721T100858 recid=1 stamp=596369350

channel ORA_DISK_4: datafile copy complete, elapsed time: 00:00:20

channel ORA_DISK_4: starting full datafile backupset

channel ORA_DISK_4: specifying datafile(s) in backupset

output filename=+DATA/db10g/controlfile/backup.261.596369361 tag=TAG20060721T100858 recid=3 stamp=596369364

channel ORA_DISK_3: datafile copy complete, elapsed time: 00:00:06

including current SPFILE in backupset

channel ORA_DISK_4: starting piece 1 at 21-JUL-06

channel ORA_DISK_4: finished piece 1 at 21-JUL-06

piece handle=+DATA/db10g/backupset/2006_07_21/nnsnf0_tag20060721t100858_0.262.596369369 tag=TAG20060721T100858 comment=NONE

channel ORA_DISK_4: backup set complete, elapsed time: 00:00:10

output filename=+DATA/db10g/datafile/sysaux.258.596369341 tag=TAG20060721T100858 recid=4 stamp=596369390

channel ORA_DISK_2: datafile copy complete, elapsed time: 00:01:05

output filename=+DATA/db10g/datafile/system.257.596369339 tag=TAG20060721T100858 recid=5 stamp=596369414

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:21

Finished backup at 21-JUL-06

RMAN> SWITCH DATABASE TO COPY;

datafile 1 switched to datafile copy "+DATA/db10g/datafile/system.257.596369339"

datafile 2 switched to datafile copy "+DATA/db10g/datafile/undotbs1.259.596369341"

datafile 3 switched to datafile copy "+DATA/db10g/datafile/sysaux.258.596369341"

datafile 4 switched to datafile copy "+DATA/db10g/datafile/users.260.596369341"

RMAN> alter database open;

database opened

RMAN> exit

Recovery Manager complete.

Migrate tempfile to ASM:

RMAN does not migrate the tempfile as part of the BACKUP AS COPY and SWITCH command becuase the tempfile is not listed in controlfile.
The tempfile has to be manually migrated to ASM.

[oracle@node1-pub oracle]$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 21 10:12:42 2006

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> select name, bytes from v$tempfile;

NAME

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

BYTES

----------

/home/oracle/oradata/db10g/temp01.dbf

20971520

SQL> create temporary tablespace temp1 tempfile SIZE 100M extent management local uniform size 1M;

Tablespace created.

SQL> alter database default temporary tablespace temp1;

Database altered.

SQL> drop tablespace temp including contents;

Tablespace dropped.

SQL> create temporary tablespace temp tempfile SIZE 100M extent management local uniform size 1M;

Tablespace created.

SQL> alter database default temporary tablespace temp;

Database altered.

SQL> drop tablespace temp1 including contents;

Tablespace dropped.

SQL> select name from v$tempfile;

NAME

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

+DATA/db10g/tempfile/temp.264.596370217

Migrate and drop the old Online Redo Logs to ASM:

Run the below procedure to migrate the redo logs to ASM. This program is taken from OTN/metalink.

SQL> declare

cursor orlc is

select lf.member, l.bytes

from v$log l, v$logfile lf

where l.group# = lf.group# and

lf.type = 'ONLINE'

order by l.thread#, l.sequence#;

type numTab_t is table of number index by binary_integer;

type charTab_t is table of varchar2(1024) index by binary_integer;

byteslist numTab_t; namelist charTab_t;

procedure migrateorlfile(name IN varchar2, bytes IN number) is

retry number;

stmt varchar2(1024);

als varchar2(1024) := 'alter system switch logfile';

begin

select count(*) into retry from v$logfile;

stmt := 'alter database add logfile size ' || bytes;

execute immediate stmt;

stmt := 'alter database drop logfile ''' || name || '''';

for i in 1..retry loop

begin execute immediate stmt;

exit;

exception

when others then

if i > retry then raise;

end if;

execute immediate als;

end;

end loop;

end;

begin

open orlc;

fetch orlc bulk collect into namelist, byteslist;

close orlc;

for i in 1..namelist.count loop migrateorlfile(namelist(i), byteslist(i));

end loop;

end;

2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

PL/SQL procedure successfully completed.

SQL> SQL>

SQL>

SQL> select member from v$logfile;

MEMBER

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

+FLASH/db10g/onlinelog/group_3.259.596373299

+FLASH/db10g/onlinelog/group_2.258.596373295

/home/oracle/oradata/db10g/redo01.log

+FLASH/db10g/onlinelog/group_4.257.596373293

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

Re-Execute the same script again in order to migrate the remaining ones.

SQL> declare

cursor orlc is

select lf.member, l.bytes

from v$log l, v$logfile lf

where l.group# = lf.group# and

lf.type = 'ONLINE'

order by l.thread#, l.sequence#;

type numTab_t is table of number index by binary_integer;

type charTab_t is table of varchar2(1024) index by binary_integer;

byteslist numTab_t; namelist charTab_t;

procedure migrateorlfile(name IN varchar2, bytes IN number) is

retry number;

stmt varchar2(1024);

als varchar2(1024) := 'alter system switch logfile';

begin

select count(*) into retry from v$logfile;

stmt := 'alter database add logfile size ' || bytes;

execute immediate stmt;

stmt := 'alter database drop logfile ''' || name || '''';

for i in 1..retry loop

begin execute immediate stmt;

exit;

exception

when others then

if i > retry then raise;

end if;

execute immediate als;

end;

end loop;

end;

begin

open orlc;

fetch orlc bulk collect into namelist, byteslist;

close orlc;

for i in 1..namelist.count loop migrateorlfile(namelist(i), byteslist(i));

end loop;

end;

/ 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

PL/SQL procedure successfully completed.

SQL> select member from v$logfile;

MEMBER

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

+FLASH/db10g/onlinelog/group_3.259.596373619

+FLASH/db10g/onlinelog/group_2.258.596373615

+FLASH/db10g/onlinelog/group_1.261.596373613

+FLASH/db10g/onlinelog/group_4.257.596373293

+FLASH/db10g/onlinelog/group_5.260.596373609

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

DELETE THE OLD DATAFILES USING RMAN.

This way, it will also clear out the datafiles entry from controlfile.

[oracle@node1-pub oracle]$ $ORACLE_HOME/bin/rman

Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jul 21 11:22:33 2006

RMAN> connect target

connected to target database: DB10G (DBID=4283639931)

RMAN> run {

2> DELETE COPY OF DATABASE;

3> }

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=134 devtype=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: sid=151 devtype=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: sid=153 devtype=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: sid=138 devtype=DISK

List of Datafile Copies

Key File S Completion Time Ckp SCN Ckp Time Name

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

6 1 A 21-JUL-06 461254 21-JUL-06 /home/oracle/oradata/db10g/system01.dbf

7 2 A 21-JUL-06 461254 21-JUL-06 /home/oracle/oradata/db10g/undotbs01.dbf

8 3 A 21-JUL-06 461254 21-JUL-06 /home/oracle/oradata/db10g/sysaux01.dbf

9 4 A 21-JUL-06 461254 21-JUL-06 /home/oracle/oradata/db10g/users01.dbf

Do you really want to delete the above objects (enter YES or NO)? YES

deleted datafile copy

datafile copy filename=/home/oracle/oradata/db10g/system01.dbf recid=6 stamp=596369439

deleted datafile copy

datafile copy filename=/home/oracle/oradata/db10g/undotbs01.dbf recid=7 stamp=596369439

deleted datafile copy

datafile copy filename=/home/oracle/oradata/db10g/sysaux01.dbf recid=8 stamp=596369440

deleted datafile copy

datafile copy filename=/home/oracle/oradata/db10g/users01.dbf recid=9 stamp=596369440

Deleted 4 objects

RMAN> exit

Recovery Manager complete.

REMOVE THE OLD ONLINE REDO LOGS FILES PHYSICALLY:

[oracle@node1-pub oracle]$ rm /home/oracle/oradata/db10g/redo*.log

[oracle@node1-pub oracle]$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 21 11:29:56 2006

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Enable the block change tracking:

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

Database altered.

SQL>

Tuesday 29 November 2011

Recovering a Standby database from a missing archivelog

Recovering a Standby database from a missing archivelog:
We had a problem at last week .One of production DR was not sync with Production Database. Almost 2182 archieve logs were missed on DR site. We have Rman archieve logs backup and file system backup on TAP but we were not performance the restore from archive log backup instead using RMAN incremental backups can be used to synchronize a DR database with the Production Database.
To avoid the unwanted problem:
1. Rman incremental backup is very faster then restore archive log from Tab Lib.
2. Consuming space on archive location.
3. Save the time.
The following steps we were performed to resolve problems.

DR site :( Standby Database)
----------------------------------
Step 1) Cancel the recover mode on DR .
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Step2 ) To find the SCN which will be used for the incremental backup at the Production Database.
SELECT CURRENT_SCN FROM V$DATABASE;

Production Site:(Primary Database)
--------------------------------------------
Step 1) Using RMAN, connect to the primary database and create an incremental backup from the SCN.
$ RMAN connect target \
RMAN >BACKUP INCREMENTAL FROM SCN 174568 DATABASE FORMAT '/tmp/ForDR_%U' tag 'FORSTANDBY';
Step 2)Transfer all backup sets created on the Production server to the DR(Standby) server. 1. SCP /tmp/ForDR_* oracle@:\tmp\

Connect DR Site ( Standby Database):
---------------------------------------------
Step 1) Connect to the standby database as the RMAN target, and catalog all incremental backup pieces
RMAN>CATALOG START WITH '/tmp/ForDR';
Step 2) Start the recover the DR (Standby Database) with using incremental backup.
RMAN>RECOVER DATABASE NOREDO;
Once recover has been completed connect to Production Database.
Production Database Site(Primary Database):
-------------------------------------------------------
Step1 )Connect to RMAN to create standby controlfile.
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT '/tmp/stdbyctl.bk’;
Step 2) To transfer the DR controlfile to DR site(Standby Database)
SCP /tmp/stdbyctl.bk oracle@:\tmp\
DR site(Standby Database):
Step 1) Shutdown Immediate;
Step2) Startup nomount
Step 3) Using RMAN, connect to standby database and restore the standby control file
RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/stdbyctl.bk';
Step 4)Shutdown Immediate;
Step 5) Startup nount;
Step 6) Alter Database mount standby database;
Step 7) Alter database recovery managed standby database disconnect from session;
Note:
---------
RMAN does not consider the incremental backup as part of a backup strategy at the source database. Hence:
o The backup is not suitable for use in a normal RECOVER DATABASE operation at the source database
o The backup is not cataloged at the source database
o The backup sets produced by this command are written to the /dbs location by default, even if the flash recovery area or some other backup destination is defined as the default for disk backups.
You must create this incremental backup on disk for it to be useful. When you move the incremental backup to the standby database, you must catalog it at the standby as. Backups on tape cannot be cataloged.

Monday 28 November 2011

Red Hat Enterprise Linux 3 and Centos 3 Installation

Boot from the CD or DVD. At the boot screen, press the "Enter" key.
Press the "tab" key to move focus to the "Skip" key, and then press the "Enter" key to continue. On the "Welcome" screen, click the "Next" button

Select the appropriate language, and then click the "Next" button.

Select the relevant keyboard setting, and then click the "Next" button.

Select your preferred mouse configuration, and then click the "Next" button

Allow the installer to automatically partition or manual partition the disk by clicking on the "Next" button.

If manual partition

/ - root partition

/boot - Bootable file will be stored here, Referred as anaconda files

/tmp -temp files will be stored here.

/backup-optional

/swap

Note: Less than 4 GB RAM = 2* RAM SIZE (swap size)

Greater than 4 GB RAM = 1.2 * RAM SIZE (swap size)

File system

Windows –NTFS

Linux - EXT3, except swap

Click the "OK" button on the subsequent warning dialog.

To remove all existing partitions, click the "Next" button.

Click the "OK" button on the subsequent warning dialog.

The installer will list the default partitioning scheme for your size disk.

Edit the "/boot" partition, setting it to "150M" and "Fixed size", then click the "Next" button.

Edit the "/root" partition, setting it to "Fill to maximum allowable size" and "Force to be primary partition", then click the "Next" button.

Edit the "swap" partition, setting it to "2048M" and "Fixed size", then click the "Next" button if you need additional swap space, simply make this partition bigger.
The partitions screen should now looks something like the following picture, then click the "Next" button.

Accept the boot loader settings by clicking the "Next" button.

Configure the network interface with the appropriate settings. This can be done automatically using DHCP, or manually.
If you are not using DHCP, (manually) enter the appropriate IP address and subnet mask, and then click the "OK" button.

If you are not using DHCP, enter the host name, gateway and DNS information, and then click the "Next" button

Select the appropriate firewall configuration for your installation. For most test installations I select the "No firewall" option to prevent networking problems during setup. Click the "Next" button to proceed.

Select the appropriate language support for your installation, and then click the "Next" button.

Select the relevant time zone setting by clicking on the map, or selecting it on the selection list. Click on the "Next" button to proceed.

Enter a root password for the server and click the "Next" button to proceed.

Select the appropriate package groups for your installation and click the "Next" button.
The "Package Group Selection" screen allows you to select the required package. Groups and individual packages within the details section. When you've made your selection, click the "Next" button
On the "About to install" screen, click the "Next" button.

During the installation phase, enter the appropriate CDs as requested.

Select the appropriate graphics card for your server. Usually this will default to the correct setting. Click the "Next" button to continue.

Select the appropriate monitor, or a generic equivalent, and then click the "Next" button.
Select the appropriate screen resolution for your graphics card and monitor, then click the "Next" button.
Click the "Exit" button to complete the installation.
On the "Welcome" screen, click the "Next" button.
Accept the license agreement and click the "Next" button. Adjust the Date and Time settings if necessary, and click the "Next" Button

Create an additional system user if required, and click the "Next" button

If you chose not to define an additional system user, click the "Continue" button on the resulting warning dialog.
Decide if you wish to register the system with Red Hat Network, and click the "Next" button.
On the "Additional CDs" screen, click the "Next" button.
On the "Finish Setup" screen, click the "Next" button

Once the system has rebooted, you are presented with the login screen.
Once logged in, you are ready to use the desktop

Any queries regarding this please feel free to comment this.you comment will boost me to post more and more articles.