DBA Tips

Convert LONG value to VARCHAR

2010-12-07T22:55:00.000-08:00

Create a Table

CREATE TABLE nuwank (

pk NUMBER,

long_val LONG NOT NULL,

varchar_val VARCHAR2 (30)

)

Insert some values

INSERT INTO nuwank

VALUES (

1, 100, NULL

);

INSERT INTO nuwank

VALUES (

2, 200, NULL

);

INSERT INTO nuwank

VALUES (

3, 300, NULL

);

Check your records

SELECT * FROM nuwank;

-- PK LONG_VAL VARCHAR_VAL

-- 1 (LONG)

-- 2 (LONG)

-- 3 (LONG)

Now create a function to get long values

CREATE OR REPLACE FUNCTION fn_getlong (p_tname IN VARCHAR2, p_cname IN VARCHAR2,

p_rowid IN ROWID)

RETURN VARCHAR2

l_cursor INTEGER DEFAULT DBMS_SQL.open_cursor;

l_n NUMBER;

l_long_val VARCHAR2 (4000);

l_long_len NUMBER;

l_buflen NUMBER := 4000;

l_curpos NUMBER := 0;

BEGIN

DBMS_SQL.parse (l_cursor,

'select ' || p_cname || ' from ' || p_tname || ' where rowid = :x',

DBMS_SQL.native);

DBMS_SQL.bind_variable (l_cursor, ':x', p_rowid);

DBMS_SQL.define_column_long (l_cursor, 1);

l_n := DBMS_SQL.execute (l_cursor);

IF (DBMS_SQL.fetch_rows (l_cursor) > 0)

THEN

DBMS_SQL.column_value_long (l_cursor, 1, l_buflen, l_curpos, l_long_val,

l_long_len);

END IF;

DBMS_SQL.close_cursor (l_cursor);

RETURN l_long_val;

END fn_getlong;

Now Check your records including long values

SELECT pk, long_val, varchar_val,

fn_getlong ('NUWANK', 'LONG_VAL', ROWID) val

FROM NUWANK;

-- PK LONG_VAL VARCHAR_VAL VAL

-- 1 (LONG) 100

-- 2 (LONG) 200

-- 3 (LONG) 300

Now you can do what ever updates you need

BEGIN

FOR x

IN (SELECT pk, long_val, varchar_val,

fn_getlong ('NUWANK', 'LONG_VAL', ROWID) val

FROM NUWANK)

LOOP

UPDATE NUWANK

SET varchar_val = x.val

WHERE pk = x.pk;

END LOOP;

END;

Check your updated records

SELECT * FROM nuwank;

-- PK LONG_VAL VARCHAR_VAL

-- 1 (LONG) 100

-- 2 (LONG) 200

-- 3 (LONG) 300

Clean All RnD work

DROP TABLE nuwank PURGE;

DROP FUNCTION fn_getlong;

Broken Job Fixer

2010-12-01T03:22:00.000-08:00

CREATE OR REPLACE PROCEDURE sp_job_fixer

CURSOR broken_jobs_cur

SELECT job

FROM user_jobs

WHERE broken = 'Y';

BEGIN

FOR job_rec IN broken_jobs_cur

LOOP

DBMS_JOB.broken (JOB => job_rec.job, BROKEN => FALSE);

--dbms_output.put_line(job_rec.job);

END LOOP;

END;

BEGIN

sp_job_fixer;

END;

DROP PROCEDURE sp_job_fixer;

Cumulative Aggregate

2010-07-06T03:37:00.000-07:00

CREATE TABLE cum_test

(col0 NUMBER,

col1 NUMBER)

INSERT INTO cum_test (COL0,COL1) VALUES(1,100);

INSERT INTO cum_test (COL0,COL1) VALUES(1,200);

INSERT INTO cum_test (COL0,COL1) VALUES(1,300);

INSERT INTO cum_test (COL0,COL1) VALUES(2,1000);

INSERT INTO cum_test (COL0,COL1) VALUES(2,1100);

INSERT INTO cum_test (COL0,COL1) VALUES(2,1200);

SELECT * FROM CUM_TEST;

COL0 COL1

1 100

1 200

1 300

2 1000

2 1100

2 1200

SELECT COL0, COL1,

SUM(SUM (COL1))

OVER (

PARTITION BY COL0

ORDER BY COL0, COL1

ROWS UNBOUNDED PRECEDING

)

