Mak's Scratchpad: ASM

Showing posts with label ASM. Show all posts

How add or alter ASM DG in RAC

1. Dismount the diskgroup in one node

17:11:52 SYS@+ASM2 SQL> alter diskgroup tba1 dismount;

Diskgroup altered.

2. Drop it from the other instance where it is mouted

17:09:32 SYS@+ASM1 SQL> drop diskgroup tba1 ;

Diskgroup dropped.

3. Use that disk in any of the existing diskgroup or add a new group.

17:12:02 SYS@+ASM2 SQL> alter diskgroup sys2 add disk '/dev/rdsk/emcpower15a' rebalance power 3;

Diskgroup altered.

How to move ASM file from one diskgroup to another in STANDBY database

[cltpbgsqdb05:oracle]/apps/orautil/app/oracle/mak/scripts/admin> rman target / nocatalog

Recovery Manager: Release 10.2.0.3.0 - Production on Mon Oct 5 20:46:04 2009

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

connected to target database: P477 (DBID=3052489846, not open)
using target database control file instead of recovery catalog

RMAN> backup as copy datafile '+SHARED_DATA_DG01/c477/datafile/tigerdata01.281.699480277' format '+SHARED_FRA_DG01';

Starting backup at 05-OCT-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=620 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=619 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00021 name=+SHARED_DATA_DG01/c477/datafile/tigerdata01.281.699480277
output filename=+SHARED_FRA_DG01/c477/datafile/tigerdata01.256.699482855 tag=TAG20091005T204734 recid=41 stamp=699482959
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:55
Finished backup at 05-OCT-09

Starting Control File and SPFILE Autobackup at 05-OCT-09
piece handle=/ora01/orabkup/P477/rman/cf_c-3052489846-20091005-00.bak comment=NONE
Finished Control File and SPFILE Autobackup at 05-OCT-09

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database datafile '+SHARED_DATA_DG01/c477/datafile/tigerdata01.281.699480277' offline drop;

Database altered.

SQL> exit
[cltpbgsqdb05:oracle]/apps/orautil/app/oracle/mak/scripts/admin> rman target / nocatalog

Recovery Manager: Release 10.2.0.3.0 - Production on Mon Oct 5 20:57:39 2009

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

connected to target database: P477 (DBID=3052489846, not open)
using target database control file instead of recovery catalog

RMAN> SWITCH DATAFILE "+SHARED_DATA_DG01/c477/datafile/tigerdata01.281.699480277" to copy;

datafile 21 switched to datafile copy "+SHARED_FRA_DG01/c477/datafile/tigerdata01.256.699482855"

RMAN> exit

Recovery Manager complete.
[cltpbgsqdb05:oracle]/apps/orautil/app/oracle/mak/scripts/admin> s

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Oct 5 20:58:38 2009

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning and Data Mining options

SQL> alter database datafile '+SHARED_FRA_DG01/c477/datafile/tigerdata01.256.699482855' online;

Database altered.

SQL> select name from v$datafile where name like '%tigerdata01%' order by creation_time;

NAME
--------------------------------------------------------------------------------
+SHARED_DATA_DG01/c477/datafile/tigerdata01.263.697412327
+SHARED_FRA_DG01/c477/datafile/tigerdata01.256.699482855

SQL>
SQL>
SQL> recover standby database;
ORA-00279: change 48204120380 generated at 10/05/2009 16:43:26 needed for
thread 1
ORA-00289: suggestion : +SHARED_AUXFILES_DG01
ORA-00280: change 48204120380 for thread 1 is in sequence #8107

Specify log: {=suggested filename AUTO CANCEL}
auto
ORA-00279: change 48204122646 generated at 10/05/2009 17:31:03 needed for
thread 1
ORA-00289: suggestion : +SHARED_AUXFILES_DG01
ORA-00280: change 48204122646 for thread 1 is in sequence #8108
ORA-00278: log file
'+SHARED_AUXFILES_DG01/c477/archivelog/2009_10_05/thread_1_seq_8107.552.69947108
9' no longer needed for this recovery

SQL> alter system set standby_file_management=auto;

System altered.

SQL>
SQL> alter database recover managed standby database disconnect from session;

Database altered.

Connect to ASM Instance

SQL> alter diskgroup SHARED_DATA_DG01 drop file '+SHARED_DATA_DG01/c477/datafile/tigerdata01.281.699480277';

Diskgroup altered.

To migrate CONTROLFILE from cooked file system to ASM

Initial control file will be on filesystem

*.control_files='/oracle/product/10.2.0/db_2/dbs/C481.ctl'
#*.control_files='+SHARED_DATA_DG01'

