Enabling FLAHSBACK Database

Enabling FLAHSBACK Database

Database FLAHSBACK option is introduce in oracle 10g version, and it is only available with ENTERPRISE EDITION,if we have STANDARD EDITION or OTHER then you can't use FLAHSBACK DATABASE option. Suppose if we trying to enable FLASHBACK DATABASE option then we are getting below error message

ORA-00439:feature not enabled: string

To check the Flashback database option is enabled, we can check in v$option or v$version data dictionary view

Now if we are on ENTERPRISE EDITION and want to enable FLASHABACK database, so below are steps for this

1. We need to set below three parameters

1. db_recovery_file_dest

For FLASHBACK RECOVERY AREA LOCATION

2. db_recovery_file_dest_size

For FLASHBACK RECOVERY AREA size

3. db_flashback_retention_target

This parameter value specify in MINUTE, default value is 1440 minute. This parameter is use for how far back in time the database may be flashed back.

NOTE: All three parameter are DYNAMIC so no need to bounce database to take effect the parameter value.

Steps for enable flashback database

1. we can check flashback database is enable or not?

select flashback_on from v$database

NO -- means disable
YES -- means enable

2. shutdown the database

SQL>shutdown immediate

3. startup the database in mount mode

SQL> startup mount

4. enable the flashback database

SQL> alter database flashback on;

5. open the database

SQL> alter database open;

ORA-01031: insufficient privileges Could not validate ASMSNMP password

ORA-01031: insufficient privileges Could not validate ASMSNMP password

In Oracle 11gR2 grid
On one of the rac node create a asm password file and copy the file to remaining nodes of the cluster

grid@test1 bin]$ orapwd file=/u01/app/11.2.0/grid/dbs/orapw+ASM password=oracleasm

grid@test1 dbs]$ scp orapw+ASM node2:/u01/app/11.2.0/grid/dbs/

grid@test1 dbs]$ scp orapw+ASM node3:/u01/app/11.2.0/grid/dbs/

Login in to Oracle database as

export ORACLE_SID=+ASM1

export ORACLE_HOME=/u01/app/11.2.0/grid

grid@test1 bin]$ sqlplus / as sysasm

SQL>create user asmsnmp identified by oracleasm

SQL> grant sysdba to asmsnmp;

Migrating Databases From NON-ASM to ASM

Migrating Databases From NON-ASM to ASM

1. First need to set below parameter for controlfile,datafile or FRA.

NOTE: I have two disk group here i am using "DGRP2" disk group.

SQL> alter system set control_files='+DGRP2' scope=spfile;
System altered.
SQL> alter system set db_create_file_dest='+DGRP2' scope=spfile;
System altered.
SQL> alter system set db_recovery_file_dest='+DGRP2' scope=spfile;
System altered.

2. Create New controlfile , datafile or spfile in ASM diskgroup.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.
Total System Global Area 263639040 bytes

Fixed Size 1332552 bytes

Variable Size 218106552 bytes

Database Buffers 37748736 bytes

Redo Buffers 6451200 bytes

Connect with RMAN session.

C:\>RMAN target=sys
Recovery Manager: Release 11.1.0.6.0 - Production on Thu Apr 17 10:12:53 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
target database Password:connected to target database: TEST (not mounted)

Restore Controlfile.

RMAN> restore controlfile from 'C:\app\m.taj\oradata\test\CONTROL01.ctl';
Starting restore at 17-APR-08using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=151 device type=DISK
channel ORA_DISK_1: copied control file copyoutput file name=+DGRP2/test/controlfile/current.256.652270419Finished restore at 17-APR-08

RMAN> alter database mount;
database mountedreleased channel: ORA_DISK_1

Take backup of database which we use for ASM diskgroup.

RMAN> backup as copy database format '+DGRP2';

