Showing posts with label Oracle DB Administration. Show all posts
Showing posts with label Oracle DB Administration. Show all posts

Sunday 6 July 2014

Thread 1 cannot allocate new log, sequence Checkpoint not complete


Problem: Following message getting from alert log

Archived Log entry 19900 added for thread 1 sequence 19187 ID 0x9b87c8af dest 1:
Thread 1 cannot allocate new log, sequence 19189
Checkpoint not complete
Current log# 4 seq# 19188 mem# 0: /u01/app/oracle/oradata/ORCL/redo04.log
Thread 1 advanced to log sequence 19189 (LGWR switch)

Cause: The "checkpoint not complete" messages are generated because the logs are switching so fast that the checkpoint associated
with the log switch isn’t complete.During that time, Oracle’s LGWR process has filled up the other redo log groups and is now 
waiting for the first checkpoint to successfully execute.

or

In this situation either DBWR writes slowly or log switch happens before the log is completely full or log file is small. 

Solution: 

Step1: Check the redo log status 

SQL> select group#, status from v$log; 

GROUP# STATUS 
---------- ---------------- 
1 INACTIVE 
2 ACTIVE 
3 CURRENT 

Step2: Drop the group1 which is "INACTIVE" and recreate with bigger size.

SQL> alter database drop logfile group 1; 

ALTER DATABASE  ADD LOGFILE  GROUP 1 ('/data01/oradata/orcl/redo01a.log','/data02/oradata/orcl/redo01b.log')
SIZE 400M BLOCKSIZE 512 REUSE;
  
Step3: Repeat step 1 and 2 until drop and create rest of the two redologs

Step4: Using following query you can find the no of log switches per hour.

select to_char(first_time,'YYYY-MM-DD') Day,
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'00',1,0)),'999') "00",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'01',1,0)),'999') "01",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'02',1,0)),'999') "02",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'03',1,0)),'999') "03",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'04',1,0)),'999') "04",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'05',1,0)),'999') "05",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'06',1,0)),'999') "06",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'07',1,0)),'999') "07",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'08',1,0)),'999') "08",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'09',1,0)),'999') "09",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'10',1,0)),'999') "10",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'11',1,0)),'999') "11",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'12',1,0)),'999') "12",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'13',1,0)),'999') "13",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'14',1,0)),'999') "14",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'15',1,0)),'999') "15",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'16',1,0)),'999') "16",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'17',1,0)),'999') "17",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'18',1,0)),'999') "18",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'19',1,0)),'999') "19",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'20',1,0)),'999') "20",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'21',1,0)),'999') "21",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'22',1,0)),'999') "22",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'23',1,0)),'999') "23",
sum(1) "TOTAL_IN_DAY"
from v$log_history
group by to_char(first_time,'YYYY-MM-DD')
order by to_char(first_time,'YYYY-MM-DD')  desc

Note: In a busy production environment, it is important to ensure that the frequency of redo log switches is not more than 5 per hour. 

Sunday 29 June 2014

Resize Online Redo Log size in Oracle 11g R2

Re size Online Redolog size in Oracle 11gR2

Step 1. Check the status of redolog files

Step2. Create 3 three new redolog file with size 100 MB

alter database add logfile group 4  '/u01/app/oracle/oradata/ORCL/redo04.log' size 100M; 
alter database add logfile group 5  '/u01/app/oracle/oradata/ORCL/redo05.log' size 100M; 
alter database add logfile group 6  '/u01/app/oracle/oradata/ORCL/redo06.log' size 100M; 

Step 4. Check the status of redolog files 

select group#, status from v$log;

Step 5. Switch logfile to change the status 

alter system switch logfile;

Step 6. Wait untill the status set INACTIVE of old three logfiles

Step 7. Once the logfile status set to INACTIVE drop the logfiles

SQL> alter database drop logfile group 1;
SQL> alter database drop logfile group 2;
SQL> alter database drop logfile group 3;

Note: Above steps tested in my lab.

Sunday 1 June 2014

Solution for ORA-01157 ORA-01110 error

ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/u01/app/oracle/oradata/STLBAS/temp01.dbf'



1. Connect to sqlplus

SQL> select tablespace_name, file_name, bytes/1048576 "MB" from dba_temp_files;
select tablespace_name, file_name, bytes/1048576 "MB" from dba_temp_files
                                                           *
ERROR at line 1:
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/u01/app/oracle/oradata/STLBAS/temp01.dbf'

2. Check the status of datafile

SQL> select file#,status,name from v$tempfile;

     FILE# STATUS
---------- -------
NAME
--------------------------------------------------------------------------------
         1 ONLINE
/u01/app/oracle/oradata/STLBAS/temp01.dbf


3. Drop temporary datafile

SQL> alter database tempfile '/u01/app/oracle/oradata/STLBAS/temp01.dbf' drop;

Database altered.

SQL> select file#,status,name from v$tempfile;

no rows selected

SQL>
4. Create new temporary tablespace

SQL> CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE   '/oracledb/oradata/stlbas/temp02.dbf' SIZE 10G;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Database altered.

Wednesday 7 May 2014

Find alert log error from Database table

