Redo,Undo,Control file interview questions
Thanks Akhil Mohan.
Debasis Maity:
1.Why controlfile and redolog file multiplexed?
Control file :
A control file is a small binary file that records the physical structure of the database and includes:
The database name.
Names and locations of associated datafiles and online redo log files.
The timestamp of the database creation.
The current log sequence number.
Checkpoint information.
Oracle strongly recommends that your database has a minimum of two control files and that they are located on separate disks.
If a control file is damaged due to a disk failure, the associated instance must be shut down. Once the disk drive is repaired, the damaged control file can be restored using the intact copy of the control file from the other disk and the instance can be restarted. In this case, no media recovery is required.
The following describes the behavior of multiplexed control files:
Oracle writes to all filenames listed for the initialization parameter CONTROL_FILES in the database’s initialization parameter file.
The first file listed in the CONTROL_FILES parameter is the only file read by the Oracle database server during database operation.
If any of the control files become unavailable during database operation, the instance becomes inoperable and should be aborted.
Redolog :
The most crucial structure for recovery operations is the redo log, which consists of two or more preallocated files that store all changes made to the database as they occur. Every instance of an Oracle Database has an associated redo log to protect the database in case of an instance failure.
The database requires a minimum of two files to guarantee that one is always available for writing while the other is being archived (if the database is in ARCHIVELOG mode)
To protect against a failure involving the redo log itself, Oracle Database allows a multiplexed redo log, meaning that two or more identical copies of the redo log can be automatically maintained in separate locations. For the most benefit, these locations should be on separate disks. Even if all copies of the redo log are on the same disk, however, the redundancy can help protect against I/O errors, file corruption, and so on. When redo log files are multiplexed, LGWR concurrently writes the same redo log information to multiple identical redo log files, thereby eliminating a single point of redo log failure.
Multiplexing is implemented by creating groups of redo log files. A group consists of a redo log file and its multiplexed copies. Each identical copy is said to be a member of the group. Each redo log group is defined by a number, such as group 1, group 2, and so on.
2.If one of multiplexed controlfile deleted during database running,what happens to database?
Specific views and specific operation are not allowed. User transaction won’t impact. The database stays up.
Demo in 12c:
SQL> show parameter control
NAME
——————————
control_file_record_keep_time
control_files
control_management_pack_
SQL>
SQL>
SQL> !rm /u01/app/oracle/product/12.1.
SQL> show parameter control
NAME
——————————
control_file_record_keep_time
control_files
control_management_pack_
SQL>
SQL>
SQL> !ls -l /u01/app/oracle/product/12.1.
ls: cannot access /u01/app/oracle/product/12.1.
SQL>
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> shut immediate <<<<<<<<<<<<<<<<<<<<<<<<<<<<<
ORA-00210: cannot open the specified control file
ORA-00202: control file: ‘/u01/app/oracle/product/12.1.
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL>
SQL> /
INSTANCE_NUMBER INSTANCE_NAME HOST_NAME
————— —————- ——————————
DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO CON_ID INSTANCE_MO EDITION FAMILY
—————– —————— ——— — ———- ———– ——- ——————————
1 ************** ********************
ACTIVE PRIMARY_INSTANCE NORMAL NO 0 REGULAR EE
SQL> select name from v$datafile; <<<<<<<<<<<<<<<<Error out
select name from v$datafile
*
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: ‘/u01/app/oracle/product/12.1.
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> create table test as select * from all_objects where rownum<10; <<<<<<<<<<<<<<<<<Able to do transaction
Table created.
SQL> select count(*) from sys.test;
COUNT(*)
———-
9
SQL> drop table sys.test;
Table dropped.
SQL> alter system checkpoint; <<<<<<<<<<<<<<<<<<<<<<<<<<No impact here
System altered.
SQL> select * from v$controlfile;
STATUS
——-
NAME
——————————
IS_ BLOCK_SIZE FILE_SIZE_BLKS CON_ID
— ———- ————– ———-
+DATA/orcl/CONTROLFILE/
NO 16384 1156 0
/u01/app/oracle/product/12.1.
NO 16384 1156 0
SQL>
SQL> select * from dba_TablespacE_space; >>>>>>>>>>>>>>>>>>>>>error out
select * from dba_TablespacE_space
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: ‘/u01/app/oracle/product/12.1.
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> select * from v$database; >>>>>>>>>>>>>>>>error out
select * from v$database
*
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: ‘/u01/app/oracle/product/12.1.
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> select * from v$logfile;
select * from v$logfile
*
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: ‘/u01/app/oracle/product/12.1.
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> select * from v$log;
select * from v$log
*
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: ‘/u01/app/oracle/product/12.1.
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> show pdbs
ERROR:
ORA-00210: cannot open the specified control file
ORA-00202: control file: ‘/u01/app/oracle/product/12.1.
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SP2-1545: This feature requires Database availability.
SQL> alter session set container = pdb1;
Session altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00210: cannot open the specified control file
ORA-00202: control file: ‘/u01/app/oracle/product/12.1.
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
alert log :
Sun Feb 25 09:36:50 2018
Thread 1 advanced to log sequence 47 (LGWR switch)
Current log# 1 seq# 47 mem# 0: +DATA/orcl/ONLINELOG/group_1.
Sun Feb 25 09:42:59 2018
Thread 1 advanced to log sequence 48 (LGWR switch)
Current log# 2 seq# 48 mem# 0: +DATA/orcl/ONLINELOG/group_2.
Sun Feb 25 09:48:38 2018
Default pga_aggregate_limit value is too high for the
amount of physical memory in the system
pga_aggregate_limit is 2048 MB
limit based on physical memory and SGA usage is 1818 MB
Sun Feb 25 10:13:16 2018
alter database open
ORA-210 signalled during: alter database open…
SQL> shut abort
ORACLE instance shut down.
[oracle@rac1 ~]$ sqlplus
SQL*Plus: Release 12.1.0.2.0 Production on Sun Feb 25 10:25:36 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2932632 bytes
Variable Size 729809000 bytes
Database Buffers 335544320 bytes
Redo Buffers 5455872 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL> shut immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL>
ASMCMD> cp Current.285.890069161 /u01/app/oracle/product/12.1.
copying +DATA/CLAUDIA/CONTROLFILE/
ASMCMD> exit
[oracle@rac1 ~]$ sqlplus
SQL*Plus: Release 12.1.0.2.0 Production on Sun Feb 25 10:30:49 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Enter user-name: / as sysdba
strConnected to an idle instance.
SQ
SQL>
SQL>
SQL> startup
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2932632 bytes
Variable Size 729809000 bytes
Database Buffers 335544320 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
3.How you can size your redolog?
An excerpt from the Oracle Database Performance Tuning Guide provides the general guideline.
“It may not always be possible to provide a specific size recommendation for redo log files, but redo log files in the range of a hundred megabytes to a few gigabytes are considered reasonable. Size your online redo log files according to the amount of redo your system generates. A rough guide is to switch logs at most once every twenty minutes.”
So if your online redo logs (for example) switch once every 5 minutes during peak database activity, to achieve the 20 minute guideline, the logs would each need to be 4 times larger then their current size. (i.e. 20 / 5 = 4)
In addition to above, if using fast_start_mttr_target, this SQL will return recommendation. (10g and up)
select optimal_logfile_size from v$instance_recovery;
How to Estimate Size of Redo Logs (Doc ID 1038851.6)
4.What is advantage and disadvantage of having big redo log size?
– Will take longer to fill
– Fewer Checkpoints will occur
– Archiving will be done less often
– Instance recovery may take longer depending on the
Log_Checkpoint_Interval
5.why we encounter ORA-01555?
One of the famous and basic error in Oracle that every DBA should aware, “Snapshot too old”.
Affecting factor undo_retention and undo size.
Error: ORA 01555
Text “snapshot too old: rollback segment number %s with name \”%s\” too small”
——————————
Cause: rollback records needed by a reader for consistent read are overwritten by other writers
Action: If in Automatic Undo Management mode, increase undo_retention setting. Otherwise, use larger rollback segments
The ORA-01555 is caused by Oracle read consistency mechanism. If you have a long running SQL that starts at 10:30 AM, Oracle ensures that all rows are as they appeared at 10:30 AM, even if the query runs until noon!
Oracles does this by reading the “before image” of changed rows from the online undo segments. If you have lots of updates, long running SQL and too small UNDO, the ORA-01555 error will appear.
For details : ORA-01555: “Snapshot too old” – Overview (Doc ID 10630.1)
6.How you can tune undo retention and undo tablespace size.
If UNDO_MANAGEMENT=AUTO (no matter the setting for UNDO_RETENTION) automatic undo tuning will be used.
MMON will calculate the MAXQUERYLEN every 30 sec interval. Based upon the MAXQUERYLEN, MMON decides the
TUNED_UNDORETENTION. That means the new UNDO RETENTION will be set to TUNED_UNDORETENTION.
Oracle Database automatically tunes the undo retention period based on how the undo tablespace is configured.
If the undo tablespace is fixed size, the database tunes the retention period for the best possible undo retention for that tablespace size and the current system load. This tuned retention period can be significantly greater than the specified minimum retention period.
If the undo tablespace is configured with the AUTOEXTEND option, the database tunes the undo retention period to be somewhat longer than the longest-running query on the system at that time. Again, this tuned retention period can be greater than the specified minimum retention period.
Sizing an UNDO tablespace requires three pieces of data.
(UR) UNDO_RETENTION in seconds
(UPS) Number of undo data blocks generated per second
(DBS) Overhead varies based on extent and file size (db_block_size)
The undo space needed is calculated as:
UndoSpace = UR * (UPS * DBS)
The maximum number of undo blocks generated per second can be acquired from V$UNDOSTAT.
The following formula calculates the peak undo blocks generated per second:
SQL> SELECT undoblks/((end_time-begin_
How To Size UNDO Tablespace For Automatic Undo Management (Doc ID 262066.1)
7.How you can re-create controlfile?
SQL> alter database backup controlfile to trace;
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug tracefile_name;
/opt/app/oracle/diag/rdbms/
Creating a new controlfile from a database that is not able to mount.
Under the rare occasion that you do not have a controlfile to either:
1. Restore control file from backup
2. or have a script from a previous generated “backup controlfile to trace script”, i.e., when your control file was good. In many cases you may not be able to generate the script now, if you get any error and does not have any backup of control file, then only option is to go for manual listing of files method mentioned below.
3. or you must create a script from the beginning, by manually listing of all the files, which is risky if you miss out any files, so carefully list all the files..
CREATE CONTROLFILE REUSE DATABASE “DBNAME” NORESETLOGS ARCHIVELOG
Follow the format listing:
– Location of redo logs.
– Location of datafiles
– Specifying the characterset.
Once you have listed all files correctly you are ready to recreate your controlfile
SQL> startup nomount;
SQL>@control.sql
Refer : How to Recreate a Controlfile (Doc ID 735106.1)
8.How you can drop and recreate your redolog files?
At times it may become necessary to resize or recreate your Redo logs, especially if you are switching logs to frequently. The following guide shows how to quickly and easily resize the REDO logs with the database up and running. For the purpose of this guide the following REDO logs will be assumed to exist in the following state. The guide will explain how to resize these redo logs to 1Gb.
G Bytes Data File Name
—- ———– ——————————
1 52428800 +REDO01/YXDOMS02/ONLINELOG/
1 52428800 +REDO02/YXDOMS02/ONLINELOG/
2 52428800 +REDO01/YXDOMS02/ONLINELOG/
2 52428800 +REDO02/YXDOMS02/ONLINELOG/
3 52428800 +REDO01/YXDOMS02/ONLINELOG/
3 52428800 +REDO02/YXDOMS02/ONLINELOG/
4 52428800 +REDO01/YXDOMS02/ONLINELOG/
4 52428800 +REDO02/YXDOMS02/ONLINELOG/
Step-By-Step Guide
1. Run the following code to check to see what log is currently in use.
set lines 180
COL group# FOR 999 HEA ‘ G ‘
COL bytes FOR 9999999999 HEA ‘ Bytes ‘
COL member FOR a60 HEA ‘ Data File Name ‘
COL status FOR a8 HEA ‘ Status ‘
COL status FOR a8 HEA ‘ Status ‘
select distinct
l.group#, l.bytes, ll.member, l.status, ll.status
from v$log l, v$logfile ll
where l.group# = ll.group#
order by l.group#
;
2. Make a note of the current log file group
3. Drop and recreate logfile group 1.
sqlplus / as sysdba
SQL> alter database drop logfile group 1;
SQL> alter database add logfile group 1 (‘+REDO01′,’+REDO02’) size 1G;
4. Repeat the process for logfile group 2 and 3.
5. Switch log file group to free up log file group 4.
sqlplus / as sysdba
SQL> alter system switch logfile;
SQL> alter system checkpoint global;
6. Drop and recreate logfile group 4
sqlplus / as sysdba
SQL> alter database drop logfile group 4;
SQL> alter database add logfile group 4 (‘+REDO01′,’+REDO02’) size 1G;
9.Does ASM instance has controlfile?if not how ASM can be mounted without controlfile?
[oracle@rac1 ~]$ sqlplus
SQL*Plus: Release 12.1.0.2.0 Production on Tue Feb 27 01:50:24 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Enter user-name: / as sysasm
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Real Application Clusters and Automatic Storage Management options
SQL> show parameter control >>>>> No control file
SQL>
The ASM instance, which is generally named +ASM, is started with the INSTANCE_TYPE=ASM init.ora parameter. This parameter, when set, signals the Oracle initialization routine to start an ASM instance and not a standard database instance. Unlike the standard database instance, the ASM instance contains no physical files; such as logfiles, controlfiles or datafiles, and only requires a few init.ora parameters for startup.
Upon startup, an ASM instance will spawn all the basic background processes, plus some new ones that are specific to the operation of ASM. The STARTUP clauses for ASM instances are similar to those for database instances. For example, RESTRICT prevents database instances from connecting to this ASM instance. NOMOUNT starts up an ASM instance without mounting any disk group. MOUNT option simply mounts all defined diskgroups
Hemant Shahi:
10.Can we make any datafile in undo tablespace offline ?
11.what happens inside UNDO segments when an active transaction is committed ?
12.What is importance of control_file_keep_record_keep_time ?
13.Significance of ‘MAXLOGFILES’ and ‘MAXLOGMEMBERS’ in controlfile and in what situations w.r.t these, we have to re-create the controlfile. ?
14.What happens during a log switch in archivelog mode and noarchivelog mode ?
15.Difference between ‘ alter system switch logfile’ and ‘ alter system archive log current’ ?
16.Why redo log files should not be kept on SSD ?
17.what are the advantages of having local undo in each PDB over sharing a single CDB wide undo tablespace ?
18.How to set a quota on undo for specific users so that they don’t use excessive amount of space in undo ?
Selva:
19.How to fetch information for redologfile?
20.How to check valid group status an member status?
21.What are the prerequisite for add or delete for redolog group member?
22.How to check current redolog file size?
23.What is the maximum number of redolog group an member for redolog file?
Vandana Wipro:
24.Default value or undo_retention.
25.What are the all state of undo block.
26.What action will be taken if undo tbs is 100% full.
27.Can we have more than undo tablespace.
28.How do you change the value of undo_tablespace parameter.
29.How does read consistency gets managed.
30.How can size of redo log decrease the performance of database.
31.What is OLR and SRL.
32.Why it is recommended to have n+1 SRL on Physical Standby.
33.Does RMAN backup take backup of redo log
34.What are the all state of Redo and how they defer from each other.
35.Can we rename/resize the redo, If yes Kindly elaborate.
36.How does the redo log helps incase of instance crash recovery .
37.Why LGWR write redo in synchronisation unlike DBWR.
Skant Gupta
38.What is the default location of archives in 12c?
39.What is the use of FAST_START_MTTR_TARGET parameter?
40.If we have multiple members in a single group, how LGWR will write into them?
About the Author
debasis maity
12+ years of rich experience on Database Administrations and on Infrastructure Solution Architect. AWS Certified Solution Architect and Senior Oracle DBA
how to get symmetrel without a prescription symmetrel 100 mg cost avlosulfon 100mg generic
Your comment is awaiting moderation.