Wednesday 18 December 2013

Convert Non-ASM Database to ASM Database in oracle Using RMAN

Assume that you have already created an ASM instance in a disk group.
1. Install ASM binaries.
2. Configure ASM & create 2 Disk groups DATA & FRA & assign disks for the respective disk groups.
3. Keep the ASM Instance UP.



Step by Step Configuring ASM in Oracle 10g.
$ export ORACLE_SID=PROD

$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Nov 15 11:30:10 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL>

Make the necessary changes:--
SQL> alter system set control_files='+DATA' scope=spfile;
SQL> alter system set db_create_file_dest='+DATA' scope=spfile;
SQL> alter system set db_recovery_file_dest='+DATA' scope=spfile;

SQL> shutdown immediate;

Connect the RMAN & restore the controlfile into the new location from the original location

$ export ORACLE_SID=Prod
$ rman target
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PROD (not started)


RMAN> startup nomount
Oracle instance started


RMAN>Restore controlfile from '\u01\product\10.2.0\oradata\prd\ctl_prd01.ctl' ;
RMAN> ALTER DATABASE MOUNT ;
RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA' ;

Switch the database to the copy created using the following RMAN command

RMAN> SWITCH DATABASE TO COPY;
RMAN> RECOVER DATABASE;

Using SQL*Plus to migrate flashback logs.

SQL> ALTER DATABASE FLASHBACK OFF;
SQL> select flashback_on from v$database;
SQL> ALTER DATABASE FLASHBACK ON;
SQL> ALTER DATABASE OPEN;

SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS


Convert Redo log files from Non-ASM to ASM
SQL> create temporary tablespace temp1 tempfile '+DATA';
Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;
Database altered.

SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;
Create new Redo logs in ASM Diskgroup and delete the old ones.

SQL> select group#, status from v$log;
GROUP# STATUS
---------- ----------------
1 UNUSED
2 CURRENT
3 UNUSED
4 UNUSED

SQL> alter system switch logfile;
SQL> select group#, status from v$log;
GROUP# STATUS
------- -----------
1 ACTIVE
2 ACTIVE
3 CURRENT
4 UNUSED

SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance prod (thread 1)
ORA-00312: online log 1 thread 1:
'\U01\PRODUCT\10.2.0\ORADATA\PROD\REDO01.LOG'

Note: Need to make sure that the redo logs groups that are dropped are not CURRENT or ACTIVE

SQL> alter system checkpoint global;
System altered.
SQL> alter system checkpoint global;
System altered.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile group 1 size 10m ;
Database altered.
SQL> alter system checkpoint global;
System altered.
SQL> select group#, status from v$log;
GROUP# STATUS
-------- -----------
1 UNUSED
2 INACTIVE
3 CURRENT
4 UNUSED
SQL>alter database drop logfile group 2;
SQL>alter database add logfile group 2 size 10m ;
SQL>alter system checkpoint global;
SQL>select group#,status from v$log;
GROUP# STATUS
-------- -----------
1 UNUSED
2 UNUSED
3 CURRENT
4 UNUSED
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
2 CURRENT
3 INACTIVE
4 UNUSED
SQL> alter database drop logfile group 3;
SQL> alter database add logfile group 3 size 10m ;
SQL> select group#,status from v$log;
GROUP# STATUS
------- ------------
1 INACTIVE
2 CURRENT
3 UNUSED
4 UNUSED
SQL> alter database drop logfile group 4;
SQL> alter database add logfile group 4 size 10m ;
SQL> select group#,status from v$log;
GROUP# STATUS
------- ----------
1 INACTIVE
2 CURRENT
3 UNUSED
4 UNUSED


SQL> select a.group#, a.member, b.bytes from v$logfile a, v$log b where a.group#= b.group#;

Create spfile for ASM

SQL> create pfile from spfile ;
SQL> create spfile='+DATA/SPFILEASM.ORA' FROM pfile;

Monday 16 December 2013

ORA-600 Metalink-Oracle Doc ID's

ORA-600 Errors 1 to 3000
==============================
Note 138300.1 "ORA-600 [105]"
Note 263295.1 "ORA-600 [106]"
Note 28104.1 "ORA-600 [504]"
Note 138871.1 "ORA-600 [510]"
Note 138888.1 "ORA-600 [525]"
Note 138939.1 "ORA-600 [711]"
Note 39308.1 "ORA-600 [723]"
Note 31056.1 "ORA-600 [729]"
Note 131490.1 "ORA-600 [733]"
Note 217860.1 "ORA-600 [1013]"
Note 138123.1 "ORA-600 [1100]"
Note 41767.1 "ORA-600 [1113]"
Note 40514.1 "ORA-600 [1114]"
Note 137262.1 "ORA-600 [1115]"
Note 66387.1 "ORA-600 [1158]"
Note 138354.1 "ORA-600 [1236]"
Note 28045.1 "ORA-600 [2103]"
Note 28929.1 "ORA-600 [2662]"
Note 31057.1 "ORA-600 [2845]"
Note 138733.1 "ORA-600 [2865]"


