Jephe Wu - http://linuxtechres.blogspot.com
Objective: Change all database files location for 11.2.0.3 database and RAC public ip/vip/scan ip etc
Environment: Oracle 11.2.0.3 RAC, 2 nodes, 2 VIPs, 3 SCAN IPs
References:
http://www.oracle-base.com/articles/misc/renaming-or-moving-oracle-files.php
Part I - Change Oracle 11gR2 RAC database files layout
1. check current database files info
sqlplus / as sysdba
sql> select name from v$datafile
sql> select name from v$tempfile;
sql> select member from v$logfile;
sql> archive log list;
sql> select dest_name, status, destination from v$archive_dest
sql> show parameter spfile;
sql> show parameter control;
sql> show parameter LOG_ARCHIVE_DEST_1;
sql> select * from database_properties
2. change all database files location - offline mode
---------control file and archive log file
sqlplus / as sysdba
sql> show parameter spfile;
sql> create pfile='/tmp/pfile' from spfile;
sql> shutdown immediate;
vi pfile to make changes for controlfiles and archive log location line
sqlplus / as sysdba
sql> startup nomount;
sql> create spfile='/u03/spfile/spfileDBID.ora' from pfile='/tmp/pfile';
sql> startup mount;
sql> alter database open;
sql> show parameter control_files;
---------online redo logs, data files, temp files
sqlplus / as sysdba
sql> shutdown immediate;
sql> startup mount;
sql> select name from v$datafile;
sql> select name from v$tempfile;
sql> select name from v$controlfile;
sql> select log_mode from v$database;
sql> select member from v$logfile;
sql> host
cp -va /u02/oradata/DBID/*.dbf /u03/oradata/DBID/
cp -va /u02/oradata/DBID/redo* /u04/oralog/DBID/
exit
sql> alter database rename file '/u02/oradata/DBID/file1.dbf' to '/u03/oradata/DBID/file1.dbf';
Note: do above for all output from 'select name from v$datafile;' and 'select name from v$tempfile;'
sql> alter database open;
3. change all database files location - online mode
------ control file
sql> show parameter control_files;
while database is running/online, run
sql>alter system set control_files = '/u09/control/DBNAME/control01.ctl', '/u09/control/DBNAME/control02.ctl','/u09/control/DBNAME/control03.ctl'
scope=spfile;
sql>shutdown immediate;
--------redo log
sqlplus / as sysdba
sql> select * from v$log; # show thread and group numbers
sql> select a.group#,a.member,b.status,b.archived,bytes from v$logfile a, v$log b where a.group# = b.group# order by 1,2;
or
sql> select a.group#,a.member,b.status,b.archived,bytes/1024/1024 mbytes from v$logfile a, v$log b where a.group# = b.group# order by 1,2;
sql>
# check which thread for which node:
sql> select * from v$instance;
or
grep -i instance /tmp/pfile
or
strings spfilename | grep -i instance
add 2 more groups for each thread
ALTER DATABASE ADD LOGFILE [THREAD 1]
GROUP 5 ('/u04/oralog/NRMAPS0/redo05.log') size 100M,
GROUP 6 ('/u04/oralog/NRMAPS0/redo06.log') size 100M;
ALTER DATABASE ADD LOGFILE [THREAD 2]
GROUP 7 ('/u04/oralog/NRMAPS0/redo07.log') size 100M,
GROUP 8 ('/u04/oralog/NRMAPS0/redo08.log') size 100M;
alter database drop logfile group 1;
---------- data file
normal method:
If database is in archive log mode and the datafile is not system tablespace.
sql> ALTER DATABASE DATAFILE '/old/location' OFFLINE;
SQL> ALTER DATABASE RENAME FILE '/old/location' TO '/new/location';
SQL> RECOVER DATAFILE '/new/location';
SQL> ALTER DATABASE DATAFILE '/new/location' ONLINE;
You can online rename datafiles provided that datafile is not in SYSTEM tablespace.
sql> select name from v$tablespace;
sql> SELECT FILE_NAME, STATUS FROM DBA_DATA_FILES WHERE TABLESPACE_NAME = 'USERS';
sql> alter tablespace users read only;
sql> SELECT TABLESPACE_NAME, STATUS FROM DBA_TABLESPACES where tablespace_name = 'USERS';
sql> host
use os utiliy to copy files from old location to new location
sql> alter tablespace users offline;
sql> alter database rename file 'old path' to 'new path';
sql> alter tablespace users online;
sql> alter tablespace users read write;
host
delete/move old files
rman method:
rman> report schema;
rman> copy datafile 3 to '/new/path/filename';
RMAN> SQL 'ALTER TABLESPACE xyz OFFLINE';
RMAN> SWITCH DATAFILE 3 TO COPY; RMAN> RECOVER TABLESPACE xyz;
RMAN> SQL 'ALTER TABLESPACE soe ONLINE';
RMAN> HOST 'rm /old/path/filename';
rman> report schema;
-----------temp file
sql> SELECT v.file#, t.file_name, v.status from dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#;
sql> ALTER DATABASE TEMPFILE '/path/to/file' offline;
sql> SELECT v.file#, t.file_name, v.status from dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#;
sql> !cp -va /path/to/oldfile /path/to/newfile
sql> alter database rename file 'oldfile' to 'newfile';
sql> SELECT v.file#, t.file_name, v.status from dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#;
sql> ALTER DATABASE TEMPFILE '/path/to/file' online;
sql> !rm -fr oldfile
------------archive log location
sql> set line 32000
sql> select dest_name, status, destination from v$archive_dest
sql> show parameter LOG_ARCHIVE_DEST_1;
sql> archive log list;
sql> alter system set log_archive_dest_1='LOCATION=/u05/oraarch/DBID';
sql> alter system archive log current;
4. change spfile, password file and ocr/votedisk etc
-------spfile
cd $ORACLE_HOME/dbs
normally, it's /u01/app/oracle/product/11.2.0/dbhome_1/dbs
vi initDBID.ora
put spfile location there like this:
SPFILE='/u12/spfile/spfileyourDBSID.ora'
then you need to change spfile location in clusterware as follows in oracle or root user:
/u01/app/11.2.0/grid/bin/srvctl modify database -d DBSID -p /u12/spfile/spfileyourDBSID.ora
------password file
create symbolic link under $ORACLE_HOME/dbs/orapwinstanceID pointing to centralized location for accessing from all nodes.
e.g.
database name is RACDB, two instance ID are RACDB1 and RACDB2
more $ORACLE_HOME/dbs/initRACDB1.ora
SPFILE='/u12/spfile/spfileRACDB.ora'
ls -l $ORACLE_HOME/dbs/orapwRACDB01.ora
orapwRACDB01.ora -> /u12/passwdfile/orapwRACDB
on another node, it's:
more $ORACLE_HOME/dbs/initRACDB2.ora
SPFILE='/u12/spfile/spfileRACDB.ora'
ls -l $ORACLE_HOME/dbs/orapwRACDB02.ora
orapwRACDB02.ora -> /u12/passwdfile/orapwRACDB
-------------ocr
online change ocr location:
touch ocrdisk;chown root:oinstall ocrdisk; chmod 640 ocrdisk # make new ocr same permission as the existing ones
/u01/app/11.2.0/grid/bin/ocrcheck
/u01/app/11.2.0/grid/bin/ocrconfig -add /u12/crscfg/ocrdisk
/u01/app/11.2.0/grid/bin/ocrconfig -add /u12/crscfg/ocrdisk_mirror
/u01/app/11.2.0/grid/bin/ocrconfig -delete /u02/crscfg/ocr
------------votedisk
/u01/app/11.2.0/grid/bin/crsctl query css votedisk
crsctl query crs activeversion
/u01/app/11.2.0/grid/bin/crsctl add css votedisk /u12/crscfg/votedisk2
/u01/app/11.2.0/grid/bin/crsctl add css votedisk /u12/crscfg/votedisk3
/u01/app/11.2.0/grid/bin/crsctl delete css votedisk /u02/crscfg/vdsk
Part II - Change Public/Private/VIP/SCANIP etc
---------public ip
Refer to How to Modify Public Network Information Including VIP in Oracle Clusterware [ ID 276434.1 ]
You can change public NIC IP first then change ip in clusterware, or you can keep old ip, change clusterware info first, then change OS IP.
change OS IP first
then check and make sure clusterware is running by running commands below:
/u01/app/11.2.0/grid/bin/olsnodes -s
/u01/app/11.2.0/grid/bin/crsctl check clusterware -all
check public ip in OCR and clusterware:
in clusterware_interconnects parameter: oifcfg iflist -p -n
in OCR: oifcfg getif
from sqlplus : SELECT INST_ID, NAME_KSXPIA, IP_KSXPIA, PICKED_KSXPIA FROM X$KSXPIA;
debug interconnect traffic:
sqlplus / as sysdba
sql> oradebug setmypid
sql> oradebug ipc
then try to find the trace file under USER_DUMP_DEST parameter directory.
sql> show parameter user_dump_dest;
from sqlplus again:
sql> select * from v$cluster_initerconnects;
sql> select * from v$configured_interconnects;
oifcfg setif -global eth0/10.1.1.0:public
oifcfg delif -global eth0/10.2.1.0
--check network after changing public ip:
srvctl config network
srvctl modify network -k 1 -S 10.1.1.0/255.255.255.0/eth0
--stop/start clusterware:
srvctl stop cluster -all
srvctl start cluster -all
crs_stat -t
crsctl stat res -t
---------------VIP
Refer to: How to Modify Private Network Information in Oracle clusterware [ ID 283684.1 ]
check current VIPs
srvctl config nodeapps -a
crsctl stat res ora.db01.vip -p
use 'ifconfig eth0 192.168.2.1 netmask 255.255.255.0 up' to config OS ip first, then run
oifcfg setif -global eth3/192.168.2.0:cluster_interconnect
---------------SCANIP
refer to How to Modify SCAN settings or SCAN listener port after installation [ ID 972500.1 ]
crsctl stat res ora.scan1.vip -p
srvctl stop scan -f
crs_stat # to check scan name
srvctl modify scan -n db01-scan1
If SCANIP is using DNS name, you don't have to change it, just change DNS config.
if it's using /etc/hosts, and above command 'crsctl stat res ora.scan1.vip -p' shows you are using name for scan ip, you can add dummy line for
/etc/hosts for that scan ip
e.g.
root@db01:~# tail -3 /etc/hosts
#SCAN
10.12.1.200 db-scan.domain.com db-scan
10.12.1.200 db-scan1.domain.com db-scan1 # manually add this line
then modify it to db-scan1 first,then modify it back to db-scan
srvctl modify scan -n db-scan1
srvctl modify scan -n db-scan
then check it 'crsctl stat res ora.scan1.vip -p' , confirm it's using new IP address for SCAN IP
srvctl config scan
srvctl config scan_listener
srvctl modify scan_listener -u
Part III - Other RAC commands and troubleshooting
-------------misscount,reboottime and disktimeout
refer to Steps To Change CSS Misscount, Reboottime and Disktimeout [ID 284752.1]
and
CSS Timeout Computation in Oracle Clusterware [ID 294430.1]
With 11gR2, these settings can be changed online without taking any node down:
1) Execute crsctl as root to modify the misscount:
$CRS_HOME/bin/crsctl set css misscount <n> #### where <n> is the maximum private network latency in seconds
$CRS_HOME/bin/crsctl set css reboottime <r> [-force] #### (<r> is seconds)
$CRS_HOME/bin/crsctl set css disktimeout <d> [-force] #### (<d> is seconds)
2) Execute crsctl as root to confirm the change:
$CRS_HOME/bin/crsctl get css misscount
$CRS_HOME/bin/crsctl get css reboottime
$CRS_HOME/bin/crsctl get css disktimeout
---------crs_stat -p
to check all detail settings for crs
crsctl enable crs # enable startup for all crs daemons
crsctl disable crs
crsctl query crs softwareversion
crsctl query cdrs activeversion
crsctl check crs
crs_stat -t
crs_stat -p
crs_stat -ls
crsctl debug log res "ora.sdrac01.vip:5"
ocrconfig -showbackup
---------use srvctl to manage database resource
srvctl status database -d racdb
srvctl stop database -d racdb
srvctl start database -d racdb
srvctl stop instance -d racdb -i racdb1
srvctl start instance -d racdb -i racdb1
Note: it's recommended to use srvctl utility to manage database, otherwise, sometimes, if you use sqlplus to stop it, then its status is still online
in clusterware. you can use crs_stop to stop it then.
---------troubleshooting
select * from v$diag_info; # show all alert and diagostic locations info
Part IV - find Oracle GI PSU and Oracle database latest patches
Bug 14727347 - 11.2.0.3.5 Grid Infrastructure Patch Set Update (GI PSU) [ID 14727347.8]
Patch 14727347: GRID INFRASTRUCTURE PATCH SET UPDATE 11.2.0.3.5 (INCLUDES DB PSU 11.2.0.3.5)
Posts
