Oracle Forensics : January 2017

Author: Ashwani

1) I have a database YYAPRD running on normal file system
2) I have created disks DG1, DG2, DG3 and DG4 which will be used during ASM instance creation

I have a database db11g running on normal file system. I have two disk group +DATA and +FRA .

Disk Group Name Path File Name Fail Group File Size (MB) Used Size (MB)

------------------------- -------------------- -------------------- -------------------- -------------- --------------

DATA ORCL:DISK1 DISK1 DISK1 2,047 1,396

ORCL:DISK4 DISK4 DISK4 2,047 1,393

************************* -------------- --------------

4,094 2,789

FRA ORCL:DISK3 DISK3 DISK3 2,047 232

ORCL:DISK2 DISK2 DISK2 2,047 232

************************* -------------- --------------

4,094 464

-------------- --------------

Grand Total: 8,188 3,253

SQL> select name from v$database;

NAME

---------

DB11G

SQL> select file_name from dba_data_files;

FILE_NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/db11g/users01.dbf

/u01/app/oracle/oradata/db11g/undotbs01.dbf

/u01/app/oracle/oradata/db11g/sysaux01.dbf

/u01/app/oracle/oradata/db11g/system01.dbf

SQL> select file_name from dba_temp_files;

FILE_NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/db11g/temp01.dbf

SQL> col member form a50

SQL> select member from v$logfile;

MEMBER

--------------------------------------------------

/u01/app/oracle/oradata/db11g/redo03.log

/u01/app/oracle/oradata/db11g/redo02.log

/u01/app/oracle/oradata/db11g/redo01.log

SQL> show parameter spfile

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile string /u01/app/oracle/product/11.2.0

/dbhome_1/dbs/spfiledb11g.ora

SQL> show parameter control_file

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

control_file_record_keep_time integer 7

control_files string /u01/app/oracle/oradata/db11g/

control01.ctl, /u01/app/oracle

/flash_recovery_area/db11g/con

trol02.ctl

LOGIN to ASM INSTANCE and Check DG-Group and disk available :

[oracle@db12c-prod grid]$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jan 23 17:04:59 2017

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Automatic Storage Management option

SQL>-- Check the available DG groups

Out put:

Disk Group Name Path File Name Fail Group File Size (MB) Used Size (MB)

------------------------- -------------------- -------------------- -------------------- -------------- --------------

DATA ORCL:DISK1 DISK1 DISK1 2,047 1,396

ORCL:DISK4 DISK4 DISK4 2,047 1,393

************************* -------------- --------------

4,094 2,789

FRA ORCL:DISK3 DISK3 DISK3 2,047 232

ORCL:DISK2 DISK2 DISK2 2,047 232

************************* -------------- --------------

4,094 464

-------------- --------------

Grand Total: 8,188 3,253

Now, changed the location for control_file parameter to newly created ASM disks and set parameter DB_CREATE_FILE_DEST to ASM disks which will be used for datafiles. This parameter will serve as default location for all the datafiles which will be created in database. I am using multiplexing for controlfiles, so using two disk groups “+DATA” and “+FRA”. Once parameters are set, restart the database in nomount state for RMAN to start movement.

SQL> alter system set control_files='+DATA','+FRA' scope=spfile;

System altered.

SQL> alter system set db_create_file_dest='+DATA' scope=spfile;

System altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 626327552 bytes

Fixed Size 2215944 bytes

Variable Size 205524984 bytes

Database Buffers 415236096 bytes

Redo Buffers 3350528 bytes

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@db12c-prod ~]$

Now, connect with RMAN and restore control file first from the existing one. This is create a new control file in ASM disk “+DATA” and “+FRA” and then mount the database and backup the database as copy in ASM disk “+DATA” and as last step switch database to copy. Following is the logs from this activity.

RMAN> restore controlfile from '/u01/app/oracle/oradata/db11g/control01.ctl';

Starting restore at 23-JAN-17

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=23 device type=DISK

channel ORA_DISK_1: copied control file copy

output file name=+DATA/db11g/controlfile/current.262.934047269

output file name=+FRA/db11g/controlfile/current.258.934047271

Finished restore at 23-JAN-17

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

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

Starting backup at 23-JAN-17

using channel ORA_DISK_1