The most common size for an HA cluster is a two-node cluster, since that's the minimum required to provide redundancy, but many clusters consist of many more, sometimes dozens of nodes. Such configurations can sometimes be categorized into one of the following models:
* Active/Active — Traffic intended for the failed node is either passed onto an existing node or load balanced across the remaining nodes. This is usually only possible when the nodes utilize a homogeneous software configuration.
* Active/Passive — Provides a fully redundant instance of each node, which is only brought online when its associated primary node fails. This configuration typically requires the most amount of extra hardware.
* N+1 — Provides a single extra node that is brought online to take over the role of the node that has failed. In the case of heterogeneous software configuration on each primary node, the extra node must be universally capable of assuming any of the roles of the primary nodes it is responsible for. This normally refers to clusters which have multiple services running simultaneously; in the single service case, this degenerates to Active/Passive.
* N+M — In cases where a single cluster is managing many services, having only one dedicated failover node may not offer sufficient redundancy. In such cases, more than one (M) standby servers are included and available. The number of standby servers is a tradeoff between cost and reliability requirements.
* N-to-1 — Allows the failover standby node to become the active one temporarily, until the original node can be restored or brought back online, at which point the services or instances must be failed-back to it in order to restore High Availability.
* N-to-N — A combination of Active/Active and N+M clusters, N to N clusters redistribute the services or instances from the failed node among the remaining active nodes, thus eliminating (as with Active/Active) the need for a 'standby' node, but introducing a need for extra capacity on all active nodes.
Friday, November 20, 2009
Thursday, November 19, 2009
Database Replay - Ora 11g
“Simulating production load is not possible” , you might have heard these word.
In one project, where last 2 year management want to migrate from UNIX system to Linux system ( RAC ) , but they still testing because they are not sure where this Linux Boxes where bale to handle load or not. They have put lot of efforts and time in load testing and functional testing etc, but still not le gain confidence.
After using these feature of 11g , they will gain confidence and will able to migrate to Linux with full confidence and will know how there system will behave after migration/upgrade.
As per datasheet given on OTN
Database Replay workload capture of external clients is performed at the database server level. Therefore, Database Replay can be used to assess the impact of any system changes below the database tier level such as below:
* Database upgrades, patches, parameter, schema changes, etc.
* Configuration changes such as conversion from a single instance to RAC etc.
* Storage, network, interconnect changes
* Operating system, hardware migrations, patches, upgrades, parameter changes
DB replay does this by capturing a workload on the production system with negligible performance overhead( My observation is 2-5% more CPU usage ) and replaying it on a test system with the exact timing, concurrency, and transaction characteristics of the original workload. This makes possible complete assessment of the impact of the change including undesired results; new contentions points or performance regressions. Extensive analysis and reporting ( AWR , ADDM report and DB replay report) is provided to help identify any potential problems, such as new errors encountered and performance divergences. The ability to accurately capture the production workload results in significant cost and timesaving since it completely eliminates the need to develop simulation workloads or scripts. As a result, realistic testing of even complex applications using load simulation tools/scripts that previously took several months now can be accomplished at most in a few days with Database Replay and with minimal effort. Thus using Database Replay, businesses can incur much lower costs and yet have a high degree of confidence in the overall success of the system change and significantly reduce production deployment
Steps for Database Replay
1.
Workload Capture
Database are tracked and stored in binary files, called capture files, on the file system. These files contain all relevant information about the call needed for replay such as SQL text, bind values, wall clock time, SCN, etc.
1) Backup production Database #
2) Add/remove filter ( if any you want )
By default, all user sessions are recorded during workload capture. You can use workload filters to specify which user sessions to include in or exclude from the workload. Inclusion filters enable you to specify user sessions that will be captured in the workload. This is useful if you want to capture only a subset of the database workload.
For example , we don't want to capture load for SCOTT user
BEGIN
DBMS_WORKLOAD_CAPTURE.ADD_FILTER (
fname => 'user_scott',
fattribute => 'USER',
fvalue => 'SCOTT');
END;
Here filter name is "user_scott" ( user define name)
3) Create directory make sure enough space is there
CREATE OR REPLACE DIRECTORY db_replay_dir
AS '/u04/oraout/test/db-replay-capture';
4) Capture workload
BEGIN
DBMS_WORKLOAD_CAPTURE.start_capture (
name => capture_testing',dir=>'DB_REPLAY_DIR',
duration => NULL );
END;
Duration => NULL mean , it will capture load till we stop with below mentioned manual SQL command. Duration is optional input to specify the duration (in seconds) , default is NULL
5) Finish capture
BEGIN
DBMS_WORKLOAD_CAPTURE.finish_capture;
END;
# Take backup of production before Load capture, so we can restore database on test environment and will run replay on same SCN level of database to minimize data divergence
Note as per Oracle datasheet
The workload that has been captured on Oracle Database release 10.2.0.4 and higher can also be replayed on Oracle Database 11g release.So , I think , It simply mean NEW patch set 10.2.0.4 will support capture processes. Is it mean Current patch set (10.2.0.3) not support load capture ??????
2. Workload Processing
Once the workload has been captured, the information in the capture files has to be processed preferably on the test system because it is very resource intensive job. This processing transforms the captured data and creates all necessary metadata needed for replaying the workload.
exec DBMS_WORKLOAD_REPLAY.process_capture('DB_REPLAY_DIR');
3.
Workload Replay
1) Restore database backup taken step one to test system and start Database
2) Initialize
BEGIN
DBMS_WORKLOAD_REPLAY.initialize_replay (
replay_name => 'TEST_REPLAY',
replay_dir => 'DB_REPLAY_DIR');
END;
3) Prepare
exec DBMS_WORKLOAD_REPLAY.prepare_replay(synchronization => TRUE)
4) Start clients
$ wrc mode=calibrate replaydir=/u03/oradata/test/db-replay-capture
Workload Replay Client: Release 11.1.0.6.0 - Production on Wed Dec 26 00:31:41 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Report for Workload in: /u03/oradata/test/db-replay-capture
-----------------------
Recommendation:
Consider using at least 1 clients divided among 1 CPU(s).
Workload Characteristics:
- max concurrency: 1 sessions
- total number of sessions: 7
Assumptions:
- 1 client process per 50 concurrent sessions
- 4 client process per CPU
- think time scale = 100
- connect time scale = 100
- synchronization = TRUE
$ wrc system/pass mode=replay replaydir=/u03/oradata/test/db-replay-capture
Workload Replay Client: Release 11.1.0.6.0 - Production on Wed Dec 26 00:31:52 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Wait for the replay to start (00:31:52)
5) Start Replay
BEGIN
DBMS_WORKLOAD_REPLAY.start_replay;
END;
/
$ wrc system/pass mode=replay replaydir=/u03/oradata/test/db-replay-capture
Workload Replay Client: Release 11.1.0.6.0 - Production on Wed Dec 26 00:31:52 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Wait for the replay to start (00:31:52)
Replay started (00:33:32)
Replay finished (00:42:52)
4.
Analysis and Reporting
Generate AWR , ADDM and DB reply report and compare with data gathered on production for same time period when load was captured on Production database. For Database Replay Report run following command
SQL> COLUMN name FORMAT A20
SQL> SELECT id, name FROM dba_workload_replays;
ID NAME
---------- --------------------
1 TEST_REPLAY
DECLARE
v_report CLOB;
BEGIN
v_report := DBMS_WORKLOAD_replay.report(
replay_id => 1,
format=>DBMS_WORKLOAD_CAPTURE.TYPE_HTML
);
dbms_output.put_line(l_report);
END;
/
In one project, where last 2 year management want to migrate from UNIX system to Linux system ( RAC ) , but they still testing because they are not sure where this Linux Boxes where bale to handle load or not. They have put lot of efforts and time in load testing and functional testing etc, but still not le gain confidence.
After using these feature of 11g , they will gain confidence and will able to migrate to Linux with full confidence and will know how there system will behave after migration/upgrade.
As per datasheet given on OTN
Database Replay workload capture of external clients is performed at the database server level. Therefore, Database Replay can be used to assess the impact of any system changes below the database tier level such as below:
* Database upgrades, patches, parameter, schema changes, etc.
* Configuration changes such as conversion from a single instance to RAC etc.
* Storage, network, interconnect changes
* Operating system, hardware migrations, patches, upgrades, parameter changes
DB replay does this by capturing a workload on the production system with negligible performance overhead( My observation is 2-5% more CPU usage ) and replaying it on a test system with the exact timing, concurrency, and transaction characteristics of the original workload. This makes possible complete assessment of the impact of the change including undesired results; new contentions points or performance regressions. Extensive analysis and reporting ( AWR , ADDM report and DB replay report) is provided to help identify any potential problems, such as new errors encountered and performance divergences. The ability to accurately capture the production workload results in significant cost and timesaving since it completely eliminates the need to develop simulation workloads or scripts. As a result, realistic testing of even complex applications using load simulation tools/scripts that previously took several months now can be accomplished at most in a few days with Database Replay and with minimal effort. Thus using Database Replay, businesses can incur much lower costs and yet have a high degree of confidence in the overall success of the system change and significantly reduce production deployment
Steps for Database Replay
1.
Workload Capture
Database are tracked and stored in binary files, called capture files, on the file system. These files contain all relevant information about the call needed for replay such as SQL text, bind values, wall clock time, SCN, etc.
1) Backup production Database #
2) Add/remove filter ( if any you want )
By default, all user sessions are recorded during workload capture. You can use workload filters to specify which user sessions to include in or exclude from the workload. Inclusion filters enable you to specify user sessions that will be captured in the workload. This is useful if you want to capture only a subset of the database workload.
For example , we don't want to capture load for SCOTT user
BEGIN
DBMS_WORKLOAD_CAPTURE.ADD_FILTER (
fname => 'user_scott',
fattribute => 'USER',
fvalue => 'SCOTT');
END;
Here filter name is "user_scott" ( user define name)
3) Create directory make sure enough space is there
CREATE OR REPLACE DIRECTORY db_replay_dir
AS '/u04/oraout/test/db-replay-capture';
4) Capture workload
BEGIN
DBMS_WORKLOAD_CAPTURE.start_capture (
name => capture_testing',dir=>'DB_REPLAY_DIR',
duration => NULL );
END;
Duration => NULL mean , it will capture load till we stop with below mentioned manual SQL command. Duration is optional input to specify the duration (in seconds) , default is NULL
5) Finish capture
BEGIN
DBMS_WORKLOAD_CAPTURE.finish_capture;
END;
# Take backup of production before Load capture, so we can restore database on test environment and will run replay on same SCN level of database to minimize data divergence
Note as per Oracle datasheet
The workload that has been captured on Oracle Database release 10.2.0.4 and higher can also be replayed on Oracle Database 11g release.So , I think , It simply mean NEW patch set 10.2.0.4 will support capture processes. Is it mean Current patch set (10.2.0.3) not support load capture ??????
2. Workload Processing
Once the workload has been captured, the information in the capture files has to be processed preferably on the test system because it is very resource intensive job. This processing transforms the captured data and creates all necessary metadata needed for replaying the workload.
exec DBMS_WORKLOAD_REPLAY.process_capture('DB_REPLAY_DIR');
3.
Workload Replay
1) Restore database backup taken step one to test system and start Database
2) Initialize
BEGIN
DBMS_WORKLOAD_REPLAY.initialize_replay (
replay_name => 'TEST_REPLAY',
replay_dir => 'DB_REPLAY_DIR');
END;
3) Prepare
exec DBMS_WORKLOAD_REPLAY.prepare_replay(synchronization => TRUE)
4) Start clients
$ wrc mode=calibrate replaydir=/u03/oradata/test/db-replay-capture
Workload Replay Client: Release 11.1.0.6.0 - Production on Wed Dec 26 00:31:41 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Report for Workload in: /u03/oradata/test/db-replay-capture
-----------------------
Recommendation:
Consider using at least 1 clients divided among 1 CPU(s).
Workload Characteristics:
- max concurrency: 1 sessions
- total number of sessions: 7
Assumptions:
- 1 client process per 50 concurrent sessions
- 4 client process per CPU
- think time scale = 100
- connect time scale = 100
- synchronization = TRUE
$ wrc system/pass mode=replay replaydir=/u03/oradata/test/db-replay-capture
Workload Replay Client: Release 11.1.0.6.0 - Production on Wed Dec 26 00:31:52 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Wait for the replay to start (00:31:52)
5) Start Replay
BEGIN
DBMS_WORKLOAD_REPLAY.start_replay;
END;
/
$ wrc system/pass mode=replay replaydir=/u03/oradata/test/db-replay-capture
Workload Replay Client: Release 11.1.0.6.0 - Production on Wed Dec 26 00:31:52 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Wait for the replay to start (00:31:52)
Replay started (00:33:32)
Replay finished (00:42:52)
4.
Analysis and Reporting
Generate AWR , ADDM and DB reply report and compare with data gathered on production for same time period when load was captured on Production database. For Database Replay Report run following command
SQL> COLUMN name FORMAT A20
SQL> SELECT id, name FROM dba_workload_replays;
ID NAME
---------- --------------------
1 TEST_REPLAY
DECLARE
v_report CLOB;
BEGIN
v_report := DBMS_WORKLOAD_replay.report(
replay_id => 1,
format=>DBMS_WORKLOAD_CAPTURE.TYPE_HTML
);
dbms_output.put_line(l_report);
END;
/
Friday, November 13, 2009
Listener
when attempting to rename the listener.log file while the TNS listener process is running. The Oracle TNS listener process holds an open handle to the file. Under UNIX, you CAN remove the file, but Oracle will not re-create the file when it attempts to write to it again. The TNS listener will need to be stopped and restarted in order for it to create the new listener.log file.
Well, here is a solution for renaming (or removing) the listener.log file without having to stop and start the TNS listener process under either Windows or UNIX:
Windows
C:\> cd \oracle\ora92\network\log
C:\oracle\ora92\network\log> lsnrctl set log_status off
C:\oracle\ora92\network\log> rename listener.log listener.old
C:\oracle\ora92\network\log> lsnrctl set log_status on
UNIX
% cd $ORACLE_HOME/network/log
% lsnrctl set log_status off
% mv listener.log listener.old
% lsnrctl set log_status on
On the Other hand even if you were able to rename the listener.log file it will not create a new log file like alert log. Instead it keeps writing to the renamed log file.
For Eg,
When the Listener is running if we give the command as
% cd $ORACLE_HOME/network/log
% mv listener.log listenerOld.log
After we move to the listenerOld.log file, it will continue writing log files in the file listenerOld.log. In order to make sure we create a new log file we must use the following commands.
% cd $ORACLE_HOME/network/log
% lsnrctl set log_status off
% mv listener.log listener.old
% lsnrctl set log_status on
It is advisable to move/rename the listener.log file atleast once in a month, we can create a script file to do this.
Shell Script for moving the Listener Log file
Save the Following contents in a file eg.,lsnrlogmv.sh file
export ORACLE_SID=opubp
export ORACLE_HOME=/opt/oracle/u01/oracle/product/10.2.0/db_1
$ORACLE_HOME/bin/lsnrctl set log_status off
mv $ORACLE_HOME/network/log/listener.log $ORACLE_HOME/network/log/listener_$(date +"%d-%m-%Y").log
$ORACLE_HOME/bin/lsnrctl set log_status on
Edit Crontab by Crontab -e
00 15 * * 2 /opt/oracle/lsnrlogMv.sh
#mi hh dd mm dow filename
Hope this helps.
Well, here is a solution for renaming (or removing) the listener.log file without having to stop and start the TNS listener process under either Windows or UNIX:
Windows
C:\> cd \oracle\ora92\network\log
C:\oracle\ora92\network\log> lsnrctl set log_status off
C:\oracle\ora92\network\log> rename listener.log listener.old
C:\oracle\ora92\network\log> lsnrctl set log_status on
UNIX
% cd $ORACLE_HOME/network/log
% lsnrctl set log_status off
% mv listener.log listener.old
% lsnrctl set log_status on
On the Other hand even if you were able to rename the listener.log file it will not create a new log file like alert log. Instead it keeps writing to the renamed log file.
For Eg,
When the Listener is running if we give the command as
% cd $ORACLE_HOME/network/log
% mv listener.log listenerOld.log
After we move to the listenerOld.log file, it will continue writing log files in the file listenerOld.log. In order to make sure we create a new log file we must use the following commands.
% cd $ORACLE_HOME/network/log
% lsnrctl set log_status off
% mv listener.log listener.old
% lsnrctl set log_status on
It is advisable to move/rename the listener.log file atleast once in a month, we can create a script file to do this.
Shell Script for moving the Listener Log file
Save the Following contents in a file eg.,lsnrlogmv.sh file
export ORACLE_SID=opubp
export ORACLE_HOME=/opt/oracle/u01/oracle/product/10.2.0/db_1
$ORACLE_HOME/bin/lsnrctl set log_status off
mv $ORACLE_HOME/network/log/listener.log $ORACLE_HOME/network/log/listener_$(date +"%d-%m-%Y").log
$ORACLE_HOME/bin/lsnrctl set log_status on
Edit Crontab by Crontab -e
00 15 * * 2 /opt/oracle/lsnrlogMv.sh
#mi hh dd mm dow filename
Hope this helps.
Sunday, November 8, 2009
Memory Size in ASMM
Here is How you know memory component size when use automatic storage memory managenent(ASMM)
Oracle Database 10g includes the Automatic Shared Memory Management feature which simplifies the SGA memory management significantly.
When automatic SGA memory management is enabled, the sizes of the different SGA components are flexible and can adapt to the needs of a workload without requiring any additional configuration. The database automatically distributes the available memory among the various components as required, allowing the system to maximize the use of all available SGA memory.
DBA can check all memory component size with dynamic view V$SGA_DYNAMIC_COMPONENT
Oracle Database 10g includes the Automatic Shared Memory Management feature which simplifies the SGA memory management significantly.
When automatic SGA memory management is enabled, the sizes of the different SGA components are flexible and can adapt to the needs of a workload without requiring any additional configuration. The database automatically distributes the available memory among the various components as required, allowing the system to maximize the use of all available SGA memory.
DBA can check all memory component size with dynamic view V$SGA_DYNAMIC_COMPONENT
Dataguar Switchover Steps
1. Initiate the switchover operation on the primary database (On the original primary database)
- Verify database role
SQL> SELECT DATABASE_ROLE FROM V$DATABASE;
DATABASE_ROLE
----------------
PRIMARY
- Switch database role from primary to standby database
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN WAIT;
Database altered.
- Shutdown database
SQL> SHUTDOWN IMMEDIATE
- Startup database
SQL> STARTUP
- Verify database role
SQL> SELECT DATABASE_ROLE FROM V$DATABASE;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
- Start apply log process for standby database
SQL> ALTER DATABASE RECOVER MANAGE STANDBY DATABASE DISCONNECT;
2. Switch standby database to primary database (On the original standby database)
- Verify database role
SQL> SELECT DATABASE_ROLE FROM V$DATABASE;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
- Switch database role from standby to primary database
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Database altered.
- Shutdown database
SQL> SHUTDOWN IMMEDIATE
- Startup database
SQL> STARTUP
- Verify database role
SQL> SELECT DATABASE_ROLE FROM V$DATABASE;
DATABASE_ROLE
----------------
PRIMARY
- Verify database role
SQL> SELECT DATABASE_ROLE FROM V$DATABASE;
DATABASE_ROLE
----------------
PRIMARY
- Switch database role from primary to standby database
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN WAIT;
Database altered.
- Shutdown database
SQL> SHUTDOWN IMMEDIATE
- Startup database
SQL> STARTUP
- Verify database role
SQL> SELECT DATABASE_ROLE FROM V$DATABASE;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
- Start apply log process for standby database
SQL> ALTER DATABASE RECOVER MANAGE STANDBY DATABASE DISCONNECT;
2. Switch standby database to primary database (On the original standby database)
- Verify database role
SQL> SELECT DATABASE_ROLE FROM V$DATABASE;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
- Switch database role from standby to primary database
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Database altered.
- Shutdown database
SQL> SHUTDOWN IMMEDIATE
- Startup database
SQL> STARTUP
- Verify database role
SQL> SELECT DATABASE_ROLE FROM V$DATABASE;
DATABASE_ROLE
----------------
PRIMARY
Creating a Function based Index
-- syntax for creating a function-based index:
CREATE [UNIQUE] INDEX index_name
ON table_name (function1, function2, . function_n)
[ COMPUTE STATISTICS ];
-- for example:
CREATE INDEX supplier_metrics
ON supplier (UPPER(supplier_city));
-- to be sure that the Oracle optimizer uses this index
-- when executing your SQL statements, be sure that
-- UPPER(supplier_city) does not evaluate to a NULL value.
-- To ensure this, add UPPER(supplier_city) IS NOT NULL to
-- your WHERE clause as follows:
SELECT supplier_id, supplier_city, UPPER(supplier_city)
FROM supplier
WHERE UPPER(supplier_city) IS NOT NULL
ORDER BY UPPER(supplier_city);
CREATE [UNIQUE] INDEX index_name
ON table_name (function1, function2, . function_n)
[ COMPUTE STATISTICS ];
-- for example:
CREATE INDEX supplier_metrics
ON supplier (UPPER(supplier_city));
-- to be sure that the Oracle optimizer uses this index
-- when executing your SQL statements, be sure that
-- UPPER(supplier_city) does not evaluate to a NULL value.
-- To ensure this, add UPPER(supplier_city) IS NOT NULL to
-- your WHERE clause as follows:
SELECT supplier_id, supplier_city, UPPER(supplier_city)
FROM supplier
WHERE UPPER(supplier_city) IS NOT NULL
ORDER BY UPPER(supplier_city);
Monday, November 2, 2009
Recovery from missing or corrupted control file:
Case 1: A multiplexed copy of the control file is available.
--------------------------------------------------------------------
On startup Oracle must read the control file in order to find out where the datafiles and online logs are located. Oracle expects to find control files at locations specified in the CONTROL_FILE initialisation parameter. The instance will fail to mount the database if any one of the control files are missing or corrupt. A brief error message will be displayed, with further details recorded in the alert log. The exact error message will vary depending on what has gone wrong. Here's an example:
C:\>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 3 14:07:33 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> connect / as sysdba;
Connected to an idle instance.
SQL> startup open;
ORACLE instance started.
Total System Global Area 1317011456 bytes
Fixed Size 1250740 bytes
Variable Size 973081164 bytes
Database Buffers 335544320 bytes
Redo Buffers 7135232 bytes
ORA-00205: error in identifying control file, check alert log for more info
On checking the alert log, as suggested, we find the following:
ALTER DATABASE MOUNT
Tue Nov 03 14:07:42 2009
ORA-00202: control file: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMSTS\CONTROL02.CTL'
Tue Nov 03 14:07:45 2009
ORA-205 signalled during: ALTER DATABASE MOUNT...
The above corruption was introduced by manually editing the control file when the database was closed.
The solution is simple, provided you have at least one uncorrupted control file - replace the corrupted control file with a copy using operating system commands. Remember to rename the copied file. The database should now start up without any problems.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> connect / as sysdba;
Connected to an idle instance.
SQL> startup open;
ORACLE instance started.
Total System Global Area 1317011456 bytes
Fixed Size 1250740 bytes
Variable Size 973081164 bytes
Database Buffers 335544320 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL>
Case 2: All control files lost
------------------------------------
What if you lose all your control files? In that case you have no option but to use a backup control file. The recovery needs to be performed from within RMAN, and requires that all logs (archived and current online logs) since the last backup are available. The logs are required because all datafiles must also be restored from backup. The database will then have to be recovered up to the time the control files went missing. This can only be done if all intervening logs are available. Here's an annotated transcript of a recovery session (as usual, lines in bold are commands to be typed, lines in italics are explanatory comments, other lines are RMAN feedback):
C:\>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 3 14:39:34 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> connect / as sysdba;
Connected to an idle instance.
SQL> startup open;
ORACLE instance started.
Total System Global Area 1317011456 bytes
Fixed Size 1250740 bytes
Variable Size 981469772 bytes
Database Buffers 327155712 bytes
Redo Buffers 7135232 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
C:\>rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Nov 3 14:41:46 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: emsts (not mounted)
RMAN> set dbid 849865825
executing command: SET DBID
--restore controlfile from autobackup. The backup is not at the default
--location so the path must be specified
RMAN> restore controlfile from 'C:\OraBkup\emsts\C-849865825-20091102-01';
Starting restore at 03-NOV-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:06
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMSTS\CONTROL01.CTL
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMSTS\CONTROL02.CTL
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMSTS\CONTROL03.CTL
Finished restore at 03-NOV-09
-- Now that control files have been restored, the instance can mount the
-- database.
RMAN> mount database;
database mounted
released channel: ORA_DISK_1
RMAN> restore database;
Starting restore at 03-NOV-09
Starting implicit crosscheck backup at 03-NOV-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 11 objects
Finished implicit crosscheck backup at 03-NOV-09
Starting implicit crosscheck copy at 03-NOV-09
using channel ORA_DISK_1
Finished implicit crosscheck copy at 03-NOV-09
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\EMSTS\ARCHIVELOG\2009_11_03\O1_MF_1_4_5GZKBQMK_.ARC
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMSTS\SYSTEM01.DBF
restoring datafile 00002 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMSTS\UNDOTBS01.DBF
restoring datafile 00003 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMSTS\SYSAUX01.DBF
restoring datafile 00004 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMSTS\USERS01.DBF
restoring datafile 00005 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMSTS\EXAMPLE01.DBF
restoring datafile 00006 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMSTS\ODDDAT01.DBF
restoring datafile 00007 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMSTS\ODDTAB01.DBF
restoring datafile 00008 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMSTS\SMTTAB01.DBF
restoring datafile 00009 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMSTS\RMAN01.DBF
channel ORA_DISK_1: reading from backup piece C:\ORABKUP\EMSTS\BACKUPEMSTS_DB_0AKTC3D8_10_1
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\ORABKUP\EMSTS\BACKUPEMSTS_DB_0AKTC3D8_10_1 tag=TAG20091102T183415
channel ORA_DISK_1: restore complete, elapsed time: 00:03:26
Finished restore at 03-NOV-09
--Database must be recovered because all datafiles have been restored from
-- backup otherwise the following error message will be given out
RMAN> open resetlogs database;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 11/03/2009 14:54:40
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMSTS\SYSTEM01.DBF'
RMAN> alter database open resetlogs;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 11/03/2009 14:56:56
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMSTS\SYSTEM01.DBF'
RMAN> recover database;
Starting recover at 03-NOV-09
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 3 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMSTS\REDO02.LOG
archive log thread 1 sequence 4 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\EMSTS\ARC
HIVELOG\2009_11_03\O1_MF_1_4_5GZKBQMK_.ARC
archive log thread 1 sequence 5 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMSTS\REDO01.LOG
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMSTS\REDO02.LOG thread=1 sequence=3
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\EMSTS\ARCHIVELOG\2009_11_03\O1_MF_1_4_5GZKBQMK_
.ARC thread=1 sequence=4
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMSTS\REDO01.LOG thread=1 sequence=5
media recovery complete, elapsed time: 00:00:08
Finished recover at 03-NOV-09
RMAN> alter database open resetlogs;
database opened
RMAN>
Several points are worth emphasising.
1. Recovery using a backup controlfile should be done only if a current control file is unavailable.
2. All datafiles must be restored from backup. This means the database will need to be recovered using archived and online redo logs. These MUST be available for recovery until the time of failure.
3. As with any database recovery involving RESETLOGS, take a fresh backup immediately.
4. Technically the above is an example of complete recovery - since all committed transactions were recovered. However, some references consider this to be incomplete recovery because the database log sequence had to be reset.
--------------------------------------------------------------------
On startup Oracle must read the control file in order to find out where the datafiles and online logs are located. Oracle expects to find control files at locations specified in the CONTROL_FILE initialisation parameter. The instance will fail to mount the database if any one of the control files are missing or corrupt. A brief error message will be displayed, with further details recorded in the alert log. The exact error message will vary depending on what has gone wrong. Here's an example:
C:\>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 3 14:07:33 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> connect / as sysdba;
Connected to an idle instance.
SQL> startup open;
ORACLE instance started.
Total System Global Area 1317011456 bytes
Fixed Size 1250740 bytes
Variable Size 973081164 bytes
Database Buffers 335544320 bytes
Redo Buffers 7135232 bytes
ORA-00205: error in identifying control file, check alert log for more info
On checking the alert log, as suggested, we find the following:
ALTER DATABASE MOUNT
Tue Nov 03 14:07:42 2009
ORA-00202: control file: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMSTS\CONTROL02.CTL'
Tue Nov 03 14:07:45 2009
ORA-205 signalled during: ALTER DATABASE MOUNT...
The above corruption was introduced by manually editing the control file when the database was closed.
The solution is simple, provided you have at least one uncorrupted control file - replace the corrupted control file with a copy using operating system commands. Remember to rename the copied file. The database should now start up without any problems.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> connect / as sysdba;
Connected to an idle instance.
SQL> startup open;
ORACLE instance started.
Total System Global Area 1317011456 bytes
Fixed Size 1250740 bytes
Variable Size 973081164 bytes
Database Buffers 335544320 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.
SQL>
Case 2: All control files lost
------------------------------------
What if you lose all your control files? In that case you have no option but to use a backup control file. The recovery needs to be performed from within RMAN, and requires that all logs (archived and current online logs) since the last backup are available. The logs are required because all datafiles must also be restored from backup. The database will then have to be recovered up to the time the control files went missing. This can only be done if all intervening logs are available. Here's an annotated transcript of a recovery session (as usual, lines in bold are commands to be typed, lines in italics are explanatory comments, other lines are RMAN feedback):
C:\>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Nov 3 14:39:34 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> connect / as sysdba;
Connected to an idle instance.
SQL> startup open;
ORACLE instance started.
Total System Global Area 1317011456 bytes
Fixed Size 1250740 bytes
Variable Size 981469772 bytes
Database Buffers 327155712 bytes
Redo Buffers 7135232 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
C:\>rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Tue Nov 3 14:41:46 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: emsts (not mounted)
RMAN> set dbid 849865825
executing command: SET DBID
--restore controlfile from autobackup. The backup is not at the default
--location so the path must be specified
RMAN> restore controlfile from 'C:\OraBkup\emsts\C-849865825-20091102-01';
Starting restore at 03-NOV-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:06
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMSTS\CONTROL01.CTL
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMSTS\CONTROL02.CTL
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMSTS\CONTROL03.CTL
Finished restore at 03-NOV-09
-- Now that control files have been restored, the instance can mount the
-- database.
RMAN> mount database;
database mounted
released channel: ORA_DISK_1
RMAN> restore database;
Starting restore at 03-NOV-09
Starting implicit crosscheck backup at 03-NOV-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 11 objects
Finished implicit crosscheck backup at 03-NOV-09
Starting implicit crosscheck copy at 03-NOV-09
using channel ORA_DISK_1
Finished implicit crosscheck copy at 03-NOV-09
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\EMSTS\ARCHIVELOG\2009_11_03\O1_MF_1_4_5GZKBQMK_.ARC
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMSTS\SYSTEM01.DBF
restoring datafile 00002 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMSTS\UNDOTBS01.DBF
restoring datafile 00003 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMSTS\SYSAUX01.DBF
restoring datafile 00004 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMSTS\USERS01.DBF
restoring datafile 00005 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMSTS\EXAMPLE01.DBF
restoring datafile 00006 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMSTS\ODDDAT01.DBF
restoring datafile 00007 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMSTS\ODDTAB01.DBF
restoring datafile 00008 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMSTS\SMTTAB01.DBF
restoring datafile 00009 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMSTS\RMAN01.DBF
channel ORA_DISK_1: reading from backup piece C:\ORABKUP\EMSTS\BACKUPEMSTS_DB_0AKTC3D8_10_1
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\ORABKUP\EMSTS\BACKUPEMSTS_DB_0AKTC3D8_10_1 tag=TAG20091102T183415
channel ORA_DISK_1: restore complete, elapsed time: 00:03:26
Finished restore at 03-NOV-09
--Database must be recovered because all datafiles have been restored from
-- backup otherwise the following error message will be given out
RMAN> open resetlogs database;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 11/03/2009 14:54:40
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMSTS\SYSTEM01.DBF'
RMAN> alter database open resetlogs;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 11/03/2009 14:56:56
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMSTS\SYSTEM01.DBF'
RMAN> recover database;
Starting recover at 03-NOV-09
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 3 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMSTS\REDO02.LOG
archive log thread 1 sequence 4 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\EMSTS\ARC
HIVELOG\2009_11_03\O1_MF_1_4_5GZKBQMK_.ARC
archive log thread 1 sequence 5 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMSTS\REDO01.LOG
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMSTS\REDO02.LOG thread=1 sequence=3
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\EMSTS\ARCHIVELOG\2009_11_03\O1_MF_1_4_5GZKBQMK_
.ARC thread=1 sequence=4
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\EMSTS\REDO01.LOG thread=1 sequence=5
media recovery complete, elapsed time: 00:00:08
Finished recover at 03-NOV-09
RMAN> alter database open resetlogs;
database opened
RMAN>
Several points are worth emphasising.
1. Recovery using a backup controlfile should be done only if a current control file is unavailable.
2. All datafiles must be restored from backup. This means the database will need to be recovered using archived and online redo logs. These MUST be available for recovery until the time of failure.
3. As with any database recovery involving RESETLOGS, take a fresh backup immediately.
4. Technically the above is an example of complete recovery - since all committed transactions were recovered. However, some references consider this to be incomplete recovery because the database log sequence had to be reset.
Subscribe to:
Posts (Atom)