Monday, July 30, 2007

How do i put my database in archivelog?

Proceed as follows.


SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 13 14:50:13 2007

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

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> select name,open_mode from v$database;

NAME OPEN_MODE
--------- ----------
XE READ WRITE

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 146800640 bytes
Fixed Size 1286220 bytes
Variable Size 71307188 bytes
Database Buffers 71303168 bytes
Redo Buffers 2904064 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> select open_mode,log_mode from v$database;

OPEN_MODE LOG_MODE
---------- ------------
READ WRITE ARCHIVELOG

SQL>
Posted by at No comments:

Creating a service name on linux using vi.

[xmanlinux ] $echo $ORACLE_HOME
/u01/app/oracle/OraHome_1
[xmanlinux ] $cd /u01/app/oracle/OraHome_1/network/admin
[xmanlinux ] $ls
listener.ora samples shrept.lst sqlnet.log sqlnet.ora tnsnames.ora
[xmanlinux ] $vi tnsnames.ora

Copy this to the end of the file and modify appropriately.

JADUONGTNS =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = XE)
)
)
Posted by at No comments:

Cracking oracle passwords

Download this tool http://www.toolcrypt.org/tools/orabf/index.html and extract it in a folder C:\xman

The contents of C:\xman should be as shown.

orabf.exe
oraclehash.exe
orabfscript.cmd
permute.exe
default.txt
charset.orabf
CHANGES.TXT
README.TXT

Generate the hashes to crack.
SQL> select username,password from dba_users where username='HR';

USERNAME PASSWORD
------------------------------ ------------------------------
HR 4C6D73C3E8B0F0DA

SQL>

Fire up dos and cd to xman

Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.

C:\Documents and Settings\Administrator>cd \

C:\>cd xman

C:\xman>
C:\xman>orabf 4C6D73C3E8B0F0DA:hr

orabf v0.7.6, (C)2005 orm@toolcrypt.org
---------------------------------------
Trying default passwords...
password found: HR:HR

C:\xman>

Let us give it a stronger hash for sys

SQL> select username,password from dba_users where username='SYS';

USERNAME PASSWORD
------------------------------ ------------------------------
SYS 8A8F025737A9097A

SQL>

C:\xman>orabf 8A8F025737A9097A:sys

orabf v0.7.6, (C)2005 orm@toolcrypt.org
---------------------------------------
Trying default passwords...
password found: SYS:ORACLE

C:\xman>

The lazy dba used oracle as his sys password! It is always a good security measure to test your passwords with this tool to be sure they cannot be easily broken with brute force.
Posted by at 3 comments:

Detect and repair corrupt table using dbms_repair package.

To use dbms_repair to repair a corrupt table, first you create a repair table which will store data
about corruptions found by CHECK_OBJECT procedure.

BEGIN
DBMS_REPAIR.ADMIN_TABLES (
TABLE_NAME => 'REPAIR_TABLE',
TABLE_TYPE => dbms_repair.repair_table,
ACTION => dbms_repair.create_action,
TABLESPACE => 'USERS');
END;
/

Use check_object procedure to check if a table has any corruptions. For example, to check if the
hr.employees table has any corruptions, run the query below.

SET SERVEROUTPUT ON
DECLARE num_corrupt INT;
BEGIN
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
SCHEMA_NAME => 'HR',
OBJECT_NAME => 'EMPLOYEES',
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
CORRUPT_COUNT => num_corrupt);
DBMS_OUTPUT.PUT_LINE('Corrupt ' || TO_CHAR (num_corrupt));
END;
/

Query the repair table for more information about corrupt blocks.


SELECT OBJECT_NAME,CORRUPT_DESCRIPTION,REPAIR_DESCRIPTION FROM REPAIR_TABLE;

Fix the corrupt blocks.

SET SERVEROUTPUT ON
DECLARE num_fix INT;
BEGIN
num_fix := 0;
DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
SCHEMA_NAME => 'HR',
OBJECT_NAME=> 'EMPLOYEES',
OBJECT_TYPE => dbms_repair.table_object,
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
FIX_COUNT=> num_fix);
DBMS_OUTPUT.PUT_LINE('FIxed: ' || TO_CHAR(num_fix));
END;
/


example

SET SERVEROUTPUT ON
DECLARE num_fix INT;
BEGIN
num_fix := 0;
DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
SCHEMA_NAME => 'HR',
OBJECT_NAME=> 'EMPLOYEES',
OBJECT_TYPE => dbms_repair.table_object,
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
FIX_COUNT=> num_fix);
DBMS_OUTPUT.PUT_LINE('FIxed: ' || TO_CHAR(num_fix));
END;
/
Posted by at No comments:

Oracle on linux installation steps.

Sometimes, installing Oracle on linux makes you feel like this Sad . I will try to make the installation experience make you feel like this Laughing .

Steps.

Assuming you are installing Oracle 10g Release 2 on Redhat Enterprise 3.

Install Linux. Follow the link below to see how this is done.
http://www.kenyadba.com/tutorials/linux_install.htm

You mus have at least 512mb of RAM. To confirm. Run the command below. Remember linux is case sensitive.

grep MemTotal /proc/meminfo

Create groups
/usr/sbin/groupadd oinstall
/usr/sbin/groupadd dba

Create user oracle with group permissions and home /home/oracle
/usr/sbin/useradd -g oinstall -G dba -d /home/oracle oracle

Modify Oracle user's bash profile

gedit /home/oracle/.bash_profile

Then paste below entries. Modify as appropriate.
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/10.2.0
ORACLE_SID=kenyadba
PATH=$ORACLE_HOME/bin:$PATH
LD_LIBRARY_PATH=/usr/lib:/usr/X11R6/lib
export PATH=$ORACLE_HOME/bin:$PATH

Set the kernel parmeters
gedit /etc/sysctl.conf

kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_default = 262144
net.core.wmem_max = 262144
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000

Then run the following command.

/sbin/sysctl -p

Login as oracle and run the installer.

To see how this is done. See the following movie.
http://www.kenyadba.com/tutorials/oracle_install.htm
Posted by at No comments:

Sending mail using oracle smtp and plsql

Create the procedure below in sqlplus. Replace smtpout.kenyadba.com with your mail server.

CREATE OR REPLACE PROCEDURE kenyadba_send_mail (
mysender VARCHAR2,
myreceiver VARCHAR2,
mysubject VARCHAR2,
mymessage VARCHAR2) IS

mailserver CONSTANT VARCHAR2(30) := 'smtpout.kenyadba.com';
crlf CONSTANT VARCHAR2(2):= CHR(13) || CHR(10);
themessage VARCHAR2(1000);
mail_conn utl_smtp.connection;

BEGIN
mail_conn := utl_smtp.open_connection(mailserver, 25);

themessage := 'Date: ' ||
TO_CHAR( SYSDATE, 'dd Mon yy hh24:mi:ss') || crlf ||
'From: <'|| mysender ||'>' || crlf ||
'Subject: '|| mysubject|| crlf ||
'To: '||myreceiver || crlf || '' || crlf || mymessage;

utl_smtp.helo(mail_conn, mailserver);
utl_smtp.mail(mail_conn, mysender);
utl_smtp.rcpt(mail_conn, myreceiver);
utl_smtp.data(mail_conn, themessage);
utl_smtp.quit(mail_conn);
EXCEPTION
WHEN OTHERS THEN
NULL;
END kenyadba_send_mail;
/


Execute the procedure with the arguments needed to send mail.

SQLPLUS > exec kenyadba_send_mail('xogutu@gmail.com','tom@kenyadba.com','Oracle Unbreakable linux','Oracle have intoduced ...');

Where

xogutu@gmail.com = sender
tom@kenyadba.com = receiver
Oracle Unbreakable linux = Subject
Oracle have intoduced ... = message.
Posted by at 1 comment:

Trigger to backup table and send mail

create or replace trigger dominic_details
before insert on system.details2 referencing old as old new as new for each row
declare
l_maicon utl_smtp.connection;
begin
insert into details2_master values(:new.name,:new.marks);
l_maicon :=utl_smtp.open_connection('192.168.1.119');
utl_smtp.helo(l_maicon,'oracle');
utl_smtp.mail(l_maicon,'dominic@isteducation.com');
utl_smtp.rcpt(l_maicon,'dominic@isteducation.com');
utl_smtp.data(l_maicon,'From: dominic@isteducation.com' || utl_tcp.crlf||
'To: dominic@isteducation.com' || utl_tcp.crlf ||
'Subject: Insert into details2 table' || utl_tcp.crlf ||
'name '||:new.name ||' marks '||:new.marks);
utl_smtp.quit(l_maicon);
end;
/
Posted by at No comments:

