A Comprehensive Website for Oracle Learning

Q: What are the Oracle Background Processes?
A: The Oracle Background Processes are programs or tasks that run on the Oracle background such as log writers, db writers, archives, checkpoint, etc.

Q: Describe the V$BGPROCESS view.
A: The V$BGPROCESS view contains information about active and inactive background processes.

Q: Describe the following background processes:
PMON
DBWn
ARC0
CKPT
LGWR
SMON
RECO
A: PMON - The Process Monitor (PMON) is responsible for performing recovery if a user process fails and rolls back the uncommitted transactions. DBWn - The Database Writer (DBWn) is responsible for writing the changed blocks or dirty blocks in the database. ARC0 - The Archiver (ARC0) is responsible for writing the Online redo log files into the archive log destination. CKPT - The checkpoint process (CKPT) is responsible for synchronizing the buffer cache with the data file. It updates all datafile headers and the control files. LGWR - The Log Writer (LGWR) is responsible for writing data from redo log buffers to the online redo log files. SMON - The System Monitor process (SMON) is responsible for instance recovery. RECO - The Re-coverer Process (RECO) is responsible for performing recovery of in-doubt transactions that often occur in distributed transactions.

Q: Describe an archive log configuration.
A: In an archive log configuration, Oracle grantees to recover to the point of failure.

Q: What does the ARCHIVE LOG LIST command?
A: The ARCHIVE LOG LIST command shows information about the database archive log mode status.

Q: What are disadvantage and advantage of a database in the NOARCHIVELOG mode?
A: Disadvantage: You can�t recover to the database point of failure. Advantage: It will use fewer resources.

Q: What are disadvantage and advantage of a database in the ARCHIVELOG mode?
A: Advantage: You can recover to the database point of failure. Disdvantage: It will use more resources.

Q: What does the following SQL statememt?
SQL> SELECT * FROM v$bgprocess
WHERE PADDR <> '00'
/
A: It shows all the active Oracle background processes.

Q: How do you test that a database is in archivelog or not?
A: SQL> ARCHIVE LOG LIST

Q: What is the controlfile in the Oracle database?
A: The controlfile in the Oracle database is a binary file that contains the database structure, backup information, datafiles synchronization, and more.

Q: How do you get a list of all your controlfiles� location?
A: SQL> SELECT name
FROM v$controlfile

Q: Describe the following views:
V$CONTROLFILE view
V$CONTROLFILE_RECORD_SECTION view
A: The V$CONTROLFILE view contains the location of your controlfiles and their status. The V$CONTROLFILE_RECORD_SECTION view shows different section information such as record_size, records_total, records_used, etc.

Q: What do the following SQL statements?
SQL> ALTER DATABASE BACKUP CONTROLFILE
TO 'c:\backupcontrolfile\control_ddmmyyyy.ctl'
A: Copies controlfile exactly as it was on the time of its backup.
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE
A: Gets a SQL statement. We can use it to re-create the controlfile if we needed.
SQL> SELECT * FROM v$controlfile_record_section
A: Queries information about different section in the controlfile.

Q: You, as a DBA, are responsible to multiplex controlfiles to protect your organization from a possible and unexpected loss of controlfiles, due to media failure. You task is to add one more controlfile to you database. What are the steps?
A:
� Add one more controlfile entry into the parameter file.
� You shut down the database.
� Copy one of the controlfiles to with the new name that was added to the parameter file.
� Start your database.

Q: How do you configure your database to an archivelog mode?
A:
1- SQL> ALTER DATABASE CLOSE
2- SQL> ALTER DATABASE ARCHIVELOG
3- Add the following statement into your parameter file.
Log_archive_start=true
log_archive_dest=�c:\archivelogs�
log_archive_format=�arc%S.%T�
4- SQL> ALTER DATABASE OPEN

Q: How do you query your database�s archive log information?
A: SQL> ARCHIVE LOG LIST

Q: How do you set an archive log destination?
A: SQL> ALTER SYSTEM ARCHIVE LOG START TO 'c:\archivelogs';

Q: What is the Server Parameter file (SPFILE)?
A: The Server Parameter File (SPFILE) is the same as the database parameter file. The only difference is: it is in a binary format code and can not be read or edited. It is used to change the Oracle system parameters dynamically by using the ALTER SYSTEM SET command.

Q: What do the following statements do?
ALTER SYSTEM SET log_archive_start=true SCOPE=spfile
A: It changes the archive log automatic start.
ALTER SYSTEM SET log_archive_dest='c:\archivelogs' SCOPE=spfile
A: It changes the archive log destination dynamically.
ALTER SYSTEM SET log_archive_format='arc%S.%T' SCOPE=spfile
A: It changes the archive log format dynamically.

Q: You, as a DBA, are responsible to recover any failures to a point of failure and also to perform your backup while in online status. Your shop is 24x7 and you are not able to shutdown the database. Therefore, the database has to be in an archive mode. You should change you database mode from noarchivelog to archivelog mode. What are the steps that you should perform to change your database mode?
A:
1- SQL> ALTER DATABASE CLOSE
2- SQL> ALTER DATABASE ARCHIVELOG
3- Add the following statement into your parameter file.
Log_archive_start=true
log_archive_dest=�c:\archivelogs�
log_archive_format=�arc%S.%T�
4- SQL> ALTER DATABASE OPEN

Q: Describe an online redo log file in a database?
A: The online redo log files are used to store Oracle user�s entries; and once it is full, the file will be archived to an assigned destination in the Oracle database. The log writer process writes those users entries from the redo log buffer.

Q: How do you add a redo log file group 3 to a database structure?
A: SQL> ALTER DATABASE ADD LOGFILE
GROUP 3 SIZE 2M;

Q: How do you resize a redo log file?
A: You should remove the redo log file and then re-create it with a new size.

Q: How do you drop a redo log file 3?
A: You can drop the online redo log groups that bear the status of INACTIVE.
SQL> ALTER DATABASE DROP LOGFILE
GROUP 3;

Q: Describe the V$LOG and V$LOGFILE views.
A: Note that the V$LOG dictionary view contains information such as its group number, size, member, status, archived, and the first change number in the log. The V$LOGFILE dictionary view contains the location of logs and their status.

Q: What does the following SQL statement?
SQL> SELECT * FROM v$archived_log
WHERE recid >
(SELECT MAX(recid) - 10 FROM v$archived_log)
/
A: Queries the last 10 archived logs.

Q: You, as a DBA, are responsible to maintain and relocate the Redo Log files in order to distribute data among multiple hard disks to increase I/O performance. Your task is to relocate only of the redo log file from it original location c:\orignial_location to c:\newlocation sub-directory. What are the steps?
A:
� Check the status of its group, if it is inactive then drop it.
� Create an online redo log group with the same group number.

Q: How do you set an Oracle-Managed archive log file destination?
A: SQL> ALTER SYSTEM SET log_archive_duplex_dest='c:\my2ndArclogs';

Q: Describe an Oracle-Managed File (OMF).
A: An Oracle-managed file is a file that Oracle takes control to manage it.

Q: What are the following views?
V$ARCHIVE_DEST view
V$ARCHIVED_LOG view
V$LOG_HISTORY view
A: The V$ARCHIVE_DEST view shows information about archive destinations. The V$ARCHIVED_LOG view shows all created archived log information. The V$LOG_HISTORY view shows that what was the first change number on that log.

Q: What is the Sequence Archive log number?
A: It is a number that will be assigned for each archived log file.

Q: You, as a DBA, are responsible to duplex archived Online Redo log files in order to protect the organization from a loss of or a case of corrupted archived files. Take one of the redo log file group and add a member to it in a different disk. What are the steps?
A:
1- SQL> SHOW PARAMETER %archive%dest
2- SQL> HOST MKDIR c:\my2ndArclogs
3- SQL> ALTER SYSTEM SET log_archive_duplex_dest='c:\my2ndArclogs';

Q: How many backup do we have?
A: Two! They are the Physical and Logical backups. The physical backup can be performed as a COLD backup or HOT backup. The logical backup can be performed while the Oracle database is running using the EXP command.

Q: What is a cold or offline database backup?
A: It is a part of the recovery process and will be performed when the Oracle database is shutdown with immediate, transactional or normal options.

Q: Describe a usage of the following views:
V$DATABASE view
V$LOGFILE view
V$DATAFILE view
V$CONTROLFILE view
A: The V$DATABASE view contains information about the Oracle database such as the database id, name, created date, database mode, log mode, etc. The V$LOGFILE view contains information about the location of logs, their status, etc. The V$DATAFILE view contains information about datafiles� location, their status, etc. The V$CONTROLFILE view contains information about controlfiles� location, their status, etc.

Q: To perform a COLD backup, does the database need to be in an archivelog mode?
A: No.

Q: You, as a DBA, are responsible to backup the database and restore the data in case of a loss of data due to media failure. Based on your organization�s business rules, the database can be shutdown every day for 5 hours. You also know the backup won�t take more than an hour. You want to use the COLD backup process once a day. Write a script to perform a complete cold backup.
A: SQL> SET ECHO OFF
SQL> SET HEADING OFF
SQL> SET FEEDBACK OFF
SQL> SET PAGESIZE 1000
SQL> SPOOL c:\userbkup\my_COLD_bkup.sql
SQL> SELECT 'HOST COPY ' || name || ' c:\userbkup\*;'
FROM v$controlfile;
SQL> SELECT 'HOST COPY ' || name || ' c:\userbkup\*;'
FROM v$datafile;
SQL> SELECT 'HOST COPY ' || member || ' c:\userbkup\*;'
FROM v$logfile;
SQL> SELECT �HOST COPY \
�%ORACLE_BASE\admin\school\pfile\init.ora \
c:\userbkup\*;� FROM dual;
SQL>
SQL> SPOOL OFF
SQL> SET HEADING ON
SQL> SET FEEDBACK ON
SQL> SET PAGESIZE 55
SQL> SET ECHO ON

Q: What is a HOT or OFFLINE backup?
A: It is a part of the recovery process and will be performed when the Oracle database is running.

Q: Describe the DBA_DATA_FILES dictionary view.
A: This dictionary view provides datafiles� statistics information.

Q: How do you perform a hot backup on a tablespace?
A: We backup one tablespace at a time:
1- ALTER TABLESPACE mytablespace BEGIN BACKUP;
2- Copy all its datafiles� locations.
3- ALTER TABLESPACE mytablespace END BACKUP;

Q: What are the differences between a hot and cold backup?
A: The Oracle database must be closed before performing a COLD backup but while you are performing a HOT backup; your database doesn�t need to be closed.

Q: What do the following SQL statements?
SQL> ALTER TABLESPACE tools BEGIN BACKUP;
A: Put all the tablespace�s datafiles in the backup mode and don�t apply any changes to the datafiles unless I end the backup mode.
SQL> HOST COPY \
C:\ORA9I\ORADATA\SCHOOL\TOOLS01.DBF \
C:\userhotbkup\*
A: Start copying or backing up all the tablespace�s datafiles.
SQL> ALTER TABLESPACE tools END BACKUP;
A: End the tablespace backup mode.

Q: Describe the V$DATAFILE and DBA_DATA_FILES dictionary views?
A: See the above descriptions.

Q: Describe the TOTAL PAGES FAILING output from dbv utility.
A: If the TOTAL PAGES FAILING values are greater zero when you perform the DBV command, it means there are problems in the datafile. You should only perform the ONLINE tablespace backup when the tablespace�s datafile values are zero.

Q: How do you OFFLINE a tablespace?
A: SQL> ALTER TABLESPACE users OFFLINE;

Q: How many different OFFLINE option do you have for a tablespace?
A: The NORMAL (default option), TEMPORARY or IMMEDIATE options. Note that if you use the TEMPORARY or IMMEDIATE options for OFFLINE, you will not be able to get the tablespace ONLINE unless you perform a media recovery.

Q: How do you perform an integrity check on a datafile?
A: MS-DOS> dbv file=C:\ORACLE\ORA90\SCHOOL\USERS01.DBF -
BLOCKSIZE=4096

Q: What does the dbv utility?
A: It performs an integrity check on a datafile.

Q Can you ONLINE a tablespace that was OFFLINE with the TEMPORARY or IMMEDIATE options?
A: No, unless you perform a media recovery.

Q: You, as a DBA, are responsible to backup the tablespace or datafile and restore the data to the point of failure in case of a loss of data due to a media hard disk crash. Your organization is a 24x7 day shop and you are not able to shutdown the database. You have to use HOT or ONLINE backup. How do you perform a tablespace backup?
A:
4- ALTER TABLESPACE mytablespace BEGIN BACKUP;
5- Copy all its datafiles� locations.
6- ALTER TABLESPACE mytablespace END BACKUP;
-- OR --
7- ALTER TABLESPACE mytablespace OFFLINE;
8- Copy all its datafiles� locations.
9- ALTER TABLESPACE mytablespace ONLINE;

Q: How do you store a destroyed datafile when Oracle is online and running?
A:
1- Offline the tablespace.
2- Restore the tablespace�s datafile or datafiles.
3- Recover the tablespace.
4- Online the tablespace.

Q: How do you recover a tablespace?
A: SQL> RECOVER TABLESPACE mytablespqace;

Q: What does the following SQL statement?
SQL> RECOVER TABLESPACE users;
A: It recovers the USERS tablespace.

Q: You, as a DBA, are responsible to recover the database to the point of failure due to a loss of data and a media failure. Assuming that you lost your TOOLS�s datafiles, what are the steps to recover the datafiles to the point of failure?
A:
1- ATLER TABLESPACE tools OFFLINE;
2- Restore the tablespace datafile or datafiles.
3- RECOVER TABLESPACE tools;
4- ALTER TABLESPACE tools ONLINE;

Q: What is a physical backup?
A: A physical backup is one of a database recovery steps that is performed based on a database physical layout.

Q: What is a logical backup?
A: A logical backup is one of a database recovery steps that is performed based on a database logical layout.

Q: How do you perform a logical backup?
A: MS-DOS> exp

Q: How do you perform a logical restore?
A: MS-DOS> imp

Q: You, as a DBA, are responsible to perform a logical backup using the EXP tool. Notice that if the loss of data since the last time of backup is not significant then a logical backup is a good option to use. Scott lost its EMP table and you have been tasked to restore it using the IMP utility.
A: MS-DOS> IMP userid=� file=myexport.dmp tables=EMP

Q: What are the steps to perform an automated Oracle COLD backup?
A:
1- Shutdown the database,
2- Backup the database files,
3- Startup the database.

Q: What does the UTL_FILE_DIR parameter?
A: This parameter gives permission to Oracle to write or read from defined directory.

Q: What does the following SQL statements do?
GRANT SELECT ON v_$datafile TO xxx;
GRANT SELECT ON v_$logfile TO xxx;
GRANT SELECT ON v_$controlfile TO xxx;
A: They grant select privileges to the user xxx who wants to access to those views.

Q: Write a stored procedure that will create a UNIX script, and then performs your COLD backup.
A:
CREATE OR REPLACE PROCEDURE script4coldbackup
(p_filepath IN VARCHAR2,
p_orahome IN VARCHAR2,
p_sid IN VARCHAR2)
IS
-- Get the datafiles
cursor c_filename is
select name from v$datafile;
-- Get the controlfiles
cursor c_controlfile is
select name from v$controlfile;
-- Get the logfiles
cursor c_logfile is
select member from v$logfile;
output_file UTL_FILE.FILE_TYPE;
v_msg VARCHAR2(100);
v_path VARCHAR2(100);
BEGIN
v_path := p_filepath;
output_file := UTL_FILE.FOPEN(v_path,
'script4coldbackup.sh','w');
-- preparation for shutdown database...
UTL_FILE.PUTF(output_file,'%s \n',
'#!/bin/sh');
UTL_FILE.PUTF(output_file,
'ORACLE_SID=%s \n',p_sid);
UTL_FILE.PUTF(output_file,'%s \n',
'export ORACLE_SID');
UTL_FILE.PUTF(output_file,
'ORACLE_HOME=%s \n',p_orahome);
UTL_FILE.PUTF(output_file,'%s \n',
'export ORACLE_SID');

UTL_FILE.PUTF(output_file,'%s \n',
'$ORACLE_HOME/bin/sqlplus /nolog << EOF');
UTL_FILE.PUTF(output_file,'%s \n',
'CONNECT internal/');
UTL_FILE.PUTF(output_file,'%s %s/coldbackup.log \n',
'spool',v_path);

UTL_FILE.PUTF(output_file,'%s \n',
'ALTER DATABASE BACKUP CONTROLFILE TO TRACE;');
UTL_FILE.PUTF(output_file,'%s \n',
'SHUTDOWN IMMEDIATE;');
-- process the datafiles
FOR this IN c_filename LOOP
UTL_FILE.PUTF(output_file,'host cp %s %s\n',
this.name,
v_path);
END LOOP;
-- process the controlfiles
FOR this IN c_controlfile LOOP
UTL_FILE.PUTF(output_file,'host cp %s %s\n',
this.name,
v_path);
END LOOP;
-- process the logfiles
FOR this IN c_logfile LOOP
UTL_FILE.PUTF(output_file,'host cp %s %s\n',
this.member,
v_path);
END LOOP;
-- preparation for startup the database...
UTL_FILE.PUTF(output_file,'%s \n',
'CONNECT internal/');
UTL_FILE.PUTF(output_file,'%s \n',
'STARTUP;');
UTL_FILE.PUTF(output_file,'%s \n',
'exit');
UTL_FILE.PUTF(output_file,'%s \n',
'EOF');
UTL_FILE.FCLOSE_ALL;
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
UTL_FILE.PUTF(output_file,'Invalid Path');
UTL_FILE.FCLOSE(output_file);
WHEN UTL_FILE.INVALID_MODE THEN
UTL_FILE.PUTF(output_file,'Invalid Mode');
UTL_FILE.FCLOSE(output_file);
WHEN UTL_FILE.INVALID_OPERATION then
UTL_FILE.PUTF(output_file,'Invalid Operation');
UTL_FILE.FCLOSE(output_file);
WHEN UTL_FILE.INVALID_FILEHANDLE then
UTL_FILE.PUTF(output_file,'Invalid Filehandle');
UTL_FILE.FCLOSE(output_file);
WHEN UTL_FILE.WRITE_ERROR then
UTL_FILE.PUTF(output_file,'Write Error');
UTL_FILE.FCLOSE(output_file);
WHEN UTL_FILE.READ_ERROR then
UTL_FILE.PUTF(output_file,'Read Error');
UTL_FILE.FCLOSE(output_file);
WHEN UTL_FILE.INTERNAL_ERROR then
UTL_FILE.PUTF(output_file,'Internal Error');
UTL_FILE.FCLOSE(output_file);
WHEN OTHERS THEN
UTL_FILE.PUTF(output_file,'others');
UTL_FILE.FCLOSE(output_file);
END script4coldbackup;

Q: Write a UNIX or DOS script to create the "script4coldbackup.sh" script and then run it in the coldbackup directory. Call it RunColdBackup.sh.
A:
#!/bin/sh
ORACLE_SID=trav
export ORACLE_SID
BACKUP_HOME=/u07/coldbackup
export BACKUP_HOME
ORACLE_HOME=/u01/app/oracle/product/8.1.7
export ORACLE_HOME
ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE
$ORACLE_HOME/bin/sqlplus /nolog << EOF
connect INTERNAL/
DECLARE
v_bckhome VARCHAR2(100) := '/u07/coldbackup';
v_orahome VARCHAR2(100) := '/u01/app/oracle/product/8.1.7';
v_sid VARCHAR2(100) := 'trav';
BEGIN
script4coldbackup(v_bckhome,v_orahome,v_sid);
END;
/
exit
EOF
chmod 770 $BACKUP_HOME/script4coldbackup.sh
cp $ORACLE_HOME/network/admin/tnsnames.ora $BACKUP_HOME/.
cp $ORACLE_HOME/network/admin/listener.ora $BACKUP_HOME/.
cp $ORACLE_HOME/network/admin/sqlnet.ora $BACKUP_HOME/.
cp $ORACLE_BASE/admin/trav/pfile/inittrav.ora $BACKUP_HOME/.
$BACKUP_HOME/script4coldbackup.sh

Q: What are the steps to create an automated Oracle HOT backup?
A:
1- Put each tablespace into the BACKUP mode,
2- Backup its datafile or datafiles,
3- Put them off the BACKUP mode.
4- Repeat this for all the database tablespaces.

Q: Write a procedure to create a script to perform the actual HOT or ONLINE backup.
A:
CREATE OR REPLACE PROCEDURE script4hotbackup
(p_filepath IN VARCHAR2,
p_orahome IN VARCHAR2,
p_sid IN VARCHAR2)
IS
-- Get the tablespace name
cursor c_tablespace is
select distinct tablespace_name from dba_data_files;
-- Get the datafiles
cursor c_filename (p_tablespace VARCHAR2) is
select file_name from dba_data_files
where tablespace_name = p_tablespace;
output_file UTL_FILE.FILE_TYPE;
v_msg VARCHAR2(100);
v_path VARCHAR2(100);
v_arch VARCHAR2(100) := '/u01/app/oracle/admin/<sid>/arch';

BEGIN
v_path := p_filepath;
output_file := UTL_FILE.FOPEN(v_path,
'script4coldbackup.sh','w');
-- preparation for shutdown database...
UTL_FILE.PUTF(output_file,'%s \n',
'#!/bin/sh');
UTL_FILE.PUTF(output_file,
'ORACLE_SID=%s \n',p_sid);
UTL_FILE.PUTF(output_file,'%s \n',
'export ORACLE_SID');
UTL_FILE.PUTF(output_file,
'ORACLE_HOME=%s \n',p_orahome);
UTL_FILE.PUTF(output_file,'%s \n',
'export ORACLE_SID');
UTL_FILE.PUTF(output_file,'%s \n',
'$ORACLE_HOME/bin/sqlplus /nolog << EOF');
UTL_FILE.PUTF(output_file,'%s \n',
'CONNECT internal/');
UTL_FILE.PUTF(output_file,'%s %s/coldbackup.log \n',
'spool',v_path);
UTL_FILE.PUTF(output_file,'%s \n',
'ALTER DATABASE BACKUP CONTROLFILE TO TRACE;');
-- Process Online backup for each tablespace.
FOR this IN c_tablespace LOOP
UTL_FILE.PUTF(output_file,'ALTER TABLESPACE %s BEGIN BACKUP;\n',
this.tablespace_name);

