This forum is currently locked. You can't register or post questions at this time. (read more)

bash_profile

All posts relating to Oracle database administration.

Moderator: Tim...

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: bash_profile

Postby Tim... » Sat Jan 05, 2013 8:55 am

Hi.

I was answering your question on a smartphone, so I was trying to type as little as possible. I assumed when you read,

Code: Select all

/path/to/prdspfile.ora


You would say to yourself, "Wait a minute. That is not the path to my spfile!", and change it to the correct path. You will often see people write "/path/to" to indicate *you* need to add the correct path!

So, based on the information from your previous posts, the command should be,

Code: Select all

create pfile='/tmp/pfile.txt' from spfile='/home/oracle/app/oracle/product/11.1.0/db_1/dbs/prdspfile.ora';


This command creates the pfile from the existing spfile. The fact it failed in the previous attempt meant the pfile was not created, which is why the second command failed. The second command can be done unchanged, once the first command has run successfully.

Code: Select all

create spfile from pfile='/tmp/pfile.txt';


In this case, the word "spfile" tells Oracle to use the default location, based on the current ORACLE_HOME and ORACLE_SID. We could specify the name explicitly, but we don't need to. We had to for the first command because relying on the default would not work as the source spfile uses "prd" rather than the ORACLE_SID of "test", hence the need for the explicit location.

You should read this.

http://www.oracle-base.com/articles/9i/ ... meters.php

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

syedhafeez69
Senior Member
Posts: 51
Joined: Thu Dec 27, 2012 11:37 am

Re: bash_profile

Postby syedhafeez69 » Sat Jan 05, 2013 12:56 pm

The output of the first command {/path/to/prdspfile.ora} is

[ -bash: /path/to/testspfile.ora: No such file or directory ]------------ this with oracle user


For the second command { create pfile='/tmp/pfile.txt' from spfile='/home/oracle/app/oracle/product/11.1.0/db_1/dbs/prdspfile.ora'; } is

[ *
ERROR at line 1:
ORA-01565: error in identifying file
'/home/oracle/app/oracle/product/11.1.0/db_1/dbs/prdspfile.ora'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3 ]--------------------- this after running sqlplus...

The third one { create spfile from pfile='/tmp/pfile.txt'; } is

[ *
ERROR at line 1:
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/tmp/pfile.txt' ]----- in sqlplus

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: bash_profile

Postby Tim... » Sat Jan 05, 2013 1:30 pm

Dude! Did you read nothing I said?

The first thing wasn't a command. It was a path you were ti set. I was making the point that this wasn't a real path.

*YOU* know where your spfile is. *YOU* need to put in the correct path to that spfile.

Please. Just try and engage your brain for one second!

The path I gave you was based on what you had said in your previous posts. If any of that information is wrong, it is because you posted the wrong information. You have to run two commands:

Code: Select all

create pfile='/tmp/pfile.txt' from spfile='<put something sensible here>';
create spfile from pfile='/tmp/pfile.txt';


This assumes the following:

1) The section <put something sensible here> is replaced by the actual full path to the existing spfile.
2) The contents of the pfile.txt, once it is created, contains sensible information that is relevant to the real instance. If it contains entries that are not relevant to the instance, then it's not going to work. You will only know this by opening it and looking at the information. Any references to "prd" should probably be replaced by "test".

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

syedhafeez69
Senior Member
Posts: 51
Joined: Thu Dec 27, 2012 11:37 am

Re: bash_profile

Postby syedhafeez69 » Tue Jan 08, 2013 4:23 pm

1) I am configuring database manually.. When i configure parameter file manually...i.e first i copied init.ora to inittest.ora.... when i open inittest.ora i find nothing interesting to modify the file such as shared pool,controlfiles etc... these are the contents of the file

{
#
# $Header: init.ora 06-aug-98.10:24:40 atsukerm Exp $
#
# Copyright (c) 1991, 1997, 1998 by Oracle Corporation
# NAME
# init.ora
# FUNCTION
# NOTES
# MODIFIED
# atsukerm 08/06/98 - fix for 8.1.
# hpiao 06/05/97 - fix for 803
# glavash 05/12/97 - add oracle_trace_enable comment
# hpiao 04/22/97 - remove ifile=, events=, etc.
# alingelb 09/19/94 - remove vms-specific stuff
# dpawson 07/07/93 - add more comments regarded archive start
# maporter 10/29/92 - Add vms_sga_use_gblpagfile=TRUE
# jloaiza 03/07/92 - change ALPHA to BETA
# danderso 02/26/92 - change db_block_cache_protect to _db_block_cache_p
# ghallmar 02/03/92 - db_directory -> db_domain
# maporter 01/12/92 - merge changes from branch 1.8.308.1
# maporter 12/21/91 - bug 76493: Add control_files parameter
# wbridge 12/03/91 - use of %c in archive format is discouraged
# ghallmar 12/02/91 - add global_names=true, db_directory=us.acme.com
"inittest.ora" 174L, 8385C 1,1 Top
} }


2) While running Putty for accessing database remotely when i am typing the ip address with connection as SSH, the error is { Network error : Connection refused }
Last edited by syedhafeez69 on Tue Jan 08, 2013 5:18 pm, edited 1 time in total.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: bash_profile

Postby Tim... » Tue Jan 08, 2013 5:13 pm

Hi.

Create the pfile from the spfile. You can then edit the contents of the pfile. Once you are happy with it, you can turn it into an spfile. The commands are all listed in my previous posts.

If you don't have the original spfile anymore, get one off one of your other databases.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

