Tales From A Lazy Fat DBA

Loves all databases! – Its all about performance, troubleshooting & much more …. ¯\_(ツ)_/¯

  • Prashant Dixit is the 'FatDBA' ...
  • Follow me on Twitter

Archive for December, 2012

AMM (Automatic Memory Management) & ASMM (Automatic Shared MM)

Posted by FatDBA on December 31, 2012

Evolution of Memory Management Features:

Memory management has evolved with each database release:

Oracle Database 10g
Automatic Shared Memory Management (ASMM) was introduced in 10g. You enable the automatic shared memory management feature by setting the SGA_TARGET parameter to a non-zero value.

Oracle Database 11g
Automatic Memory Management is being introduced in 11g. This enables automatic tuning of PGA and SGA with use of two new parameters named MEMORY_MAX_TARGET and MEMORY_TARGET.

Oracle Database 11g supports various memory management methods, which are chosen by initialization parameter
settings. Oracle recommends that you enable the automatic memory management method.
1. Automatic Memory Management – For Both the SGA and Instance PGA
2. Automatic Shared Memory Management – For the SGA
3. Manual Shared Memory Management – For the SGA
4. Automatic PGA Memory Management – For the Instance PGA
5. Manual PGA Memory Management – For the Instance PGA

Untitled

Automatic Memory Management – For Both the SGA and Instance PGA

Beginning with Oracle Database 11g, Oracle Database can manage the SGA memory and instance PGA memory completely automatically. You designate only the total memory size to be used by the instance, and Oracle Database dynamically exchanges memory between the SGA and the instance PGA as needed to meet processing demands.
This capability is referred to as automatic memory management. With this memory management method, the database also dynamically tunes the sizes of the individual SGA components and the sizes of the individual PGAs. To achieve this, two new parameters have been introduced named MEMORY_MAX_TARGET and MEMORY_TARGET. To do so (on most platforms), you set only a target memory size initialization parameter (MEMORY_TARGET) and optionally a maximum memory size initialization parameter (MEMORY_MAX_TARGET).

Switching to Automatic Memory Management

1)Check the current values configured for SGA_TARGET and PGA_AGGREGATE_TARGET.

SQL>SHOW PARAMETER TARGET

NAME TYPE VALUE
—————————— ———– —————-
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
memory_max_target big integer 0
memory_target big integer 0
pga_aggregate_target big integer 200M
sga_target big integer 500M

Add the values of pga_aggregate_target and sga_target. In our case it would sum to 700 M

2)Decide on a maximum amount of memory that you would want to allocate to the database which will determine the maximum value for the sum of the SGA and instance PGA sizes. In our case we decide to set to 808M

3)Change the parameter in initialization parameter file.

Using Spfile
==============
SQL>ALTER SYSTEM SET MEMORY_MAX_TARGET = 808M SCOPE = SPFILE;
SQL>ALTER SYSTEM SET MEMORY_TARGET = 808M SCOPE = SPFILE;
SQL>ALTER SYSTEM SET SGA_TARGET =0 SCOPE = SPFILE;
SQL>ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0 SCOPE = SPFILE;

Using Pfile
==============
If you have started the instance with Pfile, then edit the pfile and set the parameters manually
MEMORY_MAX_TARGET = 808M
MEMORY_TARGET = 808M
SGA_TARGET =0
PGA_AGGREGATE_TARGET = 0

In case you do not specify any value for MEMORY_MAX_TARGET and only use MEMORY_TARGET then database automatically sets MEMORY_MAX_TARGET to the value of MEMORY_TARGET. If you omit the line for MEMORY_TARGET and include a value for MEMORY_MAX_TARGET, the MEMORY_TARGET parameter defaults to zero. After startup, you can then dynamically change MEMORY_TARGET to a non-zero value, provided that it does not exceed the value of
MEMORY_MAX_TARGET.
MEMORY_MAX_TARGET is a static parameter i.e it cannot be changed Dynamically and Instance has to be bounced for modifying the value. So ensure that you have set it to appropriate value.

