Installing Oracle 11g with ASM using Block Devices on RHEL5.2

ASM Installation

Automatic Storage Management (ASM) simplifies administration of Oracle related files by allowing the administrator to reference disk groups rather than individual disks and files, which ASM manages internally.

Install RedHat Linux 5.2 with 25GB HDD and then Add 3 virtual Hard disks of 5GB each to this virtual machine.

Partition the Disks: Block devices require the candidate disks to be partitioned before they can be accessed. In this example, three 5Gig VMware virtual disks are to be used for the ASM storage. The following text shows the "/dev/sdb" disk being partitioned.

# fdisk /dev/sdb (Similarly do it for /dev/sdc & /dev/sdd)Command (m for help): np primary partition (1-4)pPartition number (1-4): 1First cylinder (1-1305, default 1):Using default value 1Last cylinder or +size or +sizeM or +sizeK (1-1305, default 1305):Using default value 1305Command (m for help): wThe partition table has been altered!Calling ioctl() to re-read partition table.Syncing

Disk Device Setup

# ls -la /dev/sd*

No need to setup raw for RHEL5 as it is deprecated. On Both Nodes

Add to the file 50-udev.rules at the bottom of the file:

# cd /etc/udev/rules.d

# vi 50-udev.rules

KERNEL=="sdb1", OWNER="oracle", GROUP="oinstall", MODE="0640"KERNEL=="sdc1", OWNER="oracle", GROUP="oinstall", MODE="0640"KERNEL=="sdd1", OWNER="oracle", GROUP="oinstall", MODE="0640"

# partprobe

# ls -la /dev/sd*

Check your kernel version by running the following command:

uname -r

# uname -r2.6.18-8.el5

Required package versions (or later):

rpm -qa|grep compat-libstdc++-33-3.2.3-rpm -qa|grep elfutils-libelf-0.125-rpm -qa|grep elfutils-libelf-devel-0.125-rpm -qa|grep glibc-2.5-rpm -qa|grep glibc-devel-2.5-rpm -qa|grep glibc-common-2.5-rpm -qa|grep gcc-rpm -qa|grep gcc-c++-rpm -qa|grep kernel-headersrpm -qa|grep libgcc-rpm -qa|grep libaio-rpm -qa|grep libaio-devel-0.3.106-rpm -qa|grep libstdc++-rpm -qa|grep libstdc++-devel-rpm -qa|grep unixODBC-rpm -qa|grep unixODBC-devel-rpm -qa|grep sysstat-rpm -qa|grep binutils-2.17.50.0.6-rpm -qa|grep make-3.81

If you've performed a "default RPMs" install as suggested by Oracle, there are still some required packages that must be installed:

# rpm -ivh gcc-4.1.2-42.el5.i386.rpm# rpm -ivh unixODBC-2.2.11-7.1.i386.rpm# rpm -ivh unixODBC-devel-2.2.11-7.1.i386.rpm# rpm -ivh libaio-devel-0.3.106-3.2.i386.rpm

Configuring Linux for OracleNow that the Linux software is installed, you need to configure it for Oracle. This section walks through the steps required to configure Linux for Oracle Database 11g Release 1.

Create the Oracle Groups and User AccountNext, create the Linux groups and user account that will be used to install and maintain the Oracle Database 11g Release 1 software. The useraccount will be called oracle, and the groups will be oinstall and dba. Execute the following commands as root:

cat /etc/groupcat /etc/passwd

/usr/sbin/groupadd oinstall/usr/sbin/groupadd dba/usr/sbin/useradd -m -g oinstall -G dba oracle

[root@server ~]# id oracleuid=500(oracle) gid=500(oinstall) groups=500(oinstall),501(dba)

Set the password on the oracle account:passwd oracle

Create DirectoriesNow create directories to store the Oracle Database 11g Release 1 software and the database files. This guide adheres to the Optimal FlexibleArchitecture (OFA) for the naming conventions used in creating the directory structure. For more information on OFA standards, see Appendix Dof the Oracle Database Installation Guide 11g Release 1 (11.1) for Linux.The following assumes that the directories are being created in the root filesystem. This is done for the sake of simplicity and is not recommendedas a general practice. These directories would normally be created as separate filesystems.

