Media recovery

One day my oracle database server gives me below error message and my database running in no-archivelog mode.

ERROR at line 1:
ORA-01113: file 12 needs media recovery
ORA-01110: data file 12: 'D:\ORACLE\ORADATA\URMI\XDB01.DBF'

i am just searching for solution on tom kyte site and finally i get it which is as below.

ERROR at line 1:
ORA-01113: file 11 needs media recovery
ORA-01110: data file 11: 'C:\ORACLE\ORADATA\TEST\TEST01.DBF'

SQL> archive log list
Database log mode No Archive Mode <------------- NOARCHIVELOG MODE!
Automatic archival Disabled
Archive destination C:\oracle\ora92\RDBMS
Oldest online log sequence 2
Current log sequence 4

SQL> recover tablespace test; <---- hOW IS IT POSSIBLE IN NOARCHIVELOG MODE?
Media recovery complete.

SQL> select * from scott.t;

X
----------
10

Followup:

it just used the online redo logs. its always been "possible"

Attending Developer Days in Mumbai

During hands-on sessions.
Thanks - Pictures taken from Grant Ronald's Blog.

LOGMINER.

I am just testing logminer I have Oracle EE 9.2.0.1 installed on WinXP with noarchive mode.

connected with scott user.
scott@NIKUNJ> select count(*) from test;

COUNT(*)
----------
0

scott@NIKUNJ> insert into test select * from tab;
5 rows created.

scott@NIKUNJ> insert into test select * from tab;
5 rows created.

scott@NIKUNJ> insert into test select * from tab;
5 rows created.

scott@NIKUNJ> commit;
Commit complete.

scott@NIKUNJ> select count(*) from test;
COUNT(*)
----------
15

scott@NIKUNJ> delete from test;
15 rows deleted.

scott@NIKUNJ> commit;
Commit complete.

scott@NIKUNJ> select count(*) from test;
COUNT(*)
----------
0

scott@NIKUNJ>


Now I would like to get rows back.
connected with sys user and run below command.

sys@NIKUNJ> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => -
> 'C:\ORACLE\ORADATA\NIKUNJ\REDO03.LOG',OPTIONS => DBMS_LOGMNR.NEW);

PL/SQL procedure successfully completed.

Here I had given my current online log file, you will get list of current log file through
sys@NIKUNJ> select * from v$logfile;
GROUP# STATUS TYPE
---------- ------- -------
MEMBER
--------------------------------------------------------------------------------
3 ONLINE
C:\ORACLE\ORADATA\NIKUNJ\REDO03.LOG
2 STALE ONLINE
C:\ORACLE\ORADATA\NIKUNJ\REDO02.LOG
1 STALE ONLINE
C:\ORACLE\ORADATA\NIKUNJ\REDO01.LOG

sys@NIKUNJ>


sys@NIKUNJ> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
PL/SQL procedure successfully completed.

Above need for below reasons.

To fully translate the contents of redo logs, LogMiner requires access to a database
dictionary.

LogMiner uses the dictionary to translate internal object identifiers and datatypes to
object names and external data formats. Without a dictionary, LogMiner returns
internal object IDs and presents data as hex bytes.

For example, instead of the SQL statement:
INSERT INTO emp(name, salary) VALUES ('John Doe', 50000);
LogMiner will display:
insert into Object#2581(col#1, col#2) values (hextoraw('4a6f686e20446f65'),
hextoraw('c306'));"

LogMiner gives you three choices for your source dictionary:
1.Extracting the Dictionary to a Flat File
2.Extracting a Dictionary to the Redo Logs
3.Using the Online Catalog

sys@NIKUNJ> execute dbms_logmnr.start_logmnr;
PL/SQL procedure successfully completed.

sys@NIKUNJ> select sql_redo,sql_undo from v$logmnr_contents where username='SCOTT';


Then for time base you have to execute as below.

execute dbms_logmnr.start_logmnr(STARTTIME => TO_DATE('01-OCT-2005 10:00:00', 'DD-MON-YYYY HH:MI:SS'), -
ENDTIME => TO_DATE('01-OCT-2005 12:00:00', 'DD-MON-YYYY HH:MI:SS'));

might be you get error message like this which i get
ERROR at line 1:
ORA-01291: missing logfile
ORA-06512: at "SYS.DBMS_LOGMNR", line 53
ORA-06512: at line 1

