Step To Configure Oracle Flashback In Asm Database

December 3, 2016

By postgresdba

1. Ensure db_recovery_file_dest is set.

sqlplus ‘/ as sysdba’

SQL> alter system set db_recovery_file_dest=’+’ SCOPE=spfile;

2. Ensure db_recovery_file_dest_size is set

SQL> alter system set db_recovery_file_dest_size=100G SCOPE=spfile;

3. Stop and start the database

sqlplus ‘/ as sysdba’

SQL> shutdown immediate;

SQL> startup mount;

If flashback to any previous point in time is required, then turn flashback on using the following command

SQL> alter database flashback on;

SQL> alter database open;

SQL> alter system set db_flashback_retention_target=2880;

NOTES

Set the db_recovery_file_dest to an appropriate location for the flashback recovery files.

Set the db_recovery_file_dest_size to an appropriate size for the amount and size of the testing required.

Set the db_flashback_retention_target to an appropriate time, in mins, to retain flashbackability.

Only run alter database flashback on; if there is a requirement to flashback to ANY previous point in time.

Determine if Flashback Database is Already Enabled.

sqlplus ‘/ as sysdba’

SQL> select flashback_on from v$database;

Creating and Using Flashback Restore points.

This worked example assumes the database is using ASM to manage its storage.

Createing a Restore point

Create a restore point whenever the database is at a state that it may needed to be flashed back to. Use the optional GUARANTEE FLASHBACK DATABASE clause to ensure that the restore point is not aged out of the flashback recovery area (FRA) as dictated by the db_flashback_retention_target parameter.

1. You may want to create the restore point in mount mode. If so, put the database into mount mode now.

2. Create a restore point

sqlplus ‘/ as sysdba’

SQL> create restore point [GUARANTEE FLASHBACK DATABASE];

Rolling Back to a Restore Point

1. Identify the Restore point

sqlplus ‘/ as sysdba’

SQL> select name, time,guarantee_flashback_databse from v$restore_point;

SQL> quit

2. For a non RAC environment use the following commands to flashback to a restore point.

sqlplus ‘/ as sysdba’

SQL> shutdown immediate;

SQL> startup mount;

SQL> flashback database to restore point ;

SQL> alter database open resetlogs;

3. For RAC instances use the following commands.

One one of the nodes run, srvctl stop database -d -o immediate

sqlplus ‘/ as sysdba’

SQL> startup mount;

SQL> flashback database to restore point ;

SQL> alter database open resetlogs;

SQL> shutdown immediate;

srvctl start database -d

Run crs_stat -t to confirm that the database is backup okay.

NOTES

Any tables created and updated without the LOGGING option will be suseptable to block curruption errors when the database is flashed back. These can be remedied by issuing the TRUNCATE TABLE command against the affected object(s).

Dropping a Restore Point

Restore points can be dropped with the database open using the following commands

sqlplus ‘/ as sysdba’

SQL> drop restore poijnt ;

Monitoring Flashback Logging

After enabling flashback logging, Oracle keeps track of the amount of logging generated. This can be queried from v$flashback_database_log, the estimate gets better with age. Note that this is the size of the flashback logs only and does not include space used by archive logs and RMAN backups.

sqlplus ‘/ as sysdba’

SQL> select estimated_flashback_size/1024/1024/1024 “EST_FLASHBACK_SIZE(GB)” from v$flashback_database_log;

Finding the Earliest Flashback Point

Querying V$flashback_database_log will show you the earliest point you can flashback your database to based on the size of the FRA and the currently available flashback logs.

sqlplus ‘/ as sysdba’

SQL> alter session set nls_date_format=’dd/mm/yy hh24:mi:ss’;

SQL> select oldest_flashback_scn,oldest_flashback_time from v$flashback_database_log;

Disabling Flashback Database

Full any previous point in time flashback can be disabled with the database open. Any unused Flashback logs will be automatically removed at this point and a message detailing the file deletion written to the alert log.

sqlplus ‘/ as sysdba’

SQL> ALTER DATABASE FLASHBACK OFF;

mostly comming Errors

ORA-38754: FLASHBACK DATABASE not started; required redo log is not available

Cause:-

Oracle needs to have the required archive logs in the archive destination at the time of flashback.

Solution:-

rman target /

run{

allocate channel t1 device type ‘sbt_tape’;

FLASHBACK DATABASE TO RESTORE POINT ;

}

Database Link In Oracle (Server To Server)

December 3, 2016

By postgresdba

A database link is schema object.

A database link is a connection between two physical database servers that allows a client to access them as one logical database.

WHY USE DATABASE LINK:-

· The most advantage of database link is that they allow users to access another users objects in a remote database so that they are bounded by the privileges set of the object owner.
They are three basic types of database link.

TYPES:-

1.private

2.public

GRANT PRIVILEGES:-

Sql>grant create database link to username;

Sql>grant create public database link to username;

TYPES:-

1.private database link:-

· Create link in specific schema of the local database.

· May be used only by the link owner.

Syntax:-

Sql>create private database link connect to identified by using ‘tnsname’;

Example:-

Sql>create private database link xla1 connect to u1 identified by u1 using ‘tnsxla12’;

2.public database link:-

· A public database link that can be used by any user.

