Skip to content

May 24

1 Comment

TNS-01201: Listener cannot find executable

After configured the listener file, i tried to start my listener following error occurs, 

LSNRCTL> start
Starting /home/oracle/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait…  

TNSLSNR for Linux: Version 10.2.0.1.0 – Production
System parameter file is /home/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /home/oracle/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=1521)))
TNS-01201: Listener cannot find executable /home/oracle/oracle/product/10.2.0/db_1/bin/orcl for SID orcl Listener failed to start. See the error message(s) above… 

 This is my listener.ora file 

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1)
(PROGRAM= orcl)
)
(SID_DESC =
(SID_NAME = mydb)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1)
(PROGRAM= mydb)
)
) LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
)
) 

 Tnsnames.ora file 

MYDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = mydb)
)
) ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
) 

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
) 

My mistake was when i configured the listener file, I couldnot change the SID_NAME where PROGRAM located in listener file. 

 (SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1)
(PROGRAM= orcl)
)
(SID_DESC =
(SID_NAME = mydb)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1)
(PROGRAM= mydb)
) 

Here We should be put it SID_NAME here.
 

(SID_DESC =
(SID_NAME = orcl)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1)
(SID_NAME= orcl)
)
(SID_DESC =
(SID_NAME = mydb)
(ORACLE_HOME = /home/oracle/oracle/product/10.2.0/db_1)
(SID_NAME= mydb)
) 

Now my listener started and I could be able to connect with string. 

Status: 

Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
Service “mydb” has 1 instance(s).
Instance “mydb”, status UNKNOWN, has 1 handler(s) for this service…
Service “orcl” has 1 instance(s).
Instance “orcl”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully

May 18

6 Comments

Character Set migration Oracle 11g

When i tried to transport the tablespace from linux to windows, during import it shows charachter set error, so I just looked on linux database and windows database character set is different .

Linux is WE8IS08859P1

Windows is AR8ISO8859P6

So i plan to migrate the charater set of windows and change to WE8IS08859P1 character set. Here I provided step by step guide , How to migrate character set. I play with CSSCAN

Step1 : On windows Env

SQL> select * from nls_database_parameters;

PARAMETER                      VALUE
—————————— ————————————
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               AR8ISO8859P6
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM

21 rows selected.

Step 2:

C:\Users\mazar>CSSCAN
Character Set Scanner v2.2 : Release 11.2.0.1.0 – Production on Tue May 18 11
:43 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Username: SYS AS SYSDBA

Password:

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

CSS-00107: Character set migration utility schema not installed

So CSSCAN utility doesnot installed on your computer, so we want to install the CSSCAN utility. Go to csminst.sql script

Step 3:

SQL> @C:\app\mazar\product\11.2.0\dbhome_1\RDBMS\ADMIN\csminst.sql
Synonym created.
View created.
View created.
View created.
View created.
Grant succeeded.
Grant succeeded.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Pr
oduction
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\mazar>

Step 4:

C:\Users\mazar>CSSCAN
Character Set Scanner v2.2 : Release 11.2.0.1.0 – Production on Tue May 18 11:38
:37 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Username: SYS AS SYSDBA

Password:

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

(1)Full database, (2)User, (3)Table, (4)Column: 1 > 1

Current database character set is  AR8ISO8859P6.

Enter new database character set name: > WE8ISO8859P1

Enter array fetch buffer size: 1024000 >

Enter number of scan processes to utilize(1..64): 1 > 64

. process 51 scanning EXFSYS.RLM$EVENTSTRUCT
. process 63 scanning EXFSYS.RLM$RULESETSTCODE
. process 59 scanning EXFSYS.RLM$RULESETPRIVS
. process 27 scanning EXFSYS.RLM$INCRRRSCHACT
. process 62 scanning EXFSYS.RLM$ORDERCLSALS
. process 21 scanning EXFSYS.RLM$RSPRIMEVENTS
. process 31 scanning EXFSYS.RLM$VALIDPRIVS
. process 10 scanning EXFSYS.RLM$DMLEVTTRIGS
. process 60 scanning EXFSYS.RLM$COLLGRPBYSPEC
. process 32 scanning EXFSYS.RLM$PRIMEVTTYPEMAP
. process 5 scanning EXFSYS.RLM$EQUALSPEC

Creating Database Scan Summary Report…

Creating Individual Exception Report…

Scanner terminated successfully.

C:\Users\mazar>

Step 5:

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP RESTRICT
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             301990352 bytes
Database Buffers          226492416 bytes
Redo Buffers                5804032 bytes
Database mounted.
Database opened.

Step 6: Run the csalter.plb script

SQL> @C:\app\mazar\product\11.2.0\dbhome_1\RDBMS\ADMIN\csalter.plb
0 rows created.
Function created.
Function created.
Procedure created.

This script will update the content of the Oracle Data Dictionary.
Please ensure you have a full backup before initiating this procedure.
Would you like to proceed (Y/N)?Y
old   6:     if (UPPER(‘&conf’) <> ‘Y’) then
new   6:     if (UPPER(‘Y’) <> ‘Y’) then
Checking data validity…
begin converting system objects

PL/SQL procedure successfully completed.
0 rows deleted.
Function dropped.
Function dropped.
Procedure dropped.

SQL>

Step 7:

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             301990352 bytes
Database Buffers          226492416 bytes
Redo Buffers                5804032 bytes
Database mounted.
Database opened.

Step 8: Check the current character set.

SQL> select * from nls_database_parameters where parameter=’NLS_CHARACTERSET’;

PARAMETER                                  VALUE
—————————— —————————————-
NLS_CHARACTERSET               WE8ISO8859P1