Issue the following commands as root:

mkdir -p /u01/app/oracle/product/11.1.0/db_1chown -R oracle:oinstall /u01chmod -R 775 /u01

Configuring the Linux Kernel ParametersOracle Database 11g Release 1 requires the kernel parameter settings shown below. The values given are minimums, so if your system uses a larger value, don't change it. Linux allows modification of most kernel parameters while the system is up and running, so there's no need to reboot the system after making kernel parameter changes.

[root@server ~]# vi /etc/sysctl.conf

kernel.shmall = 2097152kernel.shmmax = 536870912kernel.shmmni = 4096kernel.sem = 250 32000 100 128fs.file-max = 65536net.ipv4.ip_local_port_range = 1024 65000net.core.rmem_default=4194304net.core.wmem_default=262144net.core.rmem_max=4194304net.core.wmem_max=262144

[root@server ~]# /sbin/sysctl -p

net.ipv4.ip_forward = 0net.ipv4.conf.default.rp_filter = 1net.ipv4.conf.default.accept_source_route = 0kernel.sysrq = 0kernel.core_uses_pid = 1net.ipv4.tcp_syncookies = 1kernel.msgmnb = 65536kernel.msgmax = 65536kernel.shmmax = 4294967295kernel.shmall = 268435456kernel.shmall = 2097152kernel.shmmax = 536870912kernel.shmmni = 4096kernel.sem = 250 32000 100 128fs.file-max = 65536net.ipv4.ip_local_port_range = 1024 65000net.core.rmem_default = 4194304net.core.wmem_default = 262144net.core.rmem_max = 4194304net.core.wmem_max = 262144[root@server ~]#

Setting shell limits for the oracle userOracle recommends setting limits on the number of processes and open files each Linux account may use. To make these changes, cut and paste the following commands as root:

cat >> /etc/security/limits.conf <<EOForacle soft nproc 2047oracle hard nproc 16384oracle soft nofile 1024oracle hard nofile 65536

EOF

cat >> /etc/pam.d/login <<EOFsession required /lib/security/pam_limits.soEOF

Change the default profile for bash and ksh as well as the default login script for cshell.cat >> /etc/profile <<EOFif [ \$USER = "oracle" ]; thenif [ \$SHELL = "/bin/ksh" ]; thenulimit -p 16384ulimit -n 65536elseulimit -u 16384 -n 65536fiumask 022fiEOF

cat >> /etc/csh.login <<EOFif ( \$USER == "oracle" ) thenlimit maxproc 16384limit descriptors 65536umask 022endifEOF

Hosts FileThe /etc/hosts file must contain a fully qualified name for the server:

127.0.0.1 localhost.localdomain localhost192.168.1.100 server.rhel5.com server

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

# Oracle SettingsTMP=/tmp; export TMPTMPDIR=$TMP; export TMPDIRORACLE_BASE=/u01/app/oracle; export ORACLE_BASEORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1; export ORACLE_HOMEORACLE_SID=orcl; export ORACLE_SIDORACLE_TERM=xterm; export ORACLE_TERMPATH=/usr/sbin:$PATH; export PATHPATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATHCLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATHif [ $USER = "oracle" ]; thenif [ $SHELL = "/bin/ksh" ]; thenulimit -p 16384ulimit -n 65536elseulimit -u 16384 -n 65536fifi

[oracle@server ~]$ env|grep ORAORACLE_SID=orclORACLE_BASE=/u01/app/oracleORACLE_TERM=xtermORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1[oracle@server ~]$

Make Staging Directory

[root@server ~]# mkdir -p /stage[root@server ~]# chown -R oracle:oinstall /stage

Copy 11g database to staging directorypwd/stage/database[oracle@server database]$ ./runInstaller &

Install Oracle Software

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

$ ./runInstaller &

Patch it to 11.1.0.7

Start the listener

ASM Instance Creation

dbca &

As a root run the below command /u01/app/oracle/product/11.1.0/db_1/bin/localconfig add

