DBA Commands .......

alter cluster
ALTER CLUSTER pub_cluster SIZE 4K;
ALTER CLUSTER pub_cluster DEALLOCATE UNUSED KEEP 1M;

alter database: Alter a Data File

ALTER DATABASE DATAFILE 4 OFFLINE;
ALTER DATABASE DATAFILE '/opt/oracle/datafile/users01.dbf' OFFLINE;
ALTER DATABASE DATAFILE '/opt/oracle/datafile/users01.dbf'
RESIZE 100m;

ALTER DATABASE DATAFILE '/opt/oracle/datafile/users01.dbf'
AUTOEXTEND ON NEXT 100M MAXSIZE 1000M;
ALTER DATABASE DATAFILE 4 END BACKUP;

alter database: Alter a Tempfile

ALTER DATABASE TEMPFILE 4 RESIZE 100M;
ALTER DATABASE TEMPFILE 4
AUTOEXTEND ON NEXT 100M MAXSIZE 1000M;
ALTER DATABASE TEMPFILE 4 DROP INCLUDING DATAFILES;
ALTER DATABASE TEMPFILE 4 OFFLINE;

alter database: ARCHIVELOG Mode Commands

ALTER DATABASE ARCHIVELOG;
ALTER DATABASE NOARCHIVELOG;
ALTER DATABASE FORCE LOGGING;
ALTER DATABASE CLEAR LOGFILE '/opt/oracle/logfiles/redo01.rdo';
ALTER DATABASE CLEAR UNARCHIVED LOGFILE
'/opt/oracle/logfiles/redo01.rdo';
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY, UNIQUE);
ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

alter database: Control File Operations

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
ALTER DATABASE BACKUP CONTROLFILE TO TRACE
AS '/opt/oracle/logfile_backup/backup_logfile.trc'
REUSE RESETLOGS;

ALTER DATABASE BACKUP CONTROLFILE TO
'/opt/oracle/logfile_backup/backup_logfile.ctl';
alter database: Create a Data File
ALTER DATABASE CREATE DATAFILE
'/opt/oracle/datafile/users01.dbf' AS '/opt/oracle/datafile/users01.dbf';
ALTER DATABASE CREATE DATAFILE 4
AS '/opt/oracle/datafile/users01.dbf';

ALTER DATABASE CREATE DATAFILE
'/opt/oracle/datafile/users01.dbf' AS NEW;
alter database: Datafile Offline/Online
See alter database: Alter a Data File
alter database: Logfile Commands
ALTER DATABASE ADD LOGFILE GROUP 2
('/opt/oracle/logfiles/redo02a.rdo', '/opt/oracle/logfiles/redo02b.rdo')
SIZE 300M REUSE;

ALTER DATABASE ADD LOGFILE MEMBER
'/opt/oracle/logfiles/redo02c.rdo'
to GROUP 2;