ORA-600 Errors 3001 to 6000
=============================

Note 30866.1 "ORA-600 [3020]"
Note 93665.1 "ORA-600 [3668]"
Note 47456.1 "ORA-600 [4000]"
Note 96642.1 "ORA-600 [4036]"
Note 43914.1 "ORA-600 [4137]"
Note 28226.1 "ORA-600 [4146]"
Note 39282.1 "ORA-600 [4193]"
Note 39283.1 "ORA-600 [4194]"
Note 138822.1 "ORA-600 [4400]"
Note 29702.1 "ORA-600 [4414]"
Note 138836.1 "ORA-600 [4454]"
Note 39553.1 "ORA-600 [4511]"
Note 73455.1 "ORA-600 [4512]"
Note 27955.1 "ORA-600 [4519]"
Note 204536.1 "ORA-600 [4820]"
Note 41840.1 "ORA-600 [4882]"


ORA-600 Errors 6001 to 9000
=============================

Note 47449.1 "ORA-600 [6002]"
Note 116552.1 "ORA-600 [6034]"
Note 40640.1 "ORA-600 [6101]"
Note 99300.1 "ORA-600 [6122]"
Note 138913.1 "ORA-600 [6193]"
Note 39399.1 "ORA-600 [6731]"
Note 41719.1 "ORA-600 [6856]"


ORA-600 Errors 9001 to 15000
=============================

Note 138325.1 "ORA-600 [12209]"
Note 33174.1 "ORA-600 [12235]"
Note 138332.1 "ORA-600 [12261]"
Note 138340.1 "ORA-600 [12304]"
Note 35928.1 "ORA-600 [12333]"
Note 28229.1 "ORA-600 [12700]"
Note 67496.1 "ORA-600 [13009]"
Note:28185.1 "ORA-600 [13013]"


ORA-600 Errors 15001 to 17000
==============================

Note 138428.1 "ORA-600 [15160]"
Note 138430.1 "ORA-600 [15201]"
Note 138431.1 "ORA-600 [15203]"
Note 131186.1 "ORA-600 [15212]"
Note 137266.1 "ORA-600 [15419]"
Note 216277.1 "ORA-600 [15456]"
Note 138457.1 "ORA-600 [15709]"
Note 67490.1 "ORA-600 [15851]"
Note 76528.1 "ORA-600 [15868]"
Note 138499.1 "ORA-600 [16201]"
Note 106607.1 "ORA-600 [16365]"
Note 138523.1 "ORA-600 [16515]"
Note 138526.1 "ORA-600 [16607]"


ORA-600 Errors 17001 to 30000
==============================

Note 138537.1 "ORA-600 [17003]"
Note 138541.1 "ORA-600 [17012]"
Note 41472.1 "ORA-600 [17034]"
Note 138554.1 "ORA-600 [17059]"
Note 39616.1 "ORA-600 [17069]"
Note 29616.1 "ORA-600 [17090]"
Note 138565.1 "ORA-600 [17099]"
Note 47411.1 "ORA-600 [17112]"
Note 39453.1 "ORA-600 [17113]"
Note 34782.1 "ORA-600 [17114]"
Note 138576.1 "ORA-600 [17128]"
Note 138580.1 "ORA-600 [17147]"
Note 34781.1 "ORA-600 [17148]"
Note 138586.1 "ORA-600 [17172]"
Note 263110.1 "ORA-600 [17175]"
Note 34779.1 "ORA-600 [17182]"
Note 45725.1 "ORA-600 [17271]"
Note 138597.1 "ORA-600 [17274]"
Note 134139.1 "ORA-600 [17280]"
Note 39361.1 "ORA-600 [17281]"
Note 138602.1 "ORA-600 [17285]"
Note 138621.1 "ORA-600 [17585]"
Note 138640.1 "ORA-600 [18209]"
Note 216278.1 "ORA-600 [18261]"
Note 138678.1 "ORA-600 [20084]"
Note 100073.1 "ORA-600 [25012]"


ORA-600 Errors kc
==================================

Note 138981.1 "ORA-600 [kcbgcur_2]"
Note 70097.1 "ORA-600 [kcbgcur_3]"
Note 114058.1 "ORA-600 [kcbgcur_9]"
Note 138990.1 "ORA-600 [kcbgtcr_4]"
Note 138991.1 "ORA-600 [kcbgtcr_5]"
Note 261264.1 "ORA-600 [kcbgtcr]"
Note 248874.1 "ORA-600 [kcbgtcr_6]"
Note 233612.1 "ORA-600 [kcbgtcr_12]"
Note 204512.1 "ORA-600 [kcbnew_3]"
Note 216104.1 "ORA-600 [kcbrbo1]"
Note 139011.1 "ORA-600 [kcbzpb_1]"
Note 139012.1 "ORA-600 [kcbzpb_2]"
Note 229467.1 "ORA-600 [kcbzwb_4]"
Note 139013.1 "ORA-600 [kccsbck_first]"
Note 216108.1 "ORA-600 [kcllcu_0]"
Note 263225.1 "ORA-600 [kclwcrs_15]"
Note 76434.1 "ORA-600 [kcoapl_blkchk]"
Note 248718.1 "ORA-600 [kcratr1_lostwrt]"