"initC481.ora" 57 lines, 1776 characters

Database is mounted

[cltpbgscdb04:oracle]/oracle/product/10.2.0/db_2/dbs> s

17:05:47 SYS@C481 SQL> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.
17:05:55 SYS@C481 SQL> startup nomount
ORACLE instance started.

Total System Global Area 775946240 bytes
Fixed Size 2141696 bytes
Variable Size 249975296 bytes
Database Buffers 469762048 bytes
Redo Buffers 54067200 bytes
17:06:01 SYS@C481 SQL> alter database mount standby database;

Database altered.

Elapsed: 00:00:04.37

Connect to RMAN and copy controlfile to ASM

[cltpbgscdb04:oracle]/oracle/product/10.2.0/db_2/dbs> rman target / catalog rmanadm1/rmanadm1@infra

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Aug 17 17:06:30 2009

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

connected to target database: P481 (DBID=2656166842, not open)
connected to recovery catalog database

RMAN> copy current controlfile to '+SHARED_DATA_DG01/c481/controlfile/control01.ctl';

Starting backup at 17-AUG-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=323 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=654 devtype=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=+SHARED_DATA_DG01/c481/controlfile/control01.ctl tag=TAG20090817T170830 recid=88 stamp=695149715
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
Finished backup at 17-AUG-09

Starting Control File Autobackup at 17-AUG-09
piece handle=/ora01/orabkup/P481/rman/cf_c-2656166842-20090817-06.bak comment=NONE
Finished Control File Autobackup at 17-AUG-09

RMAN> exit

Recovery Manager complete.

Shutdown the database, modify the controlfile to new file in init.ora or spfile

17:08:56 SYS@C481 SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

#*.control_files='/oracle/product/10.2.0/db_2/dbs/C481.ctl'
*.control_files='+SHARED_DATA_DG01/c481/controlfile/control01.ctl'

"initC481.ora" 57 lines, 1807 characters

Startup the database

17:09:57 @ SQL> startup nomount
ORACLE instance started.

Total System Global Area 775946240 bytes
Fixed Size 2141696 bytes
Variable Size 249975296 bytes
Database Buffers 469762048 bytes
Redo Buffers 54067200 bytes
17:10:03 @ SQL> alter database mount standby database;

Database altered.

09:12:37 SYS@C481 SQL> show parameter control

NAME TYPE VALUE
------------------------------------ ------------ ------------------------------
control_file_record_keep_time integer 7
control_files string +SHARED_DATA_DG01/c481/control
file/control01.ctl

How to move a datafile to different diskgroup in ASM

rman target / nocatalog cmdfile=rman_move_ts.cmd

Recovery Manager: Release 10.2.0.4.0 - Production on Thu Mar 26 09:56:03 2009

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

connected to target database: GES (DBID=1576423539)
using target database control file instead of recovery catalog

RMAN> run {
2> allocate channel c1 device type disk;
3> backup as copy tablespace USERS format '+DATA01';
4> release channel c1;
5> }
6>
7> sql 'alter tablespace users offline';
8> switch tablespace users to copy;
9> recover tablespace users;
10> sql 'alter tablespace users online';
11>
allocated channel: c1
channel c1: sid=1070 instance=ges1 devtype=DISK

Starting backup at 26-MAR-09
channel c1: starting datafile copy
input datafile fno=00006 name=+SYS01/ges/datafile/users.265.675517679
output filename=+DATA01/ges_ges/datafile/users.260.682509367 tag=TAG20090326T095606 recid=20 stamp=682509394
channel c1: datafile copy complete, elapsed time: 00:00:35
Finished backup at 26-MAR-09

Starting Control File and SPFILE Autobackup at 26-MAR-09
piece handle=/apps/warehouse2/rman/wbeqdrac07p_ges/cf_c-1576423539-20090326-00.bak comment=NONE
Finished Control File and SPFILE Autobackup at 26-MAR-09

released channel: c1

sql statement: alter tablespace users offline

datafile 6 switched to datafile copy "+DATA01/ges_ges/datafile/users.260.682509367"

Starting recover at 26-MAR-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1070 instance=ges1 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=1065 instance=ges1 devtype=DISK
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=1052 instance=ges1 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.5.1.0
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: sid=1049 instance=ges1 devtype=SBT_TAPE
channel ORA_SBT_TAPE_2: Data Protection for Oracle: version 5.5.1.0

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished recover at 26-MAR-09

sql statement: alter tablespace users online

Recovery Manager complete.

After this you may also need to delete the old file.

