Database Administration IS4510 Lab Manual

Database Administration Lab Manual
IS4510
Compiled by:
Zafar Iqbal Khan
Lecturer, Dept of IS, CCES,
Salaman bin Abdul Aziz University
Lab Session - 1
Objective:- To be aware of various Oracle Database Startup & Shutdown options
Description:- After completion of this session you would be able start and shut down oracle
instance in various modes and test oracle listener
1. To start Oracle Instance do following, at command prompt
C:\Windows\System32>sqlplus
SQL*Plus: Release 11.2.0.2.0 Production on Mon Dec 29 08:32:43 2014
Copyright (c) 1982, 2014, Oracle.
All rights reserved.
Enter user-name: sys as sysdba

Enter password:
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
2. To check the version of Oracle version

SQL> select * from v$version;
BANNER
----------------------------------------------------------Oracle Database 11g Express Edition Release 11.2.0.2.0 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE
11.2.0.2.0
Production
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 Production
3. To shutdown oracle instance normally do following
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
Compiled and Tested by Zafar Iqbal Khan, IS, CCES, SAU @ Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Ed
4. To shutdown oracle instance quickly, urgently do following

SQL> shutdown immediate
Database closed.
Database dismounted.
5. To shutdown oracle instance without committing any ongoing transaction
SQL> shutdown abort
6. Start the instance and mount the database, but leave it closed. This state
Allows us for certain DBA activities
SQL> startup mount
ORACLE instance started.
Total System Global Area
Fixed Size
Variable Size
Database Buffers
Redo Buffers
Database mounted.
835104768
2257800
499125368
331350016
2371584
bytes
bytes
bytes
bytes
bytes
7. To start instance, mount database and open it for database activities

SQL> startup open
ORACLE instance started.
Total System Global Area
Fixed Size
Variable Size
Database Buffers
Redo Buffers
Database mounted.
Database opened.
835104768
2257800
499125368
331350016
2371584
bytes
bytes
bytes
bytes
bytes
8. To open database in read-only mode, do it (valid with startup mount clause)

SQL> alter database open read only;
Database altered.
9. To exit from database, do it
SQL> exit
Disconnected from Oracle Database 11g Express Edition
Release 11.2.0.2.0 - 64bit Production
10. To check the listener parameters and ping oracle server, do this
C:\Windows\System32>tnsping xe
TNS Ping Utility for 64-bit Windows: Version 11.2.0.2.0 Production on 29-DEC-2
014 09:06:58
Copyright (c) 1997, 2014, Oracle.
Used parameter files:

C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\
sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL =
TCP)(HOST = DELL)(PORT = 1521)) (CONNECT_DATA = (SERVER =
DEDICATED) (SERVICE_NAME = XE)))OK (0 msec)
* here xe is the name of oracle_sid
Lab Session - 2
Objective:- To Analyze various memory structures of Oracle Instance and its components.
1. To see various memory parameters
SQL> show parameter memory
NAME
-----------------------------------hi_shared_memory_address
memory_max_target
memory_target
shared_memory_address
TYPE
----------integer
big integer
big integer
integer
VALUE
------0
1G
1G
0
2. To see SGA size

SQL> show parameter sga_max_size
NAME
TYPE
VALUE
------------------------------------ ----------- -------sga_max_size
big integer 800M
3. To see PGA size
SQL> show parameter pga_aggregate_target
NAME
TYPE
VALUE
------------------------------------ ----------- -------pga_aggregate_target
big integer 0
* here 0 represents this parameter is being maintained by system
4. To analyze the various components of SGA
a. For buffer cache
SQL> show parameter db_cache
NAME
-----------------------------------db_cache_advice
db_cache_size
TYPE
----------string
big integer
VALUE
------ON
0
b. For java pool

SQL> show parameter java_pool
NAME
TYPE
VALUE
------------------------------------ ----------- ------java_pool_size
big integer 0
c. For shared pool
SQL> show parameter shared_pool
NAME
-----------------------------------shared_pool_reserved_size
shared_pool_size
TYPE
----------big integer
big integer
VALUE
------7M
0
d. for large pool

