Problem Description
Oracle physical standby database is not able to synchronize with the primary database. From the standby database alert log we see the following logs.
Thu Sep 23 14:57:47 2010
RFS[2237]: Assigned to RFS process 8174
RFS[2237]: Identified database type as 'physical standby': Client is ARCH pid 25089
RFS[2236]: Opened log for thread 2 sequence 1532 dbid 1323895516 branch 728508318
RFS[2237]: Opened log for thread 2 sequence 1533 dbid 1323895516 branch 728508318
Thu Sep 23 14:58:32 2010
RFS[2226]: Possible network disconnect with primary database
Deleted Oracle managed file +RECOVERY/bdafisdrs/archivelog/2010_09_23/thread_1_seq_2044.9918.730479031
Thu Sep 23 14:58:33 2010
RFS[2176]: Possible network disconnect with primary database
Deleted Oracle managed file +RECOVERY/bdafisdrs/archivelog/2010_09_23/thread_2_seq_1466.9896.730477033
Thu Sep 23 14:58:35 2010
RFS[2168]: Possible network disconnect with primary database
Deleted Oracle managed file +RECOVERY/bdafisdrs/archivelog/2010_09_23/thread_2_seq_1545.9817.730476973
Thu Sep 23 14:58:40 2010
RFS[2238]: Assigned to RFS process 8319
RFS[2238]: Identified database type as 'physical standby': Client is ARCH pid 2094
RFS[2238]: Opened log for thread 2 sequence 1466 dbid 1323895516 branch 728508318
Thu Sep 23 14:58:41 2010
RFS[2239]: Assigned to RFS process 8321
RFS[2239]: Identified database type as 'physical standby': Client is ARCH pid 25230
Thu Sep 23 14:58:43 2010
RFS[2240]: Assigned to RFS process 8323
RFS[2240]: Identified database type as 'physical standby': Client is ARCH pid 31899
RFS[2240]: Opened log for thread 1 sequence 2044 dbid 1323895516 branch 728508318
Thu Sep 23 14:58:51 2010
RFS[2241]: Assigned to RFS process 8334
RFS[2241]: Identified database type as 'physical standby': Client is LGWR SYNC pid 22086
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
Standby controlfile consistent with primary
RFS[2241]: No standby redo logfiles of size 102400 blocks available
RFS[2241]: No standby redo logfiles selected (reason:7)
Errors in file /u01/app/oracle/diag/rdbms/bdafisdrs/bdafisdc1/trace/bdafisdc1_rfs_8334.trc:
ORA-16086: Redo data cannot be written to the standby redo log
Thu Sep 23 14:58:59 2010
RFS[2242]: Assigned to RFS process 8341
RFS[2242]: Identified database type as 'physical standby': Client is LGWR SYNC pid 6586
Thu Sep 23 14:58:59 2010
RFS[2178]: Possible network disconnect with primary database
From the trace file it is logged following entry.
[oracle@DRS-DB-01 ~]$ cat /u01/app/oracle/diag/rdbms/bdafisdrs/bdafisdc1/trace/bdafisdc1_rfs_8334.trc
Trace file /u01/app/oracle/diag/rdbms/bdafisdrs/bdafisdc1/trace/bdafisdc1_rfs_8334.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing option
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name: Linux
Node name: DRS-DB-01
Release: 2.6.18-92.el5
Version: #1 SMP Tue Apr 29 13:16:15 EDT 2008
Machine: x86_64
Instance name: bdafisdc1
Redo thread mounted by this instance: 1
Oracle process number: 124
Unix process pid: 8334, image: oracle@DRS-DB-01
*** 2010-09-23 14:58:52.578
*** SESSION ID:(88.91) 2010-09-23 14:58:52.578
*** CLIENT ID:() 2010-09-23 14:58:52.578
*** SERVICE NAME:() 2010-09-23 14:58:52.578
*** MODULE NAME:(oracle@DC-DB-01 (TNS V1-V3)) 2010-09-23 14:58:52.578
*** ACTION NAME:() 2010-09-23 14:58:52.578
The primary database is operating in MAXIMUM PROTECTION
or MAXIMUM AVAILABILITY mode, and the standby database does
not contain any viable standby redo logfiles.
ORA-16086: Redo data cannot be written to the standby redo log
Cause of the Problem
The problem happened due to recovery area of standby database becomes full and standby redo logs have no place for archival. As the standby redo logs can't be archived, so they can't free space in order to accommodate/write new logs.
Solution of the Problem
Increase the recovery area size of standby database. You can check the location of archived redo log file by,
SQL> connect / as sysdba
SQL> archive log list
If you see that archive destination is USE_DB_RECOVERY_FILE_DEST then the location will be specified by parameter DB_RECOVERY_FILE_DEST.
SQL> SHOW PARAMETER DB_RECOVERY_FILE_DEST
+RECOVERY
Note that you have sufficient storage under +RECOVERY and also you have set proper size of the parameter db_recovery_file_dest_size.
To check the size of parameter db_recovery_file_dest_size issue,
SQL> show parameter db_recovery_file_dest_size
In order to increase the size of db_recovery_file_dest_size parameter issue,
SQL> alter system set db_recovery_file_dest_size = 1000G scope=both sid='*';
Showing posts with label Redo Log. Show all posts
Showing posts with label Redo Log. Show all posts
Friday, September 24, 2010
Tuesday, February 16, 2010
ORA-01624: log needed for crash recovery
Error Description
In the database dropping standby redo logfile fails with error ORA-01624 as below.
SQL> alter database drop standby logfile group 2;
alter database drop standby logfile group 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance ctl3 (thread 1)
ORA-00312: online log 2 thread 1: '/oradata3/ctl3/redo02.log'
ORA-00312: online log 2 thread 1: '/oraidx3/ctl3/redo02b.log'
Cause of the Problem
The Online redo log which is being dropped on the primary is either the current online redo log or
it has not completely been archived yet. To know more about the redo logfile status see Redo Log file status. By querying the V$LOG view you can see the status of the redo log file. In order to drop a redo log file the status column should be 'INACTIVE'.
Solution of the Problem
1) Switch the logfile on primary database so that group can become non-current.
SQL> ALTER DATABASE SWITCH LOGFILE;
2) Copy the standby logfile from primary database to standby database.
3) In the primary database move the logfile to a different location
4) Rename the logfile
5) Clear the logfile using 'alter database clear logfile group ..'
6) Drop the standby logfile using 'alter database drop standby logfile group ...'
Related Documents
In the database dropping standby redo logfile fails with error ORA-01624 as below.
SQL> alter database drop standby logfile group 2;
alter database drop standby logfile group 2
*
ERROR at line 1:
ORA-01624: log 2 needed for crash recovery of instance ctl3 (thread 1)
ORA-00312: online log 2 thread 1: '/oradata3/ctl3/redo02.log'
ORA-00312: online log 2 thread 1: '/oraidx3/ctl3/redo02b.log'
Cause of the Problem
The Online redo log which is being dropped on the primary is either the current online redo log or
it has not completely been archived yet. To know more about the redo logfile status see Redo Log file status. By querying the V$LOG view you can see the status of the redo log file. In order to drop a redo log file the status column should be 'INACTIVE'.
Solution of the Problem
1) Switch the logfile on primary database so that group can become non-current.
SQL> ALTER DATABASE SWITCH LOGFILE;
2) Copy the standby logfile from primary database to standby database.
3) In the primary database move the logfile to a different location
4) Rename the logfile
5) Clear the logfile using 'alter database clear logfile group ..'
6) Drop the standby logfile using 'alter database drop standby logfile group ...'
Related Documents
Startup fails with oracle error ORA-00119, ORA-00132
Saturday, January 16, 2010
ORA-16014 ORA-00312 ORA-16038 ORA-19809: limit exceeded for recovery files
Problem Description:
From alert log, we find the following errors
Flash recovery area is used for archival destination. ORA-16014 ORA-00312 ORA-16038 ORA-19809: limit exceeded for recovery files will be raised because there is no space to create additional archive log in the flash recovery area. Note that the error inside trace file "ORA-19815: WARNING: db_recovery_file_dest_size of 99857989632 bytes is 70.10% used, and has 29852624896 remaining bytes available." can be misleading. Though it says 70.10% used but actually it is 100% used. Ensure by issuing following query in the database,
Consequences of the Error
Due to ORA-16014, ORA-00312, ORA-16038 and ORA-19809 there might have following effects to database.
1) Database archive process stops and database hangs.
2) Users are not able to connect to database.
3) If you try to open database you are not able to open database bypass these errors.
4) In the Alert log/Trace file Flash Recovery Area reports ORA-19809: limit exceeded for recovery files.
Solution of the Problem
There may have several solutions to solve above problems.
Solution 00: Ensure that you have enough space in the underlying directory
This step is common and application for all solution methods.
In the underlying mount point of your archival destination ensure that you have enough space. That is ensure that your hard disk is not full. If you don't have sufficient space in your disk space free up the space by deleting unnecessary files.
On Linux/Solaris you can check disk space of all mount points by $df -h
On HP-UX issue $df -k
Solution 01: Increase the size of Flash recovery area
The size of flash recovery area is determine by the parameter db_recovery_file_dest_size. You can check the current size of flash recovery area in Sql*plus by issuing,
Solution 02: Change the database archival location outside FRA
You can solve the problem if you stop using flash recovery area for your archival destination. You can disable using flash recovery area by issuing following query,
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='' SCOPE=BOTH SID='*';
System altered.
After disabling flash recovery area you will see your archival location is changed to database default archival location. If you have enough space in your default archive location (you can check it by issuing "archive log list" after connecting as sysdba) you should no longer get above errors.
Solution 03: Free up space in FRA
If you use RMAN to database backup and store backups to FRA then check space distribution in the FRA by following query,
SQL>select file_type, percent_space_used as used,percent_space_reclaimable as reclaimable,
number_of_files as "number" from v$flash_recovery_area_usage;
After issuing query if you see almost all the space are used by Archivelogs and backup pieces then there is no space to reclaim. So you need to free up some spaces with by of the following methods.
a) Take backup of archivelogs to some other locations and delete the archivelogs from flash recovery area.
You can delete older archivelogs say before 7 days ago by following RMAN command.
RMAN>Delete archivelog all completed before 'SYSDATE-7';
b) If flashback logs are enable then make sure you have enough space for all the flashback logs. If you don't need flashback logs you can disable it by command in mount stage.
SQL>Alter database FLASHBACK OFF;
c) Note that if you have guaranteed restore point then flashback logs will not be reclaimed by flash recovery area. Even due to some bugs Flashback logs are not reclaimed by flash recovery area when using guaranteed restore point (after dropping guaranteed restore point) or when changing db_flashback_retention_target to a lower value. If there is such guaranteed restore point and it is no longer needed you can delete by,
SQL> Drop restore point restore_point_name;
d) From your archival location using any operating system utility like rm or del or delete you can remove the backups/archive log files. After you remove the files the OS knows that you have deleted files but still you need to inform the database to be aware of the resulting free space.
To inform database about free space connect as RMAN and then issue following commands.
RMAN>CROSSCHECK BACKUP;
RMAN>CROSSCHECK ARCHIVELOG ALL;
RMAN>Delete expired backup;
RMAN>Delete expired archivelog all;
RMAN>Delete force obsolete;
Solution 4: Scenarios based on Database Backup Strategy
Follow the scenarios as demonstrated in topic Database Startup fails with error ORA-16038,ORA-19809, ORA-00312. In the post it is demonstrated what we will do if we don't (in Solution C:) have any backup and also the secnarios if we (Solution D:) have recent backup and only need archivelogs.
Related Documents
Set up Flash Recovery Area for RMAN
Database Startup fails with error ORA-16038,ORA-19809, ORA-00312
From alert log, we find the following errors
Sat Jan 16 03:08:36 2010If we check the content of trace file /SIDS/app/oracle/admin/OMSRPS/bdump/omsrps_arc4_9885.trc we see,
ARCH: Archival stopped, error occurred. Will continue retrying
Sat Jan 16 03:08:36 2010
ORACLE Instance OMSRPS - Archival Error
Sat Jan 16 03:08:36 2010
ORA-16014: log 6 sequence# 11181 not archived, no available destinations
ORA-00312: online log 6 thread 1: '/SIDS1/oradata/OMSRPS/stdby_redo02.log'
ORA-00312: online log 6 thread 1: '/SIDS2/oradata/OMSRPS/stdby_redo02.log'
Sat Jan 16 03:08:36 2010
Errors in file /SIDS/app/oracle/admin/OMSRPS/bdump/omsrps_arc4_9885.trc:
ORA-16014: log 6 sequence# 11181 not archived, no available destinations
ORA-00312: online log 6 thread 1: '/SIDS1/oradata/OMSRPS/stdby_redo02.log'
ORA-00312: online log 6 thread 1: '/SIDS2/oradata/OMSRPS/stdby_redo02.log'
ORA-19815: WARNING: db_recovery_file_dest_size of 99857989632 bytes is 70.10% used, and has 29852624896 remaining bytes available.Cause of the Problem
*** 2010-01-16 03:23:54.463
************************************************************************
You have following choices to free up space from flash recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
*** 2010-01-16 03:23:54.464 62692 kcrr.c
ARC4: Error 19809 Creating archive log file to '/backup/flash_recovery_area/OMSRPS/archivelog/2010_01_16/o1_mf_1_11181_%u_.arc'
*** 2010-01-16 03:23:54.464 60970 kcrr.c
kcrrfail: dest:1 err:19809 force:0 blast:1
ARCH: Connecting to console port...
ARCH: Connecting to console port...
*** 2010-01-16 03:23:54.513 21373 kcrr.c
ORA-16038: log 6 sequence# 11181 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 6 thread 1: '/SIDS1/oradata/OMSRPS/stdby_redo02.log'
ORA-00312: online log 6 thread 1: '/SIDS2/oradata/OMSRPS/stdby_redo02.log'
Flash recovery area is used for archival destination. ORA-16014 ORA-00312 ORA-16038 ORA-19809: limit exceeded for recovery files will be raised because there is no space to create additional archive log in the flash recovery area. Note that the error inside trace file "ORA-19815: WARNING: db_recovery_file_dest_size of 99857989632 bytes is 70.10% used, and has 29852624896 remaining bytes available." can be misleading. Though it says 70.10% used but actually it is 100% used. Ensure by issuing following query in the database,
SQL> set lines 120We see in the flash recovery area space limit and space used are almost same and no more space for archival. Hence we get error.
SQL> col name format a50
SQL> select name
,floor(space_limit / 1024 / 1024) "Size MB"
,ceil(space_used / 1024 / 1024) "Used MB"
from v$recovery_file_dest
order by name
/
NAME Size MB Used MB
------------------------------------------- --------- ------------
/backup/flash_recovery_area 95232 95152
Consequences of the Error
Due to ORA-16014, ORA-00312, ORA-16038 and ORA-19809 there might have following effects to database.
1) Database archive process stops and database hangs.
2) Users are not able to connect to database.
3) If you try to open database you are not able to open database bypass these errors.
4) In the Alert log/Trace file Flash Recovery Area reports ORA-19809: limit exceeded for recovery files.
Solution of the Problem
There may have several solutions to solve above problems.
Solution 00: Ensure that you have enough space in the underlying directory
This step is common and application for all solution methods.
In the underlying mount point of your archival destination ensure that you have enough space. That is ensure that your hard disk is not full. If you don't have sufficient space in your disk space free up the space by deleting unnecessary files.
On Linux/Solaris you can check disk space of all mount points by $df -h
On HP-UX issue $df -k
Solution 01: Increase the size of Flash recovery area
The size of flash recovery area is determine by the parameter db_recovery_file_dest_size. You can check the current size of flash recovery area in Sql*plus by issuing,
SYS@OMSRPS> show parameter db_recovery_fileor by issuing query,
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /backup/flash_recovery_area
db_recovery_file_dest_size big integer 93G
SQL> select value from v$parameter where name='db_recovery_file_dest_size';Here, I have increased to 95G. Ensure by issuing,
VALUE
-----------------------------------------------------------------------------
99857989632
In order to increase the size of the parameter issue following query.
SYS@OMSRPS> alter system set db_recovery_file_dest_size = 95G scope=both;
System altered.
SYS@OMSRPS> show parameter db_recovery_fileNow check your alert log file and you see Archiver process freed from errors.
NAME TYPE VALUE
------------------------------------ ----------- --------------------------
db_recovery_file_dest string /backup/flash_recovery_area
db_recovery_file_dest_size big integer 95G
Sat Jan 16 03:37:31 2010
ALTER SYSTEM SET db_recovery_file_dest_size='95G' SCOPE=BOTH;
Sat Jan 16 03:38:25 2010
Archiver process freed from errors. No longer stopped
Sat Jan 16 03:38:25 2010
Primary database is in MAXIMUM PERFORMANCE mode
RFS[9]: No standby redo logfiles of size 512000 blocks available
Sat Jan 16 03:38:27 2010
Media Recovery Waiting for thread 1 sequence 11186 (in transit)
Solution 02: Change the database archival location outside FRA
You can solve the problem if you stop using flash recovery area for your archival destination. You can disable using flash recovery area by issuing following query,
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='' SCOPE=BOTH SID='*';
System altered.
After disabling flash recovery area you will see your archival location is changed to database default archival location. If you have enough space in your default archive location (you can check it by issuing "archive log list" after connecting as sysdba) you should no longer get above errors.
Solution 03: Free up space in FRA
If you use RMAN to database backup and store backups to FRA then check space distribution in the FRA by following query,
SQL>select file_type, percent_space_used as used,percent_space_reclaimable as reclaimable,
number_of_files as "number" from v$flash_recovery_area_usage;
After issuing query if you see almost all the space are used by Archivelogs and backup pieces then there is no space to reclaim. So you need to free up some spaces with by of the following methods.
a) Take backup of archivelogs to some other locations and delete the archivelogs from flash recovery area.
You can delete older archivelogs say before 7 days ago by following RMAN command.
RMAN>Delete archivelog all completed before 'SYSDATE-7';
b) If flashback logs are enable then make sure you have enough space for all the flashback logs. If you don't need flashback logs you can disable it by command in mount stage.
SQL>Alter database FLASHBACK OFF;
c) Note that if you have guaranteed restore point then flashback logs will not be reclaimed by flash recovery area. Even due to some bugs Flashback logs are not reclaimed by flash recovery area when using guaranteed restore point (after dropping guaranteed restore point) or when changing db_flashback_retention_target to a lower value. If there is such guaranteed restore point and it is no longer needed you can delete by,
SQL> Drop restore point restore_point_name;
d) From your archival location using any operating system utility like rm or del or delete you can remove the backups/archive log files. After you remove the files the OS knows that you have deleted files but still you need to inform the database to be aware of the resulting free space.
To inform database about free space connect as RMAN and then issue following commands.
RMAN>CROSSCHECK BACKUP;
RMAN>CROSSCHECK ARCHIVELOG ALL;
RMAN>Delete expired backup;
RMAN>Delete expired archivelog all;
RMAN>Delete force obsolete;
Solution 4: Scenarios based on Database Backup Strategy
Follow the scenarios as demonstrated in topic Database Startup fails with error ORA-16038,ORA-19809, ORA-00312. In the post it is demonstrated what we will do if we don't (in Solution C:) have any backup and also the secnarios if we (Solution D:) have recent backup and only need archivelogs.
Related Documents
Set up Flash Recovery Area for RMAN
Database Startup fails with error ORA-16038,ORA-19809, ORA-00312
Interact with RMAN Client.
Saturday, December 5, 2009
Database Architecture Exercises - Control file Redo logfile Examples
The Oracle database uses two very important files for database support, stability, and recovery. The Control file is one of the most important files in the database, as it is the file that keeps track of where everything is and where it should be at any given time. Since the database itself only needs one Control File to operate with, multiplexing the Control File is a very important part of the Oracle Architecture. In this lab, you will create a third Control File for the database.
Another essential file structure for successful operation of an Oracle database is the Redo Log file. This file contains listings of every transaction that takes place in the database, and it is essential in database recovery and restoration. Log Files exist in Log Groups, and each Log Group must have at least one file--although it is recommended that there be a minimum of two per group and that they exist on separate disks. In this lab, you will also create a new Log Group containing two files.
The following steps will outline the processes needed to complete this lab. Keep in mind that the file paths shown in the lab specs may vary slightly from your file paths, depending on the database instance you have. The expected results examples given in each step are provided as a guide only. The specific results of your work may vary slightly, but should be similar enough to determine whether or not you have successfully completed the step.
VERY IMPORTANT – Setting up for your lab
Creating a SPOOL session: Be sure that you start a spool session before you start executing your SQL code when working on the lab. Your SPOOL file name should be DATABASE_lab3. If you do the lab in several sessions, be sure that you use a different file name so as not to overwrite the existing file (for example DATABASE_lab3, DATABASE_lab3a, DATABASE_lab3b etc.)
 | L A B S T E P S |  |