> asmcmd
ASMCMD [+] > cd SYS01/GES/DATAFILE
ASMCMD [+SYS01/GES/DATAFILE] > ls
SYSAUX.261.675517645
SYSTEM.259.675517625
UNDOTBS1.260.675517635
UNDOTBS2.263.675517655
UNDOTBS3.264.675517665
USERS.265.675517679
ASMCMD [+SYS01/GES/DATAFILE] > rm USERS.265.675517679

Resolving GAP for Standby

Missing Archives and Standby Recovery

Introduction

Sometimes it is required to ship the archivelogs from one server to another. It becomes difficult when archivelogs are sitting on ASM file system. ASM files can not be directly accessed. RMAN can come to the rescue in this.

I had an issue of missing archives on STANDBY server (Log GAP) and somehow those archives got backedup to tape and mrp could not access it. Now I had to restore it and then get it shipped to STANDBY server. Since the GAP was too big, I decided to restore it and manually ship it rather than MRP to handle it. This is the process I followed. I am sure there may be many ways. But this one worked for me.
Audience
This document can be used in case of standby recovery because of missing logs on ASM. It is intended for DBAs

Approach
" I identified log GAP using query on Standby database

SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG"
FROM V$LOG_HISTORY
GROUP BY THREAD#
/

" Then copy/restore the ASM logs to the cooked file system.

RMAN> run {
allocate channel c2 device type 'sbt_tape';
set archivelog destination to '/u01/app/oracle/admin/infra/arch';
restore archivelog from sequence 8852 until sequence 8856 thread 2;
release channel c2;
}
2> 3> 4> 5> 6>
allocated channel: c2
channel c2: sid=2228 instance=infra1 devtype=SBT_TAPE
channel c2: Data Protection for Oracle: version 5.3.3.0
executing command: SET ARCHIVELOG DESTINATION
Starting restore at 22-JAN-09
archive log thread 2 sequence 8856 is already on disk as file /u01/app/oracle/admin/infra/arch/infra_2_8856_641059495.arc
channel c2: starting archive log restore to user-specified destination
archive log destination=/u01/app/oracle/admin/infra/arch
channel c2: restoring archive log
archive log thread=2 sequence=8852
channel c2: restoring archive log
archive log thread=2 sequence=8853
channel c2: restoring archive log
archive log thread=2 sequence=8854
channel c2: restoring archive log
archive log thread=2 sequence=8855
channel c2: reading from backup piece qek5f68i_1_1
channel c2: restored backup piece 1
piece handle=qek5f68i_1_1 tag=TAG20090122T161057
channel c2: restore complete, elapsed time: 00:00:55
Finished restore at 22-JAN-09
released channel: c2
RMAN> exit

" Once the logs are restored, scp or sftp it to the standby destination. This will again be a cooked file system
# scp infra_2_*.arc <stby serverName>:/tmp
infra_2_8853_641059495.arc 100% 131MB 11.0MB/s 00:12
infra_2_8854_641059495.arc 100% 267MB 12.2MB/s 00:22
infra_2_8855_641059495.arc 100% 60MB 8.5MB/s 00:07
infra_2_8856_641059495.arc 100% 542KB 541.5KB/s 00:00
" Now if standby database log recovery destination is ASM file system or different than the scp destination, manually recover the database. Or at the recovery prompt manually feed the file name with a full path.
SQL> alter database recover managed standby database cancel;
SQL> recover from '/tmp' standby database;
" Start the managed recovery
SQL> alter database recover managed standby database disconnect from session;
" Verify the log shipping and log apply and That's it

Copy Tablespace to ASM

In order to copy a tablespace to ASM diskgroup, RMAN is the only utility that can be used. Here is an example on how to do it.

--Take the tablespace offline
sql 'alter tablespace name offline';

--Back it up as copy onto ASM diskgroup. This creates an image copy
backup as copy tablespace name format '+DATA02';

--Now just tell the controlfile that you will use new copy as your tablespace
switch tablespace name to copy;

--Bring the tablespace back online
sql 'alter tablespace name online';

Same can be done for datafiles as well. Also this can be used to copy or move a tablespace or datafile from one diskgroup to another.

To remove ASM Files

In order to delete RMAN backup files, which will have a type of "ARCHIVELOG"
and "BACKUPSET" the following two commands can be run and their output
spooled to separate files

COL gsql FORMAT a300
SET ECHO OFF
SET VERIFY OFF
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SET LINESIZE 600
SET TRIMSPOOL ON

