Oracle DBA User Guide

Saturday, January 25, 2014

How to Configuring the FRA

Configuring the FRA is easy,by default Flash Recovery area is ORACLE_BASE.First you create the base directory of the FRA from the OS, and then you set the following parameters in any database that will use the FRA:

DB_RECOVERY_FILE_DEST

DB_RECOVERY_FILE_DEST_SIZE

The DB_RECOVERY_FILE_DEST parameter defines the FRA base directory location. This

is the only directory you will need to create when configuring the FRA. You will need to

make sure that this directory is owned by the owner of the Oracle executable so that Oracle

can create other subdirectories beneath it.

The DB_RECOVERY_FILE_DEST_SIZE parameter defines the total amount of space for this

database instance is allowed to consume in the FRA. This is a logical limit, which can be

greater than or less than the actual physical limit of space on that device. For example, you

may have a file system with 500GB of space available on it. However, you may want to

indicate that your database can consume only up to 100GB of space within the FRA while

assigning the FRA to the 500GB file system.

You use the alter system command to configure the FRA. Note that DB_RECOVERY_

FILE_DEST is not dynamic, while DB_RECOVERY_FILE_DEST_SIZE is dynamic. To configure the FRA, do the following:

1. Create the base FRA directory:

E:\>mkdir \oracledb\flashback

E:\>

2. Log into SQL*Plus:

E:\>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Sat Jan 25 15:53:42 2014

Enter user-name: sys@test as sysdba

Enter password:

Connected to:

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

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

SQL>

3. Now use the alter system command to set the parameter DB_RECOVERY_FILE_DEST to

\oracledb\flashback and DB_RECOVERY_FILE_DEST_SIZE to 400GB.

sql>alter system set db_recovery_file_dest_size=400GB;

sql>alter system set db_recovery_file_dest=’ \oracledb\flashback’ scope=spfile;

4. Now shut down and restart the database. Once the database has been restarted, the

FRA will become operational.

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 2538741760 bytes

Fixed Size 2257872 bytes

Variable Size 889195568 bytes

Database Buffers 1627389952 bytes

Redo Buffers 19898368 bytes

Database mounted.

Database opened.

SQL>

Saturday, January 18, 2014

HOW TO CONFIGURE RMAN

HOW TO CONFIGURE RMAN:

1. Start RMAN from the command line:

E:\app\Administrator\product\11.2.0\dbhome_1\BIN>rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Sat Jan 25 16:11:39 2014

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

connected to target database: ASM (DBID=1255844286)

RMAN>

2. Display your RMAN configuration (yours may look different than our output—

that’s okay):

RMAN> show all;

RMAN configuration parameters for database with db_unique_name ORCL are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1;

CONFIGURE BACKUP OPTIMIZATION ON;

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP OFF;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE

TYPE DISK TO ‘%F’; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP

TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF;

CONFIGURE ENCRYPTION ALGORITHM ‘AES128’;

CONFIGURE COMPRESSION ALGORITHM ‘zlib’;

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘E:\app\Administrator\product\11.2.0\dbhome_1\DATABASE\SNCFORCL.ORA’; # default

3. Configure the retention policy to redundancy of 2:

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

old RMAN configuration parameters:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1;

new RMAN configuration parameters:

CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

new RMAN configuration parameters are successfully stored

4. Configure control-file autobackups on:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP on;

old RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP OFF;

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters are successfully stored

5. Configure for compressed backup sets:

RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP

TYPE TO COMPRESSED BACKUPSET;

new RMAN configuration parameters:

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE

TO COMPRESSED BACKUPSET;

new RMAN configuration parameters are successfully stored

6. Create a directory to hold the snapshot control file (you will want to use your own directory paths, of course):

RMAN> host “mkdir \rman”;

host command complete

RMAN> host “mkdir \rman\snapshot”;

host command complete

7. Configure RMAN so the snapshot control file will be created in the new directory:

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘E:\rman\snapshot’;

new RMAN configuration parameters:

CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘E:\rman\snapshot’;

new RMAN configuration parameters are successfully stored

8. Exit RMAN:

RMAN> exit

Recovery Manager complete.

Wednesday, January 15, 2014

HOW TO RECOVER REDOLOGS

If You have lost all your online redo logs. As a result, your database has crashed. You have

tried to restart the database and clear the online redo log files, but when you try to open the

database you get the following error.

SQL> startup

ORACLE instance started.

Total System Global Area 167395328 bytes

Fixed Size 1298612 bytes

Variable Size 142610252 bytes

Database Buffers 20971520 bytes

Redo Buffers 2514944 bytes

Database mounted.

ORA-00313: open failed for members of log group 2 of thread 1

ORA-00312: online log 2 thread 1: ‘/oracle01/oradata/orcl/redo02a.log’

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

ORA-00312: online log 2 thread 1: ‘/oracle01/oradata/orcl/redo02.log’

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

SQL> alter database clear logfile group 2;

alter database clear logfile group 2

ERROR at line 1:

ORA-01624: log 2 needed for crash recovery of instance orcl (thread 1)

ORA-00312: online log 2 thread 1: ‘/oracle01/oradata/orcl/redo02.log’