· You can access remote objects using insert,update and delete statement.

Syntax:-

Sql>Create public database link connect to identified by using ‘tnsname’;

Example:-

Sql>create public database link xla1 connect to u1 identified by u1 using ‘tnsla1’;

DATABASE LINK RESTRICTIONS:-

Grant privileges on remote objects.
Do support describe operations –tables,views,functions,procedures,mviews,packages.

CLOSE DATABASE LINK:-

Syntax:-

Sql>alter session close database link ;

Example:-

Sql>alter session close database link xla1 ;

DROPPING A DATABASE LINK:-

Sql>drop database link ;

Sql>drop public database link ;

VIEWS:-

All_db_links

Dba_db_links

User_db_links

The local database user can view the object.

Sql>select * from tab@;

Sql>select * from tab@xla1;

practical:-

Test11> select name, password

2 from sys.user$

3 where name=’NIJAM’;

NAME PASSWORD

—————————— ——————————

NIJAM 66EE6D5F9AB42E0F

1 row selected.

Changed current NIJAM password to temporary “tmp_pwd” and granted “create database link” privilege to NIJAM.

Test1> alter user nijam identified by tmp_pwd;

User altered.

Test1> grant create database link to nijam;

Grant succeeded.

Connect as NIJAM user using temporary password and creat database link.

Test1> connect nijam/tmp_pwd@test11

Connected.

nijam@TEST11> CREATE DATABASE LINK U1_LINK

2 CONNECT TO U1

3 IDENTIFIED BY u1

4 USING ‘(DESCRIPTION =

5 (ADDRESS_LIST =

6 (ADDRESS = (PROTOCOL = TCP)(HOST = linux01)(PORT = 1521))

7 )

8 (CONNECT_DATA =

9 (SID = TEST10)

10 )

11 )’;

Database link created.

Simple test:

nijam@TEST11> select count(*) from u1.u1_log@u1_link;

COUNT(*)

———-

355412

1 row selected.

LINK Link is functioning perfectly and now I can revoke “create database link privilege” and return password as it was before.

nijam@TEST11> conn Test11

Enter password:

Connected.

Test1> revoke create database link from nijam;

Revoke succeeded.

Test1> alter user nijam identified by values ’66EE6D5F9AB42E0F’;

User altered.

The key thing is to have prepared scripts to do this actions as quick as possible because when you change current schema password to temporary other users or u1s will not be able to log in.

Fully Concept Of Flashback Oracle ,Practical With Demo Error And Solution Also

December 3, 2016

By postgresdba

Oracle Flashback Technology is a group of Oracle Database features that let you view past states of database objects or to return database objects to a previous state without using point-in-time media recovery.

Flashback feature was introduced in Oracle9i; it was limited to Flashback Query only

Great improvements have been made in the Flashback functions in Oracle Database 10g.

Flashback functionalities provide fast and flexible data recovery

TYPES OF FLASHBACK :-

1. flashback query —we can show only the past state of the table

2. flashback version query —show the the table transaction time and scn

3. flashback transaction query —it show what operation is done and which user is performed

4. flashback table — to flash back a table to an earlier scn or timestamp

5. flashback drop — flashback table to before drop using recylebin

6. flashback database — to flash back a table to an earlier scn,timestamp or restore point

Flashback Works On Two Parameter

1. scn

2. timestamp

FLASHBACK STATUS:-

Flashback status of a database can be checked from the below query and system parameters.

SQL> select NAME, FLASHBACK_ON from v$database;

SQL> archive log list

SQL> show parameter undo_retention

SQL> show parameter db_recovery_file_dest

SQL> show parameter db_recovery_file

FLASHBACK ENABLE:-

The Database must be started through SPFILE

SQL > show parameter spfile

NAME TYPE VALUE

Step to Flashback Practical

1)undo_retention seconds, set to one hour

SQL> SELECT tablespace_name,retention FROM dba_tablespaces;

TABLESPACE_NAME RETENTION

—————————— ———–

SYSTEM NOT APPLY

SYSAUX NOT APPLY

UNDOTBS1 NOGUARANTEE

TEMP NOT APPLY

USERS NOT APPLY

TESTTBS NOT APPLY

6 ROWS selected.

SQL> ALTER TABLESPACE UNDOTBS1 retention guarantee;

TABLESPACE altered.

SQL> SELECT tablespace_name,retention FROM dba_tablespaces;

TABLESPACE_NAME RETENTION

—————————— ———–

SYSTEM NOT APPLY

SYSAUX NOT APPLY

UNDOTBS1 GUARANTEE

TEMP NOT APPLY

USERS NOT APPLY

TESTTBS NOT APPLY

6 ROWS selected.

SQL> ALTER SYSTEM SET undo_retention=3600;

SYSTEM altered.

2)create test1 table and insert some data’s

SQL> CREATE TABLE test1(id NUMBER, descr VARCHAR2(30));

TABLE created.

SQL> INSERT INTO test1 VALUES(1,’One’);

1 ROW created.

SQL> INSERT INTO test1 VALUES(2,’Two’);

1 ROW created.

SQL> INSERT INTO test1 VALUES(3,’Three’);

1 ROW created.

SQL> COMMIT;

COMMIT complete.

3)step to set flashback_on in database

