Oracle SQLLDR Command Examples (Oracle SQL*Loader

In Linux/ Fedora 14 - Oracle Database 11g R2

Disable all foreign key referenced by the primary key of the table to which the data is being loading.

SQL> alter tablename disable constraintname;

Here is the control.ctl file

[oracle@localhost sqlloader]$ vi /u01/sqlloader/country_control.ctl

LOAD DATA
INFILE *
truncate
INTO TABLE hr_country
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
( countryid INTEGER EXTERNAL,country CHAR)
BEGINDATA
1,Algeria
2,Egypt
3,Libyan Arab Jamahiriya

[oracle@localhost sqlloader]$ sqlldr hms/hms11g@hms control=country_control.ctl

SQL*Loader: Release 11.2.0.1.0 - Production on Thu Sep 26 21:52:01 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 3

[oracle@localhost sqlloader]$

SQL> select * from hr_country;

 COUNTRYID COUNTRY
---------- ------------------------------------------------------------
     1 Algeria
     2 Egypt
     3 Libyan Arab Jamahiriya

Now enable the foreign key constraints.

Here's CONTINUEIF discussion, if you are interested in some more reading.

Also, check the documentation for more information about CONTINUEIF

(and other SQL*Loader options).

For more

OR






In Widows




HOW TO IMPORT CSV FILE IN ORACLE DATABASE ?

Get a .csv format file that is to be imported in oracle database. Here this is named “FAR_T_SNSA.csv”

Create a table in sql with same column name as there were in .csv file. create table Billing ( iocl_id char(10), iocl_consumer_id char(10));


Create a Control file that contains sql*loder script. In notepad type the script as below and save this with .ctl extension, in selecting file type as All Types(*). Here control file is named as Billing. And the Script is as Follows:
LOAD DATA
INFILE 'D:FAR_T_SNSA.csv'
INSERT INTO TABLE Billing
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
iocl_id,
iocl_consumer_id
)


Now in Command prompt run command:
Sqlldr UserId/Password
Control = “ControlFileName” 
--------------------------------
Here ControlFileName is Billing.





There's sql loader.


Not a no-brainer but worth looking at. You need to be at a workstation with the oracle client installed and configured to

reach the database. You would save your excel file as a tab delimited file eg myfile.txt .

You need a control file something like this (mytable.ctl)

{code}

load data APPEND into table MYTABLE

fields terminated by '    ' trailing nullcols

(

FIELDA "upper(trim(:FIELDA))",

FIELDB "upper(trim(:FIELDB))"

)

sqldr control=mytable.ctl data=myfile.txt

(asks for login info).



Another feature, external tables:

http://docs.oracle.com/cd/B19306_01/server.102/b14215/et_concepts.htm



but back to this idea of loading data directly into forms. That's a great idea. I think it would best be done with copy

and paste. We need forms to have more features regarding copying and pasting data.  I think forms relegates too much to this hard to get to work library webutil. As applets are reined in more and more security-wise, it will become more and more difficult to get an applet to be able to browse and read files on the client. So think plan B. It will be much more difficult for the security types to stop people from copying and pasting data. Let's go that way is my advice.
For more


host(); forms 10g

Rather than calling SqlLoader directly, you must create a batch file (.bat) and call it. In the batch file, you must first set up the environment because the Forms HOST command starts a shell that doesn't have include the system configurations. So for example create this batch file and assume you name itoracle.bat :set ORACLE_HOME=C:\oracle10g\iAS1012
set ORACLE_SID=orcl
set PATH=%SystemRoot%\system32;%SystemRoot%;%ORACLE_HOME%\bin
sqlplus
If you want to pass values into the batch file you can do this, but it may take some careful coding. For example, if you wanted to pass the username and password into the above script you can do the following. The %1 accepts a value coming in from the command line. If you need more simple add %2 %3 and so on. Each parameter would be separated by a space, which is where it becomes tricky in the Forms code.set ORACLE_HOME=C:\oracle10g\iAS1012
set ORACLE_SID=orcl
set PATH=%SystemRoot%\system32;%SystemRoot%;%ORACLE_HOME%\bin
sqlplus %1
From the form, the HOST call might look something like this. Notice carefully the exact spaces and quotation marks I have included in the statement.
HOST('"c:\oracle.bat " scott/tiger""');
or*HOST('cmd /c start "" "c:\oracle.bat " scott/tiger""');

also visit and this

Loading Excel Data Into Oracle Forms 10G

In order to import data from Excel into your database through Oracle Forms, you need to use the WebUtil library. Oracle Forms 10g comes with the library, but you need to download and configure the Java Com Bridge (JACOB.jar) Java archive/library. Search the Oracle Forms Help system on Configuring Webutil. This will give you three results. Print off the Runtime Setup Checklist and Configuring WebUtil topics and follow the checklist as you are reading the Configuring WebUtil topic. Make sure you complete each configuration step.

Once configured, search the Forum for "Import Excel using WebUtil" and you will find numerous threads on this subject.

Webutil Configuration Steps

webutil object library and pll already exist in DevSuiteHome_1\forms folder.

Webutil Steps
=========================
1. On drive C create a folder name Webutil.
Extract the jacob_18.zip file into C:\Webutil_Final Folder.

2. Switch to Command Prompt, and perform following:
C:\>cd webutil
C:\>cd webutil_final
C:\Webutil_Final>COPY JACOB.JAR D:\oracle\DevSuiteHome_1\forms\java
C:\Webutil_Final>COPY JACOB.DLL D:\oracle\DevSuiteHome_1\forms\webutil
C:\Webutil_Final>COPY calendar90.pll D:\oracle\DevSuiteHome_1\forms
C:\Webutil_Final>COPY stndrd20.OLB D:\oracle\DevSuiteHome_1\forms

3. On Dos Prompt Sign frmwebutil.jar and jacob.jar with following commands
C:\>set path=D:\oracle\DevSuiteHome_1\jdk\bin;%PATH%

Sign the files by using
C:\>D:\oracle\DevSuiteHome_1\forms\webutil\sign_webutil D:\oracle\DevSuiteHome_1\forms\java\frmwebutil.jar

Output
Generating a self signing certificate for key=webutil2...
...successfully done.

.
Backing up D:\oracle\DevSuiteHome_1\forms\java\frmwebutil.jar as D:\oracle\DevSu iteHome_1\forms\java\frmwebutil.jar.old...
1 file(s) copied.
Signing D:\oracle\DevSuiteHome_1\forms\java\frmwebutil.jar using key=webutil2...
...successfully done.

C:\>D:\oracle\DevSuiteHome_1\forms\webutil\sign_webutil D:\oracle\DevSuiteHome_1\forms\java\jacob.jar

Output
Generating a self signing certificate for key=webutil2...
keytool error: java.lang.Exception: Key pair not generated, alias already exists
.
There were warnings or errors while generating a self signing certificate. Please review them.
.
Backing up D:\oracle\DevSuiteHome_1\forms\java\jacob.jar as D:\oracle\DevSuiteHome_1\forms\java\jacob.jar.old...
1 file(s) copied.
Signing D:\oracle\DevSuiteHome_1\forms\java\jacob.jar using key=webutil2...
...successfully done.

4. Now open the SQL Session connecting with SYS user, and create a user named WEBUTIL and Password is ORACLE.
SQL> CREATE USER WEBUTIL IDENTIFIED BY ORACLE
2 DEFAULT TABLESPACE USERS
3 TEMPORARY TABLESPACE TEMP;

User created.

SQL> GRANT CONNECT, CREATE PROCEDURE, CREATE PUBLIC SYNONYM TO WEBUTIL;

Grant succeeded.

SQL> CONN WEBUTIL/ORACLE
Connected.
USER is "WEBUTIL"
linesize 100
pagesize 100
long 80

SQL> @D:\DevSuiteHome_1\forms\create_webutil_db.sql

Package created.

Package body created.
SQL> CREATE PUBLIC SYNONYM WEBUTIL_DB FOR WEBUTIL.WEBUTIL_DB;

Synonym created.
SQL> CONN SYS/ORACLE AS SYSDBA
Connected.

SQL> GRANT EXECUTE ON WEBUTIL_DB TO PUBLIC;

Grant succeeded.

5.Modify the DEFAULT.ENV file, you can find default.env file at D:\oracle\DevSuiteHome_1\forms\server\Default.env
append the following path in CLASSPATH entry
;d:\oracle\DevSuiteHome_1\jdk\jre\lib\rt.jar
append the following path in FORMS_PATH
;D:\oracle\DevSuiteHome_1\forms\webutil

6.Modify the FORMSWEB.CFG file, you can find this file at D:\oracle\DevSuiteHome_1\forms\server\formsweb.cfg
add:
archive_jini=frmall_jinit.jar,frmwebutil.jar,jacob.jar
archive=frmall.jar
also add :
[webutil]
WebUtilArchive=frmwebutil.jar,jacob.jar,f90all.jar
WebUtilLogging=off
WebUtilLoggingDetail=normal
WebUtilErrorMode=Alert
WebUtilDispatchMonitorInterval=5
WebUtilTrustInternal=true
WebUtilMaxTransferSize=16384
baseHTMLjinitiator=webutiljini.htm
baseHTMLjpi=webutiljpi.htm
archive_jini=frmall_jinit.jar
archive=frmall.jar,f90all.jar
lookAndFeel=oracle
7.Modify the WEBUTIL.CFG file locate at the same place where you found the FORMSWEB.CFG file and change the
following
#transfer.database.enabled=FALSE (default value)
transfer.database.enabled=TRUE
#transfer.appsrv.enabled=FALSE (default value)
transfer.appsrv.enabled=TRUE

8. Logon scott user in sqlplusw.
SQL> DESC EMP
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

SQL> CREATE TABLE EMP_IMAGE
2 (EMPNO NUMBER(04) PRIMARY KEY REFERENCES EMP(EMPNO),
3* IMAGE BLOB);

Table created.

SQL> INSERT INTO EMP_IMAGE
2 (EMPNO)
3* SELECT EMPNO FROM EMP;

SQL> COMMIT;

Commit complete.

9. Start OC4J instance,Open the Form Builder, and connect with WEBUTIL/ORACLE@orcl, Open the WEBUTIL.PLL file, located at
D:\oracle\DevSuiteHome_1\forms folder, compile all (Shift+Control+K), and genearte to PLX. Then Open the WEBUTIL.OLB, and
Subclass object to the form. Now attache the webutil.pll library and remove the path.

10.Create a new form module as Master&Detail using EMP and EMP_IMAGE data block.
After completing the wizard switch to Object naviagtor->Object Library->Open Webutil.olb which are located on
D:\oracle\DevSuiteHome_1\forms folder. Subclass the following, which are located in object library
WEBUTILCONFIG into Parameter Node.
WEBUTIL into Object Group Node.

11. Now attached the Pl Library WEBUTIL.PLL file, located at D:\oracle\DevSuiteHome_1\forms folder, and Press Yes button
when message appeared after pressing attach button.
PUSH_BUTTON1 PUSH_BUTTON2
NAME GET_FILE_NAME UPLOAD_DB

12. Now create One Text item, and Two Push buttons on WEBUTIL data block, and choose the Canvas where EMP Block are shown.

13. Apply following codes.

At form level
==================
WHEN NEW FORM INSTANCE
declare
fake_timer TIMER;
begin