-- process the datafiles in each tablespace
FOR v_datafile IN c_filename (this.tablespace_name) LOOP
UTL_FILE.PUTF(output_file,'host cp %s %s;\n',
v_datafile.file_name,
v_path);
END LOOP;
UTL_FILE.PUTF(output_file,'ALTER TABLESPACE %s END BACKUP;\n',
this.tablespace_name);
END LOOP;
-- Process ONLINE backup for the controlfiles
UTL_FILE.PUTF(output_file,
'ALTER DATABASE BACKUP CONTROLFILE TO %s/controlbkup.ctl;\n',
v_path);
-- Swith the online redo log file.
UTL_FILE.PUTF(output_file,
'ALTER SYSTEM SWITCH LOGFILE;\n');
-- Copy all archive log files...
UTL_FILE.PUTF(output_file,'host cp %s/arc*.log %s\n',
v_arch,
v_path);

-- Remove all copied archive log files...
UTL_FILE.PUTF(output_file,'host rm %s/arc*.log\n',
v_arch);
UTL_FILE.PUTF(output_file,'%s \n',
'exit');
UTL_FILE.PUTF(output_file,'%s \n',
'EOF');

UTL_FILE.FCLOSE_ALL;
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
UTL_FILE.PUTF(output_file,'Invalid Path');
UTL_FILE.FCLOSE(output_file);

WHEN UTL_FILE.INVALID_MODE THEN
UTL_FILE.PUTF(output_file,'Invalid Mode');
UTL_FILE.FCLOSE(output_file);
WHEN UTL_FILE.INVALID_OPERATION then
UTL_FILE.PUTF(output_file,'Invalid Operation');
UTL_FILE.FCLOSE(output_file);
WHEN UTL_FILE.INVALID_FILEHANDLE then
UTL_FILE.PUTF(output_file,'Invalid Filehandle');
UTL_FILE.FCLOSE(output_file);
WHEN UTL_FILE.WRITE_ERROR then
UTL_FILE.PUTF(output_file,'Write Error');
UTL_FILE.FCLOSE(output_file);
WHEN UTL_FILE.READ_ERROR then
UTL_FILE.PUTF(output_file,'Read Error');
UTL_FILE.FCLOSE(output_file);
WHEN UTL_FILE.INTERNAL_ERROR then
UTL_FILE.PUTF(output_file,'Internal Error');
UTL_FILE.FCLOSE(output_file);
WHEN OTHERS THEN
UTL_FILE.PUTF(output_file,'others');
UTL_FILE.FCLOSE(output_file);
END script4hotbackup;

Q: Write a UNIX script to create the "script4hotbackup.sh" script and then run it in the hotbackup directory. Call it RunHotBackup.sh.
A:
#!/bin/sh
ORACLE_SID=
export ORACLE_SID
BACKUP_HOME=/u07/hotbackup
export BACKUP_HOME
ORACLE_HOME=/u01/app/oracle/product/8.1.7
export ORACLE_HOME
ORACLE_BASE=/u01/app/oracle
export ORACLE_BASE
$ORACLE_HOME/bin/sqlplus /nolog << EOF
connect INTERNAL/
DECLARE
v_bckhome VARCHAR2(100) := '/u07/hotbackup';
v_orahome VARCHAR2(100) := '/u01/app/oracle/product/8.1.7';
v_sid VARCHAR2(100) := 'your-sid';
BEGIN
script4hotbackup(v_bckhome,v_orahome,v_sid);
END;
/
exit
EOF
chmod 770 $BACKUP_HOME/script4hotbackup.sh
cp $ORACLE_HOME/network/admin/tnsnames.ora $BACKUP_HOME/.
cp $ORACLE_HOME/network/admin/listener.ora $BACKUP_HOME/.
cp $ORACLE_HOME/network/admin/sqlnet.ora $BACKUP_HOME/.
cp $ORACLE_BASE/admin/trav/pfile/inittrav.ora $BACKUP_HOME/.
$BACKUP_HOME/script4hotbackup.sh

Q: How do you create the RMAN repository?
A:
1- Create RMAN tablespace
2- Create RMAN user
3- Grant CONNECT, RECOVERY_CATALOG_OWNER, SYSDBA Roles

Q: How do you create the RMAN user?
A: SQL> CREATE USER rman IDENTIFIED BY password
DEFAULT TABLESPACE rman_tablespace
QUOTA UNLIMITED ON rman_tablespace;

Q: How do you create the RMAN objects?
A: rman> CREATE CATALOG;

Q: How do you create the RMAN tablespace?
A: SQL> CREATE TABLESPACE rman_tablespace
DATAFILE 'c:/newfolder/rman_tablespace_01.dbf' SIZE 50M
AUTOEXTEND ON;

Q: What does the RMAN RCVCAT command?
A: Sign in to the rman catalog database.

Q: What does the DBMS_RCVCAT package?
A: The DBMS_RCVCAT package is responsible for maintaining information in the recovery catalog.

Q: What does the DBMS_RCVMAN package?
A: The DBMS_RCVMAN package is used for querying the recovery catalog or the control file.

