Friday, August 28, 2015

11gR2 ASM – Inside story – Part2 - 2 (Diskgroup restore)

11gR2 ASM – Inside story – Part2 - 2 (Diskgroup restore)



Using RMAN, we backup the database ORAW:
1
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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
[oracle@wissem ~]$ rman
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Mar 5 03:32:44 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
RMAN> connect target sys
target database Password:
connected to target database: ORAW (DBID=3426823299)
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORAW are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/orawiss/dbs/snapcf_ORAW.f'; # default
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;
Starting backup at 05-MAR-11
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=1 STAMP=744953697
channel ORA_DISK_1: starting piece 1 at 05-MAR-11
channel ORA_DISK_1: finished piece 1 at 05-MAR-11
piece handle=/u01/app/oracle/admin/ORAW/fra/ORAW/backupset/2011_03_05/o1_mf_annnn_TAG20110305T033502_6q38brxq_.bkp tag=TAG20110305T033502 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:16
Finished backup at 05-MAR-11
Starting backup at 05-MAR-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/oraw/datafile/system.256.744936879
input datafile file number=00002 name=+DATA/oraw/datafile/sysaux.257.744936887
input datafile file number=00005 name=+DATA_REPORTING/oraw/datafile/data_reporting.256.744952855
input datafile file number=00003 name=+DATA/oraw/datafile/undotbs1.258.744936889
input datafile file number=00004 name=+DATA/oraw/datafile/users.259.744936891
channel ORA_DISK_1: starting piece 1 at 05-MAR-11
channel ORA_DISK_1: finished piece 1 at 05-MAR-11
piece handle=/u01/app/oracle/admin/ORAW/fra/ORAW/backupset/2011_03_05/o1_mf_nnndf_TAG20110305T033522_6q38chho_.bkp tag=TAG20110305T033522 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:07:54
Finished backup at 05-MAR-11
Starting backup at 05-MAR-11
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=2 STAMP=744954215
channel ORA_DISK_1: starting piece 1 at 05-MAR-11
channel ORA_DISK_1: finished piece 1 at 05-MAR-11
piece handle=/u01/app/oracle/admin/ORAW/fra/ORAW/backupset/2011_03_05/o1_mf_annnn_TAG20110305T034337_6q38tto8_.bkp tag=TAG20110305T034337 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-MAR-11
Starting Control File and SPFILE Autobackup at 05-MAR-11
piece handle=/u01/app/oracle/admin/ORAW/fra/ORAW/autobackup/2011_03_05/o1_mf_s_744954221_6q38v2xk_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 05-MAR-11
RMAN>
Now, we silmulate a loss of the diskgroup DATA_REPORTING. From the ASM instance, we execute the following commands:
1
2
3
4
5
6
7
8
9
SQL> alter diskgroup DATA_REPORTING DISMOUNT FORCE;
Diskgroup altered.
SQL> drop diskgroup DATA_REPORTING FORCE INCLUDING CONTENTS;
Diskgroup dropped.
SQL>
Back to the database instance and me make sure the the database is on error:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SQL> insert into reporting values (3, sysdate);
insert into reporting values (3, sysdate)
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 26825
Session ID: 23 Serial number: 86
SQL>
SQL> select * from reporting;
select * from reporting
              *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only
