Post Checking After Database Clonning

As I have already covered the clonning through different method .Here are some Post checklist to check the cloned database  environment.

1.) Check Database Name and DBID :

After successful creation of clonning on same or different server we should try to change the database name and id.(in case of same server, clone has different name) .It is suggested to changed the DBID of clone database because Recovery Manager (RMAN) distinguishes databases by DBID. For more details click here  

2.) Edit the Listener.ora and tnsname.ora files :

Edit the listener file to include the new entry of the clonned database.Invoke  the lsnrctl utility and reload the listener. Similarly check the service name in the tnsnames.ora file. Make sure the connection should be connected to cloned database.

3.) Check all the schedule jobs :

If we have scheduled any scripts ,then make sure that all the jobs are enabled for the clonned database.

4.) Verify the parameter file :

Make sure the location mention in the pfile of the clonned database should be correct. The location and values of the parameter should be valid.

5.) Check tempfile  :

After the successful creation of the clone database ,check the tempfile. If there is no tempfile then add the new tempfile in clone database.

6.) Check the archivelog :

Generally archivelog mode is disabled for UAT/Cloned databases. In case our production database is in archivelog mode, make sure that the clonned database archiving is disable. 

7.) Check for database link :

Check for database link present in the cloned environment. Ensure that these are select only dblinks and will not perform any DML in production databases. If  find any ,then you can either drop these or recreate them to point to any UAT or simply remove/hash out tnsnames entry corresponding to these hosts. Also check for any hard coded IP address in host column in DBA_DB_LINKS.

Enjoy    :-)

How To Change DBID and DB NAME

DBNEWID is a database utility that can change the internal database Identifier(DBID) and database name (DBNAME). The DBNEWID utility solves this problem by allowing us to change any of the following:                                                                                  

 1.) Only the DBID of a database

 2.) Only the DBNAME of a database

 3.) Both the DBNAME and DBID of a database

1.) Only the DBID of a database :   The DBID is an internal, unique identifier for a database. Because Recovery Manager (RMAN) distinguishes databases by DBID, we could not register a seed database and a manually copied database together in the same RMAN repository.                                                                  

Changing the DBID of a database is a serious procedure. When the DBID of a database is changed, all previous backups and archived logs of the database become unusable. This is similar to creating a database except that the data is already in the datafiles. After we change the DBID, backups and archive logs that were created prior to the change can no longer be used because they still have the original DBID, which does not match the current DBID. we must open the database with the RESETLOGS option, which re-creates the online redo logs and resets their sequence to 1 .

Below are steps to change the dbid of the database.

C:\>set ORACLE_SID=test

C:\>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 30 14:16:48 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area            263639040 bytes

Fixed Size                                    1373964 bytes

Variable Size                                  205523188 bytes

Database Buffers                            50331648 bytes

Redo Buffers                                  6410240 bytes

Database mounted.

Database opened.

SQL> select name,dbid from v$database;

NAME               DBID

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

TEST              2050645904

SQL> archive log list

Database log mode                   Archive Mode

Automatic archival                     Enabled

Archive destination                    USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence       10

Next log sequence to archive    12

Current log sequence                12

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area                263639040 bytes

Fixed Size                                       1373964 bytes

Variable Size                                   205523188 bytes

Database Buffers                             50331648 bytes

Redo Buffers                                   6410240 bytes

Database mounted.

SQL> host nid target=sys/test@test

DBNEWID: Release 11.2.0.1.0 - Production on Mon May 30 14:25:13 2011

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

Connected to database TEST (DBID=2050645904)

Connected to server version 11.2.0

Control Files in database:

    D:\APP\NEERAJS\ORADATA\TEST\CONTROL01.CTL

    D:\APP\NEERAJS\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL

Change database ID of database TEST? (Y/[N]) => Y

Proceeding with operation

Changing database ID from 2050645904 to 2050636234

Control File D:\APP\NEERAJS\ORADATA\TEST\CONTROL01.CTL - modified

Control FileD:\APP\NEERAJS\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL - modified

    Datafile D:\APP\NEERAJS\ORADATA\TEST\SYSTEM01.DB - dbid changed

    Datafile D:\APP\NEERAJS\ORADATA\TEST\SYSAUX01.DB - dbid changed

    Datafile D:\APP\NEERAJS\ORADATA\TEST\UNDOTBS01.DB - dbid changed

    Datafile D:\APP\NEERAJS\ORADATA\TEST\USERS01.DB - dbid changed

    Datafile D:\APP\NEERAJS\ORADATA\TEST\EXAMPLE01.DB - dbid changed

    Datafile D:\APP\NEERAJS\ORADATA\TEST\TEMP01.DB - dbid changed

