DBA Hub

📋Steps in this guide1/1

ORACLE DATABASE 19C – HOW TO ADD / MULTIPLEX CONTROL FILES

– protect your Database in case of a disk failure– reduce the risk of control file loss due to corruption or accidental removal– as a DBA, it’s your

oracle configurationintermediate
by OracleDba
14 views
1

Overview

ORACLE DATABASE 19C – HOW TO ADD / MULTIPLEX CONTROL FILES – protect your Database in case of a disk failure – reduce the risk of control file loss due to corruption or accidental removal – as a DBA, it’s your responsibility to multiplex and backup control files to protect your organization in case of possible data loss due to media failure or control file corruption – Single instance database (we will cover ASM and RAC environment on future post) – Database Name: testdb01 – Operating System: Oracle Linux 7.2 – add the new location and control file name using ALTER SYSTEM SET CONTROL_FILES command – set the SCOPE to SPFILE and not BOTH or MEMORY to prevent the database from attempting to look for the new file at this time – you may want to create a pfile from spfile before doing this step so you have a backup of your initialization parameters – Make sure that the new control file resides on a separate physical disk. The purpose of multiple control files is to protect the database in case of a disk failure. – Make sure that the new disk / location for your new control file exists. – Make sure that Oracle user has permission to create the control file on the new location

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
SQL> SELECT name FROM v$controlfile;
NAME
———————————————————
/u01/app/oracle/oradata/testdb01/control01.ctl
/u01/app/oracle/fast_recovery_area/testdb01/control02.ctl
/u02/oradata/testdb01/control03.ctl

SQL> show parameter control_files
NAME TYPE VALUE
————– ——- ———————————————–
control_files string /u01/app/oracle/oradata/testdb01/control01.ctl,
/u01/app/oracle/fast_recovery_area/testdb01/control02.ctl,
/u02/oradata/testdb01/control03.ctl

SQL> ALTER SYSTEM SET CONTROL_FILES='/data/app/oracle/oradata/PRIM/control01.ctl','/data/app/oracle/oradata/PRIM/control02.ctl','/data/app/oracle/oradata/PRIM/control03.ctl' SCOPE=spfile;
System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

[oracle@ol7]$ cp /u02/oradata/testdb01/control03.ctl /u03/oradata/testdb01/control04.ctl

SQL> startup
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 2924928 bytes
Variable Size 520097408 bytes
Database Buffers 1073741824 bytes
Redo Buffers 13848576 bytes
Database mounted.
Database opened.

SQL> SELECT name FROM v$controlfile;
NAME
———————————————————
/u01/app/oracle/oradata/testdb01/control01.ctl
/u01/app/oracle/fast_recovery_area/testdb01/control02.ctl
/u02/oradata/testdb01/control03.ctl
/u03/oradata/testdb01/control04.ctl

SQL> show parameter control_files
NAME TYPE VALUE
————– ——- ———————————————–
control_files string /u01/app/oracle/oradata/testdb01/control01.ctl,
/u01/app/oracle/fast_recovery_area/testdb01/control02.ctl,
/u02/oradata/testdb01/control03.ctl,
/u03/oradata/testdb01/control04.ctl

Comments (0)

Please to add comments

No comments yet. Be the first to comment!