then i add another log file
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
LOGFILENAME => 'C:\ORACLE\ORADATA\NIKUNJ\REDO02.LOG', -
OPTIONS => DBMS_LOGMNR.ADDFILE);

and executed once again and i get sucess.
execute dbms_logmnr.start_logmnr(STARTTIME => TO_DATE('01-OCT-2005 10:00:00', 'DD-MON-YYYY HH:MI:SS'), -
ENDTIME => TO_DATE('01-OCT-2005 12:00:00', 'DD-MON-YYYY HH:MI:SS'));

EXECUTE DBMS_LOGMNR.END_LOGMNR;



LogMiner information is contained in the following views. You can use SQL to query them as you would any other view.
V$LOGMNR_CONTENTS
Shows changes made to user and table information.
V$LOGMNR_DICTIONARY
Shows information about the LogMiner dictionary file, provided the dictionary was created using the STORE_IN_FLAT_FILE option. The information show includes the database name and status information.
V$LOGMNR_LOGS
Shows information about specified redo logs. There is one row for each redo log.
V$LOGMNR_PARAMETERS
Shows information about optional LogMiner parameters, including starting and ending system change numbers (SCNs) and starting and ending times.

Inside Oracle9i Tablespace Management

Good article by Donald K. Burleson

GUI Firewall Tool for Linux

Gurddog
Smoothwall
Firestarter

I asked to tom abt. db_2k_cache_size

Hi Tom,

If my database server have below
DB_BLOCK_SIZE=4096
DB_CACHE_SIZE=12M
DB_2K_CACHE_SIZE=8M
DB_8K_CACHE_SIZE=4M

and if my database server have
DB_BLOCK_SIZE=4096
DB_CACHE_SIZE=12M
DB_2K_CACHE_SIZE=0
DB_8K_CACHE_SIZE=0

what are the proc and cros between this two ?

Regards,
Nikunj


Followup:
if you don't have any 2k or 8k tablespaces - you would not configure the 2k/8k
caches - they only get used if you have them.

If you "unconfigure" them and have tablespaces with those blocksizes - you'll
not be able to use them.

How to get Block size base on OS

For Windows on Intel.
1. For window on Intel default size is 512 byte, but you can change it by pre-formatting hard drive.

2. Use fsutil fsinfo ntfsinfo c: to get

c:\>fsutil fsinfo ntfsinfo c:
NTFS Volume Serial Number : 0x1e345b64345b3e49
Version : 3.1
Number Sectors : 0x0000000004a852c0
Total Clusters : 0x0000000000950a58
Free Clusters : 0x00000000003ae433
Total Reserved : 0x0000000000000000
Bytes Per Sector : 512
Bytes Per Cluster : 4096 -- this is answer
Bytes Per FileRecord Segment : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length : 0x000000000d72a000
Mft Start Lcn : 0x00000000000c0000
Mft2 Start Lcn : 0x00000000004a852c
Mft Zone Start : 0x0000000000800020
Mft Zone End : 0x000000000083b520

3. To get all possible OS block sizes use
c:\ format /?

4. In explorer select your hard drive, click right button, select "Format..."
from
context menu, and see your OS block size as "Allocation unit size".

Redhat linux on Intel
dump2fs /dev/hda

set your sql prompt

please create login.sql with as below and put it in to ORACLE_HOME/bin.

define gname=idle
connect &connect_to
column global_name new_value gname
select lower(user) || '@' || substr( global_name,1,
decode( dot, 0, length(global_name), dot-1) ) global_name
from (select global_name, instr(global_name,'.') dot from global_name );
set sqlprompt '&gname> '

now when ever you connect to database use @login instead of connect so you get idea where you are.
i.e.
varakh@NIKUNJ> @login
Enter value for connect_to: ex2005/test@test
Connected.

GLOBAL_NAME
--------------------------------------------------------------------------------
ex2005@TEST

Input truncated to 24 characters
ex2005@TEST>

Worked example for using Excel through ODBC

I had read it on asktom i thing its good artical i had not tested but its good so i had posted here
Click Here

SPFILE

I have Book of Thomas Kyte Effective Oracle by Design. He had write good things on SPFILE which I want to share with you.

The Problem with PFILEs
PFILE resided on the database server. To start and shutdown the server everyone need to access the database server physically to ensure the correct init.ora file being used.

Also, if you used system management tools OEM, OMS to start and stop the database, the PFILEs needed to be physically wherever the system management tools was, and this generally, was not the machine where your database ran. In short you great confusion and “missing parameters” from time to time, If you simply started the database from a different machine, an entirely different set of parameters could be used, since a different PFILE would be used.