Control File D:\APP\NEERAJS\ORADATA\TEST\CONTROL01.CTL - dbid changed

Control File D:\APP\NEERAJS\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL

    dbid changed

    Instance shut down

Database ID for database TEST changed to 2050636234. All previous backups and archived redo logs for this database are unusable.Database is not aware of previous backups and archived logs in Recovery Area.Database has been shutdown, open database with RESETLOGS option.

Succesfully changed database ID.

DBNEWID - Completed succesfully.

C:\>sqlplus sys/test@test as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 30 14:26:13 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area       263639040 bytes

Fixed Size                                 1373964 bytes

Variable Size                             205523188 bytes

Database Buffers                      50331648 bytes

Redo Buffers                            6410240 bytes

Database mounted.

SQL> alter database open resetlogs;

Database altered.

SQL> select name,dbid from v$database;

NAME                  DBID

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

TEST                   2050636234

SQL> archive log list

Database log mode                     Archive Mode

Automatic archival                      Enabled

Archive destination                     USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence         1

Next log sequence to archive      1

Current log sequence                  1

2.) Only the DBNAME of a database  :  Changing the DBNAME without changing the DBID does not require us to open with the RESETLOGS option, so database backups and archived logs are not invalidated. However, changing the DBNAME does have consequences. We must change the DB_NAME initialization parameter after a database name change to reflect the new name. Also, we may have to re-create the Oracle password file. If we restore an old backup of the control file (before the name change), then we should use the initialization parameter file and password file from before the database name change.

Below are steps to change the database name  of the database :

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area         263639040 bytes

Fixed Size                               1373964 bytes

Variable Size                           209717492 bytes

Database Buffers                     46137344 bytes

Redo Buffers                          6410240 bytes

Database mounted.

SQL> host nid TARGET=SYS/test@test DBNAME=newtest SETNAME=YES

DBNEWID: Release 11.2.0.1.0 - Production on Mon May 30 14:35:45 2011

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

Connected to database TEST (DBID=2050636234)

Connected to server version 11.2.0

Control Files in database:

    D:\APP\NEERAJS\ORADATA\TEST\CONTROL01.CTL

    D:\APP\NEERAJS\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL

Change database name of database TEST to NEWTEST? (Y/[N]) => Y

Proceeding with operation

Changing database name from TEST to NEWTEST

Control File D:\APP\NEERAJS\ORADATA\TEST\CONTROL01.CTL - modified

Control File D:\APP\NEERAJS\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL - modified

    Datafile D:\APP\NEERAJS\ORADATA\TEST\SYSTEM01.DB - wrote new name

    Datafile D:\APP\NEERAJS\ORADATA\TEST\SYSAUX01.DB - wrote new name

    Datafile D:\APP\NEERAJS\ORADATA\TEST\UNDOTBS01.DB - wrote new name

    Datafile D:\APP\NEERAJS\ORADATA\TEST\USERS01.DB - wrote new name

    Datafile D:\APP\NEERAJS\ORADATA\TEST\EXAMPLE01.DB - wrote new name

    Datafile D:\APP\NEERAJS\ORADATA\TEST\TEMP01.DB - wrote new name

    Control File D:\APP\NEERAJS\ORADATA\TEST\CONTROL01.CTL - wrote new name

   Control File D:\APP\NEERAJS\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL - wrote   new name

    Instance shut down

Database name changed to NEWTEST. Modify parameter file and generate a new password file before restarting.Succesfully changed database name.

DBNEWID - Completed succesfully.

SQL> exit

C:\>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 30 14:36:39 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> create pfile='c:\inittest.ora' from spfile;

File created.

Now open the pfile inittest.ora and replace db_name parameter value "test"  to "newtest"

SQL> create spfile from  pfile='c:\inittest.ora' ;

File created.

SQL> startup

ORACLE instance started.

Total System Global Area           263639040 bytes

Fixed Size                                  1373964 bytes

Variable Size                              209717492 bytes

Database Buffers                       46137344 bytes

Redo Buffers                             6410240 bytes

Database mounted.

Database opened.

SQL> select open_mode ,name from v$database;

OPEN_MODE            NAME

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

READ WRITE           NEWTEST

3.) Both the DBNAME and DBID of a database

