What are restore points and how are they used?

Definition:

A restore point is an alias to the system change number (SCN) of the database at the time the restore point was created.

Types of restore points:

1. Normal
2. Guaranteed

For both types, the name of the restore point and the SCN are recorded in the database control file.

Normal restore points are very lightweight. The control file can maintain a record of thousands of normal restore points with no significant impact upon database performance. Normal restore points eventually age out of the control file if not manually deleted, so they require no ongoing maintenance.

Guaranteed restore points on the other hand, guarantees that Oracle will retain the flashback logs for a Flashback Database operation.

A guaranteed restore point does not age out of the control file and must be explicitly dropped.
Guaranteed restore points will utilize space in the flash recovery area.
The flash recovery area must be therefore be defined and large enough to hold the flashback logs for the duration of the guaranteed restore point's existence.

Creation example:

CREATE RESTORE POINT PRE_EOD_201208;
CREATE RESTORE POINT PRE_RELEASE2 GUARANTEE FLASHBACK DATABASE;

Usage:

You can use restore points with any commands that recognize a RESTORE POINT clause as a shorthand for specifying an SCN.

Examples:

FLASHBACK DATABASE TO RESTORE POINT < restore point name >;
RESTORE DATABASE TO RESTORE POINT < restore point name >;
FLASHBACK TABLE emp TO RESTORE POINT < restore point name >;

To view the restore points created in your database, use:

select name,scn,time,database_incarnation# "INC NR",
guarantee_flashback_database "guaranteed?"
storage_size/1024/1024 "MB"
from v$restore_point;


         NAME          SCN                               TIME    INC NR    guaranteed?      MB
_____________ ____________ __________________________________ _________ ______________ _______
PREUPGRADE       208072152 22-JAN-25 01.06.35.000000000 PM            2 YES               3072

To create a normal restore point, you need either

the SELECT ANY DICTIONARY system privilege, or

the FLASHBACK ANY TABLE system privilege

To create a guaranteed restore point, you need SYSDBA privileges.

To view or use a restore point, the user need either of the following:

The SELECT ANY DICTIONARY system privilege

The FLASHBACK ANY TABLE system privilege

The SELECT_CATALOG_ROLE role

To drop a restore point:

DROP RESTORE POINT AKSEPT_TEST_START;

How to drop a plan from the SMB

Find the SQL Plan Baselines name and sql handle:

SELECT  SQL_HANDLE,PLAN_NAME
FROM    DBA_SQL_PLAN_BASELINES 
WHERE   PLAN_NAME ='SQL_PLAN_b8p2xjvyx9ja5bcc2b4c9';

Run the following PL/SQL as a privileged user (a DBA or "as sysdba")

SET SERVEROUTPUT ON
DECLARE
  l_plans_dropped  PLS_INTEGER;
BEGIN
  l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (
    sql_handle => 'SQL_b4545d8efdd4c545',
    plan_name  => 'SQL_PLAN_b8p2xjvyx9ja5bcc2b4c9');

  DBMS_OUTPUT.put_line('Number of plans dropped: ' || l_plans_dropped);
END;
/

How to perform a full restore of Oracle

The following scripts have been tested and proven to work.
Make sure the NB_ORA_POLICY is correctly entered and adheres to the documentation from your MML provider:

#!/bin/ksh
LOGFILE=$SCRIPT_BASE/log/restore_${ORACLE_SID}_`date +%Y%m%d%H%M%S`.log
export RECOVERY_CATALOG=catowner/**********@RMANCAT
nohup rman target / catalog $RECOVERY_CATALOG << EOF > $LOGFILE 2>&1  &
startup force nomount;
 set DBID=1554594349;
 run {
  allocate channel t1 type sbt;
  allocate channel t2 type sbt
  send 'NB_ORA_POLICY=ora_dbserver1_00_netwzone1,NB_ORA_CLIENT=dbserver1-bkp.mydomain.com,NB_ORA_SCHED=ora_dbserver1_00_netwzone1_user';
  set until sequence < logseq > thread 1;
  restore controlfile;
  alter database mount;
  restore database;
  recover database;
 }
 alter database open resetlogs;

EOF
echo "RMAN restore started in background. Check logfile: $LOGFILE"

A good crontab helptext

# Use the hash sign to prefix a comment
# +---------------- minute (0 - 59)
# |  +------------- hour (0 - 23)
# |  |  +---------- day of month (1 - 31)
# |  |  |  +------- month (1 - 12)
# |  |  |  |  +---- day of week (0 - 7) (Sunday=0 or 7)
# |  |  |  |  |
# *  *  *  *  *  command to be executed

How to use EXPLAIN PLAN statement and view the results using the DBMS_XPLAN.DISPLAY function

This table function simply displays the contents of the plan table.

Execute an explain plan command on a SELECT statement:

EXPLAIN PLAN FOR
   SELECT * FROM emp e, dept d
   WHERE e.deptno = d.deptno
   AND   e.ename  = 'benoit';

Display the plan using the DBMS_XPLAN.DISPLAY table function:

SET LINESIZE 130
SET PAGESIZE 0
SELECT * FROM table(DBMS_XPLAN.DISPLAY);

This query produces the following output:

Plan hash value: 3693697075
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    57 |     6  (34)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     1 |    57 |     6  (34)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMP  |     1 |    37 |     3  (34)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPT |     4 |    80 |     3  (34)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("E"."DEPTNO"="D"."DEPTNO")
   2 - filter("E"."ENAME"='benoit')

15 rows selected.

You can also use the SET STATEMENT_ID directive to identify a specific plan:

EXPLAIN PLAN SET STATEMENT_ID='EXPL1' FOR
SELECT * FROM emp e, dept d
   WHERE e.deptno = d.deptno
   AND e.ename='benoit';

SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table','EXPL1','serial'));

If no statement_id is specified, the function will show you the plan of the most recent explained statement.

Source: Oracle Documentation

How to create a Range-Partitioned Global Index

You have the following table:

CREATE TABLE sales
  ( prod_id       NUMBER(6)
  , cust_id       NUMBER
  , time_id       DATE
  , channel_id    CHAR(1)
  , promo_id      NUMBER(6)
  , quantity_sold NUMBER(3)
  , amount_sold   NUMBER(10,2)
  )
 PARTITION BY RANGE (time_id)
 ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))
    TABLESPACE tsa
 , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))
    TABLESPACE tsb
 , PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
    TABLESPACE tsc
 , PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
    TABLESPACE tsd
 )
 ENABLE ROW MOVEMENT; 

Note: The ENABLE ROW MOVEMENT clause is specified to allow the automatic migration of a row to a new partition if an update to a key value is made that would place the row in a different partition.

The rules for creating range-partitioned global indexes are similar to those for creating range-partitioned tables.
Each index partition is named but is stored in the default tablespace for the index.

CREATE INDEX amount_sold_ix ON sales(amount_sold)
   GLOBAL PARTITION BY RANGE(sale_month)
      ( PARTITION p_100 VALUES LESS THAN (100)
      , PARTITION p_1000 VALUES LESS THAN (1000)
      , PARTITION p_10000 VALUES LESS THAN (10000)
      , PARTITION p_100000 VALUES LESS THAN (100000)
      , PARTITION p_1000000 VALUES LESS THAN (1000000)
      , PARTITION p_greater_than_1000000 VALUES LESS THAN (maxvalue)
      );

Source: Oracle Documentation

Some useful asmcmd commands

When you need to work with ASM disks, you can use the asmcmd (ASM Command Line Utility) utility. I have found the following commands very useful:

asmcmd -p 
(-p option gives you the current directory at any given time - convenient)

lsdg
--> lists disk groups

lsdsk -s
--> -s shows various I/O related statistics of the disks

lsdsk --candidate -p
--> lists all available ASM disk candidates

lsdsk --member -p
--> lists all the current member of a disk group, as opposed to the candidates.

or simply

lsdsk -p