How to Change Archive Log Destination in Oracle 11g


sqlplus / as sysdba

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     13
Next log sequence to archive   15
Current log sequence           15


 To check current archiving location:

SQL> SHOW PARAMETER DB_RECOVERY_FILE_DEST

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/flash_recovery
                                                 _area

SQL> ALTER SYSTEM SET log_archive_dest ='/u01/arch' scope=both;
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or
DB_RECOVERY_FILE_DEST

Now If you want to set log_archive_dest first reset DB_RECOVERY_FILE_DEST and then set .

SQL> alter system set DB_RECOVERY_FILE_DEST='';
System altered.

SQL> ALTER SYSTEM SET log_archive_dest ='/u01/arch';
System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/arch
Oldest online log sequence     15
Next log sequence to archive   17
Current log sequence           17





How to Hot backup & restore of an Oracle 11gR2 database

How to take Hot backup & restore in  Oracle 11gR2 database:-

Step 1:-
Prerequisites:-
$Select log_mode from v$database;
If the database is not in archivelog mode then put the database in the archive log mode
sql>shut immediate;
sql>startup mount;
sql>alter database archivelog ;
sql>alter database open;

Step 2:-
Taking a hot backup-

Now that we have prepared our database for a hot backup, we can go ahead with actually backing up the files.

Follow the following steps to take hot backup of the tablespaces

1. Find out the number of tablespaces associated with the database

sql>Select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE

2. Find out if the tablespaces are ready for hot backup

sql>select * from v$backup;

If the output says not active then it is not in hot backup mode

3. Put the tablespaces in hot backup mode

sql>Alter database begin backup;

sql>Select * from v$backup;
 
     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ---------
         1 ACTIVE                1128871 30-JUN-14
         2 ACTIVE                1128871 30-JUN-14
         3 ACTIVE                1128871 30-JUN-14
         4 ACTIVE                1128871 30-JUN-14
         5 ACTIVE                1128871 30-JUN-14

4. Copy the tablespace files on the hard drive to the backup location.

5. Put the tablespaces out of the backupmode

sql> Alter database end backup;

6. Verify that the tablespaces indeed have come out of the backup mode

sql>Select * from v$backup;

7. Switch the archive log

sql>Alter system archive log current;

8. Backup the control file

Note- Don’t use the operating system’s copy command to do this

sql> Alter database backup controlfile to '/u01/app/oracle/backup/controlfile.ctl';

9. Copy the archive logs to the backup location

Backup of the database finished.


Step 3:-
Restoring the oracle database from a hot backup

1. Copy the tablespace files from the backup location to the installation directory of the database instance. Also copy the controlfilebackup.

2. Rename it to CONTROL01.CTL as it was earlier.

Note- If you had another copy of the control file with the name CONTROL02.CTL, then just create a second copy of the CONTROL01.CTL and rename it CONTROL02.CTL

3. DO NOT COPY OR restore the REDO logs. If the REDO logs from the previous backup period persist then delete them

4. Start the database in mount mode

sql>startup mount;

5. Recover your database using the following
sql> Recover database until cancel using backup controlfile;

Note- The oracle system will suggest an ‘archive log file name’ to use for recovery, if you have copied the backup logs to the same location which was being used for storing the logs by the database, then u can just keep on pressing enter. Or you may give the full path to the log file.

6. When you have applied all the logs that you had used to take the backup, then write cancel on the prompt and press enter.

7. The transaction logs have been applied. Run the following query to open the database for transactions.

sql>Alter database open resetlogs;

Restore complete...

How to sync standby database which is lagging behind from primary database

Primary Database Server: server1.soumya.com
Standby Database Server: server2.soumya.com

Primary Database: prim
Standby database: stand

Database version:11.2.0.1.0


Reason:-
1. Might be due to the network outage between the primary and the standby database leading to the archive
gaps. Data guard would be able to detect the archive gaps automatically and can fetch the missing logs as
soon as the connection is re-established.

2. It could also be due to archive logs getting missed out on the primary database or the archives getting
corrupted and there would be no valid backups.

In such cases where the standby lags far behind from the primary database, incremental backups can be used
as one of the  methods to roll forward the physical standby database to have it in sync with the primary database.

