Travellers Trail

Monday, 17 December 2007

WebLogic app to deploy

Run below command to create the war file (nidb-app.war):

jar cvf nidb-app.war test_app.jsp test_db.jsp test_driver.jsp includes/UTF-8.inc WEB-INF/web.xml

WEB-INF/web.xml
< ?xml version="1.0" encoding="UTF-8"?>
< !DOCTYPE web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Web Application 2.3//EN" "http://java.sun.com/dtd/web-app_2_3.dtd">
<>
<>nidb_test
<>niDB Test App

<>
<>test_app.jsp
< /welcome-file-list>

< /web-app>

includes/UTF-8.inc
<% response.setHeader("Content-Type", "text/html; charset=UTF-8"); response.setContentType("text/html; charset=UTF-8"); request.setCharacterEncoding("UTF-8"); %>

Date time convert

select to_char(sysdate,'YYYYMMDD HH24:MI:SS') as "Eastern Daylight"
,to_char(new_time(sysdate,'EDT','GMT'),'YYYYMMDD HH24:MI:SS') dual

Friday, 14 December 2007

Adding data to CLOB fileds

Test table to try CLOBS

create table a (
id number primary key,
name varchar2(32),
info clob
);

insert into a values(1, 'Nazrul', 'Notest');
insert into a values(2 'Abu', 'You can add here upto 3999 char via sqlplus');

Good to use LOB function to do CLOB work so you can add upto 4gb of data.

DECLARE
v_text_loc CLOB;
v_offset INTEGER;
v_buffer VARCHAR2(100);
BEGIN
select info into v_text_loc from a where id = 15;
v_offset := DBMS_LOB.GETLENGTH (v_text_loc) + 1;
v_buffer := 'My txt The End.';
DBMS_LOB.WRITE (v_text_loc, length(v_buffer), v_offset, v_buffer);
END;
/

select '*'||info||'*' from a where id = 15;

Friday, 7 December 2007

About the Stars

Abu Qatadah mentioned Allah’s Statement "And We have adorned the nearest heaven with lamps," and said,
"The creation of these stars is for three purposes, i.e. as decoration of the (nearest) heaven, as missiles to hit the devils, and as signs to guide travellers. So if anybody tries to find a different interpretation, he is mistaken and just wastes his efforts, and troubles himself with what is beyond his limited knowledge"

Sahih Al Bukhari Vol 4, Chap 3, P 282.

Tuesday, 4 December 2007

MySQL groupby functions

Great fetaure of MySQL groupby function GROUP_CONCAT

select concat(DATE_FORMAT(date, '%a %d/%m/%Y'),' ',time,';',GROUP_CONCAT(total_gateways SEPARATOR ';'))
from tbl_concurrent_users where company_name = 'rabobank2'
group by date, time,company_server order by date,time

Good link to reference: http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

Tuesday, 27 November 2007

Note on cursor

Note on open cursors
Good REF: http://orafaq.com/node/758


-- Total cursors open, by session
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = 'opened cursors current';


-- Total open cursors, by username & machine
select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur, 
s.username, s.machine
from v$sesstat a, v$statname b, v$session s 
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = 'opened cursors current' 
group by s.username, s.machine
order by 1 desc;

Sample Oracle 9i network files (listener)


******************************************************************
* listener.ora
******************************************************************

# LISTENER.ORA Network Configuration File: /opt/oracle/product/9.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = plum)(PORT = 1521))
     )
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
     )
   )
 )

SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC =
     (SID_NAME = PLSExtProc)
     (ORACLE_HOME = /opt/oracle/product/9.2.0)
     (PROGRAM = extproc)
   )
   (SID_DESC =
     (GLOBAL_DBNAME = thenidb)
     (ORACLE_HOME = /opt/oracle/product/9.2.0)
     (SID_NAME = nidb)
   )
 )

My 9i sqlnet


******************************************************************
* sqlnet.ora
******************************************************************

