Oracle Backup& Recovery overview

Oracle backup & recovery overview
1. Query the V$ view that you use to find the names of all datafiles in the database.
SQL> SELECT name FROM v$datafile;
NAME
--------------------------------------------
/ora1/ora9i/oradata/system01.dbf
/ora1/ora9i/oradata/undotbs.dbf
/ora1/ora9i/oradata/users01.dbf
/ora1/ora9i/oradata/indx01.dbf
/ora1/ora9i/oradata/sample01.dbf
/ora1/ora9i/oradata/querydata01.dbf

2. Query the V$ views that you use to find the current online redo log group and names
of all redo log files in the database.
SQL> SELECT group#, status FROM v$log;
GROUP# STATUS
------ ----------------
1 CURRENT
2 INACTIVE
SQL> SELECT member FROM v$logfile;
MEMBER
------------------------------------
/ora1/ora9i/oradata/log01a.rdo
/ora1/ora9i/oradata/log02a.rdo

3. Query the V$ view that you use to find the names of all control files in the database.
SQL> SELECT name FROM v$controlfile;
NAME
--------------------------------------
/ora1/ora9i/oradata/ctrl01.ctl

4. Query the V$ view that you use to find the name of the database before dropping
tables or shutting down the database.
SQL> SELECT name FROM v$database;
NAME
---------
DB01

5. Query the V$ view that you use to locate processes still connected to the instance
before shutting down the database.
SQL> SELECT pid, username FROM v$process;
PID USERNAME
--- ---------------
1
2 db01
3 db01
4 db01
5 db01
6 db01
7 db01
8 db01
8 rows selected.
6. Which initialization parameter configures the memory area in the SGA that buffers
recovery information in memory before being written to disk?
LOG_BUFFER

7. What is the large pool, when is it used, and what initialization parameter configures it?
The large pool is an area of the SGA which can be used for buffering information in
memory for RecoveryManager when IO slaves are required. This increases the speed
and efficiency of backups and restores when using RMAN.
The LARGE_POOL_SIZE parameter specifies the number of bytes allocated from the
SGA.

8. Describe the significance of the FAST_START_MTTR_TARGET parameter during
instance recovery.
A target (bounded) time to complete the roll forward phase of recovery is specified by
means of the parameter FAST_START_MTTR_TARGET, and Oracle automatically
varies the checkpoint writes to meet that target.

9. Set up mirroring of control files so you have two control files. Place your second
control file in the /ora2/ora9i/oradata directory.
To add a new control file or change the number or location of the control file, follow
these steps:
a. Shut down the database:
SQL> SHUTDOWN IMMEDIATE;
b. Copy the existing control file to a different device using operating system
commands:
$cp –p /ora1/ora9i/oradata/ctrl01.ctl /ora2/ora9i/oradata/ctrl02.ctl
$chmod g+wx /ora2/ora9i/oradata/ctrl02.ctl
c. Edit or add the CONTROL_FILES parameter and specify names for all the
control files:
$vi $ORACLE_HOME /dbs/init.ora
control_files /ora1/ora9i/oradata /ctrl01.ctl, /ora2/ora9i/oradata/ctrl02.ctl
d. Start up the instance and open the database:
SQL> STARTUP PFILE$ORACLE_HOME/dbs/init.ora
SQL> show parameter control_files
NAME TYPE VALUE
------------------ ------ -------------------------------
control_files string /ora1/ora9i/oradata/ctrl01.ctl, /ora2/ora9i/oradata/ctrl02.ctl

10. Set up mirroring of online redo log files so you have two members per group. Place
the second member of each group in the /ora1/ora9i/oradata directory.
Solution Using SQL commands
SQL> alter database add logfile member
2 '/ora1/ora9i/oradata/log01b.rdo' to group 1,
3 '/ora2/ora9i/oradata/log02b.rdo' to group 2;
Database altered.
12. Query V$LOGFILE to confirm your additions.
SQL> select member from v$logfile;
MEMBER
--------------------------------------
/ora1/ora9i/oradata/log01a.rdo
/ora2/ora9i/oradata/log02a.rdo
/ora1/ora9i/oradata/log01b.rdo
/ora2/ora9i/oradata/log02b.rdo