Another problem was that changes where made to the instance via the ALTER SYSTEM command, you would need to remember to update each and every copy of the PFILE out there in order to have the new setting take effect after next restart of the database. If you forgot, the system would run file until someone restarted the database, and then you would discover that the new settings you made were no longer there.

How SPFILEs Work
As SPFILE is stored in binary format on the server itself. You no longer need to have a local copy to start the database remotely. The binary file format serves two purposes:
• Change made via the ALTER SYSTEM command may now persist across server restarts. If is no longer necessary to update the init.ora text file with every little change. You can use SQL to maintain your parameter settings.
• It prevents us from being tempted to edit the file using any sort of editor. If provides SQL to manipulate it and prohibits us from editing it directly. However, if you do want to edit the file, there are techniques you can employ to accomplish that.

How to force database to use SPFILE.
If you are using oracle 9i or later by default server used SPFILE but if you migrated you can do it with.

Create spfile from pfile=’location of your pfile’;

Next time you start your database it will use SPFILE.

You can create textual version of this binary SPFILE using below command.

Create pfile=’path where you want to be created’ from spfile=’path of spfile’;

Now if you want to force database to use PFILE instead of SPFILE.
Start your database with below command instead of just startup.

Startup pfile=’location of your pfile’;

PFILE is not still obsolete?

Using a PFILE meant that if you started a database on Unix from your windows pc, the parameter file for the database need to be accessible on the PC. For some this was not a big deal. They would simply telnet into the database server itself and start it up or shut it down. The drawback to this was that everyone needed to physically access the server in order to ensure the correct init.ora file was being used to start the database (so everyone needed the ability to physically access the server)

refreshing my memory

Hi friends,

I get interview call and I am just refreshing my memory after going through with my manuals which I get from performance tuning training from Oracle Education centre.

So get some good point and here I want to share with you.

Why we need to check alter log file regularly.
•To detect internal errors (ora-600)
•Block corruption errors (ora-1578 or ora-1498)
•Operation that effect database structures and parameters, statement such as CREATE
DATABASE, STARTUP, SHUTDOWN, RECOVER.
•View nondefualt initialization parameter
•Checkpoint start and end times
•Incomplete checkpoints
•Time to perform archiving
•Instance recovery start and complete times
•Dead lock and timeout errors.

Difference between V$view and X$tables.
V$ views
•These are based on X$tables, therefore some V$ views are available in NOMOUNT and
MOUNT stages.
•They are listed in v$fixed_table.
•The V$ views actually synonyms for V_$ views and they are belong to the sys user.

X$ tables
•These are not usually queried directly, not all the information is necessarily
useful.
•The X$ tables are memory structures that hold instance information and are available
when the instance is in NOMOUNT and MOUNT state.
•The X$ tables are dynamic and there contents are constantly changing.

The V$ views and the underlying X$ tables are populated at instance startup and cleared at shutdown.

When SYSTEM tablespace is locally managed
•It is not possible to create dictionary managed tablespaces.
•It is not possible to migrate locally managed tablespace to dictionary managed.
•It is not possible to transport dictionary managed tablespace into database with
locally managed system tablespace. Note that after the transport, dictionary managed
tablespaces cannot be made read/write. The tablespace can be migrated to locally
managed and then read/write.
•It is possible to DROP and READ ONLY dictionary managed tablespace when system
tablespace locally managed.

Note : If the database create using DBCA then locally managed system tablespace is the default.

How many elements does the IN clause support

I had taken this quetions and reply from Best Practice PL/SQL
with Steven Feuerstein available at OTN.

How many elements does the IN clause support?
Asked by Alejandra Vergara from Somewhere in South America on Tuesday, July 27, 2004

Question:
I have a query of type select * from table1 where cod_table in ( 1,2,...,300). Does the IN clause in Oracle9i Database support more than 256 elements?

Answer:
Alejandra, in Oracle9i, you can have up to 1,000 elements in your IN list.

I take it you are worried about errors like this:

ORA-01795: maximum number of expressions in a list is 1000

And that is a valid concern....we might ask the same question about Oracle Database 10g and then the future releases as well. One way to get the answer is to check Oracle documentation, but an even better way is to check by running code.

I answered your question to my satisfaction by creating the following program:


CREATE OR REPLACE PROCEDURE test_in_limit (
table_in IN VARCHAR2
,int_column_in IN VARCHAR2
,limit_in IN PLS_INTEGER
)
IS
v_sql VARCHAR (32767);
i integer;
BEGIN
v_sql :=
'SELECT ' || int_column_in || ' FROM ' || table_in ||
' WHERE ' || int_column_in || ' IN (1';

FOR indx IN 2 .. limit_in
LOOP
v_sql := v_sql || ',' || indx;
END LOOP;

v_sql := v_sql || ')';

EXECUTE IMMEDIATE v_sql into i;
END test_in_limit;
/

(Source code available here.) I then ran it as follows without any error on Oracle9i:

BEGIN
test_in_limit ('sg_script', 'id', 1000);
END;
/

Yet here is what happened with 1,001:

SQL> BEGIN
2 test_in_limit ('sg_script', 'id', 1001);
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-01795: maximum number of expressions in a list is 1000
ORA-06512: at "QNXO_DEV.TEST_IN_LIMIT", line 24
ORA-06512: at line 2

Running this program on Oracle Database 10g has identical results.

External Tables

Benefit of using External Tables is you can alleviates the need for staging tables and redundant flat file loading as the file expands.

1.First you need to diagnose which character you can able to define as a delimited file.
2.CREATE DIRECTORY data_dir as 'c:\test';

3.CREATE DIRECTORY log_dir as 'c:\test\log';

4.
CREATE TABLE EXTERNAL_FILE_READ
(
EMPNO NUMBER(5),
EMP_NAME VARCHAR2(80),
DEPT_ID NUMBER(5)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY data_dir
ACCESS PARAMETERS
(
RECORDs DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE log_dir:'emp_%p.bad'
LOGFILE log_dir:'emp_%p.log'
FIELDS TERMINATED BY "|"LDRTRIM
)
location
('emp.csv')
)
REJECT LIMIT UNLIMITED NOPARALLEL
/
5.select * from external_file_read;
more information on external table you can read Database Utilities guide of oracle documentation.

Encrypting Data Travell withing or outside Network Through Oracle Advanced Security Option.

Data Transfer between network in plain text through Advance Security you can encrypt it.

You must need to configure oracle server and client.
You can do this through GUI tool.
But its also simple to do through without GUI. so below I had mention steps without GUI option.

You can use EtherDetect tool to check data traveling between Server and client to check this.

Steps at sever side
1.You need to edit sqlnet.ora which you will find in oracle home, network,admin folder (directory).
2.Add below three parameters.
SQLNET.ENCRYPTION_TYPES_SERVER = (3DES168)
SQLNET.ENCRYPTION_SERVER = accepted
SQLNET.CRYPTO_SEED= retquewruq

You can set any text in crypto_seed.

Only if we can set server side this will not work mean text will not be encrypted. You must need to specify at client side sqlnet.ora file.

3.Add below three parameters which you will find at same place.
SQLNET.ENCRYPTION_TYPES_CLIENT = (3DES168)
SQLNET.ENCRYPTION_CLIENT = requested
SQLNET.CRYPTO_SEED= sdfhasfasfha

We had set encryption_client parameter to requested means during the handshake between client and server. Client will request to sever for encryption and we had set at server to be accepted so server will accept encryption.

You can find more information on parameter at oracle documentation at oracle technology site in Oracle Advance Security Administrator’s guide.

My new wrist wach

Brought new wrist watch on Independence Day.

Drop Constraints.

Once i had faced problem in one table which have more than 287 duplicate constraints.

So i had buid one solution as below.

I had create one script and saved in c:\ REMOVECONSTRAINT.sql.

Contains of the script REMOVECONSTRAINT.sql as below.
SET HEADING OFF
SPOOL C:\DROPCONSTRAINT.SQL
select 'alter table 'OWNER'.'table_name' drop constraint 'constraint_name' cascade;'
from dba_constraints where owner=UPPER('&owner') and table_name=UPPER('&table_name')
/
SPOOL OFF;
@C:\DROPCONSTRAINT.SQL

if you go through with script you come to know it will generate dropconstraint.sql and execute it.

This script will remove all constraint from table.
After this you need to Re-create constraints your constraints.

Creating temporary tables

Temporary tables are created using the global temporary clause

Create global temporary table Hr.employees_temp
As select * from hr.employees;

Use of Temporary Tables

• Tables retain data only for the duration of a transacton or session.
• DML locks are not acquired on the data
• Can create indexes,views and triggers on temporary tables
  

