Problem Description
Normal user can't connect to oracle database. Whenever they try to connect to database it fails with ORA-00019.
SQL> conn india/a
ERROR:
ORA-00019: maximum number of session licenses exceeded
Solution of The Problem
1)Log on to database as a DBA user.
SQL> conn arju/a
Connected.
2)Check the license_max_sessions parameter.
SQL> show parameter license_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
license_max_sessions integer 1
3)Increase the value of license_max_sessions parameter.
SQL> alter system set license_max_sessions=100;
System altered.
4)Test database to connect with the normal user.
SQL> conn india/a
Connected.
Thursday, July 31, 2008
ORA-00018, ORA-00020 maximum number of sessions exceeded
Error Description
Any operation or a new session creation is failed. Like,
ORA-00018: maximum number of sessions exceeded
ORA-00020: maximum number of processes (string) exceeded
In order to solve the problem you have to increase the value of the PROCESSES/ SESSIONS initialization parameter.
You can see the current settings of the SESSIONS and PROCESSES parameter value by querying from v$spparameter or by simply show parameter parameter_name.
SQL> col name format a30
SQL> col value format a10
SQL> select name, value from v$spparameter where name in ('processes','sessions');
NAME VALUE
------------------------------ ----------
processes 150
sessions
The explicitly value of the sessions parameter is not set and so it is left to blank. It's default value is derived from the processes parameter and value=1.1*process_parameter_value+5
You can calculate the default value of sessions parameter by,
SQL> select 1.1*value+5 "sessions par default value" from v$spparameter where name='processes';
sessions par default value
--------------------------
170
In order to change the value of the sessions you have to change it in spfile or pfile. Dynamically it can't be changed.
How to Solve the Problem
If your database start with spfile then,
Alter system set sessions=200 scope=spfile;
shutdown immediate;
startup;
If your database start with pfile then,
Open the pfile with an editor and edit the sessions parameter value and restart your database.
Similarly you can set the processes parameter.
Any operation or a new session creation is failed. Like,
ORA-00018: maximum number of sessions exceeded
ORA-00020: maximum number of processes (string) exceeded
In order to solve the problem you have to increase the value of the PROCESSES/ SESSIONS initialization parameter.
You can see the current settings of the SESSIONS and PROCESSES parameter value by querying from v$spparameter or by simply show parameter parameter_name.
SQL> col name format a30
SQL> col value format a10
SQL> select name, value from v$spparameter where name in ('processes','sessions');
NAME VALUE
------------------------------ ----------
processes 150
sessions
The explicitly value of the sessions parameter is not set and so it is left to blank. It's default value is derived from the processes parameter and value=1.1*process_parameter_value+5
You can calculate the default value of sessions parameter by,
SQL> select 1.1*value+5 "sessions par default value" from v$spparameter where name='processes';
sessions par default value
--------------------------
170
In order to change the value of the sessions you have to change it in spfile or pfile. Dynamically it can't be changed.
How to Solve the Problem
If your database start with spfile then,
Alter system set sessions=200 scope=spfile;
shutdown immediate;
startup;
If your database start with pfile then,
Open the pfile with an editor and edit the sessions parameter value and restart your database.
Similarly you can set the processes parameter.
Wednesday, July 30, 2008
ORA-01667: cannot add any more tablespaces: limit of exceeded
If your database version is higher than 8 suppose 10.2g then when MAXDATAFILES of your control file reached then MAXDATAFILES parameter would expand automatically unless you hit bug. You can see the post here,
New Feature of 10.2g Eliminate Control file Recreation
However if you are before 8 then while creating any tablespace and while adding a datafile to a tablespace you may get ORA-01667: cannot add any more tablespaces: limit of exceeded
How to Solve the Problem
Whenever you get ORA-01667 then your DB_FILES parameter have good settings and as you have older version of oracle , so your controlfile could not automatically expanded. So the solution is to either recreate the controlfile or recreate the database.
There is MAXDATAFILES parameter in the control file. Your total number of database datafiles exceed MAXDATAFILES limit. You have to enlarge it. Here is the procedure.
1)Take a text backup of the controlfile.
SQL> alter database backup controlfile to trace as '/oradata2/1.ctl'
2)Open the backup controlfile and edit it
$ vi /oradata2/1.ctl
MAXDATAFILES 100 --Edit it
-- Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
Delete all lines after this. You can press dG in vi editor to delete rest of lines.
In the text backup of controlfile you can see two versions of the controlfile. NORESETLOGS version and RESETLOGS version. Remove RESETLOGS version.
3)Save it and shutdown database.
SQL> shutdown abort;
4)Execute the saved controlfile script.
@/oradata2/1.ctl
Now you can add more datafile to your database up to MAXDATAFILES.
Related Documents
MAXDATAFILES , DB_FILES parameters and ORA-00059
How to Resize a Datafile
ORA-01667: cannot add any more tablespaces: limit of exceeded
New Feature of 10.2g: Eliminate Control File Re-Creation
When and How to Recreate the Controlfile
New Feature of 10.2g Eliminate Control file Recreation
However if you are before 8 then while creating any tablespace and while adding a datafile to a tablespace you may get ORA-01667: cannot add any more tablespaces: limit of exceeded
How to Solve the Problem
Whenever you get ORA-01667 then your DB_FILES parameter have good settings and as you have older version of oracle , so your controlfile could not automatically expanded. So the solution is to either recreate the controlfile or recreate the database.
There is MAXDATAFILES parameter in the control file. Your total number of database datafiles exceed MAXDATAFILES limit. You have to enlarge it. Here is the procedure.
1)Take a text backup of the controlfile.
SQL> alter database backup controlfile to trace as '/oradata2/1.ctl'
2)Open the backup controlfile and edit it
$ vi /oradata2/1.ctl
MAXDATAFILES 100 --Edit it
-- Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
Delete all lines after this. You can press dG in vi editor to delete rest of lines.
In the text backup of controlfile you can see two versions of the controlfile. NORESETLOGS version and RESETLOGS version. Remove RESETLOGS version.
3)Save it and shutdown database.
SQL> shutdown abort;
4)Execute the saved controlfile script.
@/oradata2/1.ctl
Now you can add more datafile to your database up to MAXDATAFILES.
Related Documents
MAXDATAFILES , DB_FILES parameters and ORA-00059
How to Resize a Datafile
ORA-01667: cannot add any more tablespaces: limit of exceeded
New Feature of 10.2g: Eliminate Control File Re-Creation
When and How to Recreate the Controlfile
How to determine OS block size for windows or unix
In many times you probably have heard about set the oracle db block size as a multiple of the OS block size. But whenever you ask how I can determine or find OS block size for windows or Unix then many one get stopped. In fact this question is OS related. In this post I will show how I can get OS block.
1)UNIX or Linux System.
On my Solaris Machine,
SQL> !perl -e '$a=(stat ".")[11]; print $a'
8192
On my debian Linux,
$ perl -e '$a=(stat ".")[11]; print $a'
4096
On my Solaris Machine another way,
SQL> !df -g /oracle
/oracle (/dev/dsk/c1d0s3 ): 8192 block size 1024 frag size
Where /oracle is disk partition.
Another example,
SQL> !df -g | grep 'block size'
/ (/dev/dsk/c1d0s0 ): 8192 block size 1024 frag size
/devices (/devices ): 512 block size 512 frag size
/system/contract (ctfs ): 512 block size 512 frag size
/proc (proc ): 512 block size 512 frag size
/etc/mnttab (mnttab ): 512 block size 512 frag size
/etc/svc/volatile (swap ): 4096 block size 4096 frag size
/system/object (objfs ): 512 block size 512 frag size
/lib/libc.so.1 (/usr/lib/libc/libc_hwcap1.so.1): 8192 block size 1024 frag size
/dev/fd (fd ): 1024 block size 1024 frag size
/tmp (swap ): 4096 block size 4096 frag size
/var/run (swap ): 4096 block size 4096 frag size
/oradata1 (/dev/dsk/c1d0s4 ): 8192 block size 1024 frag size
/oracle (/dev/dsk/c1d0s3 ): 8192 block size 1024 frag size
/oradata2 (/dev/dsk/c1d0s5 ): 8192 block size 1024 frag size
/export/home (/dev/dsk/c1d0s7 ): 8192 block size 1024 frag size
2)Windows Machine.
If you use ntfs file system you can use
fsutil fsinfo ntfsinfo drivename: to get information of block size.
c:\>fsutil fsinfo ntfsinfo f:
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 OS block size.
Bytes Per FileRecord Segment : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length : 0x000000000d72a000
Mft Start Lcn : 0x00000000000c0000
Mft2 Start Lcn : 0x00000000004a812c
Mft Zone Start : 0x0000000000800720
Mft Zone End : 0x000000000083b920
To get All possible size on windows machine use,
c:\ format /?
In the /A:size you will get the all possible size for both FAT and NTFS file system. Whenever you format a drive you can select default allocation unit as block size.
3)On HPUX issue like "vgdisplay -v /dev/vg01/lvol"
Related Documents
http://arjudba.blogspot.com/2008/06/choose-optimal-data-block-size-in.html
http://arjudba.blogspot.com/2008/06/advantages-and-disadvantages-of-using.html
1)UNIX or Linux System.
On my Solaris Machine,
SQL> !perl -e '$a=(stat ".")[11]; print $a'
8192
On my debian Linux,
$ perl -e '$a=(stat ".")[11]; print $a'
4096
On my Solaris Machine another way,
SQL> !df -g /oracle
/oracle (/dev/dsk/c1d0s3 ): 8192 block size 1024 frag size
Where /oracle is disk partition.
Another example,
SQL> !df -g | grep 'block size'
/ (/dev/dsk/c1d0s0 ): 8192 block size 1024 frag size
/devices (/devices ): 512 block size 512 frag size
/system/contract (ctfs ): 512 block size 512 frag size
/proc (proc ): 512 block size 512 frag size
/etc/mnttab (mnttab ): 512 block size 512 frag size
/etc/svc/volatile (swap ): 4096 block size 4096 frag size
/system/object (objfs ): 512 block size 512 frag size
/lib/libc.so.1 (/usr/lib/libc/libc_hwcap1.so.1): 8192 block size 1024 frag size
/dev/fd (fd ): 1024 block size 1024 frag size
/tmp (swap ): 4096 block size 4096 frag size
/var/run (swap ): 4096 block size 4096 frag size
/oradata1 (/dev/dsk/c1d0s4 ): 8192 block size 1024 frag size
/oracle (/dev/dsk/c1d0s3 ): 8192 block size 1024 frag size
/oradata2 (/dev/dsk/c1d0s5 ): 8192 block size 1024 frag size
/export/home (/dev/dsk/c1d0s7 ): 8192 block size 1024 frag size
2)Windows Machine.
If you use ntfs file system you can use
fsutil fsinfo ntfsinfo drivename: to get information of block size.
c:\>fsutil fsinfo ntfsinfo f:
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 OS block size.
Bytes Per FileRecord Segment : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length : 0x000000000d72a000
Mft Start Lcn : 0x00000000000c0000
Mft2 Start Lcn : 0x00000000004a812c
Mft Zone Start : 0x0000000000800720
Mft Zone End : 0x000000000083b920
To get All possible size on windows machine use,
c:\ format /?
In the /A:size you will get the all possible size for both FAT and NTFS file system. Whenever you format a drive you can select default allocation unit as block size.
3)On HPUX issue like "vgdisplay -v /dev/vg01/lvol"
Related Documents
http://arjudba.blogspot.com/2008/06/choose-optimal-data-block-size-in.html
http://arjudba.blogspot.com/2008/06/advantages-and-disadvantages-of-using.html
Tuesday, July 29, 2008
How to determine free memory and used memory in Oracle
The summary information of the SGA is determined by,
SQL> select * from V$SGA;
NAME VALUE
------------------------------------------------------------ ----------
Fixed Size 2073376
Variable Size 1056967904
Database Buffers 1509949440
Redo Buffers 14700544
To know the current settings of the sga dynamic components ( can be set in memory by ALTER SYSTEM)
SQL> col COMPONENT format a30
SQL> select COMPONENT, CURRENT_SIZE from V$SGA_DYNAMIC_COMPONENTS;
COMPONENT CURRENT_SIZE
------------------------------ ------------
shared pool 989855744
large pool 16777216
java pool 16777216
streams pool 33554432
DEFAULT buffer cache 603979776
KEEP buffer cache 0
RECYCLE buffer cache 0
DEFAULT 2K buffer cache 33554432
DEFAULT 4K buffer cache 637534208
DEFAULT 8K buffer cache 0
DEFAULT 16K buffer cache 67108864
COMPONENT CURRENT_SIZE
------------------------------ ------------
DEFAULT 32K buffer cache 167772160
ASM Buffer Cache 0
13 rows selected.
To know the details of free and used memory use,
col total_sga format a20
col used format a20
col free format a20
SQL> select round(sum(bytes)/1024/1024,2)||' MB' total_sga, round(round(sum(bytes)/1024/1024,2) - round(sum(decode(name,'free memory',bytes,0))/1024/1024,2))||' MB' used, round(sum(decode(name,'free memory',bytes,0))/1024/1024,2)||' MB' free from v$sgastat;
TOTAL_SGA USED FREE
-------------------- -------------------- --------------------
2608.11 MB 2382 MB 226.12 MB
Related Documents
Memory Usage of Solaris Operating System
SQL> select * from V$SGA;
NAME VALUE
------------------------------------------------------------ ----------
Fixed Size 2073376
Variable Size 1056967904
Database Buffers 1509949440
Redo Buffers 14700544
To know the current settings of the sga dynamic components ( can be set in memory by ALTER SYSTEM)
SQL> col COMPONENT format a30
SQL> select COMPONENT, CURRENT_SIZE from V$SGA_DYNAMIC_COMPONENTS;
COMPONENT CURRENT_SIZE
------------------------------ ------------
shared pool 989855744
large pool 16777216
java pool 16777216
streams pool 33554432
DEFAULT buffer cache 603979776
KEEP buffer cache 0
RECYCLE buffer cache 0
DEFAULT 2K buffer cache 33554432
DEFAULT 4K buffer cache 637534208
DEFAULT 8K buffer cache 0
DEFAULT 16K buffer cache 67108864
COMPONENT CURRENT_SIZE
------------------------------ ------------
DEFAULT 32K buffer cache 167772160
ASM Buffer Cache 0
13 rows selected.
To know the details of free and used memory use,
col total_sga format a20
col used format a20
col free format a20
SQL> select round(sum(bytes)/1024/1024,2)||' MB' total_sga, round(round(sum(bytes)/1024/1024,2) - round(sum(decode(name,'free memory',bytes,0))/1024/1024,2))||' MB' used, round(sum(decode(name,'free memory',bytes,0))/1024/1024,2)||' MB' free from v$sgastat;
TOTAL_SGA USED FREE
-------------------- -------------------- --------------------
2608.11 MB 2382 MB 226.12 MB
Related Documents
Memory Usage of Solaris Operating System
ORA-38760: This database instance failed to turn on flashback database ORA-38701 ORA-27037
Problem Description
Whenever you try to startup your database then it fails with ORA-38760.
SQL> startup
ORACLE instance started.
Total System Global Area 1161966444 bytes
Fixed Size 1787966 bytes
Variable Size 1145750568 bytes
Database Buffers 225163524 bytes
Redo Buffers 1262132 bytes
Database mounted.
ORA-38760: This database instance failed to turn on flashback database.
In the Alert log there goes entry like this.
Errors in file /oracle/admin/agprod/udump/agprod_ora_409616.trc:
ORA-38701: Flashback database log 402 seq 402 thread 1: "/DBarchive/flash_recovery_area/AGPROD/flashback/o1_mf_47cqbhb5_.flb"
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
The analogous of this error is during database running state whenever you delete the current flashbacklog, the database will crash with the following errors:
ORA-38701: Flashback database log 401 seq 401 thread 1:
"/DBarchive/flash_recovery_area/AGPROD/flashback/o1_mf_47cqbhb5_.flb"
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
Sat Apr 11 00:03:12 2006
RVWR: terminating instance due to error 38701
Instance terminated by RVWR, pid = 5721
Cause of The Problem
The above errors occurred due to a well identified bug. If there is any I/O error that is preventing write to the flashback logs, the instance will crash. Losing the current flashback log will cause the instance to crash. Also during database startup if flashback logs are inaccessible then instance will crush.
Solution of The Problem
Solution A)-Upgarde Database Version:
This bug is affected from database version 10.1.0.1 to 10.1.0.4
It is fixed in database version 10gR2. So upgrade your database version.
Solution B)-Disable Flashback and restart the instance.
1)If you don't have the situation to upgrade the database then start the database in mount stage.
SQL> startup mount;
2)Turn off the Flashback
SQL> alter database flashback off;
3)Turn on the Flashback
SQL> alter database flashback on;
4)Startup the database
SQL>alter database open;
Or, SQL>shutdown
SQL>startup
Note:
After disabling flashback if you have guaranteed restore point then you still might get the error ORA-38760: This database instance failed to turn on flashback database while starting up the database. In this case you have to drop the guaranteed restore point.
You can see the available guaranteed restore point by,
select NAME,SCN,GUARANTEE_FLASHBACK_DATABASE,DATABASE_INCARNATION# from v$restore_point;
Then drop the guaranteed restore point by,
DROP RESTORE POINT NAME_OF_THE_GUARANTEED_RESTORE_POINT;
Now start your database.
Related Documents
Restore Point and Flashback Database.
Can you use flashback database if a period of noarchivelog exists
Performing Flashback Database
Whenever you try to startup your database then it fails with ORA-38760.
SQL> startup
ORACLE instance started.
Total System Global Area 1161966444 bytes
Fixed Size 1787966 bytes
Variable Size 1145750568 bytes
Database Buffers 225163524 bytes
Redo Buffers 1262132 bytes
Database mounted.
ORA-38760: This database instance failed to turn on flashback database.
In the Alert log there goes entry like this.
Errors in file /oracle/admin/agprod/udump/agprod_ora_409616.trc:
ORA-38701: Flashback database log 402 seq 402 thread 1: "/DBarchive/flash_recovery_area/AGPROD/flashback/o1_mf_47cqbhb5_.flb"
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
The analogous of this error is during database running state whenever you delete the current flashbacklog, the database will crash with the following errors:
ORA-38701: Flashback database log 401 seq 401 thread 1:
"/DBarchive/flash_recovery_area/AGPROD/flashback/o1_mf_47cqbhb5_.flb"
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3
Sat Apr 11 00:03:12 2006
RVWR: terminating instance due to error 38701
Instance terminated by RVWR, pid = 5721
Cause of The Problem
The above errors occurred due to a well identified bug. If there is any I/O error that is preventing write to the flashback logs, the instance will crash. Losing the current flashback log will cause the instance to crash. Also during database startup if flashback logs are inaccessible then instance will crush.
Solution of The Problem
Solution A)-Upgarde Database Version:
This bug is affected from database version 10.1.0.1 to 10.1.0.4
It is fixed in database version 10gR2. So upgrade your database version.
Solution B)-Disable Flashback and restart the instance.
1)If you don't have the situation to upgrade the database then start the database in mount stage.
SQL> startup mount;
2)Turn off the Flashback
SQL> alter database flashback off;
3)Turn on the Flashback
SQL> alter database flashback on;
4)Startup the database
SQL>alter database open;
Or, SQL>shutdown
SQL>startup
Note:
After disabling flashback if you have guaranteed restore point then you still might get the error ORA-38760: This database instance failed to turn on flashback database while starting up the database. In this case you have to drop the guaranteed restore point.
You can see the available guaranteed restore point by,
select NAME,SCN,GUARANTEE_FLASHBACK_DATABASE,DATABASE_INCARNATION# from v$restore_point;
Then drop the guaranteed restore point by,
DROP RESTORE POINT NAME_OF_THE_GUARANTEED_RESTORE_POINT;
Now start your database.
Related Documents
Restore Point and Flashback Database.
Can you use flashback database if a period of noarchivelog exists
Performing Flashback Database
Monday, July 28, 2008
Classes of Oracle Wait Events
Every oracle wait event belongs to a class of wait event. Here is the list of classes of oracle wait events.
1)Administrative: DBA commands cause users to wait. Example: index rebuild
2)Application: User application codes cause to wait. Example: row level lock
3)Cluster: Waits related to Real Application Cluster resources. Example: gc cr block busy
4)Commit: After issuing commit wait for redo log write confirmation. Example: log file sync
5)Concurrency: Waits for internal database resources. Example: Latch wait.
6)Configuration: Waits caused by inadequate configuration of database or instance resources Example: Undersized log file size, undersized shared pool size.
7)Idle: Waits that signify the session is inactive, that is session waiting for work. Example: SQL*Net message from client.
8)Network: Waits related to network messaging. Example: SQL*Net more data to dblink.
9)Other: Waits which should not typically occur on a system. Example:wait for EMON to spawn.
10)Scheduler: Resource Manager related waits. Example: resmgr: become active.
11)System I/O: Waits for background process IO. Example: DBWR waits for 'db file parallel write'.
12)User I/O: Waits for user IO. Example: 'db file sequential read'
Related Documents
1)Administrative: DBA commands cause users to wait. Example: index rebuild
2)Application: User application codes cause to wait. Example: row level lock
3)Cluster: Waits related to Real Application Cluster resources. Example: gc cr block busy
4)Commit: After issuing commit wait for redo log write confirmation. Example: log file sync
5)Concurrency: Waits for internal database resources. Example: Latch wait.
6)Configuration: Waits caused by inadequate configuration of database or instance resources Example: Undersized log file size, undersized shared pool size.
7)Idle: Waits that signify the session is inactive, that is session waiting for work. Example: SQL*Net message from client.
8)Network: Waits related to network messaging. Example: SQL*Net more data to dblink.
9)Other: Waits which should not typically occur on a system. Example:wait for EMON to spawn.
10)Scheduler: Resource Manager related waits. Example: resmgr: become active.
11)System I/O: Waits for background process IO. Example: DBWR waits for 'db file parallel write'.
12)User I/O: Waits for user IO. Example: 'db file sequential read'
Related Documents
New features in Oracle database administration in 11g
DDL with the WAIT option in 11g -DDL_LOCK_TIMEOUT
DDL with the WAIT option in 11g -DDL_LOCK_TIMEOUT
Available Views for Oracle Wait Events
There are three dynamic performance views by which we can get information about the oracle wait events. Each of the wait events belong to a wait class.
The three dynamic performance views are,
1)V$SESSION_WAIT: This dynamic performance view displays the events for which sessions have just completed waiting or are currently waiting.
2)V$SYSTEM_EVENT: This view displays the total number of times all the sessions have waited for the events.
3)V$SESSION_EVENT: This view displays all waits for each session.
The list of all oracle wait events and their associated wait class can be found by querying,
SQL> SELECT name, wait_class FROM V$EVENT_NAME ORDER BY name;
The three dynamic performance views are,
1)V$SESSION_WAIT: This dynamic performance view displays the events for which sessions have just completed waiting or are currently waiting.
2)V$SYSTEM_EVENT: This view displays the total number of times all the sessions have waited for the events.
3)V$SESSION_EVENT: This view displays all waits for each session.
The list of all oracle wait events and their associated wait class can be found by querying,
SQL> SELECT name, wait_class FROM V$EVENT_NAME ORDER BY name;
Connect to database fails with ORA-09817, ORA-01075
Normal user could not connect to your database and whenever you try to connect to your database with user having SYSDBA privilege it returns error ORA-09817, ORA-01075.
-bash-3.00$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 28 04:14:44 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-09817: Write to audit file failed.
Solaris-AMD64 Error: 28: No space left on device
ORA-01075: you are currently logged on
Cause of the Error: Database could not write to audit file because of Solaris-AMD64 Error: 28.No space left on device. In the partition where audit file resides has no free space.
Solution of The Problem:
The only solution is make more free space in the partition where audit file resides. You can see the audit file destination in the pfile parameter audit_file_dest. By default the location is
$ORACLE_HOME/admin/$ORACLE_SID/adump.
First you can check the available space in your partition by,
-bash-3.00$ df -h
/dev/dsk/c1d0s3 24G 24G 0K 100% /oracle
Now delete the unnecessary files in the partition.
Like rm -r /oracle/test
-bash-3.00$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 28 04:14:44 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-09817: Write to audit file failed.
Solaris-AMD64 Error: 28: No space left on device
ORA-01075: you are currently logged on
Cause of the Error: Database could not write to audit file because of Solaris-AMD64 Error: 28.No space left on device. In the partition where audit file resides has no free space.
Solution of The Problem:
The only solution is make more free space in the partition where audit file resides. You can see the audit file destination in the pfile parameter audit_file_dest. By default the location is
$ORACLE_HOME/admin/$ORACLE_SID/adump.
First you can check the available space in your partition by,
-bash-3.00$ df -h
/dev/dsk/c1d0s3 24G 24G 0K 100% /oracle
Now delete the unnecessary files in the partition.
Like rm -r /oracle/test
Sunday, July 27, 2008
How to setup Wireless Network Connection on Windows XP
Before proceed note that your OS is determine the wireless networks. That is you will see an unplugged icon in the wireless network connections. If windows can't detect your wireless driver then you need to install the driver of the wireless adaptor.
1)Go to control panel and then click Network Connections.
2)If you have driver setup either built in OS or from third party software then you will see Wireless Network Connection icon. Right Click on the Wireless Network Connection then and click Status
3)If there is no received data then click on View Wireless Network Connection.
4)AT this stage you need to be cautious. If you click Refresh Network list then no connection will be shown if other third party software is used to configure your wireless other than windows.
5)If you want other software then don't use windows one. However if you want to let windows to configure your wireless connection and after refresh network list no list is shown then Click Change Advanced Settings.
6)Select Wireless Networks Tab.
7)Just check the box "Use Windows to configure my wireless network settings."
8)Click ok.
9)Now click Refresh Network list and you will see the available connection.
10)Select your desired network and connect to wireless network.
Related Documents
http://arjudba.blogspot.com/2009/03/how-to-get-gui-from-windows-to.html
http://arjudba.blogspot.com/2009/01/copy-files-between-unix-and-windows.html
http://arjudba.blogspot.com/2008/10/who-is-connected-to-your-windows.html
http://arjudba.blogspot.com/2008/10/open-application-using-keyboard.html
http://arjudba.blogspot.com/2009/03/how-to-get-gui-from-windows-to.html
http://arjudba.blogspot.com/2009/01/copy-files-between-unix-and-windows.html
http://arjudba.blogspot.com/2008/10/who-is-connected-to-your-windows.html
http://arjudba.blogspot.com/2008/10/open-application-using-keyboard.html
Saturday, July 26, 2008
Create the Data Dictionary in Oracle
•The data dictionary tables are the first objects created in Oracle database.
•They must be created and resided in SYSTEM tablespace.
•Whenever you create your database using dbca (Database Configuration Assistant) Oracle automatically creates the data dictionary.
•But if you create database manually which is described as in Manually Create an Oracle Database then to build or create data dictionary objects, you must run several scripts, while connecting to database as a user with SYSDBA privileges.
Here a list of scripts that you need to run to build data dictionary objects. You can get and run these scripts from $ORACLE_HOME/rdbms/admin/
1)catalog.sql: Creates the data dictionary and public synonyms for many of its views. It also grants PUBLIC access to the synonyms.
2)catproc.sql: This script builds all data dictionary objects required for PL/SQL.
3)catclust.sql: This script creates Real Application Clusters data dictionary views.
To know the additional scripts just have a look at,
Addiional Data Dictionary Scripts
•They must be created and resided in SYSTEM tablespace.
•Whenever you create your database using dbca (Database Configuration Assistant) Oracle automatically creates the data dictionary.
•But if you create database manually which is described as in Manually Create an Oracle Database then to build or create data dictionary objects, you must run several scripts, while connecting to database as a user with SYSDBA privileges.
Here a list of scripts that you need to run to build data dictionary objects. You can get and run these scripts from $ORACLE_HOME/rdbms/admin/
1)catalog.sql: Creates the data dictionary and public synonyms for many of its views. It also grants PUBLIC access to the synonyms.
2)catproc.sql: This script builds all data dictionary objects required for PL/SQL.
3)catclust.sql: This script creates Real Application Clusters data dictionary views.
To know the additional scripts just have a look at,
Addiional Data Dictionary Scripts
Tuesday, July 22, 2008
Process and Runtime Limits
1)Instances per database
Maximum number of cluster database instances per database: Operating system-dependent
2)Locks
Row-level: Unlimited
Distributed Lock Manager: Operating system dependent
3)SGA size
Maximum value: Operating system-dependent;
For 32 bit OS, 2GB - 4GB.
For 64 bit OS, >4GB.
4)Advanced Queuing Processes
Maximum per instance: 10
5)Job Queue Processes
Maximum per instance: 1000
6)I/O Slave Processes
Maximum per background process (DBWR, LGWR, ....): 15
Maximum per Backup session: 15
7)Sessions
Maximum per instance: 32 KB; Restricted by the PROCESSES and SESSIONS initialization parameters.
8)Global Cache Service Processes
Maximum per instance: 10
9)Shared Servers
Maximum per instance: Unlimited. Restricted by the PROCESSES and SESSIONS initialization parameters.
10)Dispatchers
Maximum per instance: Unlimited. Restricted by the PROCESSES and SESSIONS initialization parameters.
11)Parallel Execution Slaves
Maximum per instance: Unlimited. Restricted by the PROCESSES and SESSIONS initialization parameters.
12)Backup Sessions
Maximum per instance: Unlimited. Restricted by the PROCESSES and SESSIONS initialization parameters.
Related Document
Physical Database Limits in oracle
Datatype Limits in Oracle
Logical Database Limits in Oracle
Maximum number of cluster database instances per database: Operating system-dependent
2)Locks
Row-level: Unlimited
Distributed Lock Manager: Operating system dependent
3)SGA size
Maximum value: Operating system-dependent;
For 32 bit OS, 2GB - 4GB.
For 64 bit OS, >4GB.
4)Advanced Queuing Processes
Maximum per instance: 10
5)Job Queue Processes
Maximum per instance: 1000
6)I/O Slave Processes
Maximum per background process (DBWR, LGWR, ....): 15
Maximum per Backup session: 15
7)Sessions
Maximum per instance: 32 KB; Restricted by the PROCESSES and SESSIONS initialization parameters.
8)Global Cache Service Processes
Maximum per instance: 10
9)Shared Servers
Maximum per instance: Unlimited. Restricted by the PROCESSES and SESSIONS initialization parameters.
10)Dispatchers
Maximum per instance: Unlimited. Restricted by the PROCESSES and SESSIONS initialization parameters.
11)Parallel Execution Slaves
Maximum per instance: Unlimited. Restricted by the PROCESSES and SESSIONS initialization parameters.
12)Backup Sessions
Maximum per instance: Unlimited. Restricted by the PROCESSES and SESSIONS initialization parameters.
Related Document
Physical Database Limits in oracle
Datatype Limits in Oracle
Logical Database Limits in Oracle
Logical Database Limits in Oracle
1)CREATE MATERIALIZED VIEW definition size Limit
Maximum size: 64K Bytes
2)GROUP BY clause size Limit
Maximum length: Must fit within a single database block.
3)Indexes Limit
Maximum per table: Unlimited
Total size of indexed column: 75% of the database block size minus some overhead
4)Columns Limit
Maximum Per table: 1000
Maximum Per index (or clustered index): 32
Maximum Per bitmapped index: 30
5)Constraints
Maximum per column: Unlimited
6)Subqueries
Maximum levels of subqueries in a SQL statement in the FORM clause of top-level query: Unlimited
Maximum levels of subqueries in a SQL statement in the WHERE clause: 255
7)Partitions
Maximum length of linear partitioning key: 4 KB - overhead
Maximum number of columns in partition key: 16 columns
Maximum number of partitions allowed per table or index: 1024K - 1
8)Rows
Maximum number per table: Unlimited
9)Stored Packages
Maximum size: Limits typically range from 2000 to 3000 lines of code.
10)Trigger Cascade Limit
Maximum value: Operating system-dependent, typically 32
11)Users and Roles
Maximum: 2,147,483,638
12)Tables
Maximum per clustered table: 32 tables
Maximum per database: Unlimited
Related Documents
Physical Database Limits in oracle
Datatype Limits in Oracle
Maximum size: 64K Bytes
2)GROUP BY clause size Limit
Maximum length: Must fit within a single database block.
3)Indexes Limit
Maximum per table: Unlimited
Total size of indexed column: 75% of the database block size minus some overhead
4)Columns Limit
Maximum Per table: 1000
Maximum Per index (or clustered index): 32
Maximum Per bitmapped index: 30
5)Constraints
Maximum per column: Unlimited
6)Subqueries
Maximum levels of subqueries in a SQL statement in the FORM clause of top-level query: Unlimited
Maximum levels of subqueries in a SQL statement in the WHERE clause: 255
7)Partitions
Maximum length of linear partitioning key: 4 KB - overhead
Maximum number of columns in partition key: 16 columns
Maximum number of partitions allowed per table or index: 1024K - 1
8)Rows
Maximum number per table: Unlimited
9)Stored Packages
Maximum size: Limits typically range from 2000 to 3000 lines of code.
10)Trigger Cascade Limit
Maximum value: Operating system-dependent, typically 32
11)Users and Roles
Maximum: 2,147,483,638
12)Tables
Maximum per clustered table: 32 tables
Maximum per database: Unlimited
Related Documents
Physical Database Limits in oracle
Datatype Limits in Oracle
Physical Database Limits in oracle
1)Data Block Size Limit
Minimum: 2048 bytes; must be a multiple of operating system physical block size
Maximum: Operating system dependent; Not more than 32 KB
2)Data Blocks Limit
Minimum in initial extent of a segment: 2 data blocks
Maximum per datafile: Platform dependent; typically power(2,22) - 1 blocks
3)Controlfiles Limit
Number of control files: 1 minimum; 2 or more (on separate devices) strongly recommended.
Size of a control file: Maximum of 20,000 * (database block size)
4)Datafiles Limit
Maximum per tablespace: 1022
Maximum per database: 65533
Also, limited by the DB_FILES initialization parameter for a particular instance.
5)Database extents Limit
Maximum per dictionary managed tablespace: 4 GB * physical block size.
Maximum per locally managed (uniform) tablespace: 2 GB * physical block size.
6)Database file size Limit
Limited by maximum operating system file size.
7)MAXEXTENTS Limit
Default value: Derived from tablespace default storage or DB_BLOCK_SIZE initialization parameter
Maximum: Unlimited
8)Redo Log Files Limit
Maximum number of logfiles: Limited by value of MAXLOGFILES parameter in the CREATE DATABASE statement.
Maximum number of logfiles per group: Unlimited
9)Redo Log File Size Limit
Minimum size: 50 KB
Maximum size: Operating system limit.
10)Tablespaces:
Maximum number per database: 65533
11)Bigfile Tablespaces
Number of blocks: power(2,32)
12)Smallfile Tablespaces:
Number of blocks: power(2,22)
13)External Tables file
Maximum size: OS dependent.
Minimum: 2048 bytes; must be a multiple of operating system physical block size
Maximum: Operating system dependent; Not more than 32 KB
2)Data Blocks Limit
Minimum in initial extent of a segment: 2 data blocks
Maximum per datafile: Platform dependent; typically power(2,22) - 1 blocks
3)Controlfiles Limit
Number of control files: 1 minimum; 2 or more (on separate devices) strongly recommended.
Size of a control file: Maximum of 20,000 * (database block size)
4)Datafiles Limit
Maximum per tablespace: 1022
Maximum per database: 65533
Also, limited by the DB_FILES initialization parameter for a particular instance.
5)Database extents Limit
Maximum per dictionary managed tablespace: 4 GB * physical block size.
Maximum per locally managed (uniform) tablespace: 2 GB * physical block size.
6)Database file size Limit
Limited by maximum operating system file size.
7)MAXEXTENTS Limit
Default value: Derived from tablespace default storage or DB_BLOCK_SIZE initialization parameter
Maximum: Unlimited
8)Redo Log Files Limit
Maximum number of logfiles: Limited by value of MAXLOGFILES parameter in the CREATE DATABASE statement.
Maximum number of logfiles per group: Unlimited
9)Redo Log File Size Limit
Minimum size: 50 KB
Maximum size: Operating system limit.
10)Tablespaces:
Maximum number per database: 65533
11)Bigfile Tablespaces
Number of blocks: power(2,32)
12)Smallfile Tablespaces:
Number of blocks: power(2,22)
13)External Tables file
Maximum size: OS dependent.
Char, Varchar2, Long etc Datatype Limits in Oracle
1)BFILE:
Maximum size: 4 GB-1 which is power(2,32)-1 bytes.
Maximum size of the file name: 255 characters
Maximum size of the directory name: 30 characters
Maximum number of open BFILEs: Limited by the value of the SESSION_MAX_OPEN_FILES initialization parameter, which itself is limited by the maximum number of open files the OS will allow.
2)BLOB:
Maximum size: (4 GB - 1) * DB_BLOCK_SIZE initialization parameter/ LOB Chunk size.
So, if db_block_size=8024K then maximum size=32T
SQL> select 4*1024*1024*1024*8*1024/1024/1024/1024/1024 from dual;
4*1024*1024*1024*8*1024/1024/1024/1024/1024
-------------------------------------------
32
As database block size vary from 2K to 32K so BLOB size can vary from 8TB to 128TB.
(8 TB to 128 TB)
Number of LOB columns per table: Limited by the maximum number of columns per table where maximum can be 1000.
3)CHAR:
Maximum size: 2000 bytes
Minimum and Default Size: 1 byte
4)CHAR VARYING
Maximum size: 4000 bytes
5)CLOB
Same as BLOB in the range of 4T to 128T.
6)Literals (characters or numbers in SQL or PL/SQL)
Maximum size: 4000 characters
7)LONG
Maximum size: 2 GB - 1
8)NCHAR
Maximum size: 2000 bytes
9)NCHAR VARYING
Maximum size: 4000 bytes
10)NCLOB
Same as BLOB in the range of 4T to 128T
11)NUMBER
Maximum size: 999...(in this way 38 9s) * power(10,125)
Minimum size: -999...(in this way 38 9s) *power(10,125)
12)RAW
Maximum size: 2000 bytes
13)VARCHAR
Maximum size: 4000 bytes
Minimum size: 1 byte.
14)VARCHAR2
Maximum size: 4000 bytes
Minimum size: 1 byte.
Maximum size: 4 GB-1 which is power(2,32)-1 bytes.
Maximum size of the file name: 255 characters
Maximum size of the directory name: 30 characters
Maximum number of open BFILEs: Limited by the value of the SESSION_MAX_OPEN_FILES initialization parameter, which itself is limited by the maximum number of open files the OS will allow.
2)BLOB:
Maximum size: (4 GB - 1) * DB_BLOCK_SIZE initialization parameter/ LOB Chunk size.
So, if db_block_size=8024K then maximum size=32T
SQL> select 4*1024*1024*1024*8*1024/1024/1024/1024/1024 from dual;
4*1024*1024*1024*8*1024/1024/1024/1024/1024
-------------------------------------------
32
As database block size vary from 2K to 32K so BLOB size can vary from 8TB to 128TB.
(8 TB to 128 TB)
Number of LOB columns per table: Limited by the maximum number of columns per table where maximum can be 1000.
3)CHAR:
Maximum size: 2000 bytes
Minimum and Default Size: 1 byte
4)CHAR VARYING
Maximum size: 4000 bytes
5)CLOB
Same as BLOB in the range of 4T to 128T.
6)Literals (characters or numbers in SQL or PL/SQL)
Maximum size: 4000 characters
7)LONG
Maximum size: 2 GB - 1
8)NCHAR
Maximum size: 2000 bytes
9)NCHAR VARYING
Maximum size: 4000 bytes
10)NCLOB
Same as BLOB in the range of 4T to 128T
11)NUMBER
Maximum size: 999...(in this way 38 9s) * power(10,125)
Minimum size: -999...(in this way 38 9s) *power(10,125)
12)RAW
Maximum size: 2000 bytes
13)VARCHAR
Maximum size: 4000 bytes
Minimum size: 1 byte.
14)VARCHAR2
Maximum size: 4000 bytes
Minimum size: 1 byte.
Monday, July 21, 2008
Benefits and Considerations of Using Bigfile tablespaces
Benefits of Bigfile Tablespaces
•In a database there can have maximum 65533 data files. Database is limited by the datafiles to 65533. In other way we can say in a database there can have maximum 65533 tablespaces because each tablespace must include at least one file.
Smallfile tablespace can contain up to 1024 files, but bigfile tablespaces contain only single file that can be 1024 times larger than a smallfile tablespace. Since database is limited by the total tablespace 65533 or in other word by the total data files 65533 and since bigfile tablespace's datafile can be 1024 times larger than of smallfile tablespace maximum size of database can largely expanded if you use bigfile tablespace.
The detail calculation of overall database size with the bigfile tablespace and smallfile tablespace are discussed in Overall Database Size
•With the use of bigfile tablespace you can reduce the number of datafiles of a ultra large tablespace and thus we can manage the database well. You can also adjust parameters to reduce the SGA space required for datafile information and the size of the control file.
•If you use Oracle-managed files to name the datafile, bigfile tablespaces make datafiles completely transparent for users as bigfile tablespaces contain only one datafile.
Considerations with Bigfile Tablespaces
•We should consider to use bigfile tablespace with Automatic Storage Management or other logical volume managers that support dynamically extensible logical volumes and striping or RAID.
•We should not use bigfile tablespace on a system that does not support striping because of negative implications for parallel execution and RMAN backup parallelization.
•We should not use bigfile tablespace if there is free space problem on a disk group, and the only way to extend a tablespace is to add a new datafile on a different disk group.
•We should not use bigfile tablespace on the system that impose limit to large file sizes.
•Overall, performance of database opens, checkpoints, and DBWR processes should improve if data is stored in bigfile tablespaces instead of smallfile tablespaces. Though in case of corrupted datafile restore time of the datafile is more than of smallfile tablespace case.
Related Documents
Overall Database Size
•In a database there can have maximum 65533 data files. Database is limited by the datafiles to 65533. In other way we can say in a database there can have maximum 65533 tablespaces because each tablespace must include at least one file.
Smallfile tablespace can contain up to 1024 files, but bigfile tablespaces contain only single file that can be 1024 times larger than a smallfile tablespace. Since database is limited by the total tablespace 65533 or in other word by the total data files 65533 and since bigfile tablespace's datafile can be 1024 times larger than of smallfile tablespace maximum size of database can largely expanded if you use bigfile tablespace.
The detail calculation of overall database size with the bigfile tablespace and smallfile tablespace are discussed in Overall Database Size
•With the use of bigfile tablespace you can reduce the number of datafiles of a ultra large tablespace and thus we can manage the database well. You can also adjust parameters to reduce the SGA space required for datafile information and the size of the control file.
•If you use Oracle-managed files to name the datafile, bigfile tablespaces make datafiles completely transparent for users as bigfile tablespaces contain only one datafile.
Considerations with Bigfile Tablespaces
•We should consider to use bigfile tablespace with Automatic Storage Management or other logical volume managers that support dynamically extensible logical volumes and striping or RAID.
•We should not use bigfile tablespace on a system that does not support striping because of negative implications for parallel execution and RMAN backup parallelization.
•We should not use bigfile tablespace if there is free space problem on a disk group, and the only way to extend a tablespace is to add a new datafile on a different disk group.
•We should not use bigfile tablespace on the system that impose limit to large file sizes.
•Overall, performance of database opens, checkpoints, and DBWR processes should improve if data is stored in bigfile tablespaces instead of smallfile tablespaces. Though in case of corrupted datafile restore time of the datafile is more than of smallfile tablespace case.
Related Documents
Overall Database Size
Overview of Extents and when extents are allocated
Extents is made up of a number of contiguous data blocks. One or more extents make a segment. When in a segment there is no space i.e segment is full, then to allow more space oracle allocates a new extent in that segment.
When Extents Are Allocated
When you create a table or index then oracle automatically creates an initial extent with a number of specified data blocks (with how many data blocks will be discussed later on) under the table's/ index's data segment.
In this case though no rows are inserted into the table or index but yet extents are allocated and reserve for the table's or index's rows.
Now, if the data blocks of a segment's initial extent become full and more space is required to hold new data, then oracle automatically allocates an incremental extent for that segment.
In this way, subsequent increment extents are allocated whenever data blocks of a segment's extent full.
When Extents Are Deallocated
With the Segment Advisor oracle automatically advice on which schema objects or on which tablespaces has space available for reclamation based on the level of space fragmentation within the object.
To access Segment Advisor from enterprise manager home go to Related Links> Select Advisor Central>Select Segment Advisor.
You can reclaim space manually of LB table by
alter table "LB" enable row movement;
alter table "LB" shrink space;
In general the extents of a segment do not return to the tablespace until you drop the schema objects using DROP TABLE or DROP CLUSTER statement.
The exception is following,
•TRUNCATE...DROP STORAGE statement.
•ALTER TABLE table_name DEALLOCATE UNUSED;
•Periodically, Oracle deallocates one or more extents of a rollback segment if it has the OPTIMAL size specified.
When Extents Are Allocated
When you create a table or index then oracle automatically creates an initial extent with a number of specified data blocks (with how many data blocks will be discussed later on) under the table's/ index's data segment.
In this case though no rows are inserted into the table or index but yet extents are allocated and reserve for the table's or index's rows.
Now, if the data blocks of a segment's initial extent become full and more space is required to hold new data, then oracle automatically allocates an incremental extent for that segment.
In this way, subsequent increment extents are allocated whenever data blocks of a segment's extent full.
When Extents Are Deallocated
With the Segment Advisor oracle automatically advice on which schema objects or on which tablespaces has space available for reclamation based on the level of space fragmentation within the object.
To access Segment Advisor from enterprise manager home go to Related Links> Select Advisor Central>Select Segment Advisor.
You can reclaim space manually of LB table by
alter table "LB" enable row movement;
alter table "LB" shrink space;
In general the extents of a segment do not return to the tablespace until you drop the schema objects using DROP TABLE or DROP CLUSTER statement.
The exception is following,
•TRUNCATE...DROP STORAGE statement.
•ALTER TABLE table_name DEALLOCATE UNUSED;
•Periodically, Oracle deallocates one or more extents of a rollback segment if it has the OPTIMAL size specified.
Overview of Oracle Data Blocks
•Oracle data block is smallest unit of data that oracle use to store data.
•Oracle store data in the datafile in terms of oracle data block.
•As we know each operating system has a block size. Oracle data block is not same as operating system block size. In fact oracle block size is multiple of OS block size.
•How much of a standard oracle block size will be is defined by the DB_BLOCK_SIZE initialization parameter.
•We can also define up to five non standard block sizes.
•Data block format is discussed in Oracle data block format
•How we can choose non standard block size is discussed in Choose of a non standard Blocksize
•Oracle store data in the datafile in terms of oracle data block.
•As we know each operating system has a block size. Oracle data block is not same as operating system block size. In fact oracle block size is multiple of OS block size.
•How much of a standard oracle block size will be is defined by the DB_BLOCK_SIZE initialization parameter.
•We can also define up to five non standard block sizes.
•Data block format is discussed in Oracle data block format
•How we can choose non standard block size is discussed in Choose of a non standard Blocksize
More Internet Explorer Keyboard Shortcuts
Shortcuts to Access Address Box
Note that if internet explorer's tab are enabled then for only the first tab these shortcuts work well. For subsequent tabs these shortcuts do not work.
1)ALT+D: These two combination select the text in address box so that you can give new address or edit existing address in the address box.
2)F4: It displays a list of addresses that you have in your address box.
3)CTRL+LEFT ARROW: When in the Address box, move the cursor left to the next logical break in the address (period or slash or hypen etc). First press F4 and then press CTRL+LEFT ARROW.
4)CTRL+RIGHT ARROW: When in the Address box, move the cursor right to the next logical break in the address (period or slash or hypen etc). First press F4 and then press CTRL+RIGHT ARROW.
5)CTRL+ENTER: In the address box after typing address when you press these two kwy combination it automatically adds www. at the begining and .com at the end. Suppose press ALT+D and simply type google and then press CTRL+ENTER and then observe it will
go as www.google.com
6)UP ARROW: Move forward through the list of AutoComplete matches.
7)DOWN ARROW: Move back through the list of AutoComplete matches.
Shortcuts to Access Favourites
1)CTRL+D: Add the current page to your favorites.
2)CTRL+B: Open the Organize Favorites dialog box.
3)ALT+UP ARROW: Move selected item up in the Favorites list in the Organize Favorites dialog box.
4)ALT+DOWN ARROW: Move selected item down in the Favorites list in the Organize Favorites dialog box
Shortcut to print preview
Press ALTER+P and then v in order to come print preview page. In the print preview page you can apply following shortcut keys.
1)ALT+P:Set printing options and print the page.
2)ALT+U: Change paper, headers and footers, orientation, and margins for this page.
3)ALT+HOME: Display the first page that to be printed.
4)ALT+LEFT ARROW: Display the previous page that to be printed.
5)ALT+A: Type the page number that you want displayed.
6)ALT+RIGHT ARROW: Display the next page that to be printed.
7)ALT+END: Display the last page that to be printed.
8)ALT+-:Zoom out.
9)ALT++:Zoom in.
10)ALT+Z: Display a list of zoom percentages.
11)ALT+C: Close Print Preview.
Related Documents:
Natural Windows Keyboard Shortcut
General Windows keyboard Shortcut.
Dialog Box and Accessibility Keyboard Shortcut
Note that if internet explorer's tab are enabled then for only the first tab these shortcuts work well. For subsequent tabs these shortcuts do not work.
1)ALT+D: These two combination select the text in address box so that you can give new address or edit existing address in the address box.
2)F4: It displays a list of addresses that you have in your address box.
3)CTRL+LEFT ARROW: When in the Address box, move the cursor left to the next logical break in the address (period or slash or hypen etc). First press F4 and then press CTRL+LEFT ARROW.
4)CTRL+RIGHT ARROW: When in the Address box, move the cursor right to the next logical break in the address (period or slash or hypen etc). First press F4 and then press CTRL+RIGHT ARROW.
5)CTRL+ENTER: In the address box after typing address when you press these two kwy combination it automatically adds www. at the begining and .com at the end. Suppose press ALT+D and simply type google and then press CTRL+ENTER and then observe it will
go as www.google.com
6)UP ARROW: Move forward through the list of AutoComplete matches.
7)DOWN ARROW: Move back through the list of AutoComplete matches.
Shortcuts to Access Favourites
1)CTRL+D: Add the current page to your favorites.
2)CTRL+B: Open the Organize Favorites dialog box.
3)ALT+UP ARROW: Move selected item up in the Favorites list in the Organize Favorites dialog box.
4)ALT+DOWN ARROW: Move selected item down in the Favorites list in the Organize Favorites dialog box
Shortcut to print preview
Press ALTER+P and then v in order to come print preview page. In the print preview page you can apply following shortcut keys.
1)ALT+P:Set printing options and print the page.
2)ALT+U: Change paper, headers and footers, orientation, and margins for this page.
3)ALT+HOME: Display the first page that to be printed.
4)ALT+LEFT ARROW: Display the previous page that to be printed.
5)ALT+A: Type the page number that you want displayed.
6)ALT+RIGHT ARROW: Display the next page that to be printed.
7)ALT+END: Display the last page that to be printed.
8)ALT+-:Zoom out.
9)ALT++:Zoom in.
10)ALT+Z: Display a list of zoom percentages.
11)ALT+C: Close Print Preview.
Related Documents:
Natural Windows Keyboard Shortcut
General Windows keyboard Shortcut.
Dialog Box and Accessibility Keyboard Shortcut
Archiving not possible: No primary destinations
Error Description:
Today I got a very interesting problem. The developer told me that they can't connect to database. It says ORA-00257: archiver error. Then I log on to the database and it was ok. I asked developer it is fine. After some time developers again asked me they are having the same problem. I then look for alert log and saw the following stack of message.
ARC0: Failed to archive thread 1 sequence 1192 (4)
Mon Jul 21 05:51:08 2008
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive thread 1 sequence 1192 (4)
Mon Jul 21 05:51:08 2008
ARC0: Archiving not possible: No primary destinations
ARC0: Failed to archive thread 1 sequence 1192 (4)
Mon Jul 21 05:51:08 2008
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive thread 1 sequence 1192 (4)
Cause of The Problem:
Unknown yet. Possibly hit oracle bug.
Solution of The Problem:
At first seems I guessed there is space issue and look for following thing as in described,
ORA-00257: archiver error. Connect internal only, until freed.
Step1: Look for archival destination.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1203
Next log sequence to archive 1205
Current log sequence 1205
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /oradata1
db_recovery_file_dest_size big integer 50G
I also checked for settings whether LOG_ARCHIVE_DEST_10 is implicitly set or not. Yet it was set.
SQL> select DEST_NAME,DESTINATION from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_10';
DEST_NAME
--------------------------------------------------------------------------------
DESTINATION
--------------------------------------------------------------------------------
LOG_ARCHIVE_DEST_10
USE_DB_RECOVERY_FILE_DEST
Step2: Let's check space in flash recovery area.
SQL> SELECT * FROM V$RECOVERY_FILE_DEST;
NAME
--------------------------------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
/oradata1
5.3687E+10 32381952 0 2
So it was fine. I suddenly look for alert log and it was changed to
ORA-16038: log 2 sequence# 1160 cannot be archived
ORA-00354: corrupt redo log block header
ORA-00312: online log 2 thread 1: '/oradata1/arju/ARJU/redo02.log'
I immediately try to clear the logfile as it is described in ORA-16038,ORA-00354,ORA-00312 corrupt redo log block header
SQL> alter database clear unarchived logfile group 2;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
^C alter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
But no hope. It stopped and in alert log again it shows error message Archiving not possible: No primary destinations.
Step3: I set LOG_ARCHIVE_DEST_9 explicitly to DB_RECOVERY_FILE_DEST
With the following statement whenever I explicitly set LOG_ARCHIVE_DEST_9 to use DB_RECOVERY_FILE_DEST then the error gone.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_9='LOCATION=USE_DB_RECOVERY_FILE_DEST';
System altered.
In the alert log I put tail -f and wanted to see that status. Immediately status becomes as follows,
Cleared LOG_ARCHIVE_DEST_10 parameter default value
Mon Jul 21 05:52:04 2008
ALTER SYSTEM SET log_archive_dest_9='LOCATION=USE_DB_RECOVERY_FILE_DEST' SCOPE=BOTH;
Mon Jul 21 05:52:04 2008
Archiver process freed from errors. No longer stopped
And error gone. I tested with alter system switch logfile and it went normal.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
I don't know what happened. Possibly this is oracle bug. Later I explicitly set log_archive_dest_9 to use LOCATION USE_DB_RECOVERY_FILE_DEST and unset log_archive_dest_9 and it went normal.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_10='LOCATION=USE_DB_RECOVERY_FILE_DEST';
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_9='';
System altered.
Related Documents
ORA-16038,ORA-00354,ORA-00312 corrupt redo log block header
ORA-00257: archiver error. Connect internal only, until freed.
Today I got a very interesting problem. The developer told me that they can't connect to database. It says ORA-00257: archiver error. Then I log on to the database and it was ok. I asked developer it is fine. After some time developers again asked me they are having the same problem. I then look for alert log and saw the following stack of message.
ARC0: Failed to archive thread 1 sequence 1192 (4)
Mon Jul 21 05:51:08 2008
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive thread 1 sequence 1192 (4)
Mon Jul 21 05:51:08 2008
ARC0: Archiving not possible: No primary destinations
ARC0: Failed to archive thread 1 sequence 1192 (4)
Mon Jul 21 05:51:08 2008
ARC1: Archiving not possible: No primary destinations
ARC1: Failed to archive thread 1 sequence 1192 (4)
Cause of The Problem:
Unknown yet. Possibly hit oracle bug.
Solution of The Problem:
At first seems I guessed there is space issue and look for following thing as in described,
ORA-00257: archiver error. Connect internal only, until freed.
Step1: Look for archival destination.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1203
Next log sequence to archive 1205
Current log sequence 1205
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /oradata1
db_recovery_file_dest_size big integer 50G
I also checked for settings whether LOG_ARCHIVE_DEST_10 is implicitly set or not. Yet it was set.
SQL> select DEST_NAME,DESTINATION from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_10';
DEST_NAME
--------------------------------------------------------------------------------
DESTINATION
--------------------------------------------------------------------------------
LOG_ARCHIVE_DEST_10
USE_DB_RECOVERY_FILE_DEST
Step2: Let's check space in flash recovery area.
SQL> SELECT * FROM V$RECOVERY_FILE_DEST;
NAME
--------------------------------------------------------------------------------
SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
----------- ---------- ----------------- ---------------
/oradata1
5.3687E+10 32381952 0 2
So it was fine. I suddenly look for alert log and it was changed to
ORA-16038: log 2 sequence# 1160 cannot be archived
ORA-00354: corrupt redo log block header
ORA-00312: online log 2 thread 1: '/oradata1/arju/ARJU/redo02.log'
I immediately try to clear the logfile as it is described in ORA-16038,ORA-00354,ORA-00312 corrupt redo log block header
SQL> alter database clear unarchived logfile group 2;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
^C alter system switch logfile
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
But no hope. It stopped and in alert log again it shows error message Archiving not possible: No primary destinations.
Step3: I set LOG_ARCHIVE_DEST_9 explicitly to DB_RECOVERY_FILE_DEST
With the following statement whenever I explicitly set LOG_ARCHIVE_DEST_9 to use DB_RECOVERY_FILE_DEST then the error gone.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_9='LOCATION=USE_DB_RECOVERY_FILE_DEST';
System altered.
In the alert log I put tail -f and wanted to see that status. Immediately status becomes as follows,
Cleared LOG_ARCHIVE_DEST_10 parameter default value
Mon Jul 21 05:52:04 2008
ALTER SYSTEM SET log_archive_dest_9='LOCATION=USE_DB_RECOVERY_FILE_DEST' SCOPE=BOTH;
Mon Jul 21 05:52:04 2008
Archiver process freed from errors. No longer stopped
And error gone. I tested with alter system switch logfile and it went normal.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
I don't know what happened. Possibly this is oracle bug. Later I explicitly set log_archive_dest_9 to use LOCATION USE_DB_RECOVERY_FILE_DEST and unset log_archive_dest_9 and it went normal.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_10='LOCATION=USE_DB_RECOVERY_FILE_DEST';
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_9='';
System altered.
Related Documents
ORA-16038,ORA-00354,ORA-00312 corrupt redo log block header
ORA-00257: archiver error. Connect internal only, until freed.
Sunday, July 20, 2008
Logminer fails with ORA-01284, ORA-00308, ORA-27047
Problem Description:
Whenever you try to run the logminer in the mining database (different from source database. Source database is one in which redo or archived redo logs are generated and mining database is one in which you try to analysis those logs) to add logfile using DBMS_LOGMNR.ADD_LOGFILE it generates error ORA-01284, ORA-00308, ORA-27047.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
LOGFILENAME => '/export/home/oracle/o1_mf_1_61856_48637xkh_.arc', -
OPTIONS => DBMS_LOGMNR.NEW);
BEGIN DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/export/home/oracle/o1_mf_1_61856_48637xkh_.arc', OPTIONS => DBMS_LOGMNR.NEW); END;
*
ERROR at line 1:
ORA-01284: file /export/home/oracle/o1_mf_1_61856_48637xkh_.arc cannot be
opened
ORA-00308: cannot open archived log
'/export/home/oracle/o1_mf_1_61856_48637xkh_.arc'
ORA-27047: unable to read the header block of file
Additional information: 2
ORA-06512: at "SYS.DBMS_LOGMNR", line 68
ORA-06512: at line 1
But if you see in your mining database hard disk the archived redo log file actually exist.
SQL> !ls -l /export/home/oracle/o1_mf_1_61856_48637xkh_.arc
-rwxrwxrwx 1 oracle oinstall 24671232 Jul 21 00:38 /export/home/oracle/o1_mf_1_61856_48637xkh_.arc
Cause of The Problem:
To be able to run oracle logminer there are several requirements. If the requirements are not satisfy logminer will not run successfully. One of the requirement to run logminer is both the source database and the mining database must be running on the same hardware platform.
The database block sizes of the analyzing instance and the log source database must also be the same.
If they are different then logminer will not work and working with it will produce error ORA-01284, ORA-00308, ORA-27047.
Solution of The problem:
Use the mining database as the same hardware platform as of source database. If you don't have same platform then it is not possible. In that case you can use source database for mining.
Whenever you try to run the logminer in the mining database (different from source database. Source database is one in which redo or archived redo logs are generated and mining database is one in which you try to analysis those logs) to add logfile using DBMS_LOGMNR.ADD_LOGFILE it generates error ORA-01284, ORA-00308, ORA-27047.
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
LOGFILENAME => '/export/home/oracle/o1_mf_1_61856_48637xkh_.arc', -
OPTIONS => DBMS_LOGMNR.NEW);
BEGIN DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/export/home/oracle/o1_mf_1_61856_48637xkh_.arc', OPTIONS => DBMS_LOGMNR.NEW); END;
*
ERROR at line 1:
ORA-01284: file /export/home/oracle/o1_mf_1_61856_48637xkh_.arc cannot be
opened
ORA-00308: cannot open archived log
'/export/home/oracle/o1_mf_1_61856_48637xkh_.arc'
ORA-27047: unable to read the header block of file
Additional information: 2
ORA-06512: at "SYS.DBMS_LOGMNR", line 68
ORA-06512: at line 1
But if you see in your mining database hard disk the archived redo log file actually exist.
SQL> !ls -l /export/home/oracle/o1_mf_1_61856_48637xkh_.arc
-rwxrwxrwx 1 oracle oinstall 24671232 Jul 21 00:38 /export/home/oracle/o1_mf_1_61856_48637xkh_.arc
Cause of The Problem:
To be able to run oracle logminer there are several requirements. If the requirements are not satisfy logminer will not run successfully. One of the requirement to run logminer is both the source database and the mining database must be running on the same hardware platform.
The database block sizes of the analyzing instance and the log source database must also be the same.
If they are different then logminer will not work and working with it will produce error ORA-01284, ORA-00308, ORA-27047.
Solution of The problem:
Use the mining database as the same hardware platform as of source database. If you don't have same platform then it is not possible. In that case you can use source database for mining.
How to Load or copy data from SQL Server or excel to Oracle
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
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
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)