Get documents about "Oracle Database Administration Memory Management Shared Pool Library Cache
Document Sample
Oracle Database Administration
Memory Management
SGA (Shared Memory)
Shared Pool Database Buffer Cache Redo Log Buffer Large Pool Java Pool
Library Cache Keep
Shared Sql Area Cycle
PL/SQL Proc. Default
Locks and other
Data Dict. Cache
Control Struc.
Sql Area
PGA (Non Shared Memory)
Stack Space Session Info Sort Area
Private SQL Area
Persistant Area Freed only when cursor is closed
Run Time Area For insert, update, delete statements freed after the statement has been executed. For
queries freed only after all rows are fetched or the query is cancelled.
Session Memory In MTS this is stored in SGA.
Sql work area Memory intensive operations.
SQL> show sga
“sga_max_size” parameter defınes the max size of the system global area. *_area_size
parameter cannot exceed this value
db_cache_size ‐‐db_keep_cache_size , db_recycle_cache_size
log_buffer ‐‐redo log buffer
shared_pool_size
large_pool_size ‐‐parallel_automatic_tuning=TRUE (init.ora)
‐‐effects export and import performance
java_pool_size ‐‐for java stored procedures
alter system set db_cache_advice=[ON|OFF];
select * from v$db_cache_advice;
-- memory advisories
select * from dba_views where view_name like '%ADVICE%'
v$java_pool_advice;
v$shared_pool_advice;
v$sga_target_advice;
v$pga_target_advice;
dba_hist_java_pool_advice
dba_hist_shared_pool_advice
dba_hist_sga_target_advice
dba_hist_pga_target_advice
select name,CEIL(value/1024/1024) MB from v$parameter where name in
('db_cache_size','shared_pool_size','large_pool_size',
1|Page
'java_pool_size','log_buffer');
alter system set sga_max_size=1024M;
alter system set db_cache_size=512M;
alter system set shared_pool_size=256M;
alter system set large_pool_size=64M;
alter system set java_pool_size=64M;
-- directly effects pga usage
alter system set open_cursors = 50;
-- automatically manage private global area for user sessions
alter system set work_area_size_policy = AUTO;
alter system set pga_aggregate_target = 1500M;
After setting work_area_size_policy to auto all *_area_size parameters dont take effect:
sort_area_size, hash_area_size, bitmap_merge_area_size, create_bitmap_area_size
related views:
v$sga
v$sgastat
v$pgastat
Connect as sysdba
# sqlplus /nolog
SQL> connect / as sysdba
# orapwd file=”filename” password=”password” entries=”user_count”
SQL> alter system set remote_login_passwordfile=[EXCLUSIVE|SHARED|NONE];
desc v$PWFILE_USERS;
grant sysdba to <user_name>;
grant sysoper to <user_name>;
-- database operations with sysdba
SQL> alter database <db_name> mount | open | open readonly | readwrite |
open exclusive;
alter system enable restricted session;
alter system kill session ‘integer1,integer2’ [IMMEDIATE];
-- int1:SID, int2:SERIAL#
-- select SID, SESSION# from v$session;
shutdown abort | immediate | transactional | normal;
show parameter db_cache_size;
col <col_name> format a30;
show parameter shared_pool_size;
alter system set shared_pool_size=256M scope= spfile | memory | both;
SQL> alter system enable restricted session;
startup restrict;
select logins from v$instance;
grant restricted session to <user_name>;
2|Page
Creating database from sqlplus
1‐ with filesystem storage
SQL> create database <db_name>
logfile
group1(‘/$home/..’) size 100M,
group2(‘/$home/../redo21.log’) size 100M
maxlogfiles 5
maxlogmembers 5
maxloghistory 1
maxdatafiles 100
maxinstances 1
datafile ‘/$home/oradata/u01system01.dbf’ size 325M
undo tablespace undotbs datafile ‘/$home../undotbs01.dbf’ size 200M
autoextend on next 512K maxsize unlimited
default temporary tablespace temp
character set ‘US7ASCII’
national character set ‘AL6UTF16’
set time_zone = ‘America/Newyork’
Archivelog | NoArchivelog
2‐ with oracle managed files storage
-- user should set the following parameteres
db_create_file_dest --db_create_online_log_dest_n
db_create_online_dest_1
db_create_online_dest_2
SQL> @cddba01.sql
Create database dba01;
Alter procedure <procedure name> compile;
Alter package <package name> compile;
Alter package <backage name> compile body;
--creates the data dictionary views
@?/rdbms/admin/sql.bsq;
@?/rdbms/admin/catalog.sql;
@?/rdbms/admin/catproc.sql;
Control the following views after database creation
v$logfile
v$controlfile
v$datafile
v$tempfile
Control the operating system variables for memory allocation and optimization
SHMMAX
SHMMNI
SHMSEG
control file management
-- moving control files to a new location
-- moving with spfile
SQL> alter system set control_files=’/$home/oradata/u01/control01.ctl’,
‘/$home/oradata/u02/control02.ctl’ scope=spfile;
shutdown immediate;
!cp $home/oradata/u01/control01.ctl $home/oradata/u02/control02.ctl
Startup;
-- moving with pfile
SQL> shutdown immediate;
3|Page
!cp $home/oradata/u01/control01.ctl $home/oradata/u02/control02.ctl
--Open the initSID.ora file and update the control_files variable
Startup;
-- backup controlfile
SQL> alter database backup controlfile to ‘<backup_controlfile_name>’;
alter database backup controlfile to trace;
alter database backup controlfile to trace as '<os_path>';
related views:
v$controlfile
Redo log file
SQL> Alter system set log_checkpoints_to_alert = TRUE;
--each checkpoint will be recorded in the alertSID.log file
alter system switch log file;
alter system checkpoint;
alter system set fast_start_mttr_target=600;
alter database add logfile group 3
(‘/data1/u01/redo3_1.log’,‘/data2/u01/redo3_2.log’)size = 100M;
alter database add logfile member ‘...’ to group 3;
alter database drop logfile group 3;
alter database drop logfile member ‘...’;
shutdown immediate;
alter database clear logfile ‘...’;
!cp ;
alter database rename file ‘..’ to ‘..’;
related views:
v$log
v$loghist
v$logfile
v$log_history
log miner
--@?/rdbms/admin/dbmslogmnrd.sql;
-- related views
SELECT * FROM V$LOGMNR_DICTIONARY;
SELECT * FROM V$LOGMNR_PARAMETERS;
--contents of the logs
SELECT * FROM V$LOGMNR_CONTENTS;
--which redo logs are being analyzed in the current logminer session
SELECT * FROM V$LOGMNR_LOGS;
-- minimal supplemental logging
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA
(PRIMARY KEY, UNIQUE INDEX) COLUMNS;
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;
ALTER TABLE scott.emp ADD SUPPLEMENTAL LOG GROUP
emp_parttime (empno, ename, deptno) ALWAYS;
ALTER TABLE scott.emp ADD SUPPLEMENTAL LOG GROUP
4|Page
emp_parttime (empno, ename, deptno);
ALTER TABLE scott.emp DROP SUPPLEMENTAL LOG GROUP emp_parttime;
-- to create all logminer objects in to the specified tablespace
EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('logmnrts$');
EXECUTE DBMS_LOGMNR_D.BUILD ('dictionary.ora', '/oracle/database/',
OPTIONS => DBMS_LOGMNR_D.STORE_IN_FLAT_FILE);
EXECUTE DBMS_LOGMNR_D.BUILD (OPTIONS=>DBMS_LOGMNR_D.STORE_IN_REDO_LOGS);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'log1orc1.ora',
OPTIONS => DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'log2orc1.ora',
OPTIONS => DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'log3orc1.ora',
OPTIONS => DBMS_LOGMNR.REMOVEFILE);
EXECUTE DBMS_LOGMNR.START_LOGMNR(
OPTIONS => DBMS_LOGMNR.DICT_FROM_REDO_LOGS);
EXECUTE DBMS_LOGMNR.START_LOGMNR (
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
-- For example, to see all the DDLs executed by user SYS, you could issue
the following query
SELECT USERNAME, SQL_REDO FROM V$LOGMNR_CONTENTS
WHERE USERNAME = 'SYS' AND OEPRATION = 'DDL';
-- filtering redo_log actions
EXECUTE DBMS_LOGMNR.START_LOGMNR(
OPTIONS => DBMS_LOGMNR.COMMITTED_DATA_ONLY);
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.SKIP_CORRUPTION);
EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME => '/oracle/dictionary.ora',
STARTTIME => TO_DATE('01-Jan-1998 08:30:00', 'DD-MON-YYYY HH:MI:SS'),
ENDTIME => TO_DATE('01-Jan-1998 08:45:00', 'DD-MON-YYYY HH:MI:SS'));
EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME => '/oracle/dictionary.ora',
STARTSCN => 100,
ENDSCN => 150);
EXECUTE DBMS_LOGMNR.END_LOGMNR;
-- a session example
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'log1orc1.ora',
OPTIONS => DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => 'log2orc1.ora',
OPTIONS => DBMS_LOGMNR.ADDFILE);
EXECUTE DBMS_LOGMNR.START_LOGMNR(
DICTFILENAME =>'/oracle/database/dictionary.ora');
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS =>
DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.COMMITTED_DATA_ONLY);
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS =>
DBMS_LOGMNR.DICT_FROM_REDO_LOGS + DBMS_LOGMNR.COMMITTED_DATA_ONLY);
EXECUTE DBMS_LOGMNR.START_LOGMNR(DICTFILENAME => 'orcldict.ora',
STARTTIME => TO_DATE('01-Jan-1998 08:30:00', 'DD-MON-YYYY HH:MI:SS'),
ENDTIME => TO_DATE('01-Jan-1998 08:45:00', 'DD-MON-YYYY HH:MI:SS'));
5|Page
tablespaces and datafiles
--
-- maximum number of datafiles can a database own.
--
select * from v$parameter where upper(name) like 'DB_FILES';
-- *************************************
-- creating a locally managed tablespace
-- *************************************
create tablespace ts_data
datafile 'C:\ts_data_01.dbf'
size 50m
autoextend on
next 5m
maxsize 200m | unlimited
extent management local
segment space management auto
logging
online
permanent|temporary;
/*
extent management dictionary
default storage
( initial 1m next 1m pctincrease 0 uniform size 512k);
*/
-- dropping a tablespace
drop tablespace ts_data including contents and datafiles;
-- altering tablespace
alter tablespace ts_data [read only|read write];
alter tablespace ts_data [offline|online] [immediate|normal];
alter database datafile '<datafile_name>' resize 200m;
alter database datafile '<datafile_name>' online | offline; --archivelog
alter database datafile '<datafile_name>' offline for drop; --noarchivelog
-- altering datafiles for autoextend properties
ALTER DATABASE DATAFILE '/home/oracle/oradata/pq1.dbf' AUTOEXTEND OFF;
ALTER DATABASE DATAFILE '/home/oracle/oradata/pq10.dbf'
AUTOEXTEND ON
NEXT 100M
MAXSIZE 20480M;
alter tablespace ts_data add datafile 'C:\ts_data_02.dbf' size 200m;
alter tablespace ts_data rename file
'C:\ts_data_02.dbf' to 'D:\ts_data_02.dbf';
alter tablespace ts_data drop datafile '<datafilename>';
alter tablespace ts_data coalesce;
-- *****************************
-- creating temporary tablespace
-- *****************************
create temporary tablespace ts_temp
tempfile 'C:\ts_temp_01.dbf'
size 10m
6|Page
extent management local;
-- altering for default temporary tablespace
alter database default temporary tablespase ts_temp;
-- checking systems default temporary tablespace
select * from database_properties;
alter tablespace ts_temp add tempfile 'C:\ts_temp_02.dbf' size 100m;
alter tablespace ts_temp drop tempfile '<tempfilename>';
alter database tempfile | datafile '<filename>' drop including datafiles;
/*
inittrans & maxtrans: initial and the maximum transaction that are
created in an index or a data block.
inittrans: default is 1 for data segment. Default is 2 for index
segment.
maxtrans: default 255.
pctfree: default %10.
pctused: default %40.
*/
select file_name, autoextensible from dba_data_files;
-- renaming files
alter tablespace user_data offline;
--!copy the files as you need
--dbms_file_transfer can also be used here
/*
dbms_file_transfer.copy_file
dbms_file_transfer.put_file
dbms_file_transfer.get_file
*/
alter database rename file
'C:\ts_data_02.dbf' to 'D:\ts_data_02.dbf';
alter tablespace ts_data rename file
'C:\ts_data_02.dbf' to 'D:\ts_data_02.dbf';
alter tablespace user_data online;
dba_extents
dba_segments
dba_data_files
dba_free_space
v$tablespace
dba_tablespaces
v$temp_extent_map
dba_users
v$sort_usage --join with v$session
undo management
optimal undo_retention parameter = actual undo size / (db_block_size/undo_block_per_sec)
-- **********************
-- manual undo management
-- **********************
7|Page
create rollback segment r01 tablespace rbs;
create public rollback_segment r01 tablespace rbs;
alter rollback segment r01 offline;
drop rollback segment r01;
-- *************************
-- automatic undo management
-- *************************
alter system set undo_management = auto;
alter system set undo_tablespace = <ts_name>;
alter system set undo_suppress_errors = true|false ;
alter system set undo_retention = 900; --seconds
select name, value from v$parameter where name like 'undo_management'
select name, value from v$parameter where name like 'undo_tablespace'
select name, value from v$parameter where name like 'undo_retention'
-- creating undo tablespace
create undo tablespace ts_undo datafile 'C:\ts_undo_01.dbf' size 20m;
alter tablespace ts_undo add datafile 'C:\ts_undo_02.dbf' size 40m;
alter system set undo_tablespace = ts_undo;
drop tablespace ts_undo;
alter tablespace ts_undo retention guarantee;
select retention, tablespace_name from dba_tablespaces;
select begin_time, end_time, tuned_undoretention from v$undostat;
-- calculating undo space
select
ceil(((UR * (UPS * DBS)) + (DBS * 24))/1024/1024) as MB
from
(select value as UR from v$parameter where name='undo_retention'),
(select (sum(undoblks)/sum(((end_time - begin_time) * 84600))) as UPS
from v$undostat),
(select value as DBS from v$parameter where name = 'db_block_size');
set transaction read only;
set transaction isolation level serializable; -- possible negative impact
on performance
-- Actual Undo Size
SELECT SUM(a.bytes) "UNDO_SIZE"
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#;
-- Undo Blocks per Second
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
"UNDO_BLOCK_PER_SEC"
FROM v$undostat;
-- DB Block Size
SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
8|Page
FROM v$parameter
WHERE name = 'db_block_size';
-- optimal undo retention
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size / (to_number(f.value) * g.undo_block_per_sec)))
"OPTIMAL UNDO RETENTION [Sec]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
( SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec FROM v$undostat ) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size';
-- optimal undo size
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE [MByte]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size';
-- undo consumer sessions
SELECT
TO_CHAR (s.SID) || ',' || TO_CHAR (s.serial#) sid_serial,
NVL (s.username, 'None') orauser,
s.program,
r.NAME undoseg,
t.used_ublk * TO_NUMBER (x.VALUE) / 1024 || 'K' "Undo",
t1.tablespace_name
FROM
SYS.v_$rollname r,
9|Page
SYS.v_$session s,
SYS.v_$transaction t,
SYS.v_$parameter x,
dba_rollback_segs t1
WHERE
s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.NAME = 'db_block_size'
AND t1.segment_id = r.usn
AND t1.tablespace_name = 'UNDOTBS1';
related views:
dba_rollback_segs
dba_undo_extents
v$rollname
v$rollstat
v$undostat
dba_hist_undostat
v$session
v$transaction
Oracle Managed Files
init parameters
DB_CREATE_FILE_DEST -- datafiles tempfiles
DB_CREATE_ONLINE_LOG_DEST_n -- 1 <= n <= 5 -- redologs and controlfiles
DB_RECOVERY_FILE_DEST -- archivedlogs, rman backups, flashback logs
DB_RECOVERY_FILE_DEST_SIZE = 20G
DB_UNIQUE_NAME = <db_name>
<destination_location>/<db_unique_name>/<datafile>/o1_mf_%t_%u_.dbf
examples
CREATE DATABASE sample;
-- system and sysaux datafiles will be 100MB and autoextend on
-- redo log files will be two members 100MB
-- undo file will be 10M with autoextend on if undo_management=AUTO
CREATE DATABASE sample DATAFILE SIZE 400M
SYSAUX DATAFILE SIZE 200M
DEFAULT TEMPORARY TABLESPACE dflt_ts TEMPFILE SIZE 10M
UNDO TABLESPACE undo_ts DATAFILE SIZE 10M;
CREATE DATABASE sample
DEFAULT TEMPORARY TABLESPACE temp;
/*
* datafiles will have the specified properties
* name of the datafiles will be oracle managed
*/
10 | P a g e
ALTER SYSTEM SET DB_CREATE_FILE_DEST = '/u01/oradata';
CREATE TABLESPACE tbs_1;
CREATE TABLESPACE tbs_2 DATAFILE SIZE 400M;
CREATE TABLESPACE tbs_3 DATAFILE AUTOEXTEND ON MAXSIZE 800M;
CREATE TABLESPACE tbs_4 DATAFILE SIZE 200M SIZE 200M;
CREATE UNDO TABLESPACE undotbs_1;
ALTER TABLESPACE tbs_1 ADD DATAFILE AUTOEXTEND ON MAXSIZE 800M;
CREATE TEMPORARY TABLESPACE temptbs_1;
ALTER TABLESPACE TBS_1 ADD TEMPFILE;
ALTER TABLESPACE tbs_1 ADD DATAFILE;
DROP TABLESPACE tbs_1;
-- Creating Controlfiles
-- NORESETLOGS EXAMPLE
CREATE CONTROLFILE
DATABASE sample
LOGFILE
GROUP 1 ('/u01/oradata/SAMPLE/onlinelog/o1_mf_1_o220rtt9_.log',
'/u02/oradata/SAMPLE/onlinelog/o1_mf_1_v2o0b2i3_.log')
SIZE 100M,
GROUP 2 ('/u01/oradata/SAMPLE/onlinelog/o1_mf_2_p22056iw_.log',
'/u02/oradata/SAMPLE/onlinelog/o1_mf_2_p02rcyg3_.log')
SIZE 100M
NORESETLOGS
DATAFILE '/u01/oradata/SAMPLE/datafile/o1_mf_system_xu34ybm2_.dbf'
SIZE 100M,
'/u01/oradata/SAMPLE/datafile/o1_mf_sysaux_aawbmz51_.dbf'
SIZE 100M,
'/u01/oradata/SAMPLE/datafile/o1_mf_sys_undotbs_apqbmz51_.dbf'
SIZE 100M
MAXLOGFILES 5
MAXLOGHISTORY 100
MAXDATAFILES 10
MAXINSTANCES 2
ARCHIVELOG;
-- RESETLOGS EXAMPLE
-- Some combination of DB_CREATE_FILE_DEST, DB_RECOVERY_FILE_DEST, and
-- DB_CREATE_ONLINE_LOG_DEST_n or must be set.
CREATE CONTROLFILE
DATABASE sample
RESETLOGS
DATAFILE '/u01/oradata/SAMPLE/datafile/o1_mf_system_aawbmz51_.dbf',
'/u01/oradata/SAMPLE/datafile/o1_mf_sysaux_axybmz51_.dbf',
'/u01/oradata/SAMPLE/datafile/o1_mf_sys_undotbs_azzbmz51_.dbf'
SIZE 100M
MAXLOGFILES 5
MAXLOGHISTORY 100
MAXDATAFILES 10
MAXINSTANCES 2
ARCHIVELOG;
-- Adding Redo Logs
DB_CREATE_ONLINE_LOG_DEST_1 = '/u01/oradata'
DB_CREATE_ONLINE_LOG_DEST_2 = '/u02/oradata'
11 | P a g e
ALTER DATABASE ADD LOGFILE;
-- Creating Archived Logs Using Oracle-Managed Files
DB_RECOVERY_FILE_DEST_SIZE = 20G
DB_RECOVERY_FILE_DEST = '/u01/oradata'
LOG_ARCHIVE_DEST_1 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST'
ASM
init parameters of ASM instance
INSTANCE_TYPE -- must be ASM
ASM_POWER_LIMIT -- [1-10]
ASM_DISKSTRING
ASM_DISKGROUPS
Css should be up and running for synchronizing db instance and asm instance
-- crsctl check cssd
-- insufficient number of failure groups or disks running out of disk space
select redundancy_lowered from v$asm_file;
-- free_mb – required_mirror_free_mb = 2 * usable_file_mb
select required_mirror_free_mb, usable_file_mb from v$asm_diskgroup;
creating diskgroup
-- disk names are available in v$asm_disk view
-- set ORACLE_SID and ORACLE_HOME for asm instance
sqlplus /nolog
SQL> connect / as sysdba
startup nomount;
create diskgroup <disk_group_name> normal|high|external redundancy [force|noforce]
[failgroup <failgroupname>] DISK
‘<disk name>’ [NAME <diskname>],
‘<disk name>’
[failgroup <failgroupname> DISK
‘<disk name>’,
‘<disk name>’]
alter diskgroup <disk_group_name> add disk
‘disk_name’ NAME diska1,
‘disk_name’ NAME diska2,
‘disk_name[5678]’,
‘disk_name*’ [FORCE]
[REBALANCE POWER 5 WAIT|NOWAIT]
alter diskgroup <disk_group_name> drop disk <disk_name>
alter diskgroup <disk_group_name> resize disks [in failgroup
<failgroupname> [size 100M]]
alter diskgroup <disk_group_name> rebalance [WAIT|NOWAIT] [POWER n];
select * from v$asm_operation;
alter diskgroup <disk_group_name>|ALL MOUNT | DISMOUNT;
alter diskgroup <disk_group_name> check [REPAIR|NOREPAIR] all;
drop diskgroup <disk_group_name> INCLUDING | EXCLUDING contents;
alter diskgroup <disk_group_name> add directory
‘+<disk_group_name>/<dir_name>/..’;
alter diskgroup <disk_group_name> rename directory ‘<dir1>’ to ‘<dir2>’;
alter diskgroup <disk_group_name> drop directory ‘<dir_name>’;
12 | P a g e
alter diskgroup <disk_group_name> add alias ‘..’ FOR ‘..’;
select * from v$asm_alias;
alter diskgroup <disk_group_name> drop file ‘..’;
select software_version, compatible_version from v$asm_client;
‐‐ fully qualified asm filename
+group/dbname/file_type/file_type_tag.file.incarnation
+dgroup2/sample/controlfile/Current.256.541956473
‐‐ numeric asm filename
+group.file.incarnation
+dgroup2.257.541956473
‐‐ incomplete ASM filenames
‐‐ asm creates the unique filename
alter system set db_create_file_dest=’+diskgroup1’;
‐‐ if you want asm to be default destination set the following init parameters
DB_CREATE_FILE_DEST
DB_CREATE_ONLINE_LOG_DEST_n
DB_RECOVERY_FILE_DEST
CONTROL_FILES
LOG_ARCHIVE_DEST_n
LOG_ARCHIVE_DEST
STANDBY_ARCHIVE_DEST
LARGE_POOL_SIZE – minimum 1mb
‐‐Using an ASM Filename in a SQL Statement: Example
CREATE TABLESPACE tspace2 DATAFILE '+dgroup2' SIZE 200M AUTOEXTEND ON;
‐‐Creating a Database in ASM
DB_CREATE_FILE_DEST = '+dgroup1'
DB_RECOVERY_FILE_DEST = '+dgroup2'
DB_RECOVERY_FILE_DEST_SIZE = 10G
CREATE DATABASE sample;
‐‐Creating a Tablespace in ASM
DB_CREATE_FILE_DEST = '+dgroup2'
CREATE TABLESPACE tspace2;
‐‐the file is not an Oracle‐managed file
CREATE UNDO TABLESPACE myundo
DATAFILE '+dgroup3(my_undo_template)/myfiles/my_undo_ts' SIZE 200M;
ALTER DISKGROUP dgroup3 DROP FILE '+dgroup3/myfiles/my_undo_ts';
‐‐Adding New Redo Log Files
DB_CREATE_ONLINE_LOG_DEST_1 = '+dgroup1'
DB_CREATE_ONLINE_LOG_DEST_2 = '+dgroup2'
ALTER DATABASE ADD LOGFILE;
‐‐Creating a Control File in ASM
13 | P a g e
‐‐noresetlogs example, script is from “alter database backup controlfile to trace”
CREATE CONTROLFILE REUSE DATABASE "SAMPLE" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 (
'+DGROUP1/db/onlinelog/group_1.258.541956457',
'+DGROUP2/db/onlinelog/group_1.256.541956473'
) SIZE 100M,
GROUP 2 (
'+DGROUP1/db/onlinelog/group_2.257.541956477',
'+DGROUP2/db/onlinelog/group_2.258.541956487'
) SIZE 100M
DATAFILE
'+DGROUP1/db/datafile/system.260.541956497',
'+DGROUP1/db/datafile/sysaux.259.541956511'
CHARACTER SET US7ASCII
;
‐‐resetlogs example
CREATE CONTROLFILE REUSE DATABASE "SAMPLE" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 (
'+DGROUP1',
'+DGROUP2'
) SIZE 100M,
GROUP 2 (
'+DGROUP1',
'+DGROUP2'
) SIZE 100M
DATAFILE
'+DGROUP1/db/datafile/system.260.541956497',
'+DGROUP1/db/datafile/sysaux.259.541956511'
CHARACTER SET US7ASCII
;
‐‐ system views
V$ASM_DISKGROUP
V$ASM_DISK
V$ASM_DISKGROUP_STAT
V$ASM_DISK_STAT
V$ASM_FILE
V$ASM_TEMPLATE
V$ASM_ALIAS
V$ASM_OPERATION
V$ASM_CLIENT
Table Partititions:
14 | P a g e
Range partition:
The example below creates a table of four partitions, one for each quarter's sales. The
columns sale_year, sale_month, and sale_day are the partitioning columns, while their
values constitute a specific row's partitioning key. The VALUES LESS THAN clause determines
the partition bound: rows with partitioning key values that compare less than the ordered
list of values specified by the clause are stored in the partition. Each partition is given a
name (sales_q1, sales_q2, ...), and each partition is contained in a separate tablespace (tsa,
tsb, ...).
CREATE TABLE sales
( invoice_no NUMBER,
sale_year INT NOT NULL,
sale_month INT NOT NULL,
sale_day INT NOT NULL )
PARTITION BY RANGE (sale_year, sale_month, sale_day)
( PARTITION sales_q1 VALUES LESS THAN (1999, 04, 01) TABLESPACE tsa,
PARTITION sales_q2 VALUES LESS THAN (1999, 07, 01) TABLESPACE tsb,
PARTITION sales_q3 VALUES LESS THAN (1999, 10, 01) TABLESPACE tsc,
PARTITION sales_q4 VALUES LESS THAN (2000, 01, 01) TABLESPACE tsd );
ALTER TABLE sales
ADD PARTITION jan96 VALUES LESS THAN ( '01-FEB-1999' )
TABLESPACE tsx;
ALTER TABLE sales DROP PARTITION dec98;
ALTER INDEX sales_area_ix REBUILD; (if global index exists)
DELETE FROM sales WHERE TRANSID < 10000;
ALTER TABLE sales DROP PARTITION dec98;
ALTER TABLE sales DROP PARTITION dec98 UPDATE GLOBAL INDEXES;
ALTER TABLE four_seasons MERGE PARTITIONS quarter_one, quarter_two INTO
PARTITION quarter_two;
CREATE INDEX i_four_seasons_l ON four_seasons ( one,two )
LOCAL (
PARTITION i_quarter_one TABLESPACE i_quarter_one,
PARTITION i_quarter_two TABLESPACE i_quarter_two,
PARTITION i_quarter_three TABLESPACE i_quarter_three,
PARTITION i_quarter_four TABLESPACE i_quarter_four
);
ALTER TABLE four_seasons MODIFY PARTITION quarter_two REBUILD UNUSABLE
LOCAL INDEXES;
ALTER TABLE four_seasons TRUNCATE PARTITION quartes_one;
Dropping index partitions:
ALTER INDEX npr DROP PARTITION P1;
ALTER INDEX npr REBUILD PARTITION P2;
Hash Partition:
The following example creates a hash‐partitioned table. The partitioning column is id, four
partitions are created and assigned system generated names, and they are placed in four
named tablespaces (gear1, gear2, ...).
15 | P a g e
CREATE TABLE scubagear
(id NUMBER,
name VARCHAR2 (60))
PARTITION BY HASH (id)
PARTITIONS 4
STORE IN (gear1, gear2, gear3, gear4);
ALTER TABLE scubagear ADD PARTITION p_named TABLESPACE gear5;
List Partition:
The following example creates a list‐partitioned table. It creates table q1_sales_by_region
which is partitioned by regions consisting of groups of states.
CREATE TABLE q1_sales_by_region
(deptno number,
deptname varchar2(20),
quarterly_sales number(10, 2),
state varchar2(2))
PARTITION BY LIST (state)
(PARTITION q1_northwest VALUES ('OR', 'WA'),
PARTITION q1_southwest VALUES ('AZ', 'UT', 'NM'),
PARTITION q1_northeast VALUES ('NY', 'VM', 'NJ'),
PARTITION q1_southeast VALUES ('FL', 'GA'),
PARTITION q1_northcentral VALUES ('SD', 'WI'),
PARTITION q1_southcentral VALUES ('OK', 'TX'));
ALTER TABLE q1_sales_by_region
ADD PARTITION q1_nonmainland VALUES ('HI', 'PR')
STORAGE (INITIAL 20K NEXT 20K) TABLESPACE tbs_3
NOLOGGING;
ALTER TABLE q1_sales_by_region
MERGE PARTITIONS q1_northcentral, q1_southcentral
INTO PARTITION q1_central
PCTFREE 50 STORAGE(MAXEXTENTS 20);
Range‐Hash Partition:
The following statement creates a range‐hash partitioned table. In this example, three range
partitions are created, each containing eight subpartitions. Because the subpartitions are not
named, system generated names are assigned, but the STORE IN clause distributes them
across the 4 specified tablespaces (ts1, ...,ts4).
CREATE TABLE scubagear (equipno NUMBER, equipname VARCHAR(32), price
NUMBER)
PARTITION BY RANGE (equipno) SUBPARTITION BY HASH(equipname)
SUBPARTITIONS 8 STORE IN (ts1, ts2, ts3, ts4)
(PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (2000),
PARTITION p3 VALUES LESS THAN (MAXVALUE));
Range‐List Partition:
The following example illustrates how range‐list partitioning might be used. The example
tracks sales data of products by quarters and within each quarter, groups it by specified
states.
CREATE TABLE quarterly_regional_sales
16 | P a g e
(deptno number, item_no varchar2(20),
txn_date date, txn_amount number, state varchar2(2))
TABLESPACE ts4
PARTITION BY RANGE (txn_date)
SUBPARTITION BY LIST (state)
(PARTITION q1_1999 VALUES LESS THAN (TO_DATE('1-APR-1999','DD-MON-
YYYY'))
(SUBPARTITION q1_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q1_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q1_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q1_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q1_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q1_1999_southcentral VALUES ('OK', 'TX')
),
PARTITION q2_1999 VALUES LESS THAN ( TO_DATE('1-JUL-1999','DD-MON-
YYYY'))
(SUBPARTITION q2_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q2_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q2_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q2_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q2_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q2_1999_southcentral VALUES ('OK', 'TX')
),
PARTITION q3_1999 VALUES LESS THAN (TO_DATE('1-OCT-1999','DD-MON-
YYYY'))
(SUBPARTITION q3_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q3_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q3_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q3_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q3_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q3_1999_southcentral VALUES ('OK', 'TX')
),
PARTITION q4_1999 VALUES LESS THAN ( TO_DATE('1-JAN-2000','DD-MON-
YYYY'))
(SUBPARTITION q4_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q4_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q4_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q4_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q4_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q4_1999_southcentral VALUES ('OK', 'TX')
)
);
ALTER TABLE quarterly_regional_sales
MODIFY SUBPARTITION q1_1999_southeast
ADD VALUES ('KS');
ALTER TABLE quarterly_regional_sales
MODIFY SUBPARTITION q1_1999_southeast
DROP VALUES ('KS');
Parallel Server
Parallel Hints
Select /*+PRALLEL(<tb_name>, 8|DEFAULT)*/
/*+NOPARALLEL(<tb_name>)*/
/*+PARALLEL_INDEX(<tb_name>, <ind_name>,3,2)*/
/*+NOPARALLEL_INDEX*/
17 | P a g e
1- alter table <tb_name> parallel (degree 8)
2- select /*+PARALLEL(<tb_name>,<degree>) from <tb_name>
PARALLEL_ADAPTIVE_MULTI_USER
(user number is high: set up; degree level up: set down)
Parallel DML|DDL:
Alter session [enable|disable] parallel [dml|ddl];
İnsert /*+PARALLEL(emp_big,4,1)*/ into <table_name>
Parallel DDL:
Create table <tb_name> parallel (degree 8)
Create index <ind_name> on <tb_name> (<col_name>).... parallel (degree 8)
Parallel Loading:
Sqlldr direct=true parallel=true
Parallel Recovery:
Recover tablespace <tbs_name> parallel (degree [8|DEFAULT])
Parallel Replication:
DBMS_DEFER_SYS.SCHEDULE_PUSH
(
DESINATION -> ‘par1.world’,
INTERVAL -> ‘sysdate+1/24,
NEXT_DATE -> ‘sysdate+1/24’,
PARALLELISM -> 6
);
Alter session force parellel DDL parallel 5;
degree of parallelism ‐‐number of parallel execution servers
‐ A PARALLEL clause in a statement
‐ The PARALLEL clause that was used when the object was created or altered
‐ A parallel hint inserted into the statement
‐ A default determined by Oracle
Parallel execution can be tuned for you automatically by setting the initialization parameter;
PARALLEL_AUTOMATIC_TUNING = TRUE
Alter session ENABLE | DISABLE PARALLEL DDL | DML ;
Alter session force PARALLEL DDL | DML PARALLEL 5;
Alter system set parallel_max_servers = <degree of max parallism>;
18 | P a g e
***** *****
***** *****
Extended rowid needs 10 bytes of storage on disk, is displayed by 18 chars.
Data object number //table,index //32 bits
Relative file number //unique for each file //10bits
Block number //position of the block //22 bits
Row number //position of the row //16 bits
SQL> select department_id, row_id from hr.departments;
Create table hr.employees (
Employee_id number(6),
First_name varchar2(20),
Last_name varchar2(25),
Hire_date date default sysdate)
[Storage(
Initial 200k,
Next 200k,
Pctincrease 0,
Minextents 1,
Maxextents 5)
Tablespace data];
Create temprary table hr.employees_temp as select * from hr.employees;
Compute pctfree and pctused values
Pct used: [(Average row size – initial row size) * 100] / Average row size
Pct used: [100 – pct free – (average row size * 100)] / Available data space
SQL> Alter table hr.employees pctfree 30 pctused 50 storage(
Next 500k
Minextents 2
Maxextents 100);
Alter table hr.employees allocate extent (size 500k datafile ‘/disk3/data1.dbf’);
Alter table hr.employees move tablesapce data1;
Truncate table hr.employees;
Drop table hr.employees cascade constrains;
Alter table hr.employees drop column comments cascade constrains checkpoint
1000;
Alter table hr.employees set unusued column comments cascade constrains;
Alter table hr.employees drop unusued columns checkpoint 1000;
Dba_tables
Dba_objects
19 | P a g e
indexes
logical
unique
nonunique
concetaneted
function based
physical
partioned
B‐tree
Bitmap
SQL> create index hr.employees_last_name_idx on hr.employees(last_name)
Pctfree 30
Storage (
Initial 200k next 200k
Pctincrease 0
Maxextents 50
Tablespace indx);
Create bitmap index orders_region_id_idx on orders(region_id)
Pctfree 30
Storage (
İnitial 200k next 200k
Pctincrease 0
Maxextents 50
Tablesapce indx);
Alter index orders_region_id_idx allocate extent (size 200k datafile ‘/disk1/...’);
Alter index orders_region_id_idx deallocate unused;
Alter index orders_region_id_idx rebuild tablespace indx02; //move index to a
different tablespace
Alter index orders_region_id_idx rebuild online;
Alter index orders_region_id_idx coalesce;
Analyze index orders_region_id_idx validate structure; //query index_stats
Drop index hr.employees_name_index;
Alter index hr.dept_id_idx monitoring usage nomonitoring; //query v$object_usage
Constrains
- not null
- uniwue
- primary key
20 | P a g e
- foreign key
- check
States of constrains
- disable novalidate
- disable validate
- enable novalidate
- enable validate
SQL> create table hr.employees (
Id number(7) constraint employee_id_pk primary key deferrable using index,
Last_name varchar2(25) constraint employee_last_name_nn not null);
Alter table hr.employee enable validate constraint emp_dept_fk;
Dba_constraints
Dba_objects
User and password management
To enable password management, run utlpwdmg.sql as sys user.
Set the following parameters
Failed_login_attempts
Password_lock_time
Password_life_time
Password_Grace_time
Password_reuse_time
Password_reuse_max
Password_verify_function //this function must be created in sys schema and must have the
following specification
function_name ( userid_parameter in varchar2(30),
password_parameter in varchar2(30),
old_password_parameter in varchar2(30)) return boolean;
SQL> create profile grace_s [LIMIT] failed_login_attemps 3
Password_lock_time unlimitied
...
alter profile ...
drop profile <profile_name> [cascade];
alter system set resouce_limit=true; //cpu_per_session
sessions_per_user
connect _time
idle_time
21 | P a g e
logical_reads_per_session
private_sga //for shared server only
create user aaron identified by soccer
default tablespace data
default temporary tablesapce temp
quota 15m on data
quota 10m on users
password expire;
dba_users
dba_profiles
dba_ts_quotas
SQL> Alter system set os_authent_prefix = [OPS$] //default
Alter system set remote_os_authen = true|false
Create user ops$user identified by <password>;
Alter|drop <user name> [cascade];
Grant create session to emi [with admin option];
Revoke create table from emi;
Grant update on emi.customers to jeff with grant option;
Grant execute on dbms_output to jeff;
Query the following tables
Dba_sys_privs
Session_privs
Dba_tab_privs
Dba_col_privs
Logic of roles
SQL> create role ol_clerk;
Create role hr_clerk identified by bonus;
Grant ol_cleck to scott;
Grant hr_clerk to hr_manager [with admin option];
Show parameter max_enabled_roles;
Alter user scott default role all [except hr_clerk];
Drop role <role name;>
Some priviliges:
Connect
22 | P a g e
Resource
Dba
Exp_full_database
Imp_full_database
Delete_catalog_online
Execute_catalog_role
Select_catalog_role
SQL> Audit <table name>;
Audit create any trigger;
Audit select on emi.orders;
Query the following tables:
All_def_audit_opts
Dba_stmt_audit_opts
Dba_priv_audit_opts
Dba_obj_audit_opts
Dba_audit_trail
Dba_audit_exists
Dba_audit_object
Dba_audit_session
Dba_audit_statement
Sql loader
Insert /* +append */ into schema.table [[NO]LOGGING] sub_query;
SQL> insert /* +append */ into emp nologging select * from t_employees; //serial direct
load
Commit;
Alter session enable parallel dml;
Insert /* +parallel (hr.employees,2)*/ into hr.employees nologging
Select * from hr.old_employees; //parallel execution
$ sqlldr hr/hr \
control=case1.ctl \
log=case1.log
direct=y
control file
load data
infile ‘sample.dat’
badfile ‘sample.bad’
23 | P a g e
discardfile ‘sample.dsc’
append [insert] into table emp when (57)=’.’
Trailing nullcols
(hire_date sysdate,
dept_no position (1:2) integer external (3) nullif dept_no = blanks,
job position (7:14) char terminated by whitespace nullif job=blanks “upper(:job)”
sal position (51) char terminated by whitespace “tonumber (:sal,’$99.999.999’)”
comm integer external unclosed by ‘(‘and ‘%’ “:comm * 100”)
‐‐comment
three types of sql loading
- fixed
load data
infile ‘...dat’ ‘fix 10’
into table example fields terminated by ‘,’ (col1,col2)
- variable
infile ‘...dat’ ‘var 3’
into table example fields terminated by ‘,’ optionally enclosed by ‘”’
(col1 char5, col2 char(7))
dat file
009hello,cd;
010world,im,
012my,name is,
- stream
load data
infile ‘example.dat’ “str ‘\n’”
into table example fields terminated by ‘,’ optionally eclosed by ‘”’
(col1 char5, col2 char(7))
dat file
hello,world; /
james,bond; /
BACKUP and RECOVERY
Lsnrctl services [listener01]
Mean_time_between_failures //should be bigger in value
Mean_time_to_recover //should be smaller in value
Problem Solution
Statement failure Change the user quota (alter user)
24 | P a g e
‐logic error Add filespace to the tablespace
‐insufficient privileges
Process failure Pmon
‐user session abnormally terminated
User errors Recover from a backup
‐drop table
‐truncate table
‐delete...commit
Instance failure Restart the instance
v$sgastat
- pool
- name
- bytes
v$log
- group_number
- log_sequence_number
- size_of_the_group
- number_of_members
v$logfile
- name
- status
- group
large pool
- backup restore operations
- I/O server processes
- Session memory fro shared users
- Large_pool_size (approximately 2gb)
Rman
- dbwr_io_slaves (integer)
- backup_tape_io_slaves (true|false)
SQL> alter system switch logfile;
Alter database db_name rename file ‘...’ to ‘...’;
Fast_start_mttr_target: expected mttr specified in seconds amount of the time that has
passed since the incremental checkpoint at the position where
the last write to redo log file occured.
Log_checkpoint_interval: number of redo log file blocks that can exist between an
incremental checkpoint and the last block written to the redo
log.
v$instance recovery
25 | P a g e
‐recovery_estimated_ios: number of dirty buffers in the buffer cache
‐actual_redo_blks: current actual number of redo blocks required for recovery
‐taret_redo_blks: current number of redo blocks that must be processed for
recıvery
‐log_file_size_redo_blks: current number of redo blocks required to guarantee that
a log switch doesnt occur before checkpoint.
‐log_chkpt_timeout_redo_blks:
‐estimated_mttr: current estimated mean time to recover (mttr). Based on the
number of dirty buffers and redo log blocks.
‐ckpt_block_writes: number of blocks written by checkpoint writes.
v$fast_start_servers
v$fast_start_transactions
rolling forward phase
- set recovery_parallelism <integer>
- use parallel clause in the recover database statement
rolling back phase
‐ set fast_start_parallel_rollback [false | low | high] //low is default
user managed backup and recovery
offline backup – consistant whole database setup
SQL> shutdown immediate
!cp <files> /backup/... //control file, datafile, redo log file, password file,
parameter file
startup open
online backup – the database should be in archive log mode
SQL> alter tablespace users begin backup
!copy the datafiles
alter tablespace users end backup
alter system archivelog current //archive the unarchived redologs so that the
redo
required to recover the tablespace backup is
achieved
query the following views
v$backup //file,status,change,time
v$datafile_header
26 | P a g e
SQL> alter database end backup;
Alter database datafile ‘...’ end backup;
Alter tablespace <tablespace name> read only;
!backup the datafiles
Alter tablespace <tablespace name> read write;
Logging and nologging
Set database to nologging for faster data loading
After this you should backup the datafiles
SQL> Alter database backup controlfile to ‘control01.bck’;
Alter database backup controlfile to trace;
Obtain database information by quering the following views
v$datafile
v$controlfile
v$logfile
dba_datafiles
user managed recovery
- time based
- cancel based
- change based
recovery steps
1‐ damaged files are restored from backup
2‐ changes from archived redo logs or online redo logs are applied if necessary
3‐ the database may now contain commited and uncommited changes
4‐ the undo block are used to rollback any uncommited changes
5‐ the database is now in recoverd state
recovery in noarchivelog mode
1‐ restore all datafiles even one of them needs recovery
2‐ shutdown the instance
3‐ perform cancel based recovery
4‐ open database with resetlogs
SQL> shutdown immediate
!cp ‘...’ ‘...’
recover database until cancel using backup control file
cancel
alter database open resetlogs
recovery in archivelog mode
1‐ query the v$recover_file, v$archived_log, v$recovery_log
27 | P a g e
2‐ recover database //mounted database
3‐ recover datafile ‘...’ //mounted database
4‐ recover tablespace users //open database
5‐ recover datafile ‘...’ //open database
SQL> shutdown abort
Startup mount
Recover database //recover datafile ‘...’
Alter database open
!cp ‘...’ ‘...’
alter database rename file ‘...’ to ‘...’
startup mount
alter database datafile ‘...’ offline
alter database open
restore datafile
alter database rename file ‘...’ to ‘...’
alter database recover
recover datafile ‘...’ //recover tablespace <tablespace name>
alter database datafile ‘...’ online;
alter tablespace user_data offline immediate;
alter database create datafile ‘...’ [as] ‘...’;
alter database recover;
alter tablespace table_data online;
create controlfile;
recover database using backup controlfile;
1‐ shutdown and backup the database
2‐ restore all datafiles, dont restore controlfile, redo logs, password file and parameter
file
3‐ mount database
4‐ recover datafiles
5‐ open database with resetlogs
6‐ perform a closed database backup
SQL> Recover database until cancel
Recover [automatic] database until time ‘2001‐03‐04:14:22:03’
Time based database recovery
1‐ shutdown and backup the database
2‐ restore all datafiles //may need to recover archivelogs
3‐ mount database
4‐ recover database until time
5‐ open with resetlogs
6‐ backup the database
28 | P a g e
cancel based database recovery
1‐ redo logs are not multiplexed
one of the redo logs is missing
the missing redo log is not archived
2‐ shutdown database
3‐ restore all datafiles from backup
4‐ mount the database
5‐ recover database until cancel
6‐ open database with resetlogs
7‐ backup database
loss of current redo log files
- attempt to open database
- find the current log sequence number //select * from v$log
- recover database until cancel
- drop and recreate log files if necessary
- open database with resetlogs
- perform whole database backup
SQL> alter database clear unarchived logfile group 2;
import and export
export utility
- table mode
- user mode
- tablespace mode
- database mode
$ exp hr/hr tables=employees, departments, ... rows=y file=expdat.dmp
exp system/manager owner=hr direct=y file=expdat.dmp
exp \’username/password as sysdba\’ transport_tablespace=y tablespaces=ts_emp
log=emp.log
export parameters
- buffer
- compress (y)
- file (expdat.dmp)
- full (n)
- help
- indexes (y)
- log (none)
- owner (none)
- rows (y)
- tables (none)
- tablespaces (none)
- userid (none) user/password
- parfile (none)
import utility
29 | P a g e
- table
- user
- tablespace
- full database
$ imp hr/hr tables=employees, departments rows=y file=expdat.dmp
imp system/manager fromuser=hr file=expdat.dmp
imp \’user/password as sysdba\’ transport_tablespace=y tablespaces=ts_employees
import parameters
- buffer
- datafiles (none)
- destroy (n)
- file (expdat.dmp)
- fromuser (none)
- full (n)
- help
- ignore (n)
- indexes (y)
- log (none)
- parfile (none)
- rows (y)
- tables (none)
- tablespaces (none)
- touser (none)
- userid (none)
import sequence
- create new tables
- import data
- build index
- import trigger
- integtarity constaints enable
- built bitmap, functional, domain indexes
http://metalink.oracle.com
Subject: Alter System Kill Session Marked for Killed Forever
SQL> Alter system kill session ‘sid, serial#’;
SQL> Select status, username from v$session;
Status: killed
Username: username
On Unix:
SQL> select spid from v$process where not exists (select 1 from v$session where
paddr=addr);
!kill <spid>;
On Windows:
30 | P a g e
SQL> select spid, osuser, s.program from v$process p, v$session s where p.addr=s.paddr;
!orakill sid thread
31 | P a g e
Related docs
