Oracle & MySQL Support

InfraStack-Labs Oracle & MySQL DBA Services help you manage, maintain, and optimize your critical Oracle systems. We deliver 24/7, year-round support with flexible monthly contracts that don’t lock you in.

Please contact me :- ajith.narayanan@infrastack-labs.in

Friday, July 31, 2015

Oracle Backup & Recovery Series - 3 (Enable Archive Log Mode)

This blogpost is about enabling the archive log mode for our database using the OEM Dbconsole. In my previous post we had seen how our backup location was changed from +DATA to +BACKUP diskgroup and the +BACKUP dg was properly sized to 8 GB, After a while I did reduce the size of +BACKUP to 4GB due to space constraint in my lab.

1) Login to OEM DBConsole as a sysdba user (SYS) and click on the "Availability" tab and click on "Recovery Settings" 

2) Under "Media Recovery" section, click on the ARCHIVELOG Mode checkbox and make a note of the archive log format as %t_%s_%r.dbf.

Also, the archived log location is set as USE_DB_RECOVERY_FILE_DEST (In our case its +BACKUP ASM diskgroup) and click on "Apply"


3) The database prompts for a bounce for enabling the archive logs for the database. Click on yes to bounce the database from the OEM console itself (For this the login credentials are prompted as shown in the 2nd screenshot)



 
For people who wants to know what are the commands that enables the archiving and change the FRA size, they can use the below command instead of OEM.



ajithpathiyil1:/home/oracle[ajith1]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jun 28 20:41:13 2015

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


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

SQL> select name,open_mode,database_role from v$database;

NAME     OPEN_MODE          DATABASE_ROLE
--------- -------------------- ----------------
AJITHPAT  READ WRITE        PRIMARY

SQL> SELECT LOG_MODE FROM V$DATABASE;

LOG_MODE
------------
NOARCHIVELOG

SQL> alter database archivelog;

Database altered
SQL> exit
ajithpathiyil1:/home/oracle[ajith1]$ srvctl stop database -d ajithpathiyil
ajithpathiyil1:/home/oracle[ajith1]$ srvctl status database -d ajithpathiyil
Instance ajith1 is not running on node ajithpathiyil1
Instance ajith2 is not running on node ajithpathiyil2
Instance ajith3 is not running on node ajithpathiyil3
ajithpathiyil1:/home/oracle[ajith1]$
ajithpathiyil1:/home/oracle[ajith1]$ srvctl status database -d ajithpathiyil
Instance ajith1 is running on node ajithpathiyil1
Instance ajith2 is running on node ajithpathiyil2
Instance ajith3 is running on node ajithpathiyil3
ajithpathiyil1:/home/oracle[ajith1]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jul 31 23:38:10 2015

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


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

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     105
Next log sequence to archive   105
Current log sequence           106
SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +BACKUP
db_recovery_file_dest_size           big integer 8G

SQL> ALTER SYSTEM SET db_recovery_file_dest_size = 4294967296 SCOPE=BOTH SID='*'

SQL> show parameter db_recovery_file_dest


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +BACKUP
db_recovery_file_dest_size           big integer 4G





4) For double checking, login to the database and verify.



ajithpathiyil1:/home/oracle[ajith1]$ srvctl status database -d ajithpathiyil
Instance ajith1 is running on node ajithpathiyil1
Instance ajith2 is running on node ajithpathiyil2
Instance ajith3 is running on node ajithpathiyil3
ajithpathiyil1:/home/oracle[ajith1]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jul 31 23:38:10 2015

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


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

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

 5) Let's verify our ASM space before starting the backup. (Space constraint :) )

ajithpathiyil1:/home/oracle[ajith1]$ oenv
SIDs here are: +ASM1 ajith1 ajiebs ajithpathiyil
ORACLE_SID = [ajith1] ? +ASM1
The Oracle base remains unchanged with value /u01/app/oracle
ajithpathiyil1:/home/oracle[+ASM1]$ asmcmd
ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576      4094     2270                0            2270              0             N  BACKUP/
MOUNTED  EXTERN  N         512   4096  1048576      4094      386                0             386              0             Y  DATA/
MOUNTED  EXTERN  N         512   4096  1048576    255996    74433                0           74433              0             N  EBSDATA/
ASMCMD>


Enough space :)

6) Database Backup

ajithpathiyil1:/home/oracle[ajith1]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jul 31 23:54:33 2015

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

connected to target database: AJITHPAT (DBID=2331484960)

RMAN> backup database;

Starting backup at 31-JUL-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=62 instance=ajith1 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=+DATA/ajithpathiyil/datafile/sysaux.257.862005859
input datafile file number=00001 name=+DATA/ajithpathiyil/datafile/system.256.862005857
input datafile file number=00003 name=+DATA/ajithpathiyil/datafile/undotbs1.258.862005861
input datafile file number=00006 name=+DATA/ajithpathiyil/datafile/undotbs2.265.862005979
input datafile file number=00005 name=+DATA/ajithpathiyil/datafile/example.264.862005925
input datafile file number=00007 name=+DATA/ajithpathiyil/datafile/undotbs3.266.862005981
input datafile file number=00004 name=+DATA/ajithpathiyil/datafile/users.259.862005861
channel ORA_DISK_1: starting piece 1 at 31-JUL-15
channel ORA_DISK_1: finished piece 1 at 31-JUL-15
piece handle=+BACKUP/ajithpathiyil/backupset/2015_07_31/nnndf0_tag20150731t235438_0.264.886550079 tag=TAG20150731T235438 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
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
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 31-JUL-15
channel ORA_DISK_1: finished piece 1 at 31-JUL-15
piece handle=+BACKUP/ajithpathiyil/backupset/2015_07_31/ncsnf0_tag20150731t235438_0.267.886550095 tag=TAG20150731T235438 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 31-JUL-15

RMAN>


 Please continue reading this series of backup and recovery basic concepts.

HAPPY LEARNING!

No comments:

Post a Comment

Thanks for you valuable comments !