RAC veritabanlarında , veritabanı id ve veritabanı adının değiştirilmesi

ORACLE
24 Eki by Ahmet Duruöz

RAC veritabanlarında , veritabanı id ve veritabanı adının değiştirilmesi

Bu makalede RAC şeklinde çalışan bir veritabanının id ve adının değiştirilmesi , öncesinde ve sonrasında hangi işlemlerin yapılması gerektiği anlatılmaktadır.

Örneklerde ismi ORCL olan veritabanının adının TEST olarak değiştirilmesi için gerekli işlemler yapılmıştır.

Öncelikle cluster_database parametresinin değerini false olarak değiştirerek işe başlayalım.

-bash-4.3$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 20 17:20:17 2017

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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> alter system set cluster_database=false scope=spfile sid='*';

System altered.

Veritabanımızı bir node üzerinden mount modda başlatalım.

-bash-4.3$ srvctl stop database -d ORCL


SQL> startup mount
ORACLE instance started.

Total System Global Area 3.8215E+10 bytes
Fixed Size                  2254416 bytes
Variable Size            6979324336 bytes
Database Buffers         3.1004E+10 bytes
Redo Buffers              229019648 bytes
Database mounted.

nid aracı ile veritabanı id ve isim bilgisini değiştirelim. DBNAME ile yeni veritabanı adını belirtiyoruz.

-bash-4.3$ nid TARGET=sys/welcome1@ORCL DBNAME=TEST

DBNEWID: Release 11.2.0.4.0 - Production on Fri Oct 20 17:22:12 2017

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

Connected to database ORCL (DBID=1485672324)

Connected to server version 11.2.0

Control Files in database:
    +DATA/orcl/controlfile/current.824.957891589
    +RECO/orcl/controlfile/current.49661.957891589

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

Proceeding with operation
Changing database ID from 1485672324 to 2256023237
Changing database name from ORCL to TEST
    Control File +DATA/orcl/controlfile/current.824.957891589 - modified
    Control File +RECO/orcl/controlfile/current.49661.957891589 - modified
    Datafile +DATA/orcl/datafile/system.846.95789151 - dbid changed, wrote new name
    Datafile +DATA/orcl/datafile/sysaux.849.95789151 - dbid changed, wrote new name
    Datafile +DATA/orcl/datafile/undotbs1.844.95789151 - dbid changed, wrote new name
    Datafile +DATA/orcl/datafile/users.843.95789151 - dbid changed, wrote new name
    Datafile +DATA/orcl/datafile/undotbs2.880.95789161 - dbid changed, wrote new name
    Datafile +DATA/orcl/tempfile/temp.879.95789159 - dbid changed, wrote new name
    Control File +DATA/orcl/controlfile/current.824.957891589 - dbid changed, wrote new name
    Control File +RECO/orcl/controlfile/current.49661.957891589 - dbid changed, wrote new name
    Instance shut down

Database name changed to TEST.
Modify parameter file and generate a new password file before restarting.
Database ID for database TEST changed to 2256023237.
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.

db_name parametresinin değerini yeni veritabanı ismi olacak şekilde değiştirelim.

SQL> startup mount
ORACLE instance started.

Total System Global Area 3.8215E+10 bytes
Fixed Size                  2254416 bytes
Variable Size            6979324336 bytes
Database Buffers         3.1004E+10 bytes
Redo Buffers              229019648 bytes
ORA-01103: database name 'TEST' in control file is not 'ORCL'


SQL> alter system set db_name=TEST scope=spfile sid='*';

System altered.


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

Tüm node’larda $ORACLE_HOME/dbs dizini altındaki ilgili initSID.ora dosyasının adını initYENISID.ora oalcak şekilde değiştirelim.

-bash-4.3$ cd $ORACLE_HOME/dbs
-bash-4.3$ mv initORCL1.ora initTEST1.ora

-bash-4.3$ ssh oradb2
-bash-4.3$ cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs
-bash-4.3$ mv initORCL2.ora initTEST2.ora

