PRKP-1001 and CRS-0215 while starting 10g RAC database on 11gr2 cluster using srvctl

Recently we created 10g RAC database on 11gR2 cluster but could not able to start database with srvctl altough we can using sqlplus. Solution is to copy tnsnames.ora and listener.ora files from TNS_ADMIN location to ORACLE_HOME/network/admin:

problem:
srvctl start instance -d MYDB -i MYINST
PRKP-1001 : Error starting instance MYINST on node mynode
CRS-0215: Could not start resource 'ora.MYDB.MYINST.inst'.

Solution:
cp $TNS_ADMIN/listener.ora $ORACLE_HOME/network/admin/listener.ora
cp $TNS_ADMIN/tnsnames.ora $ORACLE_HOME/network/admin/tnsnames.ora

Using fked to restore ASM Disk Header (test)

Recently i tested kfed for restoring ASM disk header corruption. Here are the steps:


1-) Create a test Diskgroup,  test tablespace and test table
CREATE DISKGROUP TEST EXTERNAL REDUNDANCY
DISK
'/dev/rhdisk6',
'/dev/rhdisk7'
ATTRIBUTE
'compatible.asm' = '11.2',
'compatible.rdbms' = '11.2';

CREATE TABLESPACE TEST_TS DATAFILE '+TEST' SIZE 100M AUTOEXTEND ON NEXT 125M MAXSIZE 10240M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

create table SCOTT.TEST_TABLE (a int, b varchar2(1000)) tablespace TEST_TS;
insert into SCOTT.TEST_TABLE values (1, 'asddghmsddfuh');
insert into SCOTT.TEST_TABLE values (2, 'asddghmsddfuh');
insert into SCOTT.TEST_TABLE values (3,'sdfghfasdfgywbefg');

2-) Check ASM
SQL> select GROUP_NUMBER, NAME, ALLOCATION_UNIT_SIZE, TYPE, TOTAL_MB-FREE_MB USED_MB, TOTAL_MB, COMPATIBILITY, DATABASE_COMPATIBILITY from v$asm_diskgroup;
GROUP_NUMBER NAME                           ALLOCATION_UNIT_SIZE TYPE      USED_MB   TOTAL_MB COMPATIBIL DATABASE_C
------------ ------------------------------ -------------------- ------ ---------- ---------- ---------- ----------
           1 DATACRS                                     1048576 EXTERN       2123      10240 11.2.0.0.0 10.1.0.0.0
           2 TEST                                        1048576 EXTERN        163      10240 11.2.0.0.0 11.2.0.0.0

SQL> select GROUP_NUMBER, HEADER_STATUS, PATH,NAME, LIBRARY, FAILGROUP_TYPE,  trunc(OS_MB/1024*1024*1024/1024/1024) SUM_OS from V$ASM_DISK;
GROUP_NUMBER HEADER_STATU PATH                           NAME                           LIBRARY              FAILGRO     SUM_OS
------------ ------------ ------------------------------ ------------------------------ -------------------- ------- ----------
           0 CANDIDATE    /dev/rhdisk9                                                  System               REGULAR          5
           0 CANDIDATE    /dev/rhdisk8                                                  System               REGULAR          5
           1 MEMBER       /dev/rhdisk2                   DATACRS_0000                   System               REGULAR          5
           1 MEMBER       /dev/rhdisk3                   DATACRS_0001                   System               REGULAR          5
           2 MEMBER       /dev/rhdisk6                   TEST_0000                      System               REGULAR          5
           2 MEMBER       /dev/rhdisk7                   TEST_0001                      System               REGULAR          5

2-) Corrupt disk hdisk6 (with root):

node1
lquerypv -h /dev/rhdisk6
00000000   00820101 00000000 80000000 B8CC7719  |..............w.|
00000010   00000000 00000000 00000000 00000000  |................|
00000020   4F52434C 4449534B 00000000 00000000  |ORCLDISK........|
00000030   00000000 00000000 00000000 00000000  |................|
00000040   0B200000 00000103 54455354 5F303030  |. ......TEST_000|
00000050   30000000 00000000 00000000 00000000  |0...............|
00000060   00000000 00000000 54455354 00000000  |........TEST....|
00000070   00000000 00000000 00000000 00000000  |................|
00000080   00000000 00000000 54455354 5F303030  |........TEST_000|
00000090   30000000 00000000 00000000 00000000  |0...............|
000000A0   00000000 00000000 00000000 00000000  |................|
000000B0   00000000 00000000 00000000 00000000  |................|
000000C0   00000000 00000000 01F6B0CB 6422B400  |............d"..|
000000D0   01F6B0CB 64C47000 02001000 00100000  |....d.p.........|
000000E0   0001BC80 00001400 00000002 00000001  |................|
000000F0   00000002 00000002 00000000 00000000  |................|
#stop crs before. Other case ASM will protect disk from corruption
./crsctl stop crs  
chdev -l hdisk6 -a pv=yes
lquerypv -h /dev/rhdisk6 #corrupted!!!
00000000   C9C2D4C1 00000000 00000000 00000000  |................|
00000010   00000000 00000000 00000000 00000000  |................|
00000020   00000000 00000000 00000000 00000000  |................|
00000030   00000000 00000000 00000000 00000000  |................|
00000040   00000000 00000000 00000000 00000000  |................|
00000050   00000000 00000000 00000000 00000000  |................|
00000060   00000000 00000000 00000000 00000000  |................|
00000070   00000000 00000000 00000000 00000000  |................|
00000080   00C2E250 BB2CE1C7 00000000 00000000  |...P.,..........|
00000090   00000000 00000000 00000000 00000000  |................|
000000A0   00000000 00000000 00000000 00000000  |................|
000000B0   00000000 00000000 00000000 00000000  |................|
000000C0   00000000 00000000 00000000 00000000  |................|
000000D0   00000000 00000000 00000000 00000000  |................|
000000E0   00000000 00000000 00000000 00000000  |................|
000000F0   00000000 00000000 00000000 00000000  |................|

