Pages

Friday, February 11, 2011

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