CUMULATIVE_COL0

FROM CUM_TEST

GROUP BY COL0, COL1

ORDER BY COL0, COL1

Output

COL0 COL1 CUMULATIVE_COL0

1 100 100

1 200 300

1 300 600

2 1000 1000

2 1100 2100

2 1200 3300

Check for NON-Numeric Value

2010-07-06T01:04:00.000-07:00

SELECT DECODE(LENGTH(TRANSLATE('145','_0123456789','_')),NULL,1,0) "IS_NUMERIC" FROM DUAL

Will return --> 1

SELECT DECODE(LENGTH(TRANSLATE('ABC','_0123456789','_')),NULL,1,0) "IS_NUMERIC" FROM DUAL

Will return --> 0

Recreate Sequences - Dynamically

2010-06-18T01:58:00.000-07:00

declare

l_seq_start number;

l_seq_name varchar2(100) := 'SEQ_T1_OBJECT_ID';

l_c1 number;

l_sql varchar2(1000) := 'create sequence '||l_seq_name||' start with '; begin

select count(*)

into l_c1

from user_sequences

where sequence_name = l_seq_name;

if l_c1 = 1 then

execute immediate ('drop sequence '||l_seq_name);

end if;

select nvl(max(object_id),0) +10

into l_seq_start

from t1;

l_sql := l_sql || to_char(l_seq_start);

execute immediate (l_sql);

--dbms_output.put_line(l_sql);

end;

2010-06-18T01:54:00.000-07:00

DROP Database

You can do it at the OS level by deleting all the files of the database. The files to be deleted can be found using:

1) select * from dba_data_files;

2) select * from v$logfile;

3) select * from v$controlfile;

4) archive log list

5) initSID.ora

6) In addition you can clean the UDUMP, BDUMP, scripts etc

Clean up the listener.ora and the tnsnames.ora. make sure that the oratab entry is also removed.

go to dbca and click on delete database

In Oracle 10g, there is a new command to drop an entire database.

Startup restrict mount;

drop database ;

Get time difference between two date columns

2009-12-28T01:33:00.000-08:00

Ex 1

SELECT floor((date1-date2)*24) || ' HOURS ' ||

mod(floor((date1-date2)*24*60),60) || ' MINUTES ' ||

mod(floor((date1-date2)*24*60*60),60) || ' SECS ' time_difference

FROM dates

Ex 2

SELECT to_number( to_char(to_date('1','J') + (date1 - date2), 'J') - 1) days,

to_char(to_date('00:00:00','HH24:MI:SS') + (date1 - date2), 'HH24:MI:SS') time

FROM dates;

Ex 3

select numtodsinterval(date1-date2,'day') time_difference from dates;

Setup VPN setup on Ubuntu

2009-07-01T04:33:00.000-07:00

PPTP (Microsoft VPN)
sudo apt-get install network-manager-pptp

Cisco VPN
sudo apt-get install network-manager-vpnc

OpenVPN
sudo apt-get install network-manager-openvpn

Analytical Functions

2009-06-03T21:23:00.000-07:00

Sample Run
-----------
drop table t1 purge;

create table t1 (c1 number, c2 number, c3 date default sysdate);

insert into t1 (c1,c2) values (1,10);
insert into t1 (c1,c2) values (1,20);
insert into t1 (c1,c2) values (1,30);
insert into t1 (c1,c2) values (2,10);
insert into t1 (c1,c2) values (2,15);
insert into t1 (c1,c2) values (3,10);
insert into t1 (c1,c2) values (3,11);
insert into t1 (c1,c2) values (4,10);

select * from t1;

select a.*, max(a.c2) over (partition by c1) max_c2
from t1 a;

select *
from (select a.*, max(a.c2) over (partition by c1) max_c2
from t1 a
) b
where b.c2 = b.max_c2
;

Output
--------

SQL> drop table t1 purge;
Table dropped.
SQL> create table t1 (c1 number, c2 number, c3 date default sysdate);
Table created.
SQL> insert into t1 (c1,c2) values (1,10);
1 row created.
SQL> insert into t1 (c1,c2) values (1,20);
1 row created.
SQL> insert into t1 (c1,c2) values (1,30);
1 row created.
SQL> insert into t1 (c1,c2) values (2,10);
1 row created.
SQL> insert into t1 (c1,c2) values (2,15);
1 row created.
SQL> insert into t1 (c1,c2) values (3,10);
1 row created.
SQL> insert into t1 (c1,c2) values (3,11);
1 row created.
SQL> insert into t1 (c1,c2) values (4,10);
1 row created.

