Known RMAN Performance Problems [ID 247611.1]
I was searching for my RMAN backup problem and found this nice document.
Its intention is to give an overview of the know performance
issues with RMAN and not to give any diagnosis. The diagnosis and
investigation is handled more in depth in other documents as
referenced at the bottom of this document.
KNOW RMAN PERFORMANCE ISSUES
-----------------------------
Note :
Generic Recommendation for Oracle 10G
Generate statistics on the fixed objects, using
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS.
This should be done when the database has been running
for awhile with generic workload, so the information
in the fixed objects reflect a resonable state of the database load.
Gather the statistics with :
SQL> exec dbms_stats.gather_fixed_objects_stats
Alert Note 463227.1 Oracle10g RMAN Recovery Catalog Known Performance Issues
Bug 6051693 SLOW RESYNC AND SHOW ALL IS TAKING AROUND 2 MINUTES
Duplicate of Bug 5620640
Note 820856.1 High Waits On "Control File Sequential Read" During Rman Backup
Version affected: 10.2.0.1 to 10.2.0.3
Version fixed : some one-off backports for 10.2.0.3 , fixed in 10.2.0.4
Affects : Target database
Workaround : none
Unpublished Bug 8239313 Duplicate database for a very large database of around 3000 datafiles is very slow.
Version affected: 10.2.0.3 and higher
Version fixed : 11.2
Affects : Target database
Workaround : none
Bug 5251842 Poor plan against V$DATAFILE
Version affected: 10.2.0.3 and higher
Version fixed : 11.1.0.7 and 11.2 and some one-off for 10.2.0.3 4
Affects : Target database
Workaround : none
Bug 7023147: V$LOG_HISTORY queries can be slow
Version affected: 10.2.0.2 and higher
Version fixed : 10.2.0.5 and 11.2
Affects : Target database
Workaround : Recreate the controlfile
Note : 882154.1 RMAN LIST BACKUP command takes a long time querying
v$log_history
Bug 7595777 RMAN TAKES A LONG TIME BEFORE STARTING THE BACKUP
Version affected: 10.2.0.3 and higher
Version fixed : Unknown yet (FEBR-2009)
Affects : Target database
Workaround : Replace wasresynced function ?/rdbms/admin/recover.bsq with the following:
---------------------------------
function wasresynced(until_stamp IN number
,high_stamp IN number) return number is
nodups number; -- number of duplicates
high number;
low number;
resyncstamp number;
begin
high := high_stamp;
low := until_stamp;
nodups := 0;
resyncstamp := 0;
deb('resync', 'wasresynced high_stamp=' high_stamp
' high_date=' stamp2date(high_stamp), dbtype);
for duprec in duprec_c(low, high) loop
if (dbms_rcvcat.isDuplicateRecord(recid => duprec.recid
,stamp => duprec.stamp
,type => duprec.type)) then
if (resyncstamp = 0) then
resyncstamp := duprec.stamp;
end if;
nodups := nodups + 1;
if (nodups >= maxdups) then
deb('resync', 'wasresynced resyncstamp=' resyncstamp
' resyncdate=' stamp2date(resyncstamp), dbtype);
return resyncstamp;
end if;
else -- couldn't find 16 consecutive duplicate records.
deb('resync', 'wasresynced could not find record recid='
duprec.recid ' stamp=' duprec.stamp ' type='
duprec.type ' maxdups=' nodups, dbtype);
return 0;
end if;
end loop;
-- Timestamp range not enough to satisfy the number of duplicates.
-- Retry using a higher timestamp
deb('resync', 'timestamp range not enough - nodups=' nodups, dbtype);
return -1;
end;
-------------------------------------
Bug 7206538 PERFORMANCE ISSUE WITH RMAN BACKUP
Bug 6412947 DRA PERFORMANCE ISSUES
Version affected: 11.1.0.6
Version fixed : 11.1.0.7
Affects : Target database
Workaround : set parameter "_dra_enable_offline_dictionary"=false
Reference : Note 605557.1 RMAN BACKUPS CAN TAKE A LONG TIME IN 11.1.0.6
Bug 7173341 - CLEANUPRSR IS TAKING VERY LONG TIME CAUSING SLOW RESYNC
Version affected: 10.2.0.4
Version fixed : 11.2
Affects : Catalog Schema
Workaround : Change the 2 following SQL-statements in the recover.bsq
Replace the 'SELECT max(rsr_key)' statement in cleanupROUT:
SELECT max(rsr_key) into high_session_key
FROM rsr, dbinc
WHERE dbinc.db_key = this_db_key
AND rsr.dbinc_key = dbinc.dbinc_key
AND rsr.rsr_stamp < db_key =" this_db_key);" statement =" select" recid=":b1" stamp=":b2)" recid="parent_recid">sql "alter session set optimizer_mode=RULE";
Remark : It might be that bug 5247609 is a followup / new occurence of this bug
Bug 5247609 RMAN SLOW PERFORMANCE DURING REGISTER DATABASE/OPEN RESETLOGS
Version affected : 10.2
Version fixed : 10.4
Affects : Target database
Diagnosis : Expensive statement =
select round(sum(MBYTES_PROCESSED)) ,round(sum(INPUT_BYTES)),
round(sum(OUTPUT_BYTES))
from V$RMAN_STATUS
start with (RECID=:b1 and STAMP=:b2)
connect by prior RECID=parent_recid
Workaround : RMAN>sql "alter session set optimizer_mode=RULE";
Bug 1551773 RMAN RESTORE RUNS VERY SLOWLY WHEN THERE ARE MANY ROWS IN THE CKP TABLE
Version affected : 8.1
Version fixed : 9i
Affects : Catalog Schema
Diagnosis : The table CKP in the RMAN-catalog has many rows.
Workaround : Call Oracle Support to supply a cleanup-script.
Reference : Note.209780.1 RMAN Hangs During Restore if CKP Table is Excessively Large
Bug 2803823 RESYNC CATALOG AND MAINTENANCE COMMANDS ARE SLOW
Version affected : 9.2
Version fixed : 9205, 10g
Affects : Catalog Schema
Diagnosis : Bad execution plan for SQL-statement : DELETE FROM CKP
Reference : Note 248361.1 RMAN Command 'Resync Catalog' is Very Slow
Bug 4013855 - RMAN CROSSCHECK IS TAKING A LONG TIME TO COMPLETE
Version affected : 9i
Version fixed : 9207, 10.1.0.5, 10gRelease2
Affects : Catalog Schema
Diagnosis : Large number of backupsets.
LIST/CROSSCHECK commands(in 9iR2 and above) taking more time.
REPORT/DELETE obsolete (in 9iR2) takes more time.
Note 339964.1 RMAN 8.1.7 Crosscheck Command Is Very Slow
Version affected : 8i
Version fixed : No fix available.
Affects : Catalog Schema
Diagnosis : See this note for details on how to confirm if you have hit this problem.
Workaround : RMAN> sql "alter session set optimizer_mode=RULE";
Cleanup backup history - see this note for hints on how to do this at 8i.
Bug 4548861 RMAN RESTORE ARCHIVELOG UNTIL SEQUENCE TAKES A LONG TIME TO COMPLETE
Version affected : 9i and 10G
Version fixed : 10.2.0.2, 11G
Affects : RMAN executable, Target database, Catalog Schema
Diagnosis : Restore archivelog takes long compilation time either during
RESTORE command or during RECOVER command.
Workaround : None
Note 342999.1 First Resync on Production Host After Running RMAN on
Backup Server Takes a Long Time
Version affected : 8i
Version fixed : No fix available.
Affects : RMAN executable, Target database, Catalog Schema
Diagnosis : See this note for details on how to confirm if you have hit this problem.
Workaround : Recreate the controlfile.
Bug 2876717 RMAN backup starts SLOW if READ-ONLY device is used
Version affected : 9.2
Version fixed : 9204, 10g
Affects : Target database
Diagnosis : READ_ONLY devices, like CD-rom, are used.
Bug 1887868 RMAN RESYNC takes a long time with many tablespaces
Version affected : 8.1
Version fixed : 9201, 10g
Affects : Catalog Schema
Diagnosis : RMAN full resync (either implicit resync, or explicit
resync requested by RESYNC CATALOG command) takes a
very long time when there are a lot of tablespaces
(probably 500 or more) in the database)
Internal Bug 1712720 RMAN MAINTENANCE COMMANDS ARE VERY SLOW
Version affected : 8.1, 9.0.1
Version fixed : 9201
Affects : Catalog Schema
Workaround : create index brl_i_dts on brl(dbinc_key, thread#, sequence#)
Bug 2174697 RMAN incremental backup performance degrades over time in NOCATALOG mode
Version affected : 8.1 9.0.1
Version fixed : 8174 9014 9201
Affects : Target database
Diagnosis : Many entries in X$KSFQP
Workaround : SQL> alter system set events 'immediate trace name ksfqp_limit level 1';
Bug 2385857 RMAN backup performance degrades over time for large DB_FILES
Version affected : 8.1 9.0.1
Version fixed : 9202 10g
Affects : Target database
Diagnosis : Contention of ksfqpl latch
Workaround : SQL> alter system set events 'immediate trace name ksfqp_limit level 1';
or in the init.ora :
event = "logon trace name ksfqp_limit level 1"
Bug 2484250 RMAN CUMULATIVE BACKUP / MAINTENENCE may appear to hang (spin)
Version affected : 8.1 9.0.1 9.2
Version fixed : 9203 10g
Affects : Catalog Schema
Workaround : dbms_utility.analyze_schema() on RMAN- catalog schema.
Bug 2610596 Huge CPU usage from RMAN server when TAPE not mounted with BACKUP_TAPE_IO_SLAVE=TRUE
Version affected : 8.1 9.0.1 9.2
Version fixed : 9203 10g
Affects : Target database
Workaround : Insert a tape in the tape drive, or
Set init.ora param BACKUP_TAPE_IO_SLAVES to FALSE
Bug 2968394 RESYNC TAKES A LONG TIME TO COMPLETE : LARGE BP TABLE
Version affected : 8.1 9i
Version fixed : 10g
Affects : Catalog Schema
Diagnosis : Many rows in tables BS and BP
Workaround : Create 2 additional indexes in the RMAN-catalog schema :
SQL> create index bs_i_1 on bs (db_key, bs_recid, bs_stamp);
create index db_i_2 on bp (db_key, bp_recid, bp_stamp);
Bug 1407413 NO INDEX TO ON RI CONSTRAINT BETWEEN BS AND BDF
Version affected : 8.1
Version fixed : 9i
Affects : Catalog Schema
Diagnosis : Many rows in tables BDF
Workaround : Create 1 additional index in the RMAN-catalog schema :
SQL> create index bdf_i_bs_key on bdf(bs_key);
Bug 4110489 RMAN SKIP READ ONLY TABLESPACE CLAUSE TAKING TOO MUCH TIME TO SKIP
Version affected : 9i
Version fixed : 10G Release2
Affects : RMAN Executable
Diagnosis :
1) SKIP READ ONLY or SKIP OFFLINE option takes too much
when NOT BACKED UP option is used in catalog mode.
2) SKIP READ ONLY or SKIP OFFLINE option takes too much when
BACKUP OPTIMIZATION is turned ON in catalog mode
Workaround : Run the backup in NOCATALOG mode and
prefrom a RESYNC CATALOG afterwards
Bug 3966722 CONCURRENT RMAN BACKUPS WAIT ON TM ENQUEUE ON CKP TABLE
Version affected : 9i, 10G
Version fixed : 9207, 10105, 10G Release2
Affects : Catalog Schema
Diagnosis :
Multiple resyncs are done in parallel for same/different databases.
There is no error signaled, but the other backup job will not continue
until the resync in first job is completed.
Workaround : Create indexes in recovery catalog:
For 9i and 10G:
SQL> CREATE INDEX dfatt_i_sck on dfatt(start_ckp_key);
CREATE INDEX dfatt_i_eck on dfatt(end_ckp_key);
CREATE INDEX tsatt_i_sck on tsatt(start_ckp_key);
CREATE INDEX tsatt_i_eck on tsatt(end_ckp_key);
CREATE INDEX ckp_i_dbinc on ckp(dbinc_key);
For 10G Release 1 and 2:
SQL> CREATE INDEX rsr_i_dbinc on rsr(dbinc_key);
For 10G Release 2:
SQL> CREATE INDEX rout_i_db on rout(db_key);
CREATE INDEX rout_i_rsr on rout(rsr_key);
Generic Performance Issues which effect RMAN :
----------------------------------------------
Bug 2710321 X$KCCFN / V$DATAFILE much slower in 9i than 8i with many datafiles
Version affected : 9
Version fixed : 9203
Affects : Target database
Diagnosis : Many datafiles
RELATED DOCUMENTS
-----------------
Note 145624.1 : RMAN: Resolving an RMAN Hung Job
Note 228838.1 : RMAN Diagnostic Checklist
Note 605557.1 : RMAN BACKUPS CAN TAKE A LONG TIME IN 11.1.0.6
Enjoy…
Wednesday, November 3, 2010
Thursday, October 21, 2010
High Availability and Flexibility with Standby Database solution. How to create manual Standby Database?
High Availability and Flexibility with Standby Database solution. How to create manual Standby Database?
The purpose of this blog entry is to show you how to create manually a physical standby database.
The first will be a basic primary and standby database setup on 2 different machines. In this case, I used VMWARE.
If we afford to lose few minutes of data then this is very good cheap solution for disaster recovery solution.
The Standby Database
• Oracle supplied disaster recovery (DR) solution.
• Operates in 2 modes –manual and automatic.
• No GUI.
• Available since 7.3.4.
• Available with Std Edition one
Step by step document to create manual Standby Database
1 - Prerequisites
• Same Oracle software version must be used for both primary and standby databases. The operating system running on primary and standby locations must be same, but operating system release may not need to be same.
• The Primary Database must run in ARCHIVELOG mode.
• Each primary and standby database must have its own control file.
• If primary and standby database
• s are placed on the same system, initialization parameters must be adjusted correctly.
Preparing Primary Database for Standby Database creation
Configuration in Primary Database
2 Ensure the primary database in ARCHIVELOG mode
Ensure the primary database in ARCHIVELOG mode using following command.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination C:\oracle\ora92\database\archive\ORCLC
Oldest online log sequence 9
Next log sequence to archive 11
Current log sequence 11
3 - Identify the primary database Datafiles
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
C:\ORACLE\ORADATA\ORCLC\SYSTEM01.DBF
C:\ORACLE\ORADATA\ORCLC\UNDOTBS01.DBF
C:\ORACLE\ORADATA\ORCLC\CWMLITE01.DBF
C:\ORACLE\ORADATA\ORCLC\DRSYS01.DBF
C:\ORACLE\ORADATA\ORCLC\EXAMPLE01.DBF
C:\ORACLE\ORADATA\ORCLC\INDX01.DBF
C:\ORACLE\ORADATA\ORCLC\ODM01.DBF
C:\ORACLE\ORADATA\ORCLC\TOOLS01.DBF
C:\ORACLE\ORADATA\ORCLC\USERS01.DBF
C:\ORACLE\ORADATA\ORCLC\XDB01.DBF
10 rows selected.
4 - Make a cold backup copy of Primary Database
Make a cold backup copy of primary database by performing following steps:
4.1 Shutdown the Primary Database
Issue the following statement to shutdown the primary database.
SQL> shutdown immediate;
4.2 Copy the Datafiles to standby location
Copy the redo log files and Datafiles identified in section 3 to standby location.
Note: Primary Database must be shutdown while copying the files.
5 - Restart the Primary Database
Execute following command to restart the Primary Database.
SQL> startup;
6 - Create Control file for Standby Database
Issue the following command on primary database to create control file for the standby database.
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'D:\oracle\oradata\stby\control_sb01.ctl';
Database altered.
The filename for newly created standby control file must be different of current control file of the primary database. Also control file for standby database must be created after the cold backup of Datafiles and redo logs. Copy this standby controlfile to the standby server.
Configuration in Standby Database
7 - Create pfile for standby database from the primary database
Copy the pfile from the primary database to the standby server
8 - Set initialization parameters on physical standby database
Although most of the initialization parameter settings in the text initialization parameter file that you copied from the primary system are also appropriate for the physical standby database, some modifications need to be made. Edit created pfile from primary database.
db_name - Not modified. The same name as the primary database.
compatible - Not modified. The same as the primary database,
control_files - Specify the path name and filename for the standby control file .
log_archive_start - Not modified. The same as the setting for the primary database, TRUE
db_file_name_convert - Specify the location of the primary database datafiles followed by the standby location of the datafiles. This parameter will convert the filename of the primary database datafiles to the filename of the standby datafile filenames. If the standby database is on the same system as the primary database or if the directory structure where the datafiles are located on the standby site is different from the primary site then this parameter is required.
log_file_name_convert - Specify the location of the primary database logs followed by the standby location of the logs. This parameter will convert the filename of the primary database log to the filenames of the standby log. If the standby database is on the same system as the primary database or if the directory structure where the logs are located on the standby site is different from the primary site then this parameter is required.
instance_name - If both primary and standby databases are to reside in the same server, specify a different value for the standby database than the primary database. If standby database will be in a different server, then ignore this.
lock_name_space - - If both primary and standby databases are to reside in the same server, Specify the standby database instance name. Use this parameter when you create the physical standby database on the same system as the primary database. Change the INSTANCE_NAME parameter to a value other than its primary database value, and set this LOCK_NAME_SPACE initialization parameter to the same value that you specified for the standby database INSTANCE_NAME initialization parameter.
Also change the values of the parameters background_dump_dest, core_dump_dest and user_dump_dest to specify location of the standby database.
(Refer Annexure for initialization parameter settings for primary and standby database.)
9 - Create a Window service
If standby database is running on windows system, then oradim utility is used to create windows service. Issue following command from the command prompt window
C:\>oradim -new -sid ssss -intpwd yyyy -startmode manual
10 - Configure tnsnames for standby database in the standby server
11 - Start Physical standby database in standby mode
Start up the stand by database using following commands
C:\>set oracle_sid=ssss
C:\>sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Mon Apr 25 17:13:26 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup pfile='C:\oracle\ora92\database\initstby.ora' nomount;
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL> alter database mount standby database;
Database altered.
12. Now you have to transfer archivelogs from Primary database and apply them to standby database
12.1 create a script in primary server to run under windows task at desired intervals (15 mins or 30 mins etc.), to transfer archivelogs from primary to standby:-
move _arch.cmd
==================
pushd C:\oracle\admin\scripts
set ORACLE_SID=xxx
set ORACLE_HOME=C:\oracle\product\10.2.0\db_1
%ORACLE_HOME%\bin\sqlplus dbo/dbo @move_archivelog.sql
robocopy D:\oracle\backup\arch\roc8 \\172.30.36.13\oracle\backup\arch\roc8 /MIR /COPY:DT /FFT /log:robocopystatus.log
The sql below is to force a new archivelog from primary database:
Apply_archivelog.sql
==================
ALTER SYSTEM SWITCH LOGFILE;
exit;
**** Windows robocopy utility is used above to send archivelogs from arch folder in primary server to arch folder in standby server ****
12.2 apply the archivelogs arriving from primary server to the standby database every 15 mins or 30 mins under a windows task in standby server as follows:-
apply_arch.cmd
==============
pushd C:\oracle\admin\scripts
set ORACLE_SID=ssss
set ORACLE_HOME=C:\oracle\product\10.2.0\db_1
%ORACLE_HOME%\bin\sqlplus /nolog @ Apply_archivelog.sql
Apply_archivelog.sql
===============
connect sys/oracle as sysdba;
set timing on
set echo on
spool apply_archp.log
recover standby database;
AUTO
spool off
exit
License requirements by Oracle:
Standby database doesn’t need Oracle license for 10 days in calendar year.
Failover – In this type of recovery, nodes are arranged in a cluster and share
one disk array. A Failover cluster is a group of systems, bound together into a
common resource pool. In this type of recovery method, the Production node
acts as the primary node. When the primary node fails, one of the surviving
nodes in the cluster acts as the primary node. Solutions like Oracle Failsafe
(included with Oracle Database EE or SE, SE1), or third party vendor solutions
(e.g. Veritas, HP Service Guard, HACMP, Linux HA - Heartbeat) are used to
manage Failover environments. In this type of environment, Oracle permits
licensed Oracle customers to run some Technology Programs on an
unlicensed spare computer for up to a total of ten separate days in any given
calendar year. Once the primary node is repaired, you must switch back to the
primary node. Once the failover period has exceeded ten days, the failover
node must be licensed.
Source: Oracle SOFTWARE INVESTMENT GUIDE
Testing the environment
Once primary and Standby database is configured and transportation log is configured to transfer we need to test it thoroughly.
We can execute some DML and DDL in primary database. After archive logs are applied in Standby database we should able to see those DLL and DML changes in standby database.
To check it Open the database on read only mode.
SQL> alter database open read only;
After the test re-start the database in mount mode. If you OPEN the database you have to recreate the standby database again.
1. SQL> shutdown immediate;
2. SQL>startup mount;
Checking log file in Standby database:
If you check log file you might see error and warning like below.
ORCL: SQL> recover standby database;
ORA-00279: change 239868 generated at 10/20/2010 12:50:02 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_10_20\O1_MF_1_1
00_%U_.ARC
ORA-00280: change 239868 for thread 1 is in sequence #100
Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 240264 generated at 10/20/2010 13:05:02 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_10_20\O1_MF_1_1
01_%U_.ARC
ORA-00280: change 240264 for thread 1 is in sequence #101
ORA-00278: log file
'D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_10_20\O1_MF_1_
100_6CY89YTC_.ARC' no longer needed for this recovery
ORA-00308: cannot open archived log
'D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_10_20\O1_MF_1_
101_%U_.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
TOM: SQL> spool off
If you see error like ORA-00308: cannot open archived log.
Check the thread number in your Flash recovery area of Primary database to see whether that archive log file exist. If the archive log doesn’t exist just ignore it as recovery database look for next archive log which might not generated at that time.
Good luck!
The purpose of this blog entry is to show you how to create manually a physical standby database.
The first will be a basic primary and standby database setup on 2 different machines. In this case, I used VMWARE.
If we afford to lose few minutes of data then this is very good cheap solution for disaster recovery solution.
The Standby Database
• Oracle supplied disaster recovery (DR) solution.
• Operates in 2 modes –manual and automatic.
• No GUI.
• Available since 7.3.4.
• Available with Std Edition one
Step by step document to create manual Standby Database
1 - Prerequisites
• Same Oracle software version must be used for both primary and standby databases. The operating system running on primary and standby locations must be same, but operating system release may not need to be same.
• The Primary Database must run in ARCHIVELOG mode.
• Each primary and standby database must have its own control file.
• If primary and standby database
• s are placed on the same system, initialization parameters must be adjusted correctly.
Preparing Primary Database for Standby Database creation
Configuration in Primary Database
2 Ensure the primary database in ARCHIVELOG mode
Ensure the primary database in ARCHIVELOG mode using following command.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination C:\oracle\ora92\database\archive\ORCLC
Oldest online log sequence 9
Next log sequence to archive 11
Current log sequence 11
3 - Identify the primary database Datafiles
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
C:\ORACLE\ORADATA\ORCLC\SYSTEM01.DBF
C:\ORACLE\ORADATA\ORCLC\UNDOTBS01.DBF
C:\ORACLE\ORADATA\ORCLC\CWMLITE01.DBF
C:\ORACLE\ORADATA\ORCLC\DRSYS01.DBF
C:\ORACLE\ORADATA\ORCLC\EXAMPLE01.DBF
C:\ORACLE\ORADATA\ORCLC\INDX01.DBF
C:\ORACLE\ORADATA\ORCLC\ODM01.DBF
C:\ORACLE\ORADATA\ORCLC\TOOLS01.DBF
C:\ORACLE\ORADATA\ORCLC\USERS01.DBF
C:\ORACLE\ORADATA\ORCLC\XDB01.DBF
10 rows selected.
4 - Make a cold backup copy of Primary Database
Make a cold backup copy of primary database by performing following steps:
4.1 Shutdown the Primary Database
Issue the following statement to shutdown the primary database.
SQL> shutdown immediate;
4.2 Copy the Datafiles to standby location
Copy the redo log files and Datafiles identified in section 3 to standby location.
Note: Primary Database must be shutdown while copying the files.
5 - Restart the Primary Database
Execute following command to restart the Primary Database.
SQL> startup;
6 - Create Control file for Standby Database
Issue the following command on primary database to create control file for the standby database.
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'D:\oracle\oradata\stby\control_sb01.ctl';
Database altered.
The filename for newly created standby control file must be different of current control file of the primary database. Also control file for standby database must be created after the cold backup of Datafiles and redo logs. Copy this standby controlfile to the standby server.
Configuration in Standby Database
7 - Create pfile for standby database from the primary database
Copy the pfile from the primary database to the standby server
8 - Set initialization parameters on physical standby database
Although most of the initialization parameter settings in the text initialization parameter file that you copied from the primary system are also appropriate for the physical standby database, some modifications need to be made. Edit created pfile from primary database.
db_name - Not modified. The same name as the primary database.
compatible - Not modified. The same as the primary database,
control_files - Specify the path name and filename for the standby control file .
log_archive_start - Not modified. The same as the setting for the primary database, TRUE
db_file_name_convert - Specify the location of the primary database datafiles followed by the standby location of the datafiles. This parameter will convert the filename of the primary database datafiles to the filename of the standby datafile filenames. If the standby database is on the same system as the primary database or if the directory structure where the datafiles are located on the standby site is different from the primary site then this parameter is required.
log_file_name_convert - Specify the location of the primary database logs followed by the standby location of the logs. This parameter will convert the filename of the primary database log to the filenames of the standby log. If the standby database is on the same system as the primary database or if the directory structure where the logs are located on the standby site is different from the primary site then this parameter is required.
instance_name - If both primary and standby databases are to reside in the same server, specify a different value for the standby database than the primary database. If standby database will be in a different server, then ignore this.
lock_name_space - - If both primary and standby databases are to reside in the same server, Specify the standby database instance name. Use this parameter when you create the physical standby database on the same system as the primary database. Change the INSTANCE_NAME parameter to a value other than its primary database value, and set this LOCK_NAME_SPACE initialization parameter to the same value that you specified for the standby database INSTANCE_NAME initialization parameter.
Also change the values of the parameters background_dump_dest, core_dump_dest and user_dump_dest to specify location of the standby database.
(Refer Annexure for initialization parameter settings for primary and standby database.)
9 - Create a Window service
If standby database is running on windows system, then oradim utility is used to create windows service. Issue following command from the command prompt window
C:\>oradim -new -sid ssss -intpwd yyyy -startmode manual
10 - Configure tnsnames for standby database in the standby server
11 - Start Physical standby database in standby mode
Start up the stand by database using following commands
C:\>set oracle_sid=ssss
C:\>sqlplus /nolog
SQL*Plus: Release 9.2.0.1.0 - Production on Mon Apr 25 17:13:26 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup pfile='C:\oracle\ora92\database\initstby.ora' nomount;
ORACLE instance started.
Total System Global Area 135338868 bytes
Fixed Size 453492 bytes
Variable Size 109051904 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL> alter database mount standby database;
Database altered.
12. Now you have to transfer archivelogs from Primary database and apply them to standby database
12.1 create a script in primary server to run under windows task at desired intervals (15 mins or 30 mins etc.), to transfer archivelogs from primary to standby:-
move _arch.cmd
==================
pushd C:\oracle\admin\scripts
set ORACLE_SID=xxx
set ORACLE_HOME=C:\oracle\product\10.2.0\db_1
%ORACLE_HOME%\bin\sqlplus dbo/dbo @move_archivelog.sql
robocopy D:\oracle\backup\arch\roc8 \\172.30.36.13\oracle\backup\arch\roc8 /MIR /COPY:DT /FFT /log:robocopystatus.log
The sql below is to force a new archivelog from primary database:
Apply_archivelog.sql
==================
ALTER SYSTEM SWITCH LOGFILE;
exit;
**** Windows robocopy utility is used above to send archivelogs from arch folder in primary server to arch folder in standby server ****
12.2 apply the archivelogs arriving from primary server to the standby database every 15 mins or 30 mins under a windows task in standby server as follows:-
apply_arch.cmd
==============
pushd C:\oracle\admin\scripts
set ORACLE_SID=ssss
set ORACLE_HOME=C:\oracle\product\10.2.0\db_1
%ORACLE_HOME%\bin\sqlplus /nolog @ Apply_archivelog.sql
Apply_archivelog.sql
===============
connect sys/oracle as sysdba;
set timing on
set echo on
spool apply_archp.log
recover standby database;
AUTO
spool off
exit
License requirements by Oracle:
Standby database doesn’t need Oracle license for 10 days in calendar year.
Failover – In this type of recovery, nodes are arranged in a cluster and share
one disk array. A Failover cluster is a group of systems, bound together into a
common resource pool. In this type of recovery method, the Production node
acts as the primary node. When the primary node fails, one of the surviving
nodes in the cluster acts as the primary node. Solutions like Oracle Failsafe
(included with Oracle Database EE or SE, SE1), or third party vendor solutions
(e.g. Veritas, HP Service Guard, HACMP, Linux HA - Heartbeat) are used to
manage Failover environments. In this type of environment, Oracle permits
licensed Oracle customers to run some Technology Programs on an
unlicensed spare computer for up to a total of ten separate days in any given
calendar year. Once the primary node is repaired, you must switch back to the
primary node. Once the failover period has exceeded ten days, the failover
node must be licensed.
Source: Oracle SOFTWARE INVESTMENT GUIDE
Testing the environment
Once primary and Standby database is configured and transportation log is configured to transfer we need to test it thoroughly.
We can execute some DML and DDL in primary database. After archive logs are applied in Standby database we should able to see those DLL and DML changes in standby database.
To check it Open the database on read only mode.
SQL> alter database open read only;
After the test re-start the database in mount mode. If you OPEN the database you have to recreate the standby database again.
1. SQL> shutdown immediate;
2. SQL>startup mount;
Checking log file in Standby database:
If you check log file you might see error and warning like below.
ORCL: SQL> recover standby database;
ORA-00279: change 239868 generated at 10/20/2010 12:50:02 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_10_20\O1_MF_1_1
00_%U_.ARC
ORA-00280: change 239868 for thread 1 is in sequence #100
Specify log: {
AUTO
ORA-00279: change 240264 generated at 10/20/2010 13:05:02 needed for thread 1
ORA-00289: suggestion :
D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_10_20\O1_MF_1_1
01_%U_.ARC
ORA-00280: change 240264 for thread 1 is in sequence #101
ORA-00278: log file
'D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_10_20\O1_MF_1_
100_6CY89YTC_.ARC' no longer needed for this recovery
ORA-00308: cannot open archived log
'D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2010_10_20\O1_MF_1_
101_%U_.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
TOM: SQL> spool off
If you see error like ORA-00308: cannot open archived log.
Check the thread number in your Flash recovery area of Primary database to see whether that archive log file exist. If the archive log doesn’t exist just ignore it as recovery database look for next archive log which might not generated at that time.
Good luck!
Monday, September 27, 2010
How to Reclaim disk space from database/ How to reduce data file size to free disk?
My tablespace is fragmented and I want to reduce the size of my dbf files to free up disk. How do I reclaim disk space in Oracle?
Oracle maps the physical files (customer.dbf) to their logical "tablespace" construct and Oracle places objects (tables & indexes) into the tablespace.
Within the tablespace, objects are scattered throughout the tablespace and corresponding datafiles.
Tables, indexes and tablespaces will naturally fragment as a function of update activity and Oracle has many methods for reclaiming disk space and a segment advisor which will recommend when tables and indexes will benefit from a reorganization to free up disk space.
Segments that undergo significant data manipulation language (DML) activity, such as UPDATE and DELETE operations, can become sparsely populated, with chunks of free space within their data blocks. Besides simply wasting space, sparsely populated segments can also lead to poor performance, because operations such as a full table scan will need to scan more blocks than necessary to retrieve data.
Before Oracle Database 10g, you could reclaim the free segment space by dropping the table, re-creating it, and then reloading the data. You could also use the ALTER TABLE MOVE command to move the table to a different tablespace. Both of these processes, however, must occur with the table offline. Online table reorganization is another way to reclaim space, but it requires significant disk space.
So how much DISK space you can claim from your data file (Calculate)?
Let run this script:
SELECT substr(FILE_NAME,1,55),
CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SMALLEST,
CEIL( BLOCKS*&&BLKSIZE/1024/1024) CURRSIZE,
CEIL( BLOCKS*&&BLKSIZE/1024/1024) - CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SAVINGS
FROM DBA_DATA_FILES DBADF, ( SELECT FILE_ID, MAX(BLOCK_ID+BLOCKS-1) HWM FROM DBA_EXTENTS GROUP BY FILE_ID ) DBAFS
WHERE DBADF.FILE_ID = DBAFS.FILE_ID(+) Order by SAVINGS
You will ask for BOCKSIZE.
You can find database block size from intXXX.ora file.
db_block_size=8192
So in this case enter 8192 as parameter.
The output will be like this:
ORCL: SQL> SELECT substr(FILE_NAME,1,55),
2 CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SMALLEST,
3 CEIL( BLOCKS*&&BLKSIZE/1024/1024) CURRSIZE,
4 CEIL( BLOCKS*&&BLKSIZE/1024/1024) - CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SAVINGS
5 FROM DBA_DATA_FILES DBADF, ( SELECT FILE_ID, MAX(BLOCK_ID+BLOCKS-1) HWM FROM DBA_EXTENTS GROUP BY FILE_ID ) DBAFS
6 WHERE DBADF.FILE_ID = DBAFS.FILE_ID(+) Order by SAVINGS
7 /
old 2: CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SMALLEST,
new 2: CEIL( (NVL(HWM,1)*8192)/1024/1024 ) SMALLEST,
old 3: CEIL( BLOCKS*&&BLKSIZE/1024/1024) CURRSIZE,
new 3: CEIL( BLOCKS*8192/1024/1024) CURRSIZE,
old 4: CEIL( BLOCKS*&&BLKSIZE/1024/1024) - CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SAVINGS
new 4: CEIL( BLOCKS*8192/1024/1024) - CEIL( (NVL(HWM,1)*8192)/1024/1024 ) SAVINGS
SUBSTR(FILE_NAME,1,55) SMALLEST CURRSIZE SAVINGS
------------------------------------------------------- ---------- ---------- ----------
D:\ ORADATA\ORCL\DATA_AUD_02.DBF 80 80 0
D:\ ORADATA\ORCL\DATA6_02.DBF 200 200 0
D:\ ORADATA\ORCL\DATA1_02.DBF 520 520 0
D:\ ORADATA\ORCL\DATA_S_03.DBF 20 20 0
D:\ ORADATA\ORCL\DATA_AUD_01.DBF 60 60 0
D:\ ORADATA\ORCL\DATA_02.DBF 120 120 0
D:\ ORADATA\ORCL\DATA4_01.DBF 20 20 0
D:\ ORADATA\ORCL\DATA5_02.DBF 100 100 0
D:\ ORADATA\ORCL\DATA_AUDI_01.DBF 17 20 3
D:\ ORADATA\ORCL\SYSAUX01.DBF 197 200 3
D:\ ORADATA\ORCL\DATA_AUDI_02.DBF 16 20 4
SUBSTR(FILE_NAME,1,55) SMALLEST CURRSIZE
------------------------------------------------------- ---------- ---------- ----------
D:\ ORADATA\ORCL\DATA_01.DBF 135 140 5
D:\ ORADATA\ORCL\DATA_SI_01.DBF 13 20 7
D:\ ORADATA\ORCL\DATA6_01.DBF 213 220 7
D:\ ORADATA\ORCL\DATA2_02.DBF 9 20 11
D:\ ORADATA\ORCL\DATA2_01.DBF 8 20 12
D:\ ORADATA\ORCL\DATA4_02.DBF 27 40 13
D:\ ORADATA\ORCL\DATA_S_02.DBF 26 40 14
D:\ ORADATA\ORCL\DATA1_01.DBF 545 560 15
D:\ ORADATA\ORCL\USERS01.DBF 2 19 17
D:\ ORADATA\ORCL\DATA5_01.DBF 102 120 18
D:\ ORADATA\ORCL\DATA_HL7_02.DBF 1 20 19
SUBSTR(FILE_NAME,1,55) SMALLEST CURRSIZE SAVINGS
------------------------------------------------------- ---------- ---------- ----------
D:\ ORADATA\ORCL\DATA_HL7I_02.DBF 1 20 19
D:\ ORADATA\ORCL\DATA3_01.DBF 1 20 19
D:\ ORADATA\ORCL\DATA_HL7_01.DBF 1 20 19
D:\ ORADATA\ORCL\DATA_HL7I_01.DBF 1 20 19
D:\ ORADATA\ORCL\DATA3_02.DBF 1 20 19
D:\ ORADATA\ORCL\DATA_S_01.DBF 20 40 20
D:\ ORADATA\ORCL\DATA_SI_02.DBF 16 40 24
SUBSTR(FILE_NAME,1,55) SMALLEST CURRSIZE SAVINGS
------------------------------------------------------- ---------- ---------- ----------
D:\ ORADATA\ORCL\SYSTEM01.DBF 265 300 35
D:\ ORADATA\ORCL\UNDOTBS01.DBF 34 85 51
Let’s write a dynamic script to resize data file
set pagesize 0
set verify off
set heading off
set feedback off
set linesize 300
prompt Running Shrink_data_&&1..dbsql
spool Shrink_data_&&1..dbsql
SELECT 'ALTER DATABASE DATAFILE '|| ' '''|| substr(FILE_NAME,1,65) ||' '''|| ' RESIZE ' || CEIL(
(NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) ||'M; ' FROM DBA_DATA_FILES DBADF, ( SELECT FILE_ID,MAX(BLOCK_ID+BLOCKS-1) HWM FROM DBA_EXTENTS GROUP BY FILE_ID ) DBAFS WHERE DBADF.FILE_ID = DBAFS.FILE_ID(+) and CEIL(
BLOCKS*&&BLKSIZE/1024/1024) - CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 )>1
spool off
set echo on
spool Shrink_data_&&1..log
rem @Shrink_data_&&1..dbsql
spool
spool off
exit
You will see all entries of data files in spool output (Shrink_data.log).
For example:
ALTER DATABASE DATAFILE 'D:\ ORADATA\ORCL\SYSTEM01.DBF ' RESIZE 265M;
This is a dynamic script and tested in several databases.
Please be sure to retest again in your development box before you ran it in production environment.
Oracle maps the physical files (customer.dbf) to their logical "tablespace" construct and Oracle places objects (tables & indexes) into the tablespace.
Within the tablespace, objects are scattered throughout the tablespace and corresponding datafiles.
Tables, indexes and tablespaces will naturally fragment as a function of update activity and Oracle has many methods for reclaiming disk space and a segment advisor which will recommend when tables and indexes will benefit from a reorganization to free up disk space.
Segments that undergo significant data manipulation language (DML) activity, such as UPDATE and DELETE operations, can become sparsely populated, with chunks of free space within their data blocks. Besides simply wasting space, sparsely populated segments can also lead to poor performance, because operations such as a full table scan will need to scan more blocks than necessary to retrieve data.
Before Oracle Database 10g, you could reclaim the free segment space by dropping the table, re-creating it, and then reloading the data. You could also use the ALTER TABLE MOVE command to move the table to a different tablespace. Both of these processes, however, must occur with the table offline. Online table reorganization is another way to reclaim space, but it requires significant disk space.
So how much DISK space you can claim from your data file (Calculate)?
Let run this script:
SELECT substr(FILE_NAME,1,55),
CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SMALLEST,
CEIL( BLOCKS*&&BLKSIZE/1024/1024) CURRSIZE,
CEIL( BLOCKS*&&BLKSIZE/1024/1024) - CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SAVINGS
FROM DBA_DATA_FILES DBADF, ( SELECT FILE_ID, MAX(BLOCK_ID+BLOCKS-1) HWM FROM DBA_EXTENTS GROUP BY FILE_ID ) DBAFS
WHERE DBADF.FILE_ID = DBAFS.FILE_ID(+) Order by SAVINGS
You will ask for BOCKSIZE.
You can find database block size from intXXX.ora file.
db_block_size=8192
So in this case enter 8192 as parameter.
The output will be like this:
ORCL: SQL> SELECT substr(FILE_NAME,1,55),
2 CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SMALLEST,
3 CEIL( BLOCKS*&&BLKSIZE/1024/1024) CURRSIZE,
4 CEIL( BLOCKS*&&BLKSIZE/1024/1024) - CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SAVINGS
5 FROM DBA_DATA_FILES DBADF, ( SELECT FILE_ID, MAX(BLOCK_ID+BLOCKS-1) HWM FROM DBA_EXTENTS GROUP BY FILE_ID ) DBAFS
6 WHERE DBADF.FILE_ID = DBAFS.FILE_ID(+) Order by SAVINGS
7 /
old 2: CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SMALLEST,
new 2: CEIL( (NVL(HWM,1)*8192)/1024/1024 ) SMALLEST,
old 3: CEIL( BLOCKS*&&BLKSIZE/1024/1024) CURRSIZE,
new 3: CEIL( BLOCKS*8192/1024/1024) CURRSIZE,
old 4: CEIL( BLOCKS*&&BLKSIZE/1024/1024) - CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) SAVINGS
new 4: CEIL( BLOCKS*8192/1024/1024) - CEIL( (NVL(HWM,1)*8192)/1024/1024 ) SAVINGS
SUBSTR(FILE_NAME,1,55) SMALLEST CURRSIZE SAVINGS
------------------------------------------------------- ---------- ---------- ----------
D:\ ORADATA\ORCL\DATA_AUD_02.DBF 80 80 0
D:\ ORADATA\ORCL\DATA6_02.DBF 200 200 0
D:\ ORADATA\ORCL\DATA1_02.DBF 520 520 0
D:\ ORADATA\ORCL\DATA_S_03.DBF 20 20 0
D:\ ORADATA\ORCL\DATA_AUD_01.DBF 60 60 0
D:\ ORADATA\ORCL\DATA_02.DBF 120 120 0
D:\ ORADATA\ORCL\DATA4_01.DBF 20 20 0
D:\ ORADATA\ORCL\DATA5_02.DBF 100 100 0
D:\ ORADATA\ORCL\DATA_AUDI_01.DBF 17 20 3
D:\ ORADATA\ORCL\SYSAUX01.DBF 197 200 3
D:\ ORADATA\ORCL\DATA_AUDI_02.DBF 16 20 4
SUBSTR(FILE_NAME,1,55) SMALLEST CURRSIZE
------------------------------------------------------- ---------- ---------- ----------
D:\ ORADATA\ORCL\DATA_01.DBF 135 140 5
D:\ ORADATA\ORCL\DATA_SI_01.DBF 13 20 7
D:\ ORADATA\ORCL\DATA6_01.DBF 213 220 7
D:\ ORADATA\ORCL\DATA2_02.DBF 9 20 11
D:\ ORADATA\ORCL\DATA2_01.DBF 8 20 12
D:\ ORADATA\ORCL\DATA4_02.DBF 27 40 13
D:\ ORADATA\ORCL\DATA_S_02.DBF 26 40 14
D:\ ORADATA\ORCL\DATA1_01.DBF 545 560 15
D:\ ORADATA\ORCL\USERS01.DBF 2 19 17
D:\ ORADATA\ORCL\DATA5_01.DBF 102 120 18
D:\ ORADATA\ORCL\DATA_HL7_02.DBF 1 20 19
SUBSTR(FILE_NAME,1,55) SMALLEST CURRSIZE SAVINGS
------------------------------------------------------- ---------- ---------- ----------
D:\ ORADATA\ORCL\DATA_HL7I_02.DBF 1 20 19
D:\ ORADATA\ORCL\DATA3_01.DBF 1 20 19
D:\ ORADATA\ORCL\DATA_HL7_01.DBF 1 20 19
D:\ ORADATA\ORCL\DATA_HL7I_01.DBF 1 20 19
D:\ ORADATA\ORCL\DATA3_02.DBF 1 20 19
D:\ ORADATA\ORCL\DATA_S_01.DBF 20 40 20
D:\ ORADATA\ORCL\DATA_SI_02.DBF 16 40 24
SUBSTR(FILE_NAME,1,55) SMALLEST CURRSIZE SAVINGS
------------------------------------------------------- ---------- ---------- ----------
D:\ ORADATA\ORCL\SYSTEM01.DBF 265 300 35
D:\ ORADATA\ORCL\UNDOTBS01.DBF 34 85 51
Let’s write a dynamic script to resize data file
set pagesize 0
set verify off
set heading off
set feedback off
set linesize 300
prompt Running Shrink_data_&&1..dbsql
spool Shrink_data_&&1..dbsql
SELECT 'ALTER DATABASE DATAFILE '|| ' '''|| substr(FILE_NAME,1,65) ||' '''|| ' RESIZE ' || CEIL(
(NVL(HWM,1)*&&BLKSIZE)/1024/1024 ) ||'M; ' FROM DBA_DATA_FILES DBADF, ( SELECT FILE_ID,MAX(BLOCK_ID+BLOCKS-1) HWM FROM DBA_EXTENTS GROUP BY FILE_ID ) DBAFS WHERE DBADF.FILE_ID = DBAFS.FILE_ID(+) and CEIL(
BLOCKS*&&BLKSIZE/1024/1024) - CEIL( (NVL(HWM,1)*&&BLKSIZE)/1024/1024 )>1
spool off
set echo on
spool Shrink_data_&&1..log
rem @Shrink_data_&&1..dbsql
spool
spool off
exit
You will see all entries of data files in spool output (Shrink_data.log).
For example:
ALTER DATABASE DATAFILE 'D:\ ORADATA\ORCL\SYSTEM01.DBF ' RESIZE 265M;
This is a dynamic script and tested in several databases.
Please be sure to retest again in your development box before you ran it in production environment.
How to calculate the size of Oracle Database
What script do I run to get the size of an Oracle Database?
The following script calculates the storage allocated to an Oracle database, i.e., its overall size. You will need DBA privilege to run this script.
select ROUND( ( a.data_size + b.temp_size +
c.redo_size + d.cf_size +
e.bct_size)
/1024/1024/1024
) "total_GB_size"
from
( select SUM(bytes) data_size from v$datafile) a,
( select NVL(sum(bytes),0) temp_size from v$tempfile) b,
( select SUM(bytes) redo_size from v$log) c,
( select SUM(block_size*file_size_blks) cf_size from v$controlfile) d,
( select NVL(bytes,0) bct_size from v$block_change_tracking) e
select a.data_size+b.temp_size+c.redo_size "total_size"
from ( select sum(bytes)/1024/1024 data_size
from dba_data_files )a,
( select nvl(sum(bytes),0)/1024/1024 temp_size
from dba_temp_files ) b,
( select sum(bytes)/1024/1024 redo_size
from sys.v_$log ) c
The following script calculates the storage allocated to an Oracle database, i.e., its overall size. You will need DBA privilege to run this script.
select ROUND( ( a.data_size + b.temp_size +
c.redo_size + d.cf_size +
e.bct_size)
/1024/1024/1024
) "total_GB_size"
from
( select SUM(bytes) data_size from v$datafile) a,
( select NVL(sum(bytes),0) temp_size from v$tempfile) b,
( select SUM(bytes) redo_size from v$log) c,
( select SUM(block_size*file_size_blks) cf_size from v$controlfile) d,
( select NVL(bytes,0) bct_size from v$block_change_tracking) e
select a.data_size+b.temp_size+c.redo_size "total_size"
from ( select sum(bytes)/1024/1024 data_size
from dba_data_files )a,
( select nvl(sum(bytes),0)/1024/1024 temp_size
from dba_temp_files ) b,
( select sum(bytes)/1024/1024 redo_size
from sys.v_$log ) c
Monday, January 4, 2010
Manually configure the Oracle 10g EM dbconsole
Manually configure the Oracle 10g EM dbconsole
Overview
When you choose to create a preconfigured database during the Oracle 10g installation, you can select the Oracle Enterprise Manager (OEM) interface that you want to use to manage the database. The following options are available:
Database Grid Control
This option is available only if an Oracle Management Agent is installed on the system. When the Installer detects an Oracle Management Agent on the system, it allows you to choose this option and specify the Oracle Management Service that you want to use to manage the database.
If an Oracle Management Agent is not installed, you must choose to use Database Control to manage the database. However, if you install Oracle Management Agent after you install Oracle Database, you can then use Grid Control to manage this database.
Database Control
This option is selected by default if an Oracle Management Agent is not installed on the system. However, even if a Management Agent is installed, you can still choose to configure Database Control to manage the database.
Custom installation
If you choose the Custom installation type or the Advanced database configuration option during the installation, the Installer does not display the OEM setup screens. Instead, it runs the Database Configuration Assistant (DBCA) in interactive mode, which enables you to create a custom database.
DBCA also enables you to specify the Oracle Enterprise Manager interface that you want to use. Furthermore, you can also use DBCA after the installation to configure Database Control for a database that was not previously configured to use it.
However, if you decide to setup your own Database, you must install the Database Control manually.
Setup your own Database and manually install the Database Control
The Database Control relies on various underlying technologies to discover, monitor, and administer the Oracle Database environment.
From the Database Control, you can monitor and administer a single Oracle Database instance.
The Database Control Framework consists of the Database Control and its underlying technologies:
A local version of the Oracle Management Service designed to work with the local database or clustered database.
A local Oracle Management Repository installed in the local database and designed to store management data for the Database Control.
The following steps have to be performed.
Create your own Database
Create the Database Control Repository and setup the OC4J Application Server
Make sure, that you can connect to the Repository Database (Test it with SQL*Plus). Examples for Setup Files can be found here:
Windows
Linux
Now start the Oracle EM dbconsole Build Script
Windows Environment
Here Database DBPORD is using listener port number 1526 but OEM uses listener port number 1521.
d:\PROD\ADMIN\SCRIPTS\com>emca -repos create
STARTED EMCA at Oct 10, 2008 5:34:08 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: DBPORD
Listener port number: 1526
Password for SYS user:
Password for SYSMAN user:
Do you wish to continue? [yes(Y)/no(N)]: y
Oct 10, 2008 5:34:59 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at D:\oracle\product\10.2.0\db_1\cfgtoollog
s\emca\DBPORD\emca_2008-10-10_05-34-08-PM.log.
Oct 10, 2008 5:35:01 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Oct 10, 2008 5:36:50 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Enterprise Manager configuration completed successfully
FINISHED EMCA at Oct 10, 2008 5:36:50 PM
D:\PROD\ADMIN\SCRIPTS\COM>emca -config dbcontrol db
STARTED EMCA at Oct 10, 2008 5:43:43 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: DBPORD
Listener port number: 1526
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------
You have specified the following settings
Database ORACLE_HOME ................ D:\oracle\product\10.2.0\db_1
Database hostname ................ lhopis01.myCompany.ca
Listener port number ................ 1526
Database SID ................ DBPORD
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: y
Oct 10, 2008 5:45:04 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at D:\oracle\product\10.2.0\db_1\cfgtoollog
s\emca\DBPORD\emca_2008-10-10_05-43-43-PM.log.
Oct 10, 2008 5:45:21 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Oct 10, 2008 5:46:01 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Oct 10, 2008 5:46:01 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://lhopis01.lh.myCompany.ca:1158/em
<<<<<<<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at Oct 10, 2008 5:46:01 PM
Automatically start and stop the DB-Console
D:\PROD\ADMIN\SCRIPTS\COM> emctl start dbconsole
D:\PROD\ADMIN\SCRIPTS\COM> emctl stop dbconsole
D:\PROD\ADMIN\SCRIPTS\COM> emctl status dbconsole
Troubleshooting
Check \oracle\product\10.2.0\db_1\sysman\config
Check listener is up and running
Overview
When you choose to create a preconfigured database during the Oracle 10g installation, you can select the Oracle Enterprise Manager (OEM) interface that you want to use to manage the database. The following options are available:
Database Grid Control
This option is available only if an Oracle Management Agent is installed on the system. When the Installer detects an Oracle Management Agent on the system, it allows you to choose this option and specify the Oracle Management Service that you want to use to manage the database.
If an Oracle Management Agent is not installed, you must choose to use Database Control to manage the database. However, if you install Oracle Management Agent after you install Oracle Database, you can then use Grid Control to manage this database.
Database Control
This option is selected by default if an Oracle Management Agent is not installed on the system. However, even if a Management Agent is installed, you can still choose to configure Database Control to manage the database.
Custom installation
If you choose the Custom installation type or the Advanced database configuration option during the installation, the Installer does not display the OEM setup screens. Instead, it runs the Database Configuration Assistant (DBCA) in interactive mode, which enables you to create a custom database.
DBCA also enables you to specify the Oracle Enterprise Manager interface that you want to use. Furthermore, you can also use DBCA after the installation to configure Database Control for a database that was not previously configured to use it.
However, if you decide to setup your own Database, you must install the Database Control manually.
Setup your own Database and manually install the Database Control
The Database Control relies on various underlying technologies to discover, monitor, and administer the Oracle Database environment.
From the Database Control, you can monitor and administer a single Oracle Database instance.
The Database Control Framework consists of the Database Control and its underlying technologies:
A local version of the Oracle Management Service designed to work with the local database or clustered database.
A local Oracle Management Repository installed in the local database and designed to store management data for the Database Control.
The following steps have to be performed.
Create your own Database
Create the Database Control Repository and setup the OC4J Application Server
Make sure, that you can connect to the Repository Database (Test it with SQL*Plus). Examples for Setup Files can be found here:
Windows
Linux
Now start the Oracle EM dbconsole Build Script
Windows Environment
Here Database DBPORD is using listener port number 1526 but OEM uses listener port number 1521.
d:\PROD\ADMIN\SCRIPTS\com>emca -repos create
STARTED EMCA at Oct 10, 2008 5:34:08 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: DBPORD
Listener port number: 1526
Password for SYS user:
Password for SYSMAN user:
Do you wish to continue? [yes(Y)/no(N)]: y
Oct 10, 2008 5:34:59 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at D:\oracle\product\10.2.0\db_1\cfgtoollog
s\emca\DBPORD\emca_2008-10-10_05-34-08-PM.log.
Oct 10, 2008 5:35:01 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Oct 10, 2008 5:36:50 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Enterprise Manager configuration completed successfully
FINISHED EMCA at Oct 10, 2008 5:36:50 PM
D:\PROD\ADMIN\SCRIPTS\COM>emca -config dbcontrol db
STARTED EMCA at Oct 10, 2008 5:43:43 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: DBPORD
Listener port number: 1526
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------
You have specified the following settings
Database ORACLE_HOME ................ D:\oracle\product\10.2.0\db_1
Database hostname ................ lhopis01.myCompany.ca
Listener port number ................ 1526
Database SID ................ DBPORD
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............
-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: y
Oct 10, 2008 5:45:04 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at D:\oracle\product\10.2.0\db_1\cfgtoollog
s\emca\DBPORD\emca_2008-10-10_05-43-43-PM.log.
Oct 10, 2008 5:45:21 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Oct 10, 2008 5:46:01 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Oct 10, 2008 5:46:01 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://lhopis01.lh.myCompany.ca:1158/em
<<<<<<<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at Oct 10, 2008 5:46:01 PM
Automatically start and stop the DB-Console
D:\PROD\ADMIN\SCRIPTS\COM> emctl start dbconsole
D:\PROD\ADMIN\SCRIPTS\COM> emctl stop dbconsole
D:\PROD\ADMIN\SCRIPTS\COM> emctl status dbconsole
Troubleshooting
Check \oracle\product\10.2.0\db_1\sysman\config
Check listener is up and running
Tuesday, September 29, 2009
All Control file lost Recover database from RMAN Backup (ORA-00205: error in identifying control file)
All Control file lost Recover database from RMAN Backup (ORA-00205: error in identifying control file)
During media failure (single disk crash) the online redo log is usually available so it is possible to recover up to the point of failure. Quite often ,Windows Oracle instance can be started in nomount mode (i.e. spfile is also available).
In this scenario all Control files are lost and it’s not multiplexed. 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.
During media failure (single disk crash) the online redo log is usually available so it is possible to recover up to the point of failure. Quite often ,Windows Oracle instance can be started in nomount mode (i.e. spfile is also available).
In this scenario all Control files are lost and it’s not multiplexed. 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.
In this example RMAN is not using catalog.
Try to start database and found following error.
d:> sqlplus sys/**** as sysdba;
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Sep 29 11:05:12 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to an idle instance.
ORCL: SQL> startup;
ORACLE instance started.
Total System Global Area 524288000 bytes
Fixed Size 1291576 bytes
Variable Size 306186952 bytes
Database Buffers 213909504 bytes
Redo Buffers 2899968 bytes
ORA-00205: error in identifying control file, check alert log for more info
In Alter log file I found following error:
Tue Sep 29 11:05:18 2009
ALTER DATABASE MOUNT
Tue Sep 29 11:05:18 2009
ORA-00202: control file: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
Complete Recovery by last night RMAN BACKUP set
1. d:\> rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Tue Sep 29 10:19:36 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (not mounted)
2. RMAN> set dbid=1096578125
executing command: SET DBID
****set DBID - get this from the name of the controlfile autobackup.
For example, if autobackup name is CTL_SP_BAK_C-1507972899-20050124-00 the the DBID is 1507972899. This step will not be required if the instance is
3. RMAN> set controlfile autobackup format for device type disk to 'D:\ABC\BACKUP\RMAN\ORCL\cf_%d_%F';
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
using target database control file instead of recovery catalog
**** Verify the format of your backup set
4. RMAN> restore controlfile from autobackup;
Starting restore at 29-SEP-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
recovery area destination: D:\oracle\product\10.2.0\flash_recovery_area
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: no autobackups found in the recovery area
channel ORA_DISK_1: looking for autobackup on day: 20090929
channel ORA_DISK_1: looking for autobackup on day: 20090928
channel ORA_DISK_1: autobackup found: D:\ABC\BACKUP\RMAN\ORCL\cf_ORCL_c-10965
78125-20090928-03
channel ORA_DISK_1: control file restore from autobackup complete
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTL
output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03.CTL
Finished restore at 29-SEP-09
*** Now that control files have been restored, the instance can mount the
5. RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
6. RMAN> restore database;
Starting restore at 29-SEP-09
Starting implicit crosscheck backup at 29-SEP-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 7 objects
Finished implicit crosscheck backup at 29-SEP-09
Starting implicit crosscheck copy at 29-SEP-09
using channel ORA_DISK_1
Finished implicit crosscheck copy at 29-SEP-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\ORCL\ARCHIVELOG\2009_09_28\O1_MF_1_48_5D1QDPFJ_.ARC
File Name: D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_28\O1_MF_1_49_5D21CNOB_.ARC
File Name: D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_28\O1_MF_1_50_5D21CPS7_.ARC
File Name: D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_29\O1_MF_1_51_5D38N85L_.ARC
File Name: D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_29\O1_MF_1_52_5D45J1YK_.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\ORCL\SYSTEM01.DBF
restoring datafile 00002 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
restoring datafile 00003 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
restoring datafile 00004 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
restoring datafile 00005 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_01.DBF
restoring datafile 00006 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_02.DBF
restoring datafile 00007 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS1_01.DBF
restoring datafile 00008 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS1_02.DBF
restoring datafile 00009 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS2_01.DBF
restoring datafile 00010 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS2_02.DBF
restoring datafile 00011 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS3_01.DBF
restoring datafile 00012 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS3_02.DBF
restoring datafile 00013 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS4_01.DBF
restoring datafile 00014 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS4_02.DBF
restoring datafile 00015 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS5_01.DBF
restoring datafile 00016 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS5_02.DBF
restoring datafile 00017 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS6_01.DBF
restoring datafile 00018 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS6_02.DBF
restoring datafile 00019 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_S_01.DBF
restoring datafile 00020 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_S_02.DBF
restoring datafile 00021 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_SI_01.DBF
restoring datafile 00022 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_SI_02.DBF
restoring datafile 00023 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_HL7_01.DBF
restoring datafile 00024 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_HL7_02.DBF
restoring datafile 00025 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_HL7I_01.DB
F
restoring datafile 00026 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_HL7I_02.DB
F
restoring datafile 00027 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_AUD_01.DBF
restoring datafile 00028 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_AUD_02.DBF
restoring datafile 00029 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_AUDI_01.DB
F
restoring datafile 00030 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_AUDI_02.DB
F
restoring datafile 00031 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_RHAPSODY_0
1.DBF
restoring datafile 00032 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_RHAPSODY_0
2.DBF
restoring datafile 00033 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_OPIS_RPT_0
1.DBF
restoring datafile 00034 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\OPIS_OPIS_RPT_0
2.DBF
channel ORA_DISK_1: reading from backup piece D:\ABC\BACKUP\RMAN\ORCL\DB_ORCL
_T698759044_S731_P1
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\ABC\BACKUP\RMAN\ORCL\DB_ORCL_T698759044_S731_P1 tag=FULL BACK
UP
channel ORA_DISK_1: restore complete, elapsed time: 00:01:39
Finished restore at 29-SEP-09
** Database must be recovered because all datafiles have been restored from backup
7. RMAN> recover database;
Starting recover at 29-SEP-09
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 48 is already on disk as file D:\ORACLE\PRODUCT\10
.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_28\O1_MF_1_48_5D1QDPFJ_.ARC
archive log thread 1 sequence 49 is already on disk as file D:\ORACLE\PRODUCT\10
.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_28\O1_MF_1_49_5D21CNOB_.ARC
archive log thread 1 sequence 50 is already on disk as file D:\ORACLE\PRODUCT\10
.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_28\O1_MF_1_50_5D21CPS7_.ARC
archive log thread 1 sequence 51 is already on disk as file D:\ORACLE\PRODUCT\10
.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_29\O1_MF_1_51_5D38N85L_.ARC
archive log thread 1 sequence 52 is already on disk as file D:\ORACLE\PRODUCT\10
.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2009_09_29\O1_MF_1_52_5D45J1YK_.ARC
archive log thread 1 sequence 53 is already on disk as file D:\ORACLE\PRODUCT\10
.2.0\ORADATA\ORCL\REDO02.LOG
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=46
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=47
channel ORA_DISK_1: reading from backup piece D:\ABC\BACKUP\RMAN\ORCL\ARCH_P
OM_1_T698759096_S734_P1
channel ORA_DISK_1: restored backup piece 1
piece handle=D:\ABC\BACKUP\RMAN\ORCL\ARCH_ORCL_1_T698759096_S734_P1 tag=TAG20
090928T114456
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_29\O1_MF_1_46_5D475JO8_.ARC thread=1 sequence=46
channel default: deleting archive log(s)
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_29\O1_MF_1_46_5D475JO8_.ARC recid=641 stamp=698841715
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_29\O1_MF_1_47_5D475K17_.ARC thread=1 sequence=47
channel default: deleting archive log(s)
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_29\O1_MF_1_47_5D475K17_.ARC recid=640 stamp=698841713
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_28\O1_MF_1_48_5D1QDPFJ_.ARC thread=1 sequence=48
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_28\O1_MF_1_49_5D21CNOB_.ARC thread=1 sequence=49
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_28\O1_MF_1_50_5D21CPS7_.ARC thread=1 sequence=50
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_29\O1_MF_1_51_5D38N85L_.ARC thread=1 sequence=51
archive log filename=D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG
\2009_09_29\O1_MF_1_52_5D45J1YK_.ARC thread=1 sequence=52
archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG thread=1 se
quence=53
media recovery complete, elapsed time: 00:00:18
Finished recover at 29-SEP-09
8. RMAN> alter database open;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 09/29/2009 10:42:28
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
-- Recovery completed. The database must be opened with RESETLOGS
-- because a backup control file was used. Can also use
-- "alter database open resetlogs" instead.
9. RMAN> alter database open resetlogs;
database opened
**** Several points are worth emphasizing.*****
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.
After recovery using a backup controlfile, all temporary files associated with locallymanaged tablespaces are no longer available. You can check that this is so by querying the view V$TEMPFILE - no rows will be returned. Therefore tempfiles must be added (or recreated) before the database is made available for general use. In the case at hand, the tempfile already exists so we merely add it to the temporary tablespace. This can be done using SQLPlus or any tool of your choice:
SQL> alter tablespace temp add tempfile
'D:\oracle_data\datafiles\ORCL\TEMP01.DBF';
Tablespace altered.
Tuesday, July 28, 2009
RMAN-00554: initialization of internal recovery manager package failed/RMAN-04005: error from target database:/ORA-01031: insufficient privileges
Platform: Oracle 10.2.0.3.0 on Windows 2003 server
*** Logged onto Windows 2003 server Local Windows user which is a member of Administrator and Ora_DBA group. Same database user is created into database and which is authenticated by OS.
Problem:
When try to connect RMAN for database backup following error happen.
Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.
d:\OPIS2005\ADMIN\SCRIPTS\com>set oracle_sid=POM
d:\OPIS2005\ADMIN\SCRIPTS\com>rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Tue Jul 28 15:05:33 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN-00571: =================================================
RMAN-00569: =========== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: =================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-01031: insufficient privileges
Logged on to the box with a local user that is a member of both thelocal Administrators group and the local ora_dba group.Connection to db with sqlplus, using local credentials:
d:\OPIS2005\ADMIN\SCRIPTS\com>sqlplus /
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jul 28 15:06:51 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Release 10.2.0.3.0 - Production
POM: SQL>
But connecting to rman requires me to spell it out ...d:\OPIS2005\ADMIN\SCRIPTS\com>sqlplus /
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jul 28 15:06:51 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Release 10.2.0.3.0 - Production
POM: SQL>
Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.
d:\OPIS2005\ADMIN\SCRIPTS\COM>rman target sys/manager1@lom.lhsc.world
Recovery Manager: Release 10.2.0.3.0 - Production on Tue Jul 28 15:10:13 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: LOM (DBID=2640997403)
RMAN>
*** Local Windows user is a member of Administrator and Ora_DBA group. Same database user is created into database and which is authenticated by OS.
d:\opis2005\ADMIN\SCRIPTS\COM ( POM )sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.3.0 - Production on Tue Jul 28 15:23:28 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Release 10.2.0.3.0 - Production
POM: SQL>
So, things are correctly set up for connecting with local sysdbacredentials
SOLUTION:
We found 10g oem agent installed on same box in a different HOME.
The box had two Oracle homes on it, one for a single 10g datbase and one for the 10g oem agent. In the PATH environment variable, the agent home preceded the db home.
Example: C:\OracleHomes\agent10g\jlib;C:\OracleHomes\agent10g\bin;D:\oracle\product\10.2.0\db_1\bin;C:\Program Files\HP\NCU;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;C:\Program Files\OmniBack\bin\
Flipping that -- putting the db home first -- solved the problem and allowedd:\OPIS2005\ADMIN\SCRIPTS\com>rman target /' to connect as it is supposed to.
Subscribe to:
Posts (Atom)