ALTER DATABASE ADD LOGFILE thread 3 GROUP 2
('/opt/oracle/logfiles/redo02a.rdo', '/opt/oracle/logfiles/redo02b.rdo')
SIZE 300M REUSE;

ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE DROP LOGFILE MEMBER '/opt/oracle/logfiles/redo02b.rdo';
alter database: Mount and Open the Database
ALTER DATABASE MOUNT;
ALTER DATABASE OPEN;
Chapter 1: DBA Cheat Sheet 3
4 Portable DBA: Oracle
alter database: Move or Rename a Database File or Online Redo Log
NOTE
The database must be mounted to rename or move online redo logs.
The database must be mounted or the data files taken offline to move
database data files.
ALTER DATABASE RENAME FILE '/ora/datafile/oldfile.dbf' TO '/ora/datafile/newfile.dbf';
alter database: Open the Database Read-Only
ALTER DATABASE OPEN READ ONLY;
alter database: Open the Database with resetlogs
ALTER DATABASE OPEN RESETLOGS;
alter database: Recover the Database
For database recovery, I recommend the use of the recover command instead. See the “recover”
section, later in the chapter.
alter function: Recompile a Function
ALTER FUNCTION my_function COMPILE;
alter index: Allocate and Deallocate Extents
ALTER INDEX ix_my_tab ALLOCATE EXTENT;
ALTER INDEX ix_my_tab ALLOCATE EXTENT
DATAFILE '/ora/datafile/newidx.dbf';
ALTER INDEX ix_my_tab DEALLOCATE UNUSED;
ALTER INDEX ix_my_tab DEALLOCATE UNUSED KEEP 100M;
alter index: Miscellaneous Maintenance
ALTER INDEX ix_my_tab PARALLEL 3;
ALTER INDEX ix_my_tab NOPARALLEL;
ALTER INDEX ix_my_tab NOCOMPRESS;
ALTER INDEX ix_my_tab COMPRESS;
alter index: Modify Logging Attributes
ALTER INDEX ix_my_tab LOGGING;
ALTER INDEX ix_my_tab NOLOGGING;
alter index: Modify Storage and Physical Attributes
ALTER INDEX ix_my_tab PCTFREE 10 PCTUSED 40 INITRANS 5
STORAGE (NEXT 100k MAXEXTENTS UNLIMITED FREELISTS 10
BUFFER_POOL KEEP);
Chapter 1: DBA Cheat Sheet 5
alter index: Partition – Add Hash Index Partition
ALTER INDEX ix_my_tab ADD PARTITION
TABLESPACE NEWIDXTBS;
alter index: Partition – Coalesce Partition
ALTER INDEX ix_my_tab COALESCE PARTITION;
alter index: Partition – Drop Partition
ALTER INDEX ix_my_tab DROP PARTITION ix_my_tab_jan_04;
alter index: Partition – Modify Default Attributes
ALTER INDEX ix_my_tab MODIFY DEFAULT ATTRIBUTES
FOR PARTITION ix_my_tab_jan_04
PCTFREE 10 PCTUSED 40 TABLESPACE newidxtbs
NOLOGGING COMPRESS;
alter index: Partition – Modify Partition
ALTER INDEX ix_my_tab MODIFY PARTITION ix_my_tab_jan_04
DEALLOCATE UNUSED KEEP 100M;
ALTER INDEX ix_my_tab MODIFY PARTITION ix_my_tab_jan_04
ALLOCATE EXTENT SIZE 100m;
ALTER INDEX ix_my_tab MODIFY PARTITION ix_my_tab_jan_04
PCTUSED 40 STORAGE(NEXT 50m) NOLOGGING;
alter index: Partition – Modify Subpartition
ALTER INDEX ix_my_tab MODIFY SUBPARTITION ix_my_tab_jan_04
DEALLOCATE UNUSED KEEP 100M;
ALTER INDEX ix_my_tab MODIFY SUBPARTITION ix_my_tab_jan_04
ALLOCATE EXTENT SIZE 100m;
ALTER INDEX ix_my_tab MODIFY SUBPARTITION ix_my_tab_jan_04
PCTUSED 40 STORAGE(NEXT 50m) NOLOGGING;
alter index: Partition – Rename
ALTER INDEX ix_my_tab RENAME
PARTITION ix_my_tab_jan_04 TO ix_my_tab_jan_05;
ALTER INDEX ix_my_tab RENAME
SUBPARTITION ix_my_tab_jan_04 TO ix_my_tab_jan_05;
alter index: Partition – Split
ALTER INDEX ix_my_tab SPLIT PARTITION ix_my_tab_jan_05
AT ('15-JAN-05') INTO PARTITION ix_my_tab_jan_05a
TABLESPACE myidxtbs
STORAGE (INITIAL 100m NEXT 50M FREELISTS 5);
6 Portable DBA: Oracle
alter index: Rebuild Nonpartitioned Indexes
ALTER INDEX ix_my_tab REBUILD ONLINE;
ALTER INDEX ix_my_tab REBUILD ONLINE
TABLESPACE idx_tbs_new PCTFREE 1
STORAGE (INITIAL 50M NEXT 50m FREELISTS 5)
COMPUTE STATISTICS PARALLEL 0;
alter index: Rebuild Partitions
ALTER INDEX ix_my_tab
REBUILD PARTITION ix_my_tab_jan_04 ONLINE;
ALTER INDEX ix_my_tab
REBUILD SUBPARTITION ix_my_tab_jan_04 ONLINE
PCTFREE 1 STORAGE (INITIAL 50M NEXT 50m FREELISTS 5)
COMPUTE STATISTICS PARALLEL 0;
alter index: Rename
ALTER INDEX ix_my_tab RENAME TO 'ix_my_tab_01';
alter index: Shrink
ALTER INDEX ix_my_tab SHRINK SPACE;
ALTER INDEX ix_my_tab SHRINK SPACE COMPACT CASCADE;
alter materialized view: Allocate and Deallocate Extents
ALTER MATERIALIZED VIEW mv_my_tab ALLOCATE EXTENT;
ALTER MATERIALIZED VIEW mv_my_tab DEALLOCATE UNUSED;
alter materialized view: Miscellaneous
ALTER MATERIALIZED VIEW mv_my_tab COMPRESS;
ALTER MATERIALIZED VIEW mv_my_tab PARALLEL 3;
ALTER MATERIALIZED VIEW mv_my_tab NOLOGGING;
ALTER MATERIALIZED VIEW mv_my_tab LOGGING;
ALTER MATERIALIZED VIEW mv_my_tab CONSIDER FRESH;
ALTER MATERIALIZED VIEW mv_my_tab ENABLE QUERY REWRITE;
alter materialized view: Physical Attributes and Storage
ALTER MATERIALIZED VIEW mv_my_tab
PCTFREE 5 PCTUSED 60
STORAGE (NEXT 100m FREELISTS 5);
alter materialized view: Refresh
ALTER MATERIALIZED VIEW mv_my_tab REFRESH FAST;
ALTER MATERIALIZED VIEW mv_my_tab REFRESH COMPLETE;
ALTER MATERIALIZED VIEW mv_my_tab REFRESH FAST ON DEMAND;
Chapter 1: DBA Cheat Sheet 7
ALTER MATERIALIZED VIEW mv_my_tab REFRESH FAST ON COMMIT;
ALTER MATERIALIZED VIEW mv_my_tab REFRESH COMPLETE
START WITH sysdate;
ALTER MATERIALIZED VIEW mv_my_tab REFRESH COMPLETE
START WITH sysdate NEXT sysdate+1/24;
alter materialized view: Shrink Space
ALTER MATERIALIZED VIEW mv_my_tab SHRINK SPACE;
ALTER MATERIALIZED VIEW mv_my_tab
SHRINK SPACE COMPACT CASCADE;
alter materialized view log: Add Components
ALTER MATERIALIZED VIEW LOG ON my_tab ADD PRIMARY KEY;
ALTER MATERIALIZED VIEW LOG ON my_tab ADD (col1, col2)
INCLUDING NEW VALUES;
ALTER MATERIALIZED VIEW LOG ON my_tab ADD (col1, col2),
ROWID, SEQUENCE INCLUDING NEW VALUES;
alter materialized view log: Allocate and Deallocate Extents
ALTER MATERIALIZED VIEW LOG ON my_tab ALLOCATE EXTENT;
ALTER MATERIALIZED VIEW LOG ON my_tab DEALLOCATE UNUSED;
alter materialized view log: Miscellaneous
ALTER MATERIALIZED VIEW LOG ON my_tab PARALLEL 3;
ALTER MATERIALIZED VIEW LOG ON my_tab NOLOGGING;
ALTER MATERIALIZED VIEW LOG ON my_tab SHRINK SPACE;
alter materialized view log: Physical Attributes and Storage
ALTER MATERIALIZED VIEW LOG ON my_tab
PCTFREE 5 PCTUSED 60
STORAGE (NEXT 100m FREELISTS 5);
alter package: Compile
ALTER PACKAGE pk_my_package COMPILE;
ALTER PACKAGE pk_my_package COMPILE SPECIFICATION;
ALTER PACKAGE pk_my_package COMPILE BODY;
alter procedure: Compile
ALTER PROCEDURE pk_my_package COMPILE;
alter profile: Miscellaneous
ALTER ROLE my_role IDENTIFIED BY password;
ALTER ROLE my_role NOT IDENTIFIED;
alter profile: Modify Limits (Password)
ALTER PROFILE my_profile LIMIT FAILED_LOGIN_ATTEMPTS=3;
ALTER PROFILE my_profile LIMIT PASSWORD_LOCK_TIME=2/24;
ALTER PROFILE my_profile LIMIT PASSWORD_GRACE_TIME=5;
ALTER PROFILE my_profile LIMIT PASSWORD_LIFETIME=60;
ALTER PROFILE my_profile LIMIT PASSWORD_REUSE_TIME=365 PASSWORD_REUSE_MAX=3;
alter profile: Modify Limits (Resource)
ALTER PROFILE my_profile LIMIT SESSIONS_PER_CPU=10;
ALTER PROFILE my_profile LIMIT CONNECT_TIME=1000;
ALTER PROFILE my_profile LIMIT IDLE_TIME=60;
ALTER PROFILE my_profile LIMIT PRIVATE_SGA=1000000;
alter rollback segment: Online/Offline
ALTER ROLLBACK SEGMENT rbs01 OFFLINE;
ALTER ROLLBACK SEGMENT rbs01 ONLINE;
alter rollback segment: Shrink
ALTER ROLLBACK SEGMENT rbs01 SHRINK;
ALTER ROLLBACK SEGMENT rbs01 SHRINK TO 100M;
alter rollback segment: storage Clause
ALTER ROLLBACK SEGMENT rbs01 STORAGE(NEXT 50M OPTIMAL 100M);
alter sequence: Miscellaneous
ALTER SEQUENCE my_seq INCREMENT BY –5;
ALTER SEQUENCE my_seq INCREMENT BY 1 MAXVALUE 50000 CYCLE;
ALTER SEQUENCE my_seq NOMAXVALUE;
ALTER SEQUENCE my_seq CACHE ORDER;
ALTER SEQUENCE my_seq INCREMENT BY 1
MINVALUE 1 MAXVALUE 500 CYCLE;
alter session: Enable and Disable Parallel Operations
ALTER SESSION ENABLE PARALLEL DML PARALLEL 3;
ALTER SESSION ENABLE PARALLEL DDL;
ALTER SESSION DISABLE PARALLEL QUERY;
alter session: Resumable Space Management
ALTER SESSION ENABLE RESUMABLE TIMEOUT 3600;
ALTER SESSION DISABLE RESUMABLE;
8 Portable DBA: Oracle
alter session: Set Session Parameters
ALTER SESSION SET nls_date_format='MM/DD/YYYY HH24:MI:SS';
ALTER SESSION SET sort_area_size=10000000;
ALTER SESSION SET query_rewrite_enabled=TRUE;
ALTER SESSION SET resumable_timeout=3600;
ALTER SESSION SET skip_unusable_indexes=TRUE;
ALTER SESSION SET SQL_TRACE=TRUE;
alter system: Logfile and Archive Logfile Management
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM ARCHIVE LOG START;
ALTER SYSTEM ARCHIVE LOG STOP;
ALTER SYSTEM ARCHIVE LOG ALL;
ALTER SYSTEM ARCHIVE LOG THREAD 1 ALL;
ALTER SYSTEM ARCHIVE LOG ALL TO 'C:\oracle\allarch';
alter system: Set System Parameters
ALTER SYSTEM SET db_cache_size=325M
COMMENT='This change is to add more memory to the system'
SCOPE=BOTH;
ALTER SYSTEM SET COMPATIBLE=10.0.0
COMMENT='GOING TO 10G!' SCOPE=SPFILE;
alter system: System Management
ALTER SYSTEM CHECKPOINT GLOBAL;
ALTER SYSTEM KILL SESSION '145,334';
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM DISABLE RESTRICTED SESSION;
ALTER SYSTEM SUSPEND;
ALTER SYSTEM QUIESCE RESTRICTED;
ALTER SYSTEM UNQUIESCE;
ALTER SYSTEM RESUME;
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;
alter table: External Table Operations
ALTER TABLE ext_parts REJECT LIMIT 500;
ALTER TABLE ext_parts DEFUALT DIRECTORY ext_employee_dir;
ALTER TABLE ext_parts ACCESS PARAMETERS
(FIELDS TERMINATED BY ’,’);
ALTER TABLE ext_parts LOCATION (’PARTS01.TXT’,’PARTS02.TXT’);
ALTER TABLE ext_parts ADD COLUMN (SSN NUMBER);
Chapter 1: DBA Cheat Sheet 9
10 Portable DBA: Oracle
alter table: Move Table
ALTER TABLE parts move TABLESPACE parts_new_tbs PCTFREE 10 PCTUSED 60;
alter table: Table Column – Add
ALTER TABLE PARTS ADD (part_location VARCHAR2(20) );
ALTER TABLE PARTS ADD (part_location VARCHAR2(20), part_bin VARCHAR2(30) );
ALTER TABLE parts ADD (photo BLOB)
LOB (photo) STORE AS lob_parts_photo
(TABLESPACE parts_lob_tbs);
alter table: Table Column – Modify
ALTER TABLE PARTS MODIFY (part_location VARCHAR2(30) );
ALTER TABLE PARTS MODIFY
part_location VARCHAR2(30), part_bin VARCHAR2(20) );
ALTER TABLE parts modify (name NOT NULL);
ALTER TABLE parts modify (name NULL);
ALTER TABLE parts MODIFY LOB (photo) (STORAGE(FREELISTS 2));
ALTER TABLE parts MODIFY LOB (photo) (PCTVERSION 50);
alter table: Table Column – Remove
ALTER TABLE parts DROP (part_location);
ALTER TABLE parts DROP (part_location, part_bin);
alter table: Table Column – Rename
ALTER TABLE parts RENAME COLUMN part_location TO part_loc;
alter table: Table Constraints – Add Check Constraint
ALTER TABLE parts ADD (CONSTRAINT ck_parts_01 CHECK (id > 0) );
alter table: Table Constraints – Add Default Value
ALTER TABLE PARTS MODIFY (name DEFAULT 'Not Available');
ALTER TABLE PARTS ADD (vendor_code NUMBER DEFAULT 0);
ALTER TABLE PARTS MODIFY (part_description DEFAULT NULL);
alter table: Table Constraints – Add Foreign Key
ALTER TABLE parts ADD CONSTRAINT fk_part_bin
FOREIGN KEY (bin_code) REFERENCES part_bin;
alter table: Table Constraints – Add Primary and Unique Key
ALTER TABLE parts ADD CONSTRAINT pk_parts_part_id
PRIMARY KEY (id) USING INDEX TABLESPACE parts_index
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);
Chapter 1: DBA Cheat Sheet 11
ALTER TABLE parts ADD CONSTRAINT uk_parts_part_bin
UNIQUE (part_bin)USING INDEX TABLESPACE parts_index
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0);
alter table: Table Constraints – Modify
ALTER TABLE parts DISABLE UNIQUE (part_bin);
ALTER TABLE parts DISABLE CONSTRAINT uk_parts_part_bin;
ALTER TABLE parts DISABLE CONSTRAINT uk_parts_part_bin KEEP INDEX;
ALTER TABLE parts DISABLE CONSTRAINT fk_part_bin;
ALTER TABLE parts DISABLE CONSTRAINT fk_part_bin
DISABLE PRIMARY KEY KEEP INDEX;
ALTER TABLE parts ENABLE CONSTRAINT fk_part_bin;
ALTER TABLE parts ENABLE PRIMARY KEY;
ALTER TABLE parts ENABLE UNIQUE (part_bin);
ALTER TABLE parts ENABLE NOVALIDATE CONSTRAINT fk_part_bin;
ALTER TABLE parts ENABLE NOVALIDATE PRIMARY KEY;
ALTER TABLE parts ENABLE NOVALIDATE UNIQUE (part_bin);
ALTER TABLE parts ENABLE NOVALIDATE PRIMARY KEY
ENABLE NOVALIDATE CONSTRAINT fk_part_bin;
alter table: Table Constraints – Remove
ALTER TABLE parts DROP CONSTRAINT fk_part_bin;
ALTER TABLE parts DROP PRIMARY KEY;
ALTER TABLE parts DROP PRIMARY KEY CASCADE;
ALTER TABLE parts DROP UNIQUE (uk_parts_part_bin);
alter table: Table Partition – Add
ALTER TABLE store_sales ADD PARTITION sales_q1_04
VALUES LESS THAN (TO_DATE('01-APR-2004','DD-MON-YYYY'))
TABLESPACE data_0104_tbs UPDATE GLOBAL INDEXES;
ALTER TABLE daily_transactions ADD PARTITION;
ALTER TABLE daily_transactions
ADD PARTITION Alaska VALUES ('AK');
ALTER TABLE daily_transactions
add PARTITION SALES_2004_Q1 VALUES LESS THAN
(TO_DATE('01-APR-2004','DD-MON-YYYY')) SUBPARTITIONS 4;
alter table: Table Partition – Merge
ALTER TABLE store_sales
MERGE PARTITIONS Oklahoma, texas
INTO PARTITION oktx;
alter table: Table Partition – Move
ALTER TABLE store_sales MOVE PARTITION sales_overflow TABLESPACE
new_sales_overflow STORAGE (INITIAL 100m NEXT 100m PCTINCREASE 0)
UPDATE GLOBAL INDEXES;
alter table: Table Partition – Remove
ALTER TABLE store_sales DROP PARTITION sales_q1_04 UPDATE GLOBAL INDEXES;
alter table: Table Partition – Rename
ALTER TABLE store_sales RENAME PARTITION sales_q1 TO sales_first_quarter;
alter table: Table Partition – Split
ALTER TABLE store_sales
SPLIT PARTITION sales_overflow AT
(TO_DATE('01-FEB-2004','DD-MON-YYYY') )
INTO (PARTITION sales_q4_2003,
PARTITION sales_overflow)
UPDATE GLOBAL INDEXES;
ALTER TABLE composite_sales SPLIT PARTITION sales_q1
AT (TO_DATE('15-FEB-2003','DD-MON-YYYY'))
INTO (PARTITION sales_q1_01 SUBPARTITIONS 4
STORE IN (q1_01_tab1, q1_01_tab2, q1_01_tab3, q1_01_tab4),
PARTITION sales_q1_02 SUBPARTITIONS 4
STORE IN (q1_02_tab1, q1_02_tab2, q1_02_tab3, q1_02_tab4) )
UPDATE GLOBAL INDEXES;
alter table: Table Partition – Truncate
ALTER TABLE store_sales TRUNCATE PARTITION sales_overflow
UPDATE GLOBAL INDEXES;
alter table: Table Properties
ALTER TABLE parts PCTFREE 10 PCTUSED 60;
ALTER TABLE parts STORAGE (NEXT 1M);
ALTER TABLE parts PARALLEL 4;
alter table: Triggers – Modify Status
ALTER TABLE parts DISABLE ALL TRIGGERS;
ALTER TABLE parts ENABLE ALL TRIGGERS;
alter tablespace: Backups
ALTER TABLESPACE my_data_tbs BEGIN BACKUP;
ALTER TABLESPACE my_data_tbs END BACKUP;
alter tablespace: Data Files and Tempfiles
ALTER TABLESPACE mytbs
ADD DATAFILE '/ora100/oracle/mydb/mydb_mytbs_01.dbf' SIZE 100M;
12 Portable DBA: Oracle
ALTER TABLESPACE mytemp
ADD TEMPFILE '/ora100/oracle/mydb/mydb_mytemp_01.dbf'
SIZE 100M;
ALTER TABLESPACE mytemp AUTOEXTEND OFF;
ALTER TABLESPACE mytemp AUTOEXTEND ON NEXT 100m MAXSIZE 1G;
alter tablespace: Rename
ALTER TABLESPACE my_data_tbs RENAME TO my_newdata_tbs;
alter tablespace: Tablespace Management
ALTER TABLESPACE my_data_tbs DEFAULT
STORAGE (INITIAL 100m NEXT 100m FREELISTS 3);
ALTER TABLESPACE my_data_tbs MINIMUM EXTENT 500k;
ALTER TABLESPACE my_data_tbs RESIZE 100m;
ALTER TABLESPACE my_data_tbs COALESCE;
ALTER TABLESPACE my_data_tbs OFFLINE;
ALTER TABLESPACE my_data_tbs ONLINE;
ALTER TABLESPACE mytbs READ ONLY;
ALTER TABLESPACE mytbs READ WRITE;
ALTER TABLESPACE mytbs FORCE LOGGING;
ALTER TABLESPACE mytbs NOLOGGING;
ALTER TABLESPACE mytbs FLASHBACK ON;
ALTER TABLESPACE mytbs FLASHBACK OFF;
ALTER TABLESPACE mytbs RETENTION GUARANTEE;
ALTER TABLESPACE mytbs RETENTION NOGUARANTEE;
alter trigger
ALTER TRIGGER tr_my_trigger DISABLE;
ALTER TRIGGER tr_my_trigger ENABLE;
ALTER TRIGGER tr_my_trigger RENAME TO tr_new_my_trigger;
ALTER TRIGGER tr_my_trigger COMPILE;
alter user: Change Password
ALTER USER olduser IDENTIFIED BY newpassword;
ALTER USER olduser IDENTIFIED EXTERNALLY;
alter user: Password and Account Management
ALTER USER olduser PASSWORD EXPIRE;
ALTER USER olduser ACCOUNT LOCK;
ALTER USER olduser ACCOUNT UNLOCK;
alter user: Profile
ALTER USER olduser PROFILE admin_profile;
Chapter 1: DBA Cheat Sheet 13
alter user: Quotas
ALTER USER olduser QUOTA UNLIMITED ON users;
ALTER USER olduser QUOTA 10000M ON USERS;
alter user: Roles
ALTER USER olduser DEFAULT ROLE admin_role;
ALTER USER olduser DEFAULT ROLE NONE;
ALTER USER olduser DEFAULT ROLE ALL EXCEPT admin_role;
alter user: Tablespace Assignments
ALTER USER olduser DEFAULT TABLESPACE users;
ALTER USER olduser TEMPORARY TABLESPACE temp;
alter view: Constraints
ALTER VIEW my_view
ADD CONSTRAINT u_my_view_01 UNIQUE (empno)
RELY DISABLE NOVALIDATE;
ALTER VIEW my_view DROP CONSTRAINT u_my_view_01;
ALTER VIEW my_view DROP PRIMARY KEY;
ALTER VIEW my_view MODIFY CONSTRAINT u_my_view_01 NORELY;
ALTER VIEW my_view MODIFY CONSTRAINT u_my_view_01 RELY;
alter view: Recompile
ALTER VIEW my_view RECOMPILE;
analyze: Analyze Cluster
ANALYZE CLUSTER my_cluster_tab COMPUTE STATISTICS FOR ALL ROWS;
ANALYZE CLUSTER my_cluster_tab
ESTIMATE STATISTICS SAMPLE 10000 ROWS FOR ALL ROWS;
analyze: Analyze Index
ANALYZE INDEX ix_tab_01 COMPUTE STATISTICS FOR ALL ROWS;
ANALYZE INDEX ix_tab_01
ESTIMATE STATISTICS SAMPLE 10000 ROWS FOR ALL ROWS;
analyze: Analyze Table
ANALYZE TABLE mytab COMPUTE STATISTICS
FOR ALL INDEXED COLUMNS SIZE 100;
ANALYZE TABLE mytab COMPUTE STATISTICS
FOR ALL INDEXES;
14 Portable DBA: Oracle
audit
AUDIT ALL ON scott.emp;
AUDIT UPDATE, DELETE ON scott.emp;
AUDIT SELECT on scott.emp WHENEVER NOT SUCCESSFUL;
AUDIT INSERT, UPDATE, DELETE ON DEFAULT;
comment
COMMENT ON TABLE scott.mytab IS
'This is a comment on the mytab table';
COMMENT ON COLUMN scott.mytab.col1 IS
'This is a comment on the col1 column';
COMMENT ON MATERIALIZED VIEW scott.mview IS
'This is a comment on the materialized view mview';
create cluster
CREATE CLUSTER pub_cluster (pubnum NUMBER)
SIZE 8K PCTFREE 10 PCTUSED 60 TABLESPACE user_data;
CREATE CLUSTER pub_cluster (pubnum NUMBER)
SIZE 8K HASHKEYS 1000 PCTFREE 10 PCTUSED 60
TABLESPACE user_data;
create control file
CREATE CONTROLFILE REUSE DATABASE "mydb"
NORESETLOGS NOARCHIVELOG
MAXLOGFILES 32 MAXLOGMEMBERS 3
MAXDATAFILES 200 MAXINSTANCES 1
MAXLOGHISTORY 1000
LOGFILE
GROUP 1 ('/ora01/oracle/mydb/mydb_redo1a.rdo',
'/ora02/oracle/mydb/mydb_redo1b.rdo') SIZE 500K,
GROUP 2 ('/ora01/oracle/mydb/mydb_redo2a.rdo',
'/ora01/oracle/mydb/mydb_redo2b.rdo') SIZE 500K
DATAFILE
'/ora01/oracle/mydb/mydb_system_01.dbf ',
'/ora01/oracle/mydb/mydb_users_01.dbf ',
'/ora01/oracle/mydb/mydb_undo_01.dbf ',
'/ora01/oracle/mydb/mydb_sysaux_01.dbf ',
'/ora01/oracle/mydb/mydb_alldata_01.dbf ';
create database
CREATE DATABASE prodb
MAXINSTANCES 1 MAXLOGHISTORY 1
MAXLOGFILES 5 MAXLOGMEMBERS 3
MAXDATAFILES 100
Chapter 1: DBA Cheat Sheet 15
16 Portable DBA: Oracle
DATAFILE 'C:\oracle\ora92010\prodb\system01.dbf'
SIZE 250M REUSE AUTOEXTEND ON NEXT 10240K
MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL DEFAULT
TEMPORARY TABLESPACE TEMP
TEMPFILE 'C:\oracle\ora92010\prodb\temp01.dbf'
SIZE 40M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
SYSAUX TABLESPACE
DATAFILE 'C:\oracle\ora92010\prodb\sysauxtbs01.dbf'
SIZE 300M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
UNDO TABLESPACE "UNDOTBS1"
DATAFILE 'C:\oracle\ora92010\prodb\undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET WE8MSWIN1252
NATIONAL CHARACTER SET AL16UTF16
LOGFILE
GROUP 1 ('C:\oracle\ora92010\prodb\redo01.log') SIZE 102400K,
GROUP 2 ('C:\oracle\ora92010\prodb\redo02.log') SIZE 102400K,
GROUP 3 ('C:\oracle\ora92010\prodb\redo03.log') SIZE 102400K;
create database link
CREATE DATABASE LINK my_db_link
CONNECT TO current_user
USING 'my_db';
CREATE PUBLIC DATABASE LINK my_db_link
CONNECT TO remote_user IDENTIFIED BY psicorp
USING 'my_db';
create directory
CREATE OR REPLACE DIRECTORY mydir AS
'/opt/oracle/admin/directories/mydir';
create function
CREATE OR REPLACE FUNCTION find_value_in_table
(p_value IN NUMBER, p_table IN VARCHAR2,
p_column IN VARCHAR2)
RETURN NUMBER IS
v_found NUMBER;
v_sql VARCHAR2(2000);
BEGIN
v_sql:='SELECT 1 FROM '||p_table||' WHERE '||p_column||
' = '||p_value;
execute immediate v_sql into v_found;
return v_found;
END;
/
create index: Function-Based Index
CREATE INDEX fb_upper_last_name_emp ON emp_info (UPPER(last_name) );
Chapter 1: DBA Cheat Sheet 17
create index: Global Partitioned Indexes
CREATE INDEX ix_part_my_tab_01 ON store_sales (invoice_number)
GLOBAL PARTITION BY RANGE (invoice_number)
(PARTITION part_001 VALUES LESS THAN (1000),
PARTITION part_002 VALUES LESS THAN (10000),
PARTITION part_003 VALUES LESS THAN (MAXVALUE) );
CREATE INDEX ix_part_my_tab_02 ON store_sales
(store_id, time_id)
GLOBAL PARTITION BY RANGE (store_id, time_id)
(PARTITION PART_001 VALUES LESS THAN
(1000, TO_DATE('04-01-2003','MM-DD-YYYY') )
TABLESPACE partition_001
STORAGE (INITIAL 100M NEXT 200M PCTINCREASE 0),
PARTITION part_002 VALUES LESS THAN
(1000, TO_DATE('07-01-2003','MM-DD-YYYY') )
TABLESPACE partition_002
STORAGE (INITIAL 200M NEXT 400M PCTINCREASE 0),
PARTITION part_003 VALUES LESS THAN (maxvalue, maxvalue)
TABLESPACE partition_003 );
create index: Local Partitioned Indexes
CREATE INDEX ix_part_my_tab_01 ON my_tab
(col_one, col_two, col_three)
LOCAL (PARTITION tbs_part_01 TABLESPACE part_tbs_01,
PARTITION tbs_part_02 TABLESPACE part_tbs_02,
PARTITION tbs_part_03 TABLESPACE part_tbs_03,
PARTITION tbs_part_04 TABLESPACE part_tbs_04);
CREATE INDEX ix_part_my_tab_01 ON my_tab (col_one, col_two, col_three)
LOCAL STORE IN (part_tbs_01, part_tbs_02, part_tbs_03, part_tbs_04);
CREATE INDEX ix_part_my_tab_01 ON my_tab (col_one, col_two, col_three)
LOCAL STORE IN (
part_tbs_01 STORAGE (INITIAL 10M NEXT 10M MAXEXTENTS 200),
part_tbs_02,
part_tbs_03 STORAGE (INITIAL 100M NEXT 100M MAXEXTENTS 200),
part_tbs_04 STORAGE (INITIAL 1000M NEXT 1000M MAXEXTENTS 200));
create index: Local Subpartitioned Indexes
CREATE INDEX sales_ix ON store_sales(time_id, store_id)
STORAGE (INITIAL 1M MAXEXTENTS UNLIMITED) LOCAL
(PARTITION q1_2003,
PARTITION q2_2003,
PARTITION q3_2003
(SUBPARTITION pq3200301, SUBPARTITION pq3200302,
SUBPARTITION pq3200303, SUBPARTITION pq3200304,
SUBPARTITION pq3200305),
PARTITION q4_2003
(SUBPARTITION pq4200301 TABLESPACE tbs_1,
SUBPARTITION pq4200302 TABLESPACE tbs_1,
SUBPARTITION pq4200303 TABLESPACE tbs_1,
SUBPARTITION pq4200304 TABLESPACE tbs_1,
SUBPARTITION pq4200305 TABLESPACE tbs_1,
SUBPARTITION pq4200306 TABLESPACE tbs_1,
SUBPARTITION pq4200307 TABLESPACE tbs_1,
SUBPARTITION pq4200308 TABLESPACE tbs_1),
PARTITION sales_overflow
(SUBPARTITION pqoflw01 TABLESPACE tbs_2,
SUBPARTITION pqoflw02 TABLESPACE tbs_2,
SUBPARTITION pqoflw03 TABLESPACE tbs_2,
SUBPARTITION pqoflw04 TABLESPACE tbs_2));
create index: Nonpartitioned Indexes
CREATE INDEX ix_mytab_01 ON mytab(column_1);
CREATE UNIQUE INDEX ix_mytab_01 ON mytab(column_1, column_2, column_3);
CREATE INDEX ix_mytab_01 ON mytab(column_1, column_2, column_3)
TABLESPACE my_indexes COMPRESS
STORAGE (INITIAL 10K NEXT 10K PCTFREE 10) COMPUTE STATISTICS;
CREATE BITMAP INDEX bit_mytab_01 ON my_tab(col_two)
TABLESPACE my_tbs;
create materialized view
CREATE MATERIALIZED VIEW emp_dept_mv1
TABLESPACE users BUILD IMMEDIATE
REFRESH FAST ON COMMIT WITH ROWID
ENABLE QUERY REWRITE AS
SELECT d.rowid deptrowid, e.rowid emprowid,
e.empno, e.ename, e.job, d.loc
FROM dept d, emp e
WHERE d.deptno = e.deptno;
CREATE MATERIALIZED VIEW emp_dept_mv3
TABLESPACE users BUILD IMMEDIATE
REFRESH FAST ON COMMIT WITH ROWID
DISABLE QUERY REWRITE AS
SELECT d.rowid deptrowid, e.rowid emprowid,
d.dname, d.loc, e.ename, e.job
FROM dept d, emp e
WHERE d.deptno (+) = e.deptno;
create materialized view: Partitioned Materialized View
CREATE MATERIALIZED VIEW part_emp_mv1
PARTITION BY RANGE (hiredate)
(PARTITION month1
VALUES LESS THAN (TO_DATE('01-APR-1981', 'DD-MON-YYYY'))
PCTFREE 0 PCTUSED 99
STORAGE (INITIAL 64k NEXT 16k PCTINCREASE 0)
TABLESPACE users,
18 Portable DBA: Oracle
PARTITION month2
VALUES LESS THAN (TO_DATE('01-DEC-1981', 'DD-MON-YYYY'))
PCTFREE 0 PCTUSED 99
STORAGE (INITIAL 64k NEXT 16k PCTINCREASE 0)
TABLESPACE users,
PARTITION month3
VALUES LESS THAN (TO_DATE('01-APR-1988', 'DD-MON-YYYY'))
PCTFREE 0 PCTUSED 99
STORAGE (INITIAL 64k NEXT 16k PCTINCREASE 0)
TABLESPACE users)
BUILD IMMEDIATE REFRESH FAST ENABLE QUERY REWRITE AS
SELECT hiredate, count(*) as hires
FROM emp
GROUP BY hiredate;
create materialized view log
CREATE MATERIALIZED VIEW LOG ON emp
TABLESPACE users
WITH PRIMARY KEY, SEQUENCE,
(ename, job, mgr, hiredate, sal, comm, deptno)
INCLUDING NEW VALUES;
create package/create package body
CREATE OR REPLACE PACKAGE get_Tomdate_pkg IS
FUNCTION GetTomdate RETURN DATE;
PRAGMA RESTRICT_REFERENCES (GetTomdate, WNDS);
PROCEDURE ResetSysDate;
END get_Tomdate_pkg;
/
CREATE OR REPLACE PACKAGE BODY get_Tomdate_pkg IS
v_Sysdate DATE := TRUNC(SYSDATE);
FUNCTION GetTomdate RETURN DATE IS
BEGIN
RETURN v_sysdate+1;
END GetTomdate;
PROCEDURE ResetSysdate IS
BEGIN
v_Sysdate := SYSDATE;
END ResetSysdate;
END get_Tomdate_pkg;
/
create pfile
CREATE PFILE FROM SPFILE;
CREATE PFILE='/opt/oracle/admin/mydb/pfile/initmybd.ora'
FROM SPFILE='/opt/oracle/admin/mydb/pfile/spfilemybd.ora';
Chapter 1: DBA Cheat Sheet 19
create procedure
CREATE OR REPLACE PROCEDURE new_emp_salary
(p_empid IN NUMBER, p_increase IN NUMBER)
AS
BEGIN
UPDATE emp SET salary=salary*p_increase
WHERE empid=p_empid;
END;
/
create profile
CREATE PROFILE development_profile
LIMIT
SESSIONS_PER_USER 2 CONNECT_TIME 100000 IDLE_TIME 100000
LOGICAL_READS_PER_SESSION 1000000
PRIVATE_SGA 10m
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 60
PASSWORD_REUSE_TIME 365
PASSWORD_REUSE_MAX 3
PASSWORD_LOCK_TIME 30
PASSWORD_GRACE_TIME 5;
create role
CREATE ROLE developer_role IDENTIFIED USING develop;
create rollback segment
CREATE ROLLBACK SEGMENT r01 TABLESPACE RBS
STORAGE (INITIAL 100m NEXT 100M MINEXTENTS 5 OPTIMAL 500M);
create sequence
CREATE SEQUENCE my_seq
START WITH 1 INCREMENT BY 1 MAXVALUE 1000000 CYCLE CACHE;
create spfile
CREATE SPFILE FROM PFILE;
CREATE SPFILE='/opt/oracle/admin/mydb/pfile/spfilemybd.ora'
FROM PFILE='/opt/oracle/admin/mydb/pfile/initmybd.ora';
create synonym
CREATE SYNONYM scott_user.emp FOR scott.EMP;
CREATE PUBLIC SYNONYM emp FOR scott.EMP;
20 Portable DBA: Oracle
create table
CREATE TABLE my_tab
(id NUMBER, current_value VARCHAR2(2000) ) COMPRESS;
CREATE TABLE parts (id NUMBER, version NUMBER, name VARCHAR2(30),
Bin_code NUMBER, upc NUMBER, active_code VARCHAR2(1) NOT NULL
CONSTRAINT ck_parts_active_code_01
CHECK (UPPER(active_code)= 'Y' or UPPER(active_code)='N'),
CONSTRAINT pk_parts PRIMARY KEY (id, version)
USING INDEX TABLESPACE parts_index
STORAGE (INITIAL 1m NEXT 1m) )
TABLESPACE parts_tablespace
PCTFREE 20 PCTUSED 60 STORAGE ( INITIAL 10m NEXT 10m PCTINCREASE 0);
create tablespace: Permanent Tablespace
CREATE TABLESPACE data_tbs
DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf'
SIZE 100m;
CREATE TABLESPACE data_tbs
DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf'
SIZE 100m FORCE LOGGING BLOCKSIZE 8k;
CREATE TABLESPACE data_tbs
DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf'
SIZE 100m NOLOGGING
DEFAULT COMPRESS EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
CREATE TABLESPACE data_tbs
DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf'
SIZE 100m NOLOGGING
DEFAULT COMPRESS EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
CREATE BIGFILE TABLESPACE data_tbs
DATAFILE '/opt/oracle/mydbs/data/mydbs_data_tbs_01.dbf'
SIZE 10G;
create tablespace: Temporary Tablespace
CREATE TABLESPACE temp_tbs
TEMPFILE '/opt/oracle/mydbs/data/mydbs_temp_tbs_01.tmp'
SIZE 100m;
create tablespace: Undo Tablespace
CREATE TABLESPACE undo_tbs
TEMPFILE '/opt/oracle/mydbs/data/mydbs_undo_tbs_01.tmp'
SIZE 1g RETENTION GUARANTEE;
Chapter 1: DBA Cheat Sheet 21
22 Portable DBA: Oracle
create trigger
CREATE OR REPLACE TRIGGER emp_comm_after_insert
BEFORE INSERT ON emp FOR EACH ROW
DECLARE
v_sal number;
v_comm number;
BEGIN
-- Find username of person performing the INSERT into the table
v_sal:=:new.salary;
:new.comm:=v_sal*.10;
END;
/
create user
CREATE USER Robert IDENTIFIED BY Freeman
DEFAULT TABLESPACE users_tbs
TEMPORARY TABLESPACE temp
QUOTA 100M ON users_tbs
QUOTA UNLIMITED ON data_tbs;
create view
CREATE OR REPLACE VIEW vw_emp_dept_10 AS
SELECT * FROM EMP WHERE dept=10;
CREATE OR REPLACE VIEW vw_public_email AS
SELECT ename_first, ename_last, email_address
FROM EMP WHERE public='Y'
delete
DELETE FROM emp WHERE empid=100;
DELETE FROM emp e WHERE e.rowid >
(SELECT MIN (esub.ROWID) FROM emp esub
WHERE e.empid=esub.empid);
drop cluster
DROP CLUSTER scott.emp_cluster
INCLUDING TABLES CASCADE CONSTRAINTS;
drop database
DROP DATABASE;
drop database link
DROP DATABASE LINK my_db_link;
DROP PUBLIC DATABASE LINK my_db_link;
Chapter 1: DBA Cheat Sheet 23
drop directory
DROP DIRECTORY mydir;
drop function
DROP FUNCTION find_value_in_table;
drop index
DROP INDEX ix_my_tab;
drop materialized view
DROP MATERIALIZED VIEW my_mview;
DROP MATERIALIZED VIEW my_mview PRESERVE TABLE;
drop materialized view log
DROP MATERIALIZED VIEW LOG ON mytab;
drop package/drop package body
DROP PACKAGE scott.my_package
DROP PACKAGE BODY scott.my_package;
drop procedure
DROP PROCEDURE my_proc;
drop profile
DROP PROFILE my_profile CASCADE;
drop role
DROP ROLE my_role;
drop rollback segment
DROP ROLLBACK SEGMENT rbs01;
drop sequence
DROP SEQUENCE my_seq;
drop synonym
DROP SYNONYM my_synonym;
DROP PUBLIC SYNONYM my_synonym;
drop table
DROP TABLE my_tab;
DROP TABLE my_tab CASCADE CONSTRAINTS;
DROP TABLE my_tab CASCADE CONSTRAINTS PURGE;
drop tablespace
DROP TABLESPACE my_tbs;
DROP TABLESPACE my_tbs INCLUDING CONTENTS;
DROP TABLESPACE my_tbs INCLUDING CONTENTS
AND DATAFILES CASCADE CONSTRAINTS;
drop trigger
DROP TRIGGER my_trigger;
drop user
DROP USER my_user CASCADE;
drop view
DROP VIEW my_view CASCADE CONSTRAINTS;
explain plan
EXPLAIN PLAN SET STATEMENT_ID='TEST' FOR
SELECT * FROM emp WHERE EMPID=100;
flashback database
FLASHBACK DATABASE TO SCN 10000;
FLASHBACK DATABASE TO TIMESTAMP SYSDATE – 1/24;
FLASHBACK DATABASE TO BEFORE TIMESTAMP SYSDATE – 1/24;
flashback table
FLASHBACK TABLE my_tab TO SCN 10000;
FLASHBACK TABLE my_tab TO TIMESTAMP SYSDATE – 1/24
ENABLE TRIGGERS;
FLASHBACK TABLE my_tab TO BEFORE DROP;
FLASHBACK TABLE my_tab TO BEFORE DROP RENAME TO rec_tab;
grants: Object Grants
GRANT SELECT ON scott.my_tab TO my_user;
GRANT INSERT, UPDATE, SELECT ON scott.my_tab TO my_user;
24 Portable DBA: Oracle
Chapter 1: DBA Cheat Sheet 25
GRANT SELECT ON scott.my_tab TO my_user WITH GRANT OPTION;
GRANT SELECT ON scott.my_tab TO PUBLIC WITH GRANT OPTION;
grants: System Grants
GRANT CREATE TABLE to my_user;
GRANT CREATE ANY TABLE to my_user WITH ADMIN OPTION;
GRANT ALL PRIVILEGES to my_user WITH ADMIN OPTION;
insert
INSERT INTO dept VALUES (100, 'Marketing', 'Y');
INSERT INTO dept (deptid, dept_name, active)
VALUES (100, 'Marketing', 'Y');
INSERT INTO emp_history SELECT * FROM emp a
WHERE a.empid NOT IN (SELECT empid FROM emp_history);
INSERT INTO emp_pay_summary
SELECT empid, sum(gross_pay) FROM emp_pay_history
GROUP BY empid;
INSERT ALL
INTO store_sales (store_id, sales_date, deptid, sales_amt)
VALUES (store_id, start_date, deptid, mon_sales)
INTO store_sales (store_id, sales_date, deptid, sales_amt)
VALUES (store_id, start_date+1, deptid, tue_sales)
INTO store_sales (store_id, sales_date, deptid, sales_amt)
VALUES (store_id, start_date+2, deptid, wed_sales)
INTO store_sales (store_id, sales_date, deptid, sales_amt)
VALUES (store_id, start_date+3, deptid, thur_sales)
INTO store_sales (store_id, sales_date, deptid, sales_amt)
VALUES (store_id, start_date+4, deptid, fri_sales)
INTO store_sales (store_id, sales_date, deptid, sales_amt)
VALUES (store_id, start_date+5, deptid, sat_sales)
INTO store_sales (store_id, sales_date, deptid, sales_amt)
VALUES (store_id, start_date+6, deptid, sun_sales)
SELECT store_id, start_date, deptid, mon_sales, tue_sales,
wed_sales, thur_sales, fri_sales, sat_sales, sun_sales
FROM store_sales_load;
INSERT ALL
WHEN store_id < 100 THEN INTO east_stores
WHEN store_id >= 100 THEN INTO west_stores
ELSE INTO misc_stores
SELECT * FROM store_sales_load;
INSERT /*+ APPEND */ INTO emp VALUES (100,
'Jacob','Freeman',1000,20, null, 10, sysdate, 100,
sysdate+365);
lock table
LOCK TABLE my_table IN EXCLUSIVE MODE NOWAIT;
LOCK TABLE my_table IN ROW EXCLUSIVE MODE;
26 Portable DBA: Oracle
merge
MERGE INTO emp_retire A
USING (SELECT empno, ename_last, ename_first, salary
FROM emp WHERE retire_cd='Y') B
ON (a.empid=b.empid)
WHEN MATCHED THEN UPDATE SET
a.ename_last=b.ename_last,
a.ename_first=b.ename_first,
a.salary=b.salary
DELETE WHERE (b.retire_cd='D')
WHEN NOT MATCHED THEN INSERT
(a.empid, a.ename_last, a.ename_first, a.salary)
VALUES (b.empid, b.ename_last, b.ename_first, b.salary)
WHERE (b.retire_cd!='D');
noaudit
NOAUDIT ALL ON scott.emp;
NOAUDIT UPDATE, DELETE ON scott.emp;
NOAUDIT SELECT on scott.emp WHENEVER NOT SUCCESSFUL;
NOAUDIT INSERT, UPDATE, DELETE ON DEFAULT;
purge
PURGE TABLE my_tab;
PURGE INDEX ix_my_tab;
PURGE RECYCLEBIN;
PURGE DBA_RECYCLEBIN;
PURGE TABLESPACE data_tbs USER scott;
recover
RECOVER DATABASE;
RECOVER TABLESPACE user_data, user_index;
RECOVER DATAFILE
'/opt/oracle/admin/mydb/datafile/mydb_users_01.dbf';
RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;
RECOVER DATABASE UNTIL CHANGE 94044;
RECOVER DATABASE UNTIL TIME '2004-08-01:22:00:04';
rename
RENAME my_table to my_tab;
revoke: Object Grants
REVOKE SELECT ON scott.my_tab FROM my_user;
REVOKE INSERT, UPDATE, SELECT ON scott.my_tab FROM my_user;
REVOKE SELECT ON scott.my_tab FROM my_user;
REVOKE SELECT ON scott.my_tab FROM PUBLIC;
Chapter 1: DBA Cheat Sheet 27
revoke: System Grants
REVOKE CREATE TABLE FROM my_user;
REVOKE CREATE ANY TABLE FROM my_user;
REVOKE ALL PRIVILEGES FROM my_user;
rollback
ROLLBACK;
savepoint
SAVEPOINT alpha;
select
SELECT ename_last, dname
FROM emp a, dept b
WHERE a.deptid=b.deptid;
SELECT a.empid, b.dept_name
FROM emp a, dept b
WHERE a.deptid=b.deptid (+);
SELECT a.empid, b.dept_name
FROM emp a LEFT OUTER JOIN dept b
ON a.deptid=b.deptid;
SELECT * FROM dept WHERE EXISTS
(SELECT * FROM emp
WHERE emp.deptid=dept.deptid
AND emp.salary > 100);
SELECT ename_first, ename_last,
CASE deptid
WHEN 10 THEN 'Acounting' WHEN 20 THEN 'Sales'
ELSE 'None' END FROM emp;
SELECT empid, ename_last, salary, comm
FROM emp a
WHERE salary*.10 > (SELECT AVG(comm) FROM emp z
WHERE a.deptid=z.deptid);
WITH avg_dept_sales AS (
SELECT a.deptid, avg(b.sales_amt) avg_sales
FROM emp a, dept_sales b
WHERE a.deptid=b.deptid
GROUP BY a.deptid),
emp_salaries AS
(SELECT empid, AVG(salary) avg_salary FROM emp
GROUP BY empid)
SELECT * FROM emp_salaries b WHERE avg_salary*.05 >
(SELECT avg_sales FROM avg_dept_sales);
SELECT /*+ INDEX (a, emp_last_name_ix) */ empid
FROM emp a WHERE ename_last='Freeman'
SELECT empid, TO_CHAR(retire_date, 'MM/DD/YYYY')
FROM emp
WHERE retire_date IS NOT NULL
ORDER BY retire_date
SELECT empid, COUNT(*)
FROM emp
GROUP BY empid
HAVING COUNT(*) > 1;
SELECT empid, salary FROM emp
AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '1' DAY)
WHERE empid=20;
SELECT empid, salary FROM emp
VERSIONS BETWEEN
TIMESTAMP SYSTIMESTAMP - INTERVAL '1' DAY AND
SYSTIMESTAMP - INTERVAL '1' HOUR
WHERE empid=20;
set constraints
SET CONSTRAINTS ALL IMMEDIATE;
SET CONSTRAINTS ALL DEFERRED;
SET CONSTRAINT fk_my_tab DEFERRED;
set transaction
SET TRANSACTION USE ROLLBACK SEGMENT rbs01;
SET TRANSACTION READ ONLY;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
truncate
TRUNCATE TABLE my_tab;
TRUNCATE TABLE my_tab PRESERVE MATERIALIZED VIEW LOG;
TRUNCATE TABLE my_tab REUSE STORAGE;
TRUNCATE TABLE my_tab DROP STORAGE;
update
UPDATE emp SET salary=100 WHERE empid=100;
UPDATE emp SET salary=NULL, retire_date=SYSDATE
WHERE empid=100;
UPDATE emp SET salary=salary*1.10
WHERE deptid IN
(SELECT deptid FROM dept WHERE dept_name = 'Sales');
UPDATE emp a SET (salary, comm)=
(SELECT salary*1.10, comm*1.10
FROM emp b WHERE a.empid=b.empid);
INSERT INTO store_sales
PARTITION (store_sales_jan_2004) sa
SET sa.sales_amt=1.10 where store_id=100;
28 Portable DBA: Oracle

Oracle Database 10g Release 2 (10.2.0.1) Installation (RHEL5)

Oracle Database 10g Release 2 (10.2.0.1) Installation On Red Hat Enterprise Linux 5 (RHEL5)

In this article I'll describe the installation of Oracle Database 10g Release 2 (10.2.0.1) on Red Hat Enterprise Linux 5 (RHEL5). The article is based on a server installation similar to this, with a minimum of 2G swap, secure Linux disabled and the following package groups installed.

    GNOME Desktop Environment
    Editors
    Graphical Internet
    Text-based Internet
    Development Libraries
    Development Tools
    Legacy Software Development
    Server Configuration Tools
    Administration Tools
    Base
    Legacy Software Support
    System Tools
    X Window System

Alternative installations may require more packages to be loaded, in addition to the ones listed below.

    Download Software
    Unpack Files
    Hosts File
    Set Kernel Parameters
    Setup
    Installation
    Post Installation

Download Software

Download the following software.

    Oracle Database 10g Release 2 (10.2.0.1) Software

Unpack Files

Unzip the files.

    unzip 10201_database_linux32.zip

You should now have a single directory containing installation files. Depending on the age of the download this may either be named "db/Disk1" or "database".
Hosts File

The "/etc/hosts" file must contain a fully qualified name for the server.

    <IP-address>  <fully-qualified-machine-name>  <machine-name>

