Practice 9i DBA practicals

SESSION 1.

1. Create a database user?

SQL> create user test1 identified by test1
default tablespace users temporary tablespace temp;

2. Grant connect and resource privileges to the user created?

SQL> grant connect, resource to test1;

3. Create a tablespace with locally managed extent along with a datafile?

SQL> create tablespace test01
2 logging
3 datafile 'd:\oracle\oradata\prod\test01.dbf'
4 size 10m
5 autoextend on
6 next 10m maxsize 100m
7 extent management local;

4. Create a temporary tablespace with locally managed extents along with a datafile?

SQL> create temporary tablespace temp_test01
2 tempfile 'd:\oracle\oradata\prod\temp_test_01.dbf'
3 size 10m
4 autoextend on
5 next 10m maxsize 100m
6 extent management local;

5. Alter tablespace to add additional datafile?

SQL> alter tablespace test01
2 add datafile 'd:\oracle\oradata\prod\test_02.dbf'
3 size 10m autoextend on
4 next 10m maxsize 100m;

6. Alter tablespace to increase the size of one datafile?
SQL> alter database
2 datafile 'd:\oracle\oradata\prod\TEST_02.dbf'
3 resize 200m;

7. Get the list of data files present in a database?

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
D:\ORACLE\ORADATA\PROD\SYSTEM01.DBF
D:\ORACLE\ORADATA\PROD\UNDOTBS01.DBF
D:\ORACLE\ORADATA\PROD\CWMLITE01.DBF
D:\ORACLE\ORADATA\PROD\DRSYS01.DBF
D:\ORACLE\ORADATA\PROD\EXAMPLE01.DBF
D:\ORACLE\ORADATA\PROD\INDX01.DBF
D:\ORACLE\ORADATA\PROD\ODM01.DBF
D:\ORACLE\ORADATA\PROD\TOOLS01.DBF
D:\ORACLE\ORADATA\PROD\USERS01.DBF
D:\ORACLE\ORADATA\PROD\XDB01.DBF
D:\ORACLE\ORADATA\PROD\TEST01.DBF

NAME
--------------------------------------------------------------------------------
D:\ORACLE\ORADATA\PROD\TEST_02.DBF

12 rows selected.
8. get the list of tablespaces present in a database?
SQL> select name from v$tablespace;

NAME
------------------------------
CWMLITE
DRSYS
EXAMPLE
INDX
ODM
SYSTEM
TOOLS
UNDOTBS1
USERS
XDB
TEMP

NAME
------------------------------
TEST01
TEMP_TEST01

13 rows selected.

9. Get the list of users and their default tablespaces associated with them in a given database?

SQL> select username, default_tablespace
2 from dba_users
3 order by username;

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
ANONYMOUS XDB
CTXSYS DRSYS
DBSNMP SYSTEM
HR EXAMPLE
MDSYS SYSTEM
ODM ODM
ODM_MTR ODM
OE EXAMPLE
OLAPSYS CWMLITE
ORDPLUGINS SYSTEM
ORDSYS SYSTEM

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
OUTLN SYSTEM
PM EXAMPLE
QS EXAMPLE
QS_ADM EXAMPLE
QS_CB EXAMPLE
QS_CBADM EXAMPLE
QS_CS EXAMPLE
QS_ES EXAMPLE
QS_OS EXAMPLE
QS_WS EXAMPLE
RMAN TOOLS

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
SCOTT SYSTEM
SH EXAMPLE
SYS SYSTEM
SYSTEM SYSTEM
TEST1 USERS
WKPROXY DRSYS
WKSYS DRSYS
WMSYS SYSTEM
XDB XDB

31 rows selected.

10. Query database to get to know if spfile or pfile being used?

This query returns NULL in the value column if you are using the pfile.
SQL> SELECT name, value FROM v$parameter
2 WHERE name = 'spfile';

