Nilesh Oracle DBA Blog: 01/12/21

How to Change Database Name.

Scenario :- Unix Team Took Snapshot Current QA Server and Created a new Server with QAtest Name

And here I have to open a database with newname QRT from PLO.

On New Server Lets login.

export ORACLE_SID=PLO

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 12 22:27:38 2021

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select name from v$database;
NAME
---------
PL0
SQL> select open_mode,name from V$database;
OPEN_MODE NAME
-------------------- --------- =====> DB name PL0
MOUNTED PL0

Take Backup of Existing Redologfile which are need for recovery. ======>

$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 12 22:24:04 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/oracle/PL0/origlogB/log_g4m1.dbf
/oracle/PL0/mirrlogB/log_g4m2.dbf
/oracle/PL0/origlogA/log_g3m1.dbf
/oracle/PL0/mirrlogA/log_g3m2.dbf
/oracle/PL0/origlogB/log_g2m1.dbf
/oracle/PL0/mirrlogB/log_g2m2.dbf
/oracle/PL0/origlogA/log_g1m1.dbf
/oracle/PL0/mirrlogA/log_g1m2.dbf
8 rows selected.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

$ cp /oracle/PL0/origlogB/log_g4m1.dbf /oracle/PL0/origlogB/log_g4m1.dbf_bkp
$ cp /oracle/PL0/mirrlogB/log_g4m2.dbf /oracle/PL0/mirrlogB/log_g4m2.dbf_bkp
$ cp /oracle/PL0/origlogA/log_g3m1.dbf /oracle/PL0/origlogA/log_g3m1.dbf_bkp
$ cp /oracle/PL0/mirrlogA/log_g3m2.dbf /oracle/PL0/mirrlogA/log_g3m2.dbf_bkp
$ cp /oracle/PL0/origlogB/log_g2m1.dbf /oracle/PL0/origlogB/log_g2m1.dbf_bkp
$ cp /oracle/PL0/mirrlogB/log_g2m2.dbf /oracle/PL0/mirrlogB/log_g2m2.dbf_bkp
$ cp /oracle/PL0/origlogA/log_g1m1.dbf /oracle/PL0/origlogA/log_g1m1.dbf_bkp
$ cp /oracle/PL0/mirrlogA/log_g1m2.dbf /oracle/Pl0/mirrlogA/log_g1m2.dbf_bkp

Take Controlfile Backup as trace and it located on ===>

./oracle/diag/rdbms/qrt/QRT/trace/

location.

SQL> startup Mount

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 5.3447E+10 bytes

Fixed Size 2265864 bytes

Variable Size 1.7717E+10 bytes

Database Buffers 3.5702E+10 bytes

Redo Buffers 26480640 bytes

Database mounted.

Database opened.

SQL> alter database backup controlfile to trace;

Database altered.

SQL> select * from V$diag_info;

INST_ID NAME

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

VALUE

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

1 Diag Cdump

/oracle/diag/rdbms/qrt/QRT/cdump

1 Health Monitor

/oracle/diag/rdbms/qrt/QRT/hm

1 Default Trace File

/oracle/diag/rdbms/qrt/QRT/trace/QRT_ora_12755.trc

Update the Controlfile trace. and update REUSE with SET.

STARTUP NOMOUNT

CREATE CONTROLFILE SET DATABASE "QRT" RESETLOGS ARCHIVELOG

MAXLOGFILES 255

MAXLOGMEMBERS 2

MAXDATAFILES 3000

MAXINSTANCES 2

MAXLOGHISTORY 22495

LOGFILE

GROUP 1 (

'/oracle/QRT/origlogA/log_g1m1.dbf',

'/oracle/QRT/mirrlogA/log_g1m2.dbf'

) SIZE 250M BLOCKSIZE 512,

GROUP 2 (

'/oracle/QRT/origlogB/log_g2m1.dbf',

'/oracle/QRT/mirrlogB/log_g2m2.dbf'

) SIZE 250M BLOCKSIZE 512,

GROUP 3 (

'/oracle/QRT/origlogA/log_g3m1.dbf',

'/oracle/QRT/mirrlogA/log_g3m2.dbf'

) SIZE 250M BLOCKSIZE 512,

GROUP 4 (

'/oracle/QRT/origlogB/log_g4m1.dbf',

'/oracle/QRT/mirrlogB/log_g4m2.dbf'

) SIZE 250M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'/oracle/QRT/sapdata1/system_1/system.data1',

'/oracle/QRT/sapdata1/undo_1/undo.data1',

'/oracle/QRT/sapdata2/undo_2/undo.data2',

'/oracle/QRT/sapdata1/undo_3/undo.data3',

'/oracle/QRT/sapdata2/undo_4/undo.data4',

'/oracle/QRT/sapdata1/sysaux_1/sysaux.data1',

'/oracle/QRT/sapdata2/dat_1/dat.data1',

'/oracle/QRT/sapdata2/dat_2/dat.data2',

'/oracle/QRT/sapdata5/dat_3/dat.data3',

'/oracle/QRT/sapdata3/dat_4/dat.data4'

CHARACTER SET UTF8

;

Shut down The database instance

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 12 22:33:57 2021

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

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

SQL> shut abort

ORACLE instance shut down.

Take Backup of Controlfile ====>

dp3:oraQRT 71> grep con initQRT.ora

*.control_file_record_keep_time=30

*.control_files='/oracle/QRT/origlogA/cntrl/cntrlQRT.dbf','/oracle/QRT/origlogB/cntrl/cntrlQRT.dbf','/oracle/QRT/sapdata1/cntrlrlQRT.dbf'

dp3:oraQRT 72>

$ cd /oracle/QWT/origlogA/cntrl/

$ mv cntrlQWT.dbf cntrlQRT.dbf_bkp

mv/oracle/QRT/origlogB/cntrl/cntrlQRT.dbf','/oracle/QRT/sapdata1/cntrlrlQRT.dbf_bkp'

Now to startup a Database with QRT name.

export ORACLE_SID=QRT
$ ps -ef|grep pmon
oraQRT 9987 8763 0 22:36 pts/2 00:00:00 grep pmon
$
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 12 22:36:56 2021
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> @new_con.sql
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 5.3447E+10 bytes
Fixed Size 2265864 bytes
Variable Size 1.7717E+10 bytes
Database Buffers 3.5702E+10 bytes
Redo Buffers 26480640 bytes
Control file created.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oracle/QRT/sapdata1/system_1/system.data1'

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 31328916885 generated at 01/09/2021 12:53:52 needed for
thread 1
ORA-00289: suggestion : /oracle/QRT/oraarch/QRTarch1_14978_1036372415.dbf
ORA-00280: change 31328916885 for thread 1 is in sequence #14978

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oracle/P10/mirrlogB/log_g2m2.dbf_bkp
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string QWT
SQL>
SQL>
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 5.3447E+10 bytes
Fixed Size 2265864 bytes
Variable Size 1.7717E+10 bytes
Database Buffers 3.5702E+10 bytes
Redo Buffers 26480640 bytes
Database mounted.
Database opened.

SQL> select name from V$database;

NAME

---------

QWT

===============================EOD===============================

Nilesh Oracle DBA Blog

Tuesday, January 12, 2021

How to Change Database Name Using Control file trace

Report Abuse

Labels