SQL> show parameter large_pool_size
NAME
TYPE
VALUE
------------------------------------ ----------- ------large_pool_size
big integer 0
5. To see all target parameter values

SQL> show parameter target
NAME
-----------------------------------archive_lag_target
db_flashback_retention_target
fast_start_io_target
fast_start_mttr_target
memory_max_target
memory_target
parallel_servers_target
pga_aggregate_target
sga_target
TYPE
----------integer
integer
integer
integer
big integer
big integer
integer
big integer
big integer
VALUE
----0
1440
0
0
800M
800M
64
0
0
Lab Session 3
Objective:- To manually manage various memory parameters of oracle instance.
Description:- SGA_TARGET , SGA_MAX_SIZE , Memory_Target and Memory_Max_Size are the
parameter used by Oracle Instance to allocate memory to various memory structures.
SGA_TARGET :- It specifies the total amount of SGA memory available to an instance. Setting
this parameter makes Oracle distribute the available memory among various components such as shared pool (for SQL and PL/SQL), Java pool, large_pool and buffer cache - as
required.
This new feature is called Automatic Shared Memory Management. With ASMM, the
parameters java_pool_size, shared_pool_size, large_pool_size and db_cache_size need not
be specified explicitely anymore.
* sga_target cannot be higher than sga_max_size.
SGA_MAX_SIZE:- sga_max_size sets the maximum value for sga_target

Memory_Target:- It specifies total amount of memory available for an Oracle Instance
Memory_Max_Size:- It specifies Maximum amount of memory an oracle instance may have.
1. To check the SGA, do it

SQL> select * from v$SGA;
NAME
VALUE
-------------------- ---------Fixed Size
2257800
Variable Size
499125368
Database Buffers
331350016
Redo Buffers
2371584
2. To check SGA parameters do following
a. SQL> show parameter sga_target;
NAME
TYPE
VALUE
------------------------------------ ----------- -----sga_target
big integer 0
b. SQL> show parameter sga_max_size;

NAME
TYPE
VALUE
------------------------------------ ----------- -----sga_max_size
big integer 800M
3. Now assign new values to these parameters
SQL> alter system set sga_max_size=1g scope=spfile;
System altered.
SQL> alter system set sga_target=100M scope=spfile;
System altered.
Lab Session - 4
Objective:- To locate the oracle alert log file and then read the alert log file messages.
Exercise:On SQL, prompt issue command
SQL> show parameter background
NAME
TYPE
VALUE
------------------------------------ ----------- -----------------------------background_core_dump
string partial
background_dump_dest
string C:\oraclexe\app\oracle\diag\rdbms\xe\xe\trace
Inside trace folder alert_xe is the alert log file.
Alternatively, Oracle 11g also stores alert log in XML format which can be accessed at
SQL> select * from v$diag_info where name='Diag Alert';
INST_ID
NAME
VALUE
-----------------------------------------------------------------------------------------------------------------------1
Diag Alert
C:\ORACLEXE\APP\ORACLE\diag\rdbms\xe\xe\alert
Inside alert folder log.xml is the alert log in XML form.
Lab Session - 5
Objective:- To learn creation & manipulation of tablespaces, resizing them, reclaiming free
space, making them online/offline.
1. To know the name & no of tablespaces in any database, login with sys as sysdba
SQL> select * from V$TABLESPACE;
TS#
---------0
2
1
4
3
6
NAME
-----------------------------SYSTEM
UNDOTBS1
SYSAUX
USERS
TEMP
RMAN_TB
INC
--YES
YES
YES
YES
NO
YES
BIG
--NO
NO
NO
NO
NO
NO
FLA ENC
--- ----YES
YES
YES
YES
YES
YES
6 rows selected.
2. To create a new Tablespace

SQL> create tablespace demo
2 datafile 'C:\RMAN\DEMO\DEMO.dbf' size 5M autoextend on
3 extent management local;
Tablespace created.
* 'C:\RMAN\DEMO\DEMO.dbf' is the location of data file and
demo.dbf is the name of data file
3. To add new data file into an existing Tablespace
SQL> alter tablespace demo add datafile
'c:\rman\demo\datafile.dbf' size 15M autoextend on;
Tablespace altered.
* 'C:\RMAN\DEMO\DEMO.dbf' is the location of data file and
demo.dbf is the name of data file
4. To resize an existing Tablespace

SQL> alter database datafile 'c:\rman\demo\datafile.dbf'
resize 10M;
Database altered.
5. To make a Tablespace offline.
SQL> alter tablespace users offline;
Tablespace altered.
6. To make Tablespace online.
SQL> alter tablespace users online;
Tablespace altered.
* Here user is the name of tablespace
7. To Coalesce a Tablespace
SQL> alter tablespace users coalesce;
Tablespace altered.
8. To delete a Tablespace first make it offline and then delete it
SQL> alter tablespace demo offline;
Tablespace altered.
//Now DELETE Tablespace
SQL> drop tablespace demo;
Tablespace dropped.
* Tablespace is dropped by this command but you have to manually delete datafiles
from OS File System.(C: or D: whatever be the location)
Lab Session - 6
Objective:- To create user & grant it privileges & revoke them.
1. To create a user demo with password demo1
SQL> create user demo identified by demo
2 default tablespace users
3 temporary tablespace temp;
User created.
* here users & temp are system tablespaces
2. Grant privileges to demo
SQL> grant connect, resource to demo;
Grant succeeded.
3. Now grant access to table employees from user HR to user demo
SQL> grant select on hr.employees to demo;
Grant succeeded.
4. Login to user demo
SQL> conn demo/demo
Connected.
5. Select some data from EMPLOYEES table of user HR
SQL> select email, salary from hr.employees where
first_name='William';
EMAIL
SALARY
------------------------- ---------WGIETZ
8300
WSMITH
7400
6. Again login to sys as sysdba
SQL> conn sys as sysdba
Enter password:
Connected.
7. Now revoke the privileges from user demo

SQL> revoke select on hr.employees from demo;
Revoke succeeded.
To verify revoking, do following
8. Relogin as demo
SQL> conn demo/demo
Connected.
9. Again select some rows from hr.employees
SQL> select email, salary from hr.employees where first_name='William';
select email, salary from hr.employees where first_name='William'
*
ERROR at line 1:
ORA-00942: table or view does not exist
10. To delete a user do following. (login as sysdba before doing this)

SQL> drop user demo cascade;
User dropped.
Lab Session 7
Export & Import Datapump
Objective:- To create oracle logical backup by exportdp and then restore database from this
logical backup to original state by importdp
1. Create a folder C:\test
2. Login in oracle as sysdba
3. Create a directory in oracle with following command
SQL> create directory demo as 'c:\test';

