Question A: I have a default database on Windows 2000 and Oracle 9i named myd1 that has its password file pwdmyd1 in d:oracleora9database. When I create a new database (manually), is it necessary that before starting up the instance in no mount stage we have to:
- make a new instance with oradim
- SET it
- create password file with orapwd?
Are all these steps necessary? If we don't do these three steps then what happens? Also, is the password file associated with a particular instance?
Question B: My password file for the old database has been corrupted. Whenever I try to open that database, it says the password file is invalid Please tell me what to do.
Question C: I created a database manually and ran all the scripts. But when I open it I don't see any default tables that we normally see when the database is created by default during installation. I want to know whether we have to make user SCOTT in database or is that also something we can make by running some scripts?
When creating a database on Windows, you will need to create a new service for that database. But rather than do this manually, I would recommend using the Database Configuration Assistant (DBCA). This GUI wizard will guide you through database creation step by step. Among many things, it will create and start the service for you.
You do not need a password file to start the database. Just make sure that the REMOTE_LOGIN_PASSWORDFILE initialization parameter is set to NONE. If set to EXCLUSIVE, then a password file is needed before you can start the database.
If your password file is corrupt, then you can create a new one with the ORAPWD utility. This utility will create a new password file for you. The following command can be used to create a new password file:
orapwd file=pwdmyd1 password=change_on_install entries=20
The password you use is the password for the SYS user. I have used the default for this account in my example above. After the password file is created, make sure to grant SYSDBA or SYSOPER to those accounts that need it.
The password file is associated with a particular instance. In many cases, the file name needs to be the form pwd(SID).ora where "(SID)" is your ORACLE_SID.
To create the SCOTT schema, simply create a SCOTT user. Then sign on as SCOTT to your database and run the ORACLE_HOME/sqlplus/demo/demobld.sql script. This will create and populate SCOTT's tables.
This was first published in October 2004