At primary database:-
SQL> select status,instance_name,database_role from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE
------------ ---------------- ----------------
OPEN         prim             PRIMARY

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1            214

At standby database:-
SQL> select status,instance_name,database_role from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE
------------ ---------------- ----------------
OPEN         stand            PHYSICAL STANDBY

SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1             42
So we can see the standby database is having archive gap of around (214-42) 172 logs.

Step 1: Take a note of the Current SCN of the Physical Standby Database.
SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1022779

Step 2 : Cancel the Managed Recovery Process on the Standby database.
SQL> alter database recover managed standby database cancel;

Database altered.

Step 3: On the Primary database, take the incremental SCN backup from the SCN that is currently recorded on the standby database (1022779)
At primary database:-

RMAN> backup incremental from scn 1022779 database format '/tmp/rman_bkp/stnd_backp_%U.bak';

Starting backup at 28-DEC-14

using channel ORA_DISK_1
backup will be obsolete on date 04-JAN-15
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/prim/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/prim/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/prim/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/prim/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/prim/users01.dbf
channel ORA_DISK_1: starting piece 1 at 28-DEC-14
channel ORA_DISK_1: finished piece 1 at 28-DEC-14
piece handle=/tmp/rman_bkp/stnd_backp_0cpr8v08_1_1.bak tag=TAG20141228T025048 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25

using channel ORA_DISK_1
backup will be obsolete on date 04-JAN-15
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 28-DEC-14
channel ORA_DISK_1: finished piece 1 at 28-DEC-14
piece handle=/tmp/rman_bkp/stnd_backp_0dpr8v12_1_1.bak tag=TAG20141228T025048 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 28-DEC-14

We took the backup inside /tmp/rman_bkp directory and ensure that it contains nothing besides the incremental backups of scn.

Step 4: Take the standby controlfile backup of the Primary database controlfile.

At primary database:

RMAN> backup current controlfile for standby format '/tmp/rman_bkp/stnd_%U.ctl';

Starting backup at 28-DEC-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including standby control file in backup set
channel ORA_DISK_1: starting piece 1 at 28-DEC-14
channel ORA_DISK_1: finished piece 1 at 28-DEC-14
piece handle=/tmp/rman_bkp/stnd_0epr8v4e_1_1.ctl tag=TAG20141228T025301 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 28-DEC-14

Starting Control File and SPFILE Autobackup at 28-DEC-14
piece handle=/u01/app/oracle/flash_recovery_area/PRIM/autobackup/2014_12_28/o1_mf_s_867466384_b9y8sr8k_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 28-DEC-14

Step 5: Transfer the backups from the Primary Server to the Standby Server.
[oracle@server1 ~]$ cd /tmp/rman_bkp/
[oracle@server1 rman_bkp]$ ls -ltrh
total 24M
-rw-r-----. 1 oracle oinstall 4.2M Dec 28 02:51 stnd_backp_0cpr8v08_1_1.bak
-rw-r-----. 1 oracle oinstall 9.7M Dec 28 02:51 stnd_backp_0dpr8v12_1_1.bak
-rw-r-----. 1 oracle oinstall 9.7M Dec 28 02:53 stnd_0epr8v4e_1_1.ctl

oracle@server1 rman_bkp]$ scp *.* oracle@server2:/tmp/rman_bkp/
oracle@server2's password:
stnd_0epr8v4e_1_1.ctl                                                                      100% 9856KB   9.6MB/s   00:00  
stnd_backp_0cpr8v08_1_1.bak                                                                100% 4296KB   4.2MB/s   00:00  
stnd_backp_0dpr8v12_1_1.bak                                                                100% 9856KB   9.6MB/s   00:00

Step 6: On the standby server, connect the Standby Database through RMAN and catalog the copied
incremental backups so that the Controlfile of the Standby Database would be aware of these
incremental backups.

At standby database:-

SQL>

[oracle@server2 ~]$ rman target /
RMAN> catalog start with '/tmp/rman_bkp';

using target database control file instead of recovery catalog
searching for all files that match the pattern /tmp/rman_bkp