SQL>

May 6

datapump reterive original data using flashback scn

Retrieve original data:-

It may be useful to export the image of a table the way it existed before a change was committed.if the database is properly configured, the database flashback query facility also integrated with expdp may be used.

SQL> conn / as sysdba
Connected.
SQL> create restore point original_emp;

Restore point created.

SQL> select scn,name from v$restore_point;

SCN
———-
NAME
——————————————————————————–
518527
ORIGINAL_EMP

SQL> conn scott/tiger;
Connected.

SQL> select sum(sal) from emp;

SUM(SAL)
———-
29025

SQL> update test set sal=sal*1.1;

14 rows updated.

Here Test table is copy of emp table.

SQL> commit;

Commit complete.

SQL> select sum(sal) from test;

SUM(SAL)
———-
31927.5

[oracle@localhost rlwrap-0.30]$ expdp scott/tiger dumpfile=original directory=test_dir tables=test flashback_scn=518527

Export: Release 10.2.0.1.0 – Production on Friday, 07 May, 2010 13:33:02

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 “SCOTT”.”SYS_EXPORT_TABLE_01″:  scott/******** dumpfile=original directory=test_dir tables=test flashback_scn=518527
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported “SCOTT”.”TEST”                              7.820 KB      14 rows
Master table “SCOTT”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/home/oracle/datapumptest/original.dmp
Job “SCOTT”.”SYS_EXPORT_TABLE_01″ successfully completed at 13:33:11

SQL> drop table test;

Table dropped.

SQL> commit;

Commit complete.

[oracle@localhost rlwrap-0.30]$ impdp scott/tiger dumpfile=original directory=test_dir tables=test

Import: Release 10.2.0.1.0 – Production on Friday, 07 May, 2010 13:34:57

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
Master table “SCOTT”.”SYS_IMPORT_TABLE_01″ successfully loaded/unloaded
Starting “SCOTT”.”SYS_IMPORT_TABLE_01″:  scott/******** dumpfile=original directory=test_dir tables=test
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “SCOTT”.”TEST”                              7.820 KB      14 rows
Job “SCOTT”.”SYS_IMPORT_TABLE_01″ successfully completed at 13:35:00

[oracle@localhost rlwrap-0.30]$

SQL> select sum(sal) from test;

SUM(SAL)
———-
29025

May 5

deleting Archive Redo logs file

Deleting Archived Redo logs :

You can delete any eligible archived redo log by using the delete archivelog or backup…delete input command.

RMAN>delete archivelog all;

The delete archivelog all command deletes all archived redo logs on disk that aren’t necessary to meet the configured archived redo log
deletion policy,It’s more likely that you’d want to use the following delete command, which deletes archvied redo logs from disk based on whether
they have been first backed up to tape a certain number of times.

RMAN>delete archivelog until sequence – 999;

the backup…delete command lets you first backup an archived redo log and then delete the source archived redo log file.
In order to delete the source file, you use the additional clause delete input,

RMAN>backup device type sbt
     archivelog all
     delete all input;

The previous backup…delete command backs up all the archived redo logs and then deletes all those archvied redo logs (input files).
the delete all input clause results in the deletion of all backed up archived redo logs from all archived redo log destinations.

if you want to delete only the specified archived redo log that you’ve just backed up to a backup set, use the delete input clause instead,

RMAN>backup archiveloglike ‘/arch%’ delete input;

backup…delete command to back up archived redo logs to tape and then delete the source files.

How?

RMAN uses the configured archived redo log deletion policy to determine which of the archvied redo logs are eligible for deletion, including those archived redo logs that
are stored in the FRA, RMAN automatically deletes the eligible archived redo logs from the flash recovery area.

An  archived redo logs is considered eligible from deletion when the flash recovery area becomes full.
Suppose you have configured the following archived redo log deletion policy:

RMAN>configure archivelog deletion policy
     to backed up 2 times to device type sbt;

The above command that all archived redo log files will be eligible for deletion from all locations when those files have been backed up twice or more to tape.

Once you set the archvied redo log deletion policy shown here, a delete archivelog all or backup…delete input command will delete all archived redo logs that satisfy the requirements of your configured deletion policy,which requires that rman back up all archived redo logs to tape twice.
If you haven’t configured an archived redo log deletion policy( by default there is no policy set), RMAN will deem any archied redo log file in the flash recovery area eligible for deletion, If  following true,

  • The archived redo logs have been successfully sent to all the destinations specified by the log_archive_dest_n parameter
  • you have copied the archvied redo lgos to disk or tape at least once or the archived redo logs are obsolete per hour configured retention policy.

Use the configure archivelog deletion policy command to specify your own archive redo log deletion critieria of leaving the deletion timing to RMAN.

Once you configure an archived redo log deletion policy,it applies to all archived redo log locations, including FRA,if you’ve configured one.

RMAN stores the archived redo logs as long as possible in the FRA. When the flash recovery area is under space pressure, RMAN tries to ensure that any flashback retention time you’ve set is being satisfied before automatically deleting the archvied redo logs.

RMAN deletes eliggible archived redo lgos stored in all areas other than the flash recovery area when you execute one of the two deletion commands shown in the solution section of the receipe, backup…delete input or delete archivelog.

*If you execute the delete command with the force option, RMAN will ignore any configured archived redo log retention policies and deletes all the specified archived redo logs.

Apr 27

45 Comments

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>

Apr 27

1 Comment

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

Apr 27

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

Apr 26

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:

Apr 18

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.

Apr 13

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.

← Older Entries
Newer Entries →