/etc/oracle does not exist. Creating it now.Successfully accumulated necessary OCR keys.Creating OCR keys for user 'root', privgrp 'root'..Operation successful.Configuration for local CSS has been initializedAdding to inittabStartup will be queued to init within 90 seconds.Checking the status of new Oracle init process...Expecting the CRS daemons to be up within 600 seconds.CSS is active on these nodes.serverCSS is active on all nodes.Oracle CSS service is installed and running under init(1M)#

ASM Disk Group Creation

Create Disk Droups after discovering the path of disks as/dev/sd*

Database Creation

Create database with dbca& but select ASM file system in the installation giving right password of asm instance as asm123

Install Rolewrap & make following aliases

alias sqlpus='rlwrap sqlplus / as sysdba'alias sqlpas='rlwrap sqlplus / as sysasm'alias rmn='rlwrap rman target /'

Chapter 2

backup datafile '/u01/app/oracle/oradata/orcl/users01.dbf';

backup datafile 4;

restore datafile 4;

recover datafile 4;

select * from dba_tablespaces;

alter tablespace users offline;

alter tablespace users online;

run{CROSSCHECK BACKUPSET; # To check available and expired backup sets.CROSSCHECK ARCHIVELOG ALL; # To check available and expired Archive LogsDELETE NOPROMPT EXPIRED BACKUP; # It will delete expired(old/not exists physicaly) backupsDELETE NOPROMPT EXPIRED ARCHIVELOG ALL; # It will delete expired archive logsSQL ‘ALTER SYSTEM ARCHIVE LOG CURRENT’; # Switch LogBACKUP DATABASE PLUS ARCHIVELOG; # Backup the whole Database plus archive filesDELETE NOPROMPT OBSOLETE; # It will remove old backups according to the redundancy period.}

Chapter 3

DRILL FOR CREATING RMAN CATALOG

Snapshot to 11g database, increase RAM to 3GB. Before creating the catalog user we want to create a catalog database cat & tablespace rcat_ts to hold the catalog. Create database using dbca & custom create tablespace rcat_ts having 100MB datafile and give 500MB memory to this database.SQL> create tablespace rcat_ts datafile '/u01/app/oracle/oradata/cat/rcat_ts.dbf' size 100m autoextend on next 1m;SQL> Create user rcat identified by rcatDefault tablespace rcat_tsTemporary tablespace tempQuota unlimited on rcat_ts;SQL> Grant recovery_catalog_owner to rcat;

STEPS TO BE FOLLOWED BEFORE CONNECTING

Database should be in archive log modeConfigure Listeners & Service names of both the target and catalog database

Recovery catalog database should be always up and running

Target database should be in the mount phase (to read control files)

CREATING RMAN CATALOG

$ RMANRMAN > Connect target sys/oracle@orclRMAN > Connect catalog rcat/rcat@catRMAN > Create catalog tablespace rcat_tsRMAN > Register database

DRILL FOR WORKING WITH RMAN STORED SCRIPTS IN RECOVERY CATALOG

Within RMAN you can save commands and execute it whenever you wish. Stored scripts bring this facility where we should not bother about OS scripts whether RMAN client has proper permission on it or not.

Stored Scripts can be two types:1) Global Stored Scripts:A global stored script can be run against any database registered in the recovery catalog, if the RMAN client is connected to the recovery catalog and a target database.

2) Local Stored Scripts:A local stored script is associated with the target database to which RMAN is connected when the script is created, and can only be executed when you are connected to that target database.Common tasks for both local & global scripts are :

a)Create the script b)Run the scriptc)Update the script

d)Delete the scripte)Print the scriptf)Listing all the script names[Commands are not working]

RMAN> create script backup_wholecomment "backup whole database and logs"{BACKUP INCREMENTAL LEVEL 0 TAG b_whole_10DATABASE PLUS ARCHIVELOG;}

created script backup_whole

Executing local and Global scripts

RMAN> run { execute script backup_whole;}

executing script: backup_whole

RMAN> run {execute global script backup_whole;}

To UPDATE a local script Inorder to update we make use of REPLACE command

RMAN>REPLACE SCRIPT backup_whole{BACKUP DATABASE PLUS ARCHIVELOG;}

To DELETE the local script

RMAN>DELETE SCRIPT query_bakcup

RMAN> print script backup_whole;