List of Files Unknown to the Database
=====================================
File Name: /tmp/rman_bkp/stnd_0epr8v4e_1_1.ctl
File Name: /tmp/rman_bkp/stnd_backp_0dpr8v12_1_1.bak
File Name: /tmp/rman_bkp/stnd_backp_0cpr8v08_1_1.bak

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /tmp/rman_bkp/stnd_0epr8v4e_1_1.ctl
File Name: /tmp/rman_bkp/stnd_backp_0dpr8v12_1_1.bak
File Name: /tmp/rman_bkp/stnd_backp_0cpr8v08_1_1.bak

Step 7. Shutdown the database and open it in mount stage for recovery purpose.
SQL> shut immediate;
SQL> startup mount;


Step 8.Now recover the database :-
RMAN> rman target /
RMAN> recover database noredo;

Starting recover at 28-DEC-14
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 incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/stand/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/stand/sysaux01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/stand/undotbs01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/stand/users01.dbf
destination for restore of datafile 00005: /u01/app/oracle/oradata/stand/example01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/rman_bkp/stnd_backp_0cpr8v08_1_1.bak
channel ORA_DISK_1: piece handle=/tmp/rman_bkp/stnd_backp_0cpr8v08_1_1.bak tag=TAG20141228T025048
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

Finished recover at 28-DEC-14
exit.

Step 9 : Shutdown the physical standby database, start it in nomount stage and restore the standby controlfile
backup that we had taken from the primary database.

SQL> shut immediate;
SQL> startup nomount;

[oracle@server2 rman_bkp]$ rman target /
RMAN> restore standby controlfile from '/tmp/rman_bkp/stnd_0epr8v4e_1_1.ctl';
ecovery Manager: Release 11.2.0.1.0 - Production on Sun Dec 28 03:08:45 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PRIM (not mounted)

RMAN> restore standby controlfile from '/tmp/rman_bkp/stnd_0epr8v4e_1_1.ctl';

Starting restore at 28-DEC-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/stand/stand.ctl
output file name=/u01/app/oracle/flash_recovery_area/stand/stand.ctl
Finished restore at 28-DEC-14

Step 10: Shutdown the standby database and mount the standby database, so that the standby database would
be mounted with the new controlfile that was restored in the previous step.

SQL> shut immediate;
SQL> startup mount;

At standby database:-
SQL> alter database recover managed standby database disconnect from session;

At primary database:-
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1            215

At standby database:-
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1            215

Step 11.Now we will cancel the recovery to open the database
SQL> alter database recover managed standby database cancel;

SQL> alter database open;
Database altered.

SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

Now standby database is in sync with the Primary Database.


How to resize Redo Logs in Dataguard Envrionment in oracle 11g


Primary Database Server: server1.soumya.com
Standby Database Server: server2.soumya.com

Primary Database: prim
Standby database: stand

Database version:11.2.0.1.0

Both the database prim and stand are in sync. For the “Real Time Apply” implementation, we need to make
sure that we have created the Standby Redo Logs on the standby database with the size same as that of the
Online Redo logs in primary database. The Standby Redo Logs also needs to be created on the primary
database, but this is not mandatory. The Standby Redo Logs would not be used on the Primary database until
there is a switchover operation performed and the primary database starts behaving as a standby database.

At Primary Database:
[oracle@server1 ~]$ sqlplus sys/sys@stand as sysdba
SQL> select status,instance_name,database_role from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE
------------ ---------------- ----------------
OPEN         stand            PHYSICAL STANDBY

SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$log group by group#;
    GROUP# Size in MB
---------- ----------
         1         50
         2         50
         3         50

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;

    GROUP# size in MB
---------- ----------
         6         50
         4         50
         5         50
         7         50

At Standby Database:
[oracle@server2 ~]$ sqlplus sys/sys@prim as sysdba
SQL> select status,instance_name,database_role from v$database,v$Instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE
------------ ---------------- ----------------
OPEN         prim             PRIMARY

SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$log group by group#;

    GROUP# Size in MB
---------- ----------
         1        100
         2        100
         3        100

SQL> select group#,sum(bytes/1024/1024)"Size in MB" from v$standby_log group by group#;

    GROUP# Size in MB
