Database Administration

EASY CONNECT

The easy connect naming method provides TCP/IP connectivity to databases without the

• # sqlplus /@:/
• # sqlplus sys/oracle@:1521/

STARTING AND STOPPING THE DATABASE CONTROL PROCESS

• # emctl start dbconsole
• # emctl stop dbconsole
• # emctl status dbconsole

STARTUP OPTIONST.Simmonds 2010

--The instance reads the initialization parameter file, allocates SGA memory, and starts the background processes

• SQL> startup nomount;  

--The instance opens the database control file, but does not open the datafiles 

• alter database mount;

--Instance opens the redo log files and datafiles for the database25

• SQL> alter database open; 
• SQL> startup;

SHUTTING DOWN A DATABASE INSTANCE I

• SQL> shutdown normal
• SQL> shutdown immediate
• SQL> shutdown abort
• SQL> shutdown transactional
• SQL> shutdown

CREATE USER

SQL> CREATE USER jsmith IDENTIFIED BY password

     DEFAULT TABLESPACE “USERS” 

     TEMPORARY TABLESPACE “TEMP” 

     PROFILE clerk ACCOUNT UNLOCK;

GRANTING SYSTEM PRIVILEGES

SQL> GRANT { system_privilege| role | ALL PRIVILEGES } 

     TO { user | role | PUBLIC } [, { user | role | PUBLIC }]

     IDENTIFIED BY password] 

     [WITH ADMIN OPTION]

GRANTING OBJECT PRIVILEGES

SQL> GRANT update (department_id, location_id)

     ON departments

     TO scott, manager

     WITH GRANT OPTION;

CONFIRMING PRIVILEGES GRANTED

Data Dictionary View

• ROLE_SYS_PRIVS                System privileges granted to roles
• ROLE_TAB_PRIVS               Table privileges granted to roles
• USER_ROLE_PRIVS             Roles accessible by the user
• USER_TAB_PRIVS_MADE  Object privileges granted on the user’s objects
• USER_TAB_PRIVS_RECD   Object privileges granted to the user
• USER_COL_PRIVS_MADE  Object privileges granted on the columns of the user’s objects
• USER_COL_PRIVS_RECD   Object privileges granted to the user on specific columns
• USER_SYS_PRIVS                System privileges granted to the user

CREATING ROLES

• SQL> CREATE ROLE manager IDENTIFIED BY mgr_password;
• SQL> SET ROLE manager IDENTIFIED BY mgr_password;

CREATING PROFILES

SQL> CREATE PROFILE app_user LIMIT 

     SESSIONS_PER_USER UNLIMITED 

     CPU_PER_SESSION UNLIMITED 

     CPU_PER_CALL 3000 

     CONNECT_TIME 45 

     LOGICAL_READS_PER_SESSION 

     DEFAULT LOGICAL_READS_PER_CALL 1000 

     PRIVATE_SGA 15K

     FAILED_LOGIN_ATTEMPTS 5

     PASSWORD_LIFE_TIME 60

     PASSWORD_REUSE_TIME 60

     PASSWORD_REUSE_MAX 5

     PASSWORD_LOCK_TIME 1/24;

SUPPLIED PASSWORD VERIFICATION FUNCTION

SQL> ALTER PROFILE DEFAULT LIMIT 

     PASSWORD_VERIFY_FUNCTION 

     verify_function_11g;

MANAGING DATABASE STORAGE STRUCTURES

CONTROL FILES DATA DICTIONARY VIEWS

V$DATABASE                         Displays database information from the control file

V$CONTROLFILE                   Lists the names of control files

V$CONTROLFILE_RECORD_SECTION       Displays information about control file record sections

V$PARAMETER         Names of control files as specified in the CONTROL_FILES initialization parameter

SQL> SELECT NAME FROM V$CONTROLFILE;

CREATING A TABLESPACE 

Permanent

      SQL> create tablespace ts_data

           datafile '/home/oracle/db1/data.dbf' 

           size 10M 

           autoextend on 

           maxsize 200M 

           extent management local 

           uniform  size 64K;

Temporary

      SQL> create temporary tablespace ts_temp

           tempfile'/home/oracle/db1/temp01.dbf' 

           size 5M 

           autoextendon;

Undo

     SQL> create undotablespace ts_undo

          datafile'/home/oracle/db1/undo.dbf '

          size 100M;

Managing Schema Objects&Managing Data

CREATING AND MODIFYING TABLES

SQL> CREATE TABLE hr.new_jobs (

     job_id NUMBER(5)CONSTRAINT jobid_id_pk PRIMARY KEY,

     job_title VARCHAR2(30)CONSTRAINT jib_title UNIQUE,

     min_salary NUMBER(6),

     max_salary NUMBER(6),

     CONSTRAINT fk_deptno FOREIGN KEY (job_title) REFERENCES

     job(job_title)

     )

     TABLESPACE users;

SQL> ALTER TABLE hr.new_jobs ADD bonus NUMBER(6);

Copy table structure and the data (only NOT NULL constraint will be copied into the new table)