--
-- Purpose of the fake timer is the we cannot call webutil in this trigger since the
-- beans have not yet been instantiated. If we put the code in a when-timer-expired-trigger
-- it means that this timer will not start running until Forms has focus (and so the webutil
-- beans will be instantiated and so call canbe made.
--
fake_timer:= CREATE_TIMER('webutil',100,NO_REPEAT);
end;

WHEN TIMER EXPIRED
:global.user_home := webutil_clientinfo.get_system_property('user.home');

At Program Unit Node
=====================
PROCEDURE UPLOAD_DB IS
l_success boolean;
BEGIN
l_success := webutil_file_transfer.Client_To_DB_with_progress
(clientFile => :file_name
,tableName => 'EMP_IMAGE'
,columnName => 'IMAGE'
,whereClause => 'EMPNO ='||:EMP.EMPNO
,progressTitle => 'Upload to Database in progress'
,progressSubTitle=> 'Please wait'
,asynchronous => false
,callbackTrigger => null
);
if l_success then
message('File uploaded successfully into the Database');
else
message('File upload to Database failed'||SQLERRM);
end if;

exception
when others then
message('File upload failed: '||sqlerrm);
END;

When Button Pressed Trigger
============================
On GET_FILE_NAME Push Button

declare
l_filename varchar2(200);
begin
l_filename := client_get_file_name(directory_name => :global.user_home
,file_name => null
,file_filter => null
,message => null
,dialog_type => null
,select_file => null
);
:file_name := l_filename;
end;

On UPLOAD_DB Push Button
begin
upload_db;
exception
when others then
message('File upload failed: '||sqlerrm);

end;

For more

14.Under Edit->Preferences->Runtime in Forms Builder, click on "Reset to Default" if the "Application Server URL" is empty.Then append "?config=webutil" at the end, so you end up with a URL of the form http://server:port/forms/frmservlet?config=webutil

How to Unlock the Account in Oracle

A user account can be locked by an expiration of a password and can be unlocked with a command.

Steps

1
Find which accounts are locked with the following query.
- SQL > select username,account_status from dba_users where account_status like '%LOCK%';

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
PM EXPIRED & LOCKED
OLAPSYS EXPIRED & LOCKED
BI EXPIRED & LOCKED
SI_INFORMTN_SCHEMA EXPIRED & LOCKED
ORDPLUGINS EXPIRED & LOCKED
TSMSYS EXPIRED & LOCKED
XDB EXPIRED & LOCKED
WMSYS EXPIRED & LOCKED
HR EXPIRED & LOCKED
SCOTT EXPIRED & LOCKED
OE EXPIRED & LOCKED

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
DMSYS EXPIRED & LOCKED
DIP EXPIRED & LOCKED
OUTLN EXPIRED & LOCKED
EXFSYS EXPIRED & LOCKED
SH EXPIRED & LOCKED
ANONYMOUS EXPIRED & LOCKED
CTXSYS EXPIRED & LOCKED
ORDSYS EXPIRED & LOCKED
IX EXPIRED & LOCKED
MDSYS EXPIRED & LOCKED
MDDATA EXPIRED & LOCKED

22 rows selected.

For more

Creating an Oracle 11g Database using DBCA (non-ASM)

In this example, I’ve already installed the Oracle Database 11.2.0.3 software on one of my Fedora 14 machines, and I’m going to create a new database instance simply called ‘db11g’ using the DBCA tool and archiving enabled.

The data files will be stored under the /u02/oradata/{DB_NAME} directory, setup by ‘root’ as follows:

mkdir -p /u02
chown -R oracle:oinstall /u02
chmod -R 775 /u02

Start the Database Configuration Assistant (DBCA)

Open an X-Windows session to the server as the ‘oracle’ user.

Set your ORACLE_HOME environment variable appropriately, then start the assistant by running the following:

export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3
$ORACLE_HOME/bin/dbca

Once the GUI starts, respond as follows:

Welcome
- Next
Step 1 of 12: Operations
- Create a Database
- Next
Step 2 of 12: Database Templates
- General Purpose or Transaction Processing
- Next
Step 3 of 12: Database Identification
- Global Database Name: db11g (or any name you want providing it’s no more than 8 characters long, and starts with an alphabetic character)
- SID: db11g
- Next
Step 4 of 12: Management Options
- Tick/Untick ‘Configure Enterprise Manager’ according to your needs.

If you do want to configure Enterprise Manager locally, and a listener hasn’t yet been configured, you’ll be presented with a warning about having to configure one before being able to continue. If you need to create a listener service, take a look at this post.

Leave everything else as default
Next

Step 5 of 12: Database Credentials
- Use the Same Administrative Password for All Account
- Password: ******
- Confirm Password: ******
- Next
Step 6 of 12: Management Options
- Specify storage type and locations for database files
- Storage Type: File System
- Use Common Location for All Database Files
- Database Files Location: /u02/oradata
Step 7 of 12: Recovery Configuration
- Tick Specify Fast Recovery Area
- Fast Recovery Area: {ORACLE_BASE}/fra
- Tick Enable Archiving
- Click on Edit Archive Mode Parameters…
  - Archive Log File Format: %t_%s_%r.arc
  - OK
- Next
Step 8 of 12: Database Content
- Optionally, check ‘Sample Schemas’ (this will create an ‘EXAMPLE’ tablespace and some sample schemas). I recommend leaving this unticked if this is to become a live environment.
- Next
Step 9 of 11: Initialization Parameters
- Use a Typical configuration
- Tick ‘Use Automatic Memory Management’
- Change the ‘Memory Size’ value accordingly (or leave default as 40% of total machine memory)
- Review any additional parameters by clicking ‘All Initialization Parameters’
- Next
Step 10 of 11: Database Storage
- Check storage locations/options and update accordingly if required
- Next
Step 11 of 11: Creation Options
- Optionally tick ‘Generate Database Creation Scripts’ if you prefer to keep it for future reference.
- Finish
Confirmation

Check the ‘Create Database – Summary’ details
OK

deinstall oracle 11g on linux

From 11gR2, oracle provide us an deinstall tool. With that now we can easily remove oracle binaries.

Below is the step:
node1[oracle]_orcl> cd $ORACLE_HOME
node1[oracle]_orcl> cd deinstall
node1[oracle]_orcl> ls -ltr
-rwxr-xr-x 1 oracle dba 32343 Dec 16 2009 sshUserSetup.sh
-rw-r--r-- 1 oracle dba 409 Aug 18 2010 readme.txt
-rw-r--r-- 1 oracle dba 3466 Aug 18 2010 deinstall.xml
-rwxr-xr-x 1 oracle dba 9780 May 10 2011 bootstrap.pl
-rwxr-xr-x 1 oracle dba 18475 Jun 9 2011 deinstall.pl
drwxr-xr-x 2 oracle dba 4096 Feb 20 11:15 response
drwxr-xr-x 2 oracle dba 4096 Feb 20 11:15 jlib
-rwxr-xr-x 1 oracle dba 9655 Feb 20 11:17 deinstall

node1[oracle]_orcl> ./deinstall
Checking for required files and bootstrapping ...
Please wait ...
Location of logs /tmp/deinstall2012-04-19_01-29-47AM/logs/
############ ORACLE DEINSTALL & DECONFIG TOOL START ############
############### CHECK OPERATION START #######################
## [START] Install check configuration ##
Checking for existence of the Oracle home location /orcl/oracle/product/11.2.0.3
Oracle Home type selected for deinstall is: Oracle Single Instance Database
Oracle Base selected for deinstall is: /orcl/oracle
Checking for existence of central inventory location /orcl/oracle/oraInventory
Checking for sufficient temp space availability on node(s) : 'node1'
## [END] Install check configuration ##
Network Configuration check config START
Network de-configuration trace file location: /tmp/deinstall2012-04-19_01-29-47AM/logs/netdc_check2012-04-19_01-30-16-AM.log
Specify all Single Instance listeners that are to be de-configured [LISTENER_orcl]:Network Configuration check config END
Database Check Configuration START
Database de-configuration trace file location: /tmp/deinstall2012-04-19_01-29-47AM/logs/databasedc_check2012-04-19_01-31-56-AM.log
Use comma as separator when specifying list of values as input
Specify the list of database names that are configured in this Oracle home [orcl]:
###### For Database 'orcl' ######
Single Instance Database
The diagnostic destination location of the database: /orcl/oracle/dump01/oracle/orcl/diag/rdbms/orcl
Storage type used by the Database:
The details of database(s) orcl have been discovered automatically. Do you still want to modify the details of orcl database(s)? [n]:
Database Check Configuration END
Enterprise Manager Configuration Assistant START
EMCA de-configuration trace file location: /tmp/deinstall2012-04-19_01-29-47AM/logs/emcadc_check2012-04-19_01-32-05-AM.log
Checking configuration for database orcl
Enterprise Manager Configuration Assistant END
Oracle Configuration Manager check START
OCM check log file location : /tmp/deinstall2012-04-19_01-29-47AM/logs//ocm_check1221.log
Oracle Configuration Manager check END
######################### CHECK OPERATION END #########################
####################### CHECK OPERATION SUMMARY #######################
Oracle Home selected for deinstall is: /orcl/oracle/product/11.2.0.3
Inventory Location where the Oracle home registered is: /orcl/oracle/oraInventory
Following Single Instance listener(s) will be de-configured: LISTENER_orcl
The following databases were selected for de-configuration : orcl
Database unique name : orcl
Storage used :
No Enterprise Manager configuration to be updated for any database(s)
No Enterprise Manager ASM targets to update
No Enterprise Manager listener targets to migrate
Checking the config status for CCR
Oracle Home exists and CCR is configured
CCR check is finished
Do you want to continue (y - yes, n - no)? [n]: y
A log of this session will be written to: '/tmp/deinstall2012-04-19_01-29-47AM/logs/deinstall_deconfig2012-04-19_01-30-14-AM.out'
Any error messages from this session will be written to: '/tmp/deinstall2012-04-19_01-29-47AM/logs/deinstall_deconfig2012-04-19_01-30-14-AM.err'
######################## CLEAN OPERATION START ########################
Enterprise Manager Configuration Assistant START
EMCA de-configuration trace file location: /tmp/deinstall2012-04-19_01-29-47AM/logs/emcadc_clean2012-04-19_01-32-05-AM.log
Updating Enterprise Manager ASM targets (if any)
Updating Enterprise Manager listener targets (if any)
Enterprise Manager Configuration Assistant END
Database de-configuration trace file location: /tmp/deinstall2012-04-19_01-29-47AM/logs/databasedc_clean2012-04-19_01-32-09-AM.log
Database Clean Configuration START orcl
This operation may take few minutes.
Database Clean Configuration END orcl
Network Configuration clean config START
Network de-configuration trace file location: /tmp/deinstall2012-04-19_01-29-47AM/logs/netdc_clean2012-04-19_01-33-54-AM.log
De-configuring Single Instance listener(s): LISTENER_orcl
De-configuring listener: LISTENER_orcl
Stopping listener: LISTENER_orcl
Warning: Failed to stop listener. Listener may not be running.
Deleting listener: LISTENER_orcl
Listener deleted successfully.
Listener de-configured successfully.
De-configuring Listener configuration file...
Listener configuration file de-configured successfully.
De-configuring Local Net Service Names configuration file...
Local Net Service Names configuration file de-configured successfully.
De-configuring backup files...
Backup files de-configured successfully.
The network configuration has been cleaned up successfully.
Network Configuration clean config END
Oracle Configuration Manager clean START
OCM clean log file location : /tmp/deinstall2012-04-19_01-29-47AM/logs//ocm_clean1221.log
Oracle Configuration Manager clean END
Setting the force flag to false
Setting the force flag to cleanup the Oracle Base
Oracle Universal Installer clean START
Detach Oracle home '/orcl/oracle/product/11.2.0.3' from the central inventory on the local node : Done
Delete directory '/orcl/oracle/product/11.2.0.3' on the local node : Done
Delete directory '/orcl/oracle/oraInventory' on the local node : Done
The Oracle Base directory '/orcl/oracle' will not be removed on local node. The directory is not empty.
Oracle Universal Installer cleanup was successful.
Oracle Universal Installer clean END
## [START] Oracle install clean ##
Clean install operation removing temporary directory '/tmp/deinstall2012-04-19_01-29-47AM' on node 'node1'
## [END] Oracle install clean ##
######################### CLEAN OPERATION END #########################
####################### CLEAN OPERATION SUMMARY #######################
Successfully de-configured the following database instances : orcl
Following Single Instance listener(s) were de-configured successfully: LISTENER_orcl
Cleaning the config for CCR
Cleaning the CCR configuration by executing its binaries
CCR clean is finished
Successfully detached Oracle home '/orcl/oracle/product/11.2.0.3' from the central inventory on the local node.
Successfully deleted directory '/orcl/oracle/product/11.2.0.3' on the local node.
Successfully deleted directory '/orcl/oracle/oraInventory' on the local node.
Oracle Universal Installer cleanup was successful.
Run 'rm -rf /etc/oraInst.loc' as root on node(s) 'node1' at the end of the session.
Run 'rm -rf /opt/ORCLfmap' as root on node(s) 'node1' at the end of the session.
Oracle deinstall tool successfully cleaned up temporary directories.
#######################################################################
############# ORACLE DEINSTALL & DECONFIG TOOL END #############

Auto Backup of Dump File

LINUX

Link

Write shell script - create file expdp.sh in oracle user and place it in /Home/oracle/scripts/

#!/bin/ksh

#Script to perform datapump export every hour

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1

export ORACLE_SID=shisb

export PATH=$PATH:$ORACLE_HOME/bin

expdp has/hashserver@shisb directory=export_dp dumpfile=shisb-$(date +%d%m%Y_%H%M%S).dmp logfile=shisb-$(date +%d%m%Y_%H%M%S).log schemas=has

#mv /tmp/shisb*.dmp /u01/dumps/

#mv /tmp/shisb*.log /u01/dumps/

#chmod o+r /u01/dumps/*

# chmod u+x /home/oracle/scripts/expdp.sh
# chown oracle.oinstall /home/oracle/scripts/expdp.sh

For more

To fix this error ORA-39213: Metadata processing is not available

Link

Symptoms:

You get the following error during expdp or impdp

ORA-39006: internal error
ORA-39213: Metadata processing is not available

Solution:

Execute sys.dbms_metadata_util.load_stylesheets to fix this problem
#sqlplus / as sysdba

SQL> execute sys.dbms_metadata_util.load_stylesheets;

PL/SQL procedure successfully completed.

[oracle@localhost ~]$ crontab -e

and add following lines

# daily logical export

01 23 * * * /home/oracle/scripts/expdp.sh

save and quite

For more

Windows

Link

Create batch file exp.bat with following lines.

Append Date & time to File name in Window Batch file

Sometimes it may be required to schedule the same task twice a days . In such cases, we have to create two batch file and scheduling them separately because the generated name may overwrites or throws errors . To overcome from this issue , we can schedule the same task with single batch file. Suppose we have to take the logical backup of a schema(say scott) twice a days . We can do this by simply appending the date and time to the dump file name .

As in case of Linux /Unix systems, shell scripting is very liberal with variables and we can define according to ourself . e.g;
expdate=`date ‘+%d%m%Y’`
dat=`date ‘+%m%d%y %H:%M:%S’`

And then go onto define in our script as

./expdp system/xxxx dumpfile=scott_$expdate.dmp logfile=scott_log_$expdate.log schemas=scott

But on windows machine ,we use the Date and Time function. The Date and Time function are as below :

Date: %date:~4,2%-%date:~7,2%-%date:~12,2%
Time: %time:~0,2%-%time:~3,2%-%time:~6,2%

This above function can be use to generate the unique dumpfile name. Below is the Demo of this function .

c:\> exp system/ramtech@noida owner=scott file=c:\scott_%date:~4,2%-%date:~7,2%-%date:~12,2%-%time:~0,2%-%time:~3,2%-%time:~6,2%.dmp log=c:\scottlog_%date:~4,2%-%date:~7,2%-%date:~12,2%-%time:~3,2%-%time:~6,2%.log

The file name generated i.e, dump file name is 'scott_12-22-11-12-04-31.dmp' and log file name is 'scottlog_12-22-11-04-31.log' where date is 12-22-11(12th-dec-2011) and time is 12-04-31(12hr:4min:31sec) .

exp myhotel1/myhotel1@xe owner=oadmin file=d:\myhotel1\backup\myhotel1_%date:~4,2%-%date:~7,2%-%date:~12,2%-%time:~0,2%-%time:~3,2%-%time:~6,2%.dmp log=d:\myhotel1\backup\myhotel1log_%date:~4,2%-%date:~7,2%-%date:~12,2%-%time:~3,2%-%time:~6,2%.log

exp oadm/nopassword@injsys file=c:\accounts\backup\----------------------------------

start > run > task

add task scheduler > ----------------------------------

ORACLE 11gR2 does not export (exp) empty tables



Oracle data pump is not always possible to use for export DB dumps. When you are working on a client machine and do not have access to server machine, still you need to use exp command.



In Oracle11gR2 does not export empty tables by the exp export utility. So that, tables will not be export at all, even the DDL definitions.

This problem is related to the new Oracle 11gR2 feature called deferred segment creation and the fact that empty tables are not listed in dba_segments.

This database feature is controlled by the database parameter deferred_segment_creation. This parameter is in default TRUE. If you set this value FALSE, it will export new empty tables those created after the parameter change.

Using following command you will able to export existing empty tables.



ALTER TABLE <empy table> ALLOCATE EXTENT;

Data Pump or Export/ Import Data from Oracle 11g in Linux OS

Link

Correct Solution...

Export /Import dump - Oracle 10g/11g -(window)

IMPORT
C:\WINDOWS\system32>mkdir d:\data_pump

C:\Users\M.Khalil>sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Mon Jan 21 12:15:52 2019

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Enter user-name: sys/xe123@xe as sysdba

Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL>create tablespace has_tablespace datafile 'has1.dat' size 20M autoextend on;
Tablespace created.

SQL>create user myhotel1 identified by myhotel1 default tablespace myhotel1 temporary tablespace temp quota unlimited on myhotel1;

User created.

SQL>grant connect,resource,dba to myhotel1;

Grant succeeded.

SQL> connect myhotel1/myhotel1;
Connected.
SQL>create or replace directory import_dp as 'd:\data_pump';

Directory created.

SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

C:\Users\M.Khalil>cd\

C:\>impdp myhotel1/myhotel1@xe directory=import_dp dumpfile=MYHOTEL_VR2.DMP REMAP_SCHEMA=sys:myhotel1

C:\>impdp has/shserver@xe directory=import_dp dumpfile=sh-17072019_230101.DMP REMAP_SCHEMA=sys:has

SQL> create or replace directory export_dp as 'd:\data_pump';

Directory created.

SQL> select directory_name from all_directories;

c:\expdp appsys/itecappsys@orcl directory=export_dp dumpfile=exp_appsys.dmp logfile=log_appsys.log schemas=sys:appsys

Export /Import dump - Oracle 10g/11g -(Linux)

Link

Scenario: I have a db and, it contains a schema called "hr". So i want to get a export dump of hr schema and import the same dump to some other schema in same db.

First of all you must create a file location for the dump files.

[oracle@prod ~]$ mkdir /u01/dumps

[oracle@prod ~]$ ls /u01/

app database dumps

[oracle@prod ~]$

I have few schemas in my db and I gonna take the export for hr schema.

So first of all you must log into the database as the schema user.

[oracle@prod ~]$ sqlplus hr/hr

Then issue below command to create the directory

SQL> create or replace directory export_dp as '/u01/dumps/';

Directory created.

export_dp is the logical name that you can give for your directory and the '/u01/dumps/' is the physical path.

You can check yous directory using below command

SQL> select directory_name from all_directories;

DIRECTORY_NAME

------------------------------

EXPORT_DP

# chmod 777 /u01/dumps/dumpfilename.extention

# chmod o+r /home/..../tmsmv-20120706.dmp

# chmod o+rx /home/..../

now exit from sql promt and come back to oracle promt and issue below statement to take the export.

[oracle@linuxdb ~]$expdp has/shserver@shisb directory=export_dp dumpfile=shisb-$(date +%d%m%Y_%H%M%S).dmp logfile=shisb-$(date +%d%m%Y_%H%M%S).log schemas=has

[oracle@prod ~]$ expdp hr/hr@DB11G directory=export_dp dumpfile=dump_hr.dmp

logfile=log_hr.log schemas=hr

Export: Release 11.1.0.6.0 - Production on Thursday, 19 April, 2012 13:20:26

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "HR"."SYS_EXPORT_SCHEMA_01": hr/********@DB11G directory=export_dp

dum pfile=dump_hr.dmp logfile=log_hr.log schemas=hr

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 448 KB

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/TRIGGER

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA

. . exported "HR"."COUNTRIES" 6.375 KB 25 rows

. . exported "HR"."DEPARTMENTS" 7.015 KB 27 rows

. . exported "HR"."EMPLOYEES" 16.80 KB 107 rows

. . exported "HR"."JOBS" 6.984 KB 19 rows

. . exported "HR"."JOB_HISTORY" 7.054 KB 10 rows

. . exported "HR"."LOCATIONS" 8.273 KB 23 rows

. . exported "HR"."REGIONS" 5.484 KB 4 rows

Master table "HR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for HR.SYS_EXPORT_SCHEMA_01 is:

/u01/dumps/dump_hr.dmp

Job "HR"."SYS_EXPORT_SCHEMA_01" successfully completed at 13:23:42

[oracle@prod ~]$

As you can see tables have been exported.. Cross verify whether dump file is there in the location.

[oracle@prod ~]$ ls /u01/dumps/

dump_hr.dmp log_hr.log

IF
expdp ORA-39213: Metadata processing is not available

SQL> SELECT comp_id, version, status FROM dba_registry;

COMP_ID                        VERSION                        STATUS
------------------------------ ------------------------------ --------------------------------------------
OWB                            11.2.0.2.0                     VALID
APEX                           3.2.1.00.12                    VALID
EM                             11.2.0.2.0                     VALID
AMD                            11.2.0.2.0                     VALID
SDO                            11.2.0.2.0                     VALID
ORDIM                          11.2.0.2.0                     VALID
XDB                            11.2.0.2.0                     VALID
CONTEXT                        11.2.0.2.0                     VALID
EXF                            11.2.0.2.0                     VALID
RUL                            11.2.0.2.0                     VALID
OWM                            11.2.0.2.0                     VALID
CATALOG                        11.2.0.2.0                     VALID
CATPROC                        11.2.0.2.0                     VALID
JAVAVM                         11.2.0.2.0                     VALID
XML                            11.2.0.2.0                     VALID
CATJAVA                        11.2.0.2.0                     VALID
APS                            11.2.0.2.0                     VALID
XOQ                            11.2.0.2.0                     VALID
RAC                            11.2.0.2.0                     VALID

-bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Feb 27 11:02:48 2014

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> execute dbms_metadata_util.load_stylesheets

PL/SQL procedure successfully completed.

SQL> exit

[oracle@prod ~]$

Now login to database as SYS user and create the new schema.. First of all you must create the tablespace for the user and then create the user. My tablespace name is "fin" and the user is "finance".

[oracle@prod ~]$ sqlplus / as sysdba

SQL> create tablespace fin datafile 'fin.dat' size 20M autoextend on;

Tablespace created.

How to determine size of tablespace in oracle 11g

select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files 
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments 
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;

Dropping a Tablespace:

Example The following statement drops the tbs_01 tablespace and drops all referential integrity constraints that refer to primary and unique keys inside tbs_01:

DROP TABLESPACE tbs_01 
    INCLUDING CONTENTS 
        CASCADE CONSTRAINTS;

Deleting Operating System Files:

Example The following example drops the tbs_02 tablespace and deletes all associated operating system datafiles:

DROP TABLESPACE tbs_02
   INCLUDING CONTENTS AND DATAFILES;

Then create user finance and assign fin tablespace for the user

SQL> create user finance identified by finance default tablespace fin

2 temporary tablespace temp

3 quota unlimited on fin;

User created.

Now grant whatever the access you need for the user...I give dba writes for the user.

SQL> grant dba to finance;

Grant succeeded.

SQL>

Now Log in as user and create import directory

SQL> connect finance/finance

Connected.

SQL> create or replace directory import_dp as '/u01/dumps/';

Directory created.

now exit from sql and come to oracle prompt and issue below.

[oracle@prod ~]$ impdp finance/finance@DB11G directory=import_dp dumpfile=dump_hr.dmp logfile=log_fin.log REMAP_SCHEMA=hr:finance

Import: Release 11.1.0.6.0 - Production on Thursday, 19 April, 2012 13:43:23

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "FINANCE"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "FINANCE"."SYS_IMPORT_FULL_01": finance/********@DB11G directory=import_dp dumpfile=dump_hr.dmp logfile=log_fin.log REMAP_SCHEMA=hr:finance

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER:"FINANCE" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "FINANCE"."COUNTRIES" 6.375 KB 25 rows

. . imported "FINANCE"."DEPARTMENTS" 7.015 KB 27 rows

. . imported "FINANCE"."EMPLOYEES" 16.80 KB 107 rows

. . imported "FINANCE"."JOBS" 6.984 KB 19 rows

. . imported "FINANCE"."JOB_HISTORY" 7.054 KB 10 rows

. . imported "FINANCE"."LOCATIONS" 8.273 KB 23 rows

. . imported "FINANCE"."REGIONS" 5.484 KB 4 rows

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/TRIGGER

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA

Job "FINANCE"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 13:44:04

[oracle@prod ~]$

Now connect as finance user and check one table to confirm the import

[oracle@prod ~]$ sqlplus finance/finance

SQL> desc employees

Name Null? Type

----------------------------------------- -------- ----------------------------

EMPLOYEE_ID NOT NULL NUMBER(6)

FIRST_NAME VARCHAR2(20)

LAST_NAME NOT NULL VARCHAR2(25)

EMAIL NOT NULL VARCHAR2(25)

PHONE_NUMBER VARCHAR2(20)

HIRE_DATE NOT NULL DATE

JOB_ID NOT NULL VARCHAR2(10)

SALARY NUMBER(8,2)

COMMISSION_PCT NUMBER(2,2)

MANAGER_ID NUMBER(6)

DEPARTMENT_ID NUMBER(4)

This is all about export/import using expdp and impdp command.....

Note: if you are importing to same user/schema in a different database, then you dont need to use remap_schema clause in impdp command. It's all up to you how you want to do the import.

_________________________________________________________________________________

I am not a gurru but have a little bit experience and want to share with you. We may export data from Oracle 11g in dump file format a little bit different from simple export/ import command on DOS prompt. Please follow the following steps. We have done such a way.

SQL> conn /as SYSDBA

Connected.

If you have wrongly created the expdir directory in a wrong place then drop it as follow otherwise skip this step.

SQL> drop directory EXPDIR;

Directory dropped.

SQL> create or replace directory expdir as '/u01/app/oracle';

Directory created.

SQL> GRANT READ,WRITE ON directory expdir TO SYSTEM;

Grant succeeded.

SQL>ALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK;

SQL> EXIT

Database Export/Import

[oracle_db~]$ expdp system/password@db11g full=Y directory=expdir dumpfile=export.dmp logfile=export_logfile.log

When importing the above dump file the following pre-requisites must be set.

The database name must be same.
The Tablespace name must be same.
The user accounts must be same and created. Then apply the following command.

[oracle_db~]$ impdp system/password@db11g full=Y directory=expdir dumpfile=export.dmp logfile=export_logfile.log

Schema Export/Import

[oracle_db~]$ expdp scott/tiger@db11g schemas=SCOTT directory=expdir dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

[oracle_db~]$ impdp scott/tiger@db11g schemas=SCOTT directory=expdir dumpfile=SCOTT.dmp logfile=impdpSCOTT.log

Tables Export/Import

[oracle_db~]$ expdp scott/tiger@db11g tables=EMP,DEPT directory=expdir dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

[oracle_db~]$ impdp scott/tiger@db11g tables=EMP,DEPT directory=expdir dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log

exp system/manager@xyz FULL=Y FILE=FULL.DMP

How to Start/ Stop of Auto Start up of Oracle Database 11g
Link
Select Windows Start > Run and enter rededit.
HKEY_LOCAL_MACHINE
SOFTWARE
ORACLE
KEY_OraDb11g_home1
double click the file ORA_ORCL11G_AUTOSTART
enter value TRUE for auto start up and FALSE for auto stop the database.  




Link
export selected tables in oracle

exp username/password@database tables=table1,table2 file='c:\backup\table_data.dmp'

 

Oracle/PLSQL: Synonyms


A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects.

Creating or replacing a synonym

The syntax for creating a synonym is:

create [or replace]  [public]  synonym [schema .] synonym_name

for [schema .] object_name [@ dblink];

The or replace phrase allows you to recreate the synonym (if it already exists) without having to issue a DROP synonym command.

The public phrase means that the synonym is a public synonym and is accessible to all users. Remember though that the user must first have the appropriate privileges to the object to use the synonym.

The schema phrase is the appropriate schema. If this phrase is omitted, Oracle assumes that you are referring to your own schema.

The object_name phrase is the name of the object for which you are creating the synonym. It can be one of the following:


table package
view materialized view
sequence java class schema object
stored procedure user-defined object
function synonym



For Example:

create public synonym suppliers

for app.suppliers;

This first example demonstrates how to create a synonym called suppliers. Now, users of other schemas can reference the table called supplierswithout having to prefix the table name with the schema named app. For example:

select * from suppliers;

If this synonym already existed and you wanted to redefine it, you could always use the or replace phrase as follows:

create or replace public synonym suppliers

for app.suppliers;

Dropping a synonym

It is also possible to drop a synonym. The syntax for dropping a synonym is:

drop [public] synonym [schema .] synonym_name [force];

The public phrase allows you to drop a public synonym. If you have specified public, then you don't specify a schema.

The force phrase will force Oracle to drop the synonym even if it has dependencies. It is probably not a good idea to use the force phrase as it can cause invalidation of Oracle objects.

For Example:

drop public synonym suppliers;

This drop statement would drop the synonym called suppliers that we defined earlier.

Lost Oracle SYS and SYSTEM password?

If your administration is as good as anybodies, you are bound to loose the not-so-frequently used password for the SYS and SYSTEM users of oracle. Here are a few ways I found to re-set those passwords:

Method 1: SQLPLUS (Tested on AIX Oracle 9.2.0.1.0)
Log into the database server as a user belonging to ‘dba’ [unix ] or ‘ora_dba’ [windows ] group , typically ‘oracle’, or an administrator on your windos machine. You are able to log into Oracle as SYS user, and change the SYSTEM password by doing the following:
$ sqlplus "/ as sysdba"SQL*Plus: Release 9.2.0.1.0 - Production on Mon Apr 5 15:32:09 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production SQL> show user USER is "SYS" SQL> passw system Changing password for system New password: Retype new password: Password changed SQL> quitNext, we need to change the password of SYS:
$ sqlplus "/ as system"SQL*Plus: Release 9.2.0.1.0 - Production on Mon Apr 5 15:36:45 2004 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. SP2-0306: Invalid option. Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}] where <logon> ::= <username>[/<password>][@<connect_string>] | / Enter user-name: system Enter password: Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production SQL> passw sys Changing password for sys New password: Retype new password: Password changed SQL> quitYou should now be able to log on the SYS and SYSTEM users, with the passwords you just typed in.

Method 2: Creating pwd file (Tested on Windows Oracle 8.1.7)

Stop the Oracle service of the instance you want to change the passwords of.
Find the PWD###.ora file for this instance, this is usuallly located atC:\oracle\ora81\database\, where ### is the SID of your database.
rename the PWD###.ora file to PWD###.ora.bak for obvious safety reasons.
Create a new pwd file by issuing the command: orapwd file=C:\oracle\ora81\database\PWD###.ora password=XXXXX
where ### is the SID and XXXXX is the password you would like to use for the SYS and INTERNAL accounts.
Start the Oracle service for the instance you just fixed. You should be able to get in with the SYS user and change other passwords from there.

ORA-01033: ORACLE Initialization or Shutdown in progress in Windows

Link

First Try it.

from your c:\ prompt type

sqlplus /nolog

from sqlplus prompt type

>connect /as sysdba

>shutdown

>startup mount

Then note the error massege and do accordingly.

>alter database recover database until cancel;

if the above command fails try
>recover database using backup controlfile until cancel;

on success type
>ALTER DATABASE OPEN RESETLOGS;

stop and restart oracle service

or

first off all check the status of your instance,its started or mount
,i guess its not open according to your post..by issue this comm
select status from v$instance;
if it is mount then try to open it by
alter database open;

might be is there any problem with datafiles.

and check the alert log file ...tell me the detail info from the alter log file..so that i can figer out the actual problem .

If you shut down your Oracle, it is normal that you can't log on.

Open SQL*Plus and connect sys as sysdba and start the database up again.

Your data will not get lost.

Or you could start the Oracle services yourself.

Right click on My Computer and select "manage". In the MMC select "Services". The Oracle Listener should be running, as well as the Oracle Agent and the Oracleservice that contains the SID of your database.

I have lanch Compasant Instance manager ,i have give him the file init.ora ,and i OPEN my data base,OK

now i have no problom.

OK here is the solution I found with the help of a DBA here :

Actually, the database was not shutted down properly the last time (it hung), so the solution is to shutdown explicitely by using SQL Plus.

- I was not able to log in so I used :

> sqlplus /nolog

And after that, I used these commands :

SQL> connect / as sysdba

ConnectÚ.

SQL> shutdown abort

Instance ORACLE arrÛtÚe.

SQL> startup nomount

Instance ORACLE lancÚe.

SQL> alter database mount;

SQL> alter database open;

SQL> alter database open RESETLOGS;
alter database open RESETLOGS
*
ERROR at line 1:
ORA-01245: offline file 1 will be lost if RESETLOGS is done
ORA-01110: data file 1: '/u01/app/oracle/oradata/sr/system01.dbf'

Link
RECOVER DATAFILE
SQL> alter database datafile '/u01/app/oracle/oradata/gh/system01.dbf' offline drop;

SQL> alter database datafile '/u01/app/oracle/oradata/gh/system01.dbf' online;

Database altered.

SQL> alter database open NORESETLOGS;

Database altered.

SQL> shutdown;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 2042241024 bytes

Fixed Size 1337548 bytes

Variable Size 553649972 bytes

Database Buffers 1476395008 bytes

Redo Buffers 10858496 bytes

Database mounted.

Database opened.

SQL>

And all works !!

ORA-01194: file 1 needs more recovery to be consistent

Link

Error log:

[oracle@localhost ~]$ sqlplus

Enter user-name: / as sysdba

SQL> shutdown abort;


SQL> startup nomount;

SQL> alter database mount;

SQL> alter database open;

SQL> recover database using backup controlfile until cancel;
ORA-01507: database not mounted

SQL> shutdown abort;
SQL> startup mount;

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/app/oracle/oradata/RTS_NEW/system_new.dbf'

Workaround for this error is to provide all the available archive log files to the recovery:

SQL> recover database using backup controlfile until cancel;
...
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO

Above command will apply all the available archive logs automatically. Now try to open database with resetlogs:

SQL> alter database open resetlogs;

If the error persists due to insufficient archive logs, do the following workaround:

SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

Startup database in mount mode:

SQL> startup mount
ORACLE instance started.
Total System Global Area 530288640 bytes
Fixed Size 2131120 bytes
Variable Size 310381392 bytes
Database Buffers 209715200 bytes
Redo Buffers 8060928 bytes
Database mounted.

Change “_allow_resetlogs_corruption” parameter to TRUE and undo_management parameter to MANUAL:

SQL> ALTER SYSTEM SET "_allow_resetlogs_corruption"= TRUE SCOPE = SPFILE;
SQL> ALTER SYSTEM SET undo_management=MANUAL SCOPE = SPFILE;

After doing above changes, shutdown database, and startup:

SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area 530288640 bytes
Fixed Size 2131120 bytes
Variable Size 310381392 bytes
Database Buffers 209715200 bytes
Redo Buffers 8060928 bytes
Database mounted.

Now try resetlogs:

SQL> alter database open resetlogs;
Database altered.

SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup

When database started successfully and up then perform the following steps

Create new undo tablespace and set “undo_tablespace” parameter to the new undo tablespace and change “undo_management” parameter to AUTO:

Undo space once allocated will be available for reuse but will not be deallocated to the OS. The best way to shrink Undo tablespace is to switch to a new Undo tablespace and drop the old Undo tablespace. The steps are:

1. Create a new undo tablespace of the same size (larger or smaller) depending on your database requirements.

SQL> show parameter control;SQL> CREATE UNDO TABLESPACE undo datafile '/u01/app/oracle/oradata/sr/undo_df1.dbf' size 200m autoextend on maxsize 30G;

Tablespace created.

SQL> alter system set undo_tablespace = undo scope=spfile;
System altered.

SQL> alter system set undo_management=auto scope=spfile;
System altered.

Now bounce your database.

SQL> shutdown immediate
SQL> startup

Cheers!! Database started successfully.

Check the status of the undo segments and determine if all the segments in the old undo tablespace are offline.

sql> select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name, status;

If there are Undo segments with a status other than OFFLINE in the tablespace to be dropped, we need to wait till they become OFFLINE. You may have to wait for the duration of the tuned_undoretention (from v$undostat) to ensure all Undo segments have become OFFLINE.

sql> select status,segment_name from dba_rollback_segs where status not in ("OFFLINE') and tablespace_name=[undo tablespace to be dropped];

For example:

sql> select status,segment_name from dba_rollback_segs where status not in ("OFFLINE') and tablespace_name='UNDOTBS1';

If all the Undo segments in the old Undo tablespace to the dropped is of status OFFLINE, then drop the tablespace.

sql> select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name, status;

Verify and then drop:

sql> drop tablespace [tablespace_name] including contents and datafiles;

For example:

sql> drop tablespace UNDOTBS1 including contents and datafiles;

Please run expdp.sh shell script if find following errers then

ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name "DW00" prematurely terminated
ORA-31671: Worker process DW00 had an unhandled exception.
ORA-00600: internal error code, arguments: [17183], [0x7FAD49A1E198], [], [], [], [], [], [], [], [], [], []

To solve the problem, the value of the open_cursors parameter should be increased and the value of the parameter “_optimizer_cost_based_transformation” should be set to off.

1
2
3
4
5
6
7
SQL> alter system set open_cursors=1024 scope=both sid='*';
 
System altered.
 
SQL> alter system set "_optimizer_cost_based_transformation"=off scope=both sid='*';
 
System altered.

Alternativley, for windows use the Database admin assistant that comes with oracle. Click Oracle Managed Objects -->Computers --> databases --> <your database name>

Right click and click 'stop service'. And then again right click and 'start service'. Presto, you are done !

Hi all,

i had this problem, and i solved it (Thanks God).

Open Dos command Prompt

c:> sqlplus /nolog

SQL> connect sys/manager as sysdba

Connected.

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 161966464 bytes

Fixed Size 887988 bytes

Variable Size 135750508 bytes

Database Buffers 25165824 bytes

Redo Buffers 262144 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 6 - see DBWR trace file

ORA-01110: data file 6: 'E:\oracle\product\10.2.0\oradata\orcl\UNDOTBS01.DBF'

I realized what happened and how this happened. It's all my fault. During a clean-up effort this file was accidentally deleted.

so i did the folowing:

Mount the database and drop the datafile using

alter database datafile '<<file_name>>' offline drop;

and then open the database

alter database open;

ORA-00204: error in reading of control file

Error: ORA 204

Link

Text: error in reading control file <name> block <num>, # blocks <num>

-------------------------------------------------------------------------------

Cause: A disk read-failure occurred while attempting to read the specified

control file.

The block location of the failure is given.

Action: Check that the disk is online.

If it is not, bring it online and shut down and restart Oracle.

If the disk is online, then look for operating system reasons for

Oracle's inability to read the disk or control file.

Using Disk Management

To start Disk Management:

Log on as an administrator or as a member of the Administrators group.

Click Start, and then click Control Panel.

Click Performance and Maintenance, click Administrative Tools, and then double-click Computer Management. In the console tree, click Disk Management.

NOTE: You can also click Start, point to Run, type diskmgmt.msc, and then click OK.

In Disk Management, the computer's disks and volumes are displayed in a graphical view and in a list view. By default, the local computer's disks are displayed; however, if you have the required permissions, you can perform this process on remote computer.

To customize the view of the disks and the volumes in the upper and the lower panes of the window, click View, point to Top or to Bottom, and then click the view that you want.

NOTE: It is recommended that you perform a full backup of disk contents before you make any changes to disks, volumes, or partitions.

Use the mutiplxed controlfile if you have already available to start your instance...

SQL>show parameter control_files;

--Multiplex control files

You do NOT need to create a new sp file, nor do you need to create a pfile from the spfile. You simply need to alter the control_files parameter whilst the database is up and running and add the scope = spfile onto the end of the code:

Code:

alter system set control_files = 'ctrl1','ctrl2','ctrln'

scope = spfile

Then shutdown the db, copy the controlfile and start the db.

CREATE SYNONYM

CREATE SYNONYM: Examples To define the synonym offices for the table locations in the schema hr, issue the following statement:

CREATE SYNONYM offices 
   FOR hr.locations;

To create a PUBLIC synonym for the employees table in the schema hr on the remote database, you could issue the following statement:

CREATE PUBLIC SYNONYM emp_table 
   FOR hr.employees@remote.us.oracle.com;

A synonym may have the same name as the underlying object, provided the underlying object is contained in another schema.

Oracle Database Resolution of Synonyms: Example Oracle Database attempts to resolve references to objects at the schema level before resolving them at the PUBLIC synonym level. For example, the schemas oe and sh both contain tables named customers. In the next example, user SYSTEM creates a PUBLIC synonym named customers for oe.customers:

CREATE PUBLIC SYNONYM customers FOR oe.customers;

If the user sh then issues the following statement, then the database returns the count of rows from sh.customers:

SELECT COUNT(*) FROM customers;

To retrieve the count of rows from oe.customers, the user sh must preface customers with the schema name. (The user sh must have select permission on oe.customers as well.)

SELECT COUNT(*) FROM oe.customers;

If the user hr's schema does not contain an object named customers, and if hr has select permission on oe.customers, then hr can access the customers table in oe's schema by using the public synonym customers:

SELECT COUNT(*) FROM customers;

JOB & SCHEDULER

Link

Creating Detached Jobs

A detached job must point to a program object (program) that has its detached attribute set to TRUE.

Example 27-7 Creating a Detached Job That Performs a Cold Backup

This example for Linux and UNIX creates a nightly job that performs a cold backup of the database. It contains three steps.

Step 1—Create the Script That Invokes RMAN
Create a shell script that calls an RMAN script to perform a cold backup. The shell script is located in /home/oracle/scripts/coldbackup.sh. It must be executable by the user who installed Oracle Database (typically the user oracle).

#!/bin/sh
 
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export ORACLE_SID=amin

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
 
$ORACLE_HOME/bin/rman TARGET / home/oracle/scripts/coldbackup.rman
  trace /u01/app/oracle/backup/coldbackup.out &
exit 0

Step 2—Create the RMAN Script

Create an RMAN script that performs the cold backup and then ends the job. The script is located in /home/oracle/scripts/coldbackup.rman.

run {
# Shut down database for backups and put into MOUNT mode
shutdown immediate
startup mount
 
# Perform full database backup
backup full format "/u01/app/oracle/backup/%d_FULL_%U" (database) ;
 
# Open database after backup
alter database open;
 
# Call notification routine to indicate job completed successfully
sql " BEGIN  DBMS_SCHEDULER.END_DETACHED_JOB_RUN(''sys.backup_job'', 0,
  null); END; ";
}

Step 3—Create the Job and Use a Detached Program

Submit the following PL/SQL block:

BEGIN
  DBMS_SCHEDULER.CREATE_PROGRAM(
    program_name   => 'sys.backup_program',
    program_type   => 'executable',
    program_action => '?/scripts/coldbackup.sh',
    enabled        =>  TRUE);

  DBMS_SCHEDULER.SET_ATTRIBUTE('sys.backup_program', 'detached', TRUE);
 
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'sys.backup_job',
    program_name    => 'sys.backup_program',
    repeat_interval => 'FREQ=DAILY;BYHOUR=1;BYMINUTE=0');

  DBMS_SCHEDULER.ENABLE('sys.backup_job');
END;
/

How to copy data from one database/table to another database/table

In a typical Oracle environment, you have TNS names set up. That's a service to lookup the connection parameters for Oracle instances given an SID or service name. In it's simplest form, TNS names is a file called tnsnames.ora located by the environment variable TNS_ADMIN (which points to the directory where the file is).
Given the SIDs PROD and SANDBOX, you can then copy the tables:

COPY FROM username1/passwd1@PROD to username2/passwd2@SANDBOX
    INSERT TABLE_C (*) USING (SELECT * FROM TABLE_C WHERE COL_A = 4884);

If you don't have TNS names set up, you'll need to know the host name or IP address, the port number and the service name. The syntax then becomes:

COPY FROM username1/passwd1@//192.168.3.17:1521/PROD_SERVICE to username2/passwd2@//192.168.4.17:1521/SANDBOX_SERVICE
    INSERT TABLE_C (*) USING (SELECT * FROM TABLE_C WHERE COL_A = 4884);

To determine the SID and/or service name, you best have a look into the TNSNAMES.ORA file on the database server itself. If you are able to login to the database, you can use the following queries to determine the SID and service name (but don't ask me which is which):

select name from v$database;

select * from global_name;

select instance_number, instance_name, host_name from v$instance;

PURGE - Oracle Database 11g

Use the PURGE statement to remove a table or index from your recycle bin and release all of the space associated with the object, or to remove the entire recycle bin, or to remove part of all of a dropped tablespace from the recycle bin.

To see the contents of your recycle bin, query the USER_RECYCLEBIN data dictionary review. You can use the RECYCLEBIN synonym instead. The following two statements return the same rows:

SELECT * FROM RECYCLEBIN;
SELECT * FROM USER_RECYCLEBIN;

Examples
Remove a File From Your Recycle Bin: Example The following statement removes the table test from the recycle bin. If more than one version of test resides in the recycle bin, then Oracle Database removes the version that has been there the longest:

PURGE TABLE test;

To determine system-generated name of the table you want removed from your recycle bin, issue a SELECT statement on your recycle bin. Using that object name, you can remove the table by issuing a statement similar to the following statement. (The system-generated name will differ from the one shown in the example.)

PURGE TABLE RB$$33750$TABLE$0;

Remove the Contents of Your Recycle Bin: Example To remove the entire contents of your recycle bin, issue the following statement:

PURGE RECYCLEBIN;

How do I find all code, triggers from an oracle database that relate to specific tables?

So, to find all the table names and public synonyms with sqlplus:
select table_name from all_tables where table_name like 'DAP%';

select synonym_name from all_synonyms where table_name like 'DAP%';

To get a list of triggers and sequences
select trigger_name from all_triggers where table_name like 'DAP%';

select sequence_name from all_sequences where sequence_name like 'DAP%';

To get a list of all the constraints

select table_name, constraint_name from all_constraints where table_name like 'DAP%';

To get the DAP related code:

select text from dba_source where name like 'DAP%';

SELECT object_name, object_type, status FROM user_objects;

I can now write a script that drops everything.

How to create Audit Tables in a database along with database Trigger

If you had a table created as follows:

CREATE TABLE orders
( order_id number(5),
  quantity number(4),
  cost_per_item number(6,2),
  total_cost number(8,2)
);

We could then create an AFTER INSERT trigger as follows:

CREATE OR REPLACE TRIGGER orders_after_insert
AFTER INSERT
   ON orders
   FOR EACH ROW
   
DECLARE
   v_username varchar2(10);
   
BEGIN
   
   -- Find username of person performing the INSERT into the table
   SELECT user INTO v_username
   FROM dual;
   
   -- Insert record into audit table
   INSERT INTO orders_audit
   ( order_id,
     quantity,
     cost_per_item,
     total_cost,
     username )
   VALUES
   ( :new.order_id,
     :new.quantity,
     :new.cost_per_item,
     :new.total_cost,
     v_username );
     
END;

Statement Level Trigger With  Multiple Actions

CREATE OR REPLACE TRIGGER statement_level AFTER INSERT OR UPDATE OR DELETE
ON orders

DECLARE
vMsg VARCHAR2(30) := 'Statement Level Trigger Fired';
BEGIN
IF INSERTING THEN
    dbms_output.put_line(vMsg || ' When Inserting');
ELSIF UPDATING THEN
    dbms_output.put_line(vMsg || ' When Updating');
ELSIF DELETING THEN
    dbms_output.put_line(vMsg || ' When Deleting');
END IF;
END statement_level;
/

set serveroutput on

INSERT INTO orders (somecolumn) VALUES ('ABC');

UPDATE orders SET somecolumn = 'DEF' WHERE ROWNUM = 1;

DELETE FROM orders WHERE ROWNUM = 1;

CREATE OR REPLACE TRIGGER statement_levelBEFORE INSERT OR UPDATE OR DELETE
ON orders

DECLARE
vMsg VARCHAR2(30) := 'Statement Level Trigger Fired';
BEGIN
IF INSERTING THEN
    dbms_output.put_line(vMsg || ' When Inserting');
ELSIF UPDATING THEN
    dbms_output.put_line(vMsg || ' When Updating');
ELSIF DELETING THEN
    dbms_output.put_line(vMsg || ' When Deleting');
END IF;
END statement_level;
/

set serveroutput on

INSERT INTO orders (somecolumn) VALUES ('ABC');

UPDATE orders SET somecolumn = 'DEF' WHERE ROWNUM = 1;

DELETE FROM orders WHERE ROWNUM = 1;

rigger With Autonomous Transaction

The same simple trigger as an autonomous transaction

ALTER TABLE audit_log
ADD (commentcol VARCHAR2(50));

desc audit_log

CREATE OR REPLACE TRIGGER t_autonomous_tx
BEFORE INSERT
ON person

DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO audit_log
(chng_when, commentcol)
VALUES
(SYSDATE, 'Reporting an error');
COMMIT;
END t_autonomous_tx;
/

INSERT INTO person (fname) VALUES ('abcdefghijklmnopqrst');

SELECT * FROM person;
SELECT chng_when, commentcol FROM audit_log;

:new and :old are like bind variables to the trigger, they are not 'regular' variables.  
you cannot dynamically access them, only 'statically'.

I suggest you consider writing a stored procedure or sql*plus script to write a trigger 
that statically references the new/old values.  For example, if you wanted to save in a 
table the time of update, who updated, table updated, column modified and new/old values, 
you could code a sql*plus script like:


--------------------------------------------------------------------
create table audit_tbl
(    timestamp    date,
    who            varchar2(30),
    tname        varchar2(30),
    cname        varchar2(30),
    old            varchar2(2000),
    new            varchar2(2000)
)
/

create or replace package audit_pkg
as
    procedure check_val( l_tname in varchar2, 
                             l_cname in varchar2, 
                 l_new in varchar2, 
                             l_old in varchar2 );

    procedure check_val( l_tname in varchar2, 
                             l_cname in varchar2, 
                     l_new in date, 
                             l_old in date );

    procedure check_val( l_tname in varchar2, 
                             l_cname in varchar2, 
                 l_new in number, 
                             l_old in number );
end;
/


create or replace package body audit_pkg
as

procedure check_val( l_tname in varchar2,
                     l_cname in varchar2,
             l_new in varchar2,
                     l_old in varchar2 )
is
begin
    if ( l_new <> l_old or
         (l_new is null and l_old is not NULL) or
         (l_new is not null and l_old is NULL) )
    then
        insert into audit_tbl values
        ( sysdate, user, upper(l_tname), upper(l_cname),
                             l_old, l_new );
    end if;
end;

procedure check_val( l_tname in varchar2, l_cname in varchar2,
             l_new in date, l_old in date )
is
begin
    if ( l_new <> l_old or
         (l_new is null and l_old is not NULL) or
         (l_new is not null and l_old is NULL) )
    then
        insert into audit_tbl values
        ( sysdate, user, upper(l_tname), upper(l_cname),
          to_char( l_old, 'dd-mon-yyyy hh24:mi:ss' ),
          to_char( l_new, 'dd-mon-yyyy hh23:mi:ss' ) );
    end if;
end;

procedure check_val( l_tname in varchar2, l_cname in varchar2,
             l_new in number, l_old in number )
is
begin
    if ( l_new <> l_old or
         (l_new is null and l_old is not NULL) or
         (l_new is not null and l_old is NULL) )
    then
        insert into audit_tbl values
        ( sysdate, user, upper(l_tname), upper(l_cname),
                                 l_old, l_new );
    end if;
end;

end audit_pkg;
/
set serveroutput on
set feedback off
set verify off
set embedded on
set heading off
spool tmp.sql

prompt create or replace trigger aud#&1
prompt after update on &1
prompt for each row
prompt begin

select '    audit_pkg.check_val( ''&1'', ''' || column_name ||
          ''', ' || ':new.' || column_name || ', :old.' || 
             column_name || ');'
from user_tab_columns where table_name = upper('&1')
/
prompt end;;
prompt /

spool off
set feedback on
set embedded off
set heading on
set verify on

@tmp
-------------

That will build the generic table and package plus generate a trigger that would look 
like:

SQL> @thatscript dept


create or replace trigger aud#dept
after update on dept
for each row
begin
    audit_pkg.check_val( 'dept', 'DEPTNO', :new.DEPTNO, :old.DEPTNO);
    audit_pkg.check_val( 'dept', 'DNAME', :new.DNAME, :old.DNAME);
    audit_pkg.check_val( 'dept', 'LOC', :new.LOC, :old.LOC);
end;
/

http://www.astral-consultancy.co.uk/cgi-bin/hunbug/doco.cgi?11020

Configuring Auditing in Oracle Database 11g
http://www.comp.dit.ie/btierney/oracle11gdoc/network.111/b28531/auditing.htm
Auditing SQL Statements
Auditing Privileges
Auditing Schema Objects
Auditing SQL Statements and Privileges in a Multitier Environment
Auditing Network Activity
Using Fine-Grained Auditing to Monitor Specific Activities
http://sqltech.cl/doc/oracle11g/network.111/b28531/auditing.htm#CEGGCIAE

Hierarchical Queries

Queries

Column Privileges

Only INSERT, UPDATE, and REFERENCES privileges can be granted at the column level. When granting INSERT at the column level, you must include all the not null columns in the row.

Here is an example:

GRANT update (emp_name)
ON edwin.emp
TO joan;

for more detail

Find and delete out Duplicate Record

select stayid,dramt,cramt,count(1) from arledger

where dramt > 0

group by stayid,dramt,cramt

having count(1) > 1

order by stayid;

select * from arledger a

where rowid > (select min(rowid) from arledger b

where a.stayid = b.stayid);

delete from arledger a

where rowid > (select min(rowid) from arledger b

where a.stayid = b.stayid);

Create Profile

Examples

Creating a Profile: Example The following statement creates the profile new_profile:

CREATE PROFILE new_profile
  LIMIT PASSWORD_REUSE_MAX 10
        PASSWORD_REUSE_TIME 30;

Setting Profile Resource Limits: Example The following statement creates the profile app_user:

CREATE PROFILE app_user LIMIT 
   SESSIONS_PER_USER          UNLIMITED 
   CPU_PER_SESSION            UNLIMITED 
   CPU_PER_CALL               3000 
   CONNECT_TIME               45 
   LOGICAL_READS_PER_SESSION  DEFAULT 
   LOGICAL_READS_PER_CALL     1000 
   PRIVATE_SGA                15K
   COMPOSITE_LIMIT            5000000;

If you assign the app_user profile to a user, the user is subject to the following limits in subsequent sessions:

The user can have any number of concurrent sessions.
In a single session, the user can consume an unlimited amount of CPU time.
A single call made by the user cannot consume more than 30 seconds of CPU time.
A single session cannot last for more than 45 minutes.
In a single session, the number of data blocks read from memory and disk is subject to the limit specified in the DEFAULT profile.
A single call made by the user cannot read more than 1000 data blocks from memory and disk.
A single session cannot allocate more than 15 kilobytes of memory in the SGA.
In a single session, the total resource cost cannot exceed 5 million service units. The formula for calculating the total resource cost is specified by the ALTER RESOURCE COST statement.
Since the app_user profile omits a limit for IDLE_TIME and for password limits, the user is subject to the limits on these resources specified in the DEFAULT profile.

Setting Profile Password Limits: Example The following statement creates the app_user2 profile with password limits values set:

CREATE PROFILE app_user2 LIMIT
   FAILED_LOGIN_ATTEMPTS 5
   PASSWORD_LIFE_TIME 60
   PASSWORD_REUSE_TIME 60
   PASSWORD_REUSE_MAX 5
   PASSWORD_VERIFY_FUNCTION verify_function
   PASSWORD_LOCK_TIME 1/24
   PASSWORD_GRACE_TIME 10;

This example uses the default Oracle Database password verification function, verify_function. Please refer to Oracle Database Security Guide for information on using this verification function provided or designing your own verification function.

Alter Profile

Examples

Making a Password Unavailable: Example The following statement makes the password of the new_profile profile (created in "Creating a Profile: Example") unavailable for reuse for 90 days:

ALTER PROFILE new_profile 
   LIMIT PASSWORD_REUSE_TIME 90 
   PASSWORD_REUSE_MAX UNLIMITED;

Setting Default Password Values: Example The following statement defaults the PASSWORD_REUSE_TIME value of the app_user profile (created in "Setting Profile Resource Limits: Example") to its defined value in the DEFAULT profile:

ALTER PROFILE app_user 
   LIMIT PASSWORD_REUSE_TIME DEFAULT
   PASSWORD_REUSE_MAX UNLIMITED;

Limiting Login Attempts and Password Lock Time: Example The following statement alters profile app_user with FAILED_LOGIN_ATTEMPTS set to 5 and PASSWORD_LOCK_TIME set to 1:

ALTER PROFILE app_user LIMIT
   FAILED_LOGIN_ATTEMPTS 5
   PASSWORD_LOCK_TIME 1;

This statement causes the app_user account to become locked for one day after five unsuccessful login attempts.

Changing Password Lifetime and Grace Period: Example The following statement modifies the profile app_user2 PASSWORD_LIFE_TIME to 90 days and PASSWORD_GRACE_TIME to 5 days:

ALTER PROFILE app_user2 LIMIT
   PASSWORD_LIFE_TIME 90
   PASSWORD_GRACE_TIME 5;

Limiting Concurrent Sessions: Example This statement defines a new limit of 5 concurrent sessions for the app_user profile:

ALTER PROFILE app_user LIMIT SESSIONS_PER_USER 5;

If the app_user profile does not currently define a limit for SESSIONS_PER_USER, the preceding statement adds the limit of 5 to the profile. If the profile already defines a limit, the preceding statement redefines it to 5. Any user assigned the app_user profile is subsequently limited to 5 concurrent sessions.

Removing Profile Limits: Example This statement removes the IDLE_TIME limit from the app_user profile:

ALTER PROFILE app_user LIMIT IDLE_TIME DEFAULT;

Any user assigned the app_user profile is subject in their subsequent sessions to the IDLE_TIME limit defined in the DEFAULT profile.

Limiting Profile Idle Time: Example This statement defines a limit of 2 minutes of idle time for the DEFAULT profile:

ALTER PROFILE default LIMIT IDLE_TIME  2;

This IDLE_TIME limit applies to these users:

Users who are not explicitly assigned any profile
Users who are explicitly assigned a profile that does not define an IDLE_TIME limit

This statement defines unlimited idle time for the app_user2 profile:

ALTER PROFILE app_user2 LIMIT IDLE_TIME UNLIMITED;

Any user assigned the app_user2 profile is subsequently permitted unlimited idle time.

Link

SP2-1503: Unable to initialize oracle call interface

SP2-0152: ORACLE may not be functioning perperly

To all who are looking for a solution to this....

I had the same issue above. I installed Oracle 10G R2 on my Windows 7 laptop. Though I had no issues during install I wasn't able to run sqlplus, either via the gui or through the command prompt. I didn't find anyone covering a solution for this besides installing Oracle 11.6, but I did find a solution.

Run sqlplus as administrator. I just right-clicked on the shortcut for the gui app and selected "Run as Administrator" and SQLPlus ran just fine. Right click on cmd icon select 'Run as Administrator'.

1. Access User Control Panel from Start Menu -> Control Panel -> User Accounts and Family Safety -> User Account

2. Click on User Account Control settings link.

3. Move the Slider to Never Notify

4. Click OK to make the change effective

5. Reboot.

Hope this helps someone.
Link
When trying to import the Oracle export dump into Oracle database, the following errors occur:
IMP-00003: ORACLE error 3113 encountered
ORA-03113: end-of-file on communication channel
IMP-00003: ORACLE error 3114 encountered
ORA-03114: not connected to ORACLE

The solution to solve the problem is as following:
Login as sys in the SQLPLUS and run the following sqls
$OH/rdbms/admin/dbmsread.sql
$OH/rdbms/admin/prvtread.plb
After executing the above sql scripts, retry the import. The error should disappears.

OR
select count(*) from dba_constraints where status='ENABLED' and owner='ABC'; ---> compare in 11g (if same no need to proceed further)

IF not run the below command on 9i and spool the output

select 'alter table '||owner||'. '||table_name||' enable constraint '||constraint_name||' ;' from dba_constraints where status='ENABLED' and owner='ABC';

Run the spooled file (which has the result of the above command) on 11g.

ORA-00214: control file version inconsistent or corrupted

Link

ORA-00214: control file version inconsistent

Due to the power outage, one of my production database giving the following error.

When I try to startup the database the ORA-00214 controlfile inconsistent error occurred:

SQL> startup

ORACLE instance started.

Total System Global Area 662700032 bytes

Fixed Size 1250716 bytes

Variable Size 281021028 bytes

Database Buffers 373293056 bytes

Redo Buffers 7135232 bytes

ORA-00214: control file 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\SYMP\CONTROL03.CTL'

version 1021304 inconsistent with file

'C:\ORACLE\PRODUCT\10.2.0\ORADATA\SYMP\CONTROL01.CTL' version 1021301

Error: ORA-00214

Cause: An ORA-00214 is issued whenever Oracle detects an inconsistency between two mirrored copies of the control file.

All copies of the control file must have the same internal sequence number for Oracle to start up the database or shut it down in normal or immediate mode.

Action: Use a consistent set of control files.

That is, all the files must be for the same database and from same time period.

Before going to troubleshoot the above error, Please follow the Metalink Doc ID 1014751.6

Action plan:

OS: Windows 2003 R2 32-bit

Database version: 10.2.0.1

Step1: Find the controlfile location:

SQL> show parameter control

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

control_file_record_keep_time integer 7

control_files string C:\ORACLE\PRODUCT\10.2.0\ORADA

TA\SYMP\CONTROL01.CTL, C:\ORAC

LE\PRODUCT\10.2.0\ORADATA\SYMP

\CONTROL02.CTL, C:\ORACLE\PROD

UCT\10.2.0\ORADATA\SYMP\CONTRO

L03.CTL

Step2: Create and Edit the pfile from spfile:

SQL> create pfile='C:\oracle\product\10.2.0\db_1\dbs\inittest.ora' from spfile;

File created.

LINUX

SQL> SHOW PARAMETER spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/db_1/dbs/spfilesh1.ora
SQL> create pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initest.ora' from spfile;

File created.

Step3: Remove the multiplexing controlfile of control_files parameter

Control_file=c:\oracle\product\10.2.0\oradata\symp\control01.ctl

Save

LINUX

SQL> show parameter control

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

control_file_record_keep_time integer 7

control_files string /u01/app/oracle/oradata/sh1/co

ntrol01.ctl, /u01/app/oracle/f

lash_recovery_area/sh1/control

02.ctl

control_management_pack_access string DIAGNOSTIC+TUNING

SQL>

Note:

Copy control02 from flash_recovery_area (sid folder) to oradata (sid folder)

Rename control02 as control01 in oradata foler.

LINUX

Rename
[oracle@localhost ~]$mv /u01/app/oracle/oradata/sr/control01.ctl /u01/app/oracle/oradata/sr/control01_bk.ctl

Copy
[oracle@localhost ~]$cp /u01/app/oracle/flash_recovery_area/sr/control02.ctl /u01/app/oracle/oradata/sr/control01.ctl

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL>

Now try to start the edited pfile:

SQL> startup nomount pfile='C:\oracle\product\10.2.0\db_1\dbs\inittest.ora'

SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initest.ora'

ORACLE instance started.

Total System Global Area 662700032 bytes

Fixed Size 1250716 bytes

Variable Size 281021028 bytes

Database Buffers 373293056 bytes

Redo Buffers 7135232 bytes

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1337720 bytes
Variable Size 176162440 bytes
Database Buffers 352321536 bytes
Redo Buffers 5840896 bytes
SQL>

Step4: Try to mount & open the database with pfile:

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

Step5: Now try to remove the multiplexed controlfile at os level.

C:\oracle\product\10.2.0\oradata>cd symp

C:\oracle\product\10.2.0\oradata\symp>dir

Volume in drive C has no label.

Volume Serial Number is 8048-4083

Directory of C:\oracle\product\10.2.0\oradata\symp

7,290,880 CONTROL01.CTL

7,290,880 CONTROL02.CTL

7,290,880 CONTROL03.CTL

524,296,192 IDCARD_TS.DBF

52,429,312 REDO01.LOG

52,429,312 REDO02.LOG

52,429,312 REDO03.LOG

377,495,552 SYSAUX01.DBF

513,810,432 SYSTEM01.DBF

269,492,224 TEMP01.DBF

68,165,632 UNDOTBS01.DBF

17,047,552 USERS01.DBF

12 File(s) 1,949,468,160 bytes

2 Dir(s) 245,401,767,936 bytes free

C:\oracle\product\10.2.0\oradata\symp>del CONTROL02.CTL

C:\oracle\product\10.2.0\oradata\symp>del CONTROL03.CTL

C:\oracle\product\10.2.0\oradata\symp>dir

Volume in drive C has no label.

Volume Serial Number is 8048-4083

Step6: Multiplex the controlfile with existing copy of controlfile (controlfile01.ctl) :

C:\oracle\product\10.2.0\oradata\symp>copy CONTROL01.CTL CONTROL02.CTL

1 file(s) copied.

C:\oracle\product\10.2.0\oradata\symp>copy CONTROL01.CTL CONTROL03.CTL

1 file(s) copied.

Step7: Start the database with existing default spfile:

SQL> STARTUP

Database started.

Now the database was successfully started without controlfile error.

let see the controlfile location:

SQL> Show parameter control

control_files='C:\oracle\product\10.2.0\oradata\symp\control01.ctl','C:\oracle\product\10.2.0\oradata\symp\control02.ctl','C:\oracle\product\10.2.0\oradata\symp\control03.ctl'

Please follow the Metalink Doc ID 1014751.6

OR

SQL> startup mount pfile='c:\p.txt';

SQL>rman target /

RMAN>restore controlfile from autobackup;

RMAN> quit

SQL>shut immediate

SQL>startup mount;

ORACLE instance started.
Total System Global Area 3206836224 bytes
Fixed Size                  2180024 bytes
Variable Size            2415922248 bytes
Database Buffers          771751936 bytes
Redo Buffers               16982016 bytes
ORA-00214: control file
'D:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL' version 145625
inconsistent with file 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\CONTROL01.CTL' version 144010

SQL> shut immediate
ORA-01507: database not mounted


ORACLE instance shut down.

SQL>startup force mount pfile='c:\p.txt';

ORACLE instance started.
Total System Global Area 3206836224 bytes
Fixed Size                  2180024 bytes
Variable Size            2415922248 bytes
Database Buffers          771751936 bytes
Redo Buffers               16982016 bytes
Database mounted.
SQL>host rman target /

RMAN>recover database;

RMAN> alter database open resetlogs;

database opened.

SQL> shutdown abort

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1640484864 bytes
Fixed Size 1336876 bytes
Variable Size 1090521556 bytes
Database Buffers 536870912 bytes
Redo Buffers 11755520 bytes
SQL>

SQL> SHOW PARAMETER spfile;

SQL> create pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/pfileshisb.ora' from spfile='/u01/app/oracle/product/11.2.0/db_1/dbs/spfileshisb.ora';

File created.

SQL> show parameter control

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /u01/app/oracle/oradata/sh1/co
ntrol01.ctl, /u01/app/oracle/f
lash_recovery_area/sh1/control
02.ctl
control_management_pack_access string DIAGNOSTIC+TUNING
SQL>

SQL> control_file=/u01/app/oracle/oradata/shisb/control01.ctl

SQL> shutdown abort;

SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/pfileshisb.ora';

ORACLE instance started.

Total System Global Area 1640484864 bytes

Fixed Size 1336876 bytes

Variable Size 1090521556 bytes

Database Buffers 536870912 bytes

Redo Buffers 11755520 bytes

SQL>

“ORA-03113: end-of-file on communication channel” on startup
Link

After hours of misdirection from official Oracle support, I dove into this on my own and fixed it. I am documenting it here

in case someone else has this problem.

To do any of this, you must be the oracle user:

$ su - oracle

Step 1: You need to look at the alert log. It isn't in /var/log as expected. You have to run an Oracle log reading program:

$ adrci

ADRCI: Release 11.2.0.1.0 - Production on Wed Sep 11 18:27:56 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

ADR base = "/u01/app/oracle"

adrci>

Notice the ADR base. That is not the install. You need to see the homes so you can connect to the one that you use.

adrci> show homes

ADR Homes:

diag/rdbms/cci/CCI

diag/tnslsnr/cci/listener

diag/tnslsnr/cci/start

diag/tnslsnr/cci/reload

CCI is the home. Set that.

adrci> set home diag/rdbms/cci/CCI

adrci>

Now, you can look at the alert logs. It would be very nice if they were in /var/log so you could easily parse the logs.

Just stop wanting and deal with this interface. At least you can tail (and I hope you have a scrollback buffer):

adrci> show alert -tail 100

Scroll back until you see errors. You want the FIRST error. Any errors after the first error are likely being caused by the

first error. In my case, the first error was:

ORA-19815: WARNING: db_recovery_file_dest_size of 53687091200 bytes is 100.00% used, and has 0 remaining bytes available.

This is caused by transactions. Oracle is not designed to be used. If you do push a lot of data into it, it saves

transaction logs. Those go into the recovery file area. Once that is full (50GB full in this case). Then, Oracle just dies.

By design, if anything is messed up, Oracle will respond by shutting down.

There are two solutions, the proper one and the quick and dirty one. The quick and dirty one is to increase

db_recovery_file_dest_size. First, exit adrci.

adrci> exit

Now, go into sqlplus without opening the database, just mounting it (you may be able to do this without mounting the

database, but I mount it anyway).

$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 11 18:40:25 2013

Copyright (c) 1982, 2009, Oracle. All rights reserved.

SQL> connect / as sysdba

Connected.

SQL> startup mount

Now, you can increase your current db_recovery_file_dest_size, increased to 75G in my case:

SQL> alter system set db_recovery_file_dest_size = 120G scope=both

Now, you can shutdown and startup again and that previous error should be gone.

The proper fix is to get rid of the recovery files. You do that using RMAN, not SQLPLUS or ADRCI.

$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Sep 11 18:45:11 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> backup archivelog all delete input;

Wait a long time and your archivelog (that was using up all that space) will be gone. So, you can shutdown/startup your

database and be beck in business.

$ rman target /

RMAN> SHOW ALL;

RMAN> LIST BACKUP SUMMARY;

RMAN> CROSSCHECK BACKUP;

RMAN> DELETE OBSOLETE;

RMAN> DELETE NOPROMPT OBSOLETE;

RMAN> DELETE OBSOLETE RECOVERY WINDOW OF 10 DAYS;

RMAN> LIST BACKUP SUMMARY;

RMAN> DELETE EXPIRED BACKUP;

log in as root and execute command "ipcs". should you see ipcs owned by the oracle user, delete them using the "ipcrm" command, and everything will work fine again

ORA-12505: TNS:listener does not currently know of SID given in connect descriptor

Link

Solution:

alter system set LOCAL_LISTENER="(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.240)(PORT=1521))";

alter system register;

Fix for ORA-03113: end-of-file on communication channel

Link

Solved

connect / as sysdba

SQL> SELECT * FROM V$RECOVERY_FILE_DEST;

SQL> SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 200G SCOPE=BOTH SID='*';

If still not resolved then

1. rename flash_recovery_area as flash_recovery_area_bck
2. create new folder flash_recovery_area on same location.
3. connect / as sysdba
4. SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 200G SCOPE=BOTH SID='*';
5. restart/reboot the server and check database will be up.

ORA-12560: TNS:protocol adapter error occurred

Link
Possible Remedies:

* If running on the server (or workstation) that is running the database (i.e. if the database is local to the machine) then try explicitly setting 'ORACLE_SID'. e.g.

set ORACLE_SID=<sid>

and then try again. If this works then consider whether to make the change permanent by specifying it as a system variable.

sounds like the database is not up. you probably restarted your machine and the instance is not set to autostart

C:\Documents and Settings\tkyte>sqlplus scott/tiger

SQL*Plus: Release 10.1.0.4.0 - Production on Thu Sep 6 15:43:55 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options

scott%ORA10GR1> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options

C:\Documents and Settings\tkyte><b>net stop oracleserviceora10gr1
The OracleServiceORA10GR1 service is stopping.........
The OracleServiceORA10GR1 service was stopped successfully.
</b>

C:\Documents and Settings\tkyte>sqlplus scott/tiger

SQL*Plus: Release 10.1.0.4.0 - Production on Thu Sep 6 15:44:35 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.
<b>
ERROR:
ORA-12560: TNS:protocol adapter error

</b>
Enter user-name:
ERROR:
ORA-12560: TNS:protocol adapter error


Enter user-name:
ERROR:
ORA-12560: TNS:protocol adapter error


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
<b>
C:\Documents and Settings\tkyte>net start oracleserviceora10gr1
The OracleServiceORA10GR1 service is starting..........
The OracleServiceORA10GR1 service was started successfully.
</b>

C:\Documents and Settings\tkyte>sqlplus scott/tiger

SQL*Plus: Release 10.1.0.4.0 - Production on Thu Sep 6 15:45:12 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options

scott%ORA10GR1>

in the above ora10gr1 is my oracle sid, you can use control panel instead - control panel -> services -> look for the services starting with Oracle.

Start --> Run--> type " services.msc"

Select the OracleService name, Right Click, Start.


Startup Fails With ORA-01012: Not Logged On

Link
Problem Description
Users cant able to login to database server. Though it allow sysdba to login, it does not allow to run any query. Sometimes it will show database is connected to an idle instance. But when we startup database it shall throw error ORA-01081: cannot start already-running ORACLE - shut it down first.

 $ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 12 07:53:11 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select instance_name,status from v$instance;
ERROR at line 1:
ORA-01012: not logged onSQL> desc v$instance
ERROR:
ORA-01012: not logged on

SQL> startup
ORA-01012: not logged on


Changes
Oracle has been forcefully shutdown at OS level or crashed.

 CAUSE
An orphaned shared memory segment belonging to the ORACLE_SID still exists from a previous instance startup.

The command
ps -ef | grep $ORACLE_SID

shows no processes but with ORACLE_SID set the Oracle 'sysresv' utility shows a shared memory segment for a non-existing instance, e.g.

 $ sysresv

IPC Resources for ORACLE_SID "TEST" :

Shared Memory:
ID KEY
5963794 0x00000000
5996563 0x00000000
6029332 0xb2e3c9ac

Semaphores:
ID KEY
No semaphore resources used
Oracle Instance not alive for sid "TEST"


Solution
On OS level, remove the orphaned shared memory segment using:

ipcrm -m <problem shared memory id>

$ ipcrm -m 5963794
$ ipcrm -m 5996563
$ ipcrm -m 6029332

$sqlplus '/as sysdba'

Connected to an idle instance.

SQL> startup Oracle instance started
Total System Global Area 10689474560 bytes
Fixed Size 2237776 bytes
Variable Size 6375344816 bytes
Database Buffers 4294967296 bytes
Redo Buffers 16924672 bytes
Oracle Instance Started.
Oracle Database Opened.


ORA-03113: end-of-file on communication channel



Link

After hours of misdirection from official Oracle support, I dove into this on my own and fixed it. I am documenting it here in case someone else has this problem.

To do any of this, you must be the oracle user:
$ su - oracle

Step 1: You need to look at the alert log. It isn't in /var/log as expected. You have to run an Oracle log reading program:
$ adrci
ADRCI: Release 11.2.0.1.0 - Production on Wed Sep 11 18:27:56 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
ADR base = "/u01/app/oracle"
adrci>

Notice the ADR base. That is not the install. You need to see the homes so you can connect to the one that you use.
adrci> show homes
ADR Homes:
diag/rdbms/cci/CCI
diag/tnslsnr/cci/listener
diag/tnslsnr/cci/start
diag/tnslsnr/cci/reload

CCI is the home. Set that.

adrci> set home diag/rdbms/cci/CCI
adrci>


Now, you can look at the alert logs. It would be very nice if they were in /var/log so you could easily parse the logs. Just stop wanting and deal with this interface. At least you can tail (and I hope you have a scrollback buffer):

adrci> show alert -tail 100


Scroll back until you see errors. You want the FIRST error. Any errors after the first error are likely being caused by the first error. In my case, the first error was:

ORA-19815: WARNING: db_recovery_file_dest_size of 53687091200 bytes is 100.00% used, and has 0 remaining bytes available.


This is caused by transactions. Oracle is not designed to be used. If you do push a lot of data into it, it saves transaction logs. Those go into the recovery file area. Once that is full (50GB full in this case). Then, Oracle just dies. By design, if anything is messed up, Oracle will respond by shutting down.


There are two solutions, the proper one and the quick and dirty one. The quick and dirty one is to increase db_recovery_file_dest_size. First, exit adrci.

adrci> exit


Now, go into sqlplus without opening the database, just mounting it (you may be able to do this without mounting the database, but I mount it anyway).

$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 11 18:40:25 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
SQL> connect / as sysdba
Connected.
SQL> startup mount


Now, you can increase your current db_recovery_file_dest_size, increased to 75G in my case:

SQL> alter system set db_recovery_file_dest_size = 75G scope=both


Now, you can shutdown and startup again and that previous error should be gone.


The proper fix is to get rid of the recovery files. You do that using RMAN, not SQLPLUS or ADRCI.

$ rman
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Sep 11 18:45:11 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
RMAN> backup archivelog all delete input;


If you've got RMAN-06171: not connected to target database, than try to use rman target / instead of just rman


Wait a long time and your archivelog (that was using up all that space) will be gone. So, you can shutdown/startup your database and be back in business.

ORA-01113: file 6 needs media recovery

Link

SQL> shutdown immediate;

SQL> startup mount;

SQL> exit

[oracle@localhost ~]$ rman target /

RMAN> recover database;

Starting recover at 15-NOV-19

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=63 device type=DISK

starting media recovery

media recovery complete, elapsed time: 00:00:01

Finished recover at 15-NOV-19

RMAN> exit

[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Fri Nov 15 17:40:27 2019

Enter user-name: / as sysdba

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup;

ORACLE instance started.

Total System Global Area 1640484864 bytes

Fixed Size 1336876 bytes

Variable Size 1006635476 bytes

Database Buffers 620756992 bytes

Redo Buffers 11755520 bytes

Database mounted.

Database opened.

SQL>

Oracle Reports 6i On Windows 8 Showing the Database Connection Window

Link

I have installed Forms and Reports 6i on Windows8 64 bit. Forms working fine, but run the report from menu it asks the database user name and Password.
Anyone can help me in this regard

I think there is 11g Database on Backend.

By switching the SEC_CASE_SENSITIVE_LOGON initialization parameter to FALSE
you are able to run the reports.run the below query.

ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;

ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist

Link

Open command prompt and execute the below commands:

set oracle_sid=DATABASE NAME
sqlplus /nolog
conn sys/sys as sysdba
shutdown abort
startup

ORA-00845: MEMORY_TARGET not supported on this system (During Starting Instance )

Link

APPLIES TO:

Oracle Database - Enterprise Edition - Version 11.1.0.6 and later
Oracle Database - Standard Edition - Version 11.2.0.1 to 11.2.0.1 [Release 11.2]
Linux x86
Linux x86-64
***Checked for relevance on 30-Sep-2014***

SYMPTOMS

During the startup of the database instance the following error is raised:

SQL> connect sys as sysdba
Enter password: *****
Connected to an idle instance.

SQL> STARTUP NOMOUNT
ORA-00845: MEMORY_TARGET not supported on this system

The instance alert file shows:

Starting ORACLE instance (normal)
WARNING: You are trying to use the MEMORY_TARGET feature.
This feature requires the /dev/shm file system to be mounted for at Least <size> bytes.The /dev/shm is either not mounted or is mounted with available space less than this size.
Please fix this so that MEMORY_TARGET can work as expected. Current available is <size> and used is <size> bytes.memory_target needs larger /dev/shm

Also error messages like ORA-4031 may be reported and no new connections can be established to the database.

CHANGES

The Automatic Memory Management (AMM) has been implemented by setting parameters like MEMORY_TARGET and/or MEMORY_MAX_TARGET.

CAUSE

The new Automatic Memory Management functionality uses /dev/shm on Linux for SGA and PGA management. The errors occur if either MEMORY_TARGET or MEMORY_MAX_TARGET is configured larger than the configured /dev/shm size, or if /dev/shm is mounted incorrectly.

SOLUTION

Please confirm that ORACLE_HOME is set correctly. This error sometimes happens when it is not set correctly.

Make sure that the /dev/shm size is configured large enough, like in:

# mount -t tmpfs shmfs -o size=7g /dev/shm

In this case, the size of the shared memory device is configured to be 7GB.

In order to make the same change persistent across system reboots, add an entry for this to the /etc/fstab mount table, as in:

shmfs /dev/shm tmpfs size=7g 0

Note:

You should check with your System Administrator what the "best" size for /dev/shm is, based on what has been reported in the alert file.
Also, many best practices now suggest disabling AMM especially in Exa* Engineered boxes that have larger memory capability and can use Huge / Large pages.
This is because AMM and Huge / Large pages are mutually exclusive and overall performance will be better using Huge pages

Make sure that the df output shows the correct /dev/shm configuration when using Oracle on the system:

$ df -k
Filesystem 1K-blocks Used Available Use% Mounted on
...
shmfs 6291456 832356 5459100 14% /dev/shm

This document is taken from Oracle Support Doc ID 465048.1

Data Pump job fails with error – ORA-31634: job already exists

Today when I want to export schema with data pump, I encountered with an error ORA-31634: job already exists. I queried dba_datapump_jobs table and saw that it has 99 jobs with the NOT RUNNING state.

SELECT owner_name,
job_name,
operation,
job_mode,
state
FROM dba_datapump_jobs;

When we are not using job name for data pump job, Oracle generates default name to the job and in Oracle data pump can generate up to 99 unique jobs. When job name already exists or you are running many expdp jobs at the same time ( more than 99 jobs), then data pump cannot generate a unique name and you get this error. Another reason why this problem occurs is when jobs are aborted, or when KEEP_MASTER=y used for the data pump the records stay there.

There are two solutions to this problem:

The first solution is dropping this orphaned tables, use result of the query below to drop tables

SELECT 'DROP table ' || owner_name || '.' || job_name || ';'
FROM DBA_DATAPUMP_JOBS
WHERE STATE = 'NOT RUNNING';
DROP TABLE DP_USER.SYS_EXPORT_SCHEMA_01 PURGE;
…
...
DROP TABLE DP_USER.SYS_EXPORT_SCHEMA_99 PURGE;

2) The second solution is to use unique job name in data pump jobs like below:

expdp dp_user/password directory=DP_DIR dumpfile=backup.dmp logfile=logfile.log job_name=your_unique_job_name schemas=schema_name

Now you can run data pump jobs without any problem.

ORA-01658: unable to create INITIAL extent for segment in tablespace SYSTEM
Link
Import terminated successfully with warnings.

ALTER DATABASE DATAFILE 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF' RESIZE 3000M;

Or by adding a second datafile to the tablespace:

ALTER TABLESPACE SYSTEM
ADD DATAFILE 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF' 
SIZE 1000M;

Or just allow the datafile to auto extend:

ALTER DATABASE 
DATAFILE 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF'
AUTOEXTEND ON
MAXSIZE UNLIMITED;

table	package
view	materialized view
sequence	java class schema object
stored procedure	user-defined object
function	synonym

Oracle SQL

Oracle SQLLDR Command Examples (Oracle SQL*Loader

host(); forms 10g

Loading Excel Data Into Oracle Forms 10G

Webutil Configuration Steps

How to Unlock the Account in Oracle

Steps

Creating an Oracle 11g Database using DBCA (non-ASM)

Start the Database Configuration Assistant (DBCA)

deinstall oracle 11g on linux

Auto Backup of Dump File

LINUX

ORACLE 11gR2 does not export (exp) empty tables

Export /Import dump - Oracle 10g/11g -(window)

Export /Import dump - Oracle 10g/11g -(Linux)

Oracle/PLSQL: Synonyms

Creating or replacing a synonym

For Example:

Dropping a synonym

For Example:

Lost Oracle SYS and SYSTEM password?

ORA-01033: ORACLE Initialization or Shutdown in progress in Windows

ORA-01194: file 1 needs more recovery to be consistent

When database started successfully and up then perform the following steps

CREATE SYNONYM

JOB & SCHEDULER

Creating Detached Jobs

How to copy data from one database/table to another database/table

PURGE - Oracle Database 11g

How do I find all code, triggers from an oracle database that relate to specific tables?

Create Profile

Alter Profile

ORA-00214: control file version inconsistent or corrupted

OR

Startup Fails With ORA-01012: Not Logged On

Oracle Reports 6i On Windows 8 Showing the Database Connection Window

ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist

ORA-00845: MEMORY_TARGET not supported on this system (During Starting Instance )

No comments:

Post a Comment