iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Training

    Home      .Services     Login       Start Learning     Certification      .                 .Share your BELIEF(s)...

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       Email2aFriend    |

 

 

ANSWERS

Questions

More Resources by Google:

SQL

 

PL/SQL

 

FORMS

 

REPORTS

 

DBA Fundamentals I

 

DBA Fundamentals II

 

Performance Tuning

 

Oracle 10g New Features

Oracle DBA #2 Fundamental Exam Answers

 

NOTE: The answers go with their sequences. If a question was not answer, that means that 

it a repeating question and the answer was given by the previous questions or it is not in the 

scope of this subject.

 

 “We promise according to our hopes and perform according to our fears.”

 

Francois De La Rochefoucauld 1613-1680

Answers:

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;

	GRANT

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

                                          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.

 

 

Google
 
Web web site