SQL> CREATE TABLE my_jobs AS SELECT * FROM hr.new_jobs;

Truncate table

SQL> TRUNCATE TABLE hr.employees;

Disable Constraint 

SQL> ALTER TABLE empDISABLE CONSTRAINT emp_empno_pk CASCADE;

Add Constraint

SQL> ALTER TABLE dept_20

     ADD CONSTRAINT fk_empid_hiredate

     FOREIGN KEY (employee_id, hire_date)

     REFERENCES hr.job_history(employee_id, start_date)

     EXCEPTIONS INTO wrong_emp;

RESOLVING LOCK CONFLICTS WITH SQL

SQL> select sid, serial#, username from v$session 

     where sid in (select blocking_session from v$session)

SQL> alter system kill session '144,8982' immediate;

Flashback Technology

Flashback Drop

reverses the effects of a DROP TABLE operation

Flashback Table

rewinds tables to a specified point in time with a single statement

Flashback Database

rewinds an Oracle database to a previous time to correct problems caused by logical data corruptions or user errors

Flashback Query (Version Query, Transaction Query)

allows to query all data at a specified point in time, trieves all the versions of the rows that exist between two points in time

SQL> SELECT owner, original_name, object_name,type, ts_name, 

     droptime, related, space

     FROM dba_recyclebin

     WHERE can_undrop = 'YES';

SQL> FLASHBACK TABLE table_name

     TO BEFORE DROP [RENAME TO new_name];

SQL> FLASHBACK TABLE bonus TO SCN 6099208;

SQL> FLASHBACK TABLE bonus TO TIMESTAMP (SYSTIMESTAMP –INTERVAL '1' minute);

DROPTABLEPURGEcommand permanently drops a table and its dependent objects from the database, bypassing the Recycle Bin

SQL> DROP TABLE [PURGE];

FLASHBACK VERSIONS - PSEUDO COLUMNS

Flashback Version Query returns a table with a row for each version of the row that existed at any time during the specified time interval. Each row in the table includes pseudo columns of metadata about the row version

VERSIONS_STARTTIME, VERSIONS_STARTSCN

This pseudocolumnidentifies the time when the data first had the values reflected in the row version

VERSIONS_ENDTIME, VERSIONS_ENDSCN

SCN or TIMESTAMP when the row version expired

VERSIONS_XID

Transaction that created the version

VERSIONS_OPERATION

Operation that produced the version: I for insertion, D for deletion, or U for update

SQL> SELECT versions_startscn, versions_starttime, versions_endscn,         versions_endtime,versions_xid, versions_operation,last_name, salary

     FROM employees

     VERSIONS BETWEEN TIMESTAMP

     TO_TIMESTAMP('2008-12-18 14:00:00','YYYY-MM-DD HH24:MI:SS')

     AND TO_TIMESTAMP('2008-12-18 17:00:00','YYYY-MM-DD HH24:MI:SS')

     WHERE first_name= 'John'

CONFIGURING DATABASE FOR THE FLASHBACK

Before you can issue the command to Flashback, Database must be configured for archiving and FLASHBACK enabled

SQL> ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE FLASHBACK ON;

Database must be started in MOUNT mode

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> FLASHBACK DATABASE TO TIMESTAMP(SYSDATE-1/24); OR

SQL> FLASHBACK DATABASE TO SCN 53943;

To view changes the database can be opened in read-only mode.The database must be opened read/write with the RESETLOGS option when finished

SQL> ALTER DATABASE OPEN RESETLOGS;

Database Backup and Recovery

RECOVERY MANAGER

• Automatically diagnoses data failures
• Determines and presents appropriate repair options
• Performs repairs if requested by the user

SQL> rman target /
RMAN> backup database;
RMAN> configure controlfile autobackup on;


RMAN> list failure;
RMAN> advise failure;
RMAN> repair failure;

Moving Data between Systems

SQL*LOADER CONTROL FILE

Empl.ctl

LOAD DATAINFILE ’empl.dat’BADFILE ’empl.bad’DISCARDFILE ’empl.dsc’APPENDINTO TABLE empFIELDS TERMINATED BY ‘,’TRAILING NULLCOLS(empno integer external(4),last name,first name,email,deptno integer external(2),sal decimal external (5,2),hiredateSYSDATE));

INFO ABOUT EXTERNAL TABLES

DBA_EXTERNAL_TABLES
DBA_EXTERNAL_LOCATIONS
DBA_TABLES
ALL_EXTERNAL_TABLES
ALL_EXTERNAL_LOCATIONS
ALL_TABLES

DATA PUMP EXPORT / IMPORT

expdp admin/oracleFULL=yPARALLEL =2 TDIRECTORY=data_pump_dirDUMPFILE=HREXP01_U%.DMP, HREXP02_U%.DMP,FILESIZE=2GCOMRESSION=all

impdp admin/oracleFULL=yPARALLEL =2DIRECTORY=data_pump_dirDUMPFILE=HREXP01_U%.DMP,HREXP02_U%.DMP