Database crashed in manual hotbackup mode...

Introduction:

One of our test database crashed, while it was in manual hotbackup mode. And when we tried to open the database, it didnt comeup as expected, becuase all the datafiles were in backup mode, and needed recovery. We were doing the manual hotbackup becuase of some limitations of the environment, where we were not able to use RMAN.

Troubleshooting and Resolution:

Before doing a blind recovery, we checked the alert log of the database, and searched theh v$backup view, to see which files were in backup mode. And then we initiated the recovery.

1- We checked the alert log file and found the following entries.

Thu Mar 16 04:02:57 2009
ARC0: Evaluating archive log 3 thread 1 sequence 52
ARC0: Beginning to archive log 3 thread 1 sequence 52
Creating archive destination LOG_ARCHIVE_DEST_1: '/u02/oracle/admin/TEST/arch/TEST_s52.arc'
ARC0: Completed archiving log 3 thread 1 sequence 52
Thu Mar 16 04:03:46 2009
Shutting down instance (abort)
License high water mark = 4
Instance terminated by USER, pid = 17937
Thu Mar 16 04:04:04 2009

2- When database was started again, it gave the following messages.

Completed: ALTER DATABASE MOUNT
Thu Mar 16 04:04:49 2009
alter database open
Thu Mar 16 04:04:49 2009
ORA-1113 signalled during: alter database open...
Thu Apr 16 04:05:24 2009


3- When we checked the v$backup view, 7 files were in backup mode, so we had to recover these 7 datafiles.

SQL> l
1 select * from v$backup
2* where status != 'NOT ACTIVE'
SQL>
SQL> /

FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- ---------
18 ACTIVE 7.2622E+12 15-MAR-09
19 ACTIVE 7.2622E+12 15-MAR-09
37 ACTIVE 7.2622E+12 15-MAR-09
49 ACTIVE 7.2622E+12 15-MAR-09
51 ACTIVE 7.2622E+12 15-MAR-09
60 ACTIVE 7.2622E+12 15-MAR-09
75 ACTIVE 7.2622E+12 15-MAR-09

7 rows selected.


4- Then we started database recovery, having put database in autorecovery mode. Following is the log what we did, we recovered all the datafiles, which was in backup mode, and we got that information from v$backup view.

ALTER DATABASE RECOVER datafile 18
Thu Mar 16 04:05:24 2009
Media Recovery Datafile: 16
Media Recovery Start
Starting datafile 16 recovery in thread 1 sequence 45
Datafile 16: '/u02/oradata/TEST/test_idx_01.dbf'
Media Recovery Log
ORA-279 signalled during: ALTER DATABASE RECOVER datafile 16 ...
Thu Mar 16 04:05:24 2009
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log /u02/oracle/admin/TEST/arch/TEST_s45.arc
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Thu Mar 16 04:05:24 2009
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log /u02/oracle/admin/TEST/arch/TEST_s46.arc
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Thu Mar 16 04:05:24 2009
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log /u02/oracle/admin/TEST/arch/TEST_s47.arc
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Thu Mar 16 04:05:24 2009
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log /u02/oracle/admin/TEST/arch/TEST_s48.arc
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Thu Mar 16 04:05:24 2009
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log /u02/oracle/admin/TEST/arch/TEST_s49.arc
ORA-279 signalled during: ALTER DATABASE RECOVER CONTINUE DEFAULT ...
Thu Mar 16 04:05:24 2009
ALTER DATABASE RECOVER CONTINUE DEFAULT
Media Recovery Log /u02/oracle/admin/TEST/arch/TEST_s50.arc
Recovery of Online Redo Log: Thread 1 Group 2 Seq 51 Reading mem 0
Mem# 0 errs 0: /u02/oradata/TEST/redo02.log
Recovery of Online Redo Log: Thread 1 Group 3 Seq 52 Reading mem 0
Mem# 0 errs 0: /u02/oradata/TEST/redo03.log
Recovery of Online Redo Log: Thread 1 Group 1 Seq 53 Reading mem 0
Mem# 0 errs 0: /u02/oradata/TEST/redo01.log
Media Recovery Complete
Completed: ALTER DATABASE RECOVER CONTINUE DEFAULT
Thu Mar 16 04:05:49 2009
alter database open
Thu Mar 16 04:05:49 2009
Beginning crash recovery of 1 threads
Thu Mar 16 04:05:49 2009
Started first pass scan
Thu Mar 16 04:05:49 2009
Completed first pass scan
136 redo blocks read, 20 data blocks need recovery
Thu Mar 16 04:05:49 2009
Started recovery at
Thread 1: logseq 53, block 2, scn 1690.3567247729
Recovery of Online Redo Log: Thread 1 Group 1 Seq 53 Reading mem 0
Mem# 0 errs 0: /u02/oradata/TEST/redo01.log
Thu Mar 16 04:05:49 2009
Ended recovery at
Thread 1: logseq 53, block 138, scn 1690.3567267893
20 data blocks read, 20 data blocks written, 136 redo blocks read
Crash recovery completed successfully
Thu Mar 16 04:05:49 2009
LGWR: Primary database is in CLUSTER CONSISTENT mode
Thread 1 advanced to log sequence 54
Thread 1 opened at log sequence 54
Current log# 2 seq# 54 mem# 0: /u02/oradata/TEST/redo02.log
Successful open of redo thread 1.
Thu Mar 16 04:05:49 2009
ARC0: Evaluating archive log 1 thread 1 sequence 53
ARC0: Beginning to archive log 1 thread 1 sequence 53
Creating archive destination LOG_ARCHIVE_DEST_1: '/u02/oracle/admin/TEST/arch/TEST_s53.arc'
Thu Mar 16 04:05:49 2009
SMON: enabling cache recovery
Thu Mar 16 04:05:49 2009
ARC0: Completed archiving log 1 thread 1 sequence 53
Thu Mar 16 04:05:50 2009
Undo Segment 1 Onlined
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Successfully onlined Undo Tablespace 1.
Thu Mar 16 04:05:50 2009
SMON: enabling tx recovery
Thu Mar 16 04:05:50 2009
Database Characterset is UTF8
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: alter database open