Directory created.
4. From new console window run following command
C:\Windows\System32>expdp hr/hr directory=demo tables=jobs
dumpfile=demo.dmp logfile=demo.log;
Verbose console widow, says something like this
Export: Release 11.2.0.2.0 - Production on Mon Dec 29 10:09:36 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates.
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Pro
duction
Starting "HR"."SYS_EXPORT_TABLE_01":
hr/******** directory=demo tables=emp dump
file=demo.dmp logfile=demo.log;
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."EMP"
16.80 KB
107 rows
Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:
C:\TEST\DEMO.DMP Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at 10:09:48
Which means database backup has been completed

successfully, you can verify backup with directory C:\test
5. To Import this backup, do following

C:\Windows\System32>impdp hr/hr directory=demo
dumpfile=demo.dmp logfile=impdemo.log sqlfile=impdp.log;
To which Verbose console widow, says something like this
Import: Release 11.2.0.2.0 - Production on Mon Dec 29 10:12:05 2014
reserved.
All rights
Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 64bit Pro
duction
Master table "HR"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "HR"."SYS_SQL_FILE_FULL_01":
dumpfile=demo.
hr/******** directory=demo
dmp logfile=impdemo.log sqlfile=impdp.log;

Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "HR"."SYS_SQL_FILE_FULL_01" successfully completed at 10:12:10
Which means that import process completed successfully
Lab Session - 8
Objective:- Creating backup of Oracle Database with Recovery Manager (RMAN)
Steps:- Since database backup can be done only when it is in Archive Log mode.
Normally database works in Non Archive Log mode, so we will first change
log_mode of database, then will take backup
1. First run following query to check database status
SQL>select log_mode from v$database;
2. If status is NonArchivelog mode then shutdown database from oracle
prompt
SQL>shutdown immediate
3. Now change database to archivelog mode
SQL>Alter database archivelog;
4. Now from another command window, run following query
C:\Windows\System32>rman target /
it will find database, and show you RMAN prompt
5. From rman prompt, run following query
RMAN>backup as backupsets database;
Lab Session - 9
Objective:- To Create a user for RMAN recovery, create Catalog and register database to catalog for
recovery operations
Procedure:- First we will create an schema for RMAN recovery. We will create a tablespace and an user
with administrative privileges, then we will grant all privileges required for creating backups to this user,
after this will create a catalog and register our target database with this catalog.
Steps:
1. Create a recovery tablespace, in this case we give it name rman_tb, before doing this create
directories(c:\rman\backup\+rman) in Windows file system
SQL> create tablespace rman_tb datafile
'c:\rman\backup\+rman\datafile01.dbf'size 10M autoextend on;
Tablespace created.
2. Now create user for recovery, we give it name rman and its password is also rman (to do this
login with sys as sysdba)
SQL> create user rman identified by rman
2 default tablespace rman_tb
3 temporary tablespace temp;
User created.
3. Now grant privileges to user rman necessary for creating catalog
SQL> grant resources, connect,
recovery_catalog_owner to rman;
Grant succeeded.
4. Now relogin from same window as user rman
SQL> conn rman/rman;
Connected.
5. Check for registered database
SQL> select * from rc_database;
no rows selected
(this means that no database has been registered to our recovery schema.)
6. Now from command prompt login to RMAN with user rman

C:\Windows\System32>rman catalog rman/rman;
Recovery Manager: Release 11.2.0.2.0 - Production on Sat Dec 20
10:47:51 2014
rights reserved.
All
connected to recovery catalog database

7. Create catalog by following command
RMAN> create catalog;
recovery catalog created
8. Connect target databse with following command
RMAN> connect target /
connected to target database: XE (DBID=2742994663)
9. Register database
RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
10. From the SQL window, again validate register operation
SQL> select * from rc_database;
DB_KEY DBINC_KEY
DBID NAME
RESETLOGS_CHANGE# RESETLOGS
------ ---------- ---------- -------- ----------------- --------1
2
2742994663 XE
370965 25-OCT-14
Which means that, registration process completed successfully.

Database Administration IS4510 Lab Manual

Uploaded by

Copyright:

Available Formats

Database Administration IS4510 Lab Manual

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Database Administration IS4510 Lab Manual

Uploaded by

Copyright:

Available Formats

Database Administration Lab Manual

All rights reserved.

Enter user-name: sys as sysdba

2. To check the version of Oracle version

4. To shutdown oracle instance quickly, urgently do following

7. To start instance, mount database and open it for database activities

8. To open database in read-only mode, do it (valid with startup mount clause)

All rights reserved.

Used parameter files:

2. To see SGA size

b. For java pool

d. for large pool

5. To see all target parameter values

SGA_MAX_SIZE:- sga_max_size sets the maximum value for sga_target

1. To check the SGA, do it

b. SQL> show parameter sga_max_size;

2. To create a new Tablespace

4. To resize an existing Tablespace

7. Now revoke the privileges from user demo

10. To delete a user do following. (login as sysdba before doing this)

SQL> create directory demo as 'c:\test';

All rights reserved.

hr/******** directory=demo tables=emp dump

Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

Which means database backup has been completed

5. To Import this backup, do following

dmp logfile=impdemo.log sqlfile=impdp.log;

Which means that import process completed successfully

6. Now from command prompt login to RMAN with user rman

connected to recovery catalog database

You might also like