If you want any software and automatic conversion to migrate non oracle database to oracle then you can use Oracle SQL Developer Migration Workbench which can be used to migrate Microsoft Access, Microsoft SQL Server, MySQL and Sybase databases to Oracle.
However you wish to load a table sample data from non-oracle to oracle database. You may wish to do the task manually. Below is the manual procedure by which you can load data to oracle. It explain also if you have data in a flat file then how you can can able to load it into your oracle database.
Though at first time it may seem to you a difficult one but in this post I will try to make it easy. The steps involved to copy SQL Server data to an oracle database is given below. However this procedure is also applied if you want to import data from an excel flat file to an oracle database.
Step 1: Export Data to a CSV file:
You have to proceed table by table if you want to copy data from SQL Server database to Oracle.
For each table data you have to export data to a flat file and convert it to a CSV file.
Don't bother with .CSV or name CSV extension. It is nothing just abbreviate form of Comma Separated Values. If you save a normal text file with the comma between the record parts then that file is a CSV file.
You can directly export data in a CSV file from SQL SERVER database or just export data to a flat file and then convert it to a CSV file.
Export data to a flat file is discussed in http://arjudba.blogspot.com/2008/05/how-to-export-data-to-flat-file.html. Now open this file with excel and from excel file you can easily convert to a CSV file. To do it just open the excel file and >click file manu and >select save as. A pop up window will be displayed. Go to Save as Type section and select .CSV extention and click on save button. You now have got the .CSV file and you have finished step 1.
It will be more easily if you can directly export data to a CSV format. Easily you can do by separating column value of the table with an extension.
Like, you want to copy or load emp table from sql server to oracle.
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMP_NO NUMBER
EMP_NAME VARCHAR2(10)
DEPT_NO NUMBER
SQL> select emp_no ||','||emp_name ||','||dept_no from emp;
EMP_NO||','||EMP_NAME||','||DEPT_NO
--------------------------------------------------------------------------------
1,ddd,10
2,aaa,11
3,bbb,10
Save the output to a emp.csv file.
Step 2: Create a Control file:
In this are emp.csv is called datafile where data of the table to be loaded exists. Never mix with datafile of oracle with this emp.csv. This one is SQL*Loader datafile and oracle datafile are of .dbf extension. After successfully creating data file create a control file. Also don't mix this control file with database control file. This control file instructs SQL*loader how to load data.
Here is the control file. In my other posts of my blog I will go detail with it.
LOAD DATA
INFILE '/export/home/oracle/emp.dat'
INTO TABLE emp
FIELDS TERMINATED BY ','
(emp_no CHAR(2), emp_name CHAR(10), dept_no CHAR(2))
Note that whether datatype is number or varchar2 in control file it is specified as CHAR.
I save the control file as emp.ctl
Step3: Go to oracle database and create the emp table.
I created as below.
CREATE TABLE ARJU.EMP
( EMP_NO NUMBER,
EMP_NAME VARCHAR2(10),
DEPT_NO NUMBER
)TABLESPACE USER_TBS;
Table created.
Step 4: Invoke SQL*Loader and load data.
Copy datafile, control file to the oracle database and invoke sqlldr to load data.
$sqlldr arju/a control=/export/home/oracle/emp.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on Sun Jul 20 06:21:28 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 3
Let's check logfile if any error.
bash-3.00$ cat emp.log
SQL*Loader: Release 10.2.0.1.0 - Production on Sun Jul 20 06:21:28 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Control File: /export/home/oracle/emp.ctl
Data File: /export/home/oracle/emp.dat
Bad File: /export/home/oracle/emp.bad
.
.
Total logical records skipped: 0
Total logical records read: 3
Total logical records rejected: 0
Total logical records discarded: 0
Step 5: See the data from database and You are done:
SQL> select * from emp;
EMP_NO EMP_NAME DEPT_NO
---------- ---------- ----------
1 ddd 10
2 aaa 11
3 bbb 10
Sunday, July 20, 2008
Numeric REMAINDER ROUND SIGN SIN SINH SQRT TAN TANH TRUNC function
1)REMAINDER
The function REMAINDER holds the syntax REMAINDER(n2,n1) and returns the remainder of n2 divided by n1. This function is similar to MOD function except that MOD uses FLOOR in its formula, whereas REMAINDER uses ROUND.
Example:
SQL> SELECT REMAINDER(11,3) FROM DUAL;
REMAINDER(11,3)
---------------
-1
SQL> SELECT MOD(11,3) FROM DUAL;
MOD(11,3)
----------
2
2)ROUND (number)
The function ROUND has the syntax ROUND({n}[,integer]).
It returns n rounded to integer places to the right of the decimal point. If you omit integer, then n is rounded to 0 places. The argument integer can be negative to round off digits left of the decimal point.
Example:SQL> SELECT ROUND(11.283,1) FROM DUAL;
ROUND(11.283,1)
---------------
11.3
SQL> SELECT ROUND(11.283,-1)FROM DUAL;
ROUND(11.283,-1)
----------------
10
SQL> SELECT ROUND(11.283)FROM DUAL;
ROUND(11.283)
-------------
11
3)SIGN
The SIGN function returns the sign of a numeric datatype or datatype that can be implicitly converted to numeric datatype.
In case of numeric datatype,
If value <0 argument="0">0 then SIGN function returns 1.
In case of binary float or binary double it returns -1 if n<0>=0 or n=NaN
SQL> SELECT SIGN(0f) FROM DUAL;
SIGN(0F)
----------
1
As 0f is float so SIGN returns 1.
SQL> SELECT SIGN(-11) FROM DUAL;
SIGN(-11)
----------
-1
SQL> SELECT SIGN(0) FROM DUAL;
SIGN(0)
----------
0
4)SIN
The function SIN takes a value in radians and returns the sine value of the argument.
To get SIN value of 30 degree,
SQL> SELECT SIN(30 * 3.14159265359/180) FROM DUAL;
SIN(30*3.14159265359/180)
-------------------------
.5
5)SINH
SINH returns the hyperbolic sine of n.
SQL> SELECT SINH(2) FROM DUAL;
SINH(2)
----------
3.62686041
6)SQRT
SQRT returns the square root of n.
SQL> SELECT SQRT(256) FROM DUAL;
SQRT(256)
----------
16
7)TAN
TAN returns the tangent of n where n is expressed in radians.
To get tangent of 45 degree,
SQL> SELECT TAN(45 * 3.14159265359/180) FROM DUAL;
TAN(45*3.14159265359/180)
-------------------------
1
8)TANH
TANH returns the hyperbolic tangent of n.
Example:
SQL> SELECT TANH(1) FROM DUAL;
TANH(1)
----------
.761594156
9)TRUNC (number)
TRUNC (number) holds the syntax TRUNC({n2}[,n1]) where {} indicates mandatory option and [] indicate optional option.
It returns n1 truncated to n2 decimal places. If n2 is omitted, then n1 is truncated to 0 places. n2 can be negative to truncate (make zero) n2 digits left of the decimal point.
Example:SQL> SELECT TRUNC(13.59,1) FROM DUAL;
TRUNC(13.59,1)
--------------
13.5
SQL> SELECT TRUNC(13.59,-1) FROM DUAL;
TRUNC(13.59,-1)
---------------
10
SQL> SELECT TRUNC(13.59,0) FROM DUAL;
TRUNC(13.59,0)
--------------
13
The function REMAINDER holds the syntax REMAINDER(n2,n1) and returns the remainder of n2 divided by n1. This function is similar to MOD function except that MOD uses FLOOR in its formula, whereas REMAINDER uses ROUND.
Example:
SQL> SELECT REMAINDER(11,3) FROM DUAL;
REMAINDER(11,3)
---------------
-1
SQL> SELECT MOD(11,3) FROM DUAL;
MOD(11,3)
----------
2
2)ROUND (number)
The function ROUND has the syntax ROUND({n}[,integer]).
It returns n rounded to integer places to the right of the decimal point. If you omit integer, then n is rounded to 0 places. The argument integer can be negative to round off digits left of the decimal point.
Example:SQL> SELECT ROUND(11.283,1) FROM DUAL;
ROUND(11.283,1)
---------------
11.3
SQL> SELECT ROUND(11.283,-1)FROM DUAL;
ROUND(11.283,-1)
----------------
10
SQL> SELECT ROUND(11.283)FROM DUAL;
ROUND(11.283)
-------------
11
3)SIGN
The SIGN function returns the sign of a numeric datatype or datatype that can be implicitly converted to numeric datatype.
In case of numeric datatype,
If value <0 argument="0">0 then SIGN function returns 1.
In case of binary float or binary double it returns -1 if n<0>=0 or n=NaN
SQL> SELECT SIGN(0f) FROM DUAL;
SIGN(0F)
----------
1
As 0f is float so SIGN returns 1.
SQL> SELECT SIGN(-11) FROM DUAL;
SIGN(-11)
----------
-1
SQL> SELECT SIGN(0) FROM DUAL;
SIGN(0)
----------
0
4)SIN
The function SIN takes a value in radians and returns the sine value of the argument.
To get SIN value of 30 degree,
SQL> SELECT SIN(30 * 3.14159265359/180) FROM DUAL;
SIN(30*3.14159265359/180)
-------------------------
.5
5)SINH
SINH returns the hyperbolic sine of n.
SQL> SELECT SINH(2) FROM DUAL;
SINH(2)
----------
3.62686041
6)SQRT
SQRT returns the square root of n.
SQL> SELECT SQRT(256) FROM DUAL;
SQRT(256)
----------
16
7)TAN
TAN returns the tangent of n where n is expressed in radians.
To get tangent of 45 degree,
SQL> SELECT TAN(45 * 3.14159265359/180) FROM DUAL;
TAN(45*3.14159265359/180)
-------------------------
1
8)TANH
TANH returns the hyperbolic tangent of n.
Example:
SQL> SELECT TANH(1) FROM DUAL;
TANH(1)
----------
.761594156
9)TRUNC (number)
TRUNC (number) holds the syntax TRUNC({n2}[,n1]) where {} indicates mandatory option and [] indicate optional option.
It returns n1 truncated to n2 decimal places. If n2 is omitted, then n1 is truncated to 0 places. n2 can be negative to truncate (make zero) n2 digits left of the decimal point.
Example:SQL> SELECT TRUNC(13.59,1) FROM DUAL;
TRUNC(13.59,1)
--------------
13.5
SQL> SELECT TRUNC(13.59,-1) FROM DUAL;
TRUNC(13.59,-1)
---------------
10
SQL> SELECT TRUNC(13.59,0) FROM DUAL;
TRUNC(13.59,0)
--------------
13
Numeric EXP FLOOR LN LOG MOD NANVL POWER functions
1)EXP
The EXP function holds the syntax EXP(n) and returns e raised to the nth power value of argument n.
The value of e is 2.71828183 ...
Example:
SQL> SELECT EXP(3) FROM DUAL;
EXP(3)
----------
20.0855369
Almost same as,
SQL> SELECT POWER(2.71828183,3) FROM DUAL;
POWER(2.71828183,3)
-------------------
20.085537
2)FLOOR
The function FLOOR hold the format FLOOR(n) and it returns largest integer equal to or less than n.
Example:
SQL> SELECT FLOOR(11.8) from dual;
FLOOR(11.8)
-----------
11
SQL> SELECT FLOOR(11.001) from dual;
FLOOR(11.001)
-------------
11
3)LN The function LN holds the syntax LN(n) and it returns the natural logarithm of n, where n>0.
SQL> select ln(2.71828183) from dual;
LN(2.71828183)
--------------
1
Which is similar to log(e,e) where e=2.71828183.
SQL> select log(2.71828183,2.71828183) from dual;
LOG(2.71828183,2.71828183)
--------------------------
1
4)LOG The function LOG use the syntax LOG(n2,n1) and returns the logarithm, base n2, of n1. The base n1 can be any positive value other than 0 or 1 and n2 can be any positive value.
Example:
SQL>Select log(3,9) from dual;
LOG(3,9)
----------
2
5)MOD The function MOD holds the syntax MOD(n2,n1) and it returns the remainder of n2 divided by n1. It returns n2 if n1=0 (zero ).
SQL> SELECT MOD(25,6) FROM DUAL;
MOD(25,6)
----------
1
SQL> SELECT MOD(0,2) FROM DUAL;
MOD(0,2)
----------
0
SQL> SELECT MOD(10,0) FROM DUAL;
MOD(10,0)
----------
10
6)NANVL The NANVL function holds the syntax NANVL(n2,n1) and this function is useful only for floating-point numbers of type BINARY_FLOAT or BINARY_DOUBLE. NaN indicates not a number. If the value stored in a table is not a number then this function instructs oracle to return alternative value n1 if the input value n2 is NaN (not a number). If n2 is not NaN, then Oracle returns n2.
The following example will clear you.
SQL> CREATE TABLE nan(a binary_float, b binary_double);
Table created.
SQL> insert into nan values('NaN','nan');
1 row created.
SQL> select * from nan;
A B
---------- ----------
Nan Nan
Now I wants if values are not a number then it will return zerop and for that you can use NANVL function.
SQL> SELECT NANVL(a,0), NANVL(b,0) from nan;
NANVL(A,0) NANVL(B,0)
---------- ----------
0 0
7)POWER The POWER function holds the syntax POWER(n2,n1). It returns n2 raised to the n1 power. The n2 is called base and n1 is the exponent. If n2 is negative, then n1 must be an integer.
Example:
SQL> SELECT POWER(-2,3) FROM DUAL;
POWER(-2,3)
-----------
-8
SQL> SELECT POWER(2,-3) FROM DUAL;
POWER(2,-3)
-----------
.125
The EXP function holds the syntax EXP(n) and returns e raised to the nth power value of argument n.
The value of e is 2.71828183 ...
Example:
SQL> SELECT EXP(3) FROM DUAL;
EXP(3)
----------
20.0855369
Almost same as,
SQL> SELECT POWER(2.71828183,3) FROM DUAL;
POWER(2.71828183,3)
-------------------
20.085537
2)FLOOR
The function FLOOR hold the format FLOOR(n) and it returns largest integer equal to or less than n.
Example:
SQL> SELECT FLOOR(11.8) from dual;
FLOOR(11.8)
-----------
11
SQL> SELECT FLOOR(11.001) from dual;
FLOOR(11.001)
-------------
11
3)LN The function LN holds the syntax LN(n) and it returns the natural logarithm of n, where n>0.
SQL> select ln(2.71828183) from dual;
LN(2.71828183)
--------------
1
Which is similar to log(e,e) where e=2.71828183.
SQL> select log(2.71828183,2.71828183) from dual;
LOG(2.71828183,2.71828183)
--------------------------
1
4)LOG The function LOG use the syntax LOG(n2,n1) and returns the logarithm, base n2, of n1. The base n1 can be any positive value other than 0 or 1 and n2 can be any positive value.
Example:
SQL>Select log(3,9) from dual;
LOG(3,9)
----------
2
5)MOD The function MOD holds the syntax MOD(n2,n1) and it returns the remainder of n2 divided by n1. It returns n2 if n1=0 (zero ).
SQL> SELECT MOD(25,6) FROM DUAL;
MOD(25,6)
----------
1
SQL> SELECT MOD(0,2) FROM DUAL;
MOD(0,2)
----------
0
SQL> SELECT MOD(10,0) FROM DUAL;
MOD(10,0)
----------
10
6)NANVL The NANVL function holds the syntax NANVL(n2,n1) and this function is useful only for floating-point numbers of type BINARY_FLOAT or BINARY_DOUBLE. NaN indicates not a number. If the value stored in a table is not a number then this function instructs oracle to return alternative value n1 if the input value n2 is NaN (not a number). If n2 is not NaN, then Oracle returns n2.
The following example will clear you.
SQL> CREATE TABLE nan(a binary_float, b binary_double);
Table created.
SQL> insert into nan values('NaN','nan');
1 row created.
SQL> select * from nan;
A B
---------- ----------
Nan Nan
Now I wants if values are not a number then it will return zerop and for that you can use NANVL function.
SQL> SELECT NANVL(a,0), NANVL(b,0) from nan;
NANVL(A,0) NANVL(B,0)
---------- ----------
0 0
7)POWER The POWER function holds the syntax POWER(n2,n1). It returns n2 raised to the n1 power. The n2 is called base and n1 is the exponent. If n2 is negative, then n1 must be an integer.
Example:
SQL> SELECT POWER(-2,3) FROM DUAL;
POWER(-2,3)
-----------
-8
SQL> SELECT POWER(2,-3) FROM DUAL;
POWER(2,-3)
-----------
.125
Saturday, July 19, 2008
Numeric ABS ACOS ASIN ATAN ATAN2 BITAND CEIL COS COSH functions
1)ABS
The ABS function takes any numeric datatype or any nonnumeric datatype (that can be implicitly converted to a numeric datatype) as an argument and return the absolute value of the datatype.
It takes only single value as argument.
Syntax:ABS(n)
Example:
SQL>Select ABS(-100) FROM DUAL;
ABS(-100)
----------
100
2)ACOS
We all know that cos60 degree=.5 and 180 degree=pi redian=3.1416 (approx). ACOS returns the arc cosine of n. n must be in the range of -1 to 1 as we know the value of cosine can very between +1 to -1. Here ACOS function returns a value in the range of 0 to pi, expressed in radians.
Example:
Here result will appear in redians (by default)
SQL> select acos(.5) from dual;
ACOS(.5)
----------
1.04719755
To get result in degree, (180 degree= pi redian)
SQL> select acos(.5)*180/3.1416 from dual;
ACOS(.5)*180/3.1416
-------------------
59.9998597
3)ASIN
ASIN returns the arc sine of n. It behaves just like ACOS. The argument n must be in the range of -1 to 1, and ASIN returns a value in the range of -pi/2 to pi/2, expressed in radians.
Example:
To get arc sine value of .5 in radians,
SQL> SELECT ASIN(.5) FROM DUAL;
ASIN(.5)
----------
.523598776
To get arc sine value of .5 in degrees,
SQL> SELECT ASIN(.5)*180/3.1416 FROM DUAL;
ASIN(.5)*180/3.1416
-------------------
29.9999298
4)ATAN:
ATAN returns the arc tangent of n. It behaves just like ASIN, ACOS. The argument passed to this function can be in an unbounded range and returns a value in the range of -pi/2 to pi/2, expressed in radians.
To get arc tangent of 1 in radians,
SQL> SELECT ATAN(1) FROM DUAL;
ATAN(1)
----------
.785398163
To get arc tangent of value .5 in degrees,
SQL> SELECT ATAN(1) *180/3.1416 FROM DUAL;
ATAN(1)*180/3.1416
------------------
44.9998948
5)ATAN2:
This functions takes two arguments and return arc tangent of two arguments. The argument can be passed as ATAN2(n1,n2) or ATAN2(n1/n2) and both are same. The argument n1 can be in an unbounded range and this function returns a value in the range of -pi to pi, depending on the signs of n1 and n2, expressed in radians.
Example:
SQL> SELECT ATAN2(.2,.1) FROM DUAL;
ATAN2(.2,.1)
------------
1.10714872
6)BITAND: BITAND function take two integer arguments and do an AND operation between them. Suppose if we want AND operation between 10 and 7 then it AND bit by bit which is
1010(10) and
0111(7) and result is
0010 (2)
SQL> SELECT BITAND(10,7) FROM DUAL;
BITAND(10,7)
------------
2
For 1(001) and 7(111) the result is 1 (001)
SQL> SELECT BITAND(1,7) FROM DUAL;
BITAND(1,7)
-----------
1
7)CEIL
CEIL returns smallest integer greater than or equal to the argument passed in it.
SQL> SELECT CEIL(1.8) FROM DUAL;
CEIL(1.8)
----------
2
SQL> SELECT CEIL(1.2) FROM DUAL;
CEIL(1.2)
----------
2
Since the smallest greater integer than 1.2 is 2.
SQL> SELECT CEIL(1.00) FROM DUAL;
CEIL(1.00)
----------
1
8)COS
The function COS takes single argument in radians in return consine of the value.
To get consine value of 60 degree,
SQL> SELECT COS(60*3.14159265359/180) FROM DUAL;
COS(60*3.14159265359/180)
-------------------------
.5
9)COSH
The COSH function takes a single numeric argument and returns the hyperbolic cosine of that value.
SQL> SELECT COSH(2) from dual;
COSH(2)
----------
3.76219569
The ABS function takes any numeric datatype or any nonnumeric datatype (that can be implicitly converted to a numeric datatype) as an argument and return the absolute value of the datatype.
It takes only single value as argument.
Syntax:ABS(n)
Example:
SQL>Select ABS(-100) FROM DUAL;
ABS(-100)
----------
100
2)ACOS
We all know that cos60 degree=.5 and 180 degree=pi redian=3.1416 (approx). ACOS returns the arc cosine of n. n must be in the range of -1 to 1 as we know the value of cosine can very between +1 to -1. Here ACOS function returns a value in the range of 0 to pi, expressed in radians.
Example:
Here result will appear in redians (by default)
SQL> select acos(.5) from dual;
ACOS(.5)
----------
1.04719755
To get result in degree, (180 degree= pi redian)
SQL> select acos(.5)*180/3.1416 from dual;
ACOS(.5)*180/3.1416
-------------------
59.9998597
3)ASIN
ASIN returns the arc sine of n. It behaves just like ACOS. The argument n must be in the range of -1 to 1, and ASIN returns a value in the range of -pi/2 to pi/2, expressed in radians.
Example:
To get arc sine value of .5 in radians,
SQL> SELECT ASIN(.5) FROM DUAL;
ASIN(.5)
----------
.523598776
To get arc sine value of .5 in degrees,
SQL> SELECT ASIN(.5)*180/3.1416 FROM DUAL;
ASIN(.5)*180/3.1416
-------------------
29.9999298
4)ATAN:
ATAN returns the arc tangent of n. It behaves just like ASIN, ACOS. The argument passed to this function can be in an unbounded range and returns a value in the range of -pi/2 to pi/2, expressed in radians.
To get arc tangent of 1 in radians,
SQL> SELECT ATAN(1) FROM DUAL;
ATAN(1)
----------
.785398163
To get arc tangent of value .5 in degrees,
SQL> SELECT ATAN(1) *180/3.1416 FROM DUAL;
ATAN(1)*180/3.1416
------------------
44.9998948
5)ATAN2:
This functions takes two arguments and return arc tangent of two arguments. The argument can be passed as ATAN2(n1,n2) or ATAN2(n1/n2) and both are same. The argument n1 can be in an unbounded range and this function returns a value in the range of -pi to pi, depending on the signs of n1 and n2, expressed in radians.
Example:
SQL> SELECT ATAN2(.2,.1) FROM DUAL;
ATAN2(.2,.1)
------------
1.10714872
6)BITAND: BITAND function take two integer arguments and do an AND operation between them. Suppose if we want AND operation between 10 and 7 then it AND bit by bit which is
1010(10) and
0111(7) and result is
0010 (2)
SQL> SELECT BITAND(10,7) FROM DUAL;
BITAND(10,7)
------------
2
For 1(001) and 7(111) the result is 1 (001)
SQL> SELECT BITAND(1,7) FROM DUAL;
BITAND(1,7)
-----------
1
7)CEIL
CEIL returns smallest integer greater than or equal to the argument passed in it.
SQL> SELECT CEIL(1.8) FROM DUAL;
CEIL(1.8)
----------
2
SQL> SELECT CEIL(1.2) FROM DUAL;
CEIL(1.2)
----------
2
Since the smallest greater integer than 1.2 is 2.
SQL> SELECT CEIL(1.00) FROM DUAL;
CEIL(1.00)
----------
1
8)COS
The function COS takes single argument in radians in return consine of the value.
To get consine value of 60 degree,
SQL> SELECT COS(60*3.14159265359/180) FROM DUAL;
COS(60*3.14159265359/180)
-------------------------
.5
9)COSH
The COSH function takes a single numeric argument and returns the hyperbolic cosine of that value.
SQL> SELECT COSH(2) from dual;
COSH(2)
----------
3.76219569
Database Startup fails with error ORA-16038,ORA-19809, ORA-00312
Error Description:
Whenever you try to startup the database it fails with error ORA-16038,ORA-19809, ORA-00312.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 117440552 bytes
Database Buffers 41943040 bytes
Redo Buffers 6369280 bytes
Database mounted.
ORA-16038: log 3 sequence# 572 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: '/oradata2/data1/dbase/redo03.log'
Or in mount stage whenever you try to open the database it fails with error ORA-16014, ORA-00312.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16014: log 3 sequence# 572 not archived, no available destinations
ORA-00312: online log 3 thread 1: '/oradata2/data1/dbase/redo03.log'
Cause of The Problem:
----------------------------------------
There was an attempt to archived the online log 3 but it could not archive the online log in the available archived log destination. The most common of happening the error is the archive log destination if full. You have flash recovery area configured and rman retention policy is failed to delete any archived or incremental backups and so can't archived new online log.
Solution of The Problem:
-------------------------------------------
Solution A:(Enough space on the drive)
1)One more check the alert log. (Not needed though)
An extra check you can do in alert log which is in background_dump_dest/alert_$ORACLE_SID.log
SQL> show parameter background_dump_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string /oracle/app/oracle/product/10.
2.0/db_1/admin/dbase/bdump
$less /oracle/app/oracle/product/10.2.0/db_1/admin/dbase/bdump/alert_dbase.log
You may see the same entry is in the alert log.
2)Check the archive destination.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 572
Next log sequence to archive 572
Current log sequence 580
So archived log destination is DB_RECOVERY_FILE_DEST. You can see the exact destination in OS by,
SQL> show parameter db_recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /oradata2/flash_recovery_area
db_recovery_file_dest_size big integer 10G
3)Increase the value of db_recovery_file_dest_size
As archive destination is full so increase the size.
SQL> alter system set db_recovery_file_dest_size=20G;
System altered.
4)Open the database now.
SQL> alter database open;
Database altered.
Solution B: Have not enough space on the drive
If you have not enough space in your disk and you have recent backup of your database and archive log is not needed then you can issue
$rman target /
RMAN>DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-2';
Solution C: Have not any backup
If you have not any recent backup then backup database to another location and delete archivelogs.
To do this,
$rman target /
RMAN>backup format '/oradata2/%U' archivelog all delete input database;
Solution D: Have recent backup and only need archivelog
In this case backup the archive log to another location and delete archive log from flash recovery area.
You can do this by,
$rman target /
RMAN> backup format '/oradata2/%U' archivelog all delete input;
Whenever you try to startup the database it fails with error ORA-16038,ORA-19809, ORA-00312.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 117440552 bytes
Database Buffers 41943040 bytes
Redo Buffers 6369280 bytes
Database mounted.
ORA-16038: log 3 sequence# 572 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 3 thread 1: '/oradata2/data1/dbase/redo03.log'
Or in mount stage whenever you try to open the database it fails with error ORA-16014, ORA-00312.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16014: log 3 sequence# 572 not archived, no available destinations
ORA-00312: online log 3 thread 1: '/oradata2/data1/dbase/redo03.log'
Cause of The Problem:
----------------------------------------
There was an attempt to archived the online log 3 but it could not archive the online log in the available archived log destination. The most common of happening the error is the archive log destination if full. You have flash recovery area configured and rman retention policy is failed to delete any archived or incremental backups and so can't archived new online log.
Solution of The Problem:
-------------------------------------------
Solution A:(Enough space on the drive)
1)One more check the alert log. (Not needed though)
An extra check you can do in alert log which is in background_dump_dest/alert_$ORACLE_SID.log
SQL> show parameter background_dump_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest string /oracle/app/oracle/product/10.
2.0/db_1/admin/dbase/bdump
$less /oracle/app/oracle/product/10.2.0/db_1/admin/dbase/bdump/alert_dbase.log
You may see the same entry is in the alert log.
2)Check the archive destination.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 572
Next log sequence to archive 572
Current log sequence 580
So archived log destination is DB_RECOVERY_FILE_DEST. You can see the exact destination in OS by,
SQL> show parameter db_recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /oradata2/flash_recovery_area
db_recovery_file_dest_size big integer 10G
3)Increase the value of db_recovery_file_dest_size
As archive destination is full so increase the size.
SQL> alter system set db_recovery_file_dest_size=20G;
System altered.
4)Open the database now.
SQL> alter database open;
Database altered.
Solution B: Have not enough space on the drive
If you have not enough space in your disk and you have recent backup of your database and archive log is not needed then you can issue
$rman target /
RMAN>DELETE ARCHIVELOG UNTIL TIME 'SYSDATE-2';
Solution C: Have not any backup
If you have not any recent backup then backup database to another location and delete archivelogs.
To do this,
$rman target /
RMAN>backup format '/oradata2/%U' archivelog all delete input database;
Solution D: Have recent backup and only need archivelog
In this case backup the archive log to another location and delete archive log from flash recovery area.
You can do this by,
$rman target /
RMAN> backup format '/oradata2/%U' archivelog all delete input;
Thursday, July 17, 2008
List of Object Reference and Model functions
List of Object Reference Functions
DEREF
MAKE_REF
REF
REFTOHEX
VALUE
List of Model Functions
CV
ITERATION_NUMBER
PRESENTNNV
PRESENTV
PREVIOUS
DEREF
MAKE_REF
REF
REFTOHEX
VALUE
List of Model Functions
CV
ITERATION_NUMBER
PRESENTNNV
PRESENTV
PREVIOUS
Related Documents
Types of SQL function in Oracle
List of Analytic functions in oracle
Here is the list of analytic functions in oracle as of available till 10.2g.
AVG
CORR
COVAR_POP
COVAR_SAMP
COUNT
CUME_DIST
DENSE_RANK
FIRST
FIRST_VALUE
LAG
LAST
LAST_VALUE
LEAD
MAX
MIN
NTILE
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC
RANK
RATIO_TO_REPORT
REGR_ (Linear Regression) Functions
ROW_NUMBER
STDDEV
STDDEV_POP
STDDEV_SAMP
SUM
VAR_POP
VAR_SAMP
VARIANCE
AVG
CORR
COVAR_POP
COVAR_SAMP
COUNT
CUME_DIST
DENSE_RANK
FIRST
FIRST_VALUE
LAG
LAST
LAST_VALUE
LEAD
MAX
MIN
NTILE
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC
RANK
RATIO_TO_REPORT
REGR_ (Linear Regression) Functions
ROW_NUMBER
STDDEV
STDDEV_POP
STDDEV_SAMP
SUM
VAR_POP
VAR_SAMP
VARIANCE
Subscribe to:
Posts (Atom)