SQL> shutdown IMMEDIATE

DATABASE closed.

DATABASE dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total SYSTEM Global Area 1068937216 bytes

Fixed SIZE 2233336 bytes

Variable SIZE 796920840 bytes

DATABASE Buffers 260046848 bytes

Redo Buffers 9736192 bytes

DATABASE mounted.

SQL> ALTER DATABASE flashback ON;

DATABASE altered.

SQL> ALTER DATABASE OPEN;

DATABASE altered.

SQL> SELECT flashback_on FROM v$database;

FLASHBACK_ON

——————

YES

You define the flashback database retention with:

SQL> show parameter db_flashback_retention_target

NAME TYPE VALUE

———————————— ———– ——————————

db_flashback_retention_target INTEGER 1440

4) You are now able to rewind your database at maximum db_flashback_retention_target minutes in the past, if you look into your Fast Recovery Area you see creation of below files:

[oracle@server1 fast_recovery_area]$ pwd

/oracle/fast_recovery_area

[oracle@server1 fast_recovery_area]$ ll

total 20

drwx—— 2 root root 16384 May 11 17:05 lost+found

drwxr-x— 7 oracle dba 4096 Aug 15 14:23 TEST

[oracle@server1 fast_recovery_area]$ ll TEST

total 20

drwxr-x— 30 oracle dba 4096 Aug 15 00:11 archivelog

drwxr-x— 2 oracle dba 4096 Jul 13 11:22 autobackup

drwxr—– 5 oracle dba 4096 Jul 25 17:07 backupset

drwxr-x— 2 oracle dba 4096 Aug 15 14:23 flashback

drwxr-x— 2 oracle dba 4096 Jul 20 12:07 onlinelog

[oracle@server1 fast_recovery_area]$ ll TEST/flashback

total 16040

-rw-r—– 1 oracle dba 8200192 Aug 15 14:31 o1_mf_74l3ynbt_.flb

-rw-r—– 1 oracle dba 8200192 Aug 15 14:23 o1_mf_74l3yoth_.flb

5)add some values in the tables and just show the scn

SQL> SELECT current_scn, SCN_TO_TIMESTAMP(current_scn) FROM v$database;

CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)

———– ——————————-

29034095 15-AUG-11 02.35.27.000000000 PM

SQL> SELECT * FROM test1;

ID DESCR

———- ————–

1 One

2 Two

3 Three

SQL> ALTER TABLE test1 ADD (column1 VARCHAR2(20));

TABLE altered.

SQL> UPDATE test1 SET column1=’Temporary’;

3 ROWS updated.

SQL> COMMIT;

COMMIT complete.

SQL> SELECT * FROM test1;

ID DESCR COLUMN1

———- —————- ————-

1 One TEMPORARY

2 Two TEMPORARY

3 Three TEMPORARY

SQL> SELECT current_scn, SCN_TO_TIMESTAMP(current_scn) FROM v$database;

CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)

———– ———————————

29034142 15-AUG-11 02.36.11.000000000 PM

6) flashback query — we can only show the past state of the table

SQL> flashback DATABASE as of scn 29034095;

Flashback complete.

SQL> SELECT * FROM nijam.test1;

ID DESCR

———- ————–

1 One

2 Two

3 Three

7) flashback table — to flash back a table to an earlier scn or timestamp

Enabling row movement for your test table is mandatory for flashback table:

SQL> ALTER TABLE test1 enable ROW movement;

TABLE altered.

Database must be in mount state to flashback it:

SQL> shutdown IMMEDIATE;

DATABASE closed.

DATABASE dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total SYSTEM Global Area 1068937216 bytes

Fixed SIZE 2233336 bytes

Variable SIZE 796920840 bytes

DATABASE Buffers 260046848 bytes

Redo Buffers 9736192 bytes

DATABASE mounted.

SQL> flashback DATABASE TO scn 29034095;

Flashback complete.

SQL> ALTER DATABASE OPEN resetlogs;

DATABASE altered.

SQL> SELECT * FROM nijam.test1;

ID DESCR

———- ————–

1 One

2 Two

3 Three

8) Inserting few test rows and performing a “wrong” update:

SQL> SELECT current_scn, SCN_TO_TIMESTAMP(current_scn) FROM v$database;

CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)

———– ———————————–

28947307 15-AUG-11 09.35.57.000000000 AM

SQL> SELECT * FROM test1;

ID DESCR

———- ——————

1 One

2 Two

3 Three

SQL> INSERT INTO test1 VALUES(4,’Five’);

1 ROW created.

SQL> SELECT * FROM test1;

ID DESCR

———- —————–

1 One

2 Two

3 Three

4 Five

SQL> UPDATE test1 SET descr=’Four’;

4 ROWS updated.

SQL> COMMIT;

COMMIT complete.

SQL> SELECT * FROM test1;

ID DESCR

—– ————–

1 Four

2 Four

3 Four

4 Four

9) Flashing back table to original good state (SCN or timestamp taken in previous step, Oracle suggests to record current SCN before issuing such command):

SQL> SELECT current_scn, SCN_TO_TIMESTAMP(current_scn) FROM v$database;

CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)

———– ————————————-

28947426 15-AUG-11 09.38.03.000000000 AM

