Database Administration IS4510 Lab Manual

(1)

Database Administration Lab Manual

IS4510

Compiled by:

Zafar Iqbal Khan

(2)

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

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.

(3)

4. To shutdown oracle instance quickly, urgently do following

SQL> shutdown immediate Database closed.

Database dismounted.

ORACLE instance shut down.

5. To shutdown oracle instance without committing any ongoing transaction

SQL> shutdown abort

ORACLE instance shut down.

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 835104768 bytes Fixed Size 2257800 bytes Variable Size 499125368 bytes Database Buffers 331350016 bytes Redo Buffers 2371584 bytes Database mounted.

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

SQL> startup open

ORACLE instance started.

Total System Global Area 835104768 bytes Fixed Size 2257800 bytes Variable Size 499125368 bytes Database Buffers 331350016 bytes Redo Buffers 2371584 bytes Database mounted.

(4)

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

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

(5)

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 TYPE VALUE --- --- --- hi_shared_memory_address integer 0

memory_max_target big integer 1G memory_target big integer 1G shared_memory_address integer 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 TYPE VALUE --- --- --- db_cache_advice string ON

(6)

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 TYPE VALUE --- --- --- shared_pool_reserved_size big integer 7M

shared_pool_size big integer 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 TYPE VALUE --- --- --- archive_lag_target integer 0 db_flashback_retention_target integer 1440 fast_start_io_target integer 0 fast_start_mttr_target integer 0 memory_max_target big integer 800M memory_target big integer 800M parallel_servers_target integer 64 pga_aggregate_target big integer 0 sga_target big integer 0

(7)

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

(8)

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.

(9)

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.

(10)

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# NAME INC BIG FLA ENC --- --- --- --- --- --- 0 SYSTEM YES NO YES

2 UNDOTBS1 YES NO YES 1 SYSAUX YES NO YES 4 USERS YES NO YES 3 TEMP NO NO YES 6 RMAN_TB YES NO 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

(11)

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)

(12)

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:

(13)

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;

(14)

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

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

(15)

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

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": hr/******** directory=demo dumpfile=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

(16)

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

(17)

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

(18)

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

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