For example.

    127.0.0.1      localhost localhost.localdomain
    192.168.0.198  ol5-102.localdomain ol5-102

Set Kernel Parameters

Add the following lines to the "/etc/sysctl.conf" file.

    #kernel.shmall = 2097152
    #kernel.shmmax = 2147483648
    kernel.shmmni = 4096
    # semaphores: semmsl, semmns, semopm, semmni
    kernel.sem = 250 32000 100 128
    #fs.file-max = 65536
    net.ipv4.ip_local_port_range = 1024 65000
    net.core.rmem_default=262144
    net.core.rmem_max=262144
    net.core.wmem_default=262144
    net.core.wmem_max=262144

Run the following command to change the current kernel parameters.

    /sbin/sysctl -p

Add the following lines to the "/etc/security/limits.conf" file.

    *               soft    nproc   2047
    *               hard    nproc   16384
    *               soft    nofile  1024
    *               hard    nofile  65536

Add the following line to the "/etc/pam.d/login" file, if it does not already exist.

    session    required     pam_limits.so

Disable secure linux by editing the "/etc/selinux/config" file, making sure the SELINUX flag is set as follows.

    SELINUX=disabled

Alternatively, this alteration can be done using the GUI tool (System > Administration > Security Level and Firewall). Click on the SELinux tab and disable the feature.
Setup

Install the following packages.

    # From RedHat AS5 Disk 1
    cd /media/cdrom/Server
    rpm -Uvh setarch-2*
    rpm -Uvh make-3*
    rpm -Uvh glibc-2*
    rpm -Uvh libaio-0*
    cd /
    eject

    # From RedHat AS5 Disk 2
    cd /media/cdrom/Server
    rpm -Uvh compat-libstdc++-33-3*
    rpm -Uvh compat-gcc-34-3*
    rpm -Uvh compat-gcc-34-c++-3*
    rpm -Uvh gcc-4*
    rpm -Uvh libXp-1*
    cd /
    eject

    # From RedHat AS5 Disk 3
    cd /media/cdrom/Server
    rpm -Uvh openmotif-2*
    rpm -Uvh compat-db-4*
    cd /
    eject

Create the new groups and users.

    groupadd oinstall
    groupadd dba
    groupadd oper

    useradd -g oinstall -G dba oracle
    passwd oracle

Create the directories in which the Oracle software will be installed.

    mkdir -p /u01/app/oracle/product/10.2.0/db_1
    chown -R oracle.oinstall /u01

Login as root and issue the following command.

    xhost +<machine-name>

Edit the "/etc/redhat-release" file replacing the current release information (Red Hat Enterprise Linux Server release 5 (Tikanga)) with the following:

    redhat-4

Login as the oracle user and add the following lines at the end of the ".bash_profile" file.

    # Oracle Settings
    TMP=/tmp; export TMP
    TMPDIR=$TMP; export TMPDIR

    ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
    ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
    ORACLE_SID=TSH1; export ORACLE_SID
    ORACLE_TERM=xterm; export ORACLE_TERM
    PATH=/usr/sbin:$PATH; export PATH
    PATH=$ORACLE_HOME/bin:$PATH; export PATH

    LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
    CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

    if [ $USER = "oracle" ]; then
      if [ $SHELL = "/bin/ksh" ]; then
        ulimit -p 16384
        ulimit -n 65536
      else
        ulimit -u 16384 -n 65536
      fi
    fi

Installation

Log into the oracle user. If you are using X emulation then set the DISPLAY environmental variable.

    DISPLAY=<machine-name>:0.0; export DISPLAY

Start the Oracle Universal Installer (OUI) by issuing the following command in the database directory.

    ./runInstaller

During the installation enter the appropriate ORACLE_HOME and name then continue installation. For a more detailed look at the installation process, click on the links below to see screen shots of each stage.

    Select Installation Method
    Specify Inventory Directory and Credentials
    Select Installation Type
    Specify Home Details
    Product-Specific Prerequisite Checks
    Select Configuration Option
    Select Database Configuration
    Specify Database Configuration Options
    Select Database Management Option
    Specify Database Storage Option
    Specify Backup and Recovery Options
    Specify Database Schema Passwords
    Summary
    Install
    Configuration Assistants
    Database Configuration Assistant
    Database Configuration Assistant Password Management
    Execute Configuration Scripts
    End Of Installation

Post Installation

Edit the "/etc/redhat-release" file restoring the original release information.

    Red Hat Enterprise Linux Server release 5 (Tikanga)

Edit the "/etc/oratab" file setting the restart flag for each instance to 'Y'.

    TSH1:/u01/app/oracle/product/10.2.0/db_1:Y