SQL> flashback TABLE test1 TO scn 28947307;

Flashback complete.

SQL> SELECT * FROM test1;

ID DESCR

———- ——————————

1 One

2 Two

3 Three

You can also work with restore point (refer to official documentation for retention policies):

SQL> SELECT * FROM test1;

ID DESCR

———- ——————————

1 One

2 Two

3 Three

SQL> CREATE restore point before_upgrade;

Restore point created.

SQL> UPDATE test1 SET descr=’Temporary’;

3 ROWS updated.

SQL> COMMIT;

COMMIT complete.

SQL> SELECT * FROM test1;

ID DESCR

———- ——————————

1 TEMPORARY

2 TEMPORARY

3 TEMPORARY

SQL> SELECT current_scn, SCN_TO_TIMESTAMP(current_scn) FROM v$database;

CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)

———– —————————————-

28949800 15-AUG-11 10.03.02.000000000 AM

SQL> flashback TABLE test1 TO restore point before_upgrade;

Flashback complete.

SQL> SELECT * FROM test1;

ID DESCR

———- ——————————

1 One

2 Two

3 Three

SQL> DROP restore point before_upgrade;

Restore point dropped.

10) Using flasback technology will create SYS_TEMP_FBT in your schema:

SQL> SELECT table_name FROM user_tables;

TABLE_NAME

——————————

SYS_TEMP_FBT

TEST1

11) flashback drop — flashback table to before drop using recylebin

SQL> SELECT table_name FROM user_tables;

TABLE_NAME

——————————

SYS_TEMP_FBT

TEST1

SQL> DROP TABLE test1;

TABLE dropped.

SQL> SELECT table_name FROM user_tables;

TABLE_NAME

——————————

SYS_TEMP_FBT

SQL> show recyclebin;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME

—————- —————————— ———— ——————-

TEST1 BIN$qogQry1iu6TgQEsKbCUKuA==$0 TABLE 2011-08-15:10:29:44

SQL> SELECT * FROM recyclebin;

OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN

—————————— ——————————– ——— ————————- —————————— ——————- ——————- ———-

PARTITION_NAME CAN CAN RELATED BASE_OBJECT PURGE_OBJECT SPACE

——————————– — — ———- ———– ———— ———-

BIN$qogQry1iu6TgQEsKbCUKuA==$0 TEST1 DROP TABLE USERS 2011-08-15:09:35:12 2011-08-15:10:29:44 28951308

YES YES 71097 71097 71097 8

12) Once the object is dropped you cannot access it but you can still query its recyclebin counterpart and/or restore it:

SQL> SELECT * FROM test1;

SELECT * FROM test1

ERROR AT line 1:

ORA-00942: TABLE OR VIEW does NOT exist

SQL> SELECT * FROM “BIN$qogQry1iu6TgQEsKbCUKuA==$0”;

ID DESCR

———- ——————————

1 One

2 Two

3 Three

SQL> flashback TABLE test1 TO before DROP;

Flashback complete.

SQL> show recyclebin;

SQL> SELECT * FROM test1;

ID DESCR

———- ——————————

1 One

2 Two

3 Three

You can also purge it with:

SQL> purge recyclebin;

Recyclebin purged.

13) flashback query —we can show only the past state of the table

SQL> SELECT * FROM test1;

ID DESCR

———- ——————————

1 One

2 Two

3 Three

SQL> SELECT table_name FROM user_tables;

TABLE_NAME

——————————

SYS_TEMP_FBT

TEST1

SQL> SELECT current_scn, SCN_TO_TIMESTAMP(current_scn),TO_CHAR(SYSDATE,’dd-mon-yyyy hh24:mi:ss’) AS current_time FROM v$database;

CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN) CURRENT_TIME

———– ————————————————————————— —————————–

28954179 15-AUG-11 11.03.48.000000000 AM 15-aug-2011 11:03:48

SQL> UPDATE test1 SET descr=’Temporary’;

3 ROWS updated.

SQL> COMMIT;

COMMIT complete.

SQL> SELECT * FROM test1;

ID DESCR

———- ——————————

1 TEMPORARY

2 TEMPORARY

3 TEMPORARY

The flashback query feature works with AS OF SCN and AS OF TIMESTAMP in SELECT statement:

SQL> SELECT * FROM test1

AS OF scn 28954179;

ID DESCR

———- ——————————

1 One

2 Two

3 Three

SQL> SELECT * FROM test1

AS OF TIMESTAMP TO_TIMESTAMP(’15-AUG-11 11.03.48.000000000 AM’);

ID DESCR

———- ——————————

1 One

2 Two

3 Three

SQL> SELECT * FROM test1

AS OF TIMESTAMP SYSTIMESTAMP – INTERVAL ’10’ MINUTE

ID DESCR

———- ——————————

1 One

2 Two

3 Three

You can also use DBMS_FLASHBACK package for flashback query. This can as well been done with SCN using ENABLE_AT_SYSTEM_CHANGE_NUMBER procedure or timestamp using ENABLE_AT_TIME procedure:

SQL> EXEC dbms_flashback.enable_at_system_change_number(28954179);

PL/SQL PROCEDURE successfully completed.

SQL> SELECT * FROM test1;

ID DESCR

———- ——————————