| Part 1 -- The Control File: Adding a new Control File Step 1: Locating the existing control files |
Where is the existing control file located and what is the name?
Hint: Query the dynamic performance view V$CONTROLFILE or V$PARAMETER, or execute the SHOW PARAMETER command to display the name and location of the control file.Your response will look similar to this:
NAME
------------------------------------------------------------ -------------------
F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\DISK1\ctl1001. ctl
F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\DISK2\ctl1002. ctl
2 rows selected.
| Step 2: Add a new control file |
Hints:
- In your current SQL*Plus session, issue the command to shut down the database.
- On your mapped network drive, go to the DISK1 directory and physically copy the existing control file in that directory to the directory DISK3. Rename the file to CTLtest3.ctl
- Next, go to your initol###.ora file found in the PFILE directory of your mapped drive. Modify the “Control files” parameter to include the new file name, so that your parameter listing looks similar to the one below. (You will be adding a third control file path in the parameter listing for the control file). Your new listing should look similar to the one below.
Control_files=(‘F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\ DISK1\ctl1001.ctl’,
‘F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\DISK2\ctl1002. ctl',
‘F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ ORADATA\DISK3\ctltest3.ctl’)
- Save the control file.
- Now go back to yrou SQL*Plus session and issue the STARTUP command to restart the database, using your init.ora parameter file with the new setting. Your startup command should look similar to the following:
startup pfile= O:\oracle\admin\pfile\init###.ora
| Step 3: Verify the new control file |
Your response will look similar to this:
NAME
------------------------------------------------------------ -----------
F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\DISK1\ctl1001. ctl
F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\DISK2\ctl1002. ctl
F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\DISK3\ctltest3. ctl
2 rows selected.
| Step 4: Recovering from a corrupted Control file |
- To begin, issue a SHUTDOWN IMMEDIATE (make sure you are logged in as the SYS user).
- Next, go into your mapped directory structure and find the ctltest3.ctl file in the Disk3 folder. Right-click on the file and select Open With => Notepad from the options list. When the file opens, press the space bar once and then close and save the file. You have just corrupted your database!
- Now, once again, start up your database using the init.ora file for your instance, as you did in step 2. What happened? Did the database start? Go find your alert log (Hint: bdump folder in your OFA structure), copy the part of the entry that references the problem (look down at the bottom of the file), and save it to a text file to be turned in with the lab work.
- Ok, so now we need to fix this problem. Execute the following steps to recover your database from this problem.
- First, shut down the database by issuing a SHUTDOWN IMMEDIATE.
- Next, go back into your init.ora file in your mapped directories and remove the line that you just added that has the path to the ctltest3.ctl file. Make sure that you have both original files in single quotes with a comma separating them, and that there is not a comma at the end before the closing parenthesis. Close the file and save it.
- Now go back to the SQL*Plus editor and start up your database using the init.ora file, as you have done before.
| Part 2 -- The Redo Logs: Working with Redo Logs Step 5: Find the location of existing Redo Log files and Groups |
List the number and location of existing log files and display the number of redo log groups and members your database has:
Hints:Your response will look similar to this for A.
- Query the dynamic performance views V$LOGFILE to display the location.
- Use the dynamic performance view V$LOG to display the number of redo log file groups and members.
MEMBERYour response will look similar to this for B.
------------------------------------------------------------ --------
F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\DISK2\LOG1A01. RDO
F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\DISK3\LOG2A01. RDO
2 rows selected.
GROUP# MEMBERS
---------- ------------
1 1
2 1
2 rows selected.
| Step 6: Determine current Archive Mode |
Hint: Query the dynamic performance views V$DATABASE to show the database mode.Your response should look similar to this:
LOG_MODE
---------------
NOARCHIVELOG
1 row selected.
| Step7: Determine if Archiving is enabled |
Hint: Query the dynamic performance views V$INSTANCE to verify if the archiving is enabled.Your response should look similar to this:
ARCHIVE
---------
STOPPED
1 row selected.
| Step 8: Adding Redo Log Members |
Hint: Execute the ALTER DATABASE ADD LOGFILE MEMBER command to add a redo log member to each group. Then query the dynamic performance views V$LOGFILE to verify your result.Your response should look similar to this:
Database altered.
Database altered.
GROUP# STATUS MEMBER
--------- -------- ------------------------------------------------------------ ----
1 STALE F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\DISK2\LOG1A01. RDO
2 F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\DISK3\LOG2A01. RDO
1 INVALID F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\DISK3\LOG1B01. RDO
2 INVALID F:\DBM\FALLA\DB9999\ORACLE\ADMIN\ORADATA\DISK2\LOG2B01. RDO
4 rows selected.
| Step 9: Add a new Log Group |
Your response should look similar to this:
Hint: Execute the ALTER DATABASE ADD LOGFILE GROUP command to create a new group. Remember that you can add both files to the new group at one time. To verify your work, query the performance view V$LOG.
Database altered.Now, to verify your work, query the performance view V$LOG.
Your response should look similar to this:
GROUP# MEMBERSThis concludes the Lab for Week 3. Be sure to enter a SPOOL OFF command to end your spool session, to save your output file.
---------- ----------
1 2
2 2
3 2
3 rows selected.
Database Administration Exercises - Week 3
| I N D I V I D U A L E X E R C I S E S | |
| HANDS-ON #1: Dictionary Views to help you work |
Using your text and any other sources you wish, make a list of the data dictionary views you feel would be the most helpful to a programmer who is writing applications that perform DML actions on rows from tables in the database. Give a brief explanation for your choice of each view.
Place and save your answers in a Word document named week3_exercise.doc.
| HANDS-ON #2: Where are my Log Files and Groups? |
- What are the redo log group numbers, and how many members does each group have?
- What directory or directories holds the redo log group members?
| HANDS-ON #3: Working with initialization parameters |
Place and save your answers in the Word document named week3_exercise.doc that you have started for this week's exercises.
Related Documents
Basic Oracle Sql Exercise
Practice Exercise for Oracle PL/SQL
Saturday, July 19, 2008
Database Startup fails with error ORA-16038,ORA-19809, ORA-00312
Error Description:
Whenever you try to startup the database it fails with error ORA-16038,ORA-19809, ORA-00312.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 117440552 bytes
Database Buffers 41943040 bytes
Redo Buffers 6369280 bytes
Database mounted.
ORA-16038: log 3 sequence# 572 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: '/oradata2/data1/dbase/redo03.log'
Or in mount stage whenever you try to open the database it fails with error ORA-16014, ORA-00312.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16014: log 3 sequence# 572 not archived, no available destinations
ORA-00312: online log 3 thread 1: '/oradata2/data1/dbase/redo03.log'
Cause of The Problem:
----------------------------------------
There was an attempt to archived the online log 3 but it could not archive the online log in the available archived log destination. The most common of happening the error is the archive log destination if full. You have flash recovery area configured and rman retention policy is failed to delete any archived or incremental backups and so can't archived new online log.
Solution of The Problem:
-------------------------------------------
Solution A:(Enough space on the drive)
1)One more check the alert log. (Not needed though)
An extra check you can do in alert log which is in background_dump_dest/alert_$ORACLE_SID.log
SQL> show parameter background_dump_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string /oracle/app/oracle/product/10.
2.0/db_1/admin/dbase/bdump
$less /oracle/app/oracle/product/10.2.0/db_1/admin/dbase/bdump/alert_dbase.log
You may see the same entry is in the alert log.
2)Check the archive destination.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 572
Next log sequence to archive 572
Current log sequence 580
So archived log destination is DB_RECOVERY_FILE_DEST. You can see the exact destination in OS by,
SQL> show parameter db_recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /oradata2/flash_recovery_area
db_recovery_file_dest_size big integer 10G
3)Increase the value of db_recovery_file_dest_size
As archive destination is full so increase the size.
SQL> alter system set db_recovery_file_dest_size=20G;
System altered.
4)Open the database now.
SQL> alter database open;
Database altered.
Solution B: Have not enough space on the drive
If you have not enough space in your disk and you have recent backup of your database and archive log is not needed then you can issue
$rman target /
RMAN>DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-2';
Solution C: Have not any backup
If you have not any recent backup then backup database to another location and delete archivelogs.
To do this,
$rman target /
RMAN>backup format '/oradata2/%U' archivelog all delete input database;
Solution D: Have recent backup and only need archivelog
In this case backup the archive log to another location and delete archive log from flash recovery area.
You can do this by,
$rman target /
RMAN> backup format '/oradata2/%U' archivelog all delete input;
Whenever you try to startup the database it fails with error ORA-16038,ORA-19809, ORA-00312.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 117440552 bytes
Database Buffers 41943040 bytes
Redo Buffers 6369280 bytes
Database mounted.
ORA-16038: log 3 sequence# 572 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: '/oradata2/data1/dbase/redo03.log'
Or in mount stage whenever you try to open the database it fails with error ORA-16014, ORA-00312.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16014: log 3 sequence# 572 not archived, no available destinations
ORA-00312: online log 3 thread 1: '/oradata2/data1/dbase/redo03.log'
Cause of The Problem:
----------------------------------------
There was an attempt to archived the online log 3 but it could not archive the online log in the available archived log destination. The most common of happening the error is the archive log destination if full. You have flash recovery area configured and rman retention policy is failed to delete any archived or incremental backups and so can't archived new online log.
Solution of The Problem:
-------------------------------------------
Solution A:(Enough space on the drive)
1)One more check the alert log. (Not needed though)
An extra check you can do in alert log which is in background_dump_dest/alert_$ORACLE_SID.log
SQL> show parameter background_dump_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string /oracle/app/oracle/product/10.
2.0/db_1/admin/dbase/bdump
$less /oracle/app/oracle/product/10.2.0/db_1/admin/dbase/bdump/alert_dbase.log
You may see the same entry is in the alert log.
2)Check the archive destination.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 572
Next log sequence to archive 572
Current log sequence 580
So archived log destination is DB_RECOVERY_FILE_DEST. You can see the exact destination in OS by,
SQL> show parameter db_recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /oradata2/flash_recovery_area
db_recovery_file_dest_size big integer 10G
3)Increase the value of db_recovery_file_dest_size
As archive destination is full so increase the size.
SQL> alter system set db_recovery_file_dest_size=20G;
System altered.
4)Open the database now.
SQL> alter database open;
Database altered.
Solution B: Have not enough space on the drive
If you have not enough space in your disk and you have recent backup of your database and archive log is not needed then you can issue
$rman target /
RMAN>DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-2';
Solution C: Have not any backup
If you have not any recent backup then backup database to another location and delete archivelogs.
To do this,
$rman target /
RMAN>backup format '/oradata2/%U' archivelog all delete input database;
Solution D: Have recent backup and only need archivelog
In this case backup the archive log to another location and delete archive log from flash recovery area.
You can do this by,
$rman target /
RMAN> backup format '/oradata2/%U' archivelog all delete input;
Tuesday, May 20, 2008
Recovering a Database in NOARCHIVELOG Mode
If a media failure damages datafiles in a NOARCHIVELOG database, then the only option for recovery is usually to restore a consistent whole database backup. As you are in noarchivelog mode so you have to understand that changes after taken backup is lost.
If you logical backup that is export file you can import that also.
In order to recover database in noarchivelog mode you have to follow the following procedure.
1)If the database is open shutdown it.
SQL>SHUTDOWN IMMEDIATE;
2)If possible, correct the media problem so that the backup database files can be restored to their original locations.
3)Copy all of the backup control files, datafiles to the default location if you corrected media failure. However you can restore to another location. Remember that all of the files not only the damaged files.
4)Because online redo logs are not backed up, you cannot restore them with the datafiles and control files. In order to allow the database to reset the online redo logs, you must have to do incomplete recovery:
RECOVER DATABASE UNTIL CANCEL
CANCEL
5)Open the database in RESETLOGS mode:
ALTER DATABASE OPEN RESETLOGS;
In order to rename your control files or in case of media damage you can copy it to another location and then by setting (if spfile)
STARTUP NOMOUNT
ALTER SYSTEM SET CONTROL_FILES='/new/1.ctl','/new/2.ctl' scope=spfile;
STARTUP FORCE MOUNT;
In order to rename data files or online redo log files first copy it to new location and then point control file to new location by,
ALTER DATABASE RENAME FILE '/old/system01.dbf' TO '/new_disk/system01.dbf';
Related Documents:
---------------------------
User Managed Restore Operation
What you will do if spfile lost Look at Solution of The problem Section
How to solve problem of inconsistent control file.
How to re-create Control file
If you logical backup that is export file you can import that also.
In order to recover database in noarchivelog mode you have to follow the following procedure.
1)If the database is open shutdown it.
SQL>SHUTDOWN IMMEDIATE;
2)If possible, correct the media problem so that the backup database files can be restored to their original locations.
3)Copy all of the backup control files, datafiles to the default location if you corrected media failure. However you can restore to another location. Remember that all of the files not only the damaged files.
4)Because online redo logs are not backed up, you cannot restore them with the datafiles and control files. In order to allow the database to reset the online redo logs, you must have to do incomplete recovery:
RECOVER DATABASE UNTIL CANCEL
CANCEL
5)Open the database in RESETLOGS mode:
ALTER DATABASE OPEN RESETLOGS;
In order to rename your control files or in case of media damage you can copy it to another location and then by setting (if spfile)
STARTUP NOMOUNT
ALTER SYSTEM SET CONTROL_FILES='/new/1.ctl','/new/2.ctl' scope=spfile;
STARTUP FORCE MOUNT;
In order to rename data files or online redo log files first copy it to new location and then point control file to new location by,
ALTER DATABASE RENAME FILE '/old/system01.dbf' TO '/new_disk/system01.dbf';
Related Documents:
---------------------------
User Managed Restore Operation
What you will do if spfile lost Look at Solution of The problem Section
How to solve problem of inconsistent control file.
How to re-create Control file
Saturday, May 17, 2008
ORA-00354 ORA-00353 ORA-00312 Corrupt redo block header
Error Description:
----------------------------
I ran DML operation on database and it failed with exception. I looked for alert log and the entry is ,
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 11037 change 118820174 time 04/22/2008 11:15:12
ORA-00312: online log 2 thread 1: '/oradata1/system/ARJU/redo02.log'
Cause of The Problem:
----------------------------
The redo log file has been corrupted. It may be corrupted through various reasons. Suppose there is disk full or hardware failure.
Solution of The Problem:
------------------------------.
Solution 1 or 2 is the option while database is open and solution 3 is the option if database is closed.
Solution 1: Look at the disk space usage containing redo log. If disk is full then free it by removing unwanted files. If disk is ok then try to clear the log file. Here from error we see log 2 is corrupted. So , clear group 2 Like,
SQL>ALTER DATABASE CLEAR LOGFILE GROUP 2;
Clear logfile does the thing of dropping logfile and then create. The advantage of this procedure is it can be used if you have only two redo log group.
Solution 2: If redo log was not archived (query from V$LOG) then you must specify UNARCHIVED LOGFILE. Otherwise error will come.
Try, SQL>ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;
After issuing this you have lost archived data, so take backup immediately.
In both 1 and 2 if it was the logfile member of online redo log group then you must issue,
SQL> alter system checkpoint;
after clearing it.
Solution 3: If both does not work then do a point in time recovery.
SQL>SHUTDOWN ABORT;
SQL>STARTUP MOUNT;
RMAN>RESTORE DATABASE;
SQL>RECOVER DATABASE UNTIL CANCEL; CANCEL
SQL>ALTER DATABASE OPEN RESETLOGS;
Point in time recovery is described in DBPITR
If database can't be opened that solution 3 is the only option.
----------------------------
I ran DML operation on database and it failed with exception. I looked for alert log and the entry is ,
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 11037 change 118820174 time 04/22/2008 11:15:12
ORA-00312: online log 2 thread 1: '/oradata1/system/ARJU/redo02.log'
Cause of The Problem:
----------------------------
The redo log file has been corrupted. It may be corrupted through various reasons. Suppose there is disk full or hardware failure.
Solution of The Problem:
------------------------------.
Solution 1 or 2 is the option while database is open and solution 3 is the option if database is closed.
Solution 1: Look at the disk space usage containing redo log. If disk is full then free it by removing unwanted files. If disk is ok then try to clear the log file. Here from error we see log 2 is corrupted. So , clear group 2 Like,
SQL>ALTER DATABASE CLEAR LOGFILE GROUP 2;
Clear logfile does the thing of dropping logfile and then create. The advantage of this procedure is it can be used if you have only two redo log group.
Solution 2: If redo log was not archived (query from V$LOG) then you must specify UNARCHIVED LOGFILE. Otherwise error will come.
Try, SQL>ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;
After issuing this you have lost archived data, so take backup immediately.
In both 1 and 2 if it was the logfile member of online redo log group then you must issue,
SQL> alter system checkpoint;
after clearing it.
Solution 3: If both does not work then do a point in time recovery.
SQL>SHUTDOWN ABORT;
SQL>STARTUP MOUNT;
RMAN>RESTORE DATABASE;
SQL>RECOVER DATABASE UNTIL CANCEL; CANCEL
SQL>ALTER DATABASE OPEN RESETLOGS;
Point in time recovery is described in DBPITR
If database can't be opened that solution 3 is the only option.
ORA-00313: open failed for members of log group
Error Description:
------------------------
Today one of my developer said that I am getting the following error from database. He send me the message with error number,
ORA-00257: archiver error. Connect internal only, until freed.
I immediately look for V$RECOVERY_FILE_DEST as per ORA-00257 Archiver Error and I saw that there is enough space available there. So, I look for alert log. There is the error entry,
ORA-00313: open failed for members of log group 2 of thread 1
Cause of The Problem:
-------------------------------
The developer has ran a huge insert in my database which made full of the redo log.
Solutions of The Problem:
--------------------------------
Solution 1)Use Nologging operation while doing huge DML operation.And insert make as as usual.
ALTER TABLE TABLE_NAME NOLOGGING;
run DML statement and then
ALTER TABLE TABLE_NAME LOGGING;
Solution 2)Increase the redo log size.
ALTER DATABASE ADD LOGFILE '/oradata/redo1.log' SIZE 100M;
Solution 3)Tune the statements.
Tune the DML statement to use less redo, or divide the statement into smaller ones will also help to avoid error.
------------------------
Today one of my developer said that I am getting the following error from database. He send me the message with error number,
ORA-00257: archiver error. Connect internal only, until freed.
I immediately look for V$RECOVERY_FILE_DEST as per ORA-00257 Archiver Error and I saw that there is enough space available there. So, I look for alert log. There is the error entry,
ORA-00313: open failed for members of log group 2 of thread 1
Cause of The Problem:
-------------------------------
The developer has ran a huge insert in my database which made full of the redo log.
Solutions of The Problem:
--------------------------------
Solution 1)Use Nologging operation while doing huge DML operation.And insert make as as usual.
ALTER TABLE TABLE_NAME NOLOGGING;
run DML statement and then
ALTER TABLE TABLE_NAME LOGGING;
Solution 2)Increase the redo log size.
ALTER DATABASE ADD LOGFILE '/oradata/redo1.log' SIZE 100M;
Solution 3)Tune the statements.
Tune the DML statement to use less redo, or divide the statement into smaller ones will also help to avoid error.
Wednesday, May 14, 2008
ORA-16038,ORA-00354,ORA-00312 corrupt redo log block header
Error Description:
------------------------
Normal users could not connect to database. It messaged ORA-00257 :Connect Internal Only until freed. Whenever you try to archive the redo log it returns the message.
ORA-16038: log %s sequence# %s cannot be archived
ORA-00354: corrupt redo log block header
ORA-00312: "online log %s thread %s: '%s'"
Explanation of the Problem:
-------------------------------
Whenever normal user tried to connect the database the error returns as it is descriped in
ORA-00257. But you have noticed that there is enough space in V$RECOVERY_FILE_DEST. Whenever you look at alert log you will see the ORA-16038,ORA-00354,ORA-00312 error serial. The error produced as because it failed to archive online redolog due to a corruption in the online redo file.
Solution of The problem:
--------------------------------
Step1)While making your database running clear the unarchived redo log.
SQL>alter database clear unarchived logfile 'logilename';
This makes the corruption disappear which causes the contents of the cleared online redo file.
Step2)Make a complete backup of the database.
------------------------
Normal users could not connect to database. It messaged ORA-00257 :Connect Internal Only until freed. Whenever you try to archive the redo log it returns the message.
ORA-16038: log %s sequence# %s cannot be archived
ORA-00354: corrupt redo log block header
ORA-00312: "online log %s thread %s: '%s'"
Explanation of the Problem:
-------------------------------
Whenever normal user tried to connect the database the error returns as it is descriped in
ORA-00257. But you have noticed that there is enough space in V$RECOVERY_FILE_DEST. Whenever you look at alert log you will see the ORA-16038,ORA-00354,ORA-00312 error serial. The error produced as because it failed to archive online redolog due to a corruption in the online redo file.
Solution of The problem:
--------------------------------
Step1)While making your database running clear the unarchived redo log.
SQL>alter database clear unarchived logfile 'logilename';
This makes the corruption disappear which causes the contents of the cleared online redo file.
Step2)Make a complete backup of the database.
ORA-00313 ,ORA-00312 open failed for members of log group
Error Description:
---------------------------
Whenever you start your database you returned by the following message,
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/oradata2/data1/dbase/redo02.log'
Cause of The problem:
-----------------------------
Your database was in archive log file, you shutdown your database and whenever you start it up either your redo log file is deleted or it is corrupted (if you overwrite or truncate the file).In this case you had 1 redo log member on each group.
Solution of The problem:
--------------------------------
It is not possible to recover missing redo log file. In order to solve the problem do the following.
A)Mount the database.
SQL>STARTUP MOUNT
Database mounted.
B)Check the status of the logile to see whether it is current.
Here it is,
SQL> SELECT STATUS FROM V$LOG WHERE GROUP#=2;
STATUS
----------------
CURRENT
i)If the status did not CURRENT then simply drop the log file by,
SQL>ALTER DATABASE DROP LOGFILE GROUP 2;
If there are only 2 log groups then it will be necessary to add another group before dropping this one.
So, before dropping do,
SQL>ALTER DATABASE ADD LOGFILE GROUP 4 '/oradata2/redo3.log' SIZE 10M;
ii)If/As the status is CURRENT then simply perform fake recovery and then open resetlogs.
SQL>RECOVER DATABASE UNTIL CANCEL;
and print CANCEL.
SQL>ALTER DATABASE OPEN RESETLOGS;
Related Documents
---------------------------
Whenever you start your database you returned by the following message,
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/oradata2/data1/dbase/redo02.log'
Cause of The problem:
-----------------------------
Your database was in archive log file, you shutdown your database and whenever you start it up either your redo log file is deleted or it is corrupted (if you overwrite or truncate the file).In this case you had 1 redo log member on each group.
Solution of The problem:
--------------------------------
It is not possible to recover missing redo log file. In order to solve the problem do the following.
A)Mount the database.
SQL>STARTUP MOUNT
Database mounted.
B)Check the status of the logile to see whether it is current.
Here it is,
SQL> SELECT STATUS FROM V$LOG WHERE GROUP#=2;
STATUS
----------------
CURRENT
i)If the status did not CURRENT then simply drop the log file by,
SQL>ALTER DATABASE DROP LOGFILE GROUP 2;
If there are only 2 log groups then it will be necessary to add another group before dropping this one.
So, before dropping do,
SQL>ALTER DATABASE ADD LOGFILE GROUP 4 '/oradata2/redo3.log' SIZE 10M;
ii)If/As the status is CURRENT then simply perform fake recovery and then open resetlogs.
SQL>RECOVER DATABASE UNTIL CANCEL;
and print CANCEL.
SQL>ALTER DATABASE OPEN RESETLOGS;
Related Documents
Startup fails with oracle error ORA-00119, ORA-00132
Subscribe to:
Posts (Atom)