Starting backup at 17-APR-08allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=151 device type=DISKchannel ORA_DISK_1: starting datafile copyinput datafile file number=00001 name=C:\APP\M.TAJ\ORADATA\TEST\SYSTEM01.DBFoutput file name=+DGRP2/test/datafile/system.257.652270565 tag=TAG20080417T101550 RECID=1 STAMP=652270748channel ORA_DISK_1: datafile copy complete, elapsed time: 00:03:06channel ORA_DISK_1: starting datafile copyinput datafile file number=00002 name=C:\APP\M.TAJ\ORADATA\TEST\SYSAUX01.DBFoutput file name=+DGRP2/test/datafile/sysaux.258.652270761 tag=TAG20080417T101550 RECID=2 STAMP=652270908channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:35channel ORA_DISK_1: starting datafile copyinput datafile file number=00003 name=C:\APP\M.TAJ\ORADATA\TEST\UNDOTBS01.DBFoutput file name=+DGRP2/test/datafile/undotbs1.259.652270927 tag=TAG20080417T101550 RECID=3 STAMP=652270952channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35channel ORA_DISK_1: starting datafile copycopying current control fileoutput file name=+DGRP2/test/controlfile/backup.260.652270971 tag=TAG20080417T101550 RECID=4 STAMP=652270975channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07channel ORA_DISK_1: starting datafile copyinput datafile file number=00004 name=C:\APP\M.TAJ\ORADATA\TEST\USERS01.DBFoutput file name=+DGRP2/test/datafile/users.261.652270989 tag=TAG20080417T101550 RECID=5 STAMP=652270991channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setincluding current SPFILE in backup setchannel ORA_DISK_1: starting piece 1 at 17-APR-08channel ORA_DISK_1: finished piece 1 at 17-APR-08piece handle=+DGRP2/test/backupset/2008_04_17/nnsnf0_tag20080417t101550_0.262.652271003 tag=TAG20080417T101550 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 17-APR-08

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DGRP2/test/datafile/system.257.652270565"
datafile 2 switched to datafile copy "+DGRP2/test/datafile/sysaux.258.652270761"
datafile 3 switched to datafile copy "+DGRP2/test/datafile/undotbs1.259.652270927"
datafile 4 switched to datafile copy "+DGRP2/test/datafile/users.261.652270989"

Again connect to sqlplus session and perform incomplete recovery

C:\>sqlplus sys/oracle as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Thu Apr 17 10:26:24 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 1071679 generated at 04/17/2008 10:11:10 needed for thread 1ORA-00289: suggestion : +DGRP2ORA-15173: entry 'ARCHIVELOG' does not exist in directory 'TEST'ORA-00280: change 1071679 for thread 1 is in sequence #14
Specify log: {=suggested filename AUTO CANCEL}

CANCEL

Media recovery cancelled.

OPEN database with RESETLOGS option.

SQL> alter database open resetlogs;
Database altered.

4. Drop old tempfile and create new tempfile in existing temp tablespace

SQL> alter database tempfile 'c:\app\m.taj\oradata\test\temp01.dbf' 2 drop including datafiles;
Database altered.

SQL> alter tablespace temp add tempfile size 512m 2 autoextend on next 250m maxsize unlimited;
Tablespace altered.

SQL> select tablespace_name, file_name, bytes from dba_temp_files;
TABLESPACE_NAME FILE_NAME BYTES------------------------------ ------------------------------ ----------TEMP +DGRP2/test/tempfile/temp.266. 536870912 652271571

5. Recreate All redolog group on ASM diskgroup

SQL> select a.group#, a.member, b.bytes

2 from v$logfile a, v$log b where a.group# = b.group#;

GROUP# MEMBER BYTES
---------- ------------------------------------ ---------
3 C:\APP\M.TAJ\ORADATA\TEST\REDO03.LOG 52428800
2 C:\APP\M.TAJ\ORADATA\TEST\REDO02.LOG 52428800
1 C:\APP\M.TAJ\ORADATA\TEST\REDO01.LOG 52428800

SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 UNUSED
3 UNUSED

SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.

SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 ACTIVE
2 ACTIVE
3 CURRENT

SQL> alter database drop logfile group 1;