Yeni şifre dosyasını oluşturup tüm node’lara dağıtalım.

-bash-4.3$ cd $ORACLE_HOME/dbs
-bash-4.3$ orapwd file=orapwTEST1 password=welcome1 entries=10

-bash-4.3$ scp orapwTEST1 oradb2:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwTEST2
orapwTEST1                                                                                                                                                   100% 2560     2.5KB/s   00:00

Tüm node’larda listener’ların yeni veritabı ismini görmesini sağlayalım.

-bash-4.3$ . oraenv
ORACLE_SID = [TEST1] ? +ASM1
The Oracle base remains unchanged with value /u01/app/oracle
-bash-4.3$ lsnrctl reload

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 20-OCT-2017 17:35:20

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
The command completed successfully

Yeni SID ortam değişkenleri ile veritabanını mount modda başlatıp, resetlogs olacak şekilde açalım.

-bash-4.3$ . oraenv
ORACLE_SID = [TEST1] ? 
The Oracle base remains unchanged with value /u01/app/oracle
-bash-4.3$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 20 17:37:20 2017

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 3.8215E+10 bytes
Fixed Size                  2254416 bytes
Variable Size            4563405232 bytes
Database Buffers         3.3420E+10 bytes
Redo Buffers              229019648 bytes
Database mounted.
SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

cluster_database parametresini eski haline alıp, veritabanımızı tüm node’larda açalım.

SQL> alter system set cluster_database=true scope=spfile sid='*';

System altered.

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

-bash-4.3$ srvctl status database -d ORCL
Instance ORCL1 is not running on node oradb1
Instance ORCL2 is not running on node oradb2

Mevcut veritabanı konfigürasyonumuzu kontrol edelim.

-bash-4.3$ srvctl config database -d ORCL
Database unique name: ORCL
Database name: ORCL
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/ORCL/spfileORCL.ora
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: ORCL
Database instances: ORCL1,ORCL2
Disk Groups: DATA,RECO
Mount point paths: 
Services: 
Type: RAC
Database is administrator managed

Kullanılmakta olan spfile dosyasından pfile oluşturup, bu pfile da instance bazlı ayarlanmış parametrelerdeki * dan önceki SID değerinin yeni SID olacak şekide değiştirdikten sonra yeni spfile dosyasını oluşturalım.

-bash-4.3$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 20 17:47:23 2017

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

Connected to an idle instance.

SQL> create pfile='/tmp/testpfile' from spfile='+DATA/ORCL/spfileORCL.ora';

File created.

-bash-4.3$ vi /tmp/testpfile

*.audit_file_dest='/u01/app/oracle/admin/ORCL/adump'
*.audit_trail='db'
*.cluster_database=TRUE
*.compatible='11.2.0.4.0'
*.control_files='+DATA/orcl/controlfile/current.824.957891589','+RECO/orcl/controlfile/current.49661.957891589'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='TEST'
*.db_recovery_file_dest='+RECO'
*.db_recovery_file_dest_size=4621074432
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
TEST1.instance_number=1
TEST2.instance_number=2
*.open_cursors=300
*.pga_aggregate_target=12767461376
*.processes=150
*.remote_listener='oradb-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sga_target=38302384128
TEST2.thread=2
TEST1.thread=1
TEST2.undo_tablespace='UNDOTBS2'
TEST1.undo_tablespace='UNDOTBS1'

SQL> create spfile='+DATA/ORCL/spfileTEST.ora' from pfile='/tmp/testpfile';

File created.

CRS tarafında yeni veritabanı ve instance’ları oluşturalım.

-bash-4.3$ srvctl add database -d TEST -o /u01/app/oracle/product/11.2.0/dbhome_1 -p '+DATA/ORCL/spfileTEST.ora' -n TEST
-bash-4.3$ srvctl status database -d TEST
Database is not running.
-bash-4.3$ srvctl add instance -d TEST -i TEST1 -n oradb1
-bash-4.3$ srvctl add instance -d TEST -i TEST2 -n oradb2
-bash-4.3$  srvctl status database -d TEST
Instance TEST1 is not running on node oradb1
Instance TEST2 is not running on node oradb2