You can import and export the definition but you can able to import and export the data.

Demonstration

Create global temporary table tt1
(lname varchar2(25), fname varchar2(25),empno number) on commit delete rows;

desc tt1;

select count(*) from tt1;

insert into tt1 values (‘thaker’,’nikunj’,1);

select * from tt1;

commit;

select * from tt1;

create global temporary table tt2
(lname varchar2(25), fname varchar2(25),empno number) on commit preserve rows;

data reside in the table will remain in the table after commit but logs are remove of this tables on structure of the table will resides.

Select table_name,temporary from user_tables Where table_name like ‘TT%’;

Drop table tt1;

Drop table tt2;

This will gives you error because it contains row because of the table we had created with on commit preserve rows;

Because of preserve rows row will reside in the table till session is open.

So we reconnet with that user and try to

select count(*) from tt2;

there is no data now we can remove this tables.

MOVE DATA FILE FROM X LOCATION TO Y LOCATION

The section offers some procedures for renaming and relocating datafiles in a single tablespace. You must have the ALTER TABLESPACE system privilege to rename datafiles of a single tablespace.

1. The following query of the data dictionary view DBA_DATA_FILES lists the datafile names and respective sizes (in bytes) of the users tablespace:

2. SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'USERS';

3. Take the tablespace containing the datafiles offline, or shut down the database and restart and mount it, leaving it closed. Either option closes the datafiles of the tablespace.

ALTER TABLESPACE USERS OFFLINE NORMAL;

/*ALTER DATABASE DATAFILE 'C:\ORACLE\ORADATA\NIKUNJ\USERS01.DBF' OFFLINE NORMAL;*/

4. Copy the datafiles to their new locations and rename them using the operating system.

COPY C:\ORACLE\ORADATA\NIKUNJ\USERS01.DBF TO C:\ORACLE\ORADATA\USERS01.DBF

5. Rename the datafiles within Oracle. The datafile pointers for the files that make up the users tablespace, recorded in the control file of the associated database, must now be changed from the old names to the new names.

If the tablespace is offline but the database is open, use the ALTER TABLESPACE ... RENAME DATAFILE statement. If the database is mounted but closed, use the ALTER DATABASE ... RENAME FILE statement.

ALTER TABLESPACE users RENAME DATAFILE 'C:\ORACLE\ORADATA\NIKUNJ\USERS01.DBF' TO 'C:\ORACLE\ORADATA\USERS01.DBF';

6. Bring the tablespace online, or open the database. If the users tablespace is offline and the database is open, bring the tablespace back online. If the database is mounted but closed, open the database.

ALTER TABLESPACE USERS ONLINE;

/*ALTER DATABASE DATAFILE 'C:\ORACLE\ORADATA\USERS01.DBF' ONLINE;*/

7. Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.

How to move system tablespace datafile

You can use one of two techniques as below.

1. Shutdown, move the file, re-create the control file, and startup.
2. Shutdown, stat up in mount mode, move them, and then open the database.

Here I am demonstrating 2^nd option because its easy.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>startup mount;

ORACLE instance started.

System Global Area 122754516 bytes

---

---

---

Database mounted.

SQL> !copy ‘c:\oracle\ora92\oradata\nikunj\system.dbf’ to ‘c:\oracle\ora92\oradata\system.dbf’

SQL>alter database rename file ‘c:\oracle\ora92\oradata\nikunj\system.dbf’ to ‘c:\oracle\ora92\oradata\system.dbf’;

Database altered.

SQL>alter database open;

Database altered.

Backup your control file again, and that’s it.

How to run Oracle listener on non-default port

You can able to run Oracle listener on non default oracle port.
open listener.ora file you can find that in oracle_home/network folder

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1577))
)
)
)

change port=

then open tnsnames.ora in the same directory.
test =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1577))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = test)
)
)

mention same port no.

Now oracle listen on non-default oracle port.

Also you can run multiple listener for one database.
change your listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1577))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1521))
)
)
)


I had asked thomas kyte for proc and cros there input as below.
Hi Tom,

If two listener on two port i.e. 1521 and 1522 are listening for one (X
database) database.

What are the pros and cros if one database have 2 listener and have 1 listener ?

Regards,
Nikunj

Followup:
pros - extreme cases.

cons - extra work, maintainance, monitoring.

if and only if a single listener was getting so nailed, hit so hard, that it
built up a queue of requests and started failing the requests because the queue
was too long would I consider another listener.