3-) TEST DG seem to be unmounted. Try to mount it:

ASMCMD> mount TEST
ORA-15032: not all alterations performed
ORA-15017: diskgroup "TEST" cannot be mounted
ORA-15063: ASM discovered an insufficient number of disks for diskgroup "TEST" (DBD ERROR: OCIStmtExecute)

SQL> select * from SCOTT.TEST_TABLE ;
select * from SCOTT.TEST_TABLE
                    *
ERROR at line 1:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '+TEST/mydb/datafile/test_ts.256.737033885'

4-) Restore disk header using kfed (with root)
kfed REPAIR /dev/rhdisk6
#now it seems to be ok!
lquerypv -h /dev/rhdisk6
00000000   00820101 00000000 80000000 B8CC7719  |..............w.|
00000010   00000000 00000000 00000000 00000000  |................|
00000020   4F52434C 4449534B 00000000 00000000  |ORCLDISK........|
00000030   00000000 00000000 00000000 00000000  |................|
00000040   0B200000 00000103 54455354 5F303030  |. ......TEST_000|
00000050   30000000 00000000 00000000 00000000  |0...............|
00000060   00000000 00000000 54455354 00000000  |........TEST....|
00000070   00000000 00000000 00000000 00000000  |................|
00000080   00000000 00000000 54455354 5F303030  |........TEST_000|
00000090   30000000 00000000 00000000 00000000  |0...............|
000000A0   00000000 00000000 00000000 00000000  |................|
000000B0   00000000 00000000 00000000 00000000  |................|
000000C0   00000000 00000000 01F6B0CB 6422B400  |............d"..|
000000D0   01F6B0CB 64C47000 02001000 00100000  |....d.p.........|
000000E0   0001BC80 00001400 00000002 00000001  |................|
000000F0   00000002 00000002 00000000 00000000  |................|

5-) Try to mount again:

ASMCMD> mount TEST #ok!!!

6-) Now do some tests (everything ok)
CREATE TABLESPACE TEST_TS2 DATAFILE '+TEST' SIZE 100M AUTOEXTEND ON NEXT 125M MAXSIZE 10240M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
create table SCOTT.TEST_TABLE2 (a int, b varchar2(1000)) tablespace TEST_TS;

insert into SCOTT.TEST_TABLE2 values (4, 'asddghmsddfuh');
insert into SCOTT.TEST_TABLE2 values (5, 'asddghmsddfuh');
insert into SCOTT.TEST_TABLE2 values (6,'sdfghfasdfgywbefg');
drop table SCOTT.TEST_TABLE;
drop tablespace TEST_TS;
alter diskgroup TEST add disk '/dev/rhdisk8','/dev/rhdisk9' drop disk 'TEST_0000','TEST_0001';


SQL> select GROUP_NUMBER, HEADER_STATUS, PATH,NAME, LIBRARY, FAILGROUP_TYPE,  trunc(OS_MB/1024*1024*1024/1024/1024) SUM_OS from V$ASM_DISK;
GROUP_NUMBER HEADER_STATU PATH                           NAME                           LIBRARY              FAILGRO     SUM_OS
------------ ------------ ------------------------------ ------------------------------ -------------------- ------- ----------
           0 FORMER       /dev/rhdisk6                                                  System               REGULAR          5
           0 FORMER       /dev/rhdisk7                                                  System               REGULAR          5
           1 MEMBER       /dev/rhdisk2                   DATACRS_0000                   System               REGULAR          5
           1 MEMBER       /dev/rhdisk3                   DATACRS_0001                   System               REGULAR          5
           2 MEMBER       /dev/rhdisk8                   TEST_0002                      System               REGULAR          5
           2 MEMBER       /dev/rhdisk9                   TEST_0003                      System               REGULAR          5

CRS-0222 and CRS-2640 while removing RAC datagourp

Recently I tried to drop a datagroup from RAC cluster. It was previously registered with cluster while creating a RAC database:

HA Resource                                   Target     State
ora.FRA.dg                                    ONLINE     OFFLINE

I moved all database files from this datagroup to an other. Then when i tried to drop it, following error occured:

srvctl remove diskgroup -g FRA
PRCA-1002 : Failed to remove CRS resource ora.FRA.dg for ASM Disk Group FRA
PRCR-1028 : Failed to remove resource ora.FRA.dg
PRCR-1072 : Failed to unregister resource ora.FRA.dg
CRS-0222: Resource 'ora.FRA.dg' has dependency error.

The right thing was to remove datagroup as told in here.
1-) srvctl modify database -d MYDB -a “DATA,OTHER_DG,...”
2-) srvctl disable diskgroup -g FRA
3-) srvctl remove diskgroup -g FRA –f

However, I removed datagroup first:

srvctl remove diskgroup -g FRA -f

Then, when i tried to open database, It gave me fallowing error:

srvctl start database -d mydb
PRCR-1079 : Failed to start resource ora.mydb.db
CRS-2640: Required resource 'ora.FRA.dg' is missing.

That was because altough i removed datagroup with force option, dependency was remained. You can see it with similar command:

./crsctl status resource ora.mydb.db -f|grep FRA
START_DEPENDENCIES=hard(ora.DATA.dg,ora.FRA.dg) weak(type:ora.listener.type,global:type:ora.scan_listener.type,uniform:ora.ons,uniform:ora.eons) pullup(ora.DATA.dg)
STOP_DEPENDENCIES=hard(intermediate:ora.asm,shutdown:ora.DATA.dg,shutdown:ora.FRA.dg)

I run fallowing command to remove FRA dependency:

srvctl modify database -d MYDB -a “DATA”  (step 1)
When i check dependency again, i saw that FRA was gone:

./crsctl status resource ora.mydb.db -f|grep FRA
(nothing)

./crsctl status resource ora.mydb.db -f|grep DATA
START_DEPENDENCIES=hard(ora.DATA.dg) weak(type:ora.listener.type,global:type:ora.scan_listener.type,uniform:ora.ons,uniform:ora.eons) pullup(ora.DATA.dg)
STOP_DEPENDENCIES=hard(intermediate:ora.asm,shutdown:ora.DATA.dg)

After that, i could able to open my database

srvctl start database -d mydb

How to Copy Files in ASM

cp command in ASMCMD actually not copies system generated the file but creates an alias for real file. Here is a procedure to copy system generated file (redo log) from one asm datagroup to an other:

1-) Login a database which has access to asm disk groups

2-) Create following directories

CREATE OR REPLACE DIRECTORY SOURCE_DIR AS '+DG1/DBNAME/ONLINELOG';
CREATE OR REPLACE DIRECTORY DEST_DIR AS '/..filesystem_path../ONLINELOG';

3-) Copy asm file to filesystem

BEGIN
  dbms_file_transfer.copy_file(source_directory_object =>
  'SOURCE_DIR', source_file_name => 'filename',
  destination_directory_object => 'DEST_DIR',
  destination_file_name => 'filename');
END;
/

4-) Copy file on filesystem back to asm

CREATE OR REPLACE DIRECTORY SOURCE_DIR AS '/..filesystem_path../ONLINELOG';
CREATE OR REPLACE DIRECTORY DEST_DIR AS '+DG2/DBNAME/ONLINELOG';
BEGIN
  dbms_file_transfer.copy_file(source_directory_object =>
  'SOURCE_DIR', source_file_name => 'filename',
  destination_directory_object => 'DEST_DIR',
  destination_file_name => 'new_filename');
END;
/

AIX: ASM Metadata & DISKS

lspv: command used to get disk list (disk, assigned PVID, vg, status)


$lspv
hdisk0          00c2e2503cddd971                    rootvg          active
hdisk9          00c2e2508b71c3a9                    sw_vg           active
hdisk1          none                                None          
hdisk2          none                                None          
hdisk3          none                                None          
...

ASM disks should not have PVID assigned to them (Configuring Storage for Grid Infrastructure, 3.3.3 Configuring Disk Devices for Oracle ASM, step 7). They should be removed with fallowing command before assigned to ASM:

$/usr/sbin/chdev -l hdiskn -a pv=clear

Note: If you already assigned disks without clearing PVID, don't clear it using above command. It corrupts asm disks while clearing header. Because it is the same header where asm keeps its internal info. Actually ASM clears header and PVID and writes its own header when a disk assigned to a datagroup. However because OS odm (aix's internal database) is not updated, you can see PVIDs using lspv although it is not there.

By using following command you can query disk header in order to see ASM internal information written on disk (requires root permission)