--generates file list for ARCHIVELOG
SELECT 'alter diskgroup 'dg.name' drop file
''+'dg.name''SYS_CONNECT_BY_PATH(al.name,'/')''';'
FROM v$asm_alias al, v$asm_file fi, v$asm_diskgroup dg
WHERE al.file_number = fi.file_number(+)
AND al.group_number = dg.group_number
AND fi.type = 'ARCHIVELOG'
START WITH alias_index = 0
CONNECT BY PRIOR al.reference_index = al.parent_index
/

--generates file list for BACKUPSET
SELECT 'alter diskgroup 'dg.name' drop file
''+'dg.name''SYS_CONNECT_BY_PATH(al.name,'/')''';'
FROM v$asm_alias al, v$asm_file fi, v$asm_diskgroup dg
WHERE al.file_number = fi.file_number(+)
AND al.group_number = dg.group_number
AND fi.type = 'BACKUPSET'
START WITH alias_index = 0
CONNECT BY PRIOR al.reference_index = al.parent_index
/

--get database name
ACCEPT database PROMPT 'Enter the database name: '
--generates file list
SELECT 'ALTER DISKGROUP 'gname' DROP FILE '''full_path''';' gsql FROM
(SELECT CONCAT('+'gname, SYS_CONNECT_BY_PATH(aname,'/')) full_path, gname FROM
(SELECT g.name gname, a.parent_index pindex, a.name aname,
a.reference_index rindex, a.ALIAS_DIRECTORY adir
FROM v$asm_alias a, v$asm_diskgroup g
WHERE a.group_number = g.group_number)
WHERE adir='N'
START WITH (MOD(pindex, POWER(2, 24))) = 0
CONNECT BY PRIOR rindex = pindex)
WHERE full_path LIKE UPPER('%&database%');

Shipping ASM archivelogs

Sometimes it is required to ship the archivelogs from one server to another. It becomes difficult when archivelogs are sitting on ASM file system. ASM files can not be directly accessed. RMAN can come to the rescue in this.

I had an issue of missing archives on STANDBY server (Log GAP) and somehow those archives got backedup to tape and mrp could not access it. Now I had to restore it and then get it shipped to STANDBY server. Since the GAP was too big, I decided to restore it and manually ship it rather than MRP to handle it. This is the process I followed. I am sure there may be many ways. But this one worked for me.

I identified log GAP using query

SELECT THREAD#, MAX(SEQUENCE#) AS "LAST_APPLIED_LOG"
FROM V$LOG_HISTORY
GROUP BY THREAD#
/

Then copy/restore the ASM logs to the cooked file system.

RMAN> run {
allocate channel c2 device type 'sbt_tape';
set archivelog destination to '/u01/app/oracle/admin/infra/arch';
restore archivelog from sequence 8852 until sequence 8856 thread 2;
release channel c2;
}
2> 3> 4> 5> 6>
allocated channel: c2
channel c2: sid=2228 instance=infra1 devtype=SBT_TAPE
channel c2: Data Protection for Oracle: version 5.3.3.0
executing command: SET ARCHIVELOG DESTINATION
Starting restore at 22-JAN-09
archive log thread 2 sequence 8856 is already on disk as file /u01/app/oracle/admin/infra/arch/infra_2_8856_641059495.arc
channel c2: starting archive log restore to user-specified destination
archive log destination=/u01/app/oracle/admin/infra/arch
channel c2: restoring archive log
archive log thread=2 sequence=8852
channel c2: restoring archive log
archive log thread=2 sequence=8853
channel c2: restoring archive log
archive log thread=2 sequence=8854
channel c2: restoring archive log
archive log thread=2 sequence=8855
channel c2: reading from backup piece qek5f68i_1_1
channel c2: restored backup piece 1
piece handle=qek5f68i_1_1 tag=TAG20090122T161057
channel c2: restore complete, elapsed time: 00:00:55
Finished restore at 22-JAN-09
released channel: c2
RMAN> exit

Once the logs are restored, scp or sftp it to the standby destination. This will again be a cooked file system

scp infra_2_*.arc :/tmp
infra_2_8853_641059495.arc 100% 131MB 11.0MB/s 00:12
infra_2_8854_641059495.arc 100% 267MB 12.2MB/s 00:22
infra_2_8855_641059495.arc 100% 60MB 8.5MB/s 00:07
infra_2_8856_641059495.arc 100% 542KB 541.5KB/s 00:00

Now if standby database log recovery destination is ASM file system or different than the scp destination, manually recover the database. Or at the recovery prompt manually feed the file name with a full path.

SQL> alter database recover managed standby database cancel;

SQL> recover from '/tmp' standby database;

Start the managed recovery

SQL> alter database recover managed standby database disconnect from session;

Verify the log shipping and log apply and That's it

Mak's Scratchpad