Archive for

ORA-01078: failure in processing system parameters

I tried to start my test db , it shows following errors, I struggled with these errors, though finally i find out and fix it. oops but It’s so easy 🙂

Enter user-name:sys/sys as sysdba
Connected to an idle instance.

SQL> startup ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/home/oracle/oracle/product/10.2.0/db_1/dbs/initorcl.ora' SQL> exit
Disconnected

[oracle@localhost dbs]# ls
hc_azar.dat  initdw.ora  lkAZAR  orapwazar  spfileazar.ora
hc_TEST.dat  init.ora    lkTEST  orapwTEST  spfileTEST.ora

Here inittest.ora file is does not exist, so i created pfile from spfile and then It’s working.

SQL> create pfile from spfile='/home/oracle/oracle/product/10.2.0/db_1/dbs/spfileTEST.ora';

File created.

SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              92276304 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL>

Or

If you’re not set proper SID Path , you may get this error

[oracle@netbackuptest ~]$ cat /etc/oratab
netbktest:/u01/app/oracle/product/11.2.0/dbhome_1:N
cigna:/u02/oracle/product/10.2.0/db_1:N
brokertest:/u02/oracle/product/10.2.0/db_1:N
The above three are my current database. Here I'm going to set another Oracle Sid here,
[oracle@netbackuptest ~]$ export ORACLE_SID=testdb
[oracle@netbackuptest ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 25 08:56:42 2011

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

Connected to an idle instance.

SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u02/oracle/product/10.2.0/db_1/dbs/inittestdb.ora'
SQL> exit
Disconnected

If I set proper Oracle SID , I can able to start the database.

[oracle@netbackuptest ~]$ export ORACLE_SID=cigna
[oracle@netbackuptest ~]$ sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 25 08:57:28 2011

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

Enter user-name: / as sysdba
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1224736768 bytes
Fixed Size                  2020384 bytes
Variable Size             167775200 bytes
Database Buffers         1040187392 bytes
Redo Buffers               14753792 bytes
Database mounted.
Database opened.
SQL>

ORA-27040: file create error, unable to create file

I tried to export one schema using datapump in Enterprose linux enviornment , It shows following error

[oracle@localhost ~]$ expdp test/test directory=my_test dumpfile=my_test.dmp nologfile=y

Export: Release 10.2.0.1.0 – Production on Tuesday, 27 April, 2010 20:16:00

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-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31641: unable to create dump file “/tmp/backup/my_test.dmp”
ORA-27040: file create error, unable to create file
Linux Error: 13: Permission denied

The above error for that partticular directory is for root user. Its not permissable directory for Oracle.So we need to give permission to oracle for particular directory.

[root@localhost tmp]# ls -ld backup/
drwxr-xr-x 2 root root  4096 Apr 27 18:01 backup/

[root@localhost ~]# cd /tmp
[root@localhost tmp]# chown -R oracle:oinstall backup/
[root@localhost tmp]# ls -ld backup/
drwxr-xr-x 2 oracle oinstall 4096 Apr 27 18:24 backup/

Now you can take backup using datapump 🙂

[oracle@localhost ~]$ expdp test/test directory=my_test dumpfile=my_test.dmp nologfile=y

Export: Release 10.2.0.1.0 – Production on Tuesday, 27 April, 2010 20:18:14

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
Starting “TEST”.”SYS_EXPORT_SCHEMA_01″:  test/******** directory=my_test dumpfile=my_test.dmp nologfile=y
Estimate in progress using BLOCKS method…
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 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/PRE_SCHEMA/PROCACT_SCHEMA
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
. . exported “TEST”.”T”                                  7.812 KB      14 rows
Master table “TEST”.”SYS_EXPORT_SCHEMA_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_SCHEMA_01 is:
/tmp/backup/my_test.dmp
Job “TEST”.”SYS_EXPORT_SCHEMA_01″ successfully completed at 20:18:26

SP2-0734: unknown command beginning when recreate controlfile

Create backup for controlfile

SQL> alter database backup controlfile to trace as ‘d:\control\test.trc’;

Database altered.

Recreate Controlfile, Edit the test.trc file and when i try to recreate the control file , it showing SP-0734

SQL> SHUTDOWN ABORT
ORACLE instance shut down.
SQL> @D:\control\CONTROL.TRC;
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             331350480 bytes
Database Buffers          197132288 bytes
Redo Buffers                5804032 bytes
SP2-0042: unknown command “DATAFILE” – rest of line ignored.
SP2-0734: unknown command beginning “‘D:\CONTRO…” – rest of line ignored.
SP2-0734: unknown command beginning “‘D:\CONTRO…” – rest of line ignored.
SP2-0734: unknown command beginning “‘D:\CONTRO…” – rest of line ignored

This happened due to blank line before the DATAFILE clause and after — STANDBY LOGFILE and  remove that space line as well as remove line — STANDBY LOGFILE and re run the script,

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “AZAR” NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 8
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 ‘C:\DATAFILES\AZAR\REDO01.LOG’  SIZE 50M BLOCKSIZE 512,
  GROUP 2 ‘C:\DATAFILES\AZAR\REDO02.LOG’  SIZE 50M BLOCKSIZE 512,
  GROUP 3 ‘C:\DATAFILES\AZAR\REDO03.LOG’  SIZE 50M BLOCKSIZE 512
— STANDBY LOGFILE                                                             [ REMOVE THIS SPACE LINE ]

DATAFILE
  ‘C:\DATAFILES\AZAR\SYSTEM01.DBF’,
  ‘C:\DATAFILES\AZAR\SYSAUX01.DBF’,
  ‘C:\DATAFILES\AZAR\UNDOTBS01.DBF’,
  ‘C:\DATAFILES\AZAR\USERS01.DBF’,
  ‘C:\TEST01.DBF’
CHARACTER SET AR8ISO8859P6
;

After removed space

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “AZAR” NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 8
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 ‘C:\DATAFILES\AZAR\REDO01.LOG’  SIZE 50M BLOCKSIZE 512,
  GROUP 2 ‘C:\DATAFILES\AZAR\REDO02.LOG’  SIZE 50M BLOCKSIZE 512,
  GROUP 3 ‘C:\DATAFILES\AZAR\REDO03.LOG’  SIZE 50M BLOCKSIZE 512
DATAFILE
  ‘C:\DATAFILES\AZAR\SYSTEM01.DBF’,
  ‘C:\DATAFILES\AZAR\SYSAUX01.DBF’,
  ‘C:\DATAFILES\AZAR\UNDOTBS01.DBF’,
  ‘C:\DATAFILES\AZAR\USERS01.DBF’,
  ‘C:\TEST01.DBF’
CHARACTER SET AR8ISO8859P6
;

SQL> shutdown abort
ORACLE instance shut down.
SQL> @D:\control\CONTROL.TRC;
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             331350480 bytes
Database Buffers          197132288 bytes
Redo Buffers                5804032 bytes

Control file created.

SQL> select status from v$instance;

STATUS
————
MOUNTED

SQL> alter database open;

Database altered.

SQL> select status from v$instance;

STATUS
————
OPEN

rlwrap linux for oracle sqlplus

This Utility is usefull for when you’re working sqlplus in linux enviornment , If you want to see previous commands, you cannot see because your up and down arrow not working in sqlplus prompt in linux, use such a windows sqlplus command prompt. So  You need to install rlwrap for Oracle in linux. Here I have to show How to install rlwrap ,

I refered from

http://kamranagayev.wordpress.com/2009/07/04/using-rlwrap-to-get-command-lines-history-in-sqlplus-video-tutorial/

http://surachartopun.com/2009/01/rlwrap-tool-thank-haralds-blog.html

Step 1 :

Step 2:

Step 3:

Step 4:

Step 5:

Datapump Scheduler for Schema level

My previous post about How to schedule and  take full database export using dbms scheduler job .Now i’m here go to show How to schedule schema level export using datapump.But I just added some comments in package body 🙂

Conn Sys user

SQL> grant execute on dbms_lock to system;

Conn system user

Step 1:

SQL> CREATE DIRECTORY NEW_DIR AS ‘D:\ORACLE\DUMP’;

Directory created.

Step 2:

CREATE OR REPLACE PACKAGE testexport
IS
PROCEDURE exptrial;
END testexport;
/

Step 3:

SQL> CREATE OR REPLACE PACKAGE BODY testexport
  2  IS
  3  h1 NUMBER;
  4  PROCEDURE exptrial IS
  5  BEGIN
  6  BEGIN
  7  UTL_FILE.FREMOVE(‘NEW_DIR’,’SCHEMATEST.DMP’);
  8  END;
  9  BEGIN
 10  DBMS_LOCK.sleep(30);
 11  END;
 12  BEGIN
 13  h1 := DBMS_DATAPUMP.open(
 14  operation => ‘EXPORT’,
 15  job_mode => ‘SCHEMA’,
 16  job_name => ‘TESTIT’,
 17  version => ‘COMPATIBLE’);
 18  END;
 19  BEGIN
 20  DBMS_DATAPUMP.add_file(
 21  handle => h1,
 22  filename => ‘TRIAL01.LOG’,
 23  directory => ‘NEW_DIR’,
 24  filetype => 3);
 25  END;
 26  BEGIN
 27  DBMS_DATAPUMP.add_file(
 28  handle => h1,
 29  filename => ‘SCHEMATEST.DMP’,
 30  directory => ‘NEW_DIR’,
 31  filetype => 1);
 32  END;
 33  BEGIN
 34  DBMS_DATAPUMP.metadata_filter(
 35  handle => h1,
 36  name => ‘SCHEMA_EXPR’,
 37  value => ‘IN(”FIN_ACE_DAM_AGNT”)’);
 38  END;
 39  BEGIN
 40  dbms_datapump.set_parameter(
 41  handle => h1, name => ‘ESTIMATE’, value => ‘BLOCKS’);
 42  END;
 43  BEGIN
 44  DBMS_DATAPUMP.START_JOB(h1);
 45  END;
 46  END exptrial;
 47  END testexport;
 48  /

Package body created.

Step 4:

SQL> exec testexport.exptrial;

PL/SQL procedure successfully completed.

Step 5:

SQL> BEGIN
  2  DBMS_SCHEDULER.CREATE_JOB_CLASS(
  3  job_class_name => ‘BACKUPME’,
  4  resource_consumer_group => ‘DEFAULT_CONSUMER_GROUP’,
  5  comments => ‘BACKUP, EXPORT ETC’);
  6  END;
  7  /

PL/SQL procedure successfully completed.

Step 6:

SQL> BEGIN
  2  DBMS_SCHEDULER.CREATE_JOB(
  3  job_name => ‘TRIALME’,
  4  job_type => ‘STORED_PROCEDURE’,
  5  job_action => ‘TESTEXPORT.EXPTRIAL’,
  6  start_date => ‘18-APR-10 10:00:00 PM’,
  7  repeat_interval => ‘FREQ=DAILY;BYHOUR=22′,
  8  job_class => ‘BACKUPME’,
  9  comments => ‘SCHEMA EXPORT’);
 10  END;
 11  /

Step 7:

SQL> BEGIN
  2  DBMS_SCHEDULER.ENABLE(‘TRIALME’);
  3  END;
  4  /

PL/SQL procedure successfully completed.

Change Oracle password temporarily

I just want to share about How to change Oracle username password temporarily.Because this steps may use for application devlopment without conflict old password if you’re forgotten or If you’re not creator of the users.But condition apply If any limit  resource password function applicable.

Step 1: Create user and password. Conn as a sys user.

SQL> create user test identified by test;

User created.

SQL> grant connect,resource to test;

Grant succeeded.

Step 2:

SQL> select username,password  from dba_users where username =’TEST’;

USERNAME                       PASSWORD
—————————— ——————————
TEST                           7A0F2B316C212D67

Step 3:

SQL>  conn test/test@azar;
Connected.
SQL> conn sys/sys@azar as sysdba
Connected.

Step 4:

SQL> alter user test identified by test123;

User altered.

SQL> conn test/test123@azar;
Connected.

Step 5:

SQL> conn sys/sys@azar as sysdba
Connected.
SQL> alter user test identified by values ‘7A0F2B316C212D67’;

User altered.

SQL> conn test/test@azar;
Connected.

ORA-28003: password verification for the specified password failed

Hi I want to explain about why ORA-28003 errors occur when you are change the password. because Some of the Oracle users assigned profile for limit password function so in this case you couldnot change your Oracle user password.If you’re not assigned the password profile function, the users can able to change the password easily.

Example :-

SQL> conn sys/sys@azar as sysdba
Connected.
SQL> create user xxx identified by xxx;

User created.

SQL> grant connect to xxx;

Grant succeeded.

SQL> conn xxx/xxx;
Connected.
SQL> alter user xxx identified by test;

User altered.

SQL> conn xxx/test;
Connected.
SQL> conn xxx/xxx;
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.

So you can change the password after login users account. The below steps shown How to setup limit password function and then you cannot change password.

Step 1: Create Function
  1  create or replace function pwd_change(
  2  username varchar2,
  3  password varchar2,
  4  old_password varchar2) return boolean is
  5  begin
  6  raise_application_error(-20009,’Error: You cannot change password’);
  7* end;
SQL> /

Function created.

Step 2: Create Profile. This profile will containg limit Password function and then you can apply this profile who are not allowed to change their password.

SQL> create profile testpwd limit password_verify_function pwd_change;

Profile created.

Step 3:  Create  User and assign profile.

SQL> CREATE USER TRIAL IDENTIFIED BY TRIAL DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;

User created.

SQL> grant connect to trial;

Grant succeeded.

SQL> alter user trial profile testpwd;

User altered.

SQL> CONN TRIAL/TRIAL;
Connected.

SQL> alter user TRIAL identified by test replace TRIAL;
alter user TRIAL identified by test replace TRIAL
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20009: Error: You cannot change password

and tried to change the password as sys user ,you cannot.

SQL> conn sys/sys@azar as sysdba
Connected.
SQL> alter user trial identified by test;
alter user trial identified by test
*
ERROR at line 1:
ORA-28003: password verification for the specified password failed
ORA-20009: Error: You cannot change password.

How to Fix this Error If you want to change the password.

Conn sys user.

SQL> conn sys/sys@azar as sysdba
Connected.
SQL> select profile from dba_users where username=’TRIAL’;

PROFILE
——————————
TESTPWD

SQL> select RESOURCE_NAME,RESOURCE_TYPE,LIMIT FROM DBA_PROFILES WHERE PROFILE=’TESTPWD’ AND RESOURCE_NAME=’PASSWORD_VERIFY_FUNCTION’;

RESOURCE_NAME                    RESOURCE
——————————– ——–
LIMIT
—————————————-
PASSWORD_VERIFY_FUNCTION         PASSWORD
PWD_CHANGE
SQL> ALTER PROFILE TESTPWD LIMIT PASSWORD_VERIFY_FUNCTION NULL;

Profile altered.

SQL> alter user trial identified by test;

User altered.

SQL> conn trial/test;
Connected.

Host Credentials Oracle EM

When you’re going to maintain your backup through Oracle Enterprise manager, it will ask Host Credentials username and Password.

Most of the Oracle users asked What is this one Either Database username  or anything else?

I simply Replied This is Your server OS username and password.

Ok.But when i put the Server OS administrator username and password , It shows Validataion Error, why its happen and How to fix it?

If Windows Server,

Step 1: Go to Start —> Run

Step 2:type secpol.msc /s        –> Local Security Policy window appear.

Step 3: here

Left Side panel ->Local Policies -> User Rights Assignment  <– Enter

Right Side Panel -> Log on as a batch job –> here Double Click and Add Administrator User.

Now you can access your backup maintainence  through Oracle Enterprise Manager.

Redo Transport Services Overview

I just want to share about redo transport services here…

Unable to determine local host from URL REPOSITORY_UR : Oracle EM

I tried to start my dbconsole ,its not working following message occur when i start a dbconsole…

Unable to determine local host from URL REPOSITORY_URL=http://ace-brokers.ace-in
s.com:%EM_UPLOAD_PORT%/em/upload/

So I fixed for these above problem here and then ,it’s working fine..

C:\Documents and Settings\Administrator>set oracle_home=d:\oracle\product\10.2.0\db_2

C:\Documents and Settings\Administrator>set oracle_sid=broker

C:\Documents and Settings\Administrator>emca -config dbcontrol db -repos recreate

STARTED EMCA at Apr 4, 2010 11:53:46 AM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: broker
Database Control is already configured for the database broker
You have chosen to configure Database Control for managing the database broker
This will remove the existing configuration and the default settings and perform
 a fresh configuration
Do you wish to continue? [yes(Y)/no(N)]: y
Listener port number: 1521
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Password for SYSMAN user: Email address for notifications (optional): mazar@ace–
ins.com
Outgoing Mail (SMTP) server for notifications (optional): mazar@ace-ins.com
—————————————————————–

You have specified the following settings

Database ORACLE_HOME ……………. D:\oracle\product\10.2.0\db_2

Database hostname ……………. ace-brokers.ace-ins.com
Listener port number ……………. 1521
Database SID ……………. broker
Email address for notifications …………… mazar@ace-ins.com
Outgoing Mail (SMTP) server for notifications …………… mazar@ace-ins.com

—————————————————————–
Do you wish to continue? [yes(Y)/no(N)]: y
Apr 4, 2010 11:56:12 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at D:\oracle\product\10.2.0\db_2\cfgtoollog
s\emca\BROKER\emca_2010-04-04_11-53-46-AM.log.
Apr 4, 2010 11:56:21 AM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) …
Apr 4, 2010 11:56:23 AM oracle.sysman.emcp.EMReposConfig dropRepository
INFO: Dropping the EM repository (this may take a while) …
Apr 4, 2010 12:01:39 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Apr 4, 2010 12:01:40 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) …
Apr 4, 2010 12:03:40 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Apr 4, 2010 12:03:46 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) …
Apr 4, 2010 12:04:26 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Apr 4, 2010 12:04:26 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://ace-brokers.ace-ins.com:550
1/em <<<<<<<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at Apr 4, 2010 12:04:26 PM

C:\Documents and Settings\Administrator>emctl status dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation.  All rights reserved.
http://ace-brokers.ace-ins.com:5501/em/console/aboutApplication
Oracle Enterprise Manager 10g is running.
——————————————————————
Logs are generated in directory D:\oracle\product\10.2.0\db_2/ace-brokers.ace-in
s.com_broker/sysman/log