4)Shutdown and startup the database
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area 845348864 bytes
Fixed Size 1303188 bytes
Variable Size 469765484 bytes
Database Buffers 369098752 bytes
Redo Buffers 5181440 bytes

SQL> show parameter target

NAME TYPE VALUE
———————————— ———– ——————————
archive_lag_target integer 0
db_flashback_retention_target integer 1440
fast_start_io_target integer 0
fast_start_mttr_target integer 0
memory_max_target big integer 808M
memory_target big integer 808M
pga_aggregate_target big integer 0
sga_target big integer 0

Automatic Shared Memory Management – For the SGA
If you want to exercise more direct control over the size of the SGA, you can disable automatic memory management and enable automatic shared memory management.This feature was introduced in 10g with a parameter known as SGA_TARGET. When automatic SGA memory management is enabled, the sizes of the different SGA components are flexible and can adapt to the needs of current workload without requiring any additional configuration. Please refer to following document for setting SGA_TARGET

In case you have enabled Automatic Memory Management , then to switch to Automatic Shared Memory Management , please follow below procedure

SQL>Alter system set MEMORY_TARGET=0 scope=both;
SQL>Alter system set SGA_TARGET=500M scope=both;

Posted in Uncategorized | Tagged: , | 3 Comments »

ORA-00845: MEMORY_TARGET not supported on this system

Posted by FatDBA on December 30, 2012

Today while working on one of my practice machine, I’ve started receiving error when tried to start-up one of the instance which is on Cooked File system. Error reads about MEMORY_TARGET.  To be precise about the error message and code, below is what i was getting second ago.

SQL> startup
ORA-00845: MEMORY_TARGET not supported on this system

Below are the steps in sequence what i performed to test and mitigate the error.

1. I’ve checked the pfile of the instance to check MEMORY_TARGET entires and values to discover if there is any problem with the values assigned (I have the AMM enabled in my machine)
*.memory_target=715128832

2. Then i’ve checked error details more closely using OERR utlity to find the cause of the problem and suggestions.

[oracle@localhost dbs]$ oerr ORA 845
00845, 00000, “MEMORY_TARGET not supported on this system”
// *Cause: The MEMORY_TARGET parameter was not supported on this operating system or /dev/shm was not sized correctly on Linux.
// *Action: Refer to documentation for a list of supported operating systems. Or, size /dev/shm to be at least the SGA_MAX_SIZE on each Oracle instance running on the system.

Error clearly points towards the /dev/shm size on OS and asking to set the size of it to atleast SGA_MAX_SIZE on instance running which is 684m.

3. Now when we have the Cause in hand let’s try to fix the problem.

let me check current settings.

[oracle@localhost dbs]$ df -h
Filesystem            Size    Used       Avail            Use%        Mounted on
/dev/mapper/VolGroup00-LogVol00
30G   23G        6.0G            80%         /
/dev/sda1              99M   55M       40M            58%          /boot
tmpfs                 664M  154M   510M         24%        /dev/shm        /*tmps (Temp File Storage area) is in MB’s – CAUSE CLEARED*/

And it’s 664M which is clearly below the SGA_MAX_SIZE = 684m

Some more checks:
[root@localhost ~]# more /etc/fstab
/dev/VolGroup00/LogVol00 /                       ext3    defaults        1 1
LABEL=/boot             /boot                   ext3    defaults        1 2
tmpfs                   /dev/shm                tmpfs   defaults        0 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0
/dev/VolGroup00/LogVol01 swap                    swap    defaults        0 0

4. Let’s try to add up some memory space to tmpfs area.
I’ve added exact 1GB to /etc/fstab entry. Let me check the change

[root@localhost ~]# vi /etc/fstab