Yeni isimle oluşturulan veritabanı başlatalım.

-bash-4.3$  srvctl start database -d TEST
-bash-4.3$  srvctl status database -d TEST
Instance TEST1 is running on node oradb1
Instance TEST2 is running on node oradb2

CRS de eski isimle kayıtlı instance ve veritabanını silelim.

-bash-4.3$ srvctl remove instance -d ORCL -i ORCL1
Remove instance from the database ORCL? (y/[n]) y
-bash-4.3$ srvctl remove instance -d ORCL -i ORCL2
Remove instance from the database ORCL? (y/[n]) y
-bash-4.3$ srvctl remove database -d ORCL
Remove the database ORCL? (y/[n]) y

Bu aşamada , db_name ve db_unique_name parametreleri yeni isim olması beklenmektedir. ASM disk grupları içinde yeni isimle dizinler otomatik oluşması beklenmedir.

Veritabanımızı mount modda açıp, mevcut datafile’ların yeni ASM dizininde oluşmasını sağlayıp, eskilerini silelim.

-bash-4.3$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Oct 20 17:37:20 2017

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 3.8215E+10 bytes
Fixed Size                  2254416 bytes
Variable Size            4563405232 bytes
Database Buffers         3.3420E+10 bytes
Redo Buffers              229019648 bytes
Database mounted.

-bash-4.3$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Oct 20 18:15:29 2017

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

connected to target database: TEST (DBID=2256023237, not open)

RMAN> BACKUP AS COPY DATABASE FORMAT '+DATA';

Starting backup at 20-OCT-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=+DATA/orcl/datafile/system.846.957891511
output file name=+DATA/test/datafile/system.885.957896199 tag=TAG20171020T181638 RECID=7 STAMP=957896200
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=+DATA/orcl/datafile/sysaux.849.957891511
output file name=+DATA/test/datafile/sysaux.886.957896203 tag=TAG20171020T181638 RECID=8 STAMP=957896203
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=+DATA/orcl/datafile/undotbs1.844.957891511
output file name=+DATA/test/datafile/undotbs1.887.957896205 tag=TAG20171020T181638 RECID=9 STAMP=957896205
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA/orcl/datafile/undotbs2.880.957891619
output file name=+DATA/test/datafile/undotbs2.888.957896207 tag=TAG20171020T181638 RECID=10 STAMP=957896206
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DATA/test/controlfile/backup.889.957896209 tag=TAG20171020T181638 RECID=11 STAMP=957896208
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=+DATA/orcl/datafile/users.843.957891511
output file name=+DATA/test/datafile/users.890.957896209 tag=TAG20171020T181638 RECID=12 STAMP=957896209
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 20-OCT-17
channel ORA_DISK_1: finished piece 1 at 20-OCT-17
piece handle=+DATA/test/backupset/2017_10_20/nnsnf0_tag20171020t181638_0.891.957896211 tag=TAG20171020T181638 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20-OCT-17

RMAN> list copy of database;

List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time       
------- ---- - --------------- ---------- ---------------
7       1    A 20-OCT-17       1099335    20-OCT-17      
        Name: +DATA/test/datafile/system.885.957896199
        Tag: TAG20171020T181638

8       2    A 20-OCT-17       1099335    20-OCT-17      
        Name: +DATA/test/datafile/sysaux.886.957896203
        Tag: TAG20171020T181638

9       3    A 20-OCT-17       1099335    20-OCT-17      
        Name: +DATA/test/datafile/undotbs1.887.957896205
        Tag: TAG20171020T181638

12      4    A 20-OCT-17       1099335    20-OCT-17      
        Name: +DATA/test/datafile/users.890.957896209
        Tag: TAG20171020T181638

