ORACLE DATABASE PROBLEM AND SOLUTIONS: April 2013

Friday, April 26, 2013

TO CONNECT USING SHELL SCRIPTING FROM ROOT USER (CRON TAB)

PATH=$PATH:$HOME/bin

export PATH

ORACLE_SID=orcl
ORACLE_BASE=/oracle/product/10.2.0
ORACLE_HOME=$ORACLE_BASE/db_1
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID ORACLE_BASE ORACLE_HOME PATH

sqlplus -s hr/hr@orcl @ /home/oracle/Desktop/a.sql

CONFIGURE SQLDEVELOPER TOOL

ON LINUX ENVIRONMENT

FIRST STEP DOWNLOAD

http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

SECOND STEP DOWNLOAD JAVA

http://www.oracle.com/technetwork/java/javase/downloads/index.html

SET PATH IN SQLDEVELOPER.CONF
SetJavaHome /usr/java/jdk1.7.0_21/

ENJOY

Sunday, April 21, 2013

CONNECT ORACLE DB USING SHELL SCRIPT

CAN COPY THIS PATH FROM .BASH_PROFILE IN ORACLE HOME

PATH=$PATH:$HOME/bin

export PATH

ORACLE_SID=orcl
ORACLE_BASE=/oracle/product/10.2.0
ORACLE_HOME=$ORACLE_BASE/db_1
PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID ORACLE_BASE ORACLE_HOME PATH

sqlplus -s hr/hr@orcl @ /home/oracle/Desktop/a.sql

/////////////////////////////////////////////////////////////////////////////////////////////////////////
a.sql contain query to execute on database;
ex - create table test values(id number,name varchar2(30));

Thursday, April 18, 2013

STEP FOR ORACLE DATABASE IN ARCHIVE LOG MODE

SQL> startup mount
ORACLE instance started.

Total System Global Area 608174080 bytes
Fixed Size                  1220844 bytes
Variable Size             167776020 bytes
Database Buffers          436207616 bytes
Redo Buffers                2969600 bytes
Database mounted.
SQL>
SQL>
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL>
SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

SQL>

ARCHIVE LOG MULTIPLEXING
IN ORACLE 10g PROVIDE 10 DIFFERENT DESTINATION TO KEEP YOUR ARCHIVE LOG

SQL>alter system set log_archive_dest_1='LOCATION=/oracle/archive';

for remove location
SQL>alter system set log_archive_dest_1='';

SQL>alter system archive log current;

v$archived_log

///DISABLE ARCHIVE LOG MODE

SQL> connect sys/chaya as sysdba
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database noarchivelog;

SQL> alter database open;

SHOW COUNTER

free counter | dedicated server

Wednesday, April 17, 2013

CHANGE DB_NAME IN ORACLE USING DBNEWID UTILITY

SQL> startup nomount
ORACLE instance started.

Total System Global Area 608174080 bytes
Fixed Size                  1220844 bytes
Variable Size             180358932 bytes
Database Buffers          423624704 bytes
Redo Buffers                2969600 bytes
SQL> alter database mount;

Database altered.

SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      orcl
SQL>

[oracle@localhost ~]$ nid target=sys/orcl dbname=anurag setname=yes

DBNEWID: Release 10.2.0.1.0 - Production on Wed Apr 17 17:36:59 2013

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

Connected to database ORCL (DBID=1334973392)

Connected to server version 10.2.0

Control Files in database:
    /oracle/product/10.2.0/oradata/orcl/control01.ctl
    /oracle/product/10.2.0/oradata/orcl/control02.ctl
    /oracle/product/10.2.0/oradata/orcl/control03.ctl

Change database name of database ORCL to ANURAG? (Y/[N]) => Y

Proceeding with operation
Changing database name from ORCL to ANURAG
    Control File /oracle/product/10.2.0/oradata/orcl/control01.ctl - modified
    Control File /oracle/product/10.2.0/oradata/orcl/control02.ctl - modified
    Control File /oracle/product/10.2.0/oradata/orcl/control03.ctl - modified
    Datafile /oracle/product/10.2.0/oradata/orcl/system01.dbf - wrote new name
    Datafile /oracle/product/10.2.0/oradata/orcl/undotbs01.dbf - wrote new name
    Datafile /oracle/product/10.2.0/oradata/orcl/sysaux01.dbf - wrote new name
    Datafile /oracle/product/10.2.0/oradata/orcl/users01.dbf - wrote new name
    Datafile /oracle/product/10.2.0/oradata/orcl/example01.dbf - wrote new name
    Datafile /oracle/product/10.2.0/oradata/orcl/temp01.dbf - wrote new name
    Control File /oracle/product/10.2.0/oradata/orcl/control01.ctl - wrote new name
    Control File /oracle/product/10.2.0/oradata/orcl/control02.ctl - wrote new name
    Control File /oracle/product/10.2.0/oradata/orcl/control03.ctl - wrote new name
    Instance shut down

Database name changed to ANURAG.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.

AFTER THIS

STEP1 - CHANGE PFILE IN DB_NAME=ANURAG

SQL> startup pfile='/oracle/product/10.2.0/admin/orcl/pfile/init.ora.1122013121215';
ORACLE instance started.

Total System Global Area 608174080 bytes
Fixed Size                  1220844 bytes
Variable Size             167776020 bytes
Database Buffers          436207616 bytes
Redo Buffers                2969600 bytes
Database mounted.
Database opened.
SQL>

SQL> select name from v$database;

NAME
---------
ANURAG

Saturday, April 13, 2013

Export (EXPDP ) Error : ORA-31633,ORA-01950,ORA-29283,ORA-29283

Perform export/import operation using datapump

then imp_full_database/exp_full_database privilege are required

[oracle@localhost ~]$ expdp import/export directory=datapump dumpfile=orcl.dmp logfile=orcl.log

Export: Release 10.2.0.1.0 - Production on Saturday, 13 April, 2013 13:03:51

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-31626: job does not exist
ORA-31633: unable to create master table "IMPORT.SYS_EXPORT_SCHEMA_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-01950: no privileges on tablespace 'USERS'

this error is resolved by

alter user import quota unlimited on users;

means to give the quota to user on users tablespace.

with datapump export

Export: Release 10.2.0.1.0 - Production on Monday, 15 April, 2013 14:03:24

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation

to check the permission on folder

because the expdp utility not perform write operation on directory