Mani's Blog

December 29, 2011

ORACLE RAC: SINGLE CLIENT ACCESS NAME (SCAN)

Filed under: Database,Oracle,RAC — mani @ 4:17 am

Something that I learned off 11gR2 features.

http://www.oracle.com/technetwork/database/clustering/overview/scan-129069.pdf

December 19, 2011

Enterprise Manager 12c Overview – Cloud Control DB Lifecycle

Filed under: Database,Enterprise Manager,Oracle — mani @ 7:02 pm

Enjoy the presentation.

Enterprise_Manager12c_CloudControl_Overview_plus_DBLifeCycleMgmtPack

December 6, 2011

Restore READ ONLY Tablespace using RMAN

Filed under: Uncategorized — mani @ 11:11 pm

Restore READ ONLY Tablespace using RMAN

We have a database with one tablespace in readonly.   Ran into following issue while opening up the database.

Restoring the database:

RMAN> alter database mount;

database mounted

RMAN> run {
allocate channel d1 device type disk;
allocate channel d2 device type disk;
allocate channel d3 device type disk;
allocate channel d4 device type disk;
set until sequence= 11785;
restore database;
}
2> 3> 4> 5> 6> 7> 8>
allocated channel: d1
channel d1: sid=542 devtype=DISK

allocated channel: d2
channel d2: sid=1102 devtype=DISK

allocated channel: d3
channel d3: sid=537 devtype=DISK

allocated channel: d4
channel d4: sid=1099 devtype=DISK

executing command: SET until clause

Starting restore at 06-DEC-11
Starting implicit crosscheck backup at 06-DEC-11
Crosschecked 522 objects
Crosschecked 1 objects
Finished implicit crosscheck backup at 06-DEC-11

Starting implicit crosscheck copy at 06-DEC-11
Finished implicit crosscheck copy at 06-DEC-11

searching for all files in the recovery area
cataloging files…
cataloging done

List of Cataloged Files
=======================
File Name: /local/data/oracle/nfs/DATAMART/autobackup/2011_12_06/o1_mf_s_769191237_7fwht7qw_.bkp

datafile 7 not processed because file is read-only
channel d1: starting datafile backupset restore
channel d1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to +XN_DATA/dsp0001/datafile/xn_etl.259.663804029
restoring datafile 00008 to +XN_DATA/dsp0001/datafile/xn_fido.471.677889679
channel d1: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbo69_.bkp
channel d2: starting datafile backupset restore
channel d2: specifying datafile(s) to restore from backup set
restoring datafile 00002 to +XN_DATA/dsp0001/datafile/undotbs1.257.663802853
restoring datafile 00003 to +XN_DATA/dsp0001/datafile/sysaux.256.663802853
channel d2: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbnr7_.bkp
channel d3: starting datafile backupset restore
channel d3: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +XN_DATA/dsp0001/datafile/system.258.663802853
restoring datafile 00006 to +XN_DATA/dsp0001/datafile/xn_test_etl.260.663804035
channel d3: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbn7q_.bkp
channel d4: starting datafile backupset restore
channel d4: specifying datafile(s) to restore from backup set
restoring datafile 00004 to +XN_DATA/dsp0001/datafile/users.261.663802853
channel d4: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbmz3_.bkp
channel d4: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbmz3_.bkp tag=TAG20111205T235251
channel d4: restore complete, elapsed time: 00:00:01
channel d1: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbo69_.bkp tag=TAG20111205T235251
channel d1: restore complete, elapsed time: 00:00:26
channel d2: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbnr7_.bkp tag=TAG20111205T235251
channel d2: restore complete, elapsed time: 00:02:11
channel d3: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbn7q_.bkp tag=TAG20111205T235251
channel d3: restore complete, elapsed time: 00:16:17
Finished restore at 06-DEC-11
released channel: d1
released channel: d2
released channel: d3
released channel: d4

Recovering database:

RMAN> recover database;

Starting recover at 06-DEC-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=536 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=1098 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=535 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=534 devtype=DISK
datafile 7 not processed because file is read-only

starting media recovery

channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_2: starting archive log restore to default destination
channel ORA_DISK_3: starting archive log restore to default destination
channel ORA_DISK_4: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=11784
channel ORA_DISK_1: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7ftqks7j_.bkp
channel ORA_DISK_2: restoring archive log
archive log thread=1 sequence=11785
channel ORA_DISK_2: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7ftynqqx_.bkp
channel ORA_DISK_3: restoring archive log
archive log thread=1 sequence=11786
channel ORA_DISK_3: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fv5o1fd_.bkp
channel ORA_DISK_4: restoring archive log
archive log thread=1 sequence=11787
channel ORA_DISK_4: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fvdmzwf_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7ftqks7j_.bkp tag=BKP_DATAMART_ARCHL_120611121301
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=/arch/DATAMART/5641608e-0000011784_0001_697357424.oarl thread=1 sequence=11784
channel ORA_DISK_2: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7ftynqqx_.bkp tag=BKP_DATAMART_ARCHL_120611021300
channel ORA_DISK_2: restore complete, elapsed time: 00:00:03
archive log filename=/arch/DATAMART/5641608e-0000011785_0001_697357424.oarl thread=1 sequence=11785
channel ORA_DISK_3: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fv5o1fd_.bkp tag=BKP_DATAMART_ARCHL_120611041302
channel ORA_DISK_3: restore complete, elapsed time: 00:00:04
archive log filename=/arch/DATAMART/5641608e-0000011786_0001_697357424.oarl thread=1 sequence=11786
channel ORA_DISK_4: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fvdmzwf_.bkp tag=BKP_DATAMART_ARCHL_120611061301
channel ORA_DISK_4: restore complete, elapsed time: 00:00:05
archive log filename=/arch/DATAMART/5641608e-0000011787_0001_697357424.oarl thread=1 sequence=11787
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_2: starting archive log restore to default destination
channel ORA_DISK_3: starting archive log restore to default destination
channel ORA_DISK_4: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=11788
channel ORA_DISK_1: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fvmo3cv_.bkp
channel ORA_DISK_2: restoring archive log
archive log thread=1 sequence=11789
channel ORA_DISK_2: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fvtp2hs_.bkp
channel ORA_DISK_3: restoring archive log
archive log thread=1 sequence=11790
channel ORA_DISK_3: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fw1q1d4_.bkp
channel ORA_DISK_4: restoring archive log
archive log thread=1 sequence=11791
channel ORA_DISK_4: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fw8r13r_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fvmo3cv_.bkp tag=BKP_DATAMART_ARCHL_120611081305
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=/arch/DATAMART/5641608e-0000011788_0001_697357424.oarl thread=1 sequence=11788
channel ORA_DISK_2: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fvtp2hs_.bkp tag=BKP_DATAMART_ARCHL_120611101303
channel ORA_DISK_2: restore complete, elapsed time: 00:00:03
archive log filename=/arch/DATAMART/5641608e-0000011789_0001_697357424.oarl thread=1 sequence=11789
channel ORA_DISK_3: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fw1q1d4_.bkp tag=BKP_DATAMART_ARCHL_120611121302
channel ORA_DISK_3: restore complete, elapsed time: 00:00:03
archive log filename=/arch/DATAMART/5641608e-0000011790_0001_697357424.oarl thread=1 sequence=11790
channel ORA_DISK_4: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fw8r13r_.bkp tag=BKP_DATAMART_ARCHL_120611021301
channel ORA_DISK_4: restore complete, elapsed time: 00:00:04
archive log filename=/arch/DATAMART/5641608e-0000011791_0001_697357424.oarl thread=1 sequence=11791
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=11792
channel ORA_DISK_1: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fwhs19r_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fwhs19r_.bkp tag=BKP_DATAMART_ARCHL_120611041300
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=/arch/DATAMART/5641608e-0000011792_0001_697357424.oarl thread=1 sequence=11792
archive log filename=/arch/DATAMART/5641608e-0000011793_0001_697357424.oarl thread=1 sequence=11793
unable to find archive log
archive log thread=1 sequence=11794
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/06/2011 17:59:45
RMAN-06054: media recovery requesting unknown log: thread 1 seq 11794 lowscn 1558554671

RMAN>

Issue:

Opening the database:

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 12/06/2011 18:01:05
ORA-01157: cannot identify/lock data file 7 – see DBWR trace file
ORA-01110: data file 7: ‘+XN_DATA/dsp0001/datafile/xn_datamart.273.665597367’

Solution:

Ran the restore again with check readonly option.

RMAN> restore database check readonly;

