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>

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)
)
)

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.

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;
/

Oracle on linux installation steps.

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

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

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.

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;
/