SQL>
Now, using the md_restore command we restore the diskgroup:
1
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
[oracle@wissem ~]$ asmcmd
ASMCMD> md_restore /tmp/asmbkp_data_reporting -t full -g DATA_REPORTING
WARNING:option 'g' is deprecated for 'md_restore'
please use 'G'
WARNING:option 't' is deprecated for 'md_restore'
please use 'full|nodg|newdg'
Current Diskgroup metadata being restored: DATA_REPORTING
Diskgroup DATA_REPORTING created!
System template ONLINELOG modified!
System template AUTOBACKUP modified!
System template ASMPARAMETERFILE modified!
System template OCRFILE modified!
System template ASM_STALE modified!
System template OCRBACKUP modified!
System template PARAMETERFILE modified!
System template ASMPARAMETERBAKFILE modified!
System template FLASHFILE modified!
System template XTRANSPORT modified!
System template DATAGUARDCONFIG modified!
System template TEMPFILE modified!
System template ARCHIVELOG modified!
System template CONTROLFILE modified!
System template DUMPSET modified!
System template BACKUPSET modified!
System template FLASHBACK modified!
System template DATAFILE modified!
System template CHANGETRACKING modified!
Directory +DATA_REPORTING/ORAW re-created!
Directory +DATA_REPORTING/ORAW/DATAFILE re-created!
ASMCMD>
Back to the ASM instance and make sure the diskgroup is restored:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SQL> select GROUP_NUMBER, NAME from V$ASm_DISK;
GROUP_NUMBER NAME
------------ ------------------------------
           0
           1 DISK4
           1 DISK3
           1 DISK2
           1 DISK1
           2 DISK6
           2 DISK5
7 rows selected.
SQL> select NAME, TOTAL_MB, FREE_MB from V$ASM_DISKGROUP;
NAME                             TOTAL_MB    FREE_MB
------------------------------           ----------          ----------
DATA                                 4000             1068
INDX                                      0                   0
DATA_REPORTING            2000             1898
SQL>
The data are not restored yet, only the diskgroup metadata are now restored:
1
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
[oracle@wissem ~]$ source .bash_profile
[oracle@wissem ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Sat Mar 5 03:50:18 2011
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
SQL> connect sys as sysdba
Enter password:
Connected.
SQL> select * from reporting;
select * from reporting
              *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  313860096 bytes
Fixed Size                  1336232 bytes
Variable Size             255855704 bytes
Database Buffers           50331648 bytes
Redo Buffers                6336512 bytes
Database mounted.
Database opened.
SQL> select * from reporting;
select * from reporting
              *
ERROR at line 1:
ORA-00376: file 5 cannot be read at this time
ORA-01110: data file 5:
'+DATA_REPORTING/oraw/datafile/data_reporting.256.744952855'
SQL>
Now let’s restore and recover the database:
1
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
41
42
43
44
45
46
47
48
49
50
51
52
53
SQL> startup force mount;
ORACLE instance started.
Total System Global Area  313860096 bytes
Fixed Size                  1336232 bytes
Variable Size             260050008 bytes
Database Buffers           46137344 bytes
Redo Buffers                6336512 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@wissem ~]$ rman
Recovery Manager: Release 11.2.0.1.0 - Production on Sat Mar 5 04:14:50 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
RMAN> connect target sys
target database Password:
connected to target database: ORAW (DBID=3426823299, not open)
RMAN> restore database;
Starting restore at 05-MAR-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=25 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DATA/oraw/datafile/system.256.744936879
channel ORA_DISK_1: restoring datafile 00002 to +DATA/oraw/datafile/sysaux.257.744936887
channel ORA_DISK_1: restoring datafile 00003 to +DATA/oraw/datafile/undotbs1.258.744936889
channel ORA_DISK_1: restoring datafile 00004 to +DATA/oraw/datafile/users.259.744936891
channel ORA_DISK_1: restoring datafile 00005 to +DATA/oraw/datafile/data_reporting.266.744955249
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/admin/ORAW/fra/ORAW/backupset/2011_03_05/o1_mf_nnndf_TAG20110305T033522_6q38chho_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/admin/ORAW/fra/ORAW/backupset/2011_03_05/o1_mf_nnndf_TAG20110305T033522_6q38chho_.bkp tag=TAG20110305T033522
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:07:42
Finished restore at 05-MAR-11
RMAN> recover database;
Starting recover at 05-MAR-11
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:29
Finished recover at 05-MAR-11
From above, note that RMAN restored the DATA_REORTING datafie to +DATA diskgroup.
But, we would like to restore the DATA_REORTING datafie to +DATA_REPORTING diskgroup as it was before the failure.
Also note that the restored datafile is in offline state:
1
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
SQL> SELECT FILE_NAME, TABLESPACE_NAME, ONLINE_STATUS from DBa_DATA_FILES;
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME                ONLINE_
------------------------------ -------
+DATA/oraw/datafile/users.259.744936891
USERS                          ONLINE
+DATA/oraw/datafile/undotbs1.258.744936889
UNDOTBS1                       ONLINE
+DATA/oraw/datafile/sysaux.257.744936887
SYSAUX                         ONLINE
FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME                ONLINE_
------------------------------ -------
+DATA/oraw/datafile/system.256.744936879
SYSTEM                         SYSTEM
+DATA/oraw/datafile/data_reporting.266.744955249
DATA_REPORTING                 OFFLINE
SQL> ALTER DATABASE DATAFILE '+DATA/oraw/datafile/data_reporting.266.744955249' ONLINE;
Database altered.
SQL> select * from reporting;
        ID REP_DATE