ORA-600 Errors kd to kw
=====================================

Note 139037.1 "ORA-600 [kdddgb2]"
Note 139042.1 "ORA-600 [kdisle:nrows]"
Note 139051.1 "ORA-600 [kghalo2]"
Note 139052.1 "ORA-600 [kghasp1]"
Note 139066.1 "ORA-600 [kghxhdr1]"
Note 229809.1 "ORA-600 [kgliep_1]"
Note 66501.1 "ORA-600 [kkrqtab2]"
Note 139095.1 "ORA-600 [kkslgop1]"
Note 139116.1 "ORA-600 [kohdtf048]"
Note 264061.1 "ORA-600 [kqludp2]"
Note 139162.1 "ORA-600 [kssrmp1]"
Note 247822.1 "ORA-600 [ksmals]"
Note 139153.1 "ORA-600 [ksmguard2]"
Note 233864.1 "ORA-600 [kteuproptime-2]"
Note 139180.1 "ORA-600 [ktsgsp5]"
Note 139193.1 "ORA-600 [kttmupd_segment-]"
Note 228480.1 "ORA-600 [kwqitnmptme:read]"
Note 228480.1 "ORA-600 [kwqitnmptme:ready]"
Note 228364.1 "ORA-600 [kwqitnmptme:wait]"


ORA-600 Errors q to z
=====================================

Note 248095.1 "ORA-600 [qctcte1]"
Note 216273.1 "ORA-600 [qctstc2o1]"
Note 209363.1 "ORA-600 [qerrmofbu1]"
Note 237598.1 "ORA-600 [qertqtableallocate2]"
Note 226887.1 "ORA-600 [qkagby4]"
Note 222876.1 "ORA-600 [qmxiunppacked2]"
Note 244365.1 "ORA-600 [rworupo.1]"
Note 139263.1 "ORA-600 [srsget_1]"
Note 260951.1 "ORA-600 [ttcgcshnd]"
Note 216452.1 "ORA-600 [ttcgcshnd-1]"
Note 216453.1 "ORA-600 [ttcgcshnd-2]"

Wednesday 11 December 2013

Steps for performing database cloning using hot backup


 Below steps helps you in performing database cloning using hot backup
...

Assumptions:

1. directory structure is different in both source and target servers
2. Oracle version : 10.2.0.5
3. OS version : Linux 5
4. target database name is same as source database name
5. DATABASE SHOULD BE IN ARCHIVEMODE

step 1 : Take the hot backup of source database

If the souce database in not in Archivemode, alter database to archivemode.

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE OPEN;

sql> alter database begin backup;
 
Check the status whether the datafiles are in backup mode.
select * from v$backup;
FILE# STATUS CHANGE# TIME
===========================
1 ACTIVE 000007 22-DEC-11
2 ACTIVE 000007 22-DEC-11

 
 
 $ copy datafiles to backup location

select name from v$datafile;
NAME
================================================
/oradata/test123/system01.dbf
/oradata/test123/undotbs01.dbf
SQL>exit
$ cd /u04/app/oracle/product/10.2.0/oradata/test/
$ ls -lrt
-rw-r-- 1 oracle staff 209723392 DEC 22 10:00 undotbs01.dbf
-rw-r-- 1 oracle staff 429924352 DEC 22 10:00 system01.dbf
$ cp *.dbf /oradata/clonedb/data

 sql> alter database end backup;

step 2 : Take controlfile trace and pfile or spfile (that was using by the source database)

step 3 : Install Oracle software on another machine (choose “Install only” option in OUI). Don’t create any database

step 4 : Copy all the files (including trace file and pfile or spfile) from source server to target server either using FTP or rcp
Note: Not necessary in copying control files and redologfiles

step 5 : Place pfile or spfile in “dbs” directory on target

step 6 : Copy the remaining files to their respective locations (If any directories are missing, do create them)

step 7 : Connect as sysdba and Startup the database in nomount stage

step 8 : Edit the trace file (that was copied) and generate a create controlfile script from it.
Modify the script and specify the new locations of the files.

step 9 : Execute the controlfile script which will create controlfiles in the location specified in CONTROL_FILES parameter.
Once control files are created, database will be forwarded to MOUNT state.

sql> @create_controlfile.sql

step 10 : Finally, Open the database with resetlogs option

sql> startup nomount

sql> alter database mount;

sql> recover database using backup controlfile until cancel;

sql> alter database open resetlogs;



Tuesday 3 December 2013

ERROR 1133 (42000): Can't find any matching row in the user table

Error :--
 
mysql>grant all privileges on *.* to root@'%' with grant option;
ERROR 1133 (42000): Can't find any matching row in the user table
 
 
Solution:--
 
Make root user as password protected..
 
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('root123');
 
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root123' WITH GRANT OPTION;
Flush privileges;