---------- ----------
         6         50
         4         50
         5         50
         7         50
Check if the parameter standby_file_management is set to AUTO or MANUAL on standby database.
If it is not set to MANUAL, then set it.

At standby database:-
SQL> show parameter standby_file_management

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      auto

SQL> alter system set standby_file_management=manual;
System altered.

SQL> show parameter standby_file_management

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_file_management              string      manual

On the primary database(prim), check the status of the Online Redo Logs and resize them by dropping
the INACTIVE redo logs and re-creating them with the new size.

At primary database:-
SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 INACTIVE
         3 CURRENT
Here you can see that Online Redo Log groups 1 and 2 are INACTIVE. Hence we can drop them and re-create
with the new size.
SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database add logfile group 1 size 100M;

Database altered.

SQL> select group#,status from v$log;

GROUP# STATUS
------ -------
1      UNUSED
2      INACTIVE
3      CURRENT

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database add logfile group 2 size 100M;

Database altered.

SQL>
SQL> select group#,status from v$log;

GROUP# STATUS
------ -------
1      UNUSED
2      UNUSED
3      CURRENT

Now that Online Redo Log Groups 1 and 2 are resized and the status of Group 3 is CURRENT, switch logfiles
manually until Group 3 becomes INACTIVE.

SQL> select group#,status from v$log;

GROUP# STATUS
------ -------
1      ACTIVE
2      CURRENT
3      INACTIVE

Now that Group 3 is INACTIVE, we can drop it and re-create it with the new size.

SQL> alter database drop logfile group 3;
Database altered.

SQL> alter database add logfile group 3 size 100M;
Database altered.

Now, we have resized all the Online Redo Logs on the Primary Database from 50M to 100M.
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$log group by group#;

GROUP# size in MB
------ -------------
1      100
2      100
3      100

Moving on to the Standby Redo Logs on the Primary Database:
SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;

    GROUP# size in MB
---------- ----------
         6         50
         4         50
         5         50
         7         50


SQL> select group#,status from v$standby_log;

    GROUP# STATUS
---------- ----------
         4 UNASSIGNED
         5 UNASSIGNED
         6 UNASSIGNED
         7 UNASSIGNED
The status of the Standby Redo Logs (SRL) on the Primary database would be UNASSIGNED as they would be
used only when the primary database starts behaving as a Standby (Switchover)
We can easily drop the UNASSIGNED SRLs and re-create them with the new size.

At standby database:-
SQL> alter database drop standby logfile group 4;
Now while using the above query i faced an error

SQL> alter database drop standby logfile group 4;
alter database drop standby logfile group 4
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files

To solve this issue we have to cancel the recovery on standby database
At standy database:-
SQL> alter database recover managed standby database cancel ;
Database altered.

SQL>  alter database drop standby logfile group 4;
Database altered.
SQL>  alter database drop standby logfile group 5;
Database altered.
SQL>  alter database drop standby logfile group 6;
Database altered.
SQL>  alter database drop standby logfile group 7;
Database altered.

SQL> alter database add standby logfile group 4 size 100M;
Database altered.
SQL> alter database add standby logfile group 5 size 100M;
Database altered.
SQL> alter database add standby logfile group 6 size 100M;
Database altered.
SQL> alter database add standby logfile group 7 size 100M;
Database altered.

SQL> select group#,sum(bytes/1024/1024)"size in MB" from v$standby_log group by group#;

    GROUP# size in MB
---------- ----------
         6        100
         4        100
         5        100
         7        100
Once all the Standby Redo Logs and Online Redo Logs have been resize on both Primary and standby database
set the STANDBY_FILE_MANAGEMENT to AUTO on the standby database and start the recovery (MRP) on standby database.
At standby database:-
SQL> alter system set standby_file_management=auto;
System altered.

To sync both databases we will start recovery process at standby database:-
At standby database:-
SQL> select sequence#, first_time, next_time, applied from v$archived_log order by sequence#;
SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
        29 28-DEC-14 28-DEC-14 NO
        30 28-DEC-14 28-DEC-14 NO
        31 28-DEC-14 28-DEC-14 NO
        32 28-DEC-14 28-DEC-14 NO
        33 28-DEC-14 28-DEC-14 NO
        34 28-DEC-14 28-DEC-14 NO
        35 28-DEC-14 28-DEC-14 NO
        36 28-DEC-14 28-DEC-14 NO
        37 28-DEC-14 28-DEC-14 NO

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