alter database drop logfile group 1*ERROR at line 1:ORA-01624: log 1 needed for crash recovery of instance test (thread 1)ORA-00312: online log 1 thread 1: 'C:\APP\M.TAJ\ORADATA\TEST\REDO01.LOG'

When you get above error message then set checkpoint with below command.

SQL> alter system checkpoint global;
System altered.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile group 1 size 10m;
Database altered.

SQL> alter system checkpoint global;
System altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile group 2 size 10m;
Database altered.

SQL> alter system checkpoint global;
System altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile group 3 size 10m;
Database altered.

SQL> column member format a30

SQL> select a.group#, a.member, b.bytes

2 from v$logfile a, v$log b where a.group# = b.group#;
GROUP# MEMBER BYTES
---------- ------------------------------ ----------
3 +DGRP2/test/onlinelog/group_3. 10485760 277.652273117
2 +DGRP2/test/onlinelog/group_2. 10485760 274.652273019
1 +DGRP2/test/onlinelog/group_1. 10485760 271.652272977
1 +DGRP2/test/onlinelog/group_1. 10485760 272.652272979
2 +DGRP2/test/onlinelog/group_2. 10485760 275.652273021
3 +DGRP2/test/onlinelog/group_3. 10485760 278.652273119
6 rows selected.

6. Recreate SPFILE on ASM diskgroup

SQL> create pfile='c:\initTEST.ora' from spfile;
File created.
SQL> create spfile='+DGRP2/spfileTEST.ora' from pfile='c:\initTEST.ora';
File created.

7. Detele all backup copy from RMAN

RMAN> delete noprompt force copy;

Managing Diskgroups

Managing Diskgroups

Creating Diskgroup
1. Assign Unqiue name for each diskgroup
2. Specify redundancy level of disk group

There is three type of redundancy level
1. NORMAL redundancy with 2 way mirroring
2. HIGH redundancy with 3 way mirroring
3. EXTERNAL redundancy with NO mirroring.

3. Specify disks as belonging to specific failgroup.
If we manually not define any failgroup for disk then Oracle automatically create failgroup for each diskgroup.

4. Specify asm disks (v$asm_disk) for diskgroup.

5. Optionally specify disk group attributes such as compatibility or allocation unit size.

SQL> conn / as sysasm
Connected to an idle instance.
SQL> startup nomount
ASM instance started
Total System Global Area 267825152 bytes
Fixed Size 1332584 bytes
Variable Size 241326744 bytes
ASM Cache 25165824 bytes

Create DISKGROUP with NORMAL REDUNDANCY
SQL> CREATE DISKGROUP dgrp1 NORMAL REDUNDANCY
2 FAILGROUP controller1 DISK
3 'e:\asmdisks\disk1' NAME diska1,
4 'e:\asmdisks\disk2' NAME diska2
5 FAILGROUP controller2 DISK
6 'e:\asmdisks\disk3' NAME diska3;
Diskgroup created.

SQL> select group_number, name, type, total_mb, free_mb from v$asm_diskgroup;
GROUP_NUMBER NAME TYPE TOTAL_MB FREE_MB
------------ ------------------------------ ------ ---------- ------
1 DGRP1 NORMAL 1536 1432

ADD NEW DISK in exiting DISKGROUP dgrp1
SQL> alter diskgroup dgrp1 add disk 'e:\asmdisks\disk4';
Diskgroup altered.

SQL> select group_number, name, type, total_mb, free_mb from v$asm_diskgroup;
GROUP_NUMBER NAME TYPE TOTAL_MB FREE_MB
------------ ------------------------------ ------ ---------- ------
1 DGRP1 NORMAL 2048 1940

Managing Capacity in Disk Groups
When ASM provides redundancy, such as when you create a disk group with NORMAL or HIGH redundancy, you must have sufficient capacity in each disk group to manage a re-creation of data that is lost after a failure of one or two failure groups. After one or more disks fail, the process of restoring redundancy for all data requires space from the surviving disks in the disk group. If not enough space remains, then some files might end up with reduced redundancy.