syedhafeez69
Senior Member
Posts: 51
Joined: Thu Dec 27, 2012 11:37 am

Re: bash_profile

Postby syedhafeez69 » Wed Jan 09, 2013 12:39 pm

While running Putty for accessing database remotely when i am typing the ip address with connection as SSH, the error is { Network error : Connection refused }

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: bash_profile

Postby Tim... » Wed Jan 09, 2013 1:42 pm

Hi.

1) That is not a real parameter file unless it has some contents other than comments. I suggest you find a real one.

2) Regarding the SSH message, that suggests that either the sshd deamon is not running on the server, or there is a firewall blocking your access to it.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

syedhafeez69
Senior Member
Posts: 51
Joined: Thu Dec 27, 2012 11:37 am

Re: bash_profile

Postby syedhafeez69 » Wed Jan 09, 2013 11:10 pm

Hi.. I get the same error message even when i turned off the firewall.. and the connection type is SSH only....

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: bash_profile

Postby Tim... » Thu Jan 10, 2013 2:21 am

Hi.

The firewall was only 1 of 2 reasons I gave for it not working.

Remember also, there can be the firewall on your PC, the firewall on the server and possible 1 or more corporate firewalls between your PC and the server. In my company there is a minimum of three firewalls to consider, as well as the sshd configuration, that can also control user access.

Ask you network guys.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

syedhafeez69
Senior Member
Posts: 51
Joined: Thu Dec 27, 2012 11:37 am

Re: bash_profile

Postby syedhafeez69 » Thu Jan 10, 2013 5:27 pm

Sir. I am creating database manually.. i have created the control and redolog files in the editor.... after that when i am running the script.. the error is at line2
{ datafile '/home/oracle/app/oracle/oradata/test/system.dbf' size 300m;
*
ERROR at line 2:
ORA-00911: invalid character }

thanks...

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: bash_profile

Postby Tim... » Thu Jan 10, 2013 6:56 pm

Hi.

This is most likely a windows->UNIX issue. Windows using CHR(10)+ CHR(13) at the end of lines. Linux uses just CHR(10). Sometimes this can cause problems.

Many text editors provide dostounix conversions. If not, you can save a file on the unix box and use the dostounix command manually. if this is the cause of the problem, that will sort it.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

syedhafeez69
Senior Member
Posts: 51
Joined: Thu Dec 27, 2012 11:37 am

Re: bash_profile

Postby syedhafeez69 » Fri Jan 11, 2013 10:40 am

I didnt get you.. pls can u post it in detail..

and when i am typing all these things i.e. creatng redolg and control files i am typing in the editor... how to type it in sql

syedhafeez69
Senior Member
Posts: 51
Joined: Thu Dec 27, 2012 11:37 am

Re: bash_profile

Postby syedhafeez69 » Fri Jan 11, 2013 10:47 am

ok i got it i am typing it in sql...
when i type the first line {datafile '/home/oracle/app/oracle/oradata/test/system.dbf' size 300m;} the error is

[ORA-30045: No undo tablespace name specified] pls do explain in a basic way as i am a fresher

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: bash_profile

Postby Tim... » Fri Jan 11, 2013 11:14 am

Hi.

1) Where did you get this script from?

If you are creating a database manually, I would still expect you to use the DBCA, but instead of creating the database, you can just choose the option to generate the scripts. Creating a database using the DBCA is the preferred option as far as Oracle are concerned, whether you let it create the database itself, or weather you only use it to generate the scripts.

2) It's pretty difficult to make a judgement about what problem with the command is, when you've not actually posted the command itself, just a path.

3) The error message itself is quite clear. You are attempting to do an operation that requires an undo tablespace. That undo tablespace is not available. This suggests to me your database creation script is wrong, which is why you should be using the DBCA to generate one for you that is correct.

If you are a newbie, you should start by doing things the simple way, then progress to more complicated approaches. Creating a database manually from scratch is not the simplest approach, so you should not be doing it yet!

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

syedhafeez69
Senior Member
Posts: 51
Joined: Thu Dec 27, 2012 11:37 am

Re: bash_profile

Postby syedhafeez69 » Fri Jan 11, 2013 2:40 pm

ok Tim sir .. this is the whole procedure that my lecturer gave us to create database manually.. I already created it with DBCA method now i am trying it manually....

{ Manual database creation

13 create parameter file

$ cd $ORACLE_HOME/dbs
$ cp init.ora initdemo.ora
$ vi initdemo.ora
db_name = demo
shared_pool_size = 90m
control_files = /home/test/control01.ctl, /home/test/control02.ctl
user_dump_dest = /home/test/udump
core_dump_dest = /home/test/cdump
background_dump_dest = /home/test/bdump
undo_management = auto
compatible = 10.2.0.1.0

14 create folder
$ cd /demo
$ mkdir udump bdump cdump
$ chmod -R 777 /demo/udump /demo/bdump /demo/cdump

15 create control file ( control file creation is possible only in no mount stage )

sql > startup nomount;
sql > select status from v$instance;

16 execute the following scripts

sql > define_editor = 'vi'
> create db (enter 2 times)
ed
create database demo

datafile '/demo/system.dbf' size 300m
sysaux datafile '/demo/sysaux.dbf' size 300m
default tablespace userdata datafile '/demo/userdata.dbf' size 200m
default temporary tablespace tempdemo tempfile '/demo/tempdemo.dbf'
size 200m
undo tablespace undodemo datafile '/demo/undodemo.dbf' size 200m

logfile

group 1 '/demo/redo1.log' size 10m,
group 2 '/demo/redo2.log' size 10m
/

Database created. }... thanks


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 6 guests