10      5    A 20-OCT-17       1099335    20-OCT-17      
        Name: +DATA/test/datafile/undotbs2.888.957896207
        Tag: TAG20171020T181638


RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA/test/datafile/system.885.957896199"
datafile 2 switched to datafile copy "+DATA/test/datafile/sysaux.886.957896203"
datafile 3 switched to datafile copy "+DATA/test/datafile/undotbs1.887.957896205"
datafile 4 switched to datafile copy "+DATA/test/datafile/users.890.957896209"
datafile 5 switched to datafile copy "+DATA/test/datafile/undotbs2.888.957896207"

RMAN> list copy of database;

List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time       
------- ---- - --------------- ---------- ---------------
13      1    A 20-OCT-17       1099335    20-OCT-17      
        Name: +DATA/orcl/datafile/system.846.957891511

14      2    A 20-OCT-17       1099335    20-OCT-17      
        Name: +DATA/orcl/datafile/sysaux.849.957891511

15      3    A 20-OCT-17       1099335    20-OCT-17      
        Name: +DATA/orcl/datafile/undotbs1.844.957891511

16      4    A 20-OCT-17       1099335    20-OCT-17      
        Name: +DATA/orcl/datafile/users.843.957891511

17      5    A 20-OCT-17       1099335    20-OCT-17      
        Name: +DATA/orcl/datafile/undotbs2.880.957891619


RMAN>  delete copy of database;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=113 instance=TEST1 device type=DISK
List of Datafile Copies
=======================

Key     File S Completion Time Ckp SCN    Ckp Time       
------- ---- - --------------- ---------- ---------------
13      1    A 20-OCT-17       1099335    20-OCT-17      
        Name: +DATA/orcl/datafile/system.846.957891511

14      2    A 20-OCT-17       1099335    20-OCT-17      
        Name: +DATA/orcl/datafile/sysaux.849.957891511

15      3    A 20-OCT-17       1099335    20-OCT-17      
        Name: +DATA/orcl/datafile/undotbs1.844.957891511

16      4    A 20-OCT-17       1099335    20-OCT-17      
        Name: +DATA/orcl/datafile/users.843.957891511

17      5    A 20-OCT-17       1099335    20-OCT-17      
        Name: +DATA/orcl/datafile/undotbs2.880.957891619


Do you really want to delete the above objects (enter YES or NO)? YES
deleted datafile copy
datafile copy file name=+DATA/orcl/datafile/system.846.957891511 RECID=13 STAMP=957896229
deleted datafile copy
datafile copy file name=+DATA/orcl/datafile/sysaux.849.957891511 RECID=14 STAMP=957896229
deleted datafile copy
datafile copy file name=+DATA/orcl/datafile/undotbs1.844.957891511 RECID=15 STAMP=957896229
deleted datafile copy
datafile copy file name=+DATA/orcl/datafile/users.843.957891511 RECID=16 STAMP=957896229
deleted datafile copy
datafile copy file name=+DATA/orcl/datafile/undotbs2.880.957891619 RECID=17 STAMP=957896229
Deleted 5 objects


RMAN> exit


Recovery Manager complete.

Veritabanımızı tüm node’larda başlatalım.

-bash-4.3$ srvctl start database -d TEST
-bash-4.3$ srvctl status database -d TEST
Instance TEST1 is running on node oradb1
Instance TEST2 is running on node oradb2

Datafile’ları sorguladığımızda hepsi ASM diskgrubundaki yeni veritabanı adıyla oluşmuş dizin altında görüldüğünden emin olalım.

SQL> select FILE#,NAME from v$datafile;

     FILE# NAME
---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
         1 +DATA/test/datafile/system.885.957896199
         2 +DATA/test/datafile/sysaux.886.957896203
         3 +DATA/test/datafile/undotbs1.887.957896205
         4 +DATA/test/datafile/users.890.957896209
         5 +DATA/test/datafile/undotbs2.888.957896207

Mevcut controlfile’ları ASM diskgrubundaki yeni veritabanı adıyla oluşmuş dizin altında taşıyalım.

		 
SQL> show parameter control

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
control_file_record_keep_time        integer
7
control_files                        string
+DATA/orcl/controlfile/current
.824.957891589, +RECO/orcl/con
trolfile/current.49661.9578915
89
control_management_pack_access       string
DIAGNOSTIC+TUNING


SQL> show parameter db_name

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
db_name                              string
TEST
SQL> show parameter db_unique_name

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
db_unique_name                       string
TEST

Mevcut control_files parametresini , bir adet eski controlfile ve oluşmasını istediğimiz disk grupları isimlerini içerecek şekilde değiştirelim.

SQL> alter system set control_files='+DATA/orcl/controlfile/current.824.957891589','+DATA','+RECO' scope=spfile sid='*';

System altered.

Veritabanımızı nomount modda başlatıp , restore controlfile işlemi ile yeni controlfile’ların oluşmasını sağlayalım.

-bash-4.3$ srvctl stop database -d TEST


SQL> startup nomount


-bash-4.3$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Oct 24 11:18:53 2017

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

connected to target database: TEST (not mounted)

RMAN> restore controlfile from '+DATA/orcl/controlfile/current.824.957891589';

Starting restore at 24-OCT-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=109 instance=TEST1 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA/orcl/controlfile/current.824.957891589
output file name=+DATA/test/controlfile/current.899.958216739
output file name=+RECO/test/controlfile/current.49227.958216739
Finished restore at 24-OCT-17

RMAN> exit


Recovery Manager complete.

Yukarıdaki adımda oluşan yeni veritabanı dizini altındaki (örnek : test) controlfile’lar ile mevcut control_files parametresini değiştirelim.

-bash-4.3$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 24 11:19:50 2017

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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> alter system set control_files='+DATA/test/controlfile/current.899.958216739','+RECO/test/controlfile/current.49227.958216739' scope=spfile sid='*';

System altered.

SQL> shu immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

Veritabanımızı başlatarak, yeni controlfile’ları kullandığından emin olalım.

-bash-4.3$ srvctl status database -d TEST
Instance TEST1 is not running on node oradb1
Instance TEST2 is not running on node oradb2
-bash-4.3$ srvctl start database -d TEST
-bash-4.3$ srvctl status database -d TEST
Instance TEST1 is running on node oradb1
Instance TEST2 is running on node oradb2
-bash-4.3$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 24 11:21:24 2017

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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> show parameter control

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
control_file_record_keep_time        integer
7
control_files                        string
+DATA/test/controlfile/current
.899.958216739, +RECO/test/con
trolfile/current.49227.9582167
39
control_management_pack_access       string
DIAGNOSTIC+TUNING

Daha önceki adımlarda spfile’ı ASM içindeki eski veritabanı adı altında yeni ismiyle oluşturmuştuk. Bu adımda yeni dizin altında yeni ismiyle tekrar oluşturacağız.

-bash-4.3$ srvctl config database -d TEST
Database unique name: TEST
Database name: TEST
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/ORCL/spfileTEST.ora
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: TEST
Database instances: TEST1,TEST2
Disk Groups: DATA,RECO
Mount point paths: 
Services: 
Type: RAC
Database is administrator managed
-bash-4.3$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 24 11:31:19 2017

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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> show parameter spfile

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
spfile                               string
+DATA/orcl/spfiletest.ora
SQL> 
SQL> 
SQL> 
SQL> 
SQL> 
SQL> create pfile='/tmp/pfiletest.ora' from spfile='+DATA/orcl/spfiletest.ora';

File created.

SQL> create spfile='+DATA/test/spfiletest.ora' from pfile='/tmp/pfiletest.ora';

File created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

Spfile değişimi sonrasında veritabanı konfigürasyonu değiştirerek yeni spfile’ı kullanmasını sağlayalım.

-bash-4.3$ srvctl modify database -d TEST -p +DATA/test/spfiletest.ora
-bash-4.3$ srvctl config database -d TEST
Database unique name: TEST
Database name: TEST
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/test/spfiletest.ora
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: TEST
Database instances: TEST1,TEST2
Disk Groups: DATA,RECO
Mount point paths: 
Services: 
Type: RAC
Database is administrator managed

Veritabanımızı kapatıp, tüm nodelardaki $ORACLE_HOME/dbs/initSID.ora dosyasındaki ilgili satırın bir önceki adımda veritabanı konfigürasyonunda belirttiğimiz spfile’ı gösterecek şekilde değiştirelim.

-bash-4.3$ srvctl stop database -d TEST


-bash-4.3$ cat $ORACLE_HOME/dbs/initTEST1.ora
SPFILE='+DATA/ORCL/spfileTEST.ora'              # line added by Agent


-bash-4.3$ vi $ORACLE_HOME/dbs/initTEST1.ora


-bash-4.3$ cat $ORACLE_HOME/dbs/initTEST1.ora
SPFILE='+DATA/test/spfiletest.ora'              # line added by Agent

Veritabanımızı açıp , konfigirasyonda yeni spfile’ın görüldüğünden ve yeni spfile ile açıldığından emin olalım.

-bash-4.3$ srvctl start database -d TEST
-bash-4.3$ srvctl config database -d TEST
Database unique name: TEST
Database name: TEST
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/test/spfiletest.ora
Domain: 
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: TEST
Database instances: TEST1,TEST2
Disk Groups: DATA,RECO
Mount point paths: 
Services: 
Type: RAC
Database is administrator managed

-bash-4.3$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 24 11:41:19 2017

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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> show parameter spfile

NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
spfile                               string
+DATA/test/spfiletest.ora

Son olarak mevcut temp tablo alanının yeniden oluşturarak, ASM’deki yeni dizini kullanmasını sağlayalım.

-bash-4.3$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 24 11:50:43 2017

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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> CREATE TEMPORARY TABLESPACE TEMP1 TEMPFILE '+DATA' SIZE 1G AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED TABLESPACE GROUP '' EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;

Database altered.

SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '+DATA' SIZE 1G AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED TABLESPACE GROUP '' EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

Database altered.

SQL> DROP TABLESPACE TEMP1 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

ASM disk grupları içindaki eski veritabanı ismiyle oluşmuş dizinleri silelim.

-bash-4.3$ . oraenv
ORACLE_SID = [TEST1] ? +ASM1
The Oracle base remains unchanged with value /u01/app/oracle

-bash-4.3$ asmcmd
ASMCMD> ls
DATA/
RECO/
ASMCMD> cd DATA
ASMCMD> rm -rf ORCL
ASMCMD> cd RECO
ASMCMD> rm -rf ORCL
ASMCMD>

Son kontrollerimizi yapalım. Tüm datafile ve tempfile’ların ASM deki yeni dizin altında olduğundan emin olalım.

-bash-4.3$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Oct 24 11:57:35 2017

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

connected to target database: TEST (DBID=2256023237)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name TEST

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    740      SYSTEM               ***     +DATA/test/datafile/system.885.957896199
2    570      SYSAUX               ***     +DATA/test/datafile/sysaux.886.957896203
3    150      UNDOTBS1             ***     +DATA/test/datafile/undotbs1.887.957896205
4    5        USERS                ***     +DATA/test/datafile/users.890.957896209
5    25       UNDOTBS2             ***     +DATA/test/datafile/undotbs2.888.957896207

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    1024     TEMP                 32767       +DATA/test/tempfile/temp.879.958218705

NOT : redolog lar otomatik olarak ASM’deki yeni dizin altında oluştuğundan , manuel olarak yeniden oluşturulmamıştır.

Loading

ByAhmet Duruöz

Bir yanıt yazın

E-posta adresiniz yayınlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir