Changing db_name and db_id on RAC database

Changing   db_name  and  db_id  on RAC  database

1         Introduction

Avoir  le   même  dbid pour 2  bd   pose  un problème  ,sauf   si on  a  dataguard, si  on  a  un catalog  RMAN .Les  backup   risque  de se mélanger  car le  catalogue se base  sur le  dbid.

Si  on  a   pas de  dataguard âpres  un  duplicate  il  est  bon de  modifier  le  db_id.

L’idée  est  de  changer  le  db_name et  le  dbid  via  la   commande  nid  sur un  cluster.

Pour connaitre  le  dbid

select DBID,NAME from  V$database;

Voila   l’objectif

Bd  source			BD cible
instance name	db_name	db_id	instance name	db_name	db_id
MARIAN1	MARIAN	3885168184	FRANCOIS1	FRANCOIS	2944322690
MARIAN2			FRANCOIS2

2         Préalable :

2.1      Backup  du  spfile   en pfile (dans $ORACLE_HOME/dbs)

Create  pfile=marian.ora  from  spfile;

2.2      Creer  orapwd  pour  les nouvelles  instances

orapwd file='$ORACLE_HOME/dbs/orapwFRANCOIS1' password=manager  ignorecase=y

Copier  le  orapwd  sur les  nœuds

Scp  $ORACLE_HOME/dbs/orapwFRANCOIS1 pocracm2 : $ORACLE_HOME/dbs/orapwFRANCOIS2

2.3      Modifier  le   pfile  generer

Fichier  initial :

*.audit_file_dest='/apps/oracle/admin/MARIAN/adump'

*.audit_trail='db'

*.cluster_database=TRUE

*.compatible='11.2.0.0.0'

*.control_files='+POCDB/marian/controlfile/current.324.805281849'

*.db_block_size=8192

*.db_create_file_dest='+POCDB'

*.db_domain=''

*.db_name='MARIAN'

*.diagnostic_dest='/apps/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=MARIANXDB)'

MARIAN2.instance_number=2

MARIAN1.instance_number=1

*.open_cursors=300

*.pga_aggregate_target=268435456

*.processes=150

*.remote_listener='poccrs:1521'

*.remote_login_passwordfile='exclusive'

*.sga_target=805306368

MARIAN2.thread=2

MARIAN1.thread=1

MARIAN1.undo_tablespace='UNDOTBS1'

MARIAN2.undo_tablespace='UNDOTBS2'

Devient :

*.audit_file_dest='/apps/oracle/admin/MARIAN/adump'

*.audit_trail='db'

*.cluster_database=FALSE

*.compatible='11.2.0.0.0'

*.control_files='+POCDB/marian/controlfile/current.324.805281849'

*.db_block_size=8192

*.db_create_file_dest='+POCDB'

*.db_domain=''

*.db_name='MARIAN'

*.diagnostic_dest='/apps/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=MARIANXDB)'

MARIAN2.instance_number=2

MARIAN1.instance_number=1

*.open_cursors=300

*.pga_aggregate_target=268435456

*.processes=150

*.remote_listener='poccrs:1521'

*.remote_login_passwordfile='exclusive'

*.sga_target=805306368

MARIAN2.thread=2

MARIAN1.thread=1

MARIAN1.undo_tablespace='UNDOTBS1'

MARIAN2.undo_tablespace='UNDOTBS2'

3         Nid

3.1      Arret  de la bd  en  cluster  et  start   sur une  instance.

Srvctl   stop  database  -d MARIAN

Sqlplus  / as sysdba

Startup mount   pfile=/apps/oracle/11.2.0.2/dbs/marian.ora

3.2      Nid  commande

[pocracm1.noam.transat.local:oracle:MARIAN1:/home/oracle/mg]# nid target=sys/manager  DBNAME=FRANCOIS

DBNEWID: Release 11.2.0.2.0 - Production on Mon Jan 21 10:26:10 2013

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

Connected to database MARIAN (DBID=3885168184)

Connected to server version 11.2.0

Control Files in database:

    +POCDB/marian/controlfile/current.324.805281849

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

Proceeding with operation

Changing database ID from 3885168184 to 2944322690

Changing database name from MARIAN to FRANCOIS

    Control File +POCDB/marian/controlfile/current.324.805281849 - modified

    Datafile +POCDB/marian/datafile/system.320.80528176 - dbid changed, wrote new name

    Datafile +POCDB/marian/datafile/sysaux.321.80528176 - dbid changed, wrote new name

    Datafile +POCDB/marian/datafile/undotbs1.322.80528176 - dbid changed, wrote new name

    Datafile +POCDB/marian/datafile/users.323.80528176 - dbid changed, wrote new name

    Datafile +POCDB/marian/datafile/undotbs2.328.80528191 - dbid changed, wrote new name

    Datafile +POCDB/marian/tempfile/temp.327.80528188 - dbid changed, wrote new name

    Control File +POCDB/marian/controlfile/current.324.805281849 - dbid changed, wrote new name

    Instance shut down

Database name changed to FRANCOIS.

Modify parameter file and generate a new password file before restarting.

Database ID for database FRANCOIS changed to 2944322690.

All previous backups and archived redo logs for this database are unusable.

Database has been shutdown, open database with RESETLOGS option.

Succesfully changed database name and ID.

DBNEWID - Completed succesfully.

4         Post  nid

4.1      Modifier l init

*.audit_file_dest='/apps/oracle/admin/FRANCOIS/adump'

*.audit_trail='db'

*.cluster_database=false

*.compatible='11.2.0.0.0'

*.control_files='+POCDB/marian/controlfile/current.324.805281849'

*.db_block_size=8192

*.db_create_file_dest='+POCDB'

*.db_domain=''

*.db_name='FRANCOIS'

*.diagnostic_dest='/apps/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=FRANCOISXDB)'

FRANCOIS2.instance_number=2

FRANCOIS1.instance_number=1

*.open_cursors=300

*.pga_aggregate_target=268435456

*.processes=150

*.remote_listener='poccrs:1521'

*.remote_login_passwordfile='exclusive'

*.sga_target=805306368

FRANCOIS2.thread=2

FRANCOIS1.thread=1

FRANCOIS1.undo_tablespace='UNDOTBS1'

FRANCOIS2.undo_tablespace='UNDOTBS2'

Creer  l arborescence /apps/oracle/admin/FRANCOIS/adump  sur les 2  neouds

4.2      Creer un  spfile  sur  ASM

SQL>  startup  nomount  pfile=francois.ora

ORACLE instance started.

Total System Global Area  801701888 bytes

Fixed Size                  2230768 bytes

Variable Size             222299664 bytes

Database Buffers          570425344 bytes

Redo Buffers                6746112 bytes

SQL> create spfile='+POCDB'  from  pfile='/apps/oracle/11.2.0.2/dbs/francois.ora';

SQL>shutdown  immediate

Avec  les commandes asmcmd  on va   rechercher  le  nommage dus spfile

ASMCMD> pwd

+POCDB/FRAMCOIS/PARAMETERFILE

ASMCMD> ls

spfile.333.805292609

ASMCMD>

Creer  des pfile dans   $ORACLE_HOME/dbs

[pocracm1.noam.transat.local:oracle:MG1:/apps/oracle/11.2.0.2/dbs]# vi   initMG1.ora

spfile='+POCDB/FRANCOIS/PARAMETERFILE/spfile.333.805292609'

scp initFRANCOIS1.ora pocracm2:/apps/oracle/11.2.0.2/dbs/initFRANCOIS2.ora

4.3      Modifier  le  parameter  cluster_database  et Ouvrir  l instance  en  resetlogs

SQL> alter  system  set cluster_database=true scope=spfile;

SQL> alter  database  open  resetlogs;

4.4      Modifier  le  clusteware

srvctl add database -d FRANCOIS -o /apps/oracle/11.2.0.2  -p '+POCDB/FRANCOIS/PARAMETERFILE/spfile.332.805286197'

srvctl  add  instance -i  FRANCOIS1 -n pocracm1 -d FRANCOIS                                                     

srvctl  add  instance -i  FRANCOIS2 -n pocracm2 -d FRANCOIS      

srvctl start  database  -d FRANCOIS      

srvctl  config  database –d FRANCOIS                                       

Supprimer  la  bd Marian dans  le  clusterware

srvctl   remove  database  -d MARIAN

Remove the database MARIAN? (y/[n]) y

Attention les  datafiles et  le controlfiles sont dans  l asm   sur l arborecence  MARIAN :

NAME

+POCDB/marian/datafile/system.320.805281767

+POCDB/marian/datafile/sysaux.321.805281769

+POCDB/marian/datafile/undotbs1.322.805281769

+POCDB/marian/datafile/users.323.805281769

+POCDB/marian/datafile/undotbs2.328.805281919

+POCDB/marian/controlfile/current.324.805281849