MANUAL DATABASE CREATION

Oracle Version: 10.2.0.3
OS version: Solaris 5.10/Linux

To Create an oracle database using manual scripts without using dbca, we need to do the following steps.

Here the ORACLE_SID = SRITSTDB

Things we need before creating the database.

1) Proper Oracle HOME and the software should be installed in the $ORACLE_HOME
2) Folder structure in the admin directory which contains adump,bdump,cdump,and udump.
3) Init parameter file
4) DB Creation script

Step 1: The database software i.e ORACLE_HOME binaries should be installed. For installing ORACLE SOFTWARE please follow the steps described in "Oracle 10g Home Install" page.
On the database host do the following.

cd $ORACLE_HOME
ls -ltr -> Here it should list all the files which are installed during the software installation.

My Oracle home is located at /local/mnt/oracle/product/10.2.0

Step 2: Create the adump, bdump, cdump and udump directories.
We have to create the adump,bdump, cdump and udump folders in the admin directory.

Normally admin directory will be created in the directory structure. Look for admin directory for oracle on your host.

In my system my admin directory is located at /local/mnt/oracle/admin/SRITSTDB/

In this directory create adump, bdump,cdump and udump directories using mkdir command.


Step 3: Creating INIT parameter file.
On the database host do the following. The location of this file should be in $ORACLE_HOME/dbs/, because oracle will look into this folder by default for init parameter file.

cd $ORACLE_HOME
cd dbs

vi initSRITSTDB.ora

Enter the following entries in this file. All these may not be necessary. But some of them are must.

*.aq_tm_processes=0
*.audit_file_dest='/u01/app/oracle/admin/SRITSTDB/audit'
*.background_dump_dest='/opt/mis/oracle/admin/SRITSTDB/bdump'
*.COMPATIBLE='10.2.0.1'# 10.2
*.control_files='/u01/app/oracle/data/db1/data/SRITSTDB/SRITSTDB_control01.ctl',
'/u01/app/oracle/data/db2/data/SRITSTDB/SRITSTDB_control02.ctl',
'/u01/app/oracle/data/db1/data/SRITSTDB/SRITSTDB_control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/SRITSTDB/cdump'
*.db_block_size=8192
*.db_domain='WORLD'
*.db_file_multiblock_read_count=16
*.db_files=1024
*.db_name='SRITSTDB'
*.job_queue_processes=0
*.large_pool_size=150M# increase to 150M minimum req for 10.2, was 64M
*.log_archive_dest='/u01/app/oracle/admin/SRITSTDB/arch/SRITSTDB_'
*.nls_date_format='DD-MON-YYYY HH24:MI:SS'
*.NLS_LENGTH_SEMANTICS='CHAR'# turn on after upgrade
*.open_cursors=1800
*.pga_aggregate_target=500M
*.remote_os_authent=FALSE
*.sga_max_size=1000M
*.sga_target=512M
*.undo_management='AUTO'
*.undo_retention=28800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/uo1/app/oracle/admin/SRITSTDB/udump'
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

Make sure all the yellow shaded paths should exists.


Step 4: Create password file in the $ORACLE_HOME/dbs folder.
On the database host do the following.

cd $ORACLE_HOME
cd dbs

By Staying in "dbs" directory issue the following command. Here $ORACLE_SID is the name of the database which we are creating.

$ORACLE_HOME/bin/orapwd  file=orapw$ORACLE_SID password=srikanth entries=5


Step 5: DB creation scripts. In the next step I will show how to execute these scripts. In this step I will list all the scripts which are needed for the database creation.

We have to create the db creation scripts. The DB creation scripts will be as follows.

You can create these scripts in any folder you want in your machine.

Script 1: cr_db.sql file. --- This script accepts one parameter and the parameter will be the ORACLE DATABASE NAME. HERE the database name is SRITSTDB.

set time on timing on feed on
spool cr_db.log
startup nomount
CREATE DATABASE "&DBNAME"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
---
CHARACTER SET UTF8
NATIONAL CHARACTER SET AL16UTF16
---
DATAFILE '/opt/mis/oracle/data/db1/data/&DBNAME/&DBNAME._system1a.dbf' SIZE 1000M extent management local
SYSAUX DATAFILE '/opt/mis/oracle/data/db1/data/&DBNAME/&DBNAME._sysaux1a.dbf' SIZE 1000M
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/opt/mis/oracle/data/db1/data/&DBNAME/&DBNAME._temp1a.dbf' SIZE 1000M
extent management local uniform size 1m
UNDO TABLESPACE "UNDOTBS1" DATAFILE '/opt/mis/oracle/data/db1/data/&DBNAME/&DBNAME._undotbs1a.dbf' SIZE 1000M
---
LOGFILE
GROUP 1 ('/opt/mis/oracle/data/db1/data/&DBNAME/&DBNAME._log1a.dbf','/opt/mis/oracle/data/db2/data/&DBNAME/&DBNAME._log1b.dbf') SIZE 100M,
GROUP 2 ('/opt/mis/oracle/data/db1/data/&DBNAME/&DBNAME._log2a.dbf','/opt/mis/oracle/data/db2/data/&DBNAME/&DBNAME._log2b.dbf') SIZE 100M,
GROUP 3 ('/opt/mis/oracle/data/db1/data/&DBNAME/&DBNAME._log3a.dbf','/opt/mis/oracle/data/db3/data/&DBNAME/&DBNAME._log3b.dbf') SIZE 100M
---
USER SYS IDENTIFIED BY srikanth
USER SYSTEM IDENTIFIED BY srikanth
/

NOTE: MAKE SURE ALL THE SHADED PATHS SHOULD EXIST ON YOUR MACHINE. YOU CAN CHANGE THE THESE DIRECTORY PATHS WHAT EVER YOU WANT.


Script 2: @dictionary.sql

connect /as sysdba
spool dictionary.out
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catblock.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/catoctk.sql
@?/rdbms/admin/owminst.plb
@?/javavm/install/initjvm.sql
@?/xdk/admin/initxml.sql
@?/xdk/admin/xmlja.sql
@?/rdbms/admin/catjava.sql
@?/rdbms/admin/catexf.sql
@?/rdbms/admin/catclust.sql

connect SYSTEM/srikanth
spool sqlplus.log
@?/sqlplus/admin/pupbld.sql
@?/sqlplus/admin/help/hlpbld.sql helpus.sql
spool off

spool off

Step 6: Creating a wrapper script to execute the above 2 scripts.

Make another wrapper script called "SRITSTDB.sql" with the following contents.

def DBNAME="SRITSTDB" @cr_db.sql @dictionary.sql
create tablespace users datafile '/opt/mis/oracle/data/db4/data/SRITSTDB/SRITSTDB_users1a.dbf' size 1000M;
alter database default tablespace users ;
alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited ;
exit;

Step 7: Execution.

UNIX_PROMPT> sqlplus "/as sysdba"
@SRITSTDB.sql

Physical Standby Database

Step-by-Step Instructions for Creating a Physical Standby Database using SQL commands
1. General Overview :
The purpose of this document is to create a step by step guideline for using the Oracle Dataguard a
High available mechanism. I spent about 7 days investigating about a problem faced from
my first dataguard experience. Both primary and standby
servers are on linux redhat : same version of OS.

- Primary DB : 10.2.0.1
- Standby DB : 10.2.0.3

- Host Name of Primary DB : arcdb01.es.egwn.lan
- Host Name of Standby DB : x06.d15.lan

I m trying to setup oracle dataguard for 10G ; both primary and standby databases are in 10GR2.
Configuring Oracle DataGuard using SQL commands – Creating a physical standby database ; I can’t get the log files or Archive logs on my StandBy db.


Checking v$archive_dest_status view on PRIMARY DB, I found the error below :

FROM PRIMARY DB :

I getting an error Oracle not available

SELECT * FROM v$archive_dest_status

DEST_ID 2
DEST_NAME LOG_ARCHIVE_DEST_2
STATUS ERROR
TYPE PHYSICAL
DATABASE_MODE UNKNOWN
RECOVERY_MODE UNKNOWN
PROTECTION_MODE MAXIMUM PERFORMANCE
DESTINATION X06.D15.LAN
STANDBY_LOGFILE_COUNT 0
STANDBY_LOGFILE_ACTIVE 0
ARCHIVED_THREAD# 0
ARCHIVED_SEQ# 0
APPLIED_THREAD# 0
APPLIED_SEQ# 0
ERROR ORA-01034: ORACLE not available
SRL NO
DB_UNIQUE_NAME STANDBY
SYNCHRONIZATION_STATUS CHECK CONFIGURATION
SYNCHRONIZED NO

Logfiles were not applied on my standby DB even I thougth all confirgurations were succesfully done, until I realized the origin of my problem was the version of Oracle on both server. Even both oracle servers were in Oracle 10GR2, please always make sure that they have the SAME VERSION.
I spent a little bit more than hour to patch my Primary DB to 10.2.0.3 and now My dataguard works perfectly.

2- Step by Step :
In this section we will perform our Data Guard WorkShop, which outlines the procedure to create a Physical standby.

Steps are :
- Create a backup of the primary
- Create a Standby Control file
- adjust the pfile of the Primary
- Transfer the datafiles to the standby host
- create same directories for trace files
- edit the pfile of the standby
- mount the standby

Notes :

Both primary and standby servers are on linux redhat : same version of OS

- Primary DB : 10.2.0.3
- Standby DB : 10.2.0.3

- Host Name of Primary DB : arcdb01.es.egwn.lan
- Host Name of Standby DB : x06.d15.lan

Step 1 : Setup Listeners
Tnsnames.ora of the Primary DB :
[oracle@arcdb01 ~]$ export TNS_ADMIN=$ORACLE_HOME/network/admin
[oracle@arcdb01 ~]$ cat /u01/app/oracle/oracle/product/10.2.0/ARCDB01/network/admin/tnsnames.ora
X06.D15.LAN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = X06.D15.LAN)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ARCDB01)
(SERVER = DEDICATED)
)
)
ARCDB01.ES.EGWN.LAN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ARCDB01.ES.EGWN.LAN)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ARCDB01)
(SERVER = DEDICATED)
)
)

Tnsnames.ora of the Standby DB :
[oracle@x06 dbf]$ export TNS_ADMIN=$ORACLE_HOME/network/admin
[oracle@x06 dbf]$ cat $TNS_ADMIN/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/oracle/product/10.2.0/X06/oracle/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ARCDB01.ES.EGWN.LAN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ARCDB01.ES.EGWN.LAN)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ARCDB01)
(SERVER = DEDICATED)
)
)
X06.D15.LAN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = X06.D15.LAN)(PORT = 1521))
)
(CONNECT_DATA =
(SID = ARCDB01)
(SERVER = DEDICATED)
)
)
[oracle@x06 dbf]$
Step 2 : check Listeners status

TNSPING of the STANDBY DB from the PRIMARY DB :

[oracle@arcdb01 ~]$ tnsping x06.d15.lan

TNS Ping Utility for Linux: Version 10.2.0.3.0 – Production on 26-MAR-2010 15:33:12

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
/u01/app/oracle/oracle/product/10.2.0/ARCDB01/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = X06.D15.LAN)(PORT = 1521))) (CONNECT_DATA = (SID = ARCDB01) (SERVER = DEDICATED)))
OK (0 msec)
[oracle@arcdb01 ~]$

TNSPING Of the Primary DB from the Standby DB :

[oracle@x06 bdump]$ tnsping ARCDB01.ES.EGWN.LAN

TNS Ping Utility for Linux: Version 10.2.0.3.0 – Production on 26-MAR-2010 15:34:05

Copyright (c) 1997, 2006, Oracle. All rights reserved.

Used parameter files:
/u01/app/oracle/oracle/product/10.2.0/X06/oracle/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ARCDB01.ES.EGWN.LAN)(PORT = 1521))) (CONNECT_DATA = (SID = ARCDB01) (SERVER = DEDICATED)))
OK (20 msec)
[oracle@x06 bdump]$

Step 3 : Enable archiving and force logging

As a dataguard is dependent on redo to maintain the standby, we must assure that the primary database is in archivelog mode. To place the primary into archivelog, perform the following steps :
[oracle@arcdb01 ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 – Production on Mon Mar 22 20:26:19 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> connect sys as sysdba
Enter password:
Connected.
SQL>
SQL> show parameter spfile

NAME TYPE VALUE
———————————— ———– ——————————
spfile string /u01/app/oracle/oracle/product
/10.2.0/ARCDB01/dbs/spfileARCD
B01.ora
SQL> select force_logging from v$database
2 ;

FOR
—
NO

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/flash_recovery_area/ARCDB01/archivelog/
Oldest online log sequence 1418
Next log sequence to archive 1420
Current log sequence 1420
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 419430400 bytes
Fixed Size 2021280 bytes
Variable Size 117442656 bytes
Database Buffers 293601280 bytes
Redo Buffers 6365184 bytes
Database mounted.
SQL> alter database archivelog ;

Database altered.

SQL> alter database force logging;

Database altered.

SQL> alter database open;

Database altered.

SQL>
SQL>
SQL> select log_mode, force_logging from v$database;

LOG_MODE FOR
———— —
ARCHIVELOG YES

Step 4 : Create a password file
Due to new log transport security and authentification features, it is madatory that every database in a Dataguard configuration utilize a password file. In addition, the password for the sys user must be identical on every system for log transport services to function, If the primary DB does not currently have a password file, create one with the following steps :

[oracle@arcdb01 ~]$ orapwd file=/u00/oracle/product/10.2.0/db_1/dbs/orapwarcdb01 password=orawiss entries=5 force=y

Once the password file is created, you must set the following parameter in the spfile while the database is in nomount state :
alter system set remote_login_passwordfile=exclusive scope=spfile;

Tansfer the password file to the standby DB :
[oracle@x06 dbf]$ cd /u01/app/oracle/oracle/product/10.2.0/X06/oracle/dbs/
[oracle@x06 dbs]$ ls
hc_X06.dat initdw.ora init.ora lkSTANDBY lkX06 orapwarcdb01 orapwX06 spfileX06.ora
[oracle@x06 dbs]$

Step 5 : Configure the primary initilization paraneters

We must configure the parameters to control log transport services and log apply services so that the database will operate in either role with no parameter modification. While the database is mounted on a primry controlfile , the standby parameters are not read and are not into effect, so they will not affect the operation of the database while in the primary role.
The parameters shown here are in bold, to be placed into a primary standby.ora pfile :
[oracle@arcdb01 ~]$ vi /home/oracle/ADVDB/standby.ora

ARCDB01.__db_cache_size=297795584
ARCDB01.__java_pool_size=4194304
ARCDB01.__large_pool_size=4194304
ARCDB01.__shared_pool_size=100663296
ARCDB01.__streams_pool_size=4194304
*.audit_file_dest=’/u01/app/oracle/admin/ARCDB01/adump’
*.background_dump_dest=’/u01/app/oracle/admin/ARCDB01/bdump’
*.compatible=’10.2.0.1.0'
*.control_file_record_keep_time=8
*.control_files=’/u01/app/oracle/oradata/ARCDB01/control01.ctl’,'/u01/app/oracle/oradata/ARCDB01/control02.ctl’,'/u01/app/oracle/oradata/ARCDB01/control03.ctl’
*.core_dump_dest=’/u01/app/oracle/admin/ARCDB01/cdump’
*.db_block_checking=’TRUE’
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’ARCDB01'
*.db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.db_unique_name=’PRIMARY’
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=ARCDB01XDB)’
*.FAL_Client=’ARCDB01.ES.EGWN.LAN’
*.FAL_Server=’X06.D15.LAN’
*.job_queue_processes=10
*.LOG_ARCHIVE_CONFIG=’DG_CONFIG=(PRIMARY,STANDBY)’
*.log_archive_dest_1=’location=/u01/app/oracle/flash_recovery_area/ARCDB01/archivelog/’
*.log_archive_dest_2=’Service=X06.D15.LAN LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=STANDBY’
*.log_archive_dest_state_1=’ENABLE’
*.log_archive_dest_state_2=’DEFER’
*.log_archive_format=’%s_arc_ln%r_db%d_%t.arc’
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=200
*.remote_login_passwordfile=’EXCLUSIVE’
*.SERVICE_NAMES=’PRIMARY’
*.sessions=205
*.sga_max_size=419430400
*.sga_target=419430400
*.Standby_File_Management=’AUTO’
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1'
*.user_dump_dest=’/u01/app/oracle/admin/ARCDB01/udump’
*.utl_file_dir=’/home/oracle/my_logminer’


