Q#1: In an Organization, the tablespace contains important salary table is drop at 10 o’clock. How can we recover our Database? Write all steps commands?
Shutdown and Backup:
Shutdown database Mydb;
Cp ‘folder\ ‘ . ‘ folder 2 \ data;
Restore datafiles:
Cp ‘folder\ ‘ . ‘ folder 2 \ data;
Mount database:
Mount database Mydb;
Recover database:
Recover tablespace tb untill 10:00 am;
Open database with reset logs:
Alter database open resetlogs;
Backup database:
Cp ‘folder\ ‘ . ‘ folder 2 \ data;
Q#2: Write command to create a user ALI having 1GB Quota on each Tablespace TBS1 and TBS2?
The statement query is given below.
CREATE USER Ali
IDENTIFIED BY out_standing1
DEFAULT TABLESPACE TBS1
QUOTA 1G ON TBS1
TEMPORARY TABLESPACE TBS2
QUOTA 1G ON system
PROFILE app_user
PASSWORD EXPIRE;
As 1GB=1000MB
CREATE USER ALI
IDENTIFIED BY pass
QUOTA 1000M ON TBS1
QUOTA 1000M ON TBS2
Q#3: What is server Process? Explain difference between Dedicated and shared server?
A server process is a program that directly interacts with the Oracle
server.
It fulfills calls generated and returns results. It may be dedicated or shared
server.
Difference between Dedicated and Shared Server:
Dedicated Server:
In a dedicated server environment, the server process handles the request of a single user process. Once a user process disconnects, the server process is terminated.
Shared Server:
In a shared server environment, the server process handles the request of several user processes.
Q#4: What is control File? Multiplex a control file when using PFILE.
The control file is a binary file that defines the current state of the physical database. The control file is a small binary file necessary for the database to start and operate successfully.
- Each control file is associated with only one Oracle database.
- Before a database is opened, the control file is read to determine if the database is in a valid state to use.
- A control file is updated continuously by the Oracle server during database use.
- The information in the control file can be modified only by the Oracle server; no database administrator or end user can edit the control file.
Multiplex a control file when using PFILE:
Shut down the database in a normal state:
SQL > shutdown normal
Copy the existing control file to a new name and location:
$ cp control01.ct1 …/DIKS3/control02.ct1
Add the name control file name to init.ora:
CONTROL_FILES = (/DISK1/contol01.ct1,
/DSKI3/control02.ct1)
Start the database:
SQL > startup
Long Question
Q#1: Explain the working of query in shared server configuration with daigram?
How a Request is Processed:
- A user sends a request to its dispatcher.
- The dispatcher places the request into the request queue in the System Global Area (SGA).
- A shared server picks up the request from the request queue and processes the request.
- The shared server places the response on the calling dispatcher's response queue.
- The response is handed off to the dispatcher.
- The dispatcher returns the response to the user.
Once the user call has been completed, the shared server process is released and is available to service another user call in the request queue.
Request Queue:
One request queue is shared by all dispatchers. Shared servers monitor the request queue for new requests. Requests are processed on a first-in, first-out (FIFO) basis. servers place all completed requests on the calling di. Each dispatcher has its own response queue in the SGA.
Response queue:
Each dispatcher is responsible for sending completed requests back to the appropriate user process. Users are connected to the same dispatcher for the duration of a session.
Q#2: What is Parameter File? How many types of parameter files in oracle?
A parameter file is a file that contains a list of initialization parameters and a value for each parameter. You specify initialization parameters in a parameter file that reflect your particular installation. Oracle supports the following two types of parameter files:
- Server Parameter Files (SPFILE)
- Text Initialization Parameter Files (PFILE)
PFILE (Initialization Parameter Files):
- The PFILE is a text file that can be modified with an operating system editor.
- Modifications to the file are made manually.
- Changes to the file take effect on the next startup.
- Its default location is $ORACLE_HOME/dbs.
SPFILE :
- Binary file with the ability to make changes persistent across shutdown and startup
- Maintained by the Oracle server
- Records parameter value changes made with the ALTER SYSTEM command
- Can specify whether the change being made is temporary or persistent
- Values can be deleted or reset to allow an instance to revert to the default value ALTER SYSTEM SET undo_tablespace = 'UNDO2';
Q#3: Write a command to make a temporary tablespace and then make it default temporary tablespace?
Default Temporary Tablespace:
A default temporary tablespace can be created and set by:
Using the CREATE TABLESPACE command to create a temporary tablespace Using the ALTER DATABASE command. When creating a database without a default temporary tablespace the SYSTEM tablespace is assigned to any user-created without a TEMPORARY TABLESPACE clause.
SQL> CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/Oradata /mytemp_01.tmp' SIZE 20M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
Once defined, users who are not explicitly assigned to a temporary tablespace are assigned to the default temporary tablespace.
The default temporary tablespace can be changed at any time by using the ALTER DATABASE.
DEFAULT TEMPORARY TABLESPACE command.
Q#4: What is recovery? Write database recovery steps when database running in No Archive mode?
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:
Shutdown the database and restore all oracle files:
SQL > SHUTDWON ABORT;
Restore the instance.
SQL > CONNECT / as sysdba;
Startup:
SQL > STATUP;
Backup:
Q#5: Explain role in Database?
Explain Index Entry Structure with diagram?
Index:
An index on a file speeds up selections on the search key fields for the index. An index is a collection off data entries.
Index Entry:
IT includes two items or two fields, one is in the key index field and another is a pointer to the block which contains the record of that kay value.
Index Entry Structure:
An index is made up of the following components.
- Key column length.
- Key column value.
- ROWID
In a B-tree index:
- Suitable for high-cardinality columns
- Update on keys relatively inexpensive.
- Inefficient for queries using OR predicates.
- Useful for OLTP (online transaction processing).
- Key values are repeated if there are multiple rows that have the same key value.
- There is no index entry corresponding to a row that has all key columns that are null.
- Restricted ROWID is used to point to the rows of the table.
Bit-map index:
- Suitable for low-cardinality columns.
- Updates to key columns are very expensive.
- Efficient for queries using OR predicates.
- Useful for data warehousing.
- It is also organized as a B-tree but the leaf node stores, a bitmap for each key-value instead of a list of ROWIDs.
- Each bit in the bitmap corresponds to a possible ROWID.
- If the bet is set, it means that the row with the corresponding ROWID contains the key value.
- Bitmap indexes use restricted ROWIDs
See Database Administration Past Paper 2018