How to insert & as a column value in a table

Introduction:



This post is very simple, but usefull, as when I tried to find this thing on google, I couldnt get any page, may be I didnt search properly, but I wanted to share how we can insert & as a column value. This post is very useful if you are inserting thousands of records and all have & in one of the column values. As if you will try to run in SQL*PLUS, it would be asking you the value for a variable preceeding &.



Reolution:



We did differnet testings, and finally was able to insert the & as a column value, following are the testings and results.



1- created an empty table with one field as text varchar2(60).



test-sql>create table testtable (text varchar2(60));
Table created.



2- different tries to insert the &, last one is successfull.



a- test-sql>insert into testtable values(' this is & record');
Enter value for record: &
old 1: insert into testtable values(' this is & record')
new 1: insert into testtable values(' this is &')

1 row created.

test-sql>select * From testtable;

TEXT
------------------------------------------------------------
this is &



b- test-sql>insert into testtable values(' this is '&' record');
insert into testtable values(' this is '&' record')
*
ERROR at line 1:
ORA-00917: missing comma



c- test-sql>insert into testtable values(' this is "&" record');

1 row created.

test-sql>select * From testtable;

TEXT
------------------------------------------------------------
this is &
this is "&" record



d- test-sql>insert into testtable values(' this is '''&''' record');
insert into testtable values(' this is '''&''' record')
*
ERROR at line 1:
ORA-00917: missing comma



e- test-sql> insert into testtable values (' this is ' '&' ' record');

1 row created.

test-sql>select * From testtable;

TEXT
------------------------------------------------------------
this is &
this is "&" record
this is & record

ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect

Introduction:

We had issue with Listener connectivity from client. Even the Listener was running, but it was throwing the following error message.

ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connectdescriptor

Troubleshooting:

1- We checked with the tnsping utility from client side, and server side, and it was all working, it was displaying the following message.

TNS Ping Utility for 32-bit Windows: Version 9.2.0.8.0 - Production on 20-APR-20
09 10:02:32

Copyright (c) 1997, 2006, Oracle Corporation. All rights reserved.

Used parameter files:
D:\oracle\ora92\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = TESTSERVER)(PORT = 1528))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_N
AME = TESTDB)))
OK (20 msec)

2- When we checked through sql*plus it was showing the following message.

H:\>sqlplus

SQL*Plus: Release 9.2.0.8.0 - Production on Mon Apr 20 10:02:52 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter user-name: system@TESTDB
Enter password:
ERROR:
ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect
descriptor

3- We checked the local_listener parameter in the database and it was not set to any of the value.

SQL> show parameter local

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string

4- Here are the listener.ora and tnsnames.ora files. The important point to note here is the port number was 1528

LISTNER.ORA

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTSERVER)(PORT = 1528))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = TESTDB)
(ORACLE_HOME = /u02/oracle/products/9.2.0)
(SID_NAME = TESTDB)
)
)

TNSNAMES.ORA

TESTDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = TESTSERVER)(PORT = 1528))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TESTDB)
)
)
Resolution:

1- We set the LOCAL_LISTENER parameter to service name "TESTDB" and checked and it was all working.

H:\>sqlplus

SQL*Plus: Release 9.2.0.8.0 - Production on Mon Apr 20 10:02:52 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter user-name: system@TESTDB_ora
Enter password:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL>

2- Following was the value of LOCAL_LISTENER parameter

SQL> show parameter local_list

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string TESTDB