1 One

2 Two

3 Three

SQL> EXEC dbms_flashback.disable;

PL/SQL PROCEDURE successfully completed.

SQL> SELECT * FROM test1;

ID DESCR

———- ——————————

1 TEMPORARY

2 TEMPORARY

3 TEMPORARY

Finally you can flashback your table using flashback table technology (or insert using AS SELECT in a subquery):

SQL> flashback TABLE test1 TO scn 28954179;

Flashback complete.

SQL> SELECT * FROM test1

ID DESCR

———- ——————————

1 One

2 Two

3 Three

Remark:

This flashback query technology can also be used in export utility (exp and expdp) using FLASHBACK_SCN and FLASHBACK_TIME parameters.

14) flashback version query —show the the table transaction time and scn

SQL> SELECT * FROM test1;

ID DESCR

———- ——————————

1 One

2 Two

3 Three

SQL> SELECT current_scn, SCN_TO_TIMESTAMP(current_scn) FROM v$database;

CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)

———– —————————————————————————

29006118 15-AUG-11 12.51.21.000000000 PM

SQL> UPDATE test1 SET descr=’The one’ WHERE id=1;

1 ROW updated.

SQL> COMMIT;

COMMIT complete.

SQL> UPDATE test1 SET descr=’The only one’ WHERE id=1;

1 ROW updated.

SQL> COMMIT;

COMMIT complete.

SQL> SELECT current_scn, SCN_TO_TIMESTAMP(current_scn) FROM v$database;

CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)

———– —————————————————————————

29006142 15-AUG-11 12.52.03.000000000 PM

note:

Please note the commit after each update to generate multiple transactions.

You can now see past figures:

SQL> SELECT versions_startscn, versions_starttime, versions_endscn, versions_endtime, versions_xid, versions_operation, id, descr

FROM test1

VERSIONS BETWEEN SCN 29006118 AND 29006142

WHERE id = 1;

VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME VERSIONS_XID V ID DESCR

—————– ———————— ————— ———————— —————- – ———- ——————————

29006133 15-AUG-11 12.51.45 PM 0A001500B99F0000 U 1 The only one

29006128 15-AUG-11 12.51.36 PM 29006133 15-AUG-11 12.51.45 PM 060017000A100000 U 1 The one

29006128 15-AUG-11 12.51.36 PM 1 One

SQL> SELECT * FROM test1 AS OF scn 29006128;

ID DESCR

———- ——————————

1 The one

2 Two

3 Three

SQL> SELECT * FROM test1 AS OF scn 29006118;

ID DESCR

———- ——————————

1 One

2 Two

3 Three

SQL> SELECT * FROM test1;

ID DESCR

———- ——————————

1 The only one

2 Two

3 Three

Note:

Same as DBMS_FLASHBACK package you may work with timestamp or SCN

15) flashback transaction query —it show what operation is done and which user is performed

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

DATABASE altered.

If we use flashback query example:

SQL> SELECT xid, operation, start_scn, commit_scn, logon_user, undo_sql

FROM flashback_transaction_query

WHERE xid = HEXTORAW(‘0A001500B99F0000’);

no ROWS selected

SQL> SELECT xid, operation, start_scn, commit_scn, logon_user, undo_sql

FROM flashback_transaction_query

WHERE xid = HEXTORAW(‘060017000A100000’);

XID OPERATION START_SCN COMMIT_SCN LOGON_USER

—————- ——————————– ———- ———- ——————————

UNDO_SQL

——————————————————————————————————————————————————————————————————–

060017000A100000 UPDATE 29006126 29006128 YJAQUIER

UPDATE “YJAQUIER”.”TEST1″ SET “DESCR” = ‘One’ WHERE ROWID = ‘AAARW7AAEAABn6GAAA’;

060017000A100000 BEGIN 29006126 29006128 YJAQUIER

First strange thing is missing information for one of the DML statement… After a while if you again select the existing one:

SQL> SELECT xid, operation, start_scn, commit_scn, logon_user, undo_sql

FROM flashback_transaction_query

WHERE xid = HEXTORAW(‘060017000A100000’);

no ROWS selected

Behavior is really erratic and this is explained by bugs, apparently corrected in 11.2.0.2.2, so not really mature so far:

Bug 10358019 – Queries against FLASHBACK_TRANSACTION_QUERY return wrong results [ID 10358019.8]

Note:

Oracle 11gR2 extend this functionality with flashback transaction backout by flashing back a specific transactions and all its dependent transactions.

16) Flashback Data Archive

Flashback Data Archive (FDA) technology is an extension of flashback versions query technology and extend Undo functionality (bypassing UNDO_RETENTION parameter) by keeping figures for fixed pre-defined period (if enough available space obviously). On the paper it’s perfect: transparent, efficient, old figures stored in compressed format, answers to SOX requirements. Small drawback is the cost as you must purchase Oracle Total Recall option to use it…

First start by creating a dedicated tablespace (you may instead use an existing one):

SQL> CREATE TABLESPACE fda datafile ‘/oracle/data01/test/fda01.dbf’ SIZE 100m

extent management local SEGMENT SPACE management auto;

TABLESPACE created.

Then create a default flashback data archive (then no need to specify one when activating FDA on your tables), no quota to use whole tablespace and retention set to one month:

SQL> CREATE flashback archive DEFAULT fla1

TABLESPACE fda

retention 1 MONTH;

Flashback archive created.

Then modify your table:

SQL> ALTER TABLE test1 flashback archive;

TABLE altered.

Note:

To see what’s activated and your FDA you may use the following queries (USER and ALL counterparts may also be used)

SELECT * FROM dba_flashback_archive;

SELECT * FROM dba_flashback_archive_ts;

SELECT * FROM dba_flashback_archive_tables;

Let’s modify the table and see past figures, same as versions query and you may use SCN or timestamp. From pure SQL standpoint you don’t see difference with versions query, except in retention policy…

SQL> SELECT * FROM test1;

ID DESCR

———- ——————————

1 One

2 Two

3 Three

SQL> SELECT current_scn, SCN_TO_TIMESTAMP(current_scn) FROM v$database;

CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)

———– —————————————————————————

29045024 15-AUG-11 04.22.20.000000000 PM

SQL> UPDATE test1 SET descr=’The one’ WHERE id=1;

1 ROW updated.

SQL> COMMIT;

COMMIT complete.

SQL> SELECT * FROM test1;

ID DESCR

———- ——————————

1 The one

2 Two

3 Three

SQL> SELECT * FROM test1 AS OF scn 29045024;

ID DESCR

———- ——————————

1 One

2 Two

3 Three

SQL> SELECT current_scn, SCN_TO_TIMESTAMP(current_scn) FROM v$database;

CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)

———– —————————————————————————

29045079 15-AUG-11 04.23.29.000000000 PM

SQL> UPDATE test1 SET descr=’The only one’ WHERE id=1;

1 ROW updated.

SQL> COMMIT;

COMMIT complete.

SQL> SELECT * FROM test1 AS OF scn 29045079;

ID DESCR

———- ——————————

1 The one

2 Two

3 Three

In this example past version of the table will be kept for one month and then automatically deleted.

With only few exceptions DDLs are supported and for unsupported ones you may use DBMS_FLASHBACK_ARCHIVE PL/SQL package:

SQL> ALTER TABLE test1 ADD (column1 VARCHAR2(20));

TABLE altered.

SQL> SELECT * FROM test1;

ID DESCR COLUMN1

———- —————————— ——————–

1 The only one

2 Two

3 Three

SQL> SELECT * FROM test1 AS OF scn 29045024;

ID DESCR

———- ——————————

1 One

3 Three

2 Two

Reclaiming Oracle Disk Space,Datafile.Tablespace,Tables

December 3, 2016

By postgresdba

Very often DBAs are requested( by user or by force to optimze space requirement or performance) to re-org tables and indexes to get free space after a mass housekeeping of application or after some weeks of application run. In order to take back space from datafile/tablespace DBA can do one or many of few standard things:

1. move table to same or different tablespace. Rebuild of indexes is must after move of table as rowid of rows are changed.

2. do export and import of table. Need business outage.

3. rebuild only indexes(with/without online) to claim free space of indexes only.

4. alter table table_name shrink space cascade; This will try to put blocks togather starting from start of first free block in the datafile. Cascade option will take care of indexes, so no need to rebuild indexes in this case.

5. online re-org of tablespace/tables with exception for tables with data type long and raw.

The Oracle extents are mapped to OS data blocks. The mapping can be revealed by dba_free_space and dba_extents. We can check the actual layout of segments and free space in datafile by below command:

select file_id, block_id first_block, block_id+blocks-1 last_block,

segment_name

from dba_extents

where tablespace_name = ‘BHUSHAN_DATA’

union all

select file_id, block_id, block_id+blocks-1, ‘free’

from dba_free_space

where tablespace_name = ‘BHUSHAN_DATA’

order by file_id, first_block

We can play around with above query to get information of each segment, related datafile etc.

There are 2 types of Fragmentation in Oracle:

1.Honeycomb fragmentation:

This is the fragmentation when free space is side by side. Honeycomb fragmentation donot bother you much until it is at the very beginning of the datafile. Mostly Shrink space cascade for the segment which is live before honeycomb fragmentation gets you rid of this fragmentation.

2.swiss cheese fragmentation:

This is the fragmentation when the free extents are separated by live segments.

If we query any tablespace with above shared script, we may get something like :

20 73400 73407 BHUSHAN00

20 73408 73415 free

20 73416 73423 BHUSHAN00

Mostly, we have combination of both fragmentation and thats make it challenging.

Often DBA fire any of the re-org command without analyzing the order in which we should do so as to take benefit to the max and get the re-org done completely.

In my scenario, I had 15 indexes on a datafile in a tablespace. The total size on segments was 500mb and total size of datafile was 15000mb. Still I was not able to claim any space to OS for other datafile. There was lot of fragmentation for sure which got revealed on running above mentioned query.

After my effort I could claim all 11000mb.

The best approach i found was:

find the order in which extents for segment is scattered in datafile. Start rebiuld index/shrink table from the bottom or top where you find more free blocks.

Oracle has several tools to help reclaim disk space:

alter database datafile xxx.dbf resize yym; – This will remove space that the physical “end” if the datafile, and the command will not work if any segments extend beyond your resize boundary.
alter tablespace xxx coalesce – This command will reclaim space from honeycomb fragmentation