And even then, it would likely be on another machine, for another instance,
using the same database - if you are nailing a listener that hard....

Worksheet: Windows Server 2003 default services

Click above link.
Microsoft has made several key changes to Windows Server 2003. One of the best ways for admins to evaluate these changes is to examine the core services of the OS. This Excel worksheet breaks down the default services installed in Windows Server 2003.

TO RUN A COMMAND FROM ORACLE FOR ORACLE 9I

/* RUN BELOW SCRIPT LOGIN WITH SYS ACCOUNT */
begin
dbms_java.grant_permission
('TIMETRACK',
'java.io.FilePermission',
'C:\WINNT\*',
'execute');
dbms_java.grant_permission
('TIMETRACK',
'java.lang.RuntimePermission',
'*',
'writeFileDescriptor' );
end;
/
/* RUN BELOW SCRIPT LOGIN WITH APPLICATION USER */
create or replace and compile
java source named "Util"
as
import java.io.*;
import java.lang.*;
public class Util extends Object
{
public static int RunThis(String[] args)
{
Runtime rt = Runtime.getRuntime();
int rc = -1;
try
{
Process p = rt.exec(args[0]);
int bufSize = 4096;
BufferedInputStream bis =
new BufferedInputStream(p.getInputStream(), bufSize);
int len;
byte buffer[] = new byte[bufSize];
// Echo back what the program spit out
while ((len = bis.read(buffer, 0, bufSize)) != -1)
System.out.write(buffer, 0, len);
rc = p.waitFor();
}
catch (Exception e)
{
e.printStackTrace();
rc = -1;
}
finally
{
return rc;
}
}
}
/

create or replace
function RUN_CMD( p_cmd in varchar2) return number
as
language java
name 'Util.RunThis(java.lang.String[]) return integer';
/
create or replace procedure RC(p_cmd in varchar2)
as
x number;
begin
x := run_cmd(p_cmd);
end;
/
variable x number;
set serveroutput on
exec dbms_java.set_output(100000);
exec :x := run_cmd('net send nikunj test');
10514.ksh
10565.sql
...
xxx.dat
xxx.dbf
zz

Database Cloning

How to create database base on existing database.
You can able to clone database in same machine using Oracle Database Configuration Assistant but what about to copy database from Production to Test Server.

So I had done this through below way.
I have 2 Oracle Database EE 9.2 on Windows 2000 Server.
I had done below task on my A server.

CREATE PFILE=’d:/oracle/ora92/inittest.ora’ FROM SPFILE;
shutdown the database instance.
make a copy of oracle database files from oracle/ora92/oradata//*.* to temporary location including your PFILE so your database will not be down for more time.

I had done below taks on my B server.
1) create directory in oracle/ora92/oradata/ ie. Test
2) copy all files from temporary location where u had copy in A server. To ur directory in above steps.
3) go to command prompt.
set ORACLE_SID=TEST
create windows service through below command
ORADIM -new -sid babul92 -intpwd oracle -startmode manual
or
ORADIM -new -sid TEST -intpwd password -startmode auto -pfile
%ORACLE_HOME%\DATABASE\INITTEST.ORA
4) Start the above service. (if you mention with –startmode auto no need to start it manually It will start automatically after that command)
5) lsnrctl stop
6) add below entry into listener.ora (which u find in D:\oracle\ora92\network\admin)
(SID_DESC =
(GLOBAL_DBNAME = test)
(ORACLE_HOME = D:\oracle\ora92)
(SID_NAME = test)
)
7) lsnrctl start
8) Create test directory into D:\oracle\admin (where test is your instance name)
9) open D:\oracle\admin\test directory & create below directory in it.
bdump
cdump
create
pfile
udump
10) go to command prompt and enter sqlplus /nolog
CONNECT SYS/password AS SYSDBA
-- create the server parameter file
CREATE SPFILE='d:/oracle/ora92/database/spfilemynewdb.ora' FROM
PFILE='d:/oracle/ora92/database/initmynewdb.ora';
startup