NAME VALUE
---------- --------------------------------------------------
spfile %ORACLE_HOME%\DATABASE\SPFILE%ORACLE_SID%.ORA
Alternatively, use the following query, which returns NULL in the value column if you are using pfile and not spfile:
SQL> SHOW PARAMETER spfile

NAME TYPE VALUE
------- ------- ------------------------------
spfile string %ORACLE_HOME%\DATABASE\SPFILE%ORACLE_SID%.ORA
On the other hand, you may check for the count of the following query; if the count is non-zero then the instance is using a spfile, and if the count is zero then it is using a pfile:
SQL> SELECT COUNT(*) FROM v$spparameter WHERE value IS NOT NULL;

SESSION 2.

1. Check if the database is in archivelog mode or not?

SQL> select name, log_mode
2 from v$database;

NAME LOG_MODE
--------- ------------
PROD ARCHIVELOG

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination D:\oracle\oradata\prod\archive
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3

2. Check the size of the database in SQL session?
To find out how many megabytes are allocated to ALL datafiles:
>select sum(bytes)/1024/1024 "Meg" from dba_data_files;
To get the size of all TEMP files:
>select nvl(sum(bytes),0)/1024/1024 "Meg" from dba_temp_files;
To get the size of the on-line redo-logs:
>select sum(bytes)/1024/1024 "Meg" from sys.v_$log;
Putting it all together into a single query:
select a.data_size+b.temp_size+c.redo_size "total_size"
from ( select sum(bytes) data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0) temp_size
from dba_temp_files ) b,
( select sum(bytes) redo_size
from sys.v_$log ) c
/


3. Check the size of all the tablespace and also check if they have autoextendable or not?
4. Export data of schema into a physical file?

>exp system/ajai@prod file=test2.dmp compress=n consistent=n
constraints=y owner=test1 grants=y indexes=y record=y rows=y feedback=1000 log=test2.log buffer=1000

5. Take a full export of database into a physical file?

> exp userid=system/manager@prod file=full_db_18sep06.dmp
log=full_db_18sep06.log full=y

6. Create a rman user and assign access privileges to the user and check for the connectivity to rman with the user?

SQL> create user backup_admin identified by backup_admin
2 default tablespace users temporary tablespace temp;

SQL> grant sysdba to backup_admin;

C:\>rman target backup_admin/backup_admin@prod

Recovery Manager: Release 9.2.0.1.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: PROD (DBID=5469274)

RMAN> show all;

using target database controlfile instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\ORACLE\ORA92\DATABASE\SNCFPROD.ORA';
# default

RMAN>

7. Create a database link and verify if the data is accessible from the other database?

SQL> create database link testcrp
2 connect to system identified by "manager"
3 using 'TEST';

SQL> select * from tab@testcrp;

8. Create a table with five columns and insert two rows of data into the table?

CREATE TABLE TEMPO1
(
NAME VARCHAR2(10 BYTE) NOT NULL,
AGE NUMBER NOT NULL,
CITY VARCHAR2(20 BYTE),
STATE VARCHAR2(20 BYTE),
COUNTRY VARCHAR2(20 BYTE)
)

SQL> insert into tempo1
2 (name,age,city,state,country)
3 values
4 ('Ram',32,'Bangalore','Karnataka','India');

SQL> insert into tempo1
2 (Name, Age, City, State, Country)
3 values
4 ('Sree',30,'Bellary','Karnataka','India');

9. Change the parameter file to pfile instead of spfile?

SQL> create pfile from spfile;
SQL> shutdown immediate;
SQL> startup pfile='D:\oracle\ora92\database\initprod.ora';

10. Create a passworfile for authentication from OS?

SESSION 3.

1. Change the database to use archivelog mode?
# add the following parameters
log_archive_start=TRUE
log_archive_max_processes=2
log_archive_dest_1="LOCATION=/demo/oracle/db/oradata/demo/arch
MANDATORY REOPEN"
log_archive_format=arch_%t_%s.arc

SQL> show parameter log_archive_start;

NAME TYPE VALUE
------------------------------------ ----------- --------------
log_archive_start boolean TRUE

if the value is FALSE then execute below command:

SQL> show parameter log_archive_max_processes;

NAME TYPE VALUE
------------------------------------ ----------- ---------------
log_archive_max_processes integer 2

SQL> show parameter log_archive_dest_1;

NAME TYPE VALUE
----------------------- ---------- -----------------
log_archive_dest_1 string LOCATION=D:\oracle\oradata\prod\archive


SQL> show parameter log_archive_format;

NAME TYPE VALUE
------------------------------------ ----------- --------------
log_archive_format string %t_%s.dbf



SQL> alter system set log_archive_start=TRUE scope=spfile;
SQL> alter system set log_archive_max_processes=2 scope=both;
SQL> alter system set log_archive_dest_1=”LOCATION=d:\oracle\oradata\prod\arch MANDATORY REOPEN” scope=both;
SQL> alter system set log_archive_format=”arch%s.arc” scope=spfile;


Then
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database open;

After the database is started check for archivelog mode.
SQL>select log_mode from v$database;

And

SQL>archive log list
2. Take a backup of control file to trace in a binary format ?

SQL> alter database backup
2 controlfile to trace as 'd:\ramakrishna\pract\PROD_controlfile.sql';
3. Configure the database to do adhoc hotbackup of database and archivelog’s through rman without a catalog?

1. Create a rman user ‘backup_admin/backup_admin’ with default tablespace as users and temporary tablespace as temp.

SQL>create user backup_admin identified as backup_admin
Default tablespace users temporary tablespace temp;

2. Give sysdba privileges to the above user.

SQL>conn / as sysdba
SQL>grant sysdba to backup_admin;

3. Connect to SQL session with user backup_admin and check.

SQL>conn backup_admin/backup_admin
SQL>select * from tab;

4. Connect to rman with user backup_admin and check for configuration.

C:\>rman target backup_admin/backup_admin@prod nocatalog

5. Check the default configuration of rman.
RMAN>show all;

6. Change below configuration in RMAN to take backup to hard disk.

RMAN> run {
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'd:\oracle\oradata\prod\%F';
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT 'd:\ramakrishna\pract\rman\backup_db_%d_S_%s_P_%p_T_%t' MAXPIECESIZE 1024M;
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT 'd:\ramakrishna\pract\rman\backup_db2_%d_S_%s_P_%p_T_%t' MAXPIECESIZE 1024M;
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
}

7. Make a note of DBID you are taking backup.

C:\>rman target backup_admin/backup_admin@prod nocatalog
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: PROD (DBID=5469274)
RMAN>

8. Backup the target database by running the commands as a script.

C:\>rman target backup_admin/backup_admin@prod nocatalog msglog rman_full_backup_db_20sep2006.log

RMAN>run {
# -----------------------------------------------------------
# The following RMAN commands are run each day.
# The steps are:
# - Re-start the database to perform crash recovery, in
# case the database is not currently open, and was not
# shut down consistently. The database is started in DBA
# mode so that normal users cannot connect.
# - Shut down with the IMMEDIATE option to close the
# database consistently.
# - Startup and mount the database.
# - Backup database.
# - Open database for normal operation.
# -----------------------------------------------------------
startup force dba;
shutdown immediate;
startup mount;
backup database plus archivelog delete input;
alter database open;
}
exit

9. Validate the backup taken in the previous step.
run {
restore database validate;
}

10. Also check for the consistency of restoring a tablespace.
run {
restore tablespace users validate;
}
4. Recover only a tablespace from the backup?
1. Connect to rman and check if the latest backup is present.

C:\>rman target backup_admin/backup_admin@prod nocatalog

RMAN>list backup summary;

RMAN>run {
restore database validate;
}

RMAN>run {
restore tablespace test01 validate;
}