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.