Rman script to backup all archivelogs.

RMAN> run {
allocate channel c1 type disk;
backup
format 'C:\oracle\backup\archivelog_t%t_s%s_p%p'
(archivelog all);
release channel c1;
}

Replace C:\oracle\backup\archivelog_t%t_s%s_p%p with
/home/oracle/backup/archivelog_t%t_s%s_p%p' for installation on linux or solaris.

Sample run.


RMAN> run {
2> allocate channel c1 type disk;
3> backup
4> format 'C:\oracle\backup\archivelog_t%t_s%s_p%p'
5> (archivelog all);
6> release channel c1;
7> }

released channel: ORA_DISK_1
allocated channel: c1
channel c1: sid=154 devtype=DISK

Starting backup at 26-JUL-07
current log archived
channel c1: starting archive log backupset
channel c1: specifying archive log(s) in backup set
input archive log thread=1 sequence=2 recid=1 stamp=628270225
input archive log thread=1 sequence=3 recid=2 stamp=628284784
input archive log thread=1 sequence=4 recid=3 stamp=628285179
input archive log thread=1 sequence=5 recid=4 stamp=628311648
input archive log thread=1 sequence=6 recid=5 stamp=628332181
input archive log thread=1 sequence=7 recid=6 stamp=628332596
input archive log thread=1 sequence=8 recid=7 stamp=628365463
input archive log thread=1 sequence=9 recid=8 stamp=628369650
input archive log thread=1 sequence=10 recid=9 stamp=628218339
input archive log thread=1 sequence=11 recid=10 stamp=628260560
input archive log thread=1 sequence=12 recid=11 stamp=628691422
input archive log thread=1 sequence=13 recid=12 stamp=628771850
input archive log thread=1 sequence=14 recid=13 stamp=628884854
input archive log thread=1 sequence=15 recid=14 stamp=628953327
input archive log thread=1 sequence=16 recid=15 stamp=628965073
input archive log thread=1 sequence=17 recid=16 stamp=628965401
input archive log thread=1 sequence=18 recid=17 stamp=628965520
channel c1: starting piece 1 at 26-JUL-07
channel c1: finished piece 1 at 26-JUL-07
piece handle=C:\ORACLE\BACKUP\ARCHIVELOG_T628965521_S3_P1 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:18
Finished backup at 26-JUL-07

released channel: c1

RMAN>
Posted by at No comments:

Rman script to perform online backup including archivelogs.

run {
allocate channel c1 type disk;
backup database plus archivelog delete all input
tag full_database_26_jul_07
format 'C:\oracle\backup\database_%t_%s_p%p';
release channel c1;
}



Replace 'C:\oracle\backup\database_%t_%s_p%p' with /home/oracle/backup/database_%t_%s_p%p' or similar directory in unix.

sample run

RMAN> run {
2> allocate channel c1 type disk;
3> backup database plus archivelog delete all input
4> tag full_database_26_jul_07
5> format 'C:\oracle\backup\database_%t_%s_p%p';
6> release channel c1;
7> }

allocated channel: c1
channel c1: sid=154 devtype=DISK


