SHOW COUNTER

free counter | dedicated server

How to change db_name in oracle database using dbnewid utility ?

change db_name in oracle database using dbnewid

SQL> startup nomount
ORACLE instance started.

Total System Global Area  608174080 bytes
Fixed Size                  1220844 bytes
Variable Size             180358932 bytes
Database Buffers          423624704 bytes
Redo Buffers                2969600 bytes
SQL> alter database mount;

Database altered.

SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      orcl
SQL>

[oracle@localhost ~]$ nid target=sys/orcl dbname=anurag setname=yes

DBNEWID: Release 10.2.0.1.0 - Production on Wed Apr 17 17:36:59 2013

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

Connected to database ORCL (DBID=1334973392)

Connected to server version 10.2.0

Control Files in database:
    /oracle/product/10.2.0/oradata/orcl/control01.ctl
    /oracle/product/10.2.0/oradata/orcl/control02.ctl
    /oracle/product/10.2.0/oradata/orcl/control03.ctl

Change database name of database ORCL to ANURAG? (Y/[N]) => Y

Proceeding with operation
Changing database name from ORCL to ANURAG
    Control File /oracle/product/10.2.0/oradata/orcl/control01.ctl - modified
    Control File /oracle/product/10.2.0/oradata/orcl/control02.ctl - modified
    Control File /oracle/product/10.2.0/oradata/orcl/control03.ctl - modified
    Datafile /oracle/product/10.2.0/oradata/orcl/system01.dbf - wrote new name
    Datafile /oracle/product/10.2.0/oradata/orcl/undotbs01.dbf - wrote new name
    Datafile /oracle/product/10.2.0/oradata/orcl/sysaux01.dbf - wrote new name
    Datafile /oracle/product/10.2.0/oradata/orcl/users01.dbf - wrote new name
    Datafile /oracle/product/10.2.0/oradata/orcl/example01.dbf - wrote new name
    Datafile /oracle/product/10.2.0/oradata/orcl/temp01.dbf - wrote new name
    Control File /oracle/product/10.2.0/oradata/orcl/control01.ctl - wrote new name
    Control File /oracle/product/10.2.0/oradata/orcl/control02.ctl - wrote new name
    Control File /oracle/product/10.2.0/oradata/orcl/control03.ctl - wrote new name
    Instance shut down

Database name changed to ANURAG.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.


AFTER THIS

STEP1 - CHANGE PFILE IN DB_NAME=ANURAG

SQL> startup pfile='/oracle/product/10.2.0/admin/orcl/pfile/init.ora.1122013121215';
ORACLE instance started.

Total System Global Area  608174080 bytes
Fixed Size                  1220844 bytes
Variable Size             167776020 bytes
Database Buffers          436207616 bytes
Redo Buffers                2969600 bytes
Database mounted.
Database opened.
SQL>

SQL> select name from v$database;

NAME
---------
ANURAG

Important Notes

• If the DBID is changed, all previous RMAN backups become unusable unless they are cataloged appropriately.
• Changing only the DBNAME is less disruptive than changing both DBNAME and DBID.
• Always perform the activity during a maintenance window.
• Test the procedure in a non-production environment before executing it in production.
• Ensure Oracle Net configuration files (tnsnames.ora, listener.ora) and monitoring tools are updated with the new database name.

Export (EXPDP ) Error : ORA-31633,ORA-01950,ORA-29283,ORA-29283

Perform export/import operation using datapump

then imp_full_database/exp_full_database privilege are required



[oracle@localhost ~]$ expdp import/export directory=datapump dumpfile=orcl.dmp logfile=orcl.log



Export: Release 10.2.0.1.0 - Production on Saturday, 13 April, 2013 13:03:51

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-31626: job does not exist
ORA-31633: unable to create master table "IMPORT.SYS_EXPORT_SCHEMA_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-01950: no privileges on tablespace 'USERS'



this error is resolved by


alter user import quota unlimited on users;


means to give the quota to user on users tablespace.


with datapump export

Export: Release 10.2.0.1.0 - Production on Monday, 15 April, 2013 14:03:24

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation


to check the permission on folder

because the expdp utility not perform write operation on directory