June 2020
M T W T F S S
« Mar    
1234567
891011121314
15161718192021
22232425262728
2930  

Categories

WordPress Quotes

A person who has a cat by the tail knows a whole lot more about cats than someone who has just read about them.
Mark Twain
June 2020
M T W T F S S
« Mar    
1234567
891011121314
15161718192021
22232425262728
2930  

Short Cuts

2012 SERVER (64)
2016 windows (9)
AIX (13)
Amazon (40)
Ansibile (19)
Apache (135)
Asterisk (2)
cassandra (2)
Centos (211)
Centos RHEL 7 (270)
centos8 (3)
chef (3)
cloud (2)
cluster (3)
Coherence (1)
DB2 (5)
DISK (25)
DNS (9)
Docker (30)
Eassy (11)
ELKS (1)
EXCHANGE (3)
Fedora (6)
ftp (5)
GIT (3)
GOD (2)
Grub (1)
Hacking (10)
Hadoop (6)
health (2)
horoscope (23)
Hyper-V (10)
IIS (15)
IPTABLES (15)
JAVA (7)
JBOSS (32)
jenkins (1)
Kubernetes (7)
Ldap (5)
Linux (188)
Linux Commands (166)
Load balancer (5)
mariadb (14)
Mongodb (4)
MQ Server (24)
MYSQL (84)
Nagios (5)
NaturalOil (13)
Nginx (35)
Ngix (1)
openldap (1)
Openstack (6)
Oracle (35)
Perl (3)
Postfix (19)
Postgresql (1)
PowerShell (2)
Python (3)
qmail (36)
Redis (12)
RHCE (28)
SCALEIO (1)
Security on Centos (29)
SFTP (1)
Shell (64)
Solaris (58)
Sql Server 2012 (4)
squid (3)
SSH (10)
SSL (14)
Storage (1)
swap (3)
TIPS on Linux (28)
tomcat (62)
Ubuntu (1)
Uncategorized (30)
Veritas (2)
vfabric (1)
VMware (28)
Weblogic (38)
Websphere (71)
Windows (19)
Windows Software (2)
wordpress (1)
ZIMBRA (17)

WP Cumulus Flash tag cloud by Roy Tanck requires Flash Player 9 or better.

Who's Online

0 visitors online now
0 guests, 0 bots, 0 members

Hit Counter provided by dental implants orange county

Moving the control, data and redo log file of an Oracle 11g R2 database to a new location

Moving the control, data and redo log file of an Oracle 11g R2 database to a new location

I’m looking into moving the location the control file of my Oracle database into a different location. The new location will be a file system that has been mounted on an IBM Storwize V7000 storage system.

Found some great information at http://psoug.org/reference/control_file.html

Here is what I did on my system.

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 10 12:01:47 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, 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      /home/test/test1/control01.ctl
                                                 , /home/test/test1/control02.c
                                                 tl
control_management_pack_access       string      DIAGNOSTIC+TUNING
SQL>
SQL> shutdown abort;
ORACLE instance shut down.
SQL> create pfile from spfile;

File created.

SQL>

On the host system, copy the files from their current location(/home/test/test1) to the new location(/oraarch/test1)


$ ls
control01.ctl  redo01.log     redo03.log     system01.dbf   undotbs01.dbf
control02.ctl  redo02.log     sysaux01.dbf   temp01.dbf     users01.dbf
$ pwd
/home/test/test1
$

Next, update the init<instance_name>.ora file under $ORACLE_HOME/dbs with the new location

$ cat inittest1.ora | grep control
*.control_files='/oraarch/test1/control01.ctl','/oraarch/test1/control02.ctl'

Now, create a new spfile with the updated information.

SQL> create spfile from pfile;

File created.

SQL> show parameter control;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /oraarch/test1/control01.ctl,
                                                 /oraarch/test1/control02.ctl
control_management_pack_access       string      DIAGNOSTIC+TUNING
SQL>

There we go, we have the location of the control files updated.

==================================================================

Next we will move the data files to the new location.

isvp17> su - oracle
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 10 12:59:00 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/home/test/test1/system01.dbf
/home/test/test1/sysaux01.dbf
/home/test/test1/undotbs01.dbf
/home/test/test1/users01.dbf

SQL> shutdown abort;
ORACLE instance shut down.
SQL>

Copy the data files to the new location.

$ cp /home/test/test1/system01.dbf /oradata/test1
$ cp /home/test/test1/sysaux01.dbf /oradata/test1
$ cp /home/test/test1/undotbs01.dbf /oradata/test1
$ cp /home/test/test1/users01.dbf /oradata/test1


Start the database in startup mount mode
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 10 13:04:41 2011

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1.0289E+10 bytes
Fixed Size                  2215712 bytes
Variable Size            5301600480 bytes
Database Buffers         4966055936 bytes
Redo Buffers               18743296 bytes
Database mounted.
SQL>
SQL> alter database rename file '/home/test/test1/system01.dbf' to '/oradata/test1/system01.dbf';

Database altered.

SQL> alter database rename file '/home/test/test1/sysaux01.dbf' to '/oradata/test1/sysaux01.dbf';

Database altered.
SQL> alter database rename file '/home/test/test1/undotbs01.dbf' to '/oradata/test1/undotbs01.dbf';

Database altered.

SQL> alter database rename file '/home/test/test1/users01.dbf' to '/oradata/test1/users01.dbf';

Database altered.

SQL> alter database open;

Database altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/oradata/test1/system01.dbf
/oradata/test1/sysaux01.dbf
/oradata/test1/undotbs01.dbf
/oradata/test1/users01.dbf

SQL>

==================================================================================

Lastly we will change the location of the redo logs of the database

Found lots of good information on it at http://www.ordba.net/Tutorials/Redolog.htm

Here is how I moved my redlo logs to the new location.

SQL> shutdown abort;
ORACLE instance shut down.
SQL>



$ cp redo0*.log /oralog/test1
$ ls /oralog/test1
redo01.log  redo02.log  redo03.log
$


SQL> startup mount;
ORACLE instance started.

Total System Global Area 1.0289E+10 bytes
Fixed Size                  2215712 bytes
Variable Size            5301600480 bytes
Database Buffers         4966055936 bytes
Redo Buffers               18743296 bytes
Database mounted.
SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/home/test/test1/redo03.log
/home/test/test1/redo02.log
/home/test/test1/redo01.log

SQL>
SQL> alter database rename file '/home/test/test1/redo01.log' to '/oralog/test1/redo01.log';

Database altered.

SQL> alter database rename file '/home/test/test1/redo02.log' to '/oralog/test1/redo02.log';

Database altered.

SQL> alter database rename file '/home/test/test1/redo03.log' to '/oralog/test1/redo03.log';

Database altered.

SQL>
SQL> alter database open;

Database altered.

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/oralog/test1/redo03.log
/oralog/test1/redo02.log
/oralog/test1/redo01.log

SQL>


Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

  

  

  

Blue Captcha Image
Refresh

*

Protected by WP Anti Spam