[root@localhost ~]# more /etc/fstab
/dev/VolGroup00/LogVol00 /                       ext3    defaults        1 1
LABEL=/boot             /boot                   ext3    defaults        1 2
tmpfs                   /dev/shm                tmpfs   defaults,size=1G        0 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0
/dev/VolGroup00/LogVol01 swap                    swap    defaults        0 0

Alrighty, to fix the settings/changes let’s remount /dev/shm

[root@localhost ~]# mount -o remount /dev/shm
[root@localhost ~]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
30G   23G  6.0G  80% /
/dev/sda1              99M   55M   40M  58% /boot
tmpfs                 1.0G  154M  4.9G   4% /dev/shm

D.O.N.E

5. Now we are done with the change, let’s try to start the instance once again.

[oracle@localhost ~]$ . oraenv
ORACLE_SID = [orcl] ? orcl
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_2 is /u01/app/oracle

SQL> startup
ORACLE instance started.

Total System Global Area  715616256 bytes
Fixed Size                  1338924 bytes
Variable Size             482345428 bytes
Database Buffers          226492416 bytes
Redo Buffers                5439488 bytes
Database mounted.
Database opened.

It’s UP!!

Posted in Advanced | Tagged: | Leave a Comment »

Authentication: Password File and OS Based in Oracle.

Posted by FatDBA on December 28, 2012

Diffrence between “/ as sysdba” and “sys/pswrd as sysdba”

— sqlplus “/as sysdba” it mean is you use OS authorization and your user must member of dba(ORA_DBA) OS group,else operation will fail.
— sqlplus sys/pass@sid as sysdba it mean is you use passwordfile authorization .And in this case you need properly configure this file also need set remote_login_passwordfile= EXCLUSIVE or SHARED.

And @sid (sqlplus sys/pass@sid as sysdba) also need listener to be up, can be done either in the server or client side based on the entry in your tnsnames.ora otherwise you’ll receive error message on the SQL terminal the moment you try logging – ORA-12541: TNS:no listener
sqlplus / as sysdba does not require listener to be up but has to be done in the server side.

Posted in Basics | Tagged: | Leave a Comment »

ASM: File Extensions

Posted by FatDBA on December 27, 2012

If you have the ASM configured in your environment, it’s always hard to remember the ASM file names unless you have aliased the entires. But this is what i discovered while working on one of my Test machine which has the ASM Configured. I found filenames and extensions fully qualified but not just another techical opaques.

These are some of the datafiles which are part of my ASM and available on the diskgroup.

SQL> select name from v$datafile;

NAME
——————————————————————————–
+TESTDB_DATA1/orcl/datafile/tesla.261.795016577
+TESTDB_DATA1/orcl/datafile/example12.256.794584325
+TESTDB_DATA1/orcl/datafile/dixy.257.794593881
+TESTDB_DATA1/orcl/datafile/users1111.258.794825249
+TESTDB_DATA1/orcl/datafile/text.259.794825753
+TESTDB_DATA1/orcl/datafile/test12345.260.794840557

eg:
Let’s dissect one of the file
+TESTDB_DATA1/orcl/datafile/dixy.257.794593881
Here if watch carefully:
+TESTDB_DATA1: DiskGroup Name (+ indicates the root of the ASM)
orcl: Name of the client database installed.
datafile: The File Type
dixy: Name of the Data File.
257: Unique File Number
794593881: Database Incarnation Number.

To conform you can also crosscheck and validate using v$database_incarnation.

Posted in Advanced | Tagged: | Leave a Comment »

LMT vs DMT Tablespaces.

Posted by FatDBA on December 26, 2012

When Oracle allocates space to a segment (like a table or index), a group of contiguous free blocks, called an extent, is added to the segment. Metadata regarding extent allocation and unallocated extents are either stored in the data dictionary, or in the tablespace itself. Tablespaces that record extent allocation in the dictionary, are called dictionary managed tablespaces, and tablespaces that record extent allocation in the tablespace header, are called locally managed tablespaces.

SQL> select tablespace_name, extent_management, allocation_type from dba_tablespaces;

TABLESPACE_NAME                EXTENT_MAN ALLOCATIO
—————————— ———- ———
SYSTEM                         DICTIONARY USER
SYS_UNDOTS                     LOCAL      SYSTEM
TEMP                           LOCAL      UNIFORM

Oracle use the data dictionary (tables in the SYS schema) to track allocated and free extents for tablespaces that is in “dictionary managed” mode. Free space is recorded in the SYS.FET$ table, and used space in the SYS.UET$ table.

SQL> CREATE TABLESPACE etr1 DATAFILE ‘/oradata/etr1_01.dbf’ SIZE 80M
EXTENT MANAGEMENT DICTIONARY
Using LMT, each tablespace manages it’s own free and used space within a bitmap structure stored in one of the tablespace’s data files.

SQL> CREATE TABLESPACE etr2 DATAFILE ‘/oradata/etr2_01.dbf’ SIZE 80M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

 

Advantages in using Locally Managed Tablespaces Over Dictionary Managed Tablespaces

  • Because locally managed tablespaces do not record free space in data dictionary, it reduces contention on these tables.
  • Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents.
  • Sizes of extents that are managed locally can be determined automatically by the system. Alternatively, all extents can have the same size in a locally managed tablespace.
  • Reduced fragmentation

Posted in Basics | Tagged: | Leave a Comment »

Hot Backup & Fractured Blocks: Test

Posted by FatDBA on December 26, 2012

Fractured block in Oracle
A block in which the header and footer are not consistent at a given SCN. In a user-managed backup, an operating system utility can back up a datafile at the same time that DBWR is updating the file. It is possible for the operating system utility to read a block in a half-updated state, so that the block that is copied to the backup media is updated in its first half, while the second half contains older data. In this case, the block is fractured.
For non-RMAN backups, the ALTER TABLESPACE … BEGIN BACKUP or ALTER DATABASE BEGIN BACKUP command is the solution for the fractured block problem. When a tablespace is in backup mode, and a change is made to a data block, the database logs a copy of the entire block image before the change so that the database can reconstruct this block if media recovery finds that this block was fractured.
The block that the operating system reads can be split, that is, the top of the block is written at one point in time while the bottom of the block is written at another point in time. If you restore a file containing a fractured block and Oracle reads the block, then the block is considered a corrupt.

Let’s perform a test:

–> Before ‘Begin Backup Mode’:

SQL> set autotrace trace stat
SQL> update etr set team=’Oracle’ where id=’7′;

1 row updated.
Statistics
———————————————————-
          0  recursive calls
          1  db block gets
          1  consistent gets
          0  physical reads
        300  redo size
        669  bytes sent via SQL*Net to client
        580  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

: It shows redo size=300 (Normal)

–> Let me put the tablespace in ‘Begin Backup’ Mode and try to executea DML again:
SQL> alter tablespace users begin backup;
Tablespace altered.

SQL> update etr set team=’Oracle’ where id=’1′;
1 row updated.
Statistics
———————————————————-
          1  recursive calls
          6  db block gets
          1  consistent gets
          0  physical reads
      17480  redo size
        669  bytes sent via SQL*Net to client
        580  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed
: Check the size of Redo here (17480),  it’s actually the size of the datablock (+Normal redo) where the table column exits and a copy of the block is moved to the redo log buffer .

 
–> Let me try to execute the same DML statement again on same table
SQL> /

1 row updated.
Statistics
———————————————————-
          0  recursive calls
          1  db block gets
          1  consistent gets
          0  physical reads
        300  redo size
        669  bytes sent via SQL*Net to client
        580  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed
: Now Redo size is again back to it’s original value (300).

Hence proved that rather pushing changed vectors/values in redo log buffer, oracle actually copies the entire block during initial operations (Reason of large REDO generation) and will not repeat the same for all subsequent operations on the same block.