printing stored script: backup_whole {BACKUP INCREMENTAL LEVEL 0 TAG b_whole_10DATABASE PLUS ARCHIVELOG;

}

RMAN> print script backup_whole to file 'vineet.txt';

script backup_whole written to file vineet.txt

RMAN> host;

$ cat vineet.txt {BACKUP INCREMENTAL LEVEL 0 TAG b_whole_10DATABASE PLUS ARCHIVELOG;}$

RMAN> list script names;

List of Stored Scripts in Recovery Catalog

Scripts of Target Database ORCL

Script Name Description ----------------------------------------------------------------------- backup_whole backup whole database and logs

RMAN>

RMAN> list global script names;

List of Stored Scripts in Recovery Catalog

No scripts in recovery catalog

SQL> select * from rc_stored_script;

DB_KEY DB_NAME SCRIPT_NAME SCRIPT_COMMENT---------- -------- ---------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------------------------------

1 ORCL backup_whole backup whole database and logs

Creating an RMAN Virtual Private Catalog

1. Create an RMAN base catalog

2. Grant RECOVERY_CATALOG_OWNER to VPC owner

3. Create owner vpc ownerSQL> create user vpcowner identified by vpcowner;

SQL> GRANT RECOVERY_CATALOG_OWNER to vpcowner;

4. Grant REGISTER to the VPC ownerRMAN> CONNECT CATALOG rcat/rcat@cat;RMAN> GRANT REGISTER DATABASE TO vpcowner;

5. Grant CATALOG FOR DATABASE to the VPC owner:RMAN> GRANT CATALOG FOR DATABASE orcl TO vpcowner;

6. Create a virtual catalog for 11g clientsRMAN> CONNECT CATALOG vpcowner/vpcowner@cat;RMAN> CREATE VIRTUAL CATALOG;

$ rlwrap rman target sys/oracle@orcl catalog rcat/rcat@cat

RMAN> unregister database;

7. REGISTER a database that was not previously cataloged:

RMAN> connect target sys/oracle@orcl

connected to target database: ORCL (DBID=1223202247)

RMAN> connect catalog vpcowner/vpcowner@cat

connected to recovery catalog database

RMAN> REGISTER DATABASE;

8. Use the virtual catalog:

RMAN> BACKUP DATABASE;

$ sqlplus vpcowner/vpcowner@cat

select distinct db_name from dbinc;

Chapter 5

backup as backupsetformat '/home/oracle/df_%d_%s_%p.bus'tablespace users;

BACKUP AS COPY DATAFILE '/u01/app/oracle/oradata/orcl/users01.dbf';BACKUP AS COPY ARCHIVELOG LIKE '/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2009_09_12/%_.arc';

BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;

BACKUP INCREMENTAL LEVEL 0 DATABASE;BACKUP INCREMENTAL LEVEL 1 DATABASE;BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

ps -ef|grep ctwr

enable block change tracking & check the background process again.

SELECT filename, status, bytesFROM v$block_change_tracking;

SELECT file#, avg(datafile_blocks),avg(blocks_read),avg(blocks_read/datafile_blocks)* 100 AS PCT_READ_FOR_BACKUP,avg(blocks)FROM v$backup_datafileWHERE used_change_tracking = 'YES'AND incremental_level > 0GROUP BY file#;

run {allocate channel oem_backup_disk1 type disk format '%U' maxpiecesize 1 M;backup keep until time 'SYSDATE+31' restore point '%RESTORE_POINT' as BACKUPSET tag '%TAG' tablespace 'USERS' ;release channel oem_backup_disk1;}

BACKUP DATAFILE 5 SECTION SIZE = 25M TAG 'section25mb';

SET ENCRYPTION IDENTIFIED BY oracle;BACKUP DATAFILE 5;

select * from V$BACKUP_set;select * from V$BACKUP_piece;select * from V$datafile_copy;select * from V$BACKUP_files;

chapter6

ALTER TABLESPACE temp ADD TEMPFILE'/u01/app/oracle/oradata/orcl/temp02.dbf' SIZE 20M;

ALTER TABLESPACE temp DROP TEMPFILE'/u01/app/oracle/oradata/orcl/temp01.dbf' SIZE 20M;