Database altered.

SQL>  select sequence#, first_time, next_time, applied from v$archived_log order by sequence#;
 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED
---------- --------- --------- ---------
        29 28-DEC-14 28-DEC-14 YES
        30 28-DEC-14 28-DEC-14 YES
        31 28-DEC-14 28-DEC-14 YES
        32 28-DEC-14 28-DEC-14 YES
        33 28-DEC-14 28-DEC-14 YES
        34 28-DEC-14 28-DEC-14 YES
        35 28-DEC-14 28-DEC-14 YES
        36 28-DEC-14 28-DEC-14 YES
        37 28-DEC-14 28-DEC-14 YES
SQL> alter database recover managed standby database cancel;

Database altered.
QL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY



At primary database:-
SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
37

At standby database:-
SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
37
   

How to enable Block change tracking

RMAN's change tracking feature for incremental backups improves incremental backup performance by recording
changed blocks in each datafile in a change tracking file. If change tracking is enabled, RMAN uses the
change tracking file to identify changed blocks for incremental backup, thus avoiding the need to scan
every block in the datafile.

Prior to 10.2, all incremental backups had to read every single block in the database, and if the block
has changed, it was backed up. This meant the RMAN backup job took nearly as long as a normal full backup
because every block had to be read regardless.

Change tracking is disabled by default, because it introduces some minimal performance overhead on
database during normal operations. However, the benefits of avoiding full datafile scans during backup
are considerable, especially if only a small percentage of data blocks are changed between backups.
If backup strategy involves incremental backups, then we should enable change tracking.

From Oracle 10g, the background process Block Change Tracking Writer (CTWR) will do the job of writing
modified block details to block change tracking file.

Checking Whether Change Tracking is enabled:-
SQL> SELECT status FROM v$block_change_tracking;

STATUS
----------
DISABLED

Enabling and Disabling Change Tracking:-
We can enable or disable change tracking when the database is either open or mounted as sysdba.

To enable:-
SQL> alter database enable block change tracking  using file '/u01/app/oracle/oradata/prim/rman_change_track.f' ;

The REUSE option tells Oracle to overwrite any existing file with the specified name.
SQL> alter database enable block change tracking  using file '/u01/app/oracle/oradata/prim/rman_change_track.f' REUSE;

To disable:-
SQL> alter database disable block change tracking  ;

Moving the Change Tracking File:-
If you need to move the change tracking file, the ALTER DATABASE RENAME FILE command updates the control file
to refer to the new location.

1.Check the file name
SQL> SELECT filename FROM V$BLOCK_CHANGE_TRACKING;

FILENAME
-------------------------------------------------------------
/u01/app/oracle/oradata/prim/rman_change_track.f

2.Shutdown the dbase
SQL> shut immediate;
exit

3.Move the file into different location
[oracle@server1 ~]$mv /u01/app/oracle/oradata/prim/rman_change_track.f /home/oracle/rman_change_track.f

4.Mount the database and move the change tracking file to a location.
SQL> ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/prim/rman_change_track.f' TO '/home/oracle/rman_change_track.f';

5.Open the database
SQL> alter database open;

6.Determine the new location of block change tracking file
SQL> SELECT filename FROM V$BLOCK_CHANGE_TRACKING;

FILENAME
--------------------------------------------------------------------------------
/home/oracle/rman_change_track.f


If you cannot shutdown the database, then you must disable change tracking and re-enable it, at the new location:
SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '/home/oracle/rman_change_track.f';

How to Perform Block Recovery without having RMAN backup

Oracle Version :- 11g
Os Version:-    Rhel 6.4

To demonstrate this scenario we need some corrupted datablocks on some data files.


It’s possible to perform Block Media Recovery with having only OS based “hot” backups and having NO RMAN backups.

Step 1. Create a new user and a table in that schema
[oracle@server1 ~]$ sqlplus /  as sysdba
SQL> create user soumya identified by soumya;
User created.

SQL> grant dba to soumya;
Grant succeeded.

Step 2. Now create a table inside the newly created user
SQL> conn soumya/soumya
Connected.
SQL> create table tbs_corruption (id number);
Table created.

SQL> insert into tbs_corruption values (1);
1 row created.

SQL> commit;
Commit complete.

Step 4. Confirm created user's tablespace and datafile
SQL> COLUMN segment_name FORMAT a45
SQL> SELECT segment_name, tablespace_name from dba_segments WHERE segment_name='TBS_CORRUPTION';
SEGMENT_NAME                                  TABLESPACE_NAME
--------------------------------------------- ------------------------------
TBS_CORRUPTION                                    USERS

SQL> COLUMN segment_name FORMAT a15
SQL> COLUMN tablespace_name FORMAT a15
SQL> COLUMN name FORMAT a45
SQL> SELECT segment_name, a.tablespace_name, b.name FROM dba_segments a, v$datafile b
  2  WHERE a.header_file=b.file# AND a.segment_name=
  3  'TBS_CORRUPTION';

SEGMENT_NAME    TABLESPACE_NAME NAME
--------------- --------------- ---------------------------------------------
TBS_CORRUPTION  USERS           /u01/app/oracle/oradata/prim/users01.dbf


Step 5. Take hot backup of users01.dbf datafile.
SQL> ALTER TABLESPACE users BEGIN BACKUP;
Tablespace altered.

SQL> ! cp /u01/app/oracle/oradata/prim/users01.dbf /u01/app/oracle/oradata/prim/users01_bkp.dbf

SQL> ALTER TABLESPACE users END BACKUP;

Tablespace altered.

Step 6. Find out the header block where the below table exists.
SQL> SELECT header_block FROM dba_segments WHERE segment_name='TBS_CORRUPTION ';

HEADER_BLOCK
------------
         706

[oracle@server1 ~]$ dd of=/u01/app/oracle/oradata/prim/users01.dbf bs=8192 conv=notrunc seek=707  <<EOF
> corruption
> EOF
0+1 records in
0+1 records out
11 bytes (11 B) copied, 7.5949e-05 s, 145 kB/s

[oracle@server1 ~]$ sqlplus /  as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 19 19:01:19 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn soumya/soumya
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

SQL> SELECT * FROM TBS_CORRUPTION;
SELECT * FROM TBS_CORRUPTION
              *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 707)
ORA-01110: data file 4: '/u01/app/oracle/oradata/prim/users01.dbf'


SQL> exit

       
Step 7. Now we will try to recover the corrupted datablock.
[oracle@server1 ~]$ rman target sys

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Jan 19 19:02:33 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

target database Password:
connected to target database: PRIM (DBID=4130009889)

RMAN> BLOCKRECOVER DATAFILE 4 BLOCK 707;

Starting recover at 19-JAN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=37 device type=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/19/2015 19:03:08
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore

Step 8.Catalog the “hot backup”
to the RMAN repository
RMAN> CATALOG DATAFILECOPY '/u01/app/oracle/oradata/prim/users01_bkp.dbf';

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/prim/users01_BKP.DBF RECID=2 STAMP=869425424

RMAN> BLOCKRECOVER DATAFILE 4 BLOCK 707;

Starting recover at 19-JAN-15
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s) from datafile copy /u01/app/oracle/oradata/prim/users01_BKP.DBF

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

Finished recover at 19-JAN-15

RMAN> EXIT

Step 9.Verify the data.
[oracle@server1 ~]$ sqlplus SOUMYA/SOUMYA

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 19 19:04:08 2015

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from TBS_CORRUPTION;

        ID
----------
         1

Performing Block Media Recovery using RMAN

Oracle Version :- 11g
Os Version:-    Rhel 6.4

To demonstrate this scenario we need some corrupted datablocks on some data files.

So here we will corrupt a data block manually in order to test the block recovery feature of RMAN.

Step 1:-We will create a user and will grant him dba privilge.

[oracle@server1 ~]$ sqlplus /  as sysdba

SQL> create user soumya identified by soumya;
User created.

