Using Restore Points in Standard Edition (SE2)

Restore points and Flashback database are related data protection features introduced in Oracle 10g release that enable you to rewind data back in time to correct any problems caused by logical data corruption or user errors within a designated time window.

But what if you have Standard Edition database:

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-00439: feature not enabled: Flashback Database

In Standard Edition we dont have Flashback Database feature, but you can still create restore points and perform incomplete recovery to a restore point.

For this test we need to have a backup already taken, also database must be running in archivelog mode:

SQL> create restore point RP_TEST_AFTER_BACKUP;

Restore point created.

SQL> select scn, to_char(time,'dd.mm.yyyy hh24:mi:ss') time, name from v$restore_point;

       SCN TIME 	       NAME
---------- ------------------- ------------------------------
    930727 26.08.2020 17:03:01 RP_TEST_AFTER_BACKUP

As we can see that restore point is associated with SCN of the database.

Now you can perform potentially dangerous operations like database upgrades, tablespace deletion, truncating data and so on.

In our particular case we are going to create a new tablespace:

SQL> create tablespace test_tbs datafile size 64M;

Tablespace created.

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
TEST_TBS <<--

6 rows selected.

Let´s imagine we had some problems and want to "rewind" database to back to the restore point. On Enterprise Edition we would perform flashback database to restore point but in SE2 we will use different approach.

Shutdown database and startup mount.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1157627168 bytes
Fixed Size		    8895776 bytes
Variable Size		  385875968 bytes
Database Buffers	  754974720 bytes
Redo Buffers		    7880704 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.8.0.0.0

Perform restore and recover database until restore point RP_TEST_AFTER_BACKUP.

[oracle@srv1 dbhome_1]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Sep 2 17:09:56 2020
Version 19.8.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: STXTEST (DBID=3165137538, not open)

RMAN> list restore point all;

using target database control file instead of recovery catalog
SCN              RSP Time  Type       Time      Name
---------------- --------- ---------- --------- ----
930727                                26-AUG-20 RP_TEST_AFTER_BACKUP

RMAN> restore database until restore point RP_TEST_AFTER_BACKUP;

Starting restore at 02-SEP-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=296 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /srv/oradata/STXTEST/datafile/o1_mf_system_hndg669o_.dbf
channel ORA_DISK_1: restoring datafile 00002 to /srv/oradata/STXTEST/datafile/o1_mf_sysaux_hndg6c8j_.dbf
channel ORA_DISK_1: restoring datafile 00003 to /srv/oradata/STXTEST/datafile/o1_mf_undotbs1_hndg6h73_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /srv/oradata/STXTEST/datafile/o1_mf_users_hndg6r07_.dbf
channel ORA_DISK_1: reading from backup piece /srv/fra/STXTEST/backupset/2020_08_26/o1_mf_nnndf_TAG20200826T165909_hndq4x5k_.bkp
channel ORA_DISK_1: piece handle=/srv/fra/STXTEST/backupset/2020_08_26/o1_mf_nnndf_TAG20200826T165909_hndq4x5k_.bkp tag=TAG20200826T165909
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 02-SEP-20

RMAN> recover database until restore point RP_TEST_AFTER_BACKUP;

Starting recover at 02-SEP-20
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 19 is already on disk as file /srv/fra/STXTEST/archivelog/2020_08_26/o1_mf_1_19_hndq5pdq_.arc
archived log for thread 1 with sequence 20 is already on disk as file /srv/fra/STXTEST/archivelog/2020_08_26/o1_mf_1_20_hndqh2yg_.arc
archived log file name=/srv/fra/STXTEST/archivelog/2020_08_26/o1_mf_1_19_hndq5pdq_.arc thread=1 sequence=19
archived log file name=/srv/fra/STXTEST/archivelog/2020_08_26/o1_mf_1_20_hndqh2yg_.arc thread=1 sequence=20
media recovery complete, elapsed time: 00:00:00
Finished recover at 02-SEP-20

We have to open the database with resetlogs option.

RMAN> alter database open resetlogs;

Statement processed

RMAN> exit

Recovery Manager complete.

Final check.

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS

We put back database to state that existed before RP_TEST_AFTER_BACKUP restore point is created. This is an example of incomplete recovery and a restore point was used just to choose a point in time.

Procedure print_table, print columns in a table row wise

Some time ago i found this procedure in Ask TOM site, which can be used to print columns in a table row wise.

The store procedure takes 2 parameters:

1. SQL statement to be executed
2. Date format to printed. 

This second parameter is optional as procedure establish a default date format.

