database is using pfile or spfile

How can I know whether my database is started using spfile or pfile?

Execute the following query

SQL> SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
FROM sys.v_$parameter WHERE name = 'spfile';

If result displays pfile, it means that database has been started with pfile and vice-versa.

rename of dbname and dbid

To rename database_name and database sid we can use dbnewid utility.

Following steps belong to oracle 10g.2 on windows platform

1. Backup the database
2. SQL> Shutdown immediate
3. SQL> startup mount
4. Run the following command from the command line:

nid target=sys/password@olddbname DBNAME=ORCL

After the DBNEWID completed successfully, database has been shut down

5. SQL> STARTUP MOUNT
Instance start with error.

6. Modify initialization parameter DB_NAME to reflect new name.
sql> ALTER SYSTEM SET DB_NAME=ORCL SCOPE=SPFILE;

7. sql>SHUTDOWN IMMEDIATE

8. create new password file.
orapwd file=c:\oracle\ora92\database\pwdorcl.ora password=password entries=5

9.Rename the SPFILE and modify SPFILE to reflect new dbname.

10. Delete the old service and create the new one.

oradim -delete -sid olddbname
oradim -new -sid orcl -intpwd password -startmode a -pfile
 c:\oracle\ora92\database\spfileorcl.ora

11. Alter the listener.ora and tnsnames.ora setting to match the new database name and restart the listener:

lsnrctl reload

12. Open the database with RESETLOGS:
SQL >STARTUP MOUNT
SQL >ALTER DATABASE OPEN RESETLOGS;

13.Backup the database.