ALTER TABLESPACE <> BEGIN BACKUP
is the solution to the Fractured Block problem which could have create inconsistencies in case of user managed backup’s which require OS commands to use.

Posted in Advanced | Tagged: , | Leave a Comment »

Export failed when trying using SYS user. * (ORA-28009: connection as SYS should be as SYSDBA or SYSOPER)

Posted by FatDBA on December 23, 2012

[oracle@localhost ~]$ expdp sys/oracle90 directory=dpump dumpfile=emp.dmp tables=larry.emp reuse_dumpfiles=true

Errors:
UDE-28009: operation generated ORACLE error 28009
ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

SQL> show parameter dictionary

NAME                                 TYPE        VALUE
———————————— ———– ——————————
O7_DICTIONARY_ACCESSIBILITY          boolean     FALSE

 

With this parameter in False, it will not allow accessing anyone dictionary views, tables objects in SYS schema without SYSDBA/SYSOPER roles. O7_DICTIONARY_ACCESSIBILITY controls restrictions on SYSTEM privileges. If the parameter is set to true, access to objects in the SYS schema is allowed (Oracle7 behavior). The default setting of false ensures that system privileges that allow access to objects in “any schema” do not allow access to objects in the SYS schema.

 

SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=TRUE scope=spfile;
System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area  523108352 bytes
Fixed Size                  1337632 bytes
Variable Size             406849248 bytes
Database Buffers          109051904 bytes
Redo Buffers                5869568 bytes
Database mounted.
Database opened.

SQL> show parameter dictionary

NAME                                 TYPE        VALUE
———————————— ———– ——————————
O7_DICTIONARY_ACCESSIBILITY          boolean     TRUE

[oracle@localhost ~]$ expdp sys/oracle90 directory=dpump dumpfile=emp.dmp tables=larry.emp reuse_dumpfiles=true

Export: Release 11.2.0.1.0 – Production on Sun Dec 23 12:12:29 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