# SQLNET.ORA Network Configuration File: /opt/oracle/product/9.2.0/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES)

9i tnsnames


******************************************************************
* tnsnames.ora
******************************************************************

# TNSNAMES.ORA Network Configuration File: /opt/oracle/product/9.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

NAZRULDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = plum)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = nazruldb)
    )
  )

INST1_HTTP =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = plum)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = SHARED)
      (SERVICE_NAME = MODOSE)
      (PRESENTATION = http://HRService)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

Friday, 23 November 2007

10g Standby database

Creating a Physical Standby Database

On primary database

ALTER DATABASE FORCE LOGGING;

Did this on primary and then dropped, still not 100% sure where this goes.

ALTER DATABASE ADD STANDBY LOGFILE GROUP 10

('/u01/oracle/oradata/X1AR/redo01_stb.log') SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 11

('/u02/oracle/oradata/X1AR/redo02_stb.log') SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 12

('/u03/oracle/oradata/X1AR/redo03_stb.log') SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 13

('/u03/oracle/oradata/X1AR/redo04_stb.log') SIZE 50M;

SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

Create a password file if one does not already exist. Note password of sys needs to be same on both primary and standby.

For ease of modify parameters, I am making the primary database use pfile

create pfile from spfile;

cd /u01/oracle/product/10.2.0/dbs/

mv spfileX1DB.ora x_spfileX1DB.ora

shut immediate;

startup;

Parameters for primary database:

*.DB_NAME=X1DB

*.DB_UNIQUE_NAME=X1DB

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(X1DB,Z1DB)'

*.CONTROL_FILES='/u01/oracle/oradata/X1DB/control01.ctl','/u02/oracle/oradata/X1DB/control02.ctl','/u03/oracle/oradata/X1DB/control03.ctl'

*.LOG_ARCHIVE_DEST_1='LOCATION=/u04/oracle/arch_X1DB/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=X1DB'

*.LOG_ARCHIVE_DEST_2='SERVICE=Z1DB LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=Z1DB'

*.LOG_ARCHIVE_DEST_STATE_1=ENABLE

*.LOG_ARCHIVE_DEST_STATE_2=ENABLE

*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

*.LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

*.LOG_ARCHIVE_MAX_PROCESSES=30

*.FAL_SERVER=Z1DB

*.FAL_CLIENT=X1DB

*.DB_FILE_NAME_CONVERT='Z1DB','X1DB'

*.LOG_FILE_NAME_CONVERT='/u01/oracle/oradata/Z1DB/','/u01/oracle/oradata/X1DB/','/u02/oracle/oradata/Z1DB/','/u02/oracle/oradata/X1DB/','/u03/oracle/oradata/Z1DB/','/u03/oracle/oradata/X1DB/'

*.STANDBY_FILE_MANAGEMENT=AUTO

Make sure primary database in ARCHIVELOG mode and restart

shut immediate;

startup mount;

ALTER DATABASE ARCHIVELOG;

alter database open;

mkdir /u01/oracle/oradata/Z1DB

mkdir /u02/oracle/oradata/Z1DB

mkdir /u03/oracle/oradata/Z1DB

mkdir /u04/oracle/ arch_Z1DB

Backup the primary database, I performed a cold backup, then startup priamy.

startup mount;

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/Z1DB.ctl';

alter database open;

Copy control file to standby box

cd /u01/oracle/oradata/Z1DB

scp oracle@lnx-suppdb1:/tmp/Z1DB.ctl .

mv Z1DB.ctl control01.ctl

cp control01.ctl /u02/oracle/oradata/Z1DB/control02.ctl

cp control01.ctl /u03/oracle/oradata/Z1DB/control03.ctl

Copy pfile from primary

cd $ORACLE_HOME/dbs

scp oracle@lnx-suppdb1:$ORACLE_HOME/dbs/initX1DB.ora .

mv initX1DB.ora initZ1DB.ora

Modify the standby parameters.

Make sure all the bdump, cdump… folder are in correct location.

Create password file on standby (same passwd as primary)

orapwd file=$ORACLE_HOME/dbs/orapwZ1DB password=oracle entries=4

Make sure Primary box password file there, or create new one:

orapwd file=$ORACLE_HOME/dbs/orapwX1DB password=oracle entries=4

Make sure entries in tnsnames files and listener know about the databases.

Startup standby database:

export ORACLE_SID=Z1DB

First time round gave error since pfile did not have primary db’s SID, it should have X1DB.

SQL> STARTUP MOUNT;

ORACLE instance started.

Total System Global Area 1879048192 bytes

Fixed Size 2021664 bytes

Variable Size 1308624608 bytes

Database Buffers 553648128 bytes

Redo Buffers 14753792 bytes

ORA-01103: database name 'X1DB' in control file is not 'Z1DB'

SQL> !

sqlplus /nolog

STARTUP MOUNT;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Error on primary database:

Getting error on primary (alert file), fix was to shutdown both primary and sandby then reviewed both init files and tnsnames files, corrected variables and removed extra spaces from end.

Sat Nov 24 02:40:11 2007

Error 12154 received logging on to the standby

Sat Nov 24 02:40:11 2007

Errors in file /u01/oracle/admin/X1DB/bdump/x1db_arcp_14281.trc:

ORA-12154: Message 12154 not found; No message file for product=RDBMS, facility=ORA

PING[ARCp]: Heartbeat failed to connect to standby 'Z1DB'. Error is 12154.

Test Scenario 1

On primary, switch log file.

ALTER SYSTEM SWITCH LOGFILE;

Alert of standby shows:

Sat Nov 24 22:05:00 2007

RFS[15]: Archived Log: '/u04/oracle/arch_Z1DB/1_44_639415217.arc'

Primary database is in MAXIMUM PERFORMANCE mode

RFS[15]: No standby redo logfiles created

Sat Nov 24 22:05:05 2007

Media Recovery Log /u04/oracle/arch_Z1DB/1_44_639415217.arc

Media Recovery Waiting for thread 1 sequence 45 (in transit)

Test successful no the standby file system, can see:

/u04/oracle/arch_Z1DB

[oracle@lnx-qadb1 arch_Z1DB]$ ls -alrt

total 317556

-rw-r----- 1 oracle dba 4941824 Nov 24 22:05 1_44_639415217.arc

-rw-r----- 1 oracle dba 1024 Nov 24 22:29 1_46_639415217.arc

-rw-r----- 1 oracle dba 1398784 Nov 24 22:29 1_45_639415217.arc

Test Scenario 2

Execute below SQL on both primary and standby:

SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#

Test successful, saw data on both, similar numbers

Test Scenario 3

On primary, add new tablespace

create tablespace INDEXS datafile '/u01/oracle/oradata/X1DB/X1DB_INDEXS_01.dbf'

size 51264K autoextend off logging extent management local

uniform size 512K segment space management auto;

ALTER SYSTEM SWITCH LOGFILE;

Alert of standby shows alert switches and adding of tablespace (test successful):

Sat Nov 24 21:48:48 2007

Media Recovery Log /u04/oracle/arch_Z1DB/1_41_639415217.arc

Recovery created file /u01/oracle/oradata/Z1DB/Z1DB_ INDEXS_01.dbf

Successfully added datafile 6 to media recovery

Datafile #6: '/u01/oracle/oradata/Z1DB/Z1DB_INDEXS_01.dbf'

Media Recovery Log /u04/oracle/arch_Z1DB/1_42_639415217.arc

Media Recovery Log /u04/oracle/arch_Z1DB/1_43_639415217.arc

Media Recovery Waiting for thread 1 sequence 44 (in transit)

Alhamdulillah saw Physical Standby 10g working successfully.

Did nothing more, for next test round, do more detail work post setup, e.g. how to everyday maintenance on standby.