Oracle leaves the high-water mark alone after rows are deleted, and you can reclaim space at the table level with these techniques, all of which lower the high water mark for the table, thereby freeing-up the space:

export-import – For a complete reorganization and space reclamation, export/import allows you to restructure your files and reclaim lost space.
dbms_redefinition – This procedure will reorganize a table while it remains online for updates.
alter table xxx shrink – If you were 10g and beyond you could use “alter table xxx shrink space compact;” syntax.
You can look at the coalesce table syntax. Unlike the “deallocate unused space” syntax which removes space above the high-water mark, “coalesce” puts together discontiguous fragmented extents. There are two type of space fragmentation in Oracle.

First is the honeycomb fragmentation, when the free extents are side by side, and the “Swiss Cheese” fragmentation, when the extents are separated by live segments.

alter table xxx coalesce;

You can also deallocate unused space. Oracle notes that the “deallocate unused space” clause is used to to explicitly deallocate unused space at “the end” of a segment and makes that space available for other segments within the tablespace.

alter table xxx deallocate unused space;

alter index xxx deallocate unused space;

Internally, Oracle deallocates unused space beginning from the end of the objects (allocated space) and moving downwards toward the beginning of the object, continuing down until it reaches the high water mark (HWM). For indexes, “deallocate unused space” coalesces all leaf blocks within same branch of b-tree, and quickly frees up index leaf blocks for use.

Tablespace Level Fragmentation — Defragmentation/Shrink A Big Tablespace In Oracle

December 3, 2016

By postgresdba

The most efficient/effective way to defrag/shrink a big tablespace in oracle is to migrate the tables/indexes to new tablespaces.

Here below are the steps:

Step 1.find tablespace “benz” whether fragmented or not

Step 2.Login with dba account and create new tablespaces for the database user.

Sample SQL:

create tablespace BENZ2 datafile ‘/opt/oracle/storage/BENZ2.dbf’ size 256m autoextend on next 128m maxsize unlimited;

Step 3.Login with the db owner username/password

Step 4.Migrate the tables

Generate the table migration script

spool /tmp/username/moveTables.sql

select ‘alter table ‘ || SEGMENT_NAME || ‘ move tablespace BENZ2;’

FROM dba_Segments a,

dba_data_files b

WHERE b.file_id=a.relative_fno

and a.tablespace_name=‘BENZ’ and segment_type=‘TABLE’

order by FILE_NAME,segment_name;

spool off;

Step 5.Migrate the Indexes

Generate the index migration script

spool /tmp/username/MoveIndex.sql

select ‘alter index ‘ || SEGMENT_NAME || ‘ rebuild tablespace BENZ2;’

FROM dba_Segments a,

dba_data_files b

WHERE b.file_id=a.relative_fno

and a.tablespace_name=‘BENZ’ and segment_type=‘INDEX’

order by FILE_NAME,segment_name;

spool off;

Step 6. Migrate the LOB/LOBSegments if possible

spool /tmp/username/MoveLob.sql

select ‘ALTER TABLE ‘ || table_name || ‘ move lob(‘ || COLUMN_NAME || ‘) STORE AS (TABLESPACE BENZ2);’

from dba_tab_columns

where owner=‘BENZ’ and data_type=‘CLOB’;

spool off;

Step 7.check if anything missing in the original tablespace

set lin 300

col owner format A26

col segment_name format A26

col segment_type format A26

col tablespace_name format A26

col relative_fno format 99999

col file_name format A50

SELECT owner, segment_name, segment_type,a.tablespace_name, a.relative_fno, b.file_name

FROM dba_Segments a,

dba_data_files b

WHERE b.file_id=a.relative_fno

and a.tablespace_name=‘BENZ’

order by FILE_NAME,segment_name;

Step 8. Never forget to change the default tablespace of the user to the new one

ALTER USER default tablespace BENZ2;

Step 9.change tablespace offline

alter tablespace BENZ offline;

Ora-00020: Maximum Number Of Processes (1100), Exceeded Ora-01012: Not Logged On

December 3, 2016

By postgresdba

The below error may trigger when you hit with max sessions exceeded

ERROR at line 1:

ORA-01012: not logged on

What is the cause of this ORA-00020 error and how do I fix it?

Answer: The ORA-00020 is a serious production error because a user cannot connect.

The ORA-00020 is caused by two things:

1. Disconnected processes: Rogue “zombie” connections to Oracle that are idle (not working). To fix this, use the ALTER SYSTEM KILL command. You may also need to kill session at the OS level with the KILL -9 or the ORAKILL command.

2. Too few process buckets: Oracle limits the number of connected processes with the processes parameter, and you may get the ORA-00020 error as the natural result in growth of system usage.

To fix this, increase the processes parameter, usually doubling the value to allow for future growth.

The OERR command shows these details for the ORA-00020 error:

ORA-00020: maximum number of processes (string) exceeded

Cause: All process state objects are in use.

Action: Increase the value of the PROCESSES initialization parameter

Show parameter process

ORA-01261: Parameter Db_Recovery_File_Dest Destination String Cannot Be Translated ORA-01262: Stat Failed On A File Destination Directory Linux-X86_64 Error: 2: No Such File Or Directory