Step 6 : Create a backup of the primary database
A physical standby can be created using either a hot or cold backup as long as all the necessary archivelogs are available to bring the database to be a consistent state. You can simply use RMAN to backup the primary database.
RMAN> backup database plus archivelog;

Step 7 : Create the Primary spfile and Standby controlfile
First, restard the primary DB using the pfile created in step 5 and then, with the primary DB in either a mount or open state, create a standby controlfile with the following syntaxes :
[oracle@arcdb01 ~]$ vi /home/oracle/ADVDB/standby.ora
[oracle@arcdb01 ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.3.0 – Production on Sun Mar 28 14:53:18 2010

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

SQL> connect sys as sysdba
Enter password:
Connected to an idle instance.
SQL> create spfile from pfile=’/home/oracle/ADVDB/standby.ora’;

File created.

SQL> startup
ORACLE instance started.

Total System Global Area 419430400 bytes
Fixed Size 2073248 bytes
Variable Size 113249632 bytes
Database Buffers 297795584 bytes
Redo Buffers 6311936 bytes
Database mounted.
Database opened.
SQL> alter database create standby controlfile as ‘/home/oracle/ADVDB/standby_x06.ctl’;
Database altered.
SQL>
SQL> create pfile=’/home/oracle/ADVDB/standby.ora’ from spfile;
File created.
SQL>

As you can see in te last command, we create a standby pfile from the spfile, jut to be sure to have the correct pfile when this will be transferred to the standby Db, modified and the used as a pfile of the standby db.
Step 7 : Create the Standby spfile
Remember, at the previous step, we have created the pfile, this should be transferred to the standby host using for example scp or sftp commands.
Now, we should modify the pfile of the standby DB. below, are parameters that needed to be modified in our configuration.

ARCDB01.__java_pool_size=4194304
ARCDB01.__large_pool_size=4194304
ARCDB01.__shared_pool_size=100663296
ARCDB01.__streams_pool_size=4194304
*.audit_file_dest=’/u01/app/oracle/admin/ARCDB01/adump’
*.background_dump_dest=’/u01/app/oracle/admin/ARCDB01/bdump’
*.compatible=’10.2.0.1.0'
*.control_file_record_keep_time=8
*.control_files=’/home/oracle/dbf/standby_x06.ctl’
*.db_file_name_convert=’/u01/app/oracle/oradata/ARCDB01',’/home/oracle/dbf’,'/home/oracle/oradata’,'/home/oracle/dbf’
*.log_file_name_convert=’/u01/app/oracle/oradata/ARCDB01',’/home/oracle/dbf’,'/home/oracle/oradata’,'/home/oracle/dbf’
*.core_dump_dest=’/u01/app/oracle/admin/ARCDB01/cdump’
*.db_block_checking=’TRUE’
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’ARCDB01'
*.db_recovery_file_dest=’/u01/app/oracle/flash_recovery_area’
*.db_recovery_file_dest_size=2147483648
*.db_unique_name=’STANDBY’
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=ARCDB01XDB)’
*.FAL_Server=’ARCDB01.ES.EGWN.LAN’
*.FAL_client=’X06.D15.LAN’
*.job_queue_processes=10
*.LOG_ARCHIVE_CONFIG=’DG_CONFIG=(PRIMARY,STANDBY)’
*.log_archive_dest_1=’location=/u01/app/oracle/flash_recovery_area/ARCDB01/archivelog/’
*.log_archive_dest_2=’Service=ARCDB01.ES.EGWN.LAN
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=PRIMARY’
*.log_archive_dest_state_1=’ENABLE’
*.log_archive_dest_state_2=’ENABLE’
*.log_archive_format=’%s_arc_ln%r_db%d_%t.arc’
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=200
*.remote_login_passwordfile=’EXCLUSIVE’
*.SERVICE_NAMES=’PRIMARY’
*.sessions=205
*.sga_max_size=419430400
*.sga_target=419430400
*.Standby_File_Management=’AUTO’
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1'
*.user_dump_dest=’/u01/app/oracle/admin/ARCDB01/udump’
*.utl_file_dir=’/home/oracle/my_logminer’

Please not that other parameters, such as dump destinations, may need to be modified depending on your environment.
Step 8 : Transfer files to the standby host
Using an operating system utility, transfer the files of the primary DB to the standby DB including :
- The controlfile generated previously (standby_x06.ctl)
- The Standby.ora pfile modified in the previous step
- All primary DB datafiles, you can use the steps below :
from the Primary DB, connect as a DBA :

SQL> SELECT *
FROM Dba_Data_Files ;
FILE_NAME
/u01/app/oracle/oradata/ARCDB01/users01.dbf
/u01/app/oracle/oradata/ARCDB01/sysaux01.dbf
/u01/app/oracle/oradata/ARCDB01/undotbs01.dbf
/u01/app/oracle/oradata/ARCDB01/system01.dbf
/u01/app/oracle/oradata/ARCDB01/rep_for_rman_devexen15.dbf
/u01/app/oracle/oradata/ARCDB01/REP_FOR_BACKRECK_TEST_01.dbf
/u01/app/oracle/oradata/ARCDB01/indx_01.dbf
SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.

from the standby DB :
Using username “oracle”.
Authenticating with public key “imported-openssh-key”
Red Hat Enterprise Linux Server release 5.4 – Linux 2.6.18-164.11.1.el5xen
Four 2.83GHz Intel Pentium Xeon cpus with 4GB RAM
-> x06.d15.lan get *.dbf
Fetching /u01/app/oracle/oradata/ARCDB01/REP_FOR_BACKRECK_TEST_01.dbf to REP_FOR _BACKRECK_TEST_01.dbf
/u01/app/oracle/oradata/ARCDB01/REP_FOR_BACKRECK_TEST_01.dbf 100% 3315MB 24.0MB/s 02:18
Fetching /u01/app/oracle/oradata/ARCDB01/cp_indx_01.dbf to cp_indx_01.dbf
/u01/app/oracle/oradata/ARCDB01/cp_indx_01.dbf 100% 600MB 26.1MB/s 00:23
Fetching /u01/app/oracle/oradata/ARCDB01/indx_01.dbf to indx_01.dbf
/u01/app/oracle/oradata/ARCDB01/indx_01.dbf 100% 600MB 26.1MB/s 00:23
Fetching /u01/app/oracle/oradata/ARCDB01/rep_for_rman_devexen15.dbf to rep_for_rman_devexen15.dbf
/u01/app/oracle/oradata/ARCDB01/rep_for_rman_devexen15.dbf 100% 50MB 25.0MB/s 00:02
Fetching /u01/app/oracle/oradata/ARCDB01/sysaux01.dbf to sysaux01.dbf
/u01/app/oracle/oradata/ARCDB01/sysaux01.dbf 100% 530MB 21.2MB/s 00:25
Fetching /u01/app/oracle/oradata/ARCDB01/system01.dbf to system01.dbf
/u01/app/oracle/oradata/ARCDB01/system01.dbf 100% 520MB 27.4MB/s 00:19
Fetching /u01/app/oracle/oradata/ARCDB01/temp01.dbf to temp01.dbf
/u01/app/oracle/oradata/ARCDB01/temp01.dbf 100% 164MB 23.4MB/s 00:07
Fetching /u01/app/oracle/oradata/ARCDB01/undotbs01.dbf to undotbs01.dbf
/u01/app/oracle/oradata/ARCDB01/undotbs01.dbf 100% 655MB 26.2MB/s 00:25
Fetching /u01/app/oracle/oradata/ARCDB01/users01.dbf to users01.dbf
/u01/app/oracle/oradata/ARCDB01/users01.dbf 100% 5128KB 5.0MB/s 00:00
sftp>
Step 9 : Create an spfile for the standby instance
From the standby DB :
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> create spfile from pfile=’/home/oracle/dbf/standby.ora’;
File created.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 2073248 bytes
Variable Size 134221152 bytes
Database Buffers 276824064 bytes
Redo Buffers 6311936 bytes
SQL> alter database mount standby database;
Database altered.
SQL>

Step 10 : Begin shipping redo to the standby database
if you remember, earlier we deferred log_archive_dest_2 on the primary until we had the standby mounted. Now, it is time to enable that destination and begin shipping redo to the standby.
On the primary, enter the following command :
alter system set log_archive_dest_state_2=enable scope=both;

Sometimes it happens that you face the problem of Archive Gaps where by a range of archived redo log files is created. Archive gaps are created whenever the next archived redo log file generated by the primary database is not applied to the standby database. It is usually recommended to increase the LOG_ARCHIVE_MAX_PROCESSES parameter in order to resolve archive gaps by controlling the number of archive processes the instance uses.
I recommand you to increase your archive max processes parameter at your both primary and standby db :
alter system set log_archive_max_processes=4 scope=both;
From the primary DB, Check the sequence # and the archiving mode by executing following command :
SQL> Archive Log List
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/flash_recovery_area/ARCDB01/archivelog/
Oldest online log sequence 1592
Next log sequence to archive 1594
Current log sequence 1594

Next, peform a log switch on the primary and verify that the transmission of the log was successful :
SQL> alter system switch logfile;

System altered.
SQL> SELECT * FROM v$archive_dest where dest_id=2 ;
DEST_ID 2
DEST_NAME LOG_ARCHIVE_DEST_2
STATUS VALID
BINDING OPTIONAL
NAME_SPACE SYSTEM
TARGET STANDBY
ARCHIVER LGWR
SCHEDULE ACTIVE
DESTINATION X06.D15.LAN
LOG_SEQUENCE 1599
REOPEN_SECS 300
DELAY_MINS 0
MAX_CONNECTIONS 1
NET_TIMEOUT 180
PROCESS LGWR
REGISTER YES
FAIL_DATE
FAIL_SEQUENCE 0
FAIL_BLOCK 0
FAILURE_COUNT 0
MAX_FAILURE 0
ERROR
ALTERNATE NONE
DEPENDENCY NONE
REMOTE_TEMPLATE NONE
QUOTA_SIZE 0
QUOTA_USED 0
MOUNTID 0
TRANSMIT_MODE ASYNCHRONOUS
ASYNC_BLOCKS 61440
AFFIRM NO
TYPE PUBLIC
VALID_NOW YES
VALID_TYPE ONLINE_LOGFILE
VALID_ROLE PRIMARY_ROLE
DB_UNIQUE_NAME STANDBY
VERIFY NO

if the transmission was successful, the status of the destination should be valid. If the status is invalid, investigate the error listed in the error column to correct any issues.

Now, lets make a real test on our dataguard system :

From the primary DB :

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Connected as wissem
SQL> create table test (teste number);
Table created
SQL> insert into test values (1);
1 row inserted
SQL> commit;
Commit complete
SQL>

From the Standby DB :

SQL> select * from dba_users d where d.username = ‘WISSEM’;
USERNAME USER_ID PASSWORD
—————————— ———- ——————————
ACCOUNT_STATUS LOCK_DATE EXPIRY_DA
——————————– ——— ———
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED
—————————— —————————— ———
PROFILE INITIAL_RSRC_CONSUMER_GROUP
—————————— ——————————
EXTERNAL_NAME
——————————————————————————–
WISSEM 61 4531384AFBFF9B98
OPEN
USERS TEMP 21-AUG-08

USERNAME USER_ID PASSWORD
—————————— ———- ——————————
ACCOUNT_STATUS LOCK_DATE EXPIRY_DA
——————————– ——— ———
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED
—————————— —————————— ———
PROFILE INITIAL_RSRC_CONSUMER_GROUP
—————————— ——————————
EXTERNAL_NAME
——————————————————————————–
DEFAULT DEFAULT_CONSUMER_GROUP

SQL> select * from wissem.test;
select * from wissem.test
*
ERROR at line 1:
ORA-00942: table or view does not exist

BACK to the primary DB and perform a log switch :
SQL> alter system switch logfile;
System altered.
SQL> Archive Log List
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/flash_recovery_area/ARCDB01/archivelog/
Oldest online log sequence 1598
Next log sequence to archive 1600
Current log sequence 1600
SQL>exit


Check Log list from the STANDBY DB :

SQL> alter database recover managed standby database disconnect from session
2 ;
Database altered.
SQL> Archive Log List
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/flash_recovery_area/ARCDB01/archivelog/
Oldest online log sequence 1598
Next log sequence to archive 0
Current log sequence 1600
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> select * from wissem.test
2 ;
TESTE
———-
1
SQL>

Now, we can see the results of our new table from the standby DB.
Happy dataguard!
Scridb filter

BACKUP AND RECOVERY TASK

Why and when should I backup my database?

Backup and recovery is one of the most important aspects of a DBA's job. If you lose your company's data, you could very well lose your job. Hardware and software can always be replaced, but your data may be irreplaceable!

Normally one would schedule a hierarchy of daily, weekly and monthly backups, however consult with your users before deciding on a backup schedule. Backup frequency normally depends on the following factors:

    * Rate of data change/ transaction rate
    * Database availability/ Can you shutdown for cold backups?
    * Criticality of the data/ Value of the data to the company
    * Read-only tablespace needs backing up just once right after you make it read-only
    * If you are running in archivelog mode you can backup parts of a database over an extended cycle of days
    * If archive logging is enabled one needs to backup archived log files timeously to prevent database freezes
    * Etc.

Carefully plan backup retention periods. Ensure enough backup media (tapes) are available and that old backups are expired in-time to make media available for new backups. Off-site vaulting is also highly recommended.

Frequently test your ability to recover and document all possible scenarios. Remember, it's the little things that will get you. Most failed recoveries are a result of organizational errors and miscommunication.
[edit] What strategies are available for backing-up an Oracle database?

