Q#1: Explain Database server with Diagram?
Database server in the used to refer to the back-end system of a database application using client / server architecture. The back-end performs tasks such as data analysis, storage, data manipulate, archiving and other non-user specific tasks.
Q#2: 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 data dictionary.
2. All extents can have the same size in locally managed tablespace.
Q#3: Define Profile?
A profile is a database object - a named set of resource limits to:
Profiles restrict users from performing operations that exceed reasonable
resource utilization.
Examples of resources that need to be managed:
- Disk storage space.
- I/O bandwidth to run queries.
- CPU power.
- Connect time.
- CPU time.
Q#4: Write a command to set password setting through profile?
SQL > CREATE PROFILE onsite LIMIT
PASSWORD-LIFE-TIME 45
PASSWORD-GRACE-TIME 12
PASSSWORD-REUSE-TIME 3
PASSWORD-REUSE-MAX 5
FAILED-GOGIN-ATTEPTS 4
PASSSWORD-LOCK-TIME 2
Long Question
Q#1: Write down the steps with recovery of 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 > SHUTDWON ABORT;2. Restore the instance.
SQL > CONNECT / as sysdba;3. Startup:
SQL > STATUP;4. Backup
Q#2: What is Role? Explain the Different Characteristic of role?
Database Administrators are the specified software that are store and organized data.
Role includes the capacity planning, performance, monitoring, security, configuration, installation, migration as well as backup and recovery. Roles created by users’ group together privileges or other roles. There is a way to facilitate the granting of multiple privileges or other roles.
Oracle provides for easy and controlled privileges management through roles.
Roles are named group of related privilege that are granted to the other roles to users.
Role Characteristics:
- Granted to and revoked from users with the same commands used to grant and revoke system privilege.
- May be granted to any user or role. However, a role cannot grant to itself.
- Can consist of both system and object privilege.
- May be enabled or disabled for each user granted to the role. Can require a password to enable.
- Each role name must be unique in username and role names
Q#3: Create undo table spaces UNDO2 size ISM in SHOME/ORADATATA/CO3. List the roil back segment in table spaces UNDO2?
CREATE UNDO TABLESPACE UNDO2
FATAFILE ‘/$ GOME / ORADAAT / UO3 .dbf ’
SIZE 15M REUSE AUTOEXTEND ON
Create Rollback Segment:
CREATE ROLLBACK SEGMENT UNDO2-rbs2
TABLESPACE UNDO2
STORAGE (INITIAL 100K NEXT 100K OPTIMAL 4M MINEXTENT 2u);
Displaying Segment:
SELECT SEGMENT-NAME, TABLESPACE-NAM, BYTES,
BLOCK FROM DBA-SEGMENT,
WHERE SEGMENT TYPE = ’ ROLLBACK ’ :
Q#4: Write down the steps and commands of taking online database backup?
STEP 1:
Shutdown the database if it is running.
STEP 2:
Take a full offline backup.
STEP 3:
Set the following parameters in parameter file.
LOG_ARCHIVE_FORMAT=ica%s.%t.%r.arc
LOG_ARCHIVE_DEST_1=location=/u02/ica/arc1
If you want you can specify second destination also
LOG_ARCHIVE_DEST_2=location=/u02/ica/arc1
Step 4:
Start and mount the database.
SQL> STARTUP MOUNT
STEP 5:
Give the following command
SQL> ALTER DATABASE ARCHIVELOG;
STEP 6:
Then type the following to confirm.
SQL> ARCHIVE LOG LIST;
STEP 7:
Now open the database
SQL>alter database open;
Step 8:
It is recommended that you take a full back up after you brought the database in archive log mode.
See Database Administration Past Paper 2017
See Database Administration Past Paper 2019