CREATE OR REPLACE PROCEDURE print_table (
    p_query IN VARCHAR2,
    p_date_fmt IN VARCHAR2 DEFAULT 'dd-mon-yyyy hh24:mi:ss'
)
-- this utility is designed to be installed ONCE in a database and used
-- by all. Also, it is nice to have roles enabled so that queries by
-- DBA's that use a role to gain access to the DBA_* views still work
-- that is the purpose of AUTHID CURRENT_USER
authid current_user
IS
  l_thecursor   INTEGER DEFAULT dbms_sql.open_cursor;
  l_columnvalue VARCHAR2(4000);
  l_status      INTEGER;
  l_desctbl     dbms_sql.desc_tab;
  l_colcnt      NUMBER;
  l_cs          VARCHAR2(255);
  l_date_fmt    VARCHAR2(255);
  -- small inline procedure to restore the sessions state
  -- we may have modified the cursor sharing and nls date format
  -- session variables, this just restores them
  PROCEDURE Restore
  IS
  BEGIN
      IF ( Upper(l_cs) NOT IN ( 'FORCE', 'SIMILAR' ) ) THEN
        EXECUTE IMMEDIATE 'alter session set cursor_sharing=exact';
      END IF;

      IF ( p_date_fmt IS NOT NULL ) THEN
        EXECUTE IMMEDIATE 'alter session set nls_date_format='''|| l_date_fmt||
        '''';
      END IF;

      dbms_sql.Close_cursor(l_thecursor);
  END restore;
BEGIN
    -- I like to see the dates print out with times, by default, the
    -- format mask I use includes that. In order to be "friendly"
    -- we save the date current sessions date format and then use
    -- the one with the date and time. Passing in NULL will cause
    -- this routine just to use the current date format
    IF ( p_date_fmt IS NOT NULL ) THEN
      SELECT Sys_context('userenv', 'nls_date_format')
      INTO   l_date_fmt
      FROM   dual;

      EXECUTE IMMEDIATE 'alter session set nls_date_format='''|| p_date_fmt||
      '''';
    END IF;

    -- to be bind variable friendly on this ad-hoc queries, we
    -- look to see if cursor sharing is already set to FORCE or
    -- similar, if not, set it so when we parse -- literals
    -- are replaced with binds
    IF ( dbms_utility.Get_parameter_value ('cursor_sharing', l_status, l_cs) = 1
       )
    THEN
      IF ( Upper(l_cs) NOT IN ( 'FORCE', 'SIMILAR' ) ) THEN
        EXECUTE IMMEDIATE 'alter session set cursor_sharing=force';
      END IF;
    END IF;

    -- parse and describe the query sent to us. we need
    -- to know the number of columns and their names.
    dbms_sql.Parse(l_thecursor, p_query, dbms_sql.native);

    dbms_sql.Describe_columns (l_thecursor, l_colcnt, l_desctbl);

    -- define all columns to be cast to varchar2's, we
    -- are just printing them out
    FOR i IN 1 .. l_colcnt LOOP
        IF ( L_desctbl(i).col_type NOT IN ( 113 ) ) THEN
          dbms_sql.Define_column (l_thecursor, i, l_columnvalue, 4000);
        END IF;
    END LOOP;

    -- execute the query, so we can fetch
    l_status := dbms_sql.EXECUTE(l_thecursor);

    -- loop and print out each column on a separate line
    -- bear in mind that dbms_output only prints 255 characters/line
    -- so we'll only see the first 200 characters by my design...
    WHILE ( dbms_sql.Fetch_rows(l_thecursor) > 0 ) LOOP
        FOR i IN 1 .. l_colcnt LOOP
            IF ( L_desctbl(i).col_type NOT IN ( 113 ) ) THEN
              dbms_sql.Column_value (l_thecursor, i, l_columnvalue);

              dbms_output.Put_line (Rpad(L_desctbl(i).col_name, 30)
                                    || ': '
                                    || Substr(l_columnvalue, 1, 200));
            END IF;
        END LOOP;
        
        dbms_output.Put_line('-----------------');
    END LOOP;
    -- now, restore the session state, no matter what
    restore;
EXCEPTION
  WHEN OTHERS THEN
    restore;
-- RAISE;
END;
/ 

See an example below:

set serveroutput on

exec print_table('select * from dba_tables where owner=''SYS'' and table_name=''AUD$''');

OWNER                         : SYS
TABLE_NAME                    : AUD$
TABLESPACE_NAME               : SYSTEM
CLUSTER_NAME                  : 
IOT_NAME                      : 
STATUS                        : VALID
PCT_FREE                      : 10
PCT_USED                      : 40
INI_TRANS                     : 1
MAX_TRANS                     : 255
INITIAL_EXTENT                : 65536
NEXT_EXTENT                   : 1048576
MIN_EXTENTS                   : 1
MAX_EXTENTS                   : 2147483645
PCT_INCREASE                  : 
FREELISTS                     : 1
FREELIST_GROUPS               : 1
LOGGING                       : YES
BACKED_UP                     : N
NUM_ROWS                      : 368
BLOCKS                        : 12
EMPTY_BLOCKS                  : 0
AVG_SPACE                     : 0
CHAIN_CNT                     : 0
AVG_ROW_LEN                   : 162
AVG_SPACE_FREELIST_BLOCKS     : 0
NUM_FREELIST_BLOCKS           : 0
DEGREE                        :          1
INSTANCES                     :          1
CACHE                         :     N
TABLE_LOCK                    : ENABLED
SAMPLE_SIZE                   : 368
LAST_ANALYZED                 : 07-jun-2019 21:33:31
PARTITIONED                   : NO
IOT_TYPE                      : 
TEMPORARY                     : N
SECONDARY                     : N
NESTED                        : NO
BUFFER_POOL                   : DEFAULT
FLASH_CACHE                   : DEFAULT
CELL_FLASH_CACHE              : DEFAULT
ROW_MOVEMENT                  : DISABLED
GLOBAL_STATS                  : YES
USER_STATS                    : NO
DURATION                      : 
SKIP_CORRUPT                  : DISABLED
MONITORING                    : YES
CLUSTER_OWNER                 : 
DEPENDENCIES                  : DISABLED
COMPRESSION                   : DISABLED
COMPRESS_FOR                  : 
DROPPED                       : NO
READ_ONLY                     : NO
SEGMENT_CREATED               : YES
RESULT_CACHE                  : DEFAULT
-----------------