---------- ---------
         1 05-MAR-11
         2 05-MAR-11
SQL>
Now, let’s restore the DATA_REORTING datafie to +DATA_REPORTING diskgroup as it was before the failure.
from the database instance, make the datafile offline:
1
2
SQL> ALTER DATABASE DATAFILE '+DATA/oraw/datafile/data_reporting.266.744955249'
OFFLINE;
Using RMAN, execute the following sequence of commands:
1
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
41
RMAN> COPY DATAFILE '+DATA/oraw/datafile/data_reporting.266.744955249'
TO '+DATA_REPORTING';
Starting backup at 05-MAR-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA/oraw/datafile/data_reporting.266.744955249
output file name=+DATA_REPORTING/oraw/datafile/data_reporting.259.744959803
tag=TAG20110305T051638 RECID=4 STAMP=744959835
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:44
Finished backup at 05-MAR-11
Starting Control File and SPFILE Autobackup at 05-MAR-11
piece handle=/u01/app/oracle/admin/ORAW/fra/ORAW/autobackup/2011_03_05/o1_mf_s_744959848_6q3gbtvk_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 05-MAR-11
RMAN> run{
2> set newname for datafile '+DATA/oraw/datafile/data_reporting.266.744955249'
3> to '+DATA_REPORTING/oraw/datafile/data_reporting.259.744959803';
4> switch datafile all;
5> }
executing command: SET NEWNAME
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=744959835 file
 name=+DATA_REPORTING/oraw/datafile/data_reporting.259.744959803
RMAN>
RMAN> recover datafile '+DATA_REPORTING/oraw/datafile/data_reporting.259.744959803';
Starting recover at 05-MAR-11
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished recover at 05-MAR-11
RMAN>
Now, back to the oracle instance, execute the following commands in order to back online the datafile and check the reporting table.
1
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
SQL> ALTER DATABASE DATAFILE '+DATA_REPORTING/oraw/datafile/data_reporting.259.744959803'
  2  ONLINE;
Database altered.
SQL> select FILE_NAME from DBA_DATA_FILES;
FILE_NAME
--------------------------------------------------------------------------------
+DATA/oraw/datafile/users.259.744936891
+DATA/oraw/datafile/undotbs1.258.744936889
+DATA/oraw/datafile/sysaux.257.744936887
+DATA/oraw/datafile/system.256.744936879
+DATA_REPORTING/oraw/datafile/data_reporting.259.744959803
SQL> alter session set NLS_DATE_FORMAT='DD-MM-YYYY HH24:MI:SS';
Session altered.
SQL> select * from reporting;
        ID REP_DATE
---------- -------------------
         1 05-03-2011 03:25:04
         2 05-03-2011 03:25:22
SQL>
Nice reading,

No comments:

Post a Comment

  How to Change Instance Type & Security Group of EC2 in AWS By David Taylor Updated April 29, 2023 EC2 stands for Elastic Compute Cloud...