Q: What do the following SQL and RMAN commands do?
SQL> CREATE TABLESPACE rman_tablespace
DATAFILE 'c:/newfolder/rman_tablespace_01.dbf' SIZE 50M
AUTOEXTEND ON;
A: It creates the rman tablespace.
SQL> CREATE USER rman IDENTIFIED BY password
DEFAULT TABLESPACE rman_tablespace
QUOTA UNLIMITED ON rman_tablespace;
A: It creates the rman user.
SQL> GRANT CONNECT, RECOVERY_CATALOG_OWNER, SYSDBA
TO rman;
A: It grants necessary roles privileges to the rman user.
rman> CREATE CATALOG;
A: It creates the rman catalog.
SQL> SELECT object_name FROM dba_objects
WHERE owner = 'RMAN'
and object_type = 'PACKAGE';
A: It queries the rman packages.

Q: How do you register a database to a RMAN utility?
A: rman> REGISTER DATABASE;

Q: How do you synchronize a catalog?
A: rman> RESYNC CATALOG;

Q: How do you backup a datafile of a database using RMAN?
A: rman> BACKUP DATAFILE 7;

Q: How do you backup a controlfile using RMAN?
A: rman> BACKUP CURRENT CONTROLFILE;

Q: What do the following SQL and RMAN commands do?
SQL> SELECT file_id, tablespace_name, bytes
FROM dba_data_files
A: It queries one or more file_id associated to a tablespace.
DOS> rman CATALOG rman/password@dbs4rman <mailto:rman/password@dbs4rman>
TARGET system/manager@school
A: It logs in the rman with the dbs4rman database catalog and the school database target.
rman> REGISTER DATABASE;
A: It registers the target database.

rman> RESYNC CATALOG;
A: It resynchronizes the database catalog with the target database.

rman> CONFIGURE SNAPSHOT CONTROLFILE NAME TO -
'c:\newfolder\snape01.snp';
A: It backups a controlfile.
rman> BACKUP DATAFILE 7;
A: It backups a datafile.
rman> BACKUP CURRENT CONTROLFILE;
A: It backup the current controlfile.
rman> EXIT;
A: It will exit the rman utility.

Q: How do you configure a RMAN retention policy?
A: RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

Q: How do you exclude a tablespace from a daily backup?
A: RMAN> CONFIGURE EXCLUDE FOR TABLESPACE tools;

Q: How do you perform an image copy of a datafile?
A: RMAN> COPY DATAFILE 7
TO 'c:\RMANBKUP\dbf_yymmdd.dbf' NOCHECKSUM;

Q: Describe the NOCHECKSUM parameter.
A: It is a parameter to make sure that there is no datafile integrity problem.

Q: How do you perform an image copy of your current controlfile?
A: RMAN> COPY CURRENT CONTROLFILE
TO 'c:\RMANBKUP\ctlfl_yyyymmdd.ctl';

Q: How do you perform a backup in a specific location?
A: RMAN>BACKUPDATAFILE 7
FORMAT 'c:\RMANBKUP\file7_%T_%s_%p';

Q: What is the formatting the backup file�s name?
A: It is a naming formatted Oracle roles that you want to backup a datafile. For example you may want to use %T for the date, %s for the backup set number, and %p for the piece number in your naming format.

Q: How can you backup database objects using the incremental option?
A: RMAN>BACKUP INCREMENTAL LEVEL 0 TABLESPACE tools
FORMAT'c:\RMANBKUP\tools_L0';
RMAN>BACKUP INCREMENTAL LEVEL 1 TABLESPACE tools
FORMAT 'c:\RMANBKUP\tools_L1';

Q: What do the following formatting name syntaxes mean?
%T for the date
%S for the backup set number
%P for the piece number
A: We use %T for the system date to be part of a backup datafile name, %s for the backup set number, and %p for the piece number. Note that you can use a multiple datafiles to be backup in a single piece set.

Q: How do you perform a tablespace recovery using RMAN?
A:
RMAN> SQL 'ALTER DATABASE DATAFILE 7 OFFLINE';
RMAN> RESTORE DATAFILE 7;
RMAN> RECOVER DATAFILE 7;
RMAN> SQL 'ALTER DATABASE DATAFILE 7 ONLINE';

Q: How do you set a datafile status using the RMAN tool?
A: RMAN> SQL 'ALTER DATABASE DATAFILE 7 OFFLINE';

Q: How do you restore a corrupted datafile?
A: RMAN> RESTORE DATAFILE 7;

Q: How do you recover a corrupted datafile?
A: RMAN> RECOVER DATAFILE 7;

Q: How do you perform a complete database backup using the RMAN tool?
A: rman> RUN
{
ALLOCATE CHANNEL mybackup TYPE disk;
BACKUP DATABASE;
}
-- OR --
rman> BACKUP DATABASE;

Q: Describe the LIST BACKUP command.
A: It verifies the complete or full backup.

Q: Describe the SET TIME command in the SQLPLUS tool.
A: It makes it easy to see that time.

Q: What is an incomplete database recovery?
A: An incomplete database recovery is a recovery that it does not reach to the point of failure. The recovery is at a point of time.

Q: What are the differences between an incomplete database recovery and complete database recovery?
A: A complete recovery recovers to the point of failure.

Q: How do you perform an incomplete database recovery?
A:
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
RMAN> RUN
{
SQL "ALTER SESSION
SET NLS_DATE_FORMAT=''DD-MON-YYYY HH24:MI:SS''";
SET UNTIL TIME '04-AUG-2002 01:04:22';
RESTORE DATABASE;
RECOVER DATABASE;
}
RMAN> OPEN RESETLOGS DATABASE;

Q: How do you perform a restore?
A: RESTORE DATABASE;

Q: How do you recover a datafile until a specified time?
A: SQL "ALTER SESSION
SET NLS_DATE_FORMAT=''DD-MON-YYYY HH24:MI:SS''";
SET UNTIL TIME '04-AUG-2002 01:04:22';
RESTORE DATABASE;
RECOVER DATABASE;