The following methods are valid for backing-up an Oracle database:

    * Export/Import - Exports are "logical" database backups in that they extract logical definitions and data from the database to a file. See the Import/ Export FAQ for more details.

    * Cold or Off-line Backups - shut the database down and backup up ALL data, log, and control files.

    * Hot or On-line Backups - If the database is available and in ARCHIVELOG mode, set the tablespaces into backup mode and backup their files. Also remember to backup the control files and archived redo log files.

    * RMAN Backups - while the database is off-line or on-line, use the "rman" utility to backup the database.

It is advisable to use more than one of these methods to backup your database. For example, if you choose to do on-line database backups, also cover yourself by doing database exports. Also test ALL backup and recovery scenarios carefully. It is better to be safe than sorry.

Regardless of your strategy, also remember to backup all required software libraries, parameter files, password files, etc. If your database is in ARCHIVELOG mode, you also need to backup archived log files.
[edit] What is the difference between online and offline backups?

A hot (or on-line) backup is a backup performed while the database is open and available for use (read and write activity). Except for Oracle exports, one can only do on-line backups when the database is ARCHIVELOG mode.

A cold (or off-line) backup is a backup performed while the database is off-line and unavailable to its users. Cold backups can be taken regardless if the database is in ARCHIVELOG or NOARCHIVELOG mode.

It is easier to restore from off-line backups as no recovery (from archived logs) would be required to make the database consistent. Nevertheless, on-line backups are less disruptive and don't require database downtime.

Point-in-time recovery (regardless if you do on-line or off-line backups) is only available when the database is in ARCHIVELOG mode.
[edit] What is the difference between restoring and recovering?

Restoring involves copying backup files from secondary storage (backup media) to disk. This can be done to replace damaged files or to copy/move a database to a new location.

Recovery is the process of applying redo logs to the database to roll it forward. One can roll-forward until a specific point-in-time (before the disaster occurred), or roll-forward until the last transaction recorded in the log files.

SQL> connect SYS as SYSDBA
SQL> RECOVER DATABASE UNTIL TIME '2001-03-06:16:00:00' USING BACKUP CONTROLFILE;

RMAN> run {
  set until time to_date('04-Aug-2004 00:00:00', 'DD-MON-YYYY HH24:MI:SS');
  restore database;
  recover database;
}

[edit] My database is down and I cannot restore. What now?

This is probably not the appropriate time to be sarcastic, but, recovery without backups are not supported. You know that you should have tested your recovery strategy, and that you should always backup a corrupted database before attempting to restore/recover it.

Nevertheless, Oracle Consulting can sometimes extract data from an offline database using a utility called DUL (Disk UnLoad - Life is DUL without it!). This utility reads data in the data files and unloads it into SQL*Loader or export dump files. Hopefully you'll then be able to load the data into a working database.

Note that DUL does not care about rollback segments, corrupted blocks, etc, and can thus not guarantee that the data is not logically corrupt. It is intended as an absolute last resort and will most likely cost your company a lot of money!

DUDE (Database Unloading by Data Extraction) is another non-Oracle utility that can be used to extract data from a dead database. More info about DUDE is available at http://www.ora600.nl/.
[edit] How does one backup a database using the export utility?

Oracle exports are "logical" database backups (not physical) as they extract data and logical definitions from the database into a file. Other backup strategies normally back-up the physical data files.

One of the advantages of exports is that one can selectively re-import tables, however one cannot roll-forward from an restored export. To completely restore a database from an export file one practically needs to recreate the entire database.

Always do full system level exports (FULL=YES). Full exports include more information about the database in the export file than user level exports. For more information about the Oracle export and import utilities, see the Import/ Export FAQ.
[edit] How does one put a database into ARCHIVELOG mode?

The main reason for running in archivelog mode is that one can provide 24-hour availability and guarantee complete data recoverability. It is also necessary to enable ARCHIVELOG mode before one can start to use on-line database backups.

Issue the following commands to put a database into ARCHIVELOG mode:

SQL> CONNECT sys AS SYSDBA
SQL> STARTUP MOUNT EXCLUSIVE;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ARCHIVE LOG START;
SQL> ALTER DATABASE OPEN;

Alternatively, add the above commands into your database's startup command script, and bounce the database.

The following parameters needs to be set for databases in ARCHIVELOG mode:

log_archive_start         = TRUE
log_archive_dest_1        = 'LOCATION=/arch_dir_name'
log_archive_dest_state_1  = ENABLE
log_archive_format        = %d_%t_%s.arc

NOTE 1: Remember to take a baseline database backup right after enabling archivelog mode. Without it one would not be able to recover. Also, implement an archivelog backup to prevent the archive log directory from filling-up.

NOTE 2:' ARCHIVELOG mode was introduced with Oracle 6, and is essential for database point-in-time recovery. Archiving can be used in combination with on-line and off-line database backups.

NOTE 3: You may want to set the following INIT.ORA parameters when enabling ARCHIVELOG mode: log_archive_start=TRUE, log_archive_dest=..., and log_archive_format=...

NOTE 4: You can change the archive log destination of a database on-line with the ARCHIVE LOG START TO 'directory'; statement. This statement is often used to switch archiving between a set of directories.

NOTE 5: When running Oracle Real Application Clusters (RAC), you need to shut down all nodes before changing the database to ARCHIVELOG mode. See the RAC FAQ for more details.
[edit] I've lost an archived/online REDO LOG file, can I get my DB back?

The following INIT.ORA/SPFILE parameter can be used if your current redologs are corrupted or blown away. It may also be handy if you do database recovery and one of the archived log files are missing and cannot be restored.

NOTE: Caution is advised when enabling this parameter as you might end-up losing your entire database. Please contact Oracle Support before using it.

_allow_resetlogs_corruption = true

This should allow you to open the database. However, after using this parameter your database will be inconsistent (some committed transactions may be lost or partially applied).

Steps:

    * Do a "SHUTDOWN NORMAL" of the database
    * Set the above parameter
    * Do a "STARTUP MOUNT" and "ALTER DATABASE OPEN RESETLOGS;"
    * If the database asks for recovery, use an UNTIL CANCEL type recovery and apply all available archive and on-line redo logs, then issue CANCEL and reissue the "ALTER DATABASE OPEN RESETLOGS;" command.
    * Wait a couple of minutes for Oracle to sort itself out
    * Do a "SHUTDOWN NORMAL"
    * Remove the above parameter!
    * Do a database "STARTUP" and check your ALERT.LOG file for errors.
    * Extract the data and rebuild the entire database

[edit] User managed backup and recovery

This section deals with user managed, or non-RMAN backups.
[edit] How does one do off-line database backups?

Shut down the database from sqlplus or server manager. Backup all files to secondary storage (eg. tapes). Ensure that you backup all data files, all control files and all log files. When completed, restart your database.

Do the following queries to get a list of all files that needs to be backed up:

select name from sys.v_$datafile;
select member from sys.v_$logfile;
select name from sys.v_$controlfile;

Sometimes Oracle takes forever to shutdown with the "immediate" option. As workaround to this problem, shutdown using these commands:

alter system checkpoint;
shutdown abort
startup restrict
shutdown immediate

Note that if your database is in ARCHIVELOG mode, one can still use archived log files to roll forward from an off-line backup. If you cannot take your database down for a cold (off-line) backup at a convenient time, switch your database into ARCHIVELOG mode and perform hot (on-line) backups.
[edit] How does one do on-line database backups?

Each tablespace that needs to be backed-up must be switched into backup mode before copying the files out to secondary storage (tapes). Look at this simple example.

ALTER TABLESPACE xyz BEGIN BACKUP;
! cp xyzFile1 /backupDir/
ALTER TABLESPACE xyz END BACKUP;

It is better to backup tablespace for tablespace than to put all tablespaces in backup mode. Backing them up separately incurs less overhead. When done, remember to backup your control files. Look at this example:

ALTER SYSTEM SWITCH LOGFILE;   -- Force log switch to update control file headers    
ALTER DATABASE BACKUP CONTROLFILE TO '/backupDir/control.dbf';

NOTE: Do not run on-line backups during peak processing periods. Oracle will write complete database blocks instead of the normal deltas to redo log files while in backup mode. This will lead to excessive database archiving and even database freezes.
[edit] My database was terminated while in BACKUP MODE, do I need to recover?

If a database was terminated while one of its tablespaces was in BACKUP MODE (ALTER TABLESPACE xyz BEGIN BACKUP;), it will tell you that media recovery is required when you try to restart the database. The DBA is then required to recover the database and apply all archived logs to the database. However, from Oracle 7.2, one can simply take the individual datafiles out of backup mode and restart the database.

ALTER DATABASE DATAFILE '/path/filename' END BACKUP;

One can select from V$BACKUP to see which datafiles are in backup mode. This normally saves a significant amount of database down time. See script end_backup2.sql in the Scripts section of this site.

From Oracle9i onwards, the following command can be used to take all of the datafiles out of hotbackup mode:

ALTER DATABASE END BACKUP;

This command must be issued when the database is mounted, but not yet opened.
[edit] Does Oracle write to data files in begin/hot backup mode?

When a tablespace is in backup mode, Oracle will stop updating its file headers, but will continue to write to the data files.

When in backup mode, Oracle will write complete changed blocks to the redo log files. Normally only deltas (change vectors) are logged to the redo logs. This is done to enable reconstruction of a block if only half of it was backed up (split blocks). Because of this, one should notice increased log activity and archiving during on-line backups.

To solve this problem, simply switch to RMAN backups.
[edit] RMAN backup and recovery

This section deals with RMAN backups:
[edit] What is RMAN and how does one use it?

Recovery Manager (or RMAN) is an Oracle provided utility for backing-up, restoring and recovering Oracle Databases. RMAN ships with the database server and doesn't require a separate installation. The RMAN executable is located in your ORACLE_HOME/bin directory.

In fact RMAN, is just a Pro*C application that translates commands to a PL/SQL interface. The PL/SQL calls are statically linked into the Oracle kernel, and does not require the database to be opened (mapped from the ?/rdbms/admin/recover.bsq file).

RMAN can do off-line and on-line database backups. It cannot, however, write directly to tape, but various 3rd-party tools (like Veritas, Omiback, etc) can integrate with RMAN to handle tape library management.

RMAN can be operated from Oracle Enterprise Manager, or from command line. Here are the command line arguments:

Argument     Value          Description
-----------------------------------------------------------------------------
target       quoted-string  connect-string for target database
catalog      quoted-string  connect-string for recovery catalog
nocatalog    none           if specified, then no recovery catalog
cmdfile      quoted-string  name of input command file
log          quoted-string  name of output message log file
trace        quoted-string  name of output debugging message log file
append       none           if specified, log is opened in append mode
debug        optional-args  activate debugging
msgno        none           show RMAN-nnnn prefix for all messages
send         quoted-string  send a command to the media manager
pipe         string         building block for pipe names
timeout      integer        number of seconds to wait for pipe input
-----------------------------------------------------------------------------

Here is an example:

[oracle@localhost oracle]$ rman
Recovery Manager: Release 10.1.0.2.0 - Production
Copyright (c) 1995, 2004, Oracle.  All rights reserved.

RMAN> connect target;

connected to target database: ORCL (DBID=1058957020)

RMAN> backup database;
...

[edit] How does one backup and restore a database using RMAN?

The biggest advantage of RMAN is that it only backup used space in the database. RMAN doesn't put tablespaces in backup mode, saving on redo generation overhead. RMAN will re-read database blocks until it gets a consistent image of it. Look at this simple backup example.

