June 17, 2008
About a month ago, I changed my test database (10g - 10.2) ORCL on windows XP (PRO) into ARCHIVELOG mode.
After not looking at the DB for a month, the FLASH_RECOVERY_AREA (2G) got full and ARCH process was hanging.
I removed some of the "old" archived files to make room… that is when the problem started. This prevents all connections to the database!
Meanwhile, I had been experimenting and learning RMAN and was taking several backups of the database as well.
Then I increased, the FLASH_AREA_RECEOVERY_SIZE to 10G.
Everything was great from then on.
Since I removed some of the OLD archive log files, RMAN was not allowing any more backups.
About a month ago, I changed my test database (10g - 10.2) ORCL on windows XP (PRO) into ARCHIVELOG mode.
After not looking at the DB for a month, the FLASH_RECOVERY_AREA (2G) got full and ARCH process was hanging.
I removed some of the "old" archived files to make room… that is when the problem started. This prevents all connections to the database!
Meanwhile, I had been experimenting and learning RMAN and was taking several backups of the database as well.
Then I increased, the FLASH_AREA_RECEOVERY_SIZE to 10G.
Everything was great from then on.
Since I removed some of the OLD archive log files, RMAN was not allowing any more backups.
Current backup status
RMAN> list backup of database;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
16 Full 974.02M DISK 00:02:43 15-JUN-08
BP Key: 16 Status: AVAILABLE Compressed: NO Tag: TAG20080615T082927
Piece Name: C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2008_06_15\
O1_MF_NNNDF_TAG20080615T082927_45B2SJMF_.BKP
List of Datafiles in backup set 16
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 32731743 15-JUN-08 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
2 Full 32731743 15-JUN-08 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
3 Full 32731743 15-JUN-08 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
4 Full 32731743 15-JUN-08 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
5 Full 32731743 15-JUN-08 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF
When I try a backup ARCHIVELOG the following error was the result. I must have entered some wrong command along the way.
RMAN> backup archivelog all;
Starting backup at 17-JUN-08
current log archived
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 06/17/2008 18:08:05
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
ORA-19625: error identifying file C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_05_21\
O1_MF_1_9_439O2L08_.ARC
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.
RMAN> exit;
Recovery Manager complete.
So now to recover and get the maintenance of ARCHIVELOG using RMAN and get some experience I thought I will shut down the database and recover with reset logs.
SHUTDOWN NORMAL command hung…
SQL> shutdown normal;
^C
C:\Documents and Settings\sudhab_c>
I am running version 10G (10.2) and I ran into this problem connecting back to the database.
C:\Documents and Settings\sudhab_c>sqlplus sys/********@orcl as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 17 18:19:36 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
Enter user-name: sys
Enter password:
ERROR:
ORA-01090: shutdown in progress - connection is not permitted
Now SHUTDOWN IMMEDIATE and SHUTDOWN ABORT or any other command will not work.
AFTER some late night feverish searches on the net found the following website...
From the website, in response to ORA-12528 message, followed the instructions.
Website: http://dba.ipbhost.com/index.php?showtopic=6136
C:\Documents and Settings\sudhab_c>lsnrctl
LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 17-JUN-2008 18:35:32
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Start Date 09-MAY-2008 20:13:57
Uptime 38 days 22 hr. 21 min. 36 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File C:\oracle\product\10.2.0\db_1\network\log\listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sudhabc1.div16.ibm.com)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status BLOCKED, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status BLOCKED, has 1 handler(s) for this service...
Service "orcl_XPT" has 1 instance(s).
Instance "orcl", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
The command completed successfully
LSNRCTL> start
Starting tnslsnr: please wait...
TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Log messages written to C:\oracle\product\10.2.0\db_1\network\log\listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sudhabc1.div16.ibm.com)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Start Date 17-JUN-2008 18:35:53
Uptime 0 days 0 hr. 0 min. 3 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File C:\oracle\product\10.2.0\db_1\network\log\listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sudhabc1.div16.ibm.com)(PORT=1521)))
The listener supports no services
The command completed successfully
LSNRCTL>
FINALLY back in business:
C:\Documents and Settings\sudhab_c>sqlplus sys/********@orcl as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 17 19:13:22 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
Enter user-name:
C:\Documents and Settings\sudhab_c>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 17 19:14:06 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> alter database stop archive log;
SP2-0640: Not connected
SQL> conn sys/******** as sysdba;
Connected to an idle instance.
SQL> start force;
SP2-0310: unable to open file "force.sql"
SQL> startup force;
ORACLE instance started.
Total System Global Area 289406976 bytes
Fixed Size 1248600 bytes
Variable Size 117441192 bytes
Database Buffers 167772160 bytes
Redo Buffers 2945024 bytes
Database mounted.
Database opened.
SQL>
Now I am trying establish mechanism to maintain archive logs, FLASH_RECOVERY_AREA and sizing using RMAN.
Current Database status as of June 17, 2008 at 20:00 hours.
Database is up, open and running.
RMAN> list backup of database;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
16 Full 974.02M DISK 00:02:43 15-JUN-08
BP Key: 16 Status: AVAILABLE Compressed: NO Tag: TAG20080615T082927
Piece Name: C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2008_06_15\
O1_MF_NNNDF_TAG20080615T082927_45B2SJMF_.BKP
List of Datafiles in backup set 16
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 32731743 15-JUN-08 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
2 Full 32731743 15-JUN-08 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
3 Full 32731743 15-JUN-08 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
4 Full 32731743 15-JUN-08 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
5 Full 32731743 15-JUN-08 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF
When I try a backup ARCHIVELOG the following error was the result. I must have entered some wrong command along the way.
RMAN> backup archivelog all;
Starting backup at 17-JUN-08
current log archived
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 06/17/2008 18:08:05
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
ORA-19625: error identifying file C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_05_21\
O1_MF_1_9_439O2L08_.ARC
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.
RMAN> exit;
Recovery Manager complete.
So now to recover and get the maintenance of ARCHIVELOG using RMAN and get some experience I thought I will shut down the database and recover with reset logs.
SHUTDOWN NORMAL command hung…
SQL> shutdown normal;
^C
C:\Documents and Settings\sudhab_c>
I am running version 10G (10.2) and I ran into this problem connecting back to the database.
C:\Documents and Settings\sudhab_c>sqlplus sys/********@orcl as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 17 18:19:36 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
Enter user-name: sys
Enter password:
ERROR:
ORA-01090: shutdown in progress - connection is not permitted
Now SHUTDOWN IMMEDIATE and SHUTDOWN ABORT or any other command will not work.
AFTER some late night feverish searches on the net found the following website...
From the website, in response to ORA-12528 message, followed the instructions.
Website: http://dba.ipbhost.com/index.php?showtopic=6136
C:\Documents and Settings\sudhab_c>lsnrctl
LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 17-JUN-2008 18:35:32
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Welcome to LSNRCTL, type "help" for information.
LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Start Date 09-MAY-2008 20:13:57
Uptime 38 days 22 hr. 21 min. 36 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File C:\oracle\product\10.2.0\db_1\network\log\listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sudhabc1.div16.ibm.com)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status BLOCKED, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status BLOCKED, has 1 handler(s) for this service...
Service "orcl_XPT" has 1 instance(s).
Instance "orcl", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
The command completed successfully
LSNRCTL> start
Starting tnslsnr: please wait...
TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Log messages written to C:\oracle\product\10.2.0\db_1\network\log\listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sudhabc1.div16.ibm.com)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
Start Date 17-JUN-2008 18:35:53
Uptime 0 days 0 hr. 0 min. 3 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Log File C:\oracle\product\10.2.0\db_1\network\log\listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sudhabc1.div16.ibm.com)(PORT=1521)))
The listener supports no services
The command completed successfully
LSNRCTL>
FINALLY back in business:
C:\Documents and Settings\sudhab_c>sqlplus sys/********@orcl as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 17 19:13:22 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
Enter user-name:
C:\Documents and Settings\sudhab_c>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 17 19:14:06 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> alter database stop archive log;
SP2-0640: Not connected
SQL> conn sys/******** as sysdba;
Connected to an idle instance.
SQL> start force;
SP2-0310: unable to open file "force.sql"
SQL> startup force;
ORACLE instance started.
Total System Global Area 289406976 bytes
Fixed Size 1248600 bytes
Variable Size 117441192 bytes
Database Buffers 167772160 bytes
Redo Buffers 2945024 bytes
Database mounted.
Database opened.
SQL>
Now I am trying establish mechanism to maintain archive logs, FLASH_RECOVERY_AREA and sizing using RMAN.
Current Database status as of June 17, 2008 at 20:00 hours.
Database is up, open and running.