SQL> select * from t1;
C1 C2 C3
---------- ---------- ---------
1 10 03-JUN-09
1 20 03-JUN-09
1 30 03-JUN-09
2 10 03-JUN-09
2 15 03-JUN-09
3 10 03-JUN-09
3 11 03-JUN-09
4 10 03-JUN-09
8 rows selected.

SQL> select a.*, max(a.c2) over (partition by c1) max_c2
from t1 a;
C1 C2 C3 MAX_C2
---------- ---------- --------- ----------
1 10 03-JUN-09 30
1 20 03-JUN-09 30
1 30 03-JUN-09 30
2 10 03-JUN-09 15
2 15 03-JUN-09 15
3 10 03-JUN-09 11
3 11 03-JUN-09 11
4 10 03-JUN-09 10
8 rows selected.

SQL> select *
from (select a.*, max(a.c2) over (partition by c1) max_c2
from t1 a) b
where b.c2 = b.max_c2 ;
C1 C2 C3 MAX_C2
---------- ---------- --------- ----------
1 30 03-JUN-09 30
2 15 03-JUN-09 15
3 11 03-JUN-09 11
4 10 03-JUN-09 10

Reference
-----------
http://www.orafaq.com/node/55

Manually configure the Oracle 10g Enterprise Manager

2009-05-17T22:22:00.000-07:00

oracle@nuwank:/home/app/oracle/product/10.2.0/db_1/bin$ emca -repos create

oracle@nuwank:/home/app/oracle/product/10.2.0/db_1/bin$ emca -config dbcontrol db

STARTED EMCA at May 18, 2009 10:46:20 AM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: MYDB2
Database Control is already configured for the database MYDB2
You have chosen to configure Database Control for managing the database MYDB2
This will remove the existing configuration and the default settings and perform a fresh configuration
Do you wish to continue? [yes(Y)/no(N)]: Y
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /home/app/oracle/product/10.2.0/db_1

Database hostname ................ nuwank
Listener port number ................ 1521
Database SID ................ MYDB2
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y

RHEL Run Level Commands

2009-04-22T03:50:00.000-07:00

RHEL Run Level Commands

* Shutdown:
init 0
shutdown -h now
+ -a: Use file /etc/shutdown.allow
+ -c: Cancel scheduled shutdown.
halt -p
+ -p: Turn power off after shutdown.
poweroff

* Reboot:
init 6
shutdown -r now
reboot

* Enter single user mode:
init 1

Change Linux IP address

2009-04-20T22:19:00.000-07:00

Display current IP address and setting for network interface called eth0

# ifconfig eth0

Output:
eth0
Link encap:Ethernet HWaddr 00:30:48:5A:BF:46
inet addr:10.5.123.2 Bcast:10.5.123.63 Mask:255.255.255.192
inet6 addr: fe80::230:48ff:fe5a:bf46/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:728204 errors:0 dropped:0 overruns:0 frame:0
TX packets:1097451 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:62774749 (59.8 MiB) TX bytes:1584343634 (1.4 GiB)
Interrupt:177

Change IP address

You can change ip address using ifconfig command itself. To set IP address 192.168.2.101, enter command:

# ifconfig eth0 192.168.2.101 netmask 255.255.0.0 up
# ifconfig eth0

To make permanent changes to IP address you need to edit configuration file according to your Linux distribution.

Check 32-bit or 64-bit

2009-03-12T23:55:00.000-07:00

1. sqlplus
should display whether it is 64 bit or 32 bit

or

2. sqlplus / as sysdba
select banner from v$version where banner like 'Oracle%';

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi

or

3. select address from v$sql where rownum < 2

32-bit
ADDRESS
--------
8435920

64-bit
ADDRESS
----------------
78BA1040787F3E44

Note: 64-bit has 16 characters

Free Space in Tablespace

2009-03-12T03:28:00.000-07:00

select tablespace_name,round(sum(bytes/(1024*1024))) "Free Bytes In MB"
from dba_free_space
group by tablespace_name;

Change RHEL5 Default Repository

2009-03-12T01:13:00.000-07:00

[root@rhel5 ~]# cd /etc/yum.repos.d

[root@rhel5 yum.repos.d]# ls
rhel-debuginfo.repo