$lquerypv -h /dev/rhdisk5
00000000 00820101 00000000 80000000 81AA935B |...............[|
00000010 00000000 00000000 00000000 00000000 |................|
00000020 4F52434C 4449534B 00000000 00000000 |ORCLDISK........|
00000030 00000000 00000000 00000000 00000000 |................|
00000040 0A100000 00000103 54455354 5F303030 |........TEST_000|
00000050 30000000 00000000 00000000 00000000 |0...............|
00000060 00000000 00000000 54455354 00000000 |........TEST....|
00000070 00000000 00000000 00000000 00000000 |................|
00000080 00000000 00000000 54455354 5F303030 |........TEST_000|


In 11g you can backup and recover corrupted disk headers using md_backup and md_restore. The corrupted diskgroup must be specified with the ‘-g’ flag for the restore to complete
successfully:

ASMCMD> md_restore -b /oracle/backup/asm_metadata020409.bkp -g 'TEST'

Ref:
11gRAC_ASM_1.pdf (doc link)

How to Drop ASM Datagroup while not mounted and disks corrupted

In a test environment we accidentally corrupt asm disks and reset their headers. When I query V$ASM_DISK view, I saw that disks were discovered as "CANDIDATE". It was like newly added disks available for ASM. The problem was, my datagroup which was using these disks, was exists but i could not drop it. That was because a diskgroup has to be mounted in order to be dropped, but its disks were corrupted so i could not mount it:

SQL> drop diskgroup DATA;
drop diskgroup DATA
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15001: diskgroup "DATA" does not exist or is not mounted


SQL> ALTER DISKGROUP DATA mount;
ALTER DISKGROUP DATA mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15017: diskgroup "DATA" cannot be mounted
ORA-15063: ASM discovered an insufficient number of disks for diskgroup "DATA"

How can i drop/delete datagroup which i can not mount?

Solution is to create a new datagroup, add all disks to new datagroup. At the end, old datagroup disappears... Then you may drop newly added diskgroup.

create diskgroup DATA_DROP external redundancy disk '/dev/rhdisk#' force;
alter diskgroup DATA_DROP add disk '/dev/rhdisk#';
...
DATA disappears when i add all its disks to DATA_DROP...
...
drop diskgroup DATA_DROP including contents;

now, DATA diskgroup has gone and all its disks are in "FORMER" status.

How to get process path on linux

$ps -eaf | grep pmon


oracle 24771 1 0 Aug14 ? 00:02:59 ora_pmon_INST1
oracle 28486 1 0 Sep05 ? 00:04:00 ora_pmon_INST2

$readlink -f /proc/24771/exe
/.../bin/oracle
$readlink -f /proc/28486/exe
/.../bin/oracle

SCAN IP Configuration

In order to display scan configuration of RAC cluster:

$srvctl config scan

SCAN name: my-scan.mydomain.com.tr, Network: X/XXX.XXX.XXX.XXX/XXX.XXX.XXX.XXX/en0
SCAN VIP name: scan1, IP: /XXX.XXX.XXX.XXX/XXX.XXX.XXX.XXX
SCAN VIP name: scan2, IP: /XXX.XXX.XXX.XXX/XXX.XXX.XXX.XXX
SCAN VIP name: scan3, IP: /XXX.XXX.XXX.XXX/XXX.XXX.XXX.XXX

AIX: list top 10 processes consume memory

ps aux | head -1; ps aux | sort -rn +5 | head -10

AIX mount-point hdisk information

In order to find relations between mount point and disks follow this procedure:
1-) Find logical volume groups:
lsvg
2-) Find which mount points are belong to which logical volume:
lsvg -l {volume group}
3-) Find which hdisks are belong to logical volume
lslv -l {logical volume}
4-) Finally find storage serial and other information
lscfg -vl {hdisk}

11GR2: Creating Physical Standby on ASM usign Active Database Duplicate

You can follow official documents:
e10642.pdf: Backup and Recovery User's Guide  page:24-1 "Duplicating a Database"
e10700.pdf: Data Guard Concepts and Administration  page: F-4 "Using the DUPLICATE Command to Create a Standby Database"
e10643.pdf: "Backup and Recovery Reference "

Prepare Auxiliary database

#Create Password File for the Auxiliary Instance
$orapwd file=$ORACLE_HOME/dbs/orapwMYSID password=xxxx entries=5

#Create a static listener (single-instance)

LISTENER=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS = (PROTOCOL = TCP)(HOST = myserver)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = myserverip)(PORT = 1521)(IP = FIRST))
))
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /oracle_home_path)
(SID_NAME = MYSID)
))

#Create necessary folders and prepare paths for new database:
ORACLE_BASE
ORACLE_BASE\DBNAME
ORACLE_BASE\DBNAME\adump
ORACLE_BASE\DBNAME\dpdump
ORACLE_BASE\DBNAME\hdump
ORACLE_BASE\DBNAME\pfile
ORACLE_BASE\DBNAME\scripts

Normally documents say that you need to create a pfile including only db_name parameter at Auxiliary site. Then you can start instance with nomount mode and Auxiliary configuration is done. However, at later steps you may have troupble with rman dublicate command including SPFILE configuration parameters (like me):
 
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
SPFILE
....
SET "db_unique_name"="..."
SET DB_CREATE_FILE_DEST "+ASMDISK";

An other choice, you can manually create Auxiliary spfile and set all spfile parameters here instead of rman duplicate command. Later we will use only command to create standby....
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
Here is my previously created pfile at Auxiliary database:

MYDBNAME.__db_cache_size=XXXXXXXX
MYDBNAME.__java_pool_size=XXXXXXXX
MYDBNAME.__large_pool_size=XXXXXXXX
MYDBNAME.__oracle_base='/oracle_base_path'#ORACLE_BASE set from environment
MYDBNAME.__pga_aggregate_target=XXXXXXXX
MYDBNAME.__sga_target=XXXXXXXX
MYDBNAME.__shared_io_pool_size=0
MYDBNAME.__shared_pool_size=XXXXXXXX
MYDBNAME.__streams_pool_size=XXXXXXXX
*.audit_file_dest='/adump_path'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/db_ctrl_path/ctrl1.ctl','/db_ctrl_path/ctrl2.ctl'
*.db_block_size=XXXXXXXX
*.db_create_file_dest='+ASMDISK'
*.db_domain=''
*.db_file_multiblock_read_count=XXXXXXXX
*.db_name='MYDBNAME'
*.db_recovery_file_dest='+ASMRECODISK'
*.db_recovery_file_dest_size=XXXXXXXX
*.db_unique_name='MYDBNAME'
*.diagnostic_dest='/oracle_base_path'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=MYDBNAME2XDB)'
*.memory_max_target=XXXXXXXX
*.memory_target=XXXXXXXX
*.open_cursors=XXXXXXXX
*.processes=XXXXXXXX
*.remote_login_passwordfile='EXCLUSIVE'
*.result_cache_max_size=XXXXXXXX
*.service_names='MYDBNAME',...
*.sessions=XXXXXXXX
*.sort_area_size=XXXXXXXX
*.undo_retention=XXXXXXXX
*.undo_tablespace='MYUNDOTS'