ORA-28002: the password will expire within 7 days            //   **** This error points to change password for user the table belongs to ‘Larry’****

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “SYS”.”SYS_EXPORT_TABLE_01″:  sys/******** directory=dpump dumpfile=emp.dmp tables=larry.emp reuse_dumpfiles=true
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported “LARRY”.”EMP”                               8.046 KB       2 rows
Master table “SYS”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/tmp/emp.dmp
Job “SYS”.”SYS_EXPORT_TABLE_01″ successfully completed at 12:12:47

 

Or —->>>>
Export it with SYSDBA role.

[oracle@localhost ~]$ expdp \’sys/oracle90 as sysdba\’ directory=dpump dumpfile=emp.dmp tables=larry.emp reuse_dumpfiles=true

Export: Release 11.2.0.1.0 – Production on Sun Dec 23 16:42:15 2012

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “SYS”.”SYS_EXPORT_TABLE_01″:  “sys/******** AS SYSDBA” directory=dpump dumpfile=emp.dmp tables=larry.emp reuse_dumpfiles=true
Estimate in progress using BLOCKS method…

Posted in Advanced | Tagged: | 2 Comments »

Difference: DBA & oinstall Groups

Posted by FatDBA on December 9, 2012

Here i tried to highlight some differences between DBA and oinstall groups
dba – This group identifies operating system user accounts that have database administrative privileges (the SYSDBA privilege).
oinstall – This group owns the Oracle Inventory, which is a catalog of all Oracle software installed on the system

Posted in Basics | Tagged: | Leave a Comment »

What is this ‘lkORCL’ under my /dbs Folder ?

Posted by FatDBA on December 9, 2012

Ever noticed while exploring ‘dbs’  folder under ORACLE_HOME location about a file name starting from ‘lk’ or ‘kl’ followed by SID in uppercase e.g lkORCL. Let me paste what are my DB’s dbs contents.

[oracle@localhost dbs]$ ls
hc_orcl.dat  init.ora  initorcl.ora  lkORCL  orapworcl  peshm_orcl_0  snapcf_orcl.f  spfileorcl.ora

These are some default files which always created under dbs, everytime your Database restarts and it if you ever tries opening this file it reads “DO NOT DELETE THIS FILE!”. lk<sid> file is used to lock shared memory for specific database but there is still no harm in deleting this file, database will keep on working normal even after deletion and will create it by it’s own after DB bounce.

for example, in our case
-rw-rw—- 1 oracle oinstall 24 Oct 9 18:04 lkORCL

(oracle owns the file and has 660 level permission)
lkORCL is used to lock shared memory for ORCL database in RAM.

Posted in Basics | Tagged: | 1 Comment »

11g Data Recovery Advisor: Diagnosing and Repairing Failures

Posted by FatDBA on December 9, 2012

Oracle’s 11g New Feature ‘Data Recovery Advisor’ is one of the most important tool introduced by The Red Giants with the release of r2. I will of course consider this functionality as one of the most impressive and best among others added in 11g r2 package. The Repair Advisor can take away lot of the stress associated with peforming backup and recovery by diagnosing what is wrong as well as presenting us with the syntax as well to execute the commands to restore and recover as the case may be. Under pressure, everyone can make mistakes and it is comforting to know that there is a tool which can really he;p the DBA.

The Data Recovery Advisor can be used via OEM Database or Grid Control or via the RMAN command line interface.

 

Let me Explain it using a real time issue: (Missed one of the control file).
Here I’ve intentionally deleted Control File 2 (Below provided was past status)

SQL> select name from v$controlfile;

NAME
——————————————————————————–
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/flash_recovery_area/orcl/control02.ctl

 

Once the file is removed or corrupted it will start throwing below provided error message with code to check error log (Alert Log)

ORA-00205: error in identifying control file, check alert log for more info

Launch RMAN console and connect to the database (NO MOUNT Mode – Pretty Obvious!!) and check failures using ‘list failure’ command and will show you problem detected by DB engine.

 

RMAN> list failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
———- ——– ——— ————- ——-
322        CRITICAL OPEN      09-DEC-12     Control file /u01/app/oracle/flash_recovery_area/orcl/control02.ctl is missi                                                                     ng

RMAN> advise failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
———- ——– ——— ————- ——-
322        CRITICAL OPEN      09-DEC-12     Control file /u01/app/oracle/flash_recovery_area/orcl/control02.ctl is missing

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
no manual actions available

Automated Repair Options
========================
Option Repair Description
—— ——————
1      Use a multiplexed copy to restore control file /u01/app/oracle/flash_recovery_area/orcl/control02.ctl
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_2219809221.hm

 

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl/orcl/hm/reco_2219809221.hm

contents of repair script:
# restore control file using multiplexed copy
restore controlfile from ‘/u01/app/oracle/oradata/orcl/control01.ctl’;
sql ‘alter database mount’;

Do you really want to execute the above repair (enter YES or NO)? YES
executing repair script

Starting restore at 09-DEC-12
using channel ORA_DISK_1

channel ORA_DISK_1: copied control file copy
output file name=/u01/app/oracle/oradata/orcl/control01.ctl
output file name=/u01/app/oracle/flash_recovery_area/orcl/control02.ctl
Finished restore at 09-DEC-12

sql statement: alter database mount
released channel: ORA_DISK_1
repair failure complete

Do you want to open the database (enter YES or NO)? YES
database opened

It’s back!!!!

SQL> select name from v$controlfile;

NAME
——————————————————————————–
/u01/app/oracle/oradata/orcl/control01.ctl
/u01/app/oracle/flash_recovery_area/orcl/control02.ctl

Posted in Advanced | Tagged: , | Leave a Comment »

 
%d bloggers like this: