ASM administration

ASM "Automatic Storage Management" as we experience simplifies DBA's manage storage. In coordination with OMF (oracle managed files) it helps us get away by avoiding micro-management  of datafiles, where files can be added by referring to diskgroups.

Oracle recommendation is to keep asm disks of same size
Some of the administration you can do with ASM & see the benefits of adding/removing storage while database remains available.

In 11gR2 you need to connect as "sysasm" to add/drop disks, mount/dismount diskgroups

Below find some commands while you are working with ASM


Adding Diskgroup
create diskgroup data external redundancy disk '/dev/rhdiskasm001';

Drop diskgroup
drop diskgroup data including contents;


Find asm disks allocated
SELECT group_number, disk_number, mount_status, header_status, state, path  FROM   v$asm_disk;

ASM disks which can be added to diskgroups--> CANDIDATE/ FORMER
SELECT group_number, disk_number, mount_status, header_status, state, path 
FROM   v$asm_disk
where header_status='CANDIDATE' OR HEADER_STATUS='FORMER' ;

GROUP_Nu DISK_NUMBER MOUNT_S HEADER_STATU STATE    PATH
------------   -----------            -------      ------------    --------                  -----------------------------
           0           1                       CLOSED  FORMER       NORMAL          /dev/rhdiskasm011
           0          63                      CLOSED  CANDIDATE NORMAL          /dev/rhdiskasm064
           0           3                       CLOSED  FORMER       NORMAL           /dev/rhdiskasm006
           0          37                      CLOSED  CANDIDATE NORMAL           /dev/rhdiskasm137_T2
           0          38                      CLOSED  CANDIDATE NORMAL           /dev/rhdiskasm138_T2
Query disks in a specific diskgroup
select d.group_number, d.disk_number, d.name, d.path, dg.name, dg.group_number from
v$asm_disk d, v$asm_diskgroup dg
where d.group_number=dg.group_number
and dg.name='&NAME';

Add disk
alter diskgroup DATA add disk '/dev/rhdiskasm104_T2' rebalance power 11;

Drop disk from Diskgroup
alter diskgroup DATA drop disk DATA_0000;
or
alter diskgroup DATA drop disk DATA_0001 rebalance power 11;
or
alter diskgroup DATA drop disk DATA_0002, DATA_0003, DATA_0004 rebalance power 11;

To monitor the progress of rebalancing operation

SQL> SELECT group_number, operation, state, power,sofar,est_work, est_minutes
FROM v$asm_operation;
GROUP_NUMBER OPERA STAT      POWER      SOFAR   EST_WORK   EST_MINUTES
     ------------           -----     ----        ----------    ----------     ----------           -----------
           4                      REBAL RUN          11            100918         321268                   35
During rebalancing you can alos monitor the work & see the size of disk & how much of it is free.

select group_number, name, total_mb/1024 "SizeofDisk_GB", free_mb/1024 "FreesizeofDisk_now_GB"  from v$asm_disk_stat where group_number=4 and name LIKE 'DATA_00%' ;
GROUP_NUMBER NAME                           SizeofDisk_GB FreesizeofDisk_now_GB
------------ ------------------------------ ------------- ---------------------
           4 DATA_0085                         50.5810547            20.9111328
           4 DATA_0081                         50.5810547            20.9101563
           4 DATA_0082                         50.5810547            20.9130859
           4 DATA_0083                         50.5810547            20.9140625
           4 DATA_0084                         50.5810547            20.9121094
           4 DATA_0077                         50.5810547            20.9140625
           4 DATA_0078                         50.5810547            20.9140625

RMAN Restore/ Recovery using Incremental backups--serial#1

To recover to a particular point in time, getting details about time or scn will help in doing a point in time recovery. The goal here is limited to do recovery using incremental backups.
SQL> conn / as sysdba
Connected.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
8142998
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
21-JUN-12 02.22.37.519217 PM -05:00
　
Kick off by taking a level 0 Backup, Taking a level 0 cumulative backup
[oracle@lnx133 work]$ ls -lrt r*
-rwxr-xr-x 1 oracle oinstall 294 Jun 21 13:58 rmanbackup.sh
-rw-r--r-- 1 oracle oinstall 586 Jun 21 14:31 rmanhotbackup.shdrwxrwxr-x 2 oracle oinstall 16K Dec 19 2011 lost+found
A look at the RMAN scripts to take hot backup
[oracle@lnx133 work]$ cat rmanbackup.sh#!/bin/ksh
export ORACLE_SID=maze
export ORACLE_HOME=/u01/app/oracle/product/11g/11.2.0.2/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin
msglog=rman_hot_backup_`date +\%Y\%m\%d`.log
rman target / cmdfile=rmanhotbackup.sh log=$msglog append
The command file for RMAN, you can configure it as per your requirement, here basic few parameters
have been configured.
[oracle@lnx133 work]$ cat rmanhotbackup.shrun
{
configure CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/backup/maze/ctl%d_%F';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/backup/maze/sncf_%d';
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
backup tag HBlevel0 filesperset 10 format '/u01/app/oracle/backup/maze/Hot_Base_%d_s%s_%U' as compressed backupset incremental level 0 cumulative database plus archivelog;
release channel c1;
release channel c2;
release channel c3;
}
exit;　
Ready to run the script
[oracle@lnx133 work]$ nohup ./rmanbackup.sh &[1] 3764
[oracle@lnx133 work]$ nohup: appending output to `nohup.out'
[oracle@lnx133 work]$ ls -lrt r*
-rwxr-xr-x 1 oracle oinstall 294 Jun 21 13:58 rmanbackup.sh
-rw-r--r-- 1 oracle oinstall 586 Jun 21 14:31 rmanhotbackup.sh
-rw-r--r-- 1 oracle oinstall 6195 Jun 21 14:34 rman_hot_backup_20120621.logmonitoring the rman log
[oracle@lnx133 work]$ tail -f rman_hot_backup_20120621.logchannel c1: starting piece 1 at 21-JUN-12
channel c2: starting compressed archived log backup set
channel c2: specifying archived log(s) in backup set
input archived log thread=1 sequence=341 RECID=2 STAMP=786550083
input archived log thread=1 sequence=342 RECID=3 STAMP=786550332
channel c2: starting piece 1 at 21-JUN-12
channel c3: starting compressed archived log backup set
channel c3: specifying archived log(s) in backup set
input archived log thread=1 sequence=343 RECID=4 STAMP=786551149
channel c3: starting piece 1 at 21-JUN-12
channel c1: finished piece 1 at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/Hot_Base_MAZE_s15_0fne3kli_1_1 tag=HBLEVEL0 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:04
channel c1: starting compressed archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=344 RECID=5 STAMP=786551357
input archived log thread=1 sequence=345 RECID=6 STAMP=786551473
channel c1: starting piece 1 at 21-JUN-12
channel c2: finished piece 1 at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/Hot_Base_MAZE_s16_0gne3kli_1_1 tag=HBLEVEL0 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:04
channel c3: finished piece 1 at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/Hot_Base_MAZE_s17_0hne3klj_1_1 tag=HBLEVEL0 comment=NONE
channel c3: backup set complete, elapsed time: 00:00:03
channel c1: finished piece 1 at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/Hot_Base_MAZE_s18_0ine3klm_1_1 tag=HBLEVEL0 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-JUN-12
Starting backup at 21-JUN-12
channel c1: starting compressed incremental level 0 datafile backup setchannel c1: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/oradata1/maze/sysaux01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata1/maze/sj01.dbf
channel c1: starting piece 1 at 21-JUN-12
channel c2: starting compressed incremental level 0 datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata1/maze/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata1/maze/example01.dbf
channel c2: starting piece 1 at 21-JUN-12
channel c3: starting compressed incremental level 0 datafile backup set
channel c3: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata1/maze/system01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata1/maze/users01.dbf
channel c3: starting piece 1 at 21-JUN-12
channel c2: finished piece 1 at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/Hot_Base_MAZE_s20_0kne3klo_1_1 tag=HBLEVEL0 comment=NONE
channel c2: backup set complete, elapsed time: 00:00:45
channel c3: finished piece 1 at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/Hot_Base_MAZE_s21_0lne3klo_1_1 tag=HBLEVEL0 comment=NONE
channel c3: backup set complete, elapsed time: 00:03:06
channel c1: finished piece 1 at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/Hot_Base_MAZE_s19_0jne3klo_1_1 tag=HBLEVEL0 comment=NONE
channel c1: backup set complete, elapsed time: 00:03:16
Finished backup at 21-JUN-12
Starting backup at 21-JUN-12
current log archivedchannel c1: starting compressed archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=346 RECID=7 STAMP=786551678
channel c1: starting piece 1 at 21-JUN-12
channel c1: finished piece 1 at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/Hot_Base_MAZE_s22_0mne3ks0_1_1 tag=HBLEVEL0 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-JUN-12
Starting Control File and SPFILE Autobackup at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/ctlMAZE_c-1370075061-20120621-02 comment=NONE
Finished Control File and SPFILE Autobackup at 21-JUN-12
released channel: c1
released channel: c2
released channel: c3
Recovery Manager complete.
[1]+ Done nohup ./rmanbackup.sh
　
level 0 backup is followed by generating activity--> creating & inserting records in scott.worker2 table, followed by incremental level 1 cumulative backup[oracle@lnx133 work]$ sql
SQL*Plus: Release 11.2.0.2.0 Production on Thu Jun 21 14:35:13 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn scott/abc123;
Connected.
SQL> create table worker2 as select * from worker;Table created.
SQL> insert into worker2 select * from worker2;6 rows created.
SQL> /
12 rows created.
SQL> /
24 rows created.
SQL> /
48 rows created.
SQL> /
96 rows created.
SQL> /
192 rows created.
SQL> /
384 rows created.
SQL> /
768 rows created.
SQL> /
1536 rows created.
SQL> /
3072 rows created.
SQL> /
6144 rows created.
SQL> /
12288 rows created.
SQL> /
24576 rows created.
SQL> /
49152 rows created.
SQL> /
98304 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from worker2;COUNT(*)
----------
196608
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Ready to take level 1 incremental cumulative backup so it back's up the changes.
[oracle@lnx133 work]$ ls -lrt r*
-rwxr-xr-x 1 oracle oinstall 294 Jun 21 13:58 rmanbackup.sh
-rw-r--r-- 1 oracle oinstall 586 Jun 21 14:31 rmanhotbackup.sh
-rw-r--r-- 1 oracle oinstall 6195 Jun 21 14:34 rman_hot_backup_20120621.log
-rwxr-xr-x 1 oracle oinstall 301 Jun 21 14:42 rmanincbackup.sh
-rw-r--r-- 1 oracle oinstall 585 Jun 21 14:44 rmaninchotbackup.sh[oracle@lnx133 work]$ cat rmanincbackup.sh#!/bin/ksh
export ORACLE_SID=maze
export ORACLE_HOME=/u01/app/oracle/product/11g/11.2.0.2/dbhome_1
export PATH=$PATH:$ORACLE_HOME/bin
msglog=rman_hot_backup_`date +\%Y\%m\%d_\%T`.log
rman target / cmdfile=rmaninchotbackup.sh log=$msglog append
[oracle@lnx133 work]$ cat rmaninchotbackup.shrun
{
configure CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/backup/maze/ctl%d_%F';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/backup/maze/sncf_%d_`date`';
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
backup tag HBlevel1 filesperset 10 format '/u01/app/oracle/backup/maze/Hot_Inc_%d_s%s_%U' as compressed backupset incremental level 1 cumulative database plus archivelog;
release channel c1;
release channel c2;
release channel c3;
}
exit;
Ready to run incremental backup
[oracle@lnx133 work]$ nohup ./rmanincbackup.sh &[1] 3973
[oracle@lnx133 work]$ nohup: appending output to `nohup.out'
[oracle@lnx133 work]$ ls -lrt r*
-rwxr-xr-x 1 oracle oinstall 294 Jun 21 13:58 rmanbackup.sh
-rw-r--r-- 1 oracle oinstall 586 Jun 21 14:31 rmanhotbackup.sh
-rw-r--r-- 1 oracle oinstall 6195 Jun 21 14:34 rman_hot_backup_20120621.log
-rwxr-xr-x 1 oracle oinstall 301 Jun 21 14:42 rmanincbackup.sh
-rw-r--r-- 1 oracle oinstall 585 Jun 21 14:44 rmaninchotbackup.sh
-rw-r--r-- 1 oracle oinstall 6316 Jun 21 14:49 rman_hot_backup_20120621_14:45:42.logmonitoring the rman backup log
[oracle@lnx133 work]$ tail -f rman_hot_backup_20120621_14\:45\:42.logchannel c2: starting compressed incremental level 1 datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata1/maze/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata1/maze/example01.dbf
channel c2: starting piece 1 at 21-JUN-12
channel c3: starting compressed incremental level 1 datafile backup set
channel c3: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata1/maze/system01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata1/maze/users01.dbf
channel c3: starting piece 1 at 21-JUN-12
channel c3: finished piece 1 at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/Hot_Inc_MAZE_s30_0une3lh5_1_1 tag=HBLEVEL1 comment=NONE
channel c3: backup set complete, elapsed time: 00:02:06
channel c2: finished piece 1 at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/Hot_Inc_MAZE_s29_0tne3lh5_1_1 tag=HBLEVEL1 comment=NONE
channel c2: backup set complete, elapsed time: 00:02:26
channel c1: finished piece 1 at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/Hot_Inc_MAZE_s28_0sne3lh5_1_1 tag=HBLEVEL1 comment=NONE
channel c1: backup set complete, elapsed time: 00:02:56
Finished backup at 21-JUN-12
Starting backup at 21-JUN-12
current log archived
channel c1: starting compressed archived log backup set
channel c1: specifying archived log(s) in backup set
input archived log thread=1 sequence=348 RECID=9 STAMP=786552534
channel c1: starting piece 1 at 21-JUN-12
channel c1: finished piece 1 at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/Hot_Inc_MAZE_s31_0vne3lmn_1_1 tag=HBLEVEL1 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-JUN-12
Starting Control File and SPFILE Autobackup at 21-JUN-12
piece handle=/u01/app/oracle/backup/maze/ctlMAZE_c-1370075061-20120621-03 comment=NONE
Finished Control File and SPFILE Autobackup at 21-JUN-12
released channel: c1
released channel: c2
released channel: c3
Recovery Manager complete.
[1]+ Done nohup ./rmanincbackup.sh　
Now to imitate failure, lets move the database file from its location to another location & try to insert data in scott.worker2 table.
[oracle@lnx133 work]$ sql
SQL*Plus: Release 11.2.0.2.0 Production on Thu Jun 21 14:50:17 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> conn scott/abc123;
Connected.
SQL> select count(*) from worker2;
COUNT(*)
----------
196608
SQL> insert into worker2 select * from worker;
6 rows created.
SQL> commit;
Commit complete.
SQL> insert into worker2 select * from worker2;
insert into worker2 select * from worker2
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/app/oracle/oradata1/maze/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3　
SQL> conn / as sysdba
Connected.
SQL> shut immediate;
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/u01/app/oracle/oradata1/maze/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort;
ORACLE instance shut down.
SQL> exitDisconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@lnx133 work]$ sql
SQL*Plus: Release 11.2.0.2.0 Production on Thu Jun 21 14:52:30 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1344616 bytes
Variable Size 318770072 bytes
Database Buffers 96468992 bytes
Redo Buffers 6086656 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
We can/have to run the recovery from a script, but here I chose to run by connecting to rman & then issuing commands to restore/recover.
[oracle@lnx133 work]$ rman target /
Recovery Manager: Release 11.2.0.2.0 - Production on Thu Jun 21 14:52:57 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: MAZE (DBID=1370075061, not open)
RMAN> list backup summary;
using target database control file instead of recovery catalog
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
15 B A A DISK 21-JUN-12 1 1 YES HBLEVEL0
16 B A A DISK 21-JUN-12 1 1 YES HBLEVEL0
17 B A A DISK 21-JUN-12 1 1 YES HBLEVEL0
18 B A A DISK 21-JUN-12 1 1 YES HBLEVEL0
19 B 0 A DISK 21-JUN-12 1 1 YES HBLEVEL0
20 B 0 A DISK 21-JUN-12 1 1 YES HBLEVEL0
21 B 0 A DISK 21-JUN-12 1 1 YES HBLEVEL0
22 B A A DISK 21-JUN-12 1 1 YES HBLEVEL0
23 B F A DISK 21-JUN-12 1 1 NO TAG20120621T143442
24 B A A DISK 21-JUN-12 1 1 YES HBLEVEL1
25 B A A DISK 21-JUN-12 1 1 YES HBLEVEL1
26 B A A DISK 21-JUN-12 1 1 YES HBLEVEL1
27 B A A DISK 21-JUN-12 1 1 YES HBLEVEL1
28 B 1 A DISK 21-JUN-12 1 1 YES HBLEVEL1
29 B 1 A DISK 21-JUN-12 1 1 YES HBLEVEL1
30 B 1 A DISK 21-JUN-12 1 1 YES HBLEVEL1
31 B A A DISK 21-JUN-12 1 1 YES HBLEVEL1
32 B F A DISK 21-JUN-12 1 1 NO TAG20120621T144856
RMAN> run {Starting restore at 21-JUN-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK
channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata1/maze/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata1/maze/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/maze/Hot_Base_MAZE_s20_0kne3klo_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/maze/Hot_Base_MAZE_s20_0kne3klo_1_1 tag=HBLEVEL0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:25
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 /u01/app/oracle/oradata1/maze/system01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata1/maze/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/maze/Hot_Base_MAZE_s21_0lne3klo_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/maze/Hot_Base_MAZE_s21_0lne3klo_1_1 tag=HBLEVEL0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:56
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 00002 to /u01/app/oracle/oradata1/maze/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata1/maze/sj01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/maze/Hot_Base_MAZE_s19_0jne3klo_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/maze/Hot_Base_MAZE_s19_0jne3klo_1_1 tag=HBLEVEL0
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:02:35
Finished restore at 21-JUN-12Starting recover at 21-JUN-12using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata1/maze/system01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata1/maze/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/maze/Hot_Inc_MAZE_s30_0une3lh5_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/maze/Hot_Inc_MAZE_s30_0une3lh5_1_1 tag=HBLEVEL1
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00003: /u01/app/oracle/oradata1/maze/undotbs01.dbf
destination for restore of datafile 00005: /u01/app/oracle/oradata1/maze/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/maze/Hot_Inc_MAZE_s29_0tne3lh5_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/maze/Hot_Inc_MAZE_s29_0tne3lh5_1_1 tag=HBLEVEL1
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00002: /u01/app/oracle/oradata1/maze/sysaux01.dbf
destination for restore of datafile 00006: /u01/app/oracle/oradata1/maze/sj01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/maze/Hot_Inc_MAZE_s28_0sne3lh5_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/maze/Hot_Inc_MAZE_s28_0sne3lh5_1_1 tag=HBLEVEL1
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
starting media recovery
media recovery complete, elapsed time: 00:00:14
Finished recover at 21-JUN-12
sql statement: alter database open
RMAN> exitRecovery Manager complete.
2> restore database;
3> recover database;
4> sql 'alter database open';
5> }　Below is the activity in aler*log

Completed: ALTER DATABASE MOUNTThu Jun 21 14:55:25 2012
Full restore complete of datafile
checkpoint is 8143741
last deallocation scn is 8133875
Undo Optimization current scn is 8139292
Thu Jun 21 14:55:40 2012
Full restore complete of datafile 5 /u01/app/oracle/oradata1/maze/example01.dbf. Elapsed time: 0:01:16
checkpoint is 8143741
last deallocation scn is 7542368
Full restore complete of datafile 4 /u01/app/oracle/oradata1/maze/users01.dbf. Elapsed time: 0:00:02
checkpoint is 8143742
last deallocation scn is 8142856
Thu Jun 21 14:57:35 2012
Full restore complete of datafile 1 /u01/app/oracle/oradata1/maze/system01.dbf. Elapsed time: 0:01:49
checkpoint is 8143742
last deallocation scn is 7355940
Undo Optimization current scn is 8139292
Thu Jun 21 14:58:08 2012
Full restore complete of datafile 6 /u01/app/oracle/oradata1/maze/sj01.dbf. Elapsed time: 0:00:20
checkpoint is 8143740
last deallocation scn is 770465
Thu Jun 21 15:00:10 2012
Full restore complete of datafile 2 /u01/app/oracle/oradata1/maze/sysaux01.dbf. Elapsed time: 0:02:24
checkpoint is 8143740
last deallocation scn is 8093014
Thu Jun 21 15:00:22 2012
Incremental restore complete of datafile
checkpoint is 8144777
last deallocation scn is 8142856
Incremental restore complete of datafile 1 /u01/app/oracle/oradata1/maze/system01.dbf
checkpoint is 8144777
last deallocation scn is 7355940
Incremental restore complete of datafile 5 /u01/app/oracle/oradata1/maze/example01.dbf
checkpoint is 8144776
last deallocation scn is 7542368
Incremental restore complete of datafile 3 /u01/app/oracle/oradata1/maze/undotbs01.dbf
checkpoint is 8144776
last deallocation scn is 8133875
Incremental restore complete of datafile 6 /u01/app/oracle/oradata1/maze/sj01.dbf
checkpoint is 8144775
last deallocation scn is 770465
Incremental restore complete of datafile 2 /u01/app/oracle/oradata1/maze/sysaux01.dbf
checkpoint is 8144775
last deallocation scn is 8093014
Thu Jun 21 15:00:31 2012
alter database recover datafile list clear
Completed: alter database recover datafile list clearalter database recover datafile list
1 , 2 , 3 , 4 , 5 , 6
Completed: alter database recover datafile list
1 , 2 , 3 , 4 , 5 , 6
alter database recover if needed
start
Media Recovery Startstarted logmerger process
Parallel Media Recovery started with 2 slaves
Thu Jun 21 15:00:39 2012
Recovery of Online Redo Log: Thread 1 Group 3 Seq 348 Reading mem 0
Mem# 0: /u01/app/oracle/oradata1/maze/redo03.log
Recovery of Online Redo Log: Thread 1 Group 1 Seq 349 Reading mem 0
Mem# 0: /u01/app/oracle/oradata1/maze/redo01.log
Thu Jun 21 15:00:45 2012
Media Recovery Complete (maze)
Completed: alter database recover if needed
start
alter database openBeginning crash recovery of 1 threads
parallel recovery started with 2 processes
Started redo scan
Completed redo scan
read 0 KB redo, 0 data blocks need recovery
Started redo application at
Thread 1: logseq 349, block 154, scn 8145023
Recovery of Online Redo Log: Thread 1 Group 1 Seq 349 Reading mem 0
Mem# 0: /u01/app/oracle/oradata1/maze/redo01.log
Completed redo application of 0.00MB
Completed crash recovery at
Thread 1: logseq 349, block 154, scn 8165024
0 data blocks read, 0 data blocks written, 0 redo k-bytes read
Thu Jun 21 15:00:48 2012
LGWR: STARTING ARCH PROCESSES
Thu Jun 21 15:00:48 2012
ARC0 started with pid=24, OS id=4258
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Thu Jun 21 15:00:49 2012
ARC1 started with pid=25, OS id=4260
Thu Jun 21 15:00:49 2012
ARC2 started with pid=26, OS id=4262
ARC1: Archival started
ARC2: Archival started
Thu Jun 21 15:00:49 2012
ARC3 started with pid=27, OS id=4264
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Thread 1 advanced to log sequence 350 (thread open)
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Thread 1 opened at log sequence 350
Current log# 2 seq# 350 mem# 0: /u01/app/oracle/oradata1/maze/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu Jun 21 15:00:50 2012
SMON: enabling cache recovery
Archived Log entry 10 added for thread 1 sequence 349 ID 0x51a9aeb5 dest 1:
Thu Jun 21 15:00:56 2012
[4206] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:447383434 end:447385294 diff:1860 (18 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is AL32UTF8
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Thu Jun 21 15:01:05 2012
QMNC started with pid=28, OS id=4268
Thu Jun 21 15:01:13 2012
Completed: alter database open
Thu Jun 21 15:01:20 20124 /u01/app/oracle/oradata1/maze/users01.dbf3 /u01/app/oracle/oradata1/maze/undotbs01.dbf. Elapsed time: 0:00:42　
Once the database is open, connecting to verify records.
SQL> select count(*) from scott.worker2;COUNT(*)
----------
196614
SQL> conn scott/abc123;
Connected.
SQL> insert into worker2 select * from worker2;
196614 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from scott.worker2;
COUNT(*)
----------
393228
　

Confirms we have recovered the failure.

Please refer to oracle documentation for a complete/accurate information.

Can views be updated/deleted/Insert?

Views: Lets start with getting some info about views

• Object in database
• Is a virtual table, which is based on a table(s)
• They represent the results of custom SQL statements in memory, it doesn't occupy any space.
• On creation of view an entry is made in data dictionary which represents the data fetched by SQL statement.
• Data in view can come from one or more tables.
• Data in view can have all columns of one or more tables or can have select/specific columns from one or more tables.
• The selection of columns in a view helps in masking selective columns of a table from user while helping us provide access to specific columns of a particular table.
• A view can be updated, inserted into & deleted from, this will update the base tables related columns, however a View is not updatable if its query contains JOINS, SET OPERATORS, AGGREGRATE FUNCTIONS, GROUP BY, DISTINCT clause or if a view query contains psuedocolumns, expressions.
• To be able to create view in its own schema user needs to have "CREATE VIEW" privilege & if it needs to be able to create views in other schemas user should have "CREATE ANY VIEW" privilege.
• To be able to select, insert, update or delete user needs to have those privileges on all base objects(tables), these privileges should not be granted through roles.
• Base tables can be protected by restricting the privileges (providing only select privlege)

Can views be updated, deleted, inserted?
Lets take up an example

SQL> conn / as sysdba
Connected.

SQL> grant create view to scott;
Grant succeeded.

SQL> conn scott/xxx;
Connected.
SQL> create or replace view vw_emp
           as
           select ename, deptno, salary
          from employee;

View created.

SQL> select * from vw_emp;
ENAME          DEPTNO   SALARY
---------- ----------           ----------
Carter             10                  25000
Albert             20                  37000
Breen              30                  50500
Gould              20                 23700
Barker             10                 75000
Mubeen           10                 10000
Sajid                20                10005
7 rows selected.

SQL> update vw_emp
  2  set deptno=35 where ename='Carter';
1 row updated.

SQL> commit;
Commit complete.

SQL> select * from vw_emp;
ENAME          DEPTNO     SALARY
---------- ---------- ----------
Carter             35      25000Albert              20      37000
Breen               30      50500
Gould               20      23700
Barker             10      75000
Mubeen           10      10000
Sajid                20      10005
7 rows selected
.
Base table gets updated too

SQL> select * from employee;
     EMPNO ENAME          DEPTNO     PROJNO     SALARY
---------- ---------- ---------- ---------- ----------
       101 Carter             35          1      25000       102 Albert             20          3      37000
       103 Breen              30          6      50500
       104 Gould              20          5      23700
       105 Barker             10          7      75000
       101 Mubeen             10          7      10000
       102 Sajid              20          3      10005
7 rows selected.

SQL> conn radio/xxx;
Connected.

SQL> select * from vw_emp;select * from vw_emp
              *
ERROR at line 1:
ORA-00942: table or view does not exist

Since user "radio" doesn't have select privileges on view created by scott, it can't do a select on it.

We grant a select privilege to user "radio" on scott's view (vw_emp)

SQL> conn scott/xxxx;
Connected.
SQL> grant select on vw_emp to radio;
Grant succeeded.

SQL> conn radio/xxxx;
Connected.
SQL> select * from scott.vw_emp;
ENAME          DEPTNO     SALARY
---------- ---------- ----------
Carter             35      25000
Albert             20      37000
Breen              30      50500
Gould              20      23700
Barker             10      75000
Mubeen             10      10000
Sajid              20      10005
7 rows selected.

Next we attempt to update view (vw_emp, created in scott schema) as user "radio"
SQL> update scott.vw_emp
          set deptno=45 where ename='Carter';update scott.vw_emp
             *
ERROR at line 1:
ORA-01031: insufficient privileges

User "radio" doesn't have "update" privileges on view

SQL> conn scott/xxxx;
Connected.
SQL> grant update on vw_emp to radio;
Grant succeeded.

SQL> conn radio/xxxx;
Connected.

SQL> update scott.vw_emp
          set deptno=45 where ename='Carter';
1 row updated.

SQL> select * from scott.vw_emp;
ENAME          DEPTNO     SALARY
---------- ---------- ----------
Carter             45      25000
Albert             20      37000
Breen              30      50500
Gould              20      23700
Barker             10      75000
Mubeen             10      10000
Sajid              20      10005
7 rows selected.

SQL> conn scott/xxxx;
Connected.
SQL> grant select on employee to radio;
Grant succeeded.

SQL> conn radio/xxxx;
Connected.

SQL> select * from scott.employee;
     EMPNO ENAME          DEPTNO     PROJNO     SALARY
---------- ---------- ---------- ---------- ----------
       101 Carter             45          1      25000       102 Albert             20          3      37000
       103 Breen              30          6      50500
       104 Gould              20          5      23700
       105 Barker             10          7      75000
       101 Mubeen             10          7      10000
       102 Sajid              20          3      10005
7 rows selected.

Now we can see that when user "radio" after having necessary privileges did an update, view gets updated & consequently underlying table (base table) gets updated too.

Note: Please refer to oracle documentation

Grant privileges to database user to Run AWR reports

Nothing fancy but just a note on granting any database user privileges to run AWR reports.

As sysdba or system grant the following privileges to database user (say "scott")

Grant sys.advisor to scott;
grant execute on sys.dbms_workload_repository to scott;


Hope it helps!