Note: I dont suggest using "db_file_name_convert" or "log_file_name_convert" parameters. If you use them both with db_create_file_dest parameter, then datafiles and logfiles are created at paths "{db_create_file_dest}/{db_unique_name}/DATAFILE", "{db_create_file_dest}/{db_unique_name}/ONLINELOG" and aliases are created at paths "{db_file_name_convert}", "{log_file_name_convert}".
Note: Because db_unique_name is used as a template parameter for asm paths, chose it wisely. Then It will be difficult to move them and rename in controlfile. I suggest use DBNAME as unique name. However both primary and standby can not have same db_unique_name so decide what to set which. If you decide to change db_unique_name of the primary, it resuires restart...

When your pfile is ready, first startup instance with nomount option using this pfile and then create spfile. Then shutdown, startup again with nomount option, this time using spfile.

Prepare Source database and run RMAN DUPLICATE

Before beginning duplication, force database logging:

ALTER DATABASE FORCE LOGGING;

You need to configure sql net connections for both source and auxiliary databases. Here is the tnsnames.ora:

MYDBNAMEPROD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = prodhost)(PORT = 1521))
(CONNECT_DATA = (SID = MYSID))
))

MYDBNAMESTBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = stbyhost)(PORT = 1521))
(CONNECT_DATA =(SERVER = DEDICATED)(SID = MYSID))
)

You should be able to connect both instance with sys as sysdba through sql.net:

sqlplus /nolog
SQL> connect SYS@MYDBNAMEPROD as sysdba
SQL> connect SYS@MYDBNAMESTBY as sysdba

If you could not connect, you should correct this before proceeding.

Now connect rman and start duplicate:

$rman
RMAN> CONNECT TARGET SYS@MYDBNAMEPROD
target database Password:
connected to target database: MYDBNAME (DBID=XXXXXX)

RMAN> CONNECT AUXILIARY SYS@MYDBNAMESTBY
auxiliary database Password:
connected to auxiliary database: MYDBNAME (not mounted)

RMAN> DUPLICATE TARGET DATABASE
2> FOR STANDBY
3> FROM ACTIVE DATABASE
4> ;
....

Note: If datafiles are created with aliases and you decide to remove aliases, dont use "rm" command of asmcmd. It will remove both alias and file itself. Instead, use "rmalias". I removed a file accidently by removing alias with rm command and could not find a way to get that single datafile from source directly into auxillary asm. If you have a filesystem drive at auxillary big enough to hold that datafile, then there seems a way...

Prepare Dataguard Configuration

Put standby control files into ASM:

First of all, prepare paths for controlfiles. If necessary, create asm directories.

SQL> shutdown immediate

ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount

ORACLE instance started.
Total System Global Area 2.6724E+10 bytes
Fixed Size 2219408 bytes
Variable Size 1.2885E+10 bytes
Database Buffers 1.3824E+10 bytes
Redo Buffers 12148736 bytes
SQL> exit

$rman target /

RMAN> restore controlfile to '+ASMDISK' from '/db_ctrl_path/ctrl1.ctl';
RMAN> restore controlfile to '+ASMRECODISK' from '/db_ctrl_path/ctrl2.ctl';

Check that new control files are created at paths:
+ASMDISK/MYDBNAME/CONTROLFILE
+ASMRECODISK/MYDBNAME/CONTROLFILE


Go to asm and find out newly created control files:
+ASMDISK/MYDBNAME/CONTROLFILE/current.XXX.XXXXXXXXX

+ASMRECODISK/MYDBNAME/CONTROLFILE/current.XXX.XXXXXXXXX

Then update control_files and mount database:

sqlplus '/ AS SYSDBA' 
SQL> alter system set control_files='+ASMDISK/MYDBNAME/CONTROLFILE/current.XXX.XXXXXXXXX',
'+ASMRECODISK/MYDBNAME/CONTROLFILE/current.XXX.XXXXXXXXX' scope=spfile;
SQL> startup mount


Follow this procedure to setup dataguard:
http://erkanorclog.blogspot.com/2008/03/setting-up-dataguard-configuration-for.html

Configure dynamic (local) listener:

You may want to register standby with local listener :

ALTER SYSTEM SET LOCAL_LISTENER='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mystbyhost)(PORT=1521))))' scope=both;

Check and see if local listener registration is ok. Change "MYDBNAMESTBY" tns at primary side to use this new listener configuration. If it is ok and you see dataguard is working fine then you can unset static listener configuration created previously.

Using Sql loader (sqlldr.exe)

Here is an example of using sql loader to load data from flat file to oracle table. In order to solve input-client time format conflicts, time format for timestamp columns specified manually in parameter file.

C:\>set NLS_LANG=TURKISH_TURKEY.TR8MSWIN1254
C:\>sqlldr myuser@mydb control=loader.ctl

loader.ctl:
load data

infile 'myinputfile'
into table MYSCHEMA.MYTABLE
fields terminated by "\t" OPTIONALLY ENCLOSED BY '"'
(COLUMN1, TIMESTAMP_COLUMN TIMESTAMP "DDMONYYYY:HH24:MI:SS", COLUMN2, COLUMN3...)

How to change DBNAME & SID without changing DBID

This procedure does not require resetlogs so previously taken backups will be valid after this operation. However database restart is needed at some points so maintenance with downtime should be scheduled for this operation.

You may follow “Changing Only the Database Name”  topic at official document:

http://download.oracle.com/docs/cd/B14117_01/server.101/b10825/dbnewid.htm#i1004734

$cd $ORACLE_HOME/dbs
#backup old initora file
$mv initMYOLDSID.ora initMYOLDSID.ora.org  

#create pfile from spfile
$sqlplus '/ AS SYSDBA'
SQL> create pfile from spfile;
SQL> exit

#replace all old dbname parameters with new dbname in pfile. Becareful, if you change some path variables, make sure that new path (including new dbname) also exists on system. If not, create manually.
$vi initMYOLDSID.ora
vi=> :%s/MYOLDDBNAME/MYNEWDBNAME/g

vi=> :wq

#now database goes offline...
$sqlplus '/ AS SYSDBA'
SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP MOUNT
SQL> exit

#change dbname using nid. Output is similar to fallowing...
$nid TARGET=/ DBNAME=MYNEWDBNAME SETNAME=YES
DBNEWID: Release 11.2.0.1.0 - Production on ....


Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to database MYOLDDBNAME (DBID=XXXXXXXXX)

Connected to server version 11.2.0

Control Files in database:
/db_ctrl_path/ctrl1.ctl
/db_ctrl_path/ctrl2.ctl

Change database name of database MYOLDDBNAME to MYNEWDBNAME? (Y/[N]) => Y

Proceeding with operation
Changing database name from MYOLDDBNAME to MYNEWDBNAME
Control File /db_ctrl_path/ctrl1.ctl - modified
Control File /db_ctrl_path/ctrl2.ctl - modified
Datafile /db_data_path/file1.db - wrote new name
Datafile /db_data_path/file2.db - wrote new name
Datafile /db_data_path/file3.db - wrote new name
...
Control File /db_ctrl_path/ctrl1.ctl - wrote new name
Control File /db_ctrl_path/ctrl2.ctl - wrote new name
Instance shut down

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

#Change ORACLE_SID environment variable
$vi $HOME/.profile
vi=> change ORACLE_SID from MYOLDSID to MYNEWSID
$. $HOME/.profile

$env|grep SID
ORACLE_SID=MYNEWSID

#Create new password file and rename pfile..
$orapwd file=$ORACLE_HOME/dbs/orapwMYNEWSID password=xxxx entries=5

$mv initMYOLDSID.ora initMYNEWSID.ora

#Start database with new pfile and create spfile.
$sqlplus '/ AS SYSDBA'
SQL> startup pfile=oracle_home_path/dbs/initMYNEWSID.ora
ORACLE instance started.
…
SQL> select name from v$database;
NAME
---------
MYNEWDBNAME

SQL> create spfile from pfile;

#Note that database is opened using pfile so no spfile operation will be allowed. You should restart database again without pfile parameter to open it with spfile.

ODI: connection to repository failed

If connection string for the repository database has been changed, you may face following error while connecting to repository altough you have changed url parameter of client tool:

java.sql.SQLException: Io Exception: The Network Adapter could not establish the connection
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:125)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:162)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:274)
at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:328)
at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:361)
at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:151)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:595)
at com.sunopsis.sql.SnpsConnection.u(SnpsConnection.java)
at com.sunopsis.sql.SnpsConnection.c(SnpsConnection.java)
at com.sunopsis.sql.h.run(h.java)

This is because, altough tool connects to repository successfully, it retrieves repository connection string from repository database and reconnects using it (or tries to open a new connection with it and fails). As a result, you can not connect and correct repository connection parameter.

I solved this problem by manually logging into repository database and updating repository connection  parameter:

select TXT from snp_mtxt_part where i_txt=( select I_TXT_JAVA_URL from SNP_CONNECT where CON_NAME='MY_PROD_WORK_REP_NAME')

update snp_mtxt_part set TXT='{correct repository connection string}' where i_txt=....;

Creating New 11gr2 database on existing 11gr2 RAC

It is assumed that 11gr2 cluster has been installed. ASM is configured and disks are prepared for new database.

1-) Create new OS user for new database. New user should be in the same group with grid's OS user (Using different os users for databases is optional, not a must)

2-) Set ssh trust between the nodes for the newly created os user:

From each node, logged in as new user:


mkdir ~/.ssh
chmod 755 ~/.ssh
/usr/bin/ssh-keygen -t rsa
/usr/bin/ssh-keygen -t dsa

Example:
$ mkdir ~/.ssh
$ chmod 755 ~/.ssh
$ /usr/bin/ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (.../.ssh/id_rsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:

Your identification has been saved in .../.ssh/id_rsa.
Your public key has been saved in .../.ssh/id_rsa.pub.
The key fingerprint is:
XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX os_user@my.domain.com

$ /usr/bin/ssh-keygen -t dsa
Generating public/private dsa key pair.
Enter file in which to save the key (.../.ssh/id_dsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:

Your identification has been saved in .../.ssh/id_dsa.
Your public key has been saved in .../.ssh/id_dsa.pub.
 The key fingerprint is:
XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX:XX os_user@my.domain.com

Append all public keys to authorized_keys of all nodes:

FIRST NODE

cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys
ssh os_user@node2 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
ssh os_user@node2 cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys
chmod 644 ~/.ssh/authorized_keys

SECOND NODE

cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys
ssh os_user@node1 cat ~/.ssh/id_rsa.pub >> ~/.ssh/authorized_keys
ssh os_user@node1 cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys
chmod 644 ~/.ssh/authorized_keys

ssh command run at previous step will register each node's ssh identity to the known_hosts file at other node (confirmation appeared when you run ssh first). However, every node should also has a know_hosts entry for itself. In order to do that, run following command at each node and answer yes at confirmation.

ssh os_user@nodeitself ls

Finally, check that each node does a ssh ls to orher node without requiring any password or confirmation.

ssh os_user@othernode ls
ssh os_user@nodeitself ls


3-) edit user profile and set following variables:
 
ORACLE_HOME

RDBMS_HOME
ASM_HOME
CRS_HOME
ORACLE_BASE
PATH
ORACLE_SID
LD_LIBRARY_PATH
4-) Check permisions of ORACLE HOME and BASE so new user has write permission on these (on both nodes)
5-) Set new environments paramaters on both nodes.
6-) Start Installer and install software only... Create database? maybe next time...
 