[root@rhel5 yum.repos.d]# cat rhel-debuginfo.repo

[rhel-debuginfo]
name=Red Hat Enterprise Linux $releasever - $basearch - Debug
baseurl=ftp://ftp.redhat.com/pub/redhat/linux/enterprise/$releasever/en/os/$basearch/Debuginfo/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-redhat-release

[root@rhel5 yum.repos.d]# cp rhel-debuginfo.repo rhel-base.repo

[root@rhel5 yum.repos.d]# vi rhel-base.repo

[rhel-base]
name=Red Hat Enterprise Linux $releasever - $basearch - Debug
baseurl=http://192.168.0.25/rhel/5.0/RPMS
enabled=1
gpgcheck=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-redhat-release

Note: http://192.168.0.25/rhel/5.0/RPMS is the new location with rpms

[root@rhel5 ~]# yum clean all

Now you can proceed with installations
[root@rhel5 ~]# yum install gcc

Solution to SYS_GUID in PostgreSQL

2009-03-12T00:43:00.000-07:00

In Oracle

SQL> select sys_guid() from dual;

SYS_GUID()
--------------------------------
64E8185AA305DEF4E040007F010027D3

In PostgreSQL

1. Create a sequence

CREATE SEQUENCE seq_new_val
INCREMENT 1
MINVALUE 1
MAXVALUE 999999999999999999
START 60
CACHE 20;

2. Generate the value

select md5(clock_timestamp()::text||random()::text||nextval('seq_new_val'))

md5 Text
--------------------------------
ea07e20cee142c942cc043db0a4d8f09

Solution to Object Type in PostgreSQL

2009-03-12T00:27:00.000-07:00

1. Create a Function

CREATE OR REPLACE FUNCTION nuwank_test(IN p_code character varying, OUT p_cv1 refcursor)
RETURNS refcursor AS
$$
DECLARE

l_code VARCHAR (4000) := p_code || ',';
n integer;
BEGIN

EXECUTE 'CREATE TEMPORARY TABLE tmpTbl(x_cd varchar(4)) ON COMMIT DROP';
while position( ',' in l_code)<>0
loop
n := position( ',' in l_code);
INSERT INTO tmpTbl VALUES (substr(l_code,0,n));
l_code :=substring(l_code from n+1);
end loop;

OPEN p_cv1 FOR
SELECT *
FROM abc
WHERE code IN (select x_cd from tmpTbl);

END;
$$
LANGUAGE 'plpgsql';

2. Test the function

begin;
select nuwank_test('ABC,XYZ,PQR');
fetch all in "";
end;

Install Oracle Using Response File

2009-03-11T03:22:00.000-07:00

1. Start the installer
./runInstaller -record -destinationFile /tmp/recorded.rsp

2. Make all the selections according to your preference (source destination, home, home name, products)

3. At the Summary screen, instead of Install, press Cancel

4. Response file will be created in /tmp/recorded.rsp. If needed change the file as you desired

5. Do the silent installation
./runInstaller -silent -responseFile /tmp/recorded.rsp

Note:
In case of an installation failure, information can be found in the directory oraInventory/logs. (format installActions-.log and silentInstall.log)
Can be work with dbca and netca, by copying the files /source/database/response/dbca.rsp netca.rsp to convenient location

Ex: dbca -silent -responseFile /tmp/dbca.rsp

Date Upto Milli Seconds

2009-03-11T02:57:00.000-07:00

select to_char(systimestamp, 'DD.MM.YYYY:HH24:MI:SS.FF6') from dual;
11.03.2009:09:56:13.718000

Note:
FF[1-9] --> This format is to get the Fractional seconds
Ex: FF3 Thousandths of a second (1.123 seconds)
FF6 Millionths of a second (1.123456 seconds)

Check Oracle Error Description in Shell Prompt

2009-03-11T02:40:00.000-07:00

Ex: ORA-00308

oracle@nuwank:~$ oerr ora 308

00308, 00000, "cannot open archived log '%s'"
// *Cause: The system cannot access a required archived redo log file.
// *Action: Check that the off line log exists, the storage device is
// online, and the archived file is in the correct location.
// Then attempt to continue recovery or restart the recovery
// session.

Session Time Format

2009-03-11T02:35:00.000-07:00

alter session set nls_date_format='YYYY-MM-DD:HH24:MI:SS';

Check Character set

2009-03-11T02:30:00.000-07:00

