Change Database Name

There are two parts on changing DB_NAME: modify the db_name parameter in initialization file and re-create the control file.

1. Check the current DB_NAME, and shut down the database clearly

SQL> select database_name from dual;
SQL> select * from props$ where name = 'GLOBAL_DB_NAME';
SQL> alter database backup controlfile to trace;
SQL> show parameter db_name;
SQL> alter system switch logfile;
SQL> shutdown immediate;

2. Make sure there is no database process (such as pmon) running on OS.

3. Edit the control file saved in trace and make a script for creating control file. The key change is

FROM: CREATE CONTROLFILE REUSE DATABASE "olddbname" RESETLOGS ...
TO: CREATE CONTROLFILE set DATABASE "newdbname" RESETLOGS ...

4. Rename the existing control files.

5. Change the db_name parameter in SPFILE:

SQL> startup nomount;
SQL> alter system set db_name=newdbname scope=spfile;
SQL> shutdown immediate;
SQL> startup nomount;
SQL> show parameter db_name;
SQL> Run the script to create the control file.

7. Open the database
SQL> alter database open resetlogs;

8. Add temp files and verify their Status by v$tempfile.

9. Check the global_name has been changed (by "select database_name from dual;"). If necessary, run
SQL> alter database rename global_name to newdbname.domain ;

Change SID / Instance ID

It is eazy to change ORACLE_SID / SID. Really, only two files are involved.

1. Identify the current SID:

SQL> select instance from v$thread;

INSTANCE
---------
pdb

SQL> select value from v$parameter where name = 'spfile';

VALUE
--------------------------------------------------------------------
/u01/app/oracle/product/10.2.0/db_1/dbs/spfilepdb.ora

SQL> select value from v$parameter where name = 'control_files';

VALUE
-------------------------------------------------------------------------
/u08/app/oracle/oradata/pdb/control01.ctl, /u09/app/oracle/oradata/pdb/control02.ctl

SQL> alter database backup controlfile to trace;
SQL> shutdown immediate;

2. Rename SPFILE and password file:

$ cd /u01/app/oracle/product/10.2.0/db_1/dbs
$ cp spfilepdb.ora spfilepdb.ora_bk
$ mv spfilepdb.ora spfilepdbs.ora
$ cp orapwpdb orapwpdb_bk
$ mv orapwpdb orapwpdbs

3. Optional: back up the control files. Once the instance is started, the control file will be updated with the new SID.

4. Start the instance and see the new SID:

$ export ORACLE_SID=pdbs
$ sqlplus /nolog
SQL> connect / as sysdba
SQL> startup open
SQL> select instance from v$thread;

INSTANCE
--------
pdbs

SQL> select value from v$parameter where name = 'spfile';

VALUE
--------------------------------------------------------------------
/u01/app/oracle/product/10.2.0/db_1/dbs/spfilepdbs.ora

SQL> select value from v$parameter where name = 'control_files';

VALUE
---------------------------------------------------------------------------
/u08/app/oracle/oradata/pdb/control01.ctl, /u09/app/oracle/oradata/pdb/control02.ctl

5. Update oratab, tnsnames.ora, .profile with the new SID
6. If necessary, all folders for the instance on the file system can be changed to reflect the new SID.

Identify master node in RAC

1. Check the ocssd.log file.

db1p:$CRS_HOME/log/db1p/cssd$grep -i "master node" ocssd.log | tail -1
[ CSSD]CLSS-3001: local node number 1, master node number 2

or

db2p:$CRS_HOME/log/db2p/cssd$grep -i "master node" ocssd.log | tail -1
[ CSSD]CLSS-3001: local node number 2, master node number 2

2. "ocrconfig -showbackup" show you the last backup on master node.

DDL: create statements

Since 9i, Oracle provides a package to get the DDL statements, such as for creating tablespace.

SQL> set long 50000
SQL> set heading off
SQL> select dbms_metadata.get_ddl('TABLESPACE', 'USERS') from dual;

In 11.2.0.3, following lines will get the statements for creating indexes and creating user:

SQL> set pagesize 0
SQL> set long 90000
SQL> set feedback off
SQL> set echo off

SQL> select dbms_metadata.get_ddl('INDEX', index_name, 'OWNER_ID')
            from dba_indexes where owner = 'OWNER_ID' and table_name in (......) ;


SQL> select dbms_metadata.get_ddl('USER', 'OWNER_ID') from dual;


In 8i, you have to use Export to extract the same type of statement.

Grid Control: Status Pending

On one Host's homepage in Grid Control, it says "Status Pending" even Grid Control still monitors all databases on the host.

I ran agentca on the database host while the agent is up and running. After this action, the host's status is changed to "Up". If there are database and instance on the host but missing from Grid Control, it will also find and add them to Grid Control.

It seems it stopped the agent during running time, but I did not get notifications saying the agent is down. Here is the log:


/u01/oracle/product/agent10g/bin$./agentca -d

Stopping the agent using /u01/oracle/product/agent10g/bin/emctl stop agent
Oracle Enterprise Manager 10g Release 10.2.0.1.0.
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
Stopping agent ... stopped.
Running agentca using /u01/oracle/product/agent10g/oui/bin/runConfig.sh ORACLE_HOME=/u01/oracle/product/agent10g ACTION=Configure MODE=Perform RESPONSE_FILE=/u01/oracle/product/agent10g/response_file RERUN=TRUE INV_PTR_LOC=/etc/oraInst.loc
Perform - mode is starting for action: Configure

Perform - mode finished for action: Configure

You can see the log file: /u01/oracle/product/agent10g/cfgtoollogs/oui/configActions2009-02-03_09-14-00-AM.log