Database Administration PU Past Paper 2017

Q#1: Write down the steps with the recovery of the database in non-archiving mode with commands?

In non-archiving mode, you must restore the following database files.

Datafiles
Control files
Redo log files
Password file
Parameter file

Recovery in non-archiving log made with Redo Log files Backup:

1. Shutdown the database and restore all oracle files:

SQL > SHUTDOWN ABORT;

2. Restore the instance.

SQL > CONNECT / as sysdba;

3. Startup:

SQL > STATUP;

4. Backup

Q#2: Write a recovery step with the command. When database running in Archive log mode and database initially closed?

SQL>shutdown abort;
Mount the database in restricted mode.;
SQL>start up restrict mount;;
Check the list of files for which media recovery will be needed.;
SQL>select*from v$recovery_log;;
Copy an intact copy of the data file from secondary storage.;
Copy all the archive log files to the required destination.;
Recover all the database:;
SQL>recover database;;
In case of complete recovery:;
SQL>alter database open:;
In case of partial recovery:;
SQL> alter database open reset logs;;

Q#3: Add redo log (log01.rdo, log02.rdo) to each group (Group1, Group2) in your database located on “mydb”.

ALTER DATABASE ADD LOGFILE MEMBER;
'$mydb/ORADATA/u04/log01.rdo' TO GROUP 1,;
'$mydb/ORADATA/u04/log01.rdo' TO GROUP 2,;

Creating Redo Log Groups:

To create a new group of redo log files, use the SQL statement ALTER DATABASE with the ADD LOGFILE clause.;
The following statement adds a new group of redo logs to the database:;
ALTER DATABASE;
ADD LOGFILE ('/oracle/dbs/log1c.rdo', '/oracle/dbs/log2c.rdo') SIZE 4M;;

Q#4: Create table space with the name “user01” locally managed with uniform size extends.

Use create tablespace command:

CREATE TABLESPACE user01;
DATAFILE '/u01/oradata/user01.dbf' SIZE 500M;
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K;;
a. Locally managed tablespaces do not record free space in the data dictionary, it reduces contention on these tables.;
b. Local management of extents automatically tracks adjacent free space.;
c. Sizes of the extent that are managed locally can be determined automatically by the system. Alternatively, all extents can have the same size in a locally managed tablespace.;

Long Questions

Q#1: Difference between PFILE and SPFILE?

PFILE:

PFILE is a parameter file.
PFILE is a init.ora file.
PFILE is a static parameter file.
PFILE is a text file.
PFILE can be modified with an operating system editor.
Changes to the file take effort on the next startup.
Only opened during instance startup.

It's default location is:$ORACLE-HOME/dbs.
Types of Parameter:
There are two types of parameter
Explicit:
Having an entry in the file.
Implicit:
No entry within the file: but assuming the Oracle default values.

SPFILE:

SPFILE is a server parameter file.
SPFILE is a dynamic parameter file.
SPFILE is a binary file.
SPFILE is maintained by the Oracle server.
Always resides on the server-side.
Ability to make changes persistent across shut down and startup.
Can self-tune parameter file?
Can have Recovery Manager support backing up to the initialization parameter file.

Q#2: Describe different stages of starting up instances?

When Oracle Database starts an instance, it reads the server parameter file or initialization parameter file to determine the values of the initialization parameter.

Restricted Mode of instance Startup:

You can start an instance in restricted mode. This restricts connections to only those users who have been granted The RESTRICTED SESSION system privilege.

Forced Startup in Abnormal Situations

In unusual circumstances, a previous instance might not have been shut down cleanly. To resolve this problem, you must terminate all remnant Oracle Database processes of the previous instances before starting the new instance.

Starting Up an Oracle DB Instance: NOMOUNT:

An instance is typically started only in NOMOUNT mode during database creation, during re-creation of control file. Starting an instance includes the following tasks. Searching of a particular name.

Start Up an Oracle database instance: MOUNT

Mounting a database includes the following tasks.
Associating a database with a previously started instance.
Locating and opening the control file specified in the parameter file.
Reading the control files to obtain the names and statuses of the data files and online redo log files.
To perform specific maintenance operations, start an instance and munt a database.
Renaming data files.
Performing full database recovery.

Starting Up an Oracle database instance: Open:

A normal database operation means that an instance is started and the database is mounted and opened. Opening the database instance in the following tasks.

Opening the data files.
Opening the online redo log files.

Q#3: Write a query that is used to create a table space and convert into Locally Managed table space?

The extents allocated to a locally managed tablespace are managed through the use of bitmaps. The example below shows the four ways a tablespace can be created, including the default creation which is the same as using the DICTIONARY clause.
CREATE TABLESPACE tsh_data_1
DATAFILE 'c:\Oracle\Oradata\TSH1\tsh101.dbf' SIZE 50M;
CREATE TABLESPACE tsh_data_2
DATAFILE 'c:\Oracle\Oradata\TSH1\tsh201.dbf' SIZE 50M
EXTENT MANAGEMENT DICTIONARY;
CREATE TABLESPACE tsh_data_3
DATAFILE 'c:\Oracle\Oradata\TSH1\tsh301.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
CREATE TABLESPACE tsh_data_4
DATAFILE 'c:\Oracle\Oradata\TSH1\tsh401.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Maintenance:

//Local to dictionary
EXEC DBMS_SPACE_ADMIN.tablespace_migrate_from_local('TSH_DATA_3');
//Dictionary to local
EXEC DBMS_SPACE_ADMIN.tablespace_migrate_to_local('TSH_DATA_3');
Benefits of Locally Managed Tablespace:
1. Locally managed tablespace cannot record free space in the data dictionary.
2. All extents can have the same size in a locally managed tablespace.

Q#4: Explain the recovery procedure w.r.t Redo log files and UNDO segments.

Redo Log Files:

The recovery procedure is depending upon the following:

The configuration of the online redo log.
The type of media failure is temporary or permanent.
The type of online redo log files affected by the media failure.
Using online Redo log files.
Online redo log files have the following characteristics.
Records all changes made to data.
Provide a recovery mechanism
Can be organized into groups.
At least two groups are required.

Redo logs:

The Oracle maintains a record of all transactions called the redo log.
Undo segments: Purpose Transaction Roll Back:
When a transaction modifies a row in a table, the old image of a modified column is saved in the Undo segment. If the transaction is rolled back, The Oracle server restores the original value by writing the values in the undo segment back to the row.
Transaction Recovery:
This rollback is part of transaction recovery. Recovery is possible only because changes made to the undo segment are also protected by online redo log files.
Read Consistency:
While transactions are in the progress, other users in the database should not see any uncommitted changes made by these transactions.

Q#5: Define Physical and lgical structure of database?

Physical Database Structure:

It is simply the physical data files that you can see in a file manager. You can find them in the directory where your database is resided.

Few Types of files:

Data file
Parameter File
Control File
Redo log File

Logical Structure:

It only stays in memory, where physically store in physical file. It only appears when the database service is started and only can be view by using certain database tools.