Q: When do you use the RESETLOGS option?
A: When you perform an incomplete recovery.

Q: How do you perform a LIST and REPORT commands in the RMAN tool?
A: RMAN> LIST EXPIRED BACKUPSET;
RMAN> LIST BACKUP OF TABLESPACE tools;
RMAN> LIST INCARNATION OF DATABASE;
RMAN> REPORT NEED BACKUP;
RMAN> REPORT OBSOLETE;
RMAN> REPORT SCHEMA;

Q: How do you write a RMAN script?
A: RMAN> CREATE SCRIPT backup_tools
{
BACKUP TABLESPACE tools;
}

Q: How do you replace a RMAN script?
A: RMAN> REPLACE SCRIPT backup_tools
{
BACKUP TABLESPACE tools PLUS ARCHIVELOG;
}

Q: How do you delete a RMAN script?
A: RMAN> DELETE SCRIPT backup_tools;

Q: How do you run a RMAN script?
A: RMAN> RUN {EXECUTE SCRIPT backup_tools;}

Q: How do you get a list of the backup of the datafiles for a tablespace?
A: RMAN> LIST BACKUP;

Q: How do you get a list of objects that need backup?
A: RMAN> REPORT NEED BACKUP;

Q: How do you get a list of objects with their confirmed retention policy?
A: RMAN> REPORT NEED BACKUP;

Q: How do you get a list of a database incarnations?
A: RMAN> LIST INCARNATION OF DATABASE;

Q: How do you get a list of the backup sets that are obsolete?
A: RMAN> REPORT OBSOLETE;

Q: How do you get a list of the image copies that are obsolete?
A: RMAN> REPORT OBSOLETE;

Q: How do you get a list of the schemas that can be deleted?
A: RMAN> REPORT SCHEMA;

Q: What is Oracle Networking?
A: Oracle networking is a method for a DBA to manage connectivity between database application clients and the server.

Q: What tool does Oracle use to establish client/server connectivity?
A: Oracle uses the SQL*NET tool (Oracle Networking Utility) such as NET8 to perform connectivity, security, performance, configuration, and administration of its network.

Q: What tool does a DBA use to configure an Oracle Network?
A: The NET8 Assistant or the NET8 Easy Config tools.

Q: What is a dedicated server?
A: In the dedicated server, a server doesn�t share its work with any other clients.

Q: What is a shared server?
A: In the dedicated server, a server shares its work with other clients.

Q: What is multithreaded sever (MTS)?
A: The Multithreaded server (MTS) will be used when your clients (user processes) are using shared server. Connecting your client (user processes) to shared servers in the multithreaded server (MTS) configuration is designed to reduce network traffic. Notice that the MTS architecture consists of a SQL*NET listener, which hears user requests from across the network and passes all requests to the dispatcher.

Q: Describe a dispatcher jobs in the Oracle database?
A: A dispatcher can receive information from a client or the Connection Manager server. A communication between a dispatcher and a shared server will be performed through the Common Request Queue and the Response Queue in the System Global Area.

Q: Describe the Common Request Queue in the SGA?
A: The Common Request Queue and the Response Queue in the System Global Area performs a communication between a dispatcher and a shared server.

Q: What does the Oracle Connection Manager utility?
A: We use the Oracle Connection Manager utility to configure the network that can act as a firewall for checking authorization and as a multi-protocol interchange translates from one network protocol to another. You can use the Connection Pooling feature to reduce the burden on the Oracle Network. It allows the server to define a maximum number of connection sockets. If one connection socket is inactive, it will disable it temporarily in order to process the new one.

Q: What does the Oracle Internet Directory (OID) provide for Oracle users?
A: We use the Oracle Internet Directory (OID) to provide a single, centralized repository for all user data. It creates and manages user identities, roles, authorization and authentication credentials, and profiles within a single repository.

Q: What is the Oracle Names (ONAME) Utility?
A: We use the Oracle Names utility to centrally define the service addresses, inter-database links, net service names (aliases), and client configuration profiles by using Oracle Enterprise Manager utility (OEM).

Q: Describe the Single Sign-On feature in the Oracle environment?
A: It is a feature to let a user access multiple accounts and applications with a single password by using the Oracle Advanced Security.

Q: Describe the Oracle Wallet Manager feature in the Oracle environment?
A: It is a feature to authenticate a user to multiple services, such as database and application servers. The contents of the wallet are encrypted with a key based on a user-specified password. It is part of Oracle�s SSL implementation.

Q: What is a protocol?
A: It is a translator.

Q: How many different ways can a user connect to the Oracle database using Oracle NET?
A: Oracle NET helps you connect users to the Oracle database through TCP/IP, DECnet, IPX, and many other LAN or WAN products. It allows for Internet computing and Client/Server architecture. The Client/Server architecture requires you to maintain a local copy of database connectivity and naming information on the client side. This decentralization is only viable an organization has a few users. See the Connection for a client to a server Figure.

Q: Describe a connect string.
A: A �Connect String� tells that where your database is located and what type of protocol will be used to communicate with the database server. An example of connect string is: SQL> CONNECT system/manager@connect_string.

Q: What does a connect descriptor contain?
A: It contains network-specific, host name, Oracle SID, and specific port number.

Q: What is a listener in the Oracle environment?
A: The listener on the server always is listening or waiting for a call using the same port number (default is: 1521 or 1526). When the listener hears a call, it will either reject or accept the call. It will reject it only if the user process requests a connection to a SID that it doesn�t give service, has a wrong password, or the database is down.

Q: What is a System Identifier (SID) in an Oracle database?
A: The System Identifier (SID) is a unique name identifier that is assigned to an instance in a server.

Q: What are the relationships between a listener, user processes, dedicated servers, shared servers, and dispatchers?
A: If accepted, then the listener process assigns either a new dedicated server, prespawned dedicated server, or the least busy dispatcher to the user process depending on the listener configuration file (listener.ora). If a listener (listener.ora) was configured to use a dedicated server process, then it generates or spawns a new dedicated server for that user process. Once the user process and dedicated server shake hands, the listener will be isolated. Now, the user process requests the dedicated server to provide data on its behalf and when the user process ends, the dedicated server ends too. If a listener was configured to use a shared server, then it will provide the user process with the least busy dispatcher process. Now, the user process will establish a connection with the dispatcher directly. The dispatcher process will maintain a queue for the user process to place its data requests on. Note that the dispatcher has number of shared processes to work with. They will pull the user�s request off the queue in FIFO (First in First Out) order and process the request in behalf of the user process.

Q: How do you configure a LISTENER in your database server?
A: The listener�s configuration file is stored in listener.ora, which is located in the default directory $ORACLE_HOME/network/admin on UNIX and %ORACLE_HOME\network\admin on Windows. If you are going to relocate the configuration file make sure to set the TNS_ADMIN environment variable.
An example of listener.ora:
LISTENER4MYDBS=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp) (HOST=mycomupter) (PORT=1521))
(ADDRESS=(PROTOCOL=ipc) (KEY=extproc))
)
)
SID_LIST_LISTENER4MYDBS=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=mydbs.company)
(ORACLE_HOME=/u01/app/oracle/product/9.2.0)
(PROGRAM=extproc)
(SID_NAME=mydbs)
)
)

Q: Can you have multiple listeners running on a database server?
A: Yes.

Q: How do you configure a LISTENER with one or more protocol?
A: Just add more protocol to it.
(ADDRESS=(PROTOCOL=tcp) (HOST=mycomupter) (PORT=1521))

Q: Describe the PAMN background process jobs?
A: Its job is to make sure that if a user process got disconnected, clean the entire remaining task.

Q: How can you assign a password to you listener?
A: Add the following line to the listener.ora file.
PASSWARDS_listener4mydbs=(X6Z76H07Y34D)

Q: How do you turn on the trace utility on a listener on the user level?
A: Add the following line to the listener.ora file.
TRACE_LEVEL_listener4mydbs=(OFF|ADMIN|USER|SUPPORT)

Q: How do you start a listener?
A: $ lsnrctl start

Q: How do you stop a listener?
A: $ lsnrctl stop

Q: What does the reload command do in the listener?
A: It reloads the modified listener file.

Q: What are the various naming method supported by Oracle Net?
A: The following are the various naming methods supported by Oracle Net.

1- Host Naming Method
2- Local Naming Method
3- Directory Naming Method
4- Oracle Names Method

Q: Describe the Host Naming Method.
A: When you using the Host Naming method, a client must specify a username, password, and a connect string. The connect string either should be the hostname or its IP address. The database listener on the server must listen to the port number 1521. This will not work if you change the port number.

Q: Describe the Local Naming Method?
A: When you using the Local Naming method, the same as the Host Naming method, a client must specify a username, password, and a connect string. The connect string must be your Service Name. The Service Name is an entry that was defined in the TNSNAMES.ORA file. The TNS stand for Transparent Network Substrate. In contrast to the Host Naming Method, the Local Naming Method can use any port number as long as it has not been used. The Oracle default port number is 1521 or 1526.

Q: Describe the Oracle Names Method?
A: When you using the Oracle Names method, the same as the Local Naming method, a client must specify a username, password, and a connect string. The connect string must be your Service Name. In contrast to the local Naming Method, the Oracle Names Method will use the IP address or host name where the Oracle Name Server was installed (not the database hostname or IP address) and is listening. Then from the Oracle Name Server, a user process will be directed to an assigned destination database server.

Q: How do you add a listener using the Net Manager tool?
A: Click on the �+� sign next to the �Listeners� item to expand it. You will see a default created listener. To create a new listener, highlight the �Listeners� item and click on the green �+� sign in the vertical toolbar in the Oracle Net Manager window. In the �Choose Listener Name� window, type a new listener name such as �MYLISTENER� and then click OK.

Q: How do you modify a listener using the Net Manager tool?
A: Open the listener and modify the changes.

Q: How do you view all listeners in your server using the Net Manager tool?
A: In the Oracle Net Configuration section, expand the Local item. In the Local item, you can display listeners.

Q: How do you enable or disable a listener�s logging using the Net Manager tool?
A: Click on the �Logging & Tracing� tab to enable or disable your logging. The default is enabled and the location of your log is: %ORACLE_HOME\network\log\<your listener name>. You can also, disable or enable the tracing. The default is disabled. Enabling this option may generate thousands of bytes of dump to trace the network traffic. The tracing can be on USER, ADMIN, etc.

Q: How do you add a service name using the Net Manager tool?
A: To create a Service Name, highlight the Service Naming item and then click on the Green �+� sign (Create icon). In the �Net Service Name Wizard: Welcome� window, type your net service name (myaccess). Click Next.

Q: How do you modify a service name the Net Manager tool?
A: Open a service name and then modify the changes.

Q: How do you test a service name using the Net Manager tool?
A: Click on the Test button to test the connection. If failed check the userid and password. To change the userid and password, you should click on the �Change Login� push button on the window. Type the right userid and password (SYSTEM/MANAGER) and click OK to close the window. In the Connection Test window, click on the �Test� button again. If your user id or your net work configuration parameter were right, then you get �The connection test was successful.� Then Close the window and click �Finish.� Notice that the �MYACCESS� service name was added to the list of Service Name.