Negative Values of USABLE_FILE_MB
Due to the relationship between FREE_MB, REQUIRED_MIRROR_FREE_MB, and USABLE_FILE_MB, USABLE_FILE_MB can become negative. Although this is not necessarily a critical situation, it does mean that:
Depending on the value of FREE_MB, you may not be able to create new files.

The next failure might result in files with reduced redundancy.

If USABLE_FILE_MB becomes negative, it is strongly recommended that you add more space to the disk group as soon as possible.

When Should External, Normal, or High Redundancy Be Used?
ASM mirroring runs on the database server and Oracle recommends to off load this processing to the storage hardware RAID controller by using external redundancy. You can use normal redundancy in the following scenarios:

Storage system does not have RAID controller
Mirroring across storage arrays
Extended cluster configurations

In general, ASM mirroring is the Oracle alternative to third party logical volume managers. ASM mirroring eliminates the need to deploy additional layers of software complexity in your Oracle database environment.

ASM Instance Creation on Windows

ASM Instance Creation on Windows

1. Create INIT.ORA file for asm instance

DIAGNOSTIC_DEST=C:\app\mqm\diag_asm_allow_only_raw_disks= FALSE instance_type=asm remote_login_passwordfile=exclusive
memory_target=52m

Note: Minimum required parameters.

C:\>oradim -new -asmsid +ASM -startmode manual
Instance created.

C:\>oradim -edit -asmsid +ASM -startmode a

C:\>oradim -edit -asmsid +ASM -syspwd change_on_install

C:\>set ORACLE_SID=+ASM

C:\>sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Apr 8 11:22:47 2008

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

Connected to an idle instance.

SQL> startup pfile ='c:\init.ora';
ASM instance started

Total System Global Area 267825152 bytes
Fixed Size 1332584 bytes
Variable Size 241326744 bytes
ASM Cache 25165824 bytes
ORA-15110: no diskgroups mounted

SQL> create spfile from pfile='c:\init.ora';

File created.

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

ASM instance shutdown
SQL> startup
ASM instance started

Total System Global Area 267825152 bytes
Fixed Size 1332584 bytes
Variable Size 241326744 bytes
ASM Cache 25165824 bytes
ORA-15110: no diskgroups mounted

SQL> SELECT group_number, disk_number, mount_status, header_status, state, path FROM v$asm_disk;
GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STATU STATE PATH

----------- ----------- ------- ------------ -------- -------------------- 0 1 CLOSED CANDIDATE NORMAL E:\ASMDISKS\DISK2 0 0 CLOSED CANDIDATE NORMAL E:\ASMDISKS\DISK1

Preparing disk for ASM instance on Windows

Preparing disk for ASM instance on Windows

We can use DISKPART.EXE to create primary,logical partitions and using ASMTOOL (commandline) & ASMTOOLG (GUI)stamp(map) for ASM instance.

Preparing Disks for ASM

http://download.oracle.com/docs/cd/B28359_01/server.111/b31107/asmprepare.htm#BHCDCFIH

Diskpart.exe

http://download.oracle.com/docs/cd/B28359_01/install.111/b28250/racstorage.htm#BABFIDGI

ASMTOOL & ASMTOOLG

http://download.oracle.com/docs/cd/B28359_01/install.111/b28250/racstorage.htm#sthref212

Demostration

E:\>asmtool -list
NTFS \Device\Harddisk0\Partition1 37997M
NTFS \Device\Harddisk0\Partition2 38319M

E:\>mkdir asmdisks
E:\>cd e:\asmdisks
E:\asmdisks>asmtool -create e:\asmdisks\DISK1 512
E:\asmdisks>asmtool -create e:\asmdisks\DISK2 512
E:\asmdisks>cd c:\app\m.taj\product\11.1.0

More Reference on disks preparing for ASM

http://www.databasejournal.com/features/oracle/article.php/3571371

http://www.databasejournal.com/img/jsc_ASM_Listing2.html