Changing database name and DBID in Oracle

After restoring production database to test for UAT or dev purposes, we may need to change the database name as well as DBID. When DBID is maintained unique, RMAN backup can be triggered for the restored database if we use recovery catalog. Let us see how to change both database name
and the DBID. You can opt to change just the database name.

First let us see the name and the DBID as below:

SQL> select name,dbid from v$database;

NAME            DBID
--------- ----------
PRDIMP  47133164

To change the database name and the DBID, ensure that the database is in mounted state:

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

The tool 'nid' helps us change both name and DBID. Let us see the parameters of nid as below:

[oracle@myserver ~]$ nid

DBNEWID: Release 11.2.0.4.0 - Production on Wed Aug 26 12:59:19 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Keyword     Description                    (Default)

----------------------------------------------------

TARGET      Username/Password              (NONE)

DBNAME      New database name              (NONE)

LOGFILE     Output Log                     (NONE)

REVERT      Revert failed change           NO

SETNAME     Set a new database name only   NO

APPEND      Append to output log           NO

HELP        Displays these messages        NO

As shown above, DBNAME accepts the new database name. If you make the SETNAME parameter to YES, only the database name will get changed. 

Execute the below to change the name only:

[oracle@myserver ~]$ nid TARGET=sys/ DBNAME=TSTIMP SETNAME='YES' LOGFILE=nid.log  

Execute the below to change the name and DBID:

[oracle@myserver ~]$ nid TARGET=sys/ DBNAME=TSTIMP SETNAME='NO' LOGFILE=nid.log  

This takes a few seconds to change the name. The log file can be located at the current working directory. 

After executing the above command, ensure that you copy your parameter file to reflect the new name as below:

[oracle@myserver dbs]$ cp initPRDIMP.ora initTSTIMP.ora

And change db_name in your parameter file. If it is a clustered setup, add instance_name as well. Once the change is made, startup the database using resetlogs if you opted to change both. If you just changed the name, you don't have to open using reset logs. 

SQL> alter database open resetlogs;

Database altered.

SQL> select name,dbid from v$database;

NAME            DBID

--------- ----------

TSTIMP  2323285955

Both got changed as executed the second command.

Now you can make entries in your /etc/oratab file to reflect your changed database name.

This is how we change the database name and the DBID using nid utility.

A word of caution: If you change the DBID of a database, all the backups and archive logs become unusable!

How to check whether a table is audited or not in Oracle?

Problem

After enabling audit for some tables, I wanted to check a few days later the list of audited tables. Unfortunately I could not find a column like 'ISAUDITED' in dba_audit_trail/AUD$.

Solution

We can find out the list of audited tables in 'DBA_OBJ_AUDIT_OPTS' table as below:

In the list above, the tables are audited for select,insert,update and delete operations as they are marked by 'A/A'

Now we can check whether these audited tables are accessed or not by issuing the following query:

select OBJ_NAME from dba_audit_trail where obj_name in (select object_name from dba_obj_audit_opts);

If this query displays any record, then we can conclude that that table has been accessed.

Also, if an audited table is queried by SYS, then the table will not be tracked in AUD$ or DBA_AUDIT_TRAIL. So make you you query the table by some other user so that you can get your audit tested!

Disable logon/logoff audit for a specific user in Oracle

Problem

After enabling audit, there are many entries created in DBA_AUDIT_TRAIL table. If the database is a data warehouse (DWH) one, there are chances for many logon and logoff entries in the audit table by the user who loads data to DWH.

Solution

So it's better to disable auditing of the load user, mostly an application user. We can do that as below:

SQL> noaudit create session by ODI_USER;

Noaudit succeeded.

From now on, logon and logoff actions won't be audited for ODI_USER

How to change lines of scrollback in PuTTY permanently

Problem

Often we like to go back to see what we did or what was the output we got in a putty session. But, by default PuTTY can scroll back for 200 lines. As a result, we lose the commands we typed or the logs we saw that keep changing.

Solution

Of course you can change the setting for the current session by clicking the change settings as below:

and then you can change the lines of scrollback to 200000 or to a number of your choice as below:

But how can we change that for every new session that you open. It is possible by updating the 'Default Settings' as below. Open PuTTy and then load 'Default Settings' as below:

an then click 'Window' under Category panel in the left and increase the value as you wish. Best practice is adding 2 or 3 more zeroes:

Now come back to 'Session' and then save the 'Default Settings'. Now on if you open any PuTTy session, the lines of scrollback will reflect the new setting you made.

Conclusion

It is a good DBA/Sysadmin practice to change the lines of scrollback so that you miss your logs/ commands/query output.