Starting backup at 26-JUL-07
current log archived
channel c1: starting archive log backupset
channel c1: specifying archive log(s) in backup set
input archive log thread=1 sequence=2 recid=1 stamp=628270225
input archive log thread=1 sequence=3 recid=2 stamp=628284784
input archive log thread=1 sequence=4 recid=3 stamp=628285179
input archive log thread=1 sequence=5 recid=4 stamp=628311648
input archive log thread=1 sequence=6 recid=5 stamp=628332181
input archive log thread=1 sequence=7 recid=6 stamp=628332596
input archive log thread=1 sequence=8 recid=7 stamp=628365463
input archive log thread=1 sequence=9 recid=8 stamp=628369650
input archive log thread=1 sequence=10 recid=9 stamp=628218339
input archive log thread=1 sequence=11 recid=10 stamp=628260560
input archive log thread=1 sequence=12 recid=11 stamp=628691422
input archive log thread=1 sequence=13 recid=12 stamp=628771850
input archive log thread=1 sequence=14 recid=13 stamp=628884854
input archive log thread=1 sequence=15 recid=14 stamp=628953327
input archive log thread=1 sequence=16 recid=15 stamp=628965073
input archive log thread=1 sequence=17 recid=16 stamp=628965401
input archive log thread=1 sequence=18 recid=17 stamp=628965520
input archive log thread=1 sequence=19 recid=18 stamp=628966600
input archive log thread=1 sequence=20 recid=19 stamp=628966646
channel c1: starting piece 1 at 26-JUL-07
channel c1: finished piece 1 at 26-JUL-07
piece handle=C:\ORACLE\BACKUP\DATABASE_628966647_5_P1 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:28
channel c1: deleting archive log(s)
archive log filename=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELO
G\2007_07_18\O1_MF_1_2_39X53WQD_.ARC recid=1 stamp=628270225
archive log filename=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELO
G\2007_07_18\O1_MF_1_3_39XMBXVB_.ARC recid=2 stamp=628284784
archive log filename=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELO
G\2007_07_18\O1_MF_1_4_39XMQB97_.ARC recid=3 stamp=628285179
archive log filename=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELO
G\2007_07_19\O1_MF_1_5_39YFLDMX_.ARC recid=4 stamp=628311648
archive log filename=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELO
G\2007_07_19\O1_MF_1_6_39Z1N1Y7_.ARC recid=5 stamp=628332181
archive log filename=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELO
G\2007_07_19\O1_MF_1_7_39Z213RW_.ARC recid=6 stamp=628332596
archive log filename=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELO
G\2007_07_19\O1_MF_1_8_3B02460G_.ARC recid=7 stamp=628365463
archive log filename=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELO
G\2007_07_19\O1_MF_1_9_3B06721X_.ARC recid=8 stamp=628369650
archive log filename=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELO
G\2007_07_18\O1_MF_1_10_39VLGKY7_.ARC recid=9 stamp=628218339
archive log filename=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELO
G\2007_07_18\O1_MF_1_11_39WVOZ6C_.ARC recid=10 stamp=628260560
archive log filename=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELO
G\2007_07_23\O1_MF_1_12_3BB0GFMJ_.ARC recid=11 stamp=628691422
archive log filename=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELO
G\2007_07_24\O1_MF_1_13_3BDGZO88_.ARC recid=12 stamp=628771850
archive log filename=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELO
G\2007_07_25\O1_MF_1_14_3BHXC37B_.ARC recid=13 stamp=628884854
archive log filename=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELO
G\2007_07_26\O1_MF_1_15_3BL06WZ7_.ARC recid=14 stamp=628953327
archive log filename=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELO
G\2007_07_26\O1_MF_1_16_3BLCP11X_.ARC recid=15 stamp=628965073
archive log filename=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELO
G\2007_07_26\O1_MF_1_17_3BLD09B7_.ARC recid=16 stamp=628965401
archive log filename=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELO
G\2007_07_26\O1_MF_1_18_3BLD406B_.ARC recid=17 stamp=628965520
archive log filename=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELO
G\2007_07_26\O1_MF_1_19_3BLF5QZ7_.ARC recid=18 stamp=628966600
archive log filename=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELO
G\2007_07_26\O1_MF_1_20_3BLF761G_.ARC recid=19 stamp=628966646
Finished backup at 26-JUL-07
Posted by at No comments:

Cron job to perform an rman backup

This script will perfom an rman backup of the database in redhat enterprise 3 and automatically delete archive logs and obsolete backups. Obsolete backups are determined by a retention policy.

. $HOME/.bash_profile
rman target / << EOF
configure controlfile autobackup on;
backup database plus archivelog delete input;
delete obsolete;
EOF


Save the script in a file on your operating system and call it using a cron job e.g

30 20 * * 1-5 sh /home/oracle/rmanbackup.sh

Will perfom a backup every 8.30 p.m of ever month of everday on monday (1) to friday (5)

Remember to run the script as an oracle user.
Posted by at No comments:

Thursday, June 14, 2007

Oracle Tips for beginners.

To view your database name run the following query.

SQL> conn
Enter user-name: sys as sysdba
Enter password: ******
Connected.
SQL> select name from v$database;

NAME
---------
XMANDB

SQL>

To check whether your database is open in read write mode

SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE

SQL>

To check whether your database is in archivelog

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

SQL>
Posted by at No comments:
Subscribe to: Posts (Atom)