select * from props$
where name like 'NLS_CHARACTERSET';

NAME VALUE$ COMMENT$
------------------------------------------------------------
NLS_CHARACTERSET AL32UTF8 Character set

Get Only Time

2009-03-11T02:26:00.000-07:00

select substr(to_char(sysdate,'DD.MM.YYYY:HH24:MI:SS'),-8,8) from dual;

Shrink undo tablespace in 10g

2009-03-11T02:02:00.000-07:00

1. Create a new undo tablespace

create undo tablespace undotbs2 datafile '/home/app/oracle/oradata/mydb/undotbs02.dbf' size 100m autoextend on next 10240k maxsize 32767m ;

2. Issue the alter system command to point to the temporary tablespace

alter system set undo_tablespace = UNDOTBS2 ;

3. Drop the original undo tablespace including datafile

drop tablespace "undotbs1" including contents and datafiles;

4. Recreate the original one

create undo tablespace undotbs1 datafile '/home/app/oracle/oradata/mydb/undotbs01.dbf' size 100m autoextend on next 10240k maxsize 32767m ;

5. Issue the alter system command to point to the new tablespace

alter system set undo_tablespace = UNDOTBS1 ;

6. Drop the temporally created tablespace

drop tablespace "undotbs2" including contents and datafiles;

Note: Find out which undo tablespace is being used:

select name,value from v$parameter where name in ('undo_management','undo_tablespace');

Automatic Database Startup (Oracle 10g) at System Startup

2009-03-11T01:12:00.000-07:00

1. Edit /etc/oratab

Set the restart flag to "Y" for the instance you want to automatically startup

MYDB:/home/app/oracle/product/10.2.0/db_1:Y

2. Create dbora file
create a text file named /etc/init.d/dbora as the root user

#!/bin/sh
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.
#
# Set ORA_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
#
# Set ORA_OWNER to the user id of the owner of the
# Oracle database in ORA_HOME.
ORA_HOME=/home/app/oracle/product/10.2.0/db_1
ORA_OWNER=oracle
if [ ! -f $ORA_HOME/bin/dbstart ]
then
echo "Oracle startup: cannot start"
exit
fi
case "$1" in
'start')
# Start the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su - $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME"
;;
'stop')
# Stop the Oracle databases:
# The following command assumes that the oracle login
# will not prompt the user for any values
su - $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME"
;;
esac

3. Set the privileges to 750

# chmod 750 /etc/init.d/dbora

4. Set appropriate run levels

# chkconfig --level 345 dbora on

Duplicate Records in a Table

2009-03-08T23:51:00.000-07:00

Find Duplicate Rows

select *
from employee_tab a
where rowid >
(select min(rowid) from employee_tab b where b.emp_id = a.emp_id);

Remove Duplicate Rows

delete from employee_tab a
where a.rowid >
(select min(rowid) from employee_tab b where a.emp_id = b.emp_id);

or

delete from employee_tab
where rowid in (select rowid
from (select rowid,
row_number() over(partition by emp_id order by emp_id) dup
from customer)
where dup > 1);

Pagination

2009-03-06T03:11:00.001-08:00

Ex: records between 20 and 50

select *
from (select a.*, rownum
from (
select first_name, last_name, department_id, salary
from employees
order by salary) a
where rownum <= 50)
where rownum >= 20

Change Upper Case file names to Lower Case

2009-03-05T23:02:00.000-08:00

1. Before executing the script

nuwank@nuwank:~/tmp$ ls
ABC1.txt ABC2.txt PQR1.txt PQR2.txt

2. Script

#!/bin/sh
for i in *
do
j=`echo $i | tr '[A-Z]' '[a-z]'`
mv $i $j
done

3. After executing the script

nuwank@nuwank:~/tmp$ ls
abc1.txt abc2.txt pqr1.txt pqr2.txt

Get Arrow keys working on sqlplus

2009-03-05T22:56:00.000-08:00

01. Install rlfe

nuwank@nuwank:~$ sudo apt-get install rlfe

02. You can use an alias for the program to ensure that your program is always run in this 'enhanced' text entry mode. For example, add the following to ~/.bashrc

alias sqlplus='rlfe sqlplus'

Format Flash Drive on Linux

2009-03-05T22:52:00.000-08:00

root@nuwank:~# fdisk -l

Disk /dev/sda: 80.0 GB, 80026361856 bytes
255 heads, 63 sectors/track, 9729 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Disk identifier: 0xc86e764a

Device Boot Start End Blocks Id System
/dev/sda1 * 1 1824 14651248+ 83 Linux
/dev/sda2 1825 2310 3903795 82 Linux swap / Solaris
/dev/sda3 2311 9729 59593117+ 5 Extended
/dev/sda5 2311 9729 59593086 83 Linux

Disk /dev/sdb: 1044 MB, 1044381184 bytes
129 heads, 16 sectors/track, 988 cylinders
Units = cylinders of 2064 * 512 = 1056768 bytes
Disk identifier: 0x67181611

Device Boot Start End Blocks Id System
/dev/sdb1 * 1 989 1019895+ b W95 FAT32
Partition 1 has different physical/logical endings:
phys=(994, 128, 16) logical=(988, 35, 15)

root@nuwank:~# mkfs.vfat -vc /dev/sdb1

mkfs.vfat 2.11 (12 Mar 2005)
Auto-selecting FAT32 for large filesystem
/dev/sdb1 has 33 heads and 61 sectors per track,
logical sector size is 512,
using 0xf8 media descriptor, with 2039790 sectors;
file system has 2 32-bit FATs and 8 sectors per cluster.
FAT size is 1989 sectors, and provides 254472 clusters.
Volume ID is 4799d8e9, no volume label.
Searching for bad blocks 56800... 113440... 169504... 225824... 282272... 338656... 395040... 451552... 507936... 564256... 620768... 677216... 733664... 789856... 845024... 901280... 957408... 1013728...

Enable Flashback

2009-02-05T23:51:00.000-08:00

Introduction

Flashback Database is an alternative to a point in time recovery (PITR). datafiles are brought to their contents they had at a past time.

Old database block images are stored in a flash recovery area which allow fast rollbacks of database (as no online redo logs are required.)

Relevant parameters:

db_recovery_file_dest

db_recovery_file_dest_size

db_flashback_retention_target

Relevant Commands:

alter database flashback on | off

alter tablespace flashback on | off

Steps

Log as SYS:

CMD>set oracle_sid=DB01

CMD>sqlplus / as sysdba

Database should be operated in spfile mode :

SQL>show parameter spfile;

Set the Archive Log Destination:

SQL>alter system set log_archive_dest_1='location=F:\oracle\oradata\DB01\archives';
SQL>alter system set log_archive_format='arch_%t_%s_%r.log' scope=spfile;

Check the recovery file destination parameters:

SQL>show parameter recover

It should show the parameters like db_recovery_file_dest, db_recovery_file_dest_size, recovery_parallelism

Change the parameter values : Ex:

SQL>alter system set db_recovery_file_dest_size=10G scope=both;

SQL>alter system set db_recovery_file_dest='F:\oracle\oradata\flash_recovery_area' scope=both;

Check the retention parameters:

SQL>show parameter reten

It should show the parameters like db_flashback_retention_target, undo_retention

Change the parameter values : Ex:

SQL>alter system set db_flashback_retention_target=14400 scope=both;

Check the lag parameters:

SQL>show parameter lag

It should show the parameters like archive_lag_target

Change the parameter values : Ex:

SQL>alter system set archive_lag_target=3600 scope=both;

Then do a checkpoint :

SQL>alter system checkpoint;

Shutdown the database :

SQL>shutdown immediate;

Statup the database (mount) :

SQL>startup mount;

Execute following statements to enable flashback :

SQL>alter database archivelog;

SQL>alter database force logging;

SQL>alter database flashback on;

Shutdown the database :

SQL>shutdown immediate;

Statup the database :

SQL>startup

Create a restore point :

SQL>create restore point initdb guarantee flashback database;

You can view the created restore point :

SQL>select * from v$restore_point;

Restore to previous point

SQL>shutdown immediate;
SQL>startup mount;
SQL>flashback database to restore point initdb;
SQL>alter database open resetlogs;

Housekeep Flash Recovery Area

The database issues a warning alert when reclaimable space is less than 15% and a critical alert when reclaimable space is less than 3%. You can see the alerts in the alert.log and in DBA_OUTSTANDING_ALERTS.

SQL>select object_type, message_type, message_level, reason, suggested_action from dba_outstanding_alerts;

If the flash recovery area becomes full, an error is issued. Beware of using the flash recovery area for log_archive_dest_n.
If the flash recovery becomes full and Oracle cannot archive redo logs then the instance will hang.

v$flash_recovery_area_usage, shows the percentage of the total disk quota used by different types of files, and how much space for each type of file can be reclaimed by deleting files

SQL>select * from v$flash_recovery_area_usage;

v$recovery_file_dest, shows the current location, disk quota, space in use, space reclaimable by deleting files, and total number of files in the flash recovery area

SQL>select * from v$recovery_file_dest;

The following actions can be done to resolve the space issue :

1.Add disk space to the Flash Recovery Area or increase DB_RECOVERY_FILE_DEST_SIZE

SQL>alter system set DB_RECOVERY_FILE_DEST_SIZE= ;

2.Delete unnecessary restore points

SQL>drop restore point initdb;

3.Delete the files from the Flash Recovery Area using RMAN

set oracle_sid=MYSID

rman target /

rman> crosscheck backup;
rman> delete obsolete;
rman> crosscheck archivelog all;
rman> delete expired archivelog all;

Timing for Schedules

2009-02-05T23:37:00.000-08:00

Description	Date Expression
Now	SYSDATE
Tomorow/ next day	SYSDATE + 1
Seven days from now	SYSDATE + 7
One hour from now	SYSDATE + 1/24
Three hours from now	SYSDATE + 3/24
An half hour from now	SYSDATE + 1/48
10 minutes from now	SYSDATE + 10/1440
30 seconds from now	SYSDATE + 30/86400
Tomorrow at 12 midnight	TRUNC(SYSDATE + 1)
Tomorrow at 8 AM	TRUNC(SYSDATE + 1) + 8/24
Next Monday at 12:00 noon	NEXT_DAY(TRUNC(SYSDATE), 'MONDAY') + 12/24
First day of the month at 12 midnight	TRUNC(LAST_DAY(SYSDATE ) + 1)
The next Monday, Wednesday or Friday at 9 a.m	TRUNC(LEAST(NEXT_DAY(sysdate,MONDAY' ' ),NEXT_DAY(sysdate,WEDNESDAY), NEXT_DAY(sysdate,FRIDAY ))) + (9/24)

Calculate number of milli seconds

2009-02-05T23:34:00.000-08:00

select to_number(sysdate - to_date('01-JAN-1970','DD-MON-YYYY')) * (24 * 60 * 60 * 1000) from dual;

Reset Sequences

2009-01-26T22:35:00.000-08:00

1. Create Sequence Syntax

SQL> create sequence nuwank_seq start with 1 increment by 1;

2. Check the current value

SQL> select nuwank_seq.nextval from dual;
NEXTVAL
----------
11

3. Reset the sequence

SQL> alter sequence nuwank_seq increment by -10;
Sequence altered.

SQL> select nuwank_seq.nextval from dual;
NEXTVAL
----------
1

SQL> alter sequence nuwank_seq increment by 1;
Sequence altered.

Monitor Import Speed

2009-01-26T22:28:00.000-08:00

SELECT
SUBSTR(sql_text, INSTR(sql_text,'INTO "'),30) table_name
, rows_processed
, ROUND( (sysdate-TO_DATE(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes
, TRUNC(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_minute
FROM
sys.v_$sqlarea
WHERE
sql_text like 'INSERT %INTO "%'
AND command_type = 2
AND open_versions > 0;

How To Create RMAN Catalog

2008-05-30T01:48:00.000-07:00

1. Create the catalog Database (RMANDB)
2. Create a tablespace named "rmandb_ts"

create tablespace rmandb_ts datafile '/home/app/oracle/oradata/RMANDB/rmandb_ts01.dbf' size 100m autoextend on;

3. Create a user named "rman"

create user rman identified by rman default tablespace rmandb_ts quota unlimited on rmandb_ts temporary tablespace temp;

4. Log to RMANDB and issue following commands

$ . oraenv
$ RMANDB
$ sqlplus / as sysdba

SQL> grant connect, resource, recovery_catalog_owner to rman;
SQL> select name from v$datafile;
SQL> alter user rman quota unlimited on rmandb_ts;
SQL> exit
$

$ rman catalog rman/rman
RMAN> create catalog tablespace rmandb_ts;
recovery catalog created

5. Go to Target database site and enter the tns entry to RMANDB and register the target database

$ . oraenv
$ PRIMARYDB
$ rman target / catalog rman/rman@rmandb

RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> exit;
$