ORA-00312: online log 2 thread 1: ‘/oracle01/oradata/orcl/redo02a.log’

Steps for Recovery.

11) Restore the last full database backup.

22) Restore all archived redo logs generated during and after the last full database backup.

33) Issue the Startup Mount command to mount the database.

44) Perform a point-in-time recovery, applying all archived redo logs that are available.

55) Open the database using the alter database open resetlogs command.

Monday, January 6, 2014

ORA-03113: end-of-file on communication channel Process ID: 1380 Session ID: 191 Serial number: 3 When archive destination is full

Delete Archivelog Using RMAN in Oracle

SQL> startup
ORACLE instance started.

Total System Global Area 1636814848 bytes
Fixed Size 2176248 bytes
Variable Size 1140853512 bytes
Database Buffers 486539264 bytes
Redo Buffers 7245824 bytes

Database mounted.

ORA-03113: end-of-file on communication channel
Process ID: 1380
Session ID: 191 Serial number: 3

Check in Alert Log file …….

NOTE: initiating MARK startup

Starting background process MARK

Mon Jan 06 16:41:54 2014

MARK started with pid=19, OS id=2952

NOTE: MARK has subscribed

Mon Jan 06 16:41:54 2014

starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...

starting up 1 shared server(s) ...

ORACLE_BASE from environment = E:\app\Administrator

Mon Jan 06 16:41:56 2014

ALTER DATABASE MOUNT /* db agent *//* {0:1:28} */

NOTE: Loaded library: System

SUCCESS: diskgroup DATA was mounted

NOTE: dependency between database ASM and diskgroup resource ora.DATA.dg is established

Successful mount of redo thread 1, with mount id 3855157908

Database mounted in Exclusive Mode

Lost write protection disabled

Completed: ALTER DATABASE MOUNT /* db agent *//* {0:1:28} */

ALTER DATABASE OPEN /* db agent *//* {0:1:28} */

LGWR: STARTING ARCH PROCESSES

Mon Jan 06 16:42:02 2014

ARC0 started with pid=26, OS id=8080

ARC0: Archival started

ARC0: Becoming the 'no FAL' ARCH

ARC0: Becoming the 'no SRL' ARCH

LGWR: STARTING ARCH PROCESSES COMPLETE

ARCH: Error 19504 Creating archive log file to 'E:\ARCHIVE\ARC0000003106_0832442370.0001'

ARC0: Error 19504 Creating archive log file to 'E:\ARCHIVE\ARC0000003107_0832442370.0001'

Errors in file E:\APP\ADMINISTRATOR\diag\rdbms\asm\asm\trace\asm_ora_1500.trc:

ORA-16038: log 1 sequence# 3106 cannot be archived

ORA-19504: failed to create file ""

ORA-00312: online log 1 thread 1: '+DATA/asm/onlinelog/group_1.261.832442371'

USER (ospid: 1500): terminating the instance due to error 16038

Mon Jan 06 16:42:04 2014

System state dump requested by (instance=1, osid=1500), summary=[abnormal instance termination].

System State dumped to trace file E:\APP\ADMINISTRATOR\diag\rdbms\asm\asm\trace\asm_diag_1608.trc

Dumping diagnostic data in directory=[cdmp_20140106164204], requested by (instance=1, osid=1500), summary=[abnormal instance termination].

Mon Jan 06 16:42:11 2014

Instance terminated by USER, pid = 1500

SQL>Shutdown Immediate;

SQL> startup mount

Then follow the below steps….

SQL> HOST

E:\app\Administrator\product\11.2.0\dbhome_1\BIN>rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Jan 6 17:36:48 2014

connected to target database: ASM (DBID=1255844286)

RMAN>

Archivelog List Commands

RMAN>list archivelog all;
RMAN>list copy of archivelog until time ‘SYSDATE-10′;
RMAN>list copy of archivelog from time ‘SYSDATE-10′
RMAN>list copy of archivelog from time ‘SYSDATE-10′ until time ‘SYSDATE-2′;
RMAN>list copy of archivelog from sequence 1000;
RMAN>list copy of archivelog until sequence 1500;
RMAN>list copy of archivelog from sequence 1000 until sequence 1500;

Archivelog Delete Commands

RMAN>delete archivelog all;
RMAN>delete archivelog until time ‘SYSDATE-10′;
RMAN>delete archivelog from time ‘SYSDATE-10′
RMAN>delete archivelog from time ‘SYSDATE-10′ until time ‘SYSDATE-2′;
RMAN>delete archivelog from sequence 1000;
RMAN>delete archivelog until sequence 1500;
RMAN>delete archivelog from sequence 1000 until sequence 1500;

Note : Also, you can use noprompt statement for do not yes-no question.

RMAN>delete noprompt archivelog until time ‘SYSDATE-10′;

RMAN> quit

Recovery Manager complete.

E:\app\Administrator\product\11.2.0\dbhome_1\BIN>sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Mon Jan 6 17:39:15 2014

Enter user-name: sys as sysdba

Enter password:

Connected to:

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

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

SQL>alter database open;

Now Issue is solved…..

Please share your valuable feedback.