Note : if you get error at time of startup you can create password file through below command
orapwd file=d:\oracle\ora92\database\pwdtest.ora password=oracle
Now if you wish to set oracle database should start automaticaly when ur service start (if u choose mannual in steps 3) then follow below steps.
1) Open Administration Assistant for Windows NT which u find in Oracle - OraHome92 -> Configuration and Migration Tools
2) Double click on Oracle Managed Objects then Computers then nikunj then Databases single click on your SID (TEST)
3) Right click on it and select Startup/Shutdown options.
4) check mark Start up instance when service is started.
orapwd file=d:\oracle\ora92\database\pwdtryguard.ora password=oracle
CONNECT SYS/password AS SYSDBA
-- create the server parameter file
CREATE SPFILE='d:/oracle/ora92/database/spfilemynewdb.ora' FROM
PFILE='d:/oracle/ora92/database/initmynewdb.ora';
SHUTDOWN

-- this time you will start up using the server parameter file
CONNECT SYS/password AS SYSDBA
STARTUP
EXIT
orapwd file=d:\oracle\ora92\database\pwdtryguard.ora password=oracle


IF database in different directory then
I have 2 Oracle Database EE 9.2 on Windows 2000 Server.
I had done below task on my A server.
CREATE PFILE=’d:/oracle/ora92/inittest.ora’ FROM SPFILE;
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
This will create control file statement in your server trace file (at last) i.e.
D:\oracle\admin\nikunj\udump
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "NIKUNJ" RESETLOGS ARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 'D:\ORACLE\ORADATA\NIKUNJ\REDO01.LOG' SIZE 100M,
GROUP 2 'D:\ORACLE\ORADATA\NIKUNJ\REDO02.LOG' SIZE 100M,
GROUP 3 'D:\ORACLE\ORADATA\NIKUNJ\REDO03.LOG' SIZE 100M
-- STANDBY LOGFILE
DATAFILE
'D:\ORACLE\ORADATA\NIKUNJ\SYSTEM01.DBF',
'D:\ORACLE\ORADATA\NIKUNJ\UNDOTBS01.DBF',
'D:\ORACLE\ORADATA\NIKUNJ\CWMLITE01.DBF',
'D:\ORACLE\ORADATA\NIKUNJ\DRSYS01.DBF',
'D:\ORACLE\ORADATA\NIKUNJ\EXAMPLE01.DBF',
'D:\ORACLE\ORADATA\NIKUNJ\INDX01.DBF',
'D:\ORACLE\ORADATA\NIKUNJ\ODM01.DBF',
'D:\ORACLE\ORADATA\NIKUNJ\TOOLS01.DBF',
'D:\ORACLE\ORADATA\NIKUNJ\USERS01.DBF',
'D:\ORACLE\ORADATA\NIKUNJ\XDB01.DBF'
CHARACTER SET WE8MSWIN1252
;
# Configure RMAN configuration record 1
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
# Configure RMAN configuration record 2
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''D:\oracle\orabackup\%F''');
# Configure RMAN configuration record 3
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE DISK FORMAT ''d:\oracle\orabackup\backup_db_%d_S_%s_P_%p_T_%t'' MAXPIECESIZE 250 M');
# Configure RMAN configuration record 4
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('MAXSETSIZE TO','UNLIMITED');
# Recovery is required if any of the datafiles are restored backups,
# or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
# Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
# Commands to add tempfiles to temporary tablespaces.
# Online tempfiles have complete space information.
# Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\ORACLE\ORADATA\NIKUNJ\TEMP01.DBF'
SIZE 41943040 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
# End of tempfile additions.
#
shutdown the database instance.
make a copy of oracle database files from oracle/ora92/oradata//*.* to temporary location including your PFILE so your database will not be down for more time.
I had done below taks on my B server.
1) create directory in oracle/ora92/oradata/ ie. Test
2) copy all files from temporary location where u had copy in A server. To ur directory in above steps.
3) go to command prompt.
set ORACLE_SID=TEST
create windows service through below command
ORADIM -new -sid babul92 -intpwd oracle -startmode manual
or
ORADIM -new -sid TEST -intpwd password -startmode auto -pfile
%ORACLE_HOME%\DATABASE\INITTEST.ORA
4) Start the above service. (if you mention with –startmode auto no need to start it manually It will start automatically after that command)
5) lsnrctl stop
6) add below entry into listener.ora (which u find in D:\oracle\ora92\network\admin)
(SID_DESC =
(GLOBAL_DBNAME = test)
(ORACLE_HOME = D:\oracle\ora92)
(SID_NAME = test)
)
7) lsnrctl start
8) Create test directory into D:\oracle\admin (where test is your instance name)
9) open D:\oracle\admin\test directory & create below directory in it.
bdump
cdump
create
pfile
udump
10) go to command prompt and enter sqlplus /nolog
1. CONNECT SYS/password AS SYSDBA
-- create the server parameter file
2. CREATE SPFILE='d:/oracle/ora92/database/spfilemynewdb.ora' FROM
PFILE='d:/oracle/ora92/database/initmynewdb.ora';
3. paste your edited control file create script.
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "NIKUNJ" RESETLOGS
ARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 'C:\ORACLE\ORADATA\NIKUNJ\REDO01.LOG' SIZE 100M,
GROUP 2 'C:\ORACLE\ORADATA\NIKUNJ\REDO02.LOG' SIZE 100M,
GROUP 3 'C:\ORACLE\ORADATA\NIKUNJ\REDO03.LOG' SIZE 100M
DATAFILE
'C:\ORACLE\ORADATA\NIKUNJ\SYSTEM01.DBF',
'C:\ORACLE\ORADATA\NIKUNJ\UNDOTBS01.DBF',
'C:\ORACLE\ORADATA\NIKUNJ\CWMLITE01.DBF',
'C:\ORACLE\ORADATA\NIKUNJ\DRSYS01.DBF',
'C:\ORACLE\ORADATA\NIKUNJ\EXAMPLE01.DBF',
'C:\ORACLE\ORADATA\NIKUNJ\INDX01.DBF',
'C:\ORACLE\ORADATA\NIKUNJ\ODM01.DBF',
'C:\ORACLE\ORADATA\NIKUNJ\TOOLS01.DBF',
'C:\ORACLE\ORADATA\NIKUNJ\USERS01.DBF',
'C:\ORACLE\ORADATA\NIKUNJ\XDB01.DBF'
CHARACTER SET WE8MSWIN1252
;
ALTER DATABASE OPEN RESETLOGS;
# Commands to add tempfiles to temporary tablespaces.
# Online tempfiles have complete space information.
# Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\ORACLE\ORADATA\NIKUNJ\TEMP01.DBF'
SIZE 41943040 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
Shutdown immediate;
Startup;

Covert Number to word for indian

create or replace function spell_indian_money
( p_number in number )
return varchar2
as
type myArray is table of varchar2(255);
l_str myArray := myArray( ' Thousand ',
' Lakh ',
' Crore ',
' Arab ',
' Kharab ',
' Shankh ' );
l_num varchar2(50) default trunc( p_number );
l_return varchar2(4000);
d_count number;
begin
if ( substr(l_num, length(l_num)-2, 3) <> 0 )
then
l_return := to_char(
to_date(
substr(l_num, length(l_num)-2, 3),
'J' ),
'Jsp' );
end if;
l_num := substr( l_num, 1, length(l_num)-3 );

for i in 1 .. l_str.count
loop
exit when l_num is null;
if ( substr(l_num, length(l_num)-1, 2) <> 0 )
then
l_return := to_char(
to_date(
substr(l_num, length(l_num)-1, 2),
'J' ),
'Jsp' ) || l_str(i) || l_return;
end if;
l_num := substr( l_num, 1, length(l_num)-2 );
end loop;

if to_char( p_number ) like '%.%'
then
l_num := substr( round( p_number, 2), instr( p_number, '.' )+1 );

/* here i had done my modifcation */
if (length(substr( round( p_number, 2), instr( p_number, '.' )+1)))=1 then
l_num:=to_number(to_char(l_num)||'0');
end if;
/* here i had completed my modifcation */
if l_num > 0
then
l_return := l_return || ' And '
|| to_char(
to_date(
l_num,
'J' ),
'Jsp' )
|| ' Paise';
end if;
end if;

return l_return||' Only.';
end spell_indian_money;
/

Oracle Database 10g Release 2 (10.2.0.1) Installation On RedHat Advanced Server 4.0

A brief guide to installing Oracle 10g Release 2 (10.2.0.1) on RedHat Advanced Server 4.0

Oracle Database 10g Release 2 (10.2.0.1) Installation On Fedora Core 4

A brief guide to installing Oracle 10g Release 2 (10.2.0.1) on Fedora Core 4.

Build Your Own Oracle RAC 10g Cluster on Linux and FireWire

by Jeffrey Hunter
Learn how to set up and configure an Oracle RAC 10g development cluster for less than US$1,800.

Oracle Database 10g Release 2 (10.2.0.1) Installation On RedHat Advanced Server 3.0 at Oracle-Base

A brief guide to installing Oracle 10g Release 2 (10.2.0.1) on RedHat Advanced Server 3.0.

My First Post

I had posted this to test my blogs.