SQL> grant dba to soumya;
Grant succeeded.


Step 2:- Now create a table inside the newly created user
SQL> conn soumya/soumya
Connected.
SQL> create table tbs_corruption (id number);
Table created.

SQL> insert into tbs_corruption values (1);
1 row created.

SQL> commit;
Commit complete.

Step 3:-Now we will take rman backup of database
[oracle@server1 ~]$ rman target /

RMAN> backup database plus archivelog;
Starting backup at 17-JAN-15
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=39 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=6 RECID=1 STAMP=869264964
input archived log thread=1 sequence=7 RECID=2 STAMP=869265416
channel ORA_DISK_1: starting piece 1 at 17-JAN-15
channel ORA_DISK_1: finished piece 1 at 17-JAN-15
piece handle=/u01/app/oracle/rman_backup/PRIM_DB_07psvs08_7_1 tag=TAG20150117T223656 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-JAN-15

Starting backup at 17-JAN-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/prim/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/prim/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/prim/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/prim/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/prim/users01.dbf
channel ORA_DISK_1: starting piece 1 at 17-JAN-15
channel ORA_DISK_1: finished piece 1 at 17-JAN-15
piece handle=/u01/app/oracle/rman_backup/PRIM_DB_08psvs0a_8_1 tag=TAG20150117T223657 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
Finished backup at 17-JAN-15

Starting backup at 17-JAN-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=3 STAMP=869265483
channel ORA_DISK_1: starting piece 1 at 17-JAN-15
channel ORA_DISK_1: finished piece 1 at 17-JAN-15
piece handle=/u01/app/oracle/rman_backup/PRIM_DB_09psvs2c_9_1 tag=TAG20150117T223804 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 17-JAN-15

Starting Control File and SPFILE Autobackup at 17-JAN-15
piece handle=/u01/app/oracle/flash_recovery_area/PRIM/autobackup/2015_01_17/o1_mf_s_869265485_bco5qp79_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 17-JAN-15

Step 4:-Now we will find out the datablock number which we need to restore
[oracle@server1 ~]$ sqlplus /  as sysdba
SQL> conn soumya/soumya
Connected.
SQL>  select HEADER_BLOCK from dba_segments where segment_name='TBS_CORRUPTION';

HEADER_BLOCK
------------
         522

The header block of the segment is 522.We are going to corrupt the next data block which is 523.

Step 5:- We will check in which datafile the above data block exists.
SQL> select a.name from v$datafile a, dba_segments b
  2  where a.file#=b.header_file and b.segment_name='TBS_CORRUPTION';

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prim/users01.dbf

So the table is  stored inside users01.dbf datafile.

Step 6:-Now lets corrupt the datafile
[oracle@server1 ~]$ dd of=/u01/app/oracle/oradata/prim/users01.dbf bs=8192 conv=notrunc seek=523 <<EOF
> testing corruption
> EOF
0+1 records in
0+1 records out
19 bytes (19 B) copied, 5.8942e-05 s, 322 kB/s

Now we have successfully corrupted the datafile

Step 7:- Now we need to flush the database buffer cache
[oracle@server1 ~]$ sqlplus soumya/soumya
SQL> alter system flush buffer_cache;
System altered.

SQL> select * from tbs_corruption;
select * from tbs_corruption
              *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 523)
ORA-01110: data file 4: '/u01/app/oracle/oradata/prim/users01.dbf'

Step 8:-Now we will do block media recovery using rman
[oracle@server1 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Jan 17 23:15:40 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PRIM (DBID=4130009889)

RMAN> blockrecover datafile 4 block 523;

Starting recover at 17-JAN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=49 device type=DISK

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00004
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/rman_backup/PRIM_DB_0cpsvs3b_12_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/rman_backup/PRIM_DB_0cpsvs3b_12_1 tag=TAG20150117T223835
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

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

Finished recover at 17-JAN-15

RMAN> exit

Now recovery has been done. we will check the table's data now
[oracle@server1 ~]$ sqlplus soumya/soumya
SQL> select * from tbs_corruption;

        ID
----------
         1

So we have successfully recovered the corrupted data block .






Related Posts Plugin for WordPress, Blogger...