C:\>sqlplus sys/test@newtest as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 30 15:54:45 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select dbid, name,open_mode from v$database;

      DBID                       NAME                       OPEN_MODE

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

2050636234                   NEWTEST                 READ WRITE

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 30 16:01:03 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area             263639040 bytes

Fixed Size                                     1373964 bytes

Variable Size                                 213911796 bytes

Database Buffers                           41943040 bytes

Redo Buffers                                 6410240 bytes

Database mounted.

SQL> host nid target=sys/test@newtest dbname=prod

DBNEWID: Release 11.2.0.1.0 - Production on Mon May 30 16:11:17 2011

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

Connected to database NEWTEST (DBID=2050636234)

Connected to server version 11.2.0

Control Files in database:

    D:\APP\NEERAJS\ORADATA\TEST\CONTROL01.CTL

    D:\APP\NEERAJS\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL

Change database ID and database name NEWTEST to PROD? (Y/[N]) => Y

Proceeding with operation

Changing database ID from 2050636234 to 164330150

Changing database name from NEWTEST to PROD

Control File D:\APP\NEERAJS\ORADATA\TEST\CONTROL01.CTL - modified

Control FileD:\APP\NEERAJS\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL modifd

Datafile D:\APP\NEERAJS\ORADATA\TEST\SYSTEM01.DB - dbid changed, wrote new name

Datafile D:\APP\NEERAJS\ORADATA\TEST\SYSAUX01.DB - dbid changed, wrote new name

Datafile D:\APP\NEERAJS\ORADATA\TEST\UNDOTBS01.DB-dbid changed,wrote new name

Datafile D:\APP\NEERAJS\ORADATA\TEST\USERS01.DB - dbid changed, wrote new name

Datafile D:\APP\NEERAJS\ORADATA\TEST\EXAMPLE01.DB - dbid changed, wrote new name

Datafile D:\APP\NEERAJS\ORADATA\TEST\TEMP01.DB - dbid changed, wrote new name

Control File D:\APP\NEERAJS\ORADATA\TEST\CONTROL01.CTLdbid changd,wrote new name  Control File D:\APP\NEERAJS\FLASH_RECOVERY_AREA\TEST\CONTROL02.CTL - dbid changed, wrote new name 

Instance shut down

Database name changed to PROD.  Modify parameter file and generate a new password file before restarting. Database ID for database PROD changed to 164330150.  All previous backups and archived redo logs for this database are unusable.Database is not aware of previous backups and archived logs in  Recovery Area.  Database has been shutdown, open database with RESETLOGS option.  Succesfully changed database name and ID.

DBNEWID - Completed succesfully.

SQL> exit

C:\>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 30 16:43:18 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount

ORACLE instance started.

Total System Global Area  263639040 bytes

Fixed Size                  1373964 bytes

Variable Size             209717492 bytes

Database Buffers           46137344 bytes

Redo Buffers                6410240 bytes

Database mounted.

SQL> alter database open resetlogs;

Database altered.

SQL> select name,dbid,open_mode from v$database;

NAME              DBID                    OPEN_MODE

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

PROD            164330150                READ WRITE

Enjoy      :-)

ORA-27300: OS system dependent operation, ORA-27301 , ORA-27302

Once while monitering i found the the below error is periodically occuring in my alert log file. The error is 

Process startup failed, error stack:
Errors in file d:\app\diag\rdbms\royal1\royal1\trace\royal1_psp0_1700.trc:
ORA-27300 : OS system dependent operation:CreateThread failed with status: 8
ORA-27301 :  OS failure message: Not enough storage is available to process this command.
ORA-27302 : failure occurred at: ssthrddcr.

From the above error message , I guess that the above message has some relation with OS . On googling , i found that the above message may be a BUG and refer me to check Metalink BUG 3411021 . After going  through some documententaion, we come to the  conclusion that  : 

The system lacked the necessary resources to create another process, or the system-imposed limit on the total number of processes under execution system-wide or by a single user {CHILD_MAX} would be exceeded.

In my case, Our server is having 24GB of RAM and Database is on window server . When i have check the virtual memory(Swap Memory) , I found that it is having 24GB virtual memory and recommended memory is 36GB and it is system managed . Finally,I have decided to increase the Virtual Memory(Swap Memory) . I have increased the virtual memory to 40GB and on next time i found that no such error is occurring .

To solve this error ensure that the existing memory is sufficient and functioning properly. Check for OS log message for any hardware errors. The another option to solve this issue is to decrease the size of SGA so that OS process can get more memory. Hope this help to solve this issue.

Enjoy       :-)