System Wide Stats Gathering

It is recommended to run gather statistics reqularly, especially after upgrades or running catalog scripts:

exec DBMS_STATS.GATHER_DICTIONARY_STATS();

exec DBMS_STATS.GATHER_FIXED_OBJECTS_STATS();


Also it is recommended to gather hardware based statistics after database first created or a hardware maintenance has been done

exec DBMS_STATS.gather_system_stats('start');
exec DBMS_STATS.gather_system_stats('stop');

You may query aux_stats$ system view to see system stat results:


select * from aux_stats$

Oracle 11GR2 SCAN, ORA-12502: TNS:listener received no CONNECT_DATA from client

While local_listener parameter of RAC database was something like this:

(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=node-vip)(PORT=1521))))

connecting to database using SCAN listener gave following error (after a short period of hang time)

ORA-12502: TNS:listener received no CONNECT_DATA from client

After setting local_listener as:

(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=node-vip.mycompany.com)(PORT=1521))))

problem resolved...

Oracle 11gR2 RAC and crs_stat

In Oracle 11gR2 RAC cluster, crs_stat does not show database instance (node,online,offline) information any more. In order to get this information, you should use srvctl commands...

example: mydb2 is offline at mynode2

$crs_stat -t   (no information at output)
Name Type Target State Host
------------------------------------------------------------
ora.DATA.dg ora....up.type ONLINE ONLINE mynode1
ora.DATA10G.dg ora....up.type ONLINE ONLINE mynode1
ora....ER.lsnr ora....er.type ONLINE ONLINE mynode1
ora....N1.lsnr ora....er.type ONLINE ONLINE mynode2
ora....N2.lsnr ora....er.type ONLINE ONLINE mynode1
ora....N3.lsnr ora....er.type ONLINE ONLINE mynode1
ora....TING.dg ora....up.type ONLINE ONLINE mynode1
ora.RECO.dg ora....up.type ONLINE ONLINE mynode1
ora.asm ora.asm.type ONLINE ONLINE mynode1
ora.eons ora.eons.type ONLINE ONLINE mynode1
ora....SM1.asm application ONLINE ONLINE mynode1
ora....A1.lsnr application ONLINE ONLINE mynode1
ora....ca1.gsd application OFFLINE OFFLINE
ora....ca1.ons application ONLINE ONLINE mynode1
ora....ca1.vip ora....t1.type ONLINE ONLINE mynode1
ora....SM2.asm application ONLINE ONLINE mynode2
ora....A2.lsnr application ONLINE ONLINE mynode2
ora....ca2.gsd application OFFLINE OFFLINE
ora....ca2.ons application ONLINE ONLINE mynode2
ora....ca2.vip ora....t1.type ONLINE ONLINE mynode2
ora.gsd ora.gsd.type OFFLINE OFFLINE
ora....network ora....rk.type ONLINE ONLINE mynode1
ora.oc4j ora.oc4j.type OFFLINE OFFLINE
ora.ons ora.ons.type ONLINE ONLINE mynode1
ora.mydb.db ora....se.type ONLINE ONLINE mynode1
ora.scan1.vip ora....ip.type ONLINE ONLINE mynode2
ora.scan2.vip ora....ip.type ONLINE ONLINE mynode1
ora.scan3.vip ora....ip.type ONLINE ONLINE mynode1


$srvctl status database -d mydb
Instance mydb1 is running on node mynode1
Instance mydb2 is not running on node mynode2

DUMP_CVS: Procedure to dump table data into csv

Here is a procedure which can be used to dump table data into csv format.

CREATE OR REPLACE PROCEDURE MYSCHEMA.dump_csv( p_query in varchar2, p_separator in varchar2 default ',', p_dir in varchar2 , p_filename in varchar2 )
is
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(2000);
l_status integer;
l_colCnt number default 0;
l_separator varchar2(10) default '';
l_cnt number default 0;
begin
l_output := utl_file.fopen( p_dir, p_filename, 'w' );

dbms_sql.parse( l_theCursor, p_query,
dbms_sql.native );

for i in 1 .. 255 loop
begin
dbms_sql.define_column( l_theCursor, i,
l_columnValue, 2000 );
l_colCnt := i;
exception
when others then
if ( sqlcode = -1007 ) then exit;
else
raise;
end if;
end;
end loop;

dbms_sql.define_column( l_theCursor, 1,
l_columnValue, 2000 );

l_status := dbms_sql.execute(l_theCursor);

loop
exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
l_separator := '';
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i,
l_columnValue );
utl_file.put( l_output,
l_separator || l_columnValue );
l_separator := p_separator;
end loop;
utl_file.new_line( l_output );
l_cnt := l_cnt+1;
end loop;
dbms_sql.close_cursor(l_theCursor);