rman target sys/*** nocatalog
run {
  allocate channel t1 type disk;
  backup
    format '/app/oracle/backup/%d_t%t_s%s_p%p'
      (database);
   release channel t1;
}

Example RMAN restore:

rman target sys/*** nocatalog
run {
  allocate channel t1 type disk;
  # set until time 'Aug 07 2000 :51';
  restore tablespace users;
  recover tablespace users;
  release channel t1;
}

The examples above are extremely simplistic and only useful for illustrating basic concepts. By default Oracle uses the database controlfiles to store information about backups. Normally one would rather setup a RMAN catalog database to store RMAN metadata in. Read the Oracle Backup and Recovery Guide before implementing any RMAN backups.

Note: RMAN cannot write image copies directly to tape. One needs to use a third-party media manager that integrates with RMAN to backup directly to tape. Alternatively one can backup to disk and then manually copy the backups to tape.
[edit] How does one backup and restore archived log files?

One can backup archived log files using RMAN or any operating system backup utility. Remember to delete files after backing them up to prevent the archive log directory from filling up. If the archive log directory becomes full, your database will hang! Look at this simple RMAN backup scripts:

RMAN> run {
2> allocate channel dev1 type disk;
3> backup
4>   format '/app/oracle/archback/log_%t_%sp%p'
5>   (archivelog all delete input);
6> release channel dev1;
7> }

The "delete input" clause will delete the archived logs as they are backed-up.

List all archivelog backups for the past 24 hours:

RMAN> LIST BACKUP OF ARCHIVELOG FROM TIME 'sysdate-1';

Here is a restore example:

RMAN> run {
2> allocate channel dev1 type disk;
3> restore (archivelog low logseq 78311 high logseq 78340 thread 1 all);
4> release channel dev1;
5> }

[edit] How does one create a RMAN recovery catalog?

Start by creating a database schema (usually called rman). Assign an appropriate tablespace to it and grant it the recovery_catalog_owner role. Look at this example:

sqlplus sys
SQL> create user rman identified by rman;
SQL> alter user rman default tablespace tools temporary tablespace temp;
SQL> alter user rman quota unlimited on tools;
SQL> grant connect, resource, recovery_catalog_owner to rman;
SQL> exit;

Next, log in to rman and create the catalog schema. Prior to Oracle 8i this was done by running the catrman.sql script.

rman catalog rman/rman
RMAN> create catalog tablespace tools;
RMAN> exit;

You can now continue by registering your databases in the catalog. Look at this example:

rman catalog rman/rman target backdba/backdba
RMAN> register database;

One can also use the "upgrade catalog;" command to upgrade to a new RMAN release, or the "drop catalog;" command to remove an RMAN catalog. These commands need to be entered twice to confirm the operation.
[edit] How does one integrate RMAN with third-party Media Managers?

The following Media Management Software Vendors have integrated their media management software with RMAN (Oracle Recovery Manager):

    * Veritas NetBackup - http://www.veritas.com/
    * EMC Data Manager (EDM) - http://www.emc.com/
    * HP OMNIBack/ DataProtector - http://www.hp.com/
    * IBM's Tivoli Storage Manager (formerly ADSM) - http://www.tivoli.com/storage/
    * EMC Networker - http://www.emc.com/
    * BrightStor ARCserve Backup - http://www.ca.com/us/data-loss-prevention.aspx
    * Sterling Software's SAMS:Alexandria (formerly from Spectralogic) - http://www.sterling.com/sams/
    * SUN's Solstice Backup - http://www.sun.com/software/whitepapers/backup-n-storage/
    * CommVault Galaxy - http://www.commvault.com/
    * etc...

The above Media Management Vendors will provide first line technical support (and installation guides) for their respective products.

A complete list of supported Media Management Vendors can be found at: http://www.oracle.com/technology/deploy/availability/htdocs/bsp.htm

When allocating channels one can specify Media Management spesific parameters. Here are some examples:

Netbackup on Solaris:

allocate channel t1 type 'SBT_TAPE'  PARMS='SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so.1';

Netbackup on Windows:

allocate channel t1 type 'SBT_TAPE' send "NB_ORA_CLIENT=client_machine_name";

Omniback/ DataProtector on HP-UX:

allocate channel t1 type 'SBT_TAPE' PARMS='SBT_LIBRARY= /opt/omni/lib/libob2oracle8_64bit.sl';

or:

allocate channel 'dev_1' type 'sbt_tape' parms 'ENV=OB2BARTYPE=Oracle8,OB2APPNAME=orcl,OB2BARLIST=machinename_orcl_archlogs)';

[edit] How does one clone/duplicate a database with RMAN?

The first step to clone or duplicate a database with RMAN is to create a new INIT.ORA and password file (use the orapwd utility) on the machine you need to clone the database to. Review all parameters and make the required changed. For example, set the DB_NAME parameter to the new database's name.

Secondly, you need to change your environment variables, and do a STARTUP NOMOUNT from sqlplus. This database is referred to as the AUXILIARY in the script below.

Lastly, write a RMAN script like this to do the cloning, and call it with "rman cmdfile dupdb.rcv":

connect target sys/secure@origdb
connect catalog rman/rman@catdb
connect auxiliary /

run {
set newname for datafile 1 to '/ORADATA/u01/system01.dbf';
set newname for datafile 2 to '/ORADATA/u02/undotbs01.dbf';
set newname for datafile 3 to '/ORADATA/u03/users01.dbf';
set newname for datafile 4 to '/ORADATA/u03/indx01.dbf';
set newname for datafile 5 to '/ORADATA/u02/example01.dbf';

allocate auxiliary channel dupdb1 type disk;
set until sequence 2 thread 1;

duplicate target database to dupdb
logfile
  GROUP 1 ('/ORADATA/u02/redo01.log') SIZE 200k REUSE,
  GROUP 2 ('/ORADATA/u03/redo02.log') SIZE 200k REUSE;
}

The above script will connect to the "target" (database that will be cloned), the recovery catalog (to get backup info), and the auxiliary database (new duplicate DB). Previous backups will be restored and the database recovered to the "set until time" specified in the script.

Notes: the "set newname" commands are only required if your datafile names will different from the target database.

The newly cloned DB will have its own unique DBID.
[edit] Can one restore RMAN backups without a CONTROLFILE and RECOVERY CATALOG?

Details of RMAN backups are stored in the database control files and optionally a Recovery Catalog. If both these are gone, RMAN cannot restore the database. In such a situation one must extract a control file (or other files) from the backup pieces written out when the last backup was taken. Let's look at an example:

Let's take a backup (partial in our case for ilustrative purposes):

$ rman target / nocatalog
Recovery Manager: Release 10.1.0.2.0 - 64bit Production
Copyright (c) 1995, 2004, Oracle.  All rights reserved.

connected to target database: ORCL (DBID=1046662649)
using target database controlfile instead of recovery catalog

RMAN> backup datafile 1;

Starting backup at 20-AUG-04
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oradata/orcl/system01.dbf
channel ORA_DISK_1: starting piece 1 at 20-AUG-04
channel ORA_DISK_1: finished piece 1 at 20-AUG-04
piece handle=
/flash_recovery_area/ORCL/backupset/2004_08_20/o1_mf_nnndf_TAG20040820T153256_0lczd9tf_.bkp comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current controlfile in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 20-AUG-04
channel ORA_DISK_1: finished piece 1 at 20-AUG-04
piece handle=
/flash_recovery_area/ORCL/backupset/2004_08_20/o1_mf_ncsnf_TAG20040820T153256_0lczfrx8_.bkp comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 20-AUG-04[/code]

Now, let's destroy one of the control files:

SQL> show parameters CONTROL_FILES
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /oradata/orcl/control01.ctl,
                                                 /oradata/orcl/control02.ctl,
                                                 /oradata/orcl/control03.ctl
SQL> shutdown abort;
ORACLE instance shut down.
SQL> ! mv /oradata/orcl/control01.ctl /tmp/control01.ctl</pre>

Now, let's see if we can restore it. First we need to start the databaase in NOMOUNT mode:

SQL> startup NOMOUNT
ORACLE instance started.

Total System Global Area  289406976 bytes
Fixed Size                  1301536 bytes
Variable Size             262677472 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes</pre>

Now, from SQL*Plus, run the following PL/SQL block to restore the file:

DECLARE
  v_devtype   VARCHAR2(100);
  v_done      BOOLEAN;
  v_maxPieces NUMBER;

  TYPE t_pieceName IS TABLE OF varchar2(255) INDEX BY binary_integer;
  v_pieceName t_pieceName;
BEGIN
  -- Define the backup pieces... (names from the RMAN Log file)
  v_pieceName(1) :=
     '/flash_recovery_area/ORCL/backupset/2004_08_20/o1_mf_ncsnf_TAG20040820T153256_0lczfrx8_.bkp';
  v_pieceName(2) :=
     '/flash_recovery_area/ORCL/backupset/2004_08_20/o1_mf_nnndf_TAG20040820T153256_0lczd9tf_.bkp';
  v_maxPieces    := 2;

  -- Allocate a channel... (Use type=>null for DISK, type=>'sbt_tape' for TAPE)
  v_devtype := DBMS_BACKUP_RESTORE.deviceAllocate(type=>NULL, ident=>'d1');

  -- Restore the first Control File...
  DBMS_BACKUP_RESTORE.restoreSetDataFile;

  -- CFNAME mist be the exact path and filename of a controlfile taht was backed-up
  DBMS_BACKUP_RESTORE.restoreControlFileTo(cfname=>'/app/oracle/oradata/orcl/control01.ctl');

  dbms_output.put_line('Start restoring '||v_maxPieces||' pieces.');
  FOR i IN 1..v_maxPieces LOOP
    dbms_output.put_line('Restoring from piece '||v_pieceName(i));
    DBMS_BACKUP_RESTORE.restoreBackupPiece(handle=>v_pieceName(i), done=>v_done, params=>null);
    exit when v_done;
  END LOOP;

  -- Deallocate the channel...
  DBMS_BACKUP_RESTORE.deviceDeAllocate('d1');
EXCEPTION
   WHEN OTHERS THEN
      DBMS_BACKUP_RESTORE.deviceDeAllocate;
      RAISE;
END;
/

Let's see if the controlfile was restored:

SQL> ! ls -l /oradata/orcl/control01.ctl
-rw-r-----   1 oracle   dba      3096576 Aug 20 16:45 /oradata/orcl/control01.ctl[/code]

We should now be able to MOUNT the database and continue recovery...

SQL> ! cp /oradata/orcl/control01.ctl /oradata/orcl/control02.ctl

SQL> ! cp /oradata/orcl/control01.ctl /oradata/orcl/control03.ctl

SQL> alter database mount;

SQL> recover database using backup controlfile;
ORA-00279: change 7917452 generated at 08/20/2004 16:40:59 needed for thread 1
ORA-00289: suggestion :
/flash_recovery_area/ORCL/archivelog/2004_08_20/o1_mf_1_671_%u_.arc
ORA-00280: change 7917452 for thread 1 is in sequence #671

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oradata/orcl/redo02.log
Log applied.
Media recovery complete.

Database altered.

SQL> alter database open resetlogs;

Database altered.






How to perform and recover from a cold backup
By Nicolas de Fontenay 18 Comments
Categories: Maintenance

Back to basic.

The cold backup is the easiest backup that could be performed.

It can’t really be used in a backup strategy for businesses with critical systems because there is a risk of loss of data up to 24h.

It is useful anyway to know how to perform it because it makes duplicating a database useful and can save lives sometimes.

Perform a cold backup

A cold back requires a clean shutdown of the database.

shutdown immediate or normal are the only shutdown allowed.

A database cold backed up after a shutdown abort will result into fuzzy files which can’t be recovered.

The reason is that the datafiles must have had a checkpoint performed (which is done with shutdown immediate).

When the clean shutdown is done, copy the oradata folders to a new location.

After the files are copied, the database can be started up.

Recover from a cold backup

Depending on the structure given on the server to recover, the recovery can involve renaming datafiles and controlfiles.

1) If the service doesn’t exist yet, issue the oradim command:

oradim -NEW -SID SAME_AS_BACKED_UP_DB -pfile FULLPATH\init.ora -syspass somepassword

If you have taken the measure to copy the password file and initialization parameter from your primary server to the server to recover in ORACLE_HOME\database, then the options -pfile and -syspass are not required.

2) Copy the oradata folders to the exact same location from primary to the server to recover.

If the oradata files are located in the same drives, same folder structure, the recovery stops here and a startup can be issued.

3) If their are hard disk constraints (more drive on primary than on recovered server or not enough disk space), it must be required to move some datafiles from one drive to another.

In the case a data file has been moved to new location, the database must be started in mount mode:

C:\set oracle_sid= mySID

C:\sqlplus / as sysdba

SQL> startup mount; (if the database doesn’t mount, the location to thepfile can be specified by adding the parameter spfile=’LOCATION\SPFILESSID.ora’)

and a rename command must be issued for each files relocated:

SQL> alter database rename file ‘OLD_LOCATION\DATAFILE_1.ora’ to ‘NEW_LOCATION\DATAFIE_1.ora’;

SQL>alter database datafile ‘NEW_LOCATION\DATAFILE1.ora’ online;

SQL>alter database open;









User Managed Backup --- Oracle
User Managed Backup

1. Physical Backup

1. Cold Backup (Consistent Backup)
2. Hot Backup (Inconsistent Backup)

2. Logical Backup

1. Export / Import
2. Expdp /Impdp (available on oracle10g onwards)

Now we see the physical backup.


Cold Backup: (Consistent Backup)

A cold (or off-line) backup is a backup performed while the database is off-line and unavailable to its users. Cold backups can be taken regardless if the database is in ARCHIVELOG or NOARCHIVELOG mode.It is easier to restore from off-line backups as no recovery (from archived logs) would be required to make the database consistent.


Why you say cold backup is consistent backup?


After shutdown the database we have taken the cold backup. During this time all datafile headers SCN are same. When we restore the cold backup, no redo is needed in case of recovery.We had taken backup datafile, logfile, controlfile, parameter file & password file.


Cold Backup Steps:

1. Get the file path information using below query

Select name from v$datafile;
select member from v$logfile;
select name from v$controlfile;

2.Taken the password file & parameter file backup ( Optional)
3.Alter system switch logfile;
4.Shutdown immediate
5.Copy all the data files /control files /log files using os command & placed in backup path.
6.Startup the database.

Hot Backup (Inconsistent backup)

A hot (or on-line) backup is a backup performed while the database is open and available for use (read and write activity). Except for Oracle exports, one can only do on-line backups when the database is ARCHIVELOG mode.

Why you say Hot backup is inconsistent backup?

While database running we have taken the hot backup. During this time backup tablespace datafile headers SCN are not same with another tablespace datafiles. When we restore the hot backup, redo is needed for recovery.

Hot Backup Steps:

1.Get the file path information using below query.
Select tablespace_name, file_name from dba_data_files order by 1;

2. Put the tablespace in begin backup mode Using os command to copy the datafiles belongs to begin backup mode tablespace & placed in backup path.
3.Once copied the datafile, release the tablespace from begin backup mode to end backup
4.Repeat the steps 1-3 for all your tablespaces.
5.Taken the controlfile backup

Alter Database backup controlfile to ‘/u01/backup/control01.ctl’; ---> à Binary format

Below one is readable format. (Used for cloning)

Alter Database backup controlfile to trace;
Alter database backup controlfile to trace as ‘/u01/backup/control01.ora’;

6.Backup all your archive log files between the previous backup and the new backup as well.
7.Taken the password file & parameter file backup ( Optional)

Example:
steps:

2.Alter tablespace system begin backup;
To ensure the begin backup mode tablespace using below query
Select * from v$backup; (refer the Change#, Time column)

3. Host cp /u01/oracle/raja/system1.DBF /u01/backup/system01.dbf à using os command to copy the datafile.
4. Alter tablespace system end backup;
To ensure the begin backup mode tablespace using below query
Select * from v$backup;


Hot Backup internal operations:

Note: While I am trying to write “during hot backup what are the internal operations going on?” I have referred several notes, but below one is very clear and nice one.
Reference: http://knol.google.com/k/franck-pachot/oracle-begin-backupend-backup/17uabcrki6uux/3#

During backup mode, for each datafile in the tablespace, here is what happens:

1- When BEGIN BACKUP is issued:

•The hot backup flag in the datafile headers is set, so that the copy is identified to be a hot backup copy. This is to manage the backup consistency issue when the copy will be used for a recovery.
•A checkpoint is done for the tablespace, so that in case of recovery, no redo generated before that point will be applied. Begin backup command completes only when checkpoint is done.

2- During backup mode:
•The datafile header is frozen so that whenever it is copied, it reflects the checkpoint SCN that was at the beginning of the backup. Then, when the copy will be restored, Oracle knows that it needs to start recovery at that SCN to apply the archived redo logs. This is to avoid the header inconsistency issue.
That means that any further checkpoints do not update the datafile header SCN (but they do update a 'backup' SCN)
•Each first modification to a block in buffer cache will write the full block into the redo thread (in addition to the default behavior that writes only the change vector).
This is to avoid the fractured block issue. There may be a fractured block in the copy, but it will be overwritten during the recovery with the full block image.


That means that everything goes as normal except for two operations:
- at checkpoint the datafile header SCN is not updated
- when updating a block, the first time it is updated since it came in the buffer cache, the whole before image of the block is recorded in redo
- direct path writes do not go through the buffer cache, but they always write full blocks and then full block is written to redo log (if not in nologging)

3- When END BACKUP is issued:
•A record that marks the end of backup is written to the redo thread so that if the copy is restored and recovered, it cannot be recovered earlier than that point. This is to avoid the backup consistency issue.
•The hot backup flag in the datafile headers is unset.
•The header SCN is written with the current one.


Remarks:

1.The fractured block is not frequent as it happens only if the i/o for the copy is done at the same time on the same block as the i/o for the update. But the only mean to avoid the problem is to do that full logging of block for each block, just in case.

2.If the OS I/O size is multiple of the Oracle block size (e.g backup done with dd bs=1M), that supplemental logging is not useful as fractured blocks cannot happen.

3.The begin backup checkpoint is mandatory to manage the fractured block issue: as Oracle writes the whole before image of the block, it needs to ensure that it does not overwrite a change done previously. With the checkpoint at the beginning, it is sure that no change vector preceding the begin backup has to be applied be applied.

4.The supplemental logging occurs when accessing the block for the first time in the buffer cache. If the same block is reloaded again in the buffer cache, supplemental logging will occur again. I haven't seen that point documented, but a test case doing a 'flush buffer_cache' proves that.


I Hope this article helped you to understand the user managed physical backup.Suggestions are welcome.