December 3, 2016

By postgresdba

While I start my database with my initialization parameter it fails with oracle error

Solution :-

su oracle

run environment variable

sqlplus / as sysdba

SQL>create pfile from spfile;

Then remove or comment below line in pfile

db_recovery_file_dest

Save and exit

su oracle

run environment variable

sqlplus / as sysdba

SQL>startup mount;

ORACLE instance started.

Total System Global Area 1068937216 bytes

Fixed Size 2166536 bytes

Variable Size 427819256 bytes

Database Buffers 624951296 bytes

Redo Buffers 14000128 bytes

it should became the mount stage then open the DB as follows

SQL>alter database open;

Database altered.

Change The Database Instance After Installation In Oracle Using Nid Utility

December 3, 2016

By postgresdba

1. Shut down the database

2. Startup mount database

3. Run the nid utility as below and also as below recommended please change the parameter file for the dbname parameter and you cna open up the database

nid target=sys/oracle dbname=NIJAM setname=YES

DBNEWID: Release 11.2.0.3.0 – Production on Wed Dec 24 09:09:19 2014

Connected to database BENZ (DBID=790652138)

Connected to server version 11.2.0

Control Files in database:

/u02/app/benz/control/control1.ctl

/u02/app/benz/control/control2.ctl

/u02/app/benz/control/control3.ctl

Change database name of database BENZ to NIJAM? (Y/ [N]) => Y

Proceeding with operation

Changing database name from BENZ to NIJAM

Control File /u02/app/benz/control/control1.ctl – modified

Control File /u02/app/benz/control/control2.ctl – modified

Control File /u02/app/benz/control/control3.ctl – modified

Datafile /u02/app/oracle/benz/system01.db – wrote new name

Datafile /u02/app/oracle/benz/sysaux01.db – wrote new name

Datafile /u02/app/oracle/benz/undotbs01.db – wrote new name

Datafile /u02/app/oracle/benz/user01.db – wrote new name

Datafile /u02/app/oracle/benz/apex01.db – wrote new name

Datafile /u02/app/oracle/benz/ggs_data01.db – wrote new name

Datafile /u02/app/benz/temp01.db – wrote new name

Control File /u02/app/benz/control/control1.ctl – wrote new name

Control File /u02/app/benz/control/control2.ctl – wrote new name

Control File /u02/app/benz/control/control3.ctl – wrote new name

Instance shut down

Database name changed to NIJAM.

Modify parameter file and generate a new password file before restarting.

Successfully changed database name.

DBNEWID – Completed successfully.

SQL> select name, open_mode, database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE

——— ——————- —————-

NIJAM READ WRITE PRIMARY

4. Change db_name in spfile (or in pfile editing the file):

Alter system set db_name=newname scope=spfile;

5. Recreate password file:

orapwd file=orapwNIJAM password=oracle

6. Startup the database

Startup

7. Post rename steps:

Change SID in listener.ora
Correct tnsnames.ora
Remove old trace directories

Change /etc/oratab (UNIX) or rename windows service using oradim

Check The Oracle Database Version

December 3, 2016

By postgresdba

SQL> select banner from v$version;

BANNER

——————————————————————————–

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

PL/SQL Release 11.2.0.3.0 – Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0 – Production

NLSRTL Version 11.2.0.3.0 – Production

Maximum Key Length (3215) Exceeded While Doing Rebuild Ora-00604,Ora-01450: In Oracle

December 3, 2016

By postgresdba

SQL> Alter index SAI.TESTVALUE_IDX rebuild online;

Alter index SAI.TESTVALUE_IDX rebuild online *

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01450: maximum key length (3215) exceeded

Cause of the Problem

When creating an index the total length of the index cannot exceed a certain value. Primarily this value depends on DB_BLOCK_SIZE.

If 2K block size then maximum index key length=758

If 4K block size then maximum index key length=1578

If 8K block size then maximum index key length=3218

If 16K block size then maximum index key length=6498

How the maximum index key length is measured by?

Maximum index key length=Total index length (Sum of width of all indexed column + the number of indexed columns) +Length of the key (2 bytes) +ROWID (6 bytes) +the length of the rowid (1 byte)

The index key size is limited by the value of db_block_size, because a key value may not span multiple blocks. So, based on the size of the block size of index depends. In fact, it is required that any index block must contain at least TWO index entries per block.

So we can say that the maximum key length for an index will be less than half of the DB_BLOCK_SIZE. But we know that in a block there also needed space for PCTFREE, INITRANS and space for block overhead (Block Header, ROW Directory, Table Directory, etc.). After considering these bytes the actual space that can be used for the Index key is actually just over 1/3 of the DB_BLOCK_SIZE.

Solution of the Problem

1) Increase your database block size. Create a tablespace with bigger block size and create index on that tablespace.

2) If you have index on multiple columns then you can split index to single or 2 columns so that size does not extended over it can handle.

3) Rebuild the index without online clause. That is ALTER INDEX index_name REBUILD;

Because the online rebuild of the index creates a journal table and index. This internal journal IOT table contains more columns in its index. This is a feature of online rebuild. This time the error arises because that current value of the initialization parameter db_block_size is not large enough to create internal journal IOT.