utl_file.fclose( l_output );
dbms_output.put_line( to_char(l_cnt) ||' rows exported into '||p_filename||' file...' );
end dump_csv;
/

ORA-39126 and ORA-01017 while executing dbms_datapump import over database link

I hit this problem when executing DBMS_DATAPUMP IMPORT operation over database link. Here is the full error message:

Starting "SYS"."import_MYTASK":
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.POST_MT_INIT [SELECT SYS.KUPM$MCP.GET_ENDIANNESS@MYDBLINK FROM SYS.DUAL]
ORA-04052: error occurred when looking up remote object SYS.KUPM$MCP@MYDBLINK
ORA-00604: error occurred at recursive SQL level 3
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from MYDBLINK
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 8164
----- PL/SQL Call Stack -----
object line object
handle number name
70000058f38e5b8 19028 package body SYS.KUPW$WORKER
70000058f38e5b8 8191 package body SYS.KUPW$WORKER
70000058f38e5b8 2072 package body SYS.KUPW$WORKER
70000058f38e5b8 1438 package body SYS.KUPW$WORKER
70000058e31ea78 2 anonymous block
Job "SYS"."import_MYTASK" stopped due to fatal error at 09:33:35

Database link was working fine when i query manually. Restarting database solved the problem however i dont know what was the cause...

How to generate move/rebuild script to migrate partitioned tables from one tablespace to other

declare
cflag number;
oldDataTablespaceName varchar2(100);
newDataTablespaceName varchar2(100);
oldIndexTablespaceName varchar2(100);
newIndexTablespaceName varchar2(100);
parallel_level varchar2(2);
begin
DBMS_OUTPUT.ENABLE(1000000);
-------------------- SET VARIABLES ----------------------------
oldDataTablespaceName:=OLD_TABLESPACE';
newDataTablespaceName:='NEW_TABLESPACE';
newIndexTablespaceName:='NEW_INDEX_TABLESPACE';
parallel_level:='8';
---------------------------------------------------------------
dbms_output.put_line('alter session enable parallel ddl;');
for l in ( select table_owner, table_name, partition_name, partition_position from dba_tab_partitions where tablespace_name=oldDataTablespaceName order by table_owner, table_name, partition_position )
loop
cflag := 0;
dbms_output.put_line('alter table '||l.table_owner||'.'||l.table_name||' move partition "'||l.partition_name||'" tablespace '||newDataTablespaceName||' parallel '||parallel_level||';');
for k in (select dip.index_owner, dip.index_name, dip.partition_name from dba_ind_partitions dip, dba_indexes di where di.table_owner=l.table_owner and di.table_name=l.table_name and di.owner=dip.index_owner and di.index_name=dip.index_name and dip.partition_position=l.partition_position)
loop
dbms_output.put_line('alter index '||k.index_owner||'.'||k.index_name||' rebuild partition "'||k.partition_name||'" tablespace '||newIndexTablespaceName||' parallel '||parallel_level||';');
end loop;
end loop;
END;
/

Here is non-partitioned version:

declare
cflag number;
oldDataTablespaceName varchar2(100);
newDataTablespaceName varchar2(100);
oldIndexTablespaceName varchar2(100);
newIndexTablespaceName varchar2(100);
parallel_level varchar2(2);
begin
DBMS_OUTPUT.ENABLE(1000000);
-------------------- SET VARIABLES ----------------------------
oldDataTablespaceName:='OLD_TABLESPACE';
newDataTablespaceName:='NEW_TABLESPACE';
newIndexTablespaceName:='NEW_TABLESPACE';
parallel_level:='8';
---------------------------------------------------------------
dbms_output.put_line('alter session enable parallel ddl;');
for l in ( select owner, table_name from dba_tables where tablespace_name=oldDataTablespaceName order by 1,2 )
loop
cflag := 0;
dbms_output.put_line('alter table '||l.owner||'.'||l.table_name||' move tablespace '||newDataTablespaceName||' parallel '||parallel_level||';');
for k in (select di.owner, di.index_name from dba_indexes di where di.owner=l.owner and di.table_name=l.table_name)
loop
dbms_output.put_line('alter index '||k.owner||'.'||k.index_name||' rebuild tablespace '||newIndexTablespaceName||' parallel '||parallel_level||';');
end loop;
end loop;
END;
/

 

Recover Database with allow corruption

Here is an example of block corruption faced during database recovery:

recover database;

RA-00600: internal error code, arguments: [3020], [48], [41103361], [41103361], [], [], [], [], [], [], [], []

ORA-10567: Redo is inconsistent with data block (file# 48, block# 41103361)
ORA-10564: tablespace MYTS
ORA-01110: data file 48: '+MYDISC/mydb/datafile/myts.14714.699899641'
ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 323837
Errors in file /mytracepath/mydb_pr06_4728.trc:

You can skip recovery of this corrupted block by running following command:

recover database datafile '+MYDISC/mydb/datafile/myts.14714.699899641' allow 1 corruption;

If there are more then 1 corruption, you may repeat this step or run command with allow many corruption. However, dont forget to note corrupted file and block numbers. After opening database, we will use following query to find out to which segment corrupted block belogns to:

SELECT tablespace_name, segment_type, owner, segment_name

FROM dba_extents WHERE file_id = 48 and 41103361 between block_id AND block_id + blocks - 1;