LAB 1
Displaying basic information provided by oracle database regarding Startup and
Shutdown for following commands.
1. TASK
a. Startup nomount and mount
STARTUP NOMOUNT
STARTUP MOUNT
b. Command to open connection to database after each mount and nomount startup
ALTER DATABASE OPEN;
c. Startup Restrict along with command to enable and disable restricted sessions.
STARTUP RESTRICT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
2. TASK
a. Execute shutdown commands and display the result and write what you understood
on each command.
i. Shutdown /shut/shutdown normal
ii. Shutdown Transactional
iii. Shutdown immediate
iv. Shutdown abort
b. Execute select username, account_status,default_tablespace from dba_users where
rownum<5; Display result
LAB 2
Displaying basic information in oracle database and Formatting Output in
SQLPLUS
1. TASK
a. Viewing user, account_ status and default Tablespace
SELECT username, account_status, default_tablespace
FROM dba_users
WHERE ROWNUM < 5;
b. Display Current User in the System
SHOW USER; OR SELECT USER FROM DUAL;
c. Display Privilege granted to a user
SELECT * FROM user_sys_privs;
d. Displaying all the users in the database
SELECT username FROM dba_users;
2. TASK
a. Formatting output in the SQLPLUS: Setting linesize, formatting column and limiting
number of results to 300, a30
b. Execute select username, account_status,default_tablespace from dba_users where
rownum<5; Display result
LAB 3
Displaying basic information in oracle database and Output in SQLPLUS for
Multiplexing Control File Using Pfile in Oracle
1. TASK
a. Multiplexing Control File Using Pfile in Oracle
i. Open Command Prompt and open SqlPlus and login as SYS as SYSDBA
ii. Startup the database if database is not open
iii. Check the Parameter file used
iv. Create pfile from spfile (pfile will be created in the same location as spfile)
v. Check the control file (use v$controlfile or show control_files)
vi. Shut down the database and exit from SqlPlus
vii. Edit the control pfile (INITORCL.ORA) by adding new information about
control file
viii. Copy the control files using operating system commands like copy in
windows or CP in linux
ix. Login to Database SYS AS SYSDBA and startup the database using recently
updated pfile
x. Check the Control File
xi. Create Spfile from pfile (Very Important) and shutdown the database
xii. Startup the database (Database uses spfile) and check the control file
xiii.
xiv. Finished
LAB 4
Displaying basic information on User, Roles and Privileges Management in Oraclein
oracle database and Output in SQLPLUS.
1. TASK
a. Show account status from dba_users for user
b. Create user with password
c. Show user roles privileges using user_role_privs and user_sys_privs command
d. Unlock the user account if locked.
e. Drop user
2. TASK
a. Create Role
b. Create New user
c. Grant [resource connect, session etc] to role
d. Assign role to New user
e. Connect New user to database
f. Assign role to New user
g. Connect New user to database
h. check dba_role_privs for grantee, granted_role from dba_role_privs command
LAB 5
Displaying basic information on Oracle Tablespaces in oracle database and Output in
SQLPLUS.
1. TASK
a. show and select tablespace names in oracle database [dba_tablespaces]
b. select name, size, status of tablespaces in oracle database
c. Show filename associated with tablespace_name with size in oracle database
[dba_data_files]
d. Format column for tablespace_name and file_name of format a30 again execute step
C.
e. select file name from dba_data_files limiting rows to less than 5
f. Select from V$tempfile and display output
LAB 6
Displaying basic information on Enabling, Disabling Archivelog Mode in oracle
database and Output in SQLPLUS.
1. TASK [Enable]
a. Check name, log_mode in oracle database [v$database] make sure database is
running on SPFile
b. Enable archive mode in oracle database by altering system and set
log_archive_dest_l='LOCATION= FILE SYSTEM PATH' and Scope to spfile.
c. Shut immediate oracle database
d. Start Oracle database in mount mode
e. Alter database to archivelog
f. Alter database to open connection
g. Execute Step a again to verify
h. Check the archive log list.
2. TASK[Disable]
a. Check name, log_mode in oracle database [v$database]
b. Check archive log list
c. shutdown database server
d. Start database server in mount mode
e. Alter database to noarchivelog|
f. Open database for connection
g. Execute step "a" for verification.
LAB 7
[ Proceed with Caution on this Lab. It is highly recommended to do careful
inspection]
Displaying basic information on Taking Host and Cold backup in oracle database and
Output in SQLPLUS.
1. TASK [Cold Backup and Recover]
Backup
a. Check File name, member in oracle database [v$datafile,
v$logfile,v$controlfile].
b. Shutdown immediate
c. Make new directory to backup oracle database eg [D:\Backup\ or /usr/coldbackup/] in
windows and linux respectively.
i. Create Backup Directory:
For Windows:
Create a folder, e.g., D:\Backup
4. Copy *.dbf, *.log, *.ctl, *.ora file to newly created backup directory
From Oracle installation directories, copy the following:
● All .dbf files (datafiles)
● All .log files (redo logs)
● All .ctl files (control files)
● init*.ora or spfile*.ora (parameter files)
d. Copy *.dbf, *.log, *.ctl, *.ora file to newly created backup directory
e. Execute Alter database backup controlfile to trace;
Recovery for no-achieving mode and with redolog archieving mode
a. Shutdown immediate
b. copy d:\Backup\ *.dbf,*.ctl,*.log to respective oracle database folder for
no-archieving mode
c. For archieving mode run command "recover database untile cancel;" and "alter
database open resetlogs;"
d. For control file recovery from cold backup run command "recover database using
backup controlfile until cancel;"
e. startup
