Oracle : Experiment guaranteed restore point usage

At the start of this experiment Flashback is off. Meaning flashback is not a requirement for restore point.

V$database:Flashback_on=NO

 SQL> create restore point test guarantee flashback database;

Restore point created.


SQL> create table test (a number);

Table created.

SQL> insert into test values(1000);

1 row created.

SQL> commit;

Commit complete.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 4496291200 bytes
Fixed Size                  9037184 bytes
Variable Size             872415232 bytes
Database Buffers         3607101440 bytes
Redo Buffers                7737344 bytes
Database mounted.

SQL> flashback database to restore point test;

Flashback complete.

SQL> shut immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 4496291200 bytes
Fixed Size                  9037184 bytes
Variable Size             872415232 bytes
Database Buffers         3607101440 bytes
Redo Buffers                7737344 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.

SQL> drop restore point test;

Restore point dropped.

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


SQL>

Linux: Experiment on incremental FS backup using tar

    Incremental backup using tar : good for file system backup/archiving of log directories etc. 

Commands: I am using LVL variable to ease my work in rewriting incremental backup. Start from 0 and go up from there.

export LVL=0

tar --verbose --create --file=tmp_${LVL}.tar --listed-incremental=tmp.snar tmp/*

Note: snar file remains same at all levels, it is the tracker for incremental changes.

Next just change LVL=1,2 ..... so on. Must or the tar file get overwritten.

If any of the files get changed or new file created, next level automatically picks it up.

Alternate Method(I prefer):See the variable --level (0 means base and the incrementals)

export LVL=1

tar --verbose --create --file=tmp_${LVL}.tar --listed-incremental=tmp.snar --level=${LVL} tmp/*

Note: Extracting tar is same as before, tar xvf in the order it was backed up.

Oracle : Auto index feature

Careful, I am endorsing any feature. Use judiciously. 

DOC: https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/managing-indexes.html#GUID-FF3F7EF5-9468-48BC-8990-8189C1CBFD12

Auto Indexing feature

--create/make it available

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');

--create/keep invisible

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY');

--Feature off

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');

***AUTO_INDEX_SCHEMA configuration setting to specify schemas that can use auto indexes

-- exclusion list

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'MYSCHEMA', FALSE);

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'YOURSCHEMA', FALSE);

--remove from exclusion list i.e. include

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'HR', NULL);

--Clear exclude list

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', NULL, TRUE);

***Retition for unused indexes

AUTO_INDEX_RETENTION_FOR_AUTO

--retain for 30days

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO', '30');

--default retention 373 days

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO', NULL);

---Warning/Alert Applicable for Manually created indexes(Use judgement)

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_MANUAL', '160');

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_MANUAL', NULL);

**Auto index logs/tracking

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_REPORT_RETENTION', '160');

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_REPORT_RETENTION', NULL);        --default 373

**Specify tablespace for auto index

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE', 'TBS_AUTO');

**Allocation for auto indexes

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SPACE_BUDGET', '15');   --15%

**Compression on off

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_COMPRESSION','ON');

###**Reporting on auto index

REPORT_ACTIVITY and REPORT_LAST_ACTIVITY functions of the DBMS_AUTO_INDEX package

declare

  report clob := null;

begin

  report := DBMS_AUTO_INDEX.REPORT_ACTIVITY();

end;

declare

  report clob := null;

begin

  report := DBMS_AUTO_INDEX.REPORT_ACTIVITY(

              activity_start => TO_TIMESTAMP('2018-11-01', 'YYYY-MM-DD'),

              activity_end   => TO_TIMESTAMP('2018-12-01', 'YYYY-MM-DD'),

              type           => 'HTML',

              section        => 'SUMMARY',

              level          => 'BASIC');

end;