SELECT GROUP#, STATUS, ARCHIVED FROM V$LOG;ALTER DATABASE CLEAR LOGFILE GROUP 3;

select file_name, tablespace_name from dba_data_files;

ALTER TABLESPACE users BEGIN BACKUP;

cp $ORACLE_BASE/oradata/orcl/users*.dbf /home/oracle

select * from v$backup;

ALTER TABLESPACE users END BACKUP;

ALTER DATABASE BACKUP CONTROLFILE TO '/home/oracle/controlfile.bak';

ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

SELECT file#, error FROM v$recover_file;SELECT archive_name FROM v$recovery_log;

SELECT r.FILE#, d.NAME df_name, t.NAME tbsp_name,d.STATUS, r.ERROR, r.CHANGE#, r.TIME

FROM V$RECOVER_FILE r, V$DATAFILE d, V$TABLESPACE tWHERE t.TS# = d.TS#AND d.FILE# = r.FILE#;

DRILL TO RECOVER LOST CONTROLFILE

Backup full databaseTarget databaseShut abortmove controlfiles from location

Startup nomount

Connect to rman catalog and target database

$rman catalog rcat/rcat@cat target /

RMAN > run{Restore controlfile;alter database mount;recover database;alter database open resetlogs;}

Chapter 7

SQL 'ALTER TABLESPACE users OFFLINE IMMEDIATE';RESTORE TABLESPACE users;RECOVER TABLESPACE users;SQL 'ALTER TABLESPACE users ONLINE';

Using SET NEWNAME for Switching Files

RMAN> backup datafile 4 format '/home/oracle/users01.dbf';

RUN{ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;SQL "ALTER TABLESPACE users OFFLINE IMMEDIATE";SET NEWNAME FOR DATAFILE'/u01/app/oracle/oradata/orcl/users01.dbf'

TO '/home/oracle/users01.dbf';RESTORE TABLESPACE users;SWITCH DATAFILE ALL;RECOVER TABLESPACE users;SQL "ALTER TABLESPACE users ONLINE";}

report schema;

Chapter 8

Use EM to do Drill for clone.

Chapter 9

Chapter 10

RUN {ALLOCATE CHANNEL c1 DEVICE TYPE sbt;ALLOCATE CHANNEL c2 DEVICE TYPE sbt;ALLOCATE CHANNEL c3 DEVICE TYPE sbt;BACKUPINCREMENTAL LEVEL = 0(DATAFILE 1,4,5 CHANNEL c1)(DATAFILE 2,3,9 CHANNEL c2)(DATAFILE 6,7,8 CHANNEL c3);SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';}

RMAN> backup tablespace users;

COLUMN CLIENT_INFO FORMAT a30COLUMN SID FORMAT 999COLUMN SPID FORMAT 9999

SQL> SELECT s.sid, p.spid, s.client_infoFROM v$process p, v$session sWHERE p.addr = s.paddrAND CLIENT_INFO LIKE 'rman%';

SELECT OPNAME, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"

FROM V$SESSION_LONGOPSWHERE OPNAME LIKE 'RMAN%'AND OPNAME NOT LIKE '%aggregate%'AND TOTALWORK != 0AND SOFAR <> TOTALWORK;

rman target / catalog rcat/rcat@cat debug trace trace.log

run {debug on;allocate channel c1 type disk;backup datafile 3;debug off;backup datafile 4;}

Using filespersetUse the fileperset clause of the backup command to limit the number of datafiles in eachbackup set. For example, if you wanted to limit the number of files being written to a backup set to only two files, you would use filesperset, as shown here:

RMAN> backup database filesperset 2;

Using maxopenfiles

Use the maxopenfiles clause of the configure channel command or the allocate channelcommand to limit the number of files that can be simultaneously open for reads during abackup. If you want to limit the number of files being read by a channel to two files, usemaxopenfiles as follows:

RMAN> configure channel 1 device type disk maxopenfiles 2;

To reset the channel maxopenfiles back to the default setting, use the clear parameter as

shown here:

RMAN> configure channel 1 device type disk clear;

show parameter BACKUP_TAPE_IO_SLAVES

SQL> alter system set BACKUP_TAPE_IO_SLAVES=true scope=spfile;

select * from V$BACKUP_SYNC_IO;

select * from V$BACKUP_ASYNC_IO;

select io_count, LONG_WAITS, SHORT_WAIT_TIME_TOTAL, LONG_WAIT_TIME_TOTAL from V$BACKUP_ASYNC_IO;

select DISCRETE_BYTES_PER_SECOND from V$BACKUP_SYNC_IO;

you can set the RMAN blksize parameter to manually adjust the block size that RMAN uses to write to tape

RMAN> backup duration 0:02 minimize time database;

RMAN> backup duration 6:00 partial minimize load database filesperset 1;

Chapter 11 & 12

The Oracle flashback database feature serves as an alternative to traditional databasepoint-in-time recovery. You use this feature to undo changes made by logical data corruption or by user errors. The essential point to understand here is that the opposite of flashback is to recover. In normal database recovery, you update the backups by applying logs forward. In flashback, you rewind the database by applying flashback logs backward. Thus, in most cases, a flashback database operation will take much less time than the time it takes to restore and recover during the traditional alternative, which is a database point-in-time recovery. The flashback database feature takes the database back in time, essentially rewinding it to a past point in time by undoing all changes made to the database since that time. Unlike traditional point-in-time recovery, you don’t have to perform a media recovery by restoring backups. You simply use the new flashback logs (stored in the flash recovery area) to access older versions of the changed data blocks. In addition, the database makes use of the archived redo logs as well.

show parameter recyclebin

create table emp as select * from employees;

Drop table emp;

SQL> select * from user_recyclebin;

FLASHBACK TABLE emp TO BEFORE DROP;

ORFLASHBACK TABLE emp TO BEFORE DROP RENAME TO EMP1;

select * from cat;

DROP TABLE EMP PURGE;

As DBA

SELECT owner, original_name, object_name,type, ts_name, droptime, related, spaceFROM dba_recyclebinWHERE can_undrop = 'YES';

As HR

SELECT original_name, object_name, ts_name, droptimeFROM user_recyclebin WHERE can_undrop = 'YES';

SHOW RECYCLEBIN

select * from V$TRANSACTION;

ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;

ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;

show parameter undo

select TUNED_UNDORETENTION from V$UNDOSTAT;

Flashing Back a Database from RMAN

alter session set nls_date_format = 'mm/dd/yy hh24:mi:ss';

select flashback_on from v$database;

show parameter db_flashback_retention_target

Estimating the Amount of Flashback Logs Generated

select * from v$flashback_database_statorder by begin_time;

Estimating the Space Occupied by Flashback Logs

SQL> select current_scn from v$database;

select * from v$flashback_database_log;

select to_char(oldest_flashback_time,'mm/dd/yy hh24:mi:ss')from v$flashback_database_log;

Creating Normal Restore PointsSQL> create restore point rp1;

Creating Guaranteed Restore Pointscreate restore point rp2 guarantee flashback database;

Listing Restore Pointscol time format a32col name format a10SQL> select * from v$restore_pointorder by 2,1;

Dropping Restore PointsSQL> drop restore point rp2;

Flashing Back to a Specific SCN

rman target=/

RMAN> shutdown immediate

RMAN> startup mount

SQL> select current_scnfrom v$database;

RMAN> flashback database to scn 1050951;

RMAN> alter database open resetlogs;

Optionally

RMAN> alter database open read only;

RMAN> flashback database to scn 1050900;

Flashing Back to a Specific Timerman target=/

RMAN> shutdown immediate

RMAN> startup mount

RMAN> flashback database to time 'sysdate-2/60/24';

RMAN> flashback database to time "to_date('01/23/07 13:00:00','mm/dd/yyhh24:mi:ss')";

RMAN> alter database open resetlogs;

The database is now as January 23 at 13:00:00.

Flashing Back to a Restore Point

SQL> create restore point rp2;

Do any DML etc.

rman target=/

RMAN> shutdown immediate

RMAN> startup mount

RMAN> flashback database to restore point rp2;

RMAN> alter database open resetlogs;

Recovering a Dropped Table

You accidentally dropped a table that should not have been dropped. You want to reinstate the table without doing a database recovery.

Log on to the database as the table owner.

SQL> show recyclebin

SQL> select * from user_recyclebin;SQL> flashback table accounts to before drop;SQL> drop table accounts purge;

SQL> select * from dba_recyclebin;

Turning Off the Recycle BinSQL> alter session set recyclebin = off;SQL> alter system set recyclebin = off;

Clearing the Recycle BinSQL> purge recyclebin;

Querying the History of a Table Row (Flashback Query)

SQL> create restore point rp6;

selectsalary,versions_starttime,versions_startscn,versions_endtime,

versions_endscn,versions_xid,versions_operationfrom employeesversions between scn minvalue and maxvaluewhere employee_id = 100order by 3

SQL> update employeesset salary=salary*1.5where employee_id=100;

SQL> commit;

selectsalary,versions_starttime,versions_startscn,versions_endtime,versions_endscn,versions_xid,versions_operationfrom employeesversions between scn minvalue and maxvaluewhere employee_id = 100order by 3

Flashing Back a Specific Table

Make sure the table has row movement enabled

As HR user

select row_movement from user_tableswhere table_name = 'EMPLOYEES';

SQL> alter table EMPLOYEES enable row movement;

As DBA

SQL> flashback table hr.employees to restore point rp6;

As HR

SQL> select salary from employeeswhere employee_id=100;

Flashback Data Archive Scenario

Flashback data archive

A new feature of the Oracle Database 11g release, the flashback data archive lets you usethe previously described flashback features to access data from a period of time that’s as old as you want. By using a flashback data archive, you overcome the limitation of a short undo retention time in the undo tablespace.

CREATE TABLESPACE fda_tsDATAFILE '/u01/app/oracle/oradata/orcl/fda1_01.dbf'SIZE 1M AUTOEXTEND ON NEXT 1M;

CREATE FLASHBACK ARCHIVE DEFAULT fda_1year TABLESPACE fda_tsQUOTA 10G RETENTION 1 YEAR;

CREATE FLASHBACK ARCHIVE fda_2year TABLESPACE fda_tsRETENTION 2 YEAR;

To enable flashback archiving on a specific table the user must have the FLASHBACK ARCHIVE object privilege on the specific flashback archive used

As DBAGRANT FLASHBACK ARCHIVE ON fda_1year TO hr;

As HR

ALTER TABLE hr.employees FLASHBACK ARCHIVE;ALTER TABLE hr.jobs FLASHBACK ARCHIVE;

ALTER TABLE hr.employees NO FLASHBACK ARCHIVE;

select * from user_FLASHBACK_ARCHIVE;select * from dba_FLASHBACK_ARCHIVE;select * from dba_FLASHBACK_ARCHIVE_ts;select * from dba_FLASHBACK_ARCHIVE_TABLES;

Chapter 13

select * from V$DIAG_INFO;

$ adrci

show incident

select * from V$HM_CHECK;

exec dbms_hm.run_check('Dictionary Integrity Check','mycheck',0,'TABLE_NAME=tab$');

set long 100000

select dbms_hm.get_run_report('mycheck') from dual;

declarerep_out clob;t_id varchar2(50);begint_id := dbms_sqldiag.create_diagnosis_task(sql_text => 'delete from t t1 where t1.a = ''a'' and rowid <> (select max(rowid)from t t2 where t1.a= t2.a and t1.b = t2.b and t1.d=t2.d)',task_name => 'sqldiag_bug_5869490',problem_type => DBMS_SQLDIAG.PROBLEM_TYPE_COMPILATION_ERROR);dbms_sqltune.set_tuning_task_parameter(t_id,'_SQLDIAG_FINDING_MODE',dbms_sqldiag.SQLDIAG_FINDINGS_FILTER_PLANS);dbms_sqldiag.execute_diagnosis_task (t_id);rep_out := dbms_sqldiag.report_diagnosis_task (t_id, DBMS_SQLDIAG.TYPE_TEXT);dbms_output.put_line ('Report : ' || rep_out);end;/

execute dbms_sqldiag.accept_sql_patch(task_name => 'sqldiag_bug_5869490',task_owner => 'HR', replace => TRUE);

SHOW PARAMETER DB_BLOCK_CHECKING

select * from V$DATABASE_BLOCK_CORRUPTION

RECOVER DATAFILE 6 BLOCK 3;

RMAN> LIST FAILURE;

select * from V$IR_FAILURE;select * from V$IR_MANUAL_CHECKLIST;select * from V$IR_REPAIR;select * from V$IR_FAILURE_SET;

Chapter 14

ALTER TABLE oe.customersSTORAGE (BUFFER_POOL RECYCLE);

Enabling Automatic Shared Memory Management

Get a value for SGA_TARGET

SELECT ((SELECT SUM(value) FROM V$SGA) - (SELECT CURRENT_SIZEFROM V$SGA_DYNAMIC_FREE_MEMORY)) "SGA_TARGET" FROM DUAL;

SQL> SHOW PARAMETER SGA_TARGET

ALTER SYSTEM SET SGA_TARGET=639815680 SCOPE=BOTH;

To switch to ASMM from Automatic Memory Management:

SQL> SHOW PARAMETER MEMORY_TARGET

ALTER SYSTEM SET MEMORY_TARGET = 0;

SELECT SUM(bytes)/1024/1024 size_mbFROM v$sgastat WHERE pool = 'shared pool';

SELECT component, current_size/1024/1024 size_mb

FROM v$sga_dynamic_components;

SELECT name, value, isdefaultFROM v$parameterWHERE name LIKE '%size';

show parameter sga_max_size

Disabling ASMM

Setting SGA_TARGET to 0 disables autotuning.

show parameter PGA_AGGREGATE_TARGET

SQL> select * from V$PGA_TARGET_ADVICE;

Chapter 16

alter table EMPLOYEES enable row movement;

ALTER TABLE employees SHRINK SPACE COMPACT;

ALTER TABLE employees SHRINK SPACE;

ALTER SESSION ENABLE RESUMABLE;

INSERT INTO sales_new SELECT * FROM sh.sales;

ALTER SESSION DISABLE RESUMABLE;

SELECT name, sql_text FROM user_resumable;

SQL> select platform_name from v$database;

SELECT tp.endian_formatFROM v$transportable_platform tp, v$database dWHERE tp.platform_name = d.platform_name;

SELECT * FROM V$TRANSPORTABLE_PLATFORM;

Chapter 17

show parameter resource_manager_plan

SELECT plan, num_plan_directives, status, mandatoryFROM dba_rsrc_plans;

SQL> select * from V$RSRC_CONSUMER_GROUP;

Chapter 18

SELECT job_name, status, error#, run_durationFROM USER_SCHEDULER_JOB_RUN_DETAILS;

BEGINDBMS_SCHEDULER.CREATE_JOB(job_name => 'my_lightweight_job1',program_name => 'MY_PROG',repeat_interval => 'FREQ=DAILY;BY_HOUR=9',end_time => '30-APR-07 04.00.00 AM CST',job_style => 'LIGHTWEIGHT');END;/

BEGINDBMS_SCHEDULER.CREATE_JOB (job_name => 'my_lightweight_job2',program_name => 'MY_PROG',schedule_name => 'MY_SCHED',job_style => 'LIGHTWEIGHT');END;/

DBMS_SCHEDULER.SET_ATTRIBUTE('hr.do_backup','raise_events', DBMS_SCHEDULER.JOB_FAILED);

Chapter 20

SELECT * FROM V$NLS_PARAMETERS;

SQL> select sysdate from dual;

SELECT parameter, valueFROM nls_database_parametersWHERE parameter LIKE '%CHARACTERSET%';

ALTER SESSION SET NLS_DATE_FORMAT='DD.MM.YYYY';

SELECT TO_CHAR(hire_date,'DD.Mon.YYYY','NLS_DATE_LANGUAGE=FRENCH')FROM hr.employeesWHERE hire_date > '01-JAN-2000';

SQL> create table test (test_col timestamp);

Table created.

SQL> insert into test values (sysdate);

1 row created.

SQL> select * from test;

TEST_COL---------------------------------------------------------------------------10-SEP-08 06.28.02.000000 PM

ALTER SESSION SET NLS_SORT=BINARY;

SELECT dbtimezone FROM DUAL;