Find alert log error from  Database table

SELECT ORIGINATING_TIMESTAMP, MODULE_ID, PROCESS_ID, MESSAGE_TEXT,HOST_ID
FROM X$DBGALERTEXT
where message_text like '%ORA-%'
ORDER BY ORIGINATING_TIMESTAMP DESC;

Monday 14 April 2014

ORA-01102: cannot mount database in EXCLUSIVE mode

Error: ORA-01102: cannot mount database in EXCLUSIVE mode

SQL> startup
ORACLE instance started.

Total System Global Area 4993982464 bytes
Fixed Size                  2236928 bytes
Variable Size             922750464 bytes
Database Buffers         4060086272 bytes
Redo Buffers                8908800 bytes
ORA-01102: cannot mount database in EXCLUSIVE mode


Reason: database is started in EXCLUSIVE mode by default. Therefore, the 
ORA-01102 error is misleading and may have occurred due to one of the 
following reasons: 

- there is still an "sgadef.dbf" file in the "ORACLE_HOME/dbs" 
directory 
- the processes for Oracle (pmon, smon, lgwr and dbwr) still exist 
- shared memory segments and semaphores still exist even though the 
database has been shutdown 
- there is a "ORACLE_HOME/dbs/lk" file 

The "lk" and "sgadef.dbf" files are used for locking shared memory. 
It seems that even though no memory is allocated, Oracle thinks memory is 
still locked. By removing the "sgadef" and "lk" files you remove any knowledge
oracle has of shared memory that is in use. Now the database can start.

SQL>
SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@bachdb dbs]$ ll
total 28
-rw-rw---- 1 oracle oinstall 1544 Apr  2 02:28 hc_micrdb.dat
-rw-rw---- 1 oracle oinstall 1544 Apr 14 22:44 hc_MICRDB.dat
-rw-r----- 1 oracle oinstall  941 Apr 14 22:42 initMICRDB.ora
-rw-r--r-- 1 oracle oinstall 2851 May 15  2009 init.ora
-rw-r----- 1 oracle oinstall   24 Apr  2 02:26 lkMICRDB
-rw-r----- 1 oracle oinstall 1536 Apr  2 02:29 orapwmicrdb
-rw-r----- 1 oracle oinstall 2560 Apr 14 02:34 spfilemicrdb.ora

[oracle@bachdb dbs]$ rm -rf lkMICRDB
[oracle@bachdb dbs]$ sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 14 22:45:00 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Enter user-name: /as sysdba
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 4993982464 bytes
Fixed Size                  2236928 bytes
Variable Size             922750464 bytes
Database Buffers         4060086272 bytes
Redo Buffers                8908800 bytes 
Database mounted.
Database opened.


SQL> exit

Tuesday 8 April 2014

Step by step to configure Oracle Heterogeneous Connectivity with 10g and DB2

Step by step to configure Oracle Heterogeneous Connectivity with 10g and DB2



1. Create a ODBC named DB2_ODBC [Note: The DSN must be system not user]

Go to start-->Settings-->Control Panel-->Administrative Tools
Then Double click on Data Sources (ODBC) and create System DSN.


 

 

-----------------------------------------------------------------------------
2. Modify C:\Oracle\product\10.2.0\db_1\hs\admin\initDB2_ODBC.ORA as follows:

# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.

#
# HS init parameters
#
HS_FDS_CONNECT_INFO =DB2_ODBC
HS_FDS_TRACE_LEVEL = off
set ODBCINI= C:\WINDOWS\odbc.ini
#
# Environment variables required for the non-Oracle system
#
#set =

---------------------------------------------------------------------------------
3. Modify C:\Oracle\product\10.2.0\db_1\network\admin\LISTENER.ORA as follows:

SID_LIST_LISTENER_HS =
  (SID_LIST =
       (SID_DESC=
       (SID_NAME = db2_odbc)
       (ORACLE_HOME = C:\Oracle\product\10.2.0\db_1)
       (PROGRAM = hsodbc)
    )
  )

LISTENER_HS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.100.1.129)(PORT = 1522))
  )


---------------------------------------------------------------------------------
4. Modify C:\Oracle\product\10.2.0\db_1\network\admin\tnsnames.ora as follows:

# TNSNAMES.ORA Network Configuration File: C:\Oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

#hsagent  =
#  (DESCRIPTION=
#    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
#    (CONNECT_DATA=(SID=hsagent))
#    (HS=)
#  )

DB2_ODBC.BD.BRACBANK.COM =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = tcp)(HOST = 10.100.1.129)(PORT = 1522))
    )
    (CONNECT_DATA =
      (SID = DB2_ODBC)
    )
    (HS = OK)
  )

-----------------------------------------------------------------------------
5. Create Database Link  as follows:

create public Database link DB2_ODBC
connect to "db2admin" identified by "db2admin" using 'DB2_ODBC';


select * from db2admin.SAID@DB2_ODBC
where rownum<7 o:p="">

create table test as select * from db2admin.SAID@DB2_ODBC;


---------------------------------------------------------------------------------
Do not forget to restart the listener services
---------------------------------------------------------------------------------