Starting restore at 06-DEC-11
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to +XN_DATA/datamart/datafile/xn_etl.269.769192545
restoring datafile 00008 to +XN_DATA/datamart/datafile/xn_fido.271.769192545
channel ORA_DISK_1: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbo69_.bkp
channel ORA_DISK_2: starting datafile backupset restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
restoring datafile 00002 to +XN_DATA/datamart/datafile/undotbs1.270.769192545
restoring datafile 00003 to +XN_DATA/datamart/datafile/sysaux.263.769192547
channel ORA_DISK_2: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbnr7_.bkp
channel ORA_DISK_3: starting datafile backupset restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +XN_DATA/datamart/datafile/system.260.769192547
restoring datafile 00006 to +XN_DATA/datamart/datafile/xn_test_etl.272.769192545
channel ORA_DISK_3: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbn7q_.bkp
channel ORA_DISK_4: starting datafile backupset restore
channel ORA_DISK_4: specifying datafile(s) to restore from backup set
restoring datafile 00004 to +XN_DATA/datamart/datafile/users.261.769192545
restoring datafile 00007 to +XN_DATA/dsp0001/datafile/xn_datamart.273.665597367
channel ORA_DISK_4: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbmz3_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbo69_.bkp tag=TAG20111205T235251
channel ORA_DISK_1: restore complete, elapsed time: 00:00:28
channel ORA_DISK_2: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbnr7_.bkp tag=TAG20111205T235251
channel ORA_DISK_2: restore complete, elapsed time: 00:02:23
channel ORA_DISK_3: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbn7q_.bkp tag=TAG20111205T235251
channel ORA_DISK_3: restore complete, elapsed time: 00:16:29
channel ORA_DISK_4: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbmz3_.bkp tag=TAG20111205T235251
channel ORA_DISK_4: restore complete, elapsed time: 01:48:60
Finished restore at 06-DEC-11

RMAN>
RMAN>

RMAN> recover database;

Starting recover at 06-DEC-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=537 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=1098 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=542 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=1103 devtype=DISK
datafile 7 not processed because file is read-only

starting media recovery

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/06/2011 21:28:05
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 11793 lowscn 1558553689 found to restore

RMAN> alter database open resetlogs;

database opened

RMAN>

December 2, 2011

ORA-15018: diskgroup cannot be created : ORA-00600: internal error code, arguments: [kfmNew], [2], [], [], [], [], [], [], [], [], [], []

Filed under: Uncategorized — mani @ 6:15 pm

We were using SSD for one of our Databases,  that SSD is crashed and the server is replaced with new SSD.   I was able to create ASM disk successfully, however I couldn’t create diskgroup.  Following error was thrown.  I have seen this error when we install used SSD.  Thought may be helpful to others.

Problem:

SQL> select name, state, free_mb, total_mb from v$asm_diskgroup;

no rows selected

SQL> select path,header_status from v$asm_disk;

PATH
——————————————————————————————————————————————————————————————————–
HEADER_STATUS
————————————
ORCL:ASM_SSD1
PROVISIONED

/dev/oracleasm/disks/ASM_SSD1
PROVISIONED

SQL> CREATE DISKGROUP XN_DATA EXTERNAL REDUNDANCY DISK ‘ORCL:*’;
CREATE DISKGROUP XN_DATA NORMAL REDUNDANCY DISK ‘ORCL:*’
*
ERROR at line 1:
ORA-15018: diskgroup cannot be created
ORA-00600: internal error code, arguments: [kfmNew], [2], [], [], [], [], [], [], [], [], [], []

Solution:

Zero out the disk with dd command.

[oracle@m120359 ~]$ su –
Password:
[root@m120359 ~]# dd if=/dev/zero of=/dev/sdb bs=4096 count=1
1+0 records in
1+0 records out
4096 bytes (4.1 kB) copied, 0.000191 seconds, 21.4 MB/s

[oracle@m120359 ~]$ export ORACLE_SID=+ASM
[oracle@m120359 ~]$ dba

SQL*Plus: Release 11.1.0.7.0 – Production on Thu Dec 1 21:48:09 2011

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate;
ORA-15100: invalid or missing diskgroup name

ASM instance shutdown
SQL> startup
ASM instance started

Total System Global Area  283930624 bytes
Fixed Size            2158992 bytes
Variable Size          256605808 bytes
ASM Cache           25165824 bytes
ORA-15110: no diskgroups mounted

SQL> CREATE DISKGROUP XN_DATA EXTERNAL REDUNDANCY DISK ‘ORCL:*’;

Diskgroup created.

SQL>

Create a free website or blog at WordPress.com.