Non-critical losses are failure events that can be resolved without significantly impacting the operation of the database if performed properly. A good DBA should be able to resolve non-critical
losses quickly and leave the database in operational state throughout the recovery process. Though during the recovery process there may be performance perform but it should be operational state.
Non-critical files are essentially database files that do not have a critical impact on the operations of the database when they have been compromised. If recovery is performed properly, these files can be recovered or rebuilt in some cases with minimal impact to database operations.
Non-Critical Files in Oracle Database.
-------------------------------------------------
1)Temporary tablespaces: If temporary tablespace or its datafiles are deleted accidentally then they can be recreated easily within any database shut down.
In order to create new one issue,
SQL>create temporary tablespace temp2 tempfile '/oradata/temp01.dbf' size 100M;
Then explicitly assign users to this temporary tablespace, like
SQL>ALTER USER ARJU TEMPORARY TABLESPACE TEMP;
In order to make database default temporary tablespace to temp2 use which will affect of all users whether they are explicitly set or not.,
An example:
i)create a Temporary Tablespace.
SQL> CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '/oradata2/temp02.dbf' SIZE 10m;
Tablespace created.
SQL> ALTER USER ARJU TEMPORARY TABLESPACE TEMP;
User altered.
ii)Look at database default tablespace and temporary tablespace of user ARJU.
SQL> SELECT PROPERTY_VALUE,PROPERTY_NAME FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE '%TABLESPACE%';
PROPERTY_VALUE PROPERTY_NAME
------------------------------ ------------------------------
TEMP DEFAULT_TEMP_TABLESPACE
USERS DEFAULT_PERMANENT_TABLESPACE
SQL> SELECT TEMPORARY_TABLESPACE FROM DBA_USERS WHERE USERNAME='ARJU';
TEMPORARY_TABLESPACE
------------------------------
TEMP
iii)Now change the database default temporary tablespace.
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;
Database altered.
iv) You will see all schema users' tablespace is set to default.
SQL> SELECT TEMPORARY_TABLESPACE FROM DBA_USERS WHERE USERNAME='ARJU';
TEMPORARY_TABLESPACE
------------------------------
TEMP2
v)However now you can change a user to point another tablespace.
SQL> ALTER USER ARJU TEMPORARY TABLESPACE TEMP;
User altered.
SQL> SELECT TEMPORARY_TABLESPACE FROM DBA_USERS WHERE USERNAME='ARJU';
TEMPORARY_TABLESPACE
------------------------------
TEMP
2)Non-current redo log files: If the status of redo log group is not current then dropping them will continue work in database.
In order to drop a redo log group query from V$LOG.
If the status is current then you can't drop it. Drop a non-current redo log group by,
SQL> ALTER DATABASE DROP LOGFILE GROUP 4;
To add new group with one memeber,
SQL> ALTER DATABASE ADD LOGFILE '/oradata2/data1/dbase/redo04.log' SIZE 5m;
To add a member in a group issue,
alter database add logfile member
'/oradata2/data1/dbase/redo01_01.log' to group 1;
To delete a member from a log group issue where status is no-current,
alter database drop logfile member
'/oradata2/data1/dbase/redo01_01.log';
3)Index tablespaces: If the tablespace containing index or index datafile is dropped then database performance might suffer. Users can continue work. However in order to recover them it is needed to rebuild the index.
4)Indexes: If any index is deleted then recover them by simply creating script and run them.
5)Read-only tablespaces: Read-only tablespaces are by nature non-critical database files. These tablespaces are static or do not have data modifications like normal tablespaces. This allows recovery to be a fairly straightforward process under most circumstances. No redo log information needs to be applied to recover read-only tablespaces if they stayed in read only mode since backup. Simply restore them from backup.
Like in RMAN,
RMAN>RESTORE TABLESPACE TBS_READ_ONLY;
or from OS,
$scp source_location location_in_control_file
Saturday, May 17, 2008
ORA-00354 ORA-00353 ORA-00312 Corrupt redo block header
Error Description:
----------------------------
I ran DML operation on database and it failed with exception. I looked for alert log and the entry is ,
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 11037 change 118820174 time 04/22/2008 11:15:12
ORA-00312: online log 2 thread 1: '/oradata1/system/ARJU/redo02.log'
Cause of The Problem:
----------------------------
The redo log file has been corrupted. It may be corrupted through various reasons. Suppose there is disk full or hardware failure.
Solution of The Problem:
------------------------------.
Solution 1 or 2 is the option while database is open and solution 3 is the option if database is closed.
Solution 1: Look at the disk space usage containing redo log. If disk is full then free it by removing unwanted files. If disk is ok then try to clear the log file. Here from error we see log 2 is corrupted. So , clear group 2 Like,
SQL>ALTER DATABASE CLEAR LOGFILE GROUP 2;
Clear logfile does the thing of dropping logfile and then create. The advantage of this procedure is it can be used if you have only two redo log group.
Solution 2: If redo log was not archived (query from V$LOG) then you must specify UNARCHIVED LOGFILE. Otherwise error will come.
Try, SQL>ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;
After issuing this you have lost archived data, so take backup immediately.
In both 1 and 2 if it was the logfile member of online redo log group then you must issue,
SQL> alter system checkpoint;
after clearing it.
Solution 3: If both does not work then do a point in time recovery.
SQL>SHUTDOWN ABORT;
SQL>STARTUP MOUNT;
RMAN>RESTORE DATABASE;
SQL>RECOVER DATABASE UNTIL CANCEL; CANCEL
SQL>ALTER DATABASE OPEN RESETLOGS;
Point in time recovery is described in DBPITR
If database can't be opened that solution 3 is the only option.
----------------------------
I ran DML operation on database and it failed with exception. I looked for alert log and the entry is ,
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 11037 change 118820174 time 04/22/2008 11:15:12
ORA-00312: online log 2 thread 1: '/oradata1/system/ARJU/redo02.log'
Cause of The Problem:
----------------------------
The redo log file has been corrupted. It may be corrupted through various reasons. Suppose there is disk full or hardware failure.
Solution of The Problem:
------------------------------.
Solution 1 or 2 is the option while database is open and solution 3 is the option if database is closed.
Solution 1: Look at the disk space usage containing redo log. If disk is full then free it by removing unwanted files. If disk is ok then try to clear the log file. Here from error we see log 2 is corrupted. So , clear group 2 Like,
SQL>ALTER DATABASE CLEAR LOGFILE GROUP 2;
Clear logfile does the thing of dropping logfile and then create. The advantage of this procedure is it can be used if you have only two redo log group.
Solution 2: If redo log was not archived (query from V$LOG) then you must specify UNARCHIVED LOGFILE. Otherwise error will come.
Try, SQL>ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;
After issuing this you have lost archived data, so take backup immediately.
In both 1 and 2 if it was the logfile member of online redo log group then you must issue,
SQL> alter system checkpoint;
after clearing it.
Solution 3: If both does not work then do a point in time recovery.
SQL>SHUTDOWN ABORT;
SQL>STARTUP MOUNT;
RMAN>RESTORE DATABASE;
SQL>RECOVER DATABASE UNTIL CANCEL; CANCEL
SQL>ALTER DATABASE OPEN RESETLOGS;
Point in time recovery is described in DBPITR
If database can't be opened that solution 3 is the only option.
ORA-00313: open failed for members of log group
Error Description:
------------------------
Today one of my developer said that I am getting the following error from database. He send me the message with error number,
ORA-00257: archiver error. Connect internal only, until freed.
I immediately look for V$RECOVERY_FILE_DEST as per ORA-00257 Archiver Error and I saw that there is enough space available there. So, I look for alert log. There is the error entry,
ORA-00313: open failed for members of log group 2 of thread 1
Cause of The Problem:
-------------------------------
The developer has ran a huge insert in my database which made full of the redo log.
Solutions of The Problem:
--------------------------------
Solution 1)Use Nologging operation while doing huge DML operation.And insert make as as usual.
ALTER TABLE TABLE_NAME NOLOGGING;
run DML statement and then
ALTER TABLE TABLE_NAME LOGGING;
Solution 2)Increase the redo log size.
ALTER DATABASE ADD LOGFILE '/oradata/redo1.log' SIZE 100M;
Solution 3)Tune the statements.
Tune the DML statement to use less redo, or divide the statement into smaller ones will also help to avoid error.
------------------------
Today one of my developer said that I am getting the following error from database. He send me the message with error number,
ORA-00257: archiver error. Connect internal only, until freed.
I immediately look for V$RECOVERY_FILE_DEST as per ORA-00257 Archiver Error and I saw that there is enough space available there. So, I look for alert log. There is the error entry,
ORA-00313: open failed for members of log group 2 of thread 1
Cause of The Problem:
-------------------------------
The developer has ran a huge insert in my database which made full of the redo log.
Solutions of The Problem:
--------------------------------
Solution 1)Use Nologging operation while doing huge DML operation.And insert make as as usual.
ALTER TABLE TABLE_NAME NOLOGGING;
run DML statement and then
ALTER TABLE TABLE_NAME LOGGING;
Solution 2)Increase the redo log size.
ALTER DATABASE ADD LOGFILE '/oradata/redo1.log' SIZE 100M;
Solution 3)Tune the statements.
Tune the DML statement to use less redo, or divide the statement into smaller ones will also help to avoid error.
ORA-01466: unable to read data - table definition has changed
Problem description:
--------------------------------
Whenever you tried to flashback of a table immediately after creating table and inserting data into it, or you tried to flashback of the table to a time or to a SCN before creating it then flashback table or flashback query fails with error message,
SQL> select * from TEST_FLASHBACK_FEATURE as of scn 1371097;
select * from TEST_FLASHBACK_FEATURE as of scn 1371097
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
Cause of The Problem:
----------------------------
If you give Flashback SCN or time before creating the table then it is expected as because the table was not there. But the question may come why after creating table the error appear. This is because smon_scn_time is updated every 6 seconds and hence that is the minimum time that the flashback query time needs to be behind the timestamp of the first change to the table. So, if after creating table within 5 seconds we run flashback query then error may come.
Workaround Example and Solution:
----------------------------------
In order to create the problem I have made a script named test_flashback.sql. The script will create table TEST_FLASHBACK_FEATURE and after inserting row it will select flashback SCN. Later we will play with that SCN.
1)Create the Script.
SQL> !vi /export/home/oracle/test_flashback.sql
CREATE TABLE TEST_FLASHBACK_FEATURE(A NUMBER)
/
INSERT INTO TEST_FLASHBACK_FEATURE VALUES(1)
/
COMMIT
/
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL
2.Execute The Script.
SQL> @/export/home/oracle/test_flashback.sql
Table created.
1 row created.
Commit complete.
GET_SYSTEM_CHANGE_NUMBER
------------------------
1371097
3.After getting SCN run two query for example flashback table and flashback query I ran and it failed.
SQL> select * from TEST_FLASHBACK_FEATURE as of scn 1371097;
select * from TEST_FLASHBACK_FEATURE as of scn 1371097
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
SQL> FLASHBACK TABLE TEST_FLASHBACK_FEATURE TO SCN 1371097;
FLASHBACK TABLE TEST_FLASHBACK_FEATURE TO SCN 1371097
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
4.Now I want to do flashback to the SCN of 6 seconds later. For that I converted the SCN to timestamp and manually added 6 seconds in to it.
SQL> SELECT SCN_TO_TIMESTAMP(1371097) FROM DUAL;
SCN_TO_TIMESTAMP(1371097)
---------------------------------------------------------------------------
17-MAY-08 07.20.44.000000000 AM
SQL> SELECT TIMESTAMP_TO_SCN('17-MAY-08 07.20.50 AM') FROM DUAL; --add 6 seconds.
TIMESTAMP_TO_SCN('17-MAY-0807.20.50AM')
---------------------------------------
1371098
5.Now perform flashback with the 6 seconds later SCN.
SQL> SELECT * FROM TEST_FLASHBACK_FEATURE AS OF SCN 1371098;
A
----------
1
SQL> FLASHBACK TABLE TEST_FLASHBACK_FEATURE TO SCN 1371098;
Flashback complete.
--------------------------------
Whenever you tried to flashback of a table immediately after creating table and inserting data into it, or you tried to flashback of the table to a time or to a SCN before creating it then flashback table or flashback query fails with error message,
SQL> select * from TEST_FLASHBACK_FEATURE as of scn 1371097;
select * from TEST_FLASHBACK_FEATURE as of scn 1371097
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
Cause of The Problem:
----------------------------
If you give Flashback SCN or time before creating the table then it is expected as because the table was not there. But the question may come why after creating table the error appear. This is because smon_scn_time is updated every 6 seconds and hence that is the minimum time that the flashback query time needs to be behind the timestamp of the first change to the table. So, if after creating table within 5 seconds we run flashback query then error may come.
Workaround Example and Solution:
----------------------------------
In order to create the problem I have made a script named test_flashback.sql. The script will create table TEST_FLASHBACK_FEATURE and after inserting row it will select flashback SCN. Later we will play with that SCN.
1)Create the Script.
SQL> !vi /export/home/oracle/test_flashback.sql
CREATE TABLE TEST_FLASHBACK_FEATURE(A NUMBER)
/
INSERT INTO TEST_FLASHBACK_FEATURE VALUES(1)
/
COMMIT
/
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL
2.Execute The Script.
SQL> @/export/home/oracle/test_flashback.sql
Table created.
1 row created.
Commit complete.
GET_SYSTEM_CHANGE_NUMBER
------------------------
1371097
3.After getting SCN run two query for example flashback table and flashback query I ran and it failed.
SQL> select * from TEST_FLASHBACK_FEATURE as of scn 1371097;
select * from TEST_FLASHBACK_FEATURE as of scn 1371097
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
SQL> FLASHBACK TABLE TEST_FLASHBACK_FEATURE TO SCN 1371097;
FLASHBACK TABLE TEST_FLASHBACK_FEATURE TO SCN 1371097
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
4.Now I want to do flashback to the SCN of 6 seconds later. For that I converted the SCN to timestamp and manually added 6 seconds in to it.
SQL> SELECT SCN_TO_TIMESTAMP(1371097) FROM DUAL;
SCN_TO_TIMESTAMP(1371097)
---------------------------------------------------------------------------
17-MAY-08 07.20.44.000000000 AM
SQL> SELECT TIMESTAMP_TO_SCN('17-MAY-08 07.20.50 AM') FROM DUAL; --add 6 seconds.
TIMESTAMP_TO_SCN('17-MAY-0807.20.50AM')
---------------------------------------
1371098
5.Now perform flashback with the 6 seconds later SCN.
SQL> SELECT * FROM TEST_FLASHBACK_FEATURE AS OF SCN 1371098;
A
----------
1
SQL> FLASHBACK TABLE TEST_FLASHBACK_FEATURE TO SCN 1371098;
Flashback complete.
ORA-19573: cannot obtain exclusive enqueue for datafile 1
Problem Description:
---------------------------
Whenever you try to restore a datafile or restore database it failed with following message,
SQL> !rman TARGET /
Recovery Manager: Release 10.2.0.1.0 - Production on Sat May 17 06:06:34 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DBASE (DBID=1509380669)
RMAN> RUN{
2> SET UNTIL SCN 1364008;
3> RESTORE DATABASE;
4> RECOVER DATABASE;
5> }
.
.
.
ORA-19573: cannot obtain exclusive enqueue for datafile 1
Cause of The problem:
---------------------------
If you try to Restore database then your target database must be in mounted stage but not open. If the database is in open state then whenever you give restore then restore operation is attempting to overwrite the currently active version of the datafile and hence the error comes. Alternatively , whenever a datafile is online and you attempt to restore that datafile the error will come.
Solution of The problem:
------------------------------
Check the status of database or the datafile which you want to restore.
In case of database,
SQL> SELECT STATUS FROM V$INSTANCE;
STATUS
--------
OPEN
In case of Datafile,
SQL> select FILE# , STATUS from V$DATAFILE;
If you want to restore database then mount the database like,
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP MOUNT;
If you want to restore a datafile make the datafile offline Like,
SQL>ALTER DATABASE DATAFILE 4 OFFLINE;
And then perform
RMAN>RESTORE DATAFILE 4;
---------------------------
Whenever you try to restore a datafile or restore database it failed with following message,
SQL> !rman TARGET /
Recovery Manager: Release 10.2.0.1.0 - Production on Sat May 17 06:06:34 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DBASE (DBID=1509380669)
RMAN> RUN{
2> SET UNTIL SCN 1364008;
3> RESTORE DATABASE;
4> RECOVER DATABASE;
5> }
.
.
.
ORA-19573: cannot obtain exclusive enqueue for datafile 1
Cause of The problem:
---------------------------
If you try to Restore database then your target database must be in mounted stage but not open. If the database is in open state then whenever you give restore then restore operation is attempting to overwrite the currently active version of the datafile and hence the error comes. Alternatively , whenever a datafile is online and you attempt to restore that datafile the error will come.
Solution of The problem:
------------------------------
Check the status of database or the datafile which you want to restore.
In case of database,
SQL> SELECT STATUS FROM V$INSTANCE;
STATUS
--------
OPEN
In case of Datafile,
SQL> select FILE# , STATUS from V$DATAFILE;
If you want to restore database then mount the database like,
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP MOUNT;
If you want to restore a datafile make the datafile offline Like,
SQL>ALTER DATABASE DATAFILE 4 OFFLINE;
And then perform
RMAN>RESTORE DATAFILE 4;
Thursday, May 15, 2008
Export data to a previous time or point-in-time
From oracle 9i and 10g along with export/import utility the two parameters named FLASHBACK_SCN and FLASHBACK_TIME really help us in order to take dump up to a previous point. This allows use for example, to export the original data of a table, in which rows have been updated erroneously, or rows were deleted by mistake.
In order to illustrate the usage of these two parameters I used an example. In the example I used a table named TEST_NUM and owner of the table is ARJU whose password is A.
1)Determine the current SCN of the database.
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1252872
2)Note the contents of table test_num.
SQL> SELECT * FROM TEST_NUM;
A
----------
100
100
5
7
3)Update the table test_num.
SQL> UPDATE TEST_NUM SET A=A+1;
4 rows updated.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM TEST_NUM;
A
----------
101
101
6
8
4)Export the table test_num with parameter FLASHBACK_SCN to 1252872.
So export operation will be done up to SCN 1252872. Changes after SCN 1252872 will not reflected with dump file.
SQL> HOST expdp ARJU/A TABLES=TEST_NUM FLASHBACK_SCN=1252872
Export: Release 10.2.0.1.0 - Production on Friday, 16 May, 2008 2:50:09
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "ARJU"."SYS_EXPORT_TABLE_01": ARJU/******** TABLES=TEST_NUM FLASHBACK_SCN=1252872
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "ARJU"."TEST_NUM" 4.937 KB 4 rows
Master table "ARJU"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ARJU.SYS_EXPORT_TABLE_01 is:
/oracle/app/oracle/product/10.2.0/db_1/admin/dbase/dpdump/expdat.dmp
Job "ARJU"."SYS_EXPORT_TABLE_01" successfully completed at 02:50:17
Alternatively you can used FLASHBACK_TIME parameter like on unix,
flashback_time=\"TO_TIMESTAMP\(\'16-05-2008 11:21:42\', \'DD-MM-YYYY HH24:MI:SS\'\)\"
On windows,
flashback_time=\"TO_TIMESTAMP('16-05-2008 13:24:26', 'DD-MM-YYYY HH24:MI:SS')\"
Use backslash(\) before special character.
5)Drop the table and Import the dump file.
Here I provide no location so default location is used. While dump as I did not provide any DIRECTORY or DUMPFILE parameter and hence it looked for default directory DATA_PUMP_DIR and by default location of this parameter is $ORACLE_HOME/admin/$ORACLE_SID/dpdump/ (/oracle/app/oracle/product/10.2.0/db_1/admin/dbase/dpdump/) and searched for default dump file which is expdat.dmp.
SQL> DROP TABLE TEST_NUM;
Table dropped.
SQL> HOST impdp ARJU/A
Import: Release 10.2.0.1.0 - Production on Friday, 16 May, 2008 2:51:15
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "ARJU"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "ARJU"."SYS_IMPORT_FULL_01": ARJU/********
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ARJU"."TEST_NUM" 4.937 KB 4 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ARJU"."SYS_IMPORT_FULL_01" successfully completed at 02:51:20
6)Now look at the data in TEST_NUM.
SQL> SELECT * FROM TEST_NUM;
A
----------
100
100
5
7
In order to illustrate the usage of these two parameters I used an example. In the example I used a table named TEST_NUM and owner of the table is ARJU whose password is A.
1)Determine the current SCN of the database.
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1252872
2)Note the contents of table test_num.
SQL> SELECT * FROM TEST_NUM;
A
----------
100
100
5
7
3)Update the table test_num.
SQL> UPDATE TEST_NUM SET A=A+1;
4 rows updated.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM TEST_NUM;
A
----------
101
101
6
8
4)Export the table test_num with parameter FLASHBACK_SCN to 1252872.
So export operation will be done up to SCN 1252872. Changes after SCN 1252872 will not reflected with dump file.
SQL> HOST expdp ARJU/A TABLES=TEST_NUM FLASHBACK_SCN=1252872
Export: Release 10.2.0.1.0 - Production on Friday, 16 May, 2008 2:50:09
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "ARJU"."SYS_EXPORT_TABLE_01": ARJU/******** TABLES=TEST_NUM FLASHBACK_SCN=1252872
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "ARJU"."TEST_NUM" 4.937 KB 4 rows
Master table "ARJU"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ARJU.SYS_EXPORT_TABLE_01 is:
/oracle/app/oracle/product/10.2.0/db_1/admin/dbase/dpdump/expdat.dmp
Job "ARJU"."SYS_EXPORT_TABLE_01" successfully completed at 02:50:17
Alternatively you can used FLASHBACK_TIME parameter like on unix,
flashback_time=\"TO_TIMESTAMP\(\'16-05-2008 11:21:42\', \'DD-MM-YYYY HH24:MI:SS\'\)\"
On windows,
flashback_time=\"TO_TIMESTAMP('16-05-2008 13:24:26', 'DD-MM-YYYY HH24:MI:SS')\"
Use backslash(\) before special character.
5)Drop the table and Import the dump file.
Here I provide no location so default location is used. While dump as I did not provide any DIRECTORY or DUMPFILE parameter and hence it looked for default directory DATA_PUMP_DIR and by default location of this parameter is $ORACLE_HOME/admin/$ORACLE_SID/dpdump/ (/oracle/app/oracle/product/10.2.0/db_1/admin/dbase/dpdump/) and searched for default dump file which is expdat.dmp.
SQL> DROP TABLE TEST_NUM;
Table dropped.
SQL> HOST impdp ARJU/A
Import: Release 10.2.0.1.0 - Production on Friday, 16 May, 2008 2:51:15
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Master table "ARJU"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "ARJU"."SYS_IMPORT_FULL_01": ARJU/********
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ARJU"."TEST_NUM" 4.937 KB 4 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ARJU"."SYS_IMPORT_FULL_01" successfully completed at 02:51:20
6)Now look at the data in TEST_NUM.
SQL> SELECT * FROM TEST_NUM;
A
----------
100
100
5
7
ORA-08189: cannot flashback row movement is not enabled
Error Description:
-----------------------
Whenever I tried to Flashback Table feature of oracle the following error occurs.
SQL> FLASHBACK TABLE TEST_NUM TO TIMESTAMP SYSDATE-1;
FLASHBACK TABLE TEST_NUM TO TIMESTAMP SYSDATE-1
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
Cause of The Problem:
---------------------------
Whenever you performed flashback operation(flashback table, flashback version query,flashback transaction query) of a table except flashback drop feature(which don't need to enable row movement as whole objects come from recycle bin) then above error will come.
Solution of The Problem:
------------------------------------
Row movement of the table need to be enabled in order to perform flashback table.
1)To see whether row movement enabled or disabled issue the following,
SQL> SELECT TABLE_NAME,ROW_MOVEMENT FROM USER_TABLES WHERE TABLE_NAME='TEST_NUM';
TABLE_NAME ROW_MOVE
------------------------------ --------
TEST_NUM DISABLED
2)In order to perform flashback table enable row movement by following query,
SQL> ALTER TABLE TEST_NUM ENABLE ROW MOVEMENT;
Table altered.
Check it by,
SQL> SELECT TABLE_NAME,ROW_MOVEMENT FROM USER_TABLES WHERE TABLE_NAME='TEST_NUM';
TABLE_NAME ROW_MOVE
------------------------------ --------
TEST_NUM ENABLED
3)Now perform flashback operation.
SQL> FLASHBACK TABLE TEST_NUM TO TIMESTAMP SYSDATE-1;
Flashback complete.
-----------------------
Whenever I tried to Flashback Table feature of oracle the following error occurs.
SQL> FLASHBACK TABLE TEST_NUM TO TIMESTAMP SYSDATE-1;
FLASHBACK TABLE TEST_NUM TO TIMESTAMP SYSDATE-1
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
Cause of The Problem:
---------------------------
Whenever you performed flashback operation(flashback table, flashback version query,flashback transaction query) of a table except flashback drop feature(which don't need to enable row movement as whole objects come from recycle bin) then above error will come.
Solution of The Problem:
------------------------------------
Row movement of the table need to be enabled in order to perform flashback table.
1)To see whether row movement enabled or disabled issue the following,
SQL> SELECT TABLE_NAME,ROW_MOVEMENT FROM USER_TABLES WHERE TABLE_NAME='TEST_NUM';
TABLE_NAME ROW_MOVE
------------------------------ --------
TEST_NUM DISABLED
2)In order to perform flashback table enable row movement by following query,
SQL> ALTER TABLE TEST_NUM ENABLE ROW MOVEMENT;
Table altered.
Check it by,
SQL> SELECT TABLE_NAME,ROW_MOVEMENT FROM USER_TABLES WHERE TABLE_NAME='TEST_NUM';
TABLE_NAME ROW_MOVE
------------------------------ --------
TEST_NUM ENABLED
3)Now perform flashback operation.
SQL> FLASHBACK TABLE TEST_NUM TO TIMESTAMP SYSDATE-1;
Flashback complete.
Related Documents
Drop Table in Oracle
Flashback Table and Flashback Drop
Drop Table in Oracle
Flashback Table and Flashback Drop
Startup fails with oracle error ORA-00119, ORA-00132
Error Description:
---------------------------
Database Startup fails with oracle error ORA-00119, ORA-00132
SQL> startup mount
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_DBASE'
Cause of The problem:
-------------------------
If the tnsnames.ora entry was used for the value of the LOCAL_LISTENER and the LOCAL_LISTENER entry inside tnsnames.ora is changed or tnsnames.ora file is moved and hence the tns alias to which the LOCAL_LISTENER parameter points is no longer valid. So, the entry inside spfile or pfile to be unresolvable, and the database will not start.
PMON must be able to resolve whatever value the LOCAL_LISTENER or REMOTE_LISTENER parameter is set to. Here LISTENER_DBASE is the name of the local listener.
Remember the LISTENER_DBASE is NOT the listener name reflected in the listener.ora file but rather it is an alias stored in the tnsnames.ora file.
Solution of The problem
-----------------------------
A)Correct the tnsnames.ora
i)Determine if the tns alias is good by using tnsping.
SQL> !tnsping LISTENER_DBASE
TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 15-MAY-2008 11:57:06
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
TNS-03505: Failed to resolve name
So it could not find the name inside tnsnames.ora.
ii)
Add the LISTENER_DBASE entry in the tnsnames.ora file.
SQL> !vi /oracle/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
LISTENER_DBASE=(DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase')
and run tnsping utility,
SQL> !tnsping LISTENER_DBASE
TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 15-MAY-2008 12:11:40
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase')
TNS-12533: TNS:illegal ADDRESS parameters
So there is syntax error in the LISTENER_DBASE alias. Correct it as here add parenthesis.
SQL> !vi /oracle/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
LISTENER_DBASE=(DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase')))
iii)After correction run tnsping and start the database.
SQL> !tnsping LISTENER_DBASE
TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 15-MAY-2008 12:14:25
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase'))
OK (0 msec)
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 83886120 bytes
Database Buffers 75497472 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.
B)An alternative solution is to remove the LOCAL_LISTENER entry from the listener.ora file.
1)Create spfile from pfile if you don't have pfile upadted.
SQL>CREATE PFILE FROM SPFILE;
2)Open the pfile and remove the LOCAL_LISTENER entry.
$vi /oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase.ora
3)Create spfile from pfile.
SQL>CREATE PFILE FROM SPFILE;
4)Start the database.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 83886120 bytes
Database Buffers 75497472 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.
Recommendation:
------------------
Though you can set the value of LOCAL_LISTENER (or REMOTE_LISTENER parameter if used) as alias in tnsnames.ora like in this example but it is not recommended setting. Instead use a full address descriptor inside pfile or set dynamically inside spfile.
In order to set dynamically inside spfile and in memory log in with dba privileges and issue:
ALTER SYSTEM set LOCAL_LISTENER="(address=(protocol=tcp)(host=yourhost)(port=yourport))" scope=both sid='instancename';
In my system,
SQL> ALTER SYSTEM set LOCAL_LISTENER="(address=(protocol=tcp)(host=neptune)(port=1522))" scope=both sid='dbase';
SQL> show parameter local
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (address=(protocol=tcp)(host=n
eptune)(port=1522))
---------------------------
Database Startup fails with oracle error ORA-00119, ORA-00132
SQL> startup mount
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_DBASE'
Cause of The problem:
-------------------------
If the tnsnames.ora entry was used for the value of the LOCAL_LISTENER and the LOCAL_LISTENER entry inside tnsnames.ora is changed or tnsnames.ora file is moved and hence the tns alias to which the LOCAL_LISTENER parameter points is no longer valid. So, the entry inside spfile or pfile to be unresolvable, and the database will not start.
PMON must be able to resolve whatever value the LOCAL_LISTENER or REMOTE_LISTENER parameter is set to. Here LISTENER_DBASE is the name of the local listener.
Remember the LISTENER_DBASE is NOT the listener name reflected in the listener.ora file but rather it is an alias stored in the tnsnames.ora file.
Solution of The problem
-----------------------------
A)Correct the tnsnames.ora
i)Determine if the tns alias is good by using tnsping.
SQL> !tnsping LISTENER_DBASE
TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 15-MAY-2008 11:57:06
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
TNS-03505: Failed to resolve name
So it could not find the name inside tnsnames.ora.
ii)
Add the LISTENER_DBASE entry in the tnsnames.ora file.
SQL> !vi /oracle/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
LISTENER_DBASE=(DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase')
and run tnsping utility,
SQL> !tnsping LISTENER_DBASE
TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 15-MAY-2008 12:11:40
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase')
TNS-12533: TNS:illegal ADDRESS parameters
So there is syntax error in the LISTENER_DBASE alias. Correct it as here add parenthesis.
SQL> !vi /oracle/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
LISTENER_DBASE=(DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase')))
iii)After correction run tnsping and start the database.
SQL> !tnsping LISTENER_DBASE
TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 15-MAY-2008 12:14:25
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase'))
OK (0 msec)
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 83886120 bytes
Database Buffers 75497472 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.
B)An alternative solution is to remove the LOCAL_LISTENER entry from the listener.ora file.
1)Create spfile from pfile if you don't have pfile upadted.
SQL>CREATE PFILE FROM SPFILE;
2)Open the pfile and remove the LOCAL_LISTENER entry.
$vi /oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase.ora
3)Create spfile from pfile.
SQL>CREATE PFILE FROM SPFILE;
4)Start the database.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 83886120 bytes
Database Buffers 75497472 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.
Recommendation:
------------------
Though you can set the value of LOCAL_LISTENER (or REMOTE_LISTENER parameter if used) as alias in tnsnames.ora like in this example but it is not recommended setting. Instead use a full address descriptor inside pfile or set dynamically inside spfile.
In order to set dynamically inside spfile and in memory log in with dba privileges and issue:
ALTER SYSTEM set LOCAL_LISTENER="(address=(protocol=tcp)(host=yourhost)(port=yourport))" scope=both sid='instancename';
In my system,
SQL> ALTER SYSTEM set LOCAL_LISTENER="(address=(protocol=tcp)(host=neptune)(port=1522))" scope=both sid='dbase';
SQL> show parameter local
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (address=(protocol=tcp)(host=n
eptune)(port=1522))
Can you use flashback database if a period of noarchivelog exists
The scenario is I have enabled flashback feature. Now I have ran the database in noarchivelog mode for some time and then I again enable archivelog mode. Now can I get back to a point to previous flashback point. The answer is no. The minimum requirement to enable flashback database is to keep the database in Archive log mode.
Because in order to switch from noarchivelog mode to archivelog mode you have to disable flashback logging and so you lose the logs unless you have guaranteed restore point.
But if you have guaranteed restore point then you can't switch from noarchivelog mode to archivelog mode and hence you can perform flashback.
In the following section the whole scenario is described with example.
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
SQL> update arju.a set col1=col1+1;
1 rows updated.
SQL> commit;
Commit complete.
SQL> create restore point normal;
Restore point created.
SQL> update arju.a set col1=col1+1;
1 rows updated.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
.
database mounted.
SQL> alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:
ORA-38774: cannot disable media recovery - flashback database is enabled
SQL> alter database flashback off;
Database altered.
SQL> alter database noarchivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> update arju.a set col1=col1+1;
1 rows updated.
SQL> commit;
Commit complete.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
.
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database flashback on;
Database altered.
SQL> flashback database to restore point normal;
flashback database to restore point normal
*
ERROR at line 1:
ORA-38729: Not enough flashback database log data to do FLASHBACK.
SQL> select name from v$restore_point;
NAME
--------------------------------------------------------------------------------
NORMAL
SQL> show parameter DB_FLASHBACK_RETENTION_TARGET
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
SQL> create restore point g_restore guarantee flashback database;
Restore point created.
SQL> alter database open;
Database altered.
SQL> update arju.a set col1=col1;
1 rows updated.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
.
Database mounted.
SQL> alter database flashback off;
Database altered.
SQL> alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:
ORA-38781: cannot disable media recovery - have guaranteed restore points
So, the minimum requirement to enable flashback database is to keep the database in Archive log mode.
Related Documents
arjudba.blogspot.com/2008/04/performing-flashback-database.html
arjudba.blogspot.com/2008/04/restore-point-and-flashback-database.html
arjudba.blogspot.com/2008/05/ora-08189-cannot-flashback-row-movement.html
arjudba.blogspot.com/2008/05/flashback-on-fails-with-ora-38706-and.html
arjudba.blogspot.com/2008/04/how-to-enable-flashback-database.html
arjudba.blogspot.com/2008/04/flashback-database-to-undo-open.html
Because in order to switch from noarchivelog mode to archivelog mode you have to disable flashback logging and so you lose the logs unless you have guaranteed restore point.
But if you have guaranteed restore point then you can't switch from noarchivelog mode to archivelog mode and hence you can perform flashback.
In the following section the whole scenario is described with example.
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
SQL> update arju.a set col1=col1+1;
1 rows updated.
SQL> commit;
Commit complete.
SQL> create restore point normal;
Restore point created.
SQL> update arju.a set col1=col1+1;
1 rows updated.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
.
database mounted.
SQL> alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:
ORA-38774: cannot disable media recovery - flashback database is enabled
SQL> alter database flashback off;
Database altered.
SQL> alter database noarchivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> update arju.a set col1=col1+1;
1 rows updated.
SQL> commit;
Commit complete.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
.
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database flashback on;
Database altered.
SQL> flashback database to restore point normal;
flashback database to restore point normal
*
ERROR at line 1:
ORA-38729: Not enough flashback database log data to do FLASHBACK.
SQL> select name from v$restore_point;
NAME
--------------------------------------------------------------------------------
NORMAL
SQL> show parameter DB_FLASHBACK_RETENTION_TARGET
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target integer 1440
SQL> create restore point g_restore guarantee flashback database;
Restore point created.
SQL> alter database open;
Database altered.
SQL> update arju.a set col1=col1;
1 rows updated.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
.
Database mounted.
SQL> alter database flashback off;
Database altered.
SQL> alter database noarchivelog;
alter database noarchivelog
*
ERROR at line 1:
ORA-38781: cannot disable media recovery - have guaranteed restore points
So, the minimum requirement to enable flashback database is to keep the database in Archive log mode.
Related Documents
arjudba.blogspot.com/2008/04/performing-flashback-database.html
arjudba.blogspot.com/2008/04/restore-point-and-flashback-database.html
arjudba.blogspot.com/2008/05/ora-08189-cannot-flashback-row-movement.html
arjudba.blogspot.com/2008/05/flashback-on-fails-with-ora-38706-and.html
arjudba.blogspot.com/2008/04/how-to-enable-flashback-database.html
arjudba.blogspot.com/2008/04/flashback-database-to-undo-open.html
Flashback ON fails with ORA-38706 and ORA-38714
Problem Description:
-------------------------
Whenever you tried to enable your database flashback feature it failed with errors.
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38714: Instance recovery required.
Cause of the problem:
-------------------------
The database is abnormally terminated. In Order to Enable the Flashback option the database need to be cleaned shutdown. Enabling flashback database after crash or shutdown abort will lead to this error.
Solution of The Problem:
------------------------------
1.Cleanly Shutdown of your database.
SHUTDOWN
or SHUTDOWN IMMEDIATE
or, SHUTDOWN TRANSACTIONAL
2.Mount the database.
SQL>ALTER DATABASE MOUNT;
3.Enable flashback.
SQL>Alter database flashback on;
4.Open the database.
SQL>ALTER DATABASE OPEN;
-------------------------
Whenever you tried to enable your database flashback feature it failed with errors.
SQL> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38714: Instance recovery required.
Cause of the problem:
-------------------------
The database is abnormally terminated. In Order to Enable the Flashback option the database need to be cleaned shutdown. Enabling flashback database after crash or shutdown abort will lead to this error.
Solution of The Problem:
------------------------------
1.Cleanly Shutdown of your database.
SHUTDOWN
or SHUTDOWN IMMEDIATE
or, SHUTDOWN TRANSACTIONAL
2.Mount the database.
SQL>ALTER DATABASE MOUNT;
3.Enable flashback.
SQL>Alter database flashback on;
4.Open the database.
SQL>ALTER DATABASE OPEN;
Recover From Drop or Delete or Truncate Table in Oracle Database
It is common to hear that I accidentally deleted a table. Now how I can get back my table? Or I accidentally deleted some rows and committed. How I can get back my table to previous stage? Though it is takes almost no time to delete but sometimes it takes significant time to recover that table.
In the following section I will demonstrate possible solutions that we can have in order to recover a table.
1)If your database recyclebin parameter is on then if you dropped the table then it is best if you can recover it from recyclebin.
In order to know recylcebin on or off issue,
SQL> SHOW PARAMETER RECYCLEBIN;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
However if you previously on and after dropping off it then still object will be stored in recyclebin unless the tablespace are in space pressure. Now in order to get back your the dropped table simply issuing,
SQL>FLASHBACK TABLE TABLE_NAME TO BEFORE DROP;
Simply an example.
SQL> drop table test2;
Table dropped.
SQL> flashback table test2 to before drop;
Flashback complete.
In order to get back more about flashback drop have a look at Flashback-Section Flashback Drop
2)Import the Table if you have good known logical backup.
If you previous export the table by exp or expdp and after that nothing is modified on the table then you can simply import your dropped table from the dump. Note that if a row in changed after taken dump that row will be not found after import. In order to know how export and import can be done please have a look at,
Data Pump Export/Import
Export/Import from Enterprise Manager
Original Export/Import
3)If you erroneously updated a subset of data and you have higher UNDO RETENTION parameter settings that is your undo data is not aged out; then using flashback query restore old stage of the table. In order to know it have a look at,
Restore old data using flashback query
If you erroneously deleted data of the table and now you want to get back to a previous state of the whole table you can have a look at,
Flashback Table Part, of this link
4)Export Data up to a previous point-in-time.
You can set FLASHBACK_SCN or FLASHBACK_TIME with exp or expdp in order to export table before erroneous update or erroneous deletion of table data. After export test import it in another schema whether exported data is acceptable or not. If you are satisfied then import the data to your primary database schema. The detail procedure is discussed on Export Data up to a previous point-in-time
5)Restore and recover a subset of the database as a DUMMY database to a point-in-time. Then export the table data from that dummy database and import it into the primary database. This is very good option as only the dropped table goes back in time to before the drop.
In order to perform this operation you can have a look at RMAN DUPLICATION database in which rman will duplicate subset of database to a point-in-time of the erroneous operation of the table. And then from that database export the table. RMAN database duplication is discussed on
Performing Database Duplication.
After duplication export the data of the table by using exp or expdp utility and import it to primary database. Export and Import is discussed on,
Data Pump Export/Import
Export/Import from Enterprise Manager
Original Export/Import
6)If you don't have enough undo to restore or don't have good logical backup or you don't have recylecbin parameter on(In case of drop table) or you have truncated the table but you have physical backup then you can do Tablespace point-in-time recovery. In order to know about TSPITR have a look at,
Performing TSPITR . Also have a look at its limitation performing performing. Limitations are described in Limitations of TSPITR
In case of TSPITR the entire tablespace will go back to a previous stage.
7)If you have flashback feature enabled then you can do flashback database to get back the entire database to a prior time.
Like, in mount stage run FLASHBACK DATABASE TO TIMESTAMP SYSTIMESTAMP-1/24/60*30; in order to get back whole database 30 minutes ago. After peforming flashback open the database READ ONLY , export the data , shutdown the database, and start the database with RESETLOGS option, import the data. The flashback database feature is discussed on,
Performing Flashback Database
8)If you are unable to perform any one of the above then you can use RMAN DBPITR to a point in time before the drop. This is an extreme measure for one table as the entire database goes back in time. DBPITR recovery is discussed on How to perform DBPITR
In the following section I will demonstrate possible solutions that we can have in order to recover a table.
1)If your database recyclebin parameter is on then if you dropped the table then it is best if you can recover it from recyclebin.
In order to know recylcebin on or off issue,
SQL> SHOW PARAMETER RECYCLEBIN;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
However if you previously on and after dropping off it then still object will be stored in recyclebin unless the tablespace are in space pressure. Now in order to get back your the dropped table simply issuing,
SQL>FLASHBACK TABLE TABLE_NAME TO BEFORE DROP;
Simply an example.
SQL> drop table test2;
Table dropped.
SQL> flashback table test2 to before drop;
Flashback complete.
In order to get back more about flashback drop have a look at Flashback-Section Flashback Drop
2)Import the Table if you have good known logical backup.
If you previous export the table by exp or expdp and after that nothing is modified on the table then you can simply import your dropped table from the dump. Note that if a row in changed after taken dump that row will be not found after import. In order to know how export and import can be done please have a look at,
Data Pump Export/Import
Export/Import from Enterprise Manager
Original Export/Import
3)If you erroneously updated a subset of data and you have higher UNDO RETENTION parameter settings that is your undo data is not aged out; then using flashback query restore old stage of the table. In order to know it have a look at,
Restore old data using flashback query
If you erroneously deleted data of the table and now you want to get back to a previous state of the whole table you can have a look at,
Flashback Table Part, of this link
4)Export Data up to a previous point-in-time.
You can set FLASHBACK_SCN or FLASHBACK_TIME with exp or expdp in order to export table before erroneous update or erroneous deletion of table data. After export test import it in another schema whether exported data is acceptable or not. If you are satisfied then import the data to your primary database schema. The detail procedure is discussed on Export Data up to a previous point-in-time
5)Restore and recover a subset of the database as a DUMMY database to a point-in-time. Then export the table data from that dummy database and import it into the primary database. This is very good option as only the dropped table goes back in time to before the drop.
In order to perform this operation you can have a look at RMAN DUPLICATION database in which rman will duplicate subset of database to a point-in-time of the erroneous operation of the table. And then from that database export the table. RMAN database duplication is discussed on
Performing Database Duplication.
After duplication export the data of the table by using exp or expdp utility and import it to primary database. Export and Import is discussed on,
Data Pump Export/Import
Export/Import from Enterprise Manager
Original Export/Import
6)If you don't have enough undo to restore or don't have good logical backup or you don't have recylecbin parameter on(In case of drop table) or you have truncated the table but you have physical backup then you can do Tablespace point-in-time recovery. In order to know about TSPITR have a look at,
Performing TSPITR . Also have a look at its limitation performing performing. Limitations are described in Limitations of TSPITR
In case of TSPITR the entire tablespace will go back to a previous stage.
7)If you have flashback feature enabled then you can do flashback database to get back the entire database to a prior time.
Like, in mount stage run FLASHBACK DATABASE TO TIMESTAMP SYSTIMESTAMP-1/24/60*30; in order to get back whole database 30 minutes ago. After peforming flashback open the database READ ONLY , export the data , shutdown the database, and start the database with RESETLOGS option, import the data. The flashback database feature is discussed on,
Performing Flashback Database
8)If you are unable to perform any one of the above then you can use RMAN DBPITR to a point in time before the drop. This is an extreme measure for one table as the entire database goes back in time. DBPITR recovery is discussed on How to perform DBPITR
Backup Database control file -User Managed
Backup of controlfile is necessary if you make and modification to your database structure. For example if you add a a new tablespace or add a new datafile to an existing tablespace then backup the control file so that in the backup control file the database structure become up to date which will help you to perform database recovery in case of any problem.
If you configure the configuration parameter CONFIGURE CONTROLFILE AUTOBACKUP ON then control file is automatically backup after each database structural changes.
There are two user managed method to take control file backup.
1)Take control file Backup as a Binary File:
-----------------------------------------------
A binary backup is preferable to a trace file backup because it contains additional information such as
-the archived log history,
-offline range for read-only and offline tablespaces,
-and backup sets and copies (if you use RMAN).
But binary control file backups do not include tempfile entries.
To take control file backup as a binary file just issue,
SQL>ALTER DATABASE BACKUP CONTROLFILE TO '/oradata2/control.bak';
2)Backup control file as a Text file.
-------------------------------------------
To take backup control file as a text file simply issue,
SQL>ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/oradata2/control.bak';
If you specify neither the RESETLOGS nor NORESETLOGS option in the SQL statement, then the resulting trace file contains versions of the control file for both RESETLOGS and NORESETLOGS options.
Unlike binary backup of control file backup to trace tempfile entries are included- in the output using "ALTER TABLESPACE... ADD TEMPFILE" statements.
If you configure the configuration parameter CONFIGURE CONTROLFILE AUTOBACKUP ON then control file is automatically backup after each database structural changes.
There are two user managed method to take control file backup.
1)Take control file Backup as a Binary File:
-----------------------------------------------
A binary backup is preferable to a trace file backup because it contains additional information such as
-the archived log history,
-offline range for read-only and offline tablespaces,
-and backup sets and copies (if you use RMAN).
But binary control file backups do not include tempfile entries.
To take control file backup as a binary file just issue,
SQL>ALTER DATABASE BACKUP CONTROLFILE TO '/oradata2/control.bak';
2)Backup control file as a Text file.
-------------------------------------------
To take backup control file as a text file simply issue,
SQL>ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/oradata2/control.bak';
If you specify neither the RESETLOGS nor NORESETLOGS option in the SQL statement, then the resulting trace file contains versions of the control file for both RESETLOGS and NORESETLOGS options.
Unlike binary backup of control file backup to trace tempfile entries are included- in the output using "ALTER TABLESPACE... ADD TEMPFILE" statements.
Wednesday, May 14, 2008
How to Identify OS or Oracle 64 bit or 32 bit on Unix
It is very common question which version of oracle I need to install. 32 bit or 64 bit? As a 64-bit operating system can support either a 32-bit database or a 64-bit database. A 32-bit operating system cannot support a 64-bit database. So, version identification of the OS is necessary prior to install oracle. The following procedure will hopefully help you.
Check whether OS is 64 bit or 32 bit.
-----------------------------------------
On Solaris,
SQL> !/usr/bin/isainfo -kv
64-bit amd64 kernel modules
SQL> !/usr/bin/isainfo -v
64-bit amd64 applications
cx16 mon sse3 pause sse2 sse fxsr mmx cmov amd_sysc cx8 tsc fpu
32-bit i386 applications
cx16 mon sse3 pause sse2 sse fxsr mmx cmov sep cx8 tsc fpu
This output tells us that solaris operating systems allow co-existence of 32-bit and 64-bit files.
On AIX,
$ getconf -a | grep KERN
$ file /usr/lib/boot/unix*
On Linux,
On HP-UX,
/usr/bin/ getconf KERNEL_BITS
/usr/bin/file /stand/vmunix
On linux,
$uname -a
Linux debian 2.6.18-4-686 #1 SMP Wed May 9 23:03:12 UTC 2007 i686 GNU/Linux
If the output is x86_64 then 64-bit and i686 or similar for 32-bit.
On linux you can also see by getconf LONG_BIT.
On 64 bit,
$ getconf LONG_BIT
64
On 32 bit,
$ getconf LONG_BIT
32
On windows,
Start>All Programs>accessories> System Tools>System Information>look for under System summary.
Or start>run>dixdiag>Then check for WHQL digital signature.
Determine of whether Oracle Software is 32 bit or 64 bit.
--------------------------------------------------------------------------------
Method 1:
--------------
Go to $ORACLE_HOME/bin and see.
# cd $ORACLE_HOME/bin
# file oracle
oracle: ELF 64-bit LSB executable AMD64 Version 1, dynamically linked, not stripped
Here it comes 64 bit and hence oracle software is 64 bit. If the output of the "file oracle" command does not say 64-bit explicitly then you are running 32-bit Oracle.
If you had 32 bit oracle software installed then output will be like,
oracle@sol:/db/oracle/bin$ file oracle
oracle: setuid setgid ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), dynamically linked (uses shared libs), not stripped
Method 2:
----------------------
Log on to SQL*plus and see the banner.
-bash-3.00$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.2.0 - Production on Thu May 15 02:50:37 2008
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Method 3:Query from v$version.
-------------------------------------
sys@ARJU> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for Solaris: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production
Method 4: Check for lib, lib32
--------------------------------------
1) $ORACLE_HOME/lib32
2) $ORACLE_HOME/lib
If the two directories $ORACLE_HOME/lib32 and $ORACLE_HOME/lib are existing then it is 64 bit.
If there is only an ORACLE_HOME/lib directory then it is 32 bit client.
Check whether OS is 64 bit or 32 bit.
-----------------------------------------
On Solaris,
SQL> !/usr/bin/isainfo -kv
64-bit amd64 kernel modules
SQL> !/usr/bin/isainfo -v
64-bit amd64 applications
cx16 mon sse3 pause sse2 sse fxsr mmx cmov amd_sysc cx8 tsc fpu
32-bit i386 applications
cx16 mon sse3 pause sse2 sse fxsr mmx cmov sep cx8 tsc fpu
This output tells us that solaris operating systems allow co-existence of 32-bit and 64-bit files.
On AIX,
$ getconf -a | grep KERN
$ file /usr/lib/boot/unix*
On Linux,
On HP-UX,
/usr/bin/ getconf KERNEL_BITS
/usr/bin/file /stand/vmunix
On linux,
$uname -a
Linux debian 2.6.18-4-686 #1 SMP Wed May 9 23:03:12 UTC 2007 i686 GNU/Linux
If the output is x86_64 then 64-bit and i686 or similar for 32-bit.
On linux you can also see by getconf LONG_BIT.
On 64 bit,
$ getconf LONG_BIT
64
On 32 bit,
$ getconf LONG_BIT
32
On windows,
Start>All Programs>accessories> System Tools>System Information>look for under System summary.
Or start>run>dixdiag>Then check for WHQL digital signature.
Determine of whether Oracle Software is 32 bit or 64 bit.
--------------------------------------------------------------------------------
Method 1:
--------------
Go to $ORACLE_HOME/bin and see.
# cd $ORACLE_HOME/bin
# file oracle
oracle: ELF 64-bit LSB executable AMD64 Version 1, dynamically linked, not stripped
Here it comes 64 bit and hence oracle software is 64 bit. If the output of the "file oracle" command does not say 64-bit explicitly then you are running 32-bit Oracle.
If you had 32 bit oracle software installed then output will be like,
oracle@sol:/db/oracle/bin$ file oracle
oracle: setuid setgid ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), dynamically linked (uses shared libs), not stripped
Method 2:
----------------------
Log on to SQL*plus and see the banner.
-bash-3.00$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.2.0 - Production on Thu May 15 02:50:37 2008
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Method 3:Query from v$version.
-------------------------------------
sys@ARJU> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for Solaris: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production
Method 4: Check for lib, lib32
--------------------------------------
1) $ORACLE_HOME/lib32
2) $ORACLE_HOME/lib
If the two directories $ORACLE_HOME/lib32 and $ORACLE_HOME/lib are existing then it is 64 bit.
If there is only an ORACLE_HOME/lib directory then it is 32 bit client.
Startup fails with ORA-27102: out of memory Solaris-AMD64 Error
Scenario of The problem:
------------------------------
In my computer I have two database. One database is running smoothly but another database is not started whenever I invoke startup. It fails with error,
SQL> startup
ORA-27102: out of memory
Solaris-AMD64 Error: 22: Invalid argument
Reason of The problem:
------------------------
The database which could not start is because of the low memory on the system or in the sga_max_size there is high value set. So the system could not allocate so large memory as it does not have free so much. There may be other reasons like OS limitation in order of usage the memory. As in this case one database is ok (Both database are running on same user)and another database is failed so I suspect either low memory on the system or in the sga_max_size parameter inside spfile there is high value set.
Solution of The problem:
------------------------------
1)Set a lower amount of memory in the first database.
You can do it by, On dbase1,
SQL> alter system set sga_max_size=1600M scope=spfile;
System altered.
SQL> alter system set sga_target=1600M;
System altered.
SQL>shutdown
Now set ORACLE_SID and start the instance.
bash-3.00$ export ORACLE_SID=dupbase
bash-3.00$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 15 01:34:15 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed Size 2019320 bytes
Variable Size 113246216 bytes
Database Buffers 50331648 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.
Or,
2)Lower the setting of SGA_MAX_SIZE, SGA_TARGET on the 2nd database. To do this create pfile from spfile.
SQL>create pfile from spfile;
And then edit the pfile parameter of SGA_MAX_SIZE and SGA_TARGET.
And start the database with the pfile.
SQL>STARTUP PFILE='pfile_name';
Later , Create spfile from pfile,
SQL>CREATE SPFILE from PFILE;
Related Documents
http://arjudba.blogspot.com/2008/12/expdp-fails-with-ora-39125-ora-04031.html
http://arjudba.blogspot.com/2009/05/ora-27100-shared-memory-realm-already.html
http://arjudba.blogspot.com/2008/05/startup-fails-with-oracle-error-ora.html
http://arjudba.blogspot.com/2008/09/database-startup-fails-with-ora-27302.html
http://arjudba.blogspot.com/2008/07/database-startup-fails-with-error-ora.html
http://arjudba.blogspot.com/2008/08/startup-fails-with-ora-01261-parameter.html
------------------------------
In my computer I have two database. One database is running smoothly but another database is not started whenever I invoke startup. It fails with error,
SQL> startup
ORA-27102: out of memory
Solaris-AMD64 Error: 22: Invalid argument
Reason of The problem:
------------------------
The database which could not start is because of the low memory on the system or in the sga_max_size there is high value set. So the system could not allocate so large memory as it does not have free so much. There may be other reasons like OS limitation in order of usage the memory. As in this case one database is ok (Both database are running on same user)and another database is failed so I suspect either low memory on the system or in the sga_max_size parameter inside spfile there is high value set.
Solution of The problem:
------------------------------
1)Set a lower amount of memory in the first database.
You can do it by, On dbase1,
SQL> alter system set sga_max_size=1600M scope=spfile;
System altered.
SQL> alter system set sga_target=1600M;
System altered.
SQL>shutdown
Now set ORACLE_SID and start the instance.
bash-3.00$ export ORACLE_SID=dupbase
bash-3.00$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 15 01:34:15 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 171966464 bytes
Fixed Size 2019320 bytes
Variable Size 113246216 bytes
Database Buffers 50331648 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.
Or,
2)Lower the setting of SGA_MAX_SIZE, SGA_TARGET on the 2nd database. To do this create pfile from spfile.
SQL>create pfile from spfile;
And then edit the pfile parameter of SGA_MAX_SIZE and SGA_TARGET.
And start the database with the pfile.
SQL>STARTUP PFILE='pfile_name';
Later , Create spfile from pfile,
SQL>CREATE SPFILE from PFILE;
Related Documents
http://arjudba.blogspot.com/2008/12/expdp-fails-with-ora-39125-ora-04031.html
http://arjudba.blogspot.com/2009/05/ora-27100-shared-memory-realm-already.html
http://arjudba.blogspot.com/2008/05/startup-fails-with-oracle-error-ora.html
http://arjudba.blogspot.com/2008/09/database-startup-fails-with-ora-27302.html
http://arjudba.blogspot.com/2008/07/database-startup-fails-with-error-ora.html
http://arjudba.blogspot.com/2008/08/startup-fails-with-ora-01261-parameter.html
ORA-27123 unable to attach to shared memory segment
Error Description:
------------------------
Whenever you try to start your database instance it failed with error as follows.
SQL> startup
ORA-27123: unable to attach to shared memory segment
Solaris-AMD64 Error: 13: Permission denied
Cause of the Problem:
----------------------
As the error suggested permission denied so there is problem in proper permission settings. In the $ORACLE_HOME and in $ORACLE_HOME/bin proper permission is not set and hence error comes.
Solution of The Problem:
-----------------------------
You need to change the "umask" to the required 022, set the proper permission on $ORACLE_HOME, $ORACLE_HOME/bin directories. In my computer, I ran the following.
1)Log on as root and change permission.
bash-3.00$ su
Password:
# umask 022
# cd $ORACLE_HOME
# chmod 755 *
# cd $ORACLE_BASE/admin/$ORACLE_SID
# chmod 755 *
# cd $ORACLE_HOME/bin
# chmod 6751 oracle
# exit
2)Log on normal user and log on to database.
bash-3.00$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 14 23:40:12 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
------------------------
Whenever you try to start your database instance it failed with error as follows.
SQL> startup
ORA-27123: unable to attach to shared memory segment
Solaris-AMD64 Error: 13: Permission denied
Cause of the Problem:
----------------------
As the error suggested permission denied so there is problem in proper permission settings. In the $ORACLE_HOME and in $ORACLE_HOME/bin proper permission is not set and hence error comes.
Solution of The Problem:
-----------------------------
You need to change the "umask" to the required 022, set the proper permission on $ORACLE_HOME, $ORACLE_HOME/bin directories. In my computer, I ran the following.
1)Log on as root and change permission.
bash-3.00$ su
Password:
# umask 022
# cd $ORACLE_HOME
# chmod 755 *
# cd $ORACLE_BASE/admin/$ORACLE_SID
# chmod 755 *
# cd $ORACLE_HOME/bin
# chmod 6751 oracle
# exit
2)Log on normal user and log on to database.
bash-3.00$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 14 23:40:12 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
How to get SCN from database. Timestamp against SCN
In many cases we need to know SCN of database. Also we may need to know the previous SCN of the database suppose before 10 minutes ago. I will try to write we can can get these two and how is this correlated.
A)Determine current SCN of The database.
-----------------------------------------------------------------------
You can query from V$DATABASE or using GET_SYSTEM_CHANGE_NUMBER procedure of DBMS_FLASHBACK package.
1)SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() FROM DUAL;
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
-----------------------------------------
1053658
2)SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
1053668
B)Determine current timestamp value.
-------------------------------------------------------------------
SQL> SELECT SYSTIMESTAMP FROM DUAL;
SYSTIMESTAMP
---------------------------------------------------------------------------
14-MAY-08 11.00.08.374107 PM -04:00
C)Convert SCN to Timestamp.
-----------------------------------------------------
SQL> SELECT SCN_TO_TIMESTAMP(1053639) FROM DUAL;
SCN_TO_TIMESTAMP(1053639)
---------------------------------------------------------------------------
14-MAY-08 10.52.15.000000000 PM
D)Convert Timestamp to SCN
-------------------------------------------------------------
SQL> SELECT TIMESTAMP_TO_SCN('14-MAY-08 11.00.08.374107 PM') FROM DUAL;
TIMESTAMP_TO_SCN('14-MAY-0811.00.08.374107PM')
----------------------------------------------
1054516
A)Determine current SCN of The database.
-----------------------------------------------------------------------
You can query from V$DATABASE or using GET_SYSTEM_CHANGE_NUMBER procedure of DBMS_FLASHBACK package.
1)SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() FROM DUAL;
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()
-----------------------------------------
1053658
2)SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
1053668
B)Determine current timestamp value.
-------------------------------------------------------------------
SQL> SELECT SYSTIMESTAMP FROM DUAL;
SYSTIMESTAMP
---------------------------------------------------------------------------
14-MAY-08 11.00.08.374107 PM -04:00
C)Convert SCN to Timestamp.
-----------------------------------------------------
SQL> SELECT SCN_TO_TIMESTAMP(1053639) FROM DUAL;
SCN_TO_TIMESTAMP(1053639)
---------------------------------------------------------------------------
14-MAY-08 10.52.15.000000000 PM
D)Convert Timestamp to SCN
-------------------------------------------------------------
SQL> SELECT TIMESTAMP_TO_SCN('14-MAY-08 11.00.08.374107 PM') FROM DUAL;
TIMESTAMP_TO_SCN('14-MAY-0811.00.08.374107PM')
----------------------------------------------
1054516
How to perform Database Point in time Recovery DBPITR
About Database Point in time recovery
----------------------------------------------
Database point-in-time recovery is helpful whenever we want to back the whole database to an earlier time. With RMAN you can give a specified target time and RMAN restores the database from backups prior to that time, and then applies archived redo log or incremental backups to perform media recovery to recreate all changes between the time of the datafile backups and the target time.
Disadvantages of Database Point in time Recovery
------------------------------------------------------
1)Unlike TSPITR you can't get back a set of objects to their past state instead you have to back to an earlier with of the entire database.
2)The entire database will be unavailable during the operation.
3)It is time-consuming, because all datafiles must be restored, and redo logs and incremental backups must be restored from backup and used to recover the datafiles.
Requirements of Database Point in time Recovery
--------------------------------------------------------------
1)Your database must be in archivelog mode.
2)You must have backups of all datafiles from before the target SCN for DBPITR and archived redo logs or incremental backups for the period between the SCN of the backups and the target SCN.
Database Point-in-Time Recovery Within the Current Incarnation
----------------------------------------------------------------------
If you want to perform database point in time recovery within current incarnation then you don't have to perform extra work as RMAN by default search for backups within current incarnation. Only you need SET UNTIL clause and then RESTORE and RECOVER. However you can get back your database to an ancestor incarnation .In that case before performing operation set incarnation. Like RESET DATABASE INCARNATION TO 1. In order to do so as well as to know about incarnation please have a look About Database Incarnations.
In the following steps I demonstrate an example of how we can perform DBPITR.
1)Create a Table. Just an an extra work. I just created it and want to perform DBPITR before the time of table creation in order to show that this table would not found after DBPITR.
SQL> CONN ARJU/A
Connected.
SQL> CREATE TABLE BEFORE_PITR TABLESPACE USERS AS SELECT LEVEL A FROM DUAL CONNECT BY LEVEL <100;
Table created.
2)Shutdown the database.
SQL> CONN / AS SYSDBA
Connected.
SQL> SHUTDOWN ABORT
ORACLE instance shut down.
3)Connect to rman and Perform DBPITR. Here I wanted to get back of database to 30 minutes ago from current date. So I used SYSDATE-1/24/60*30.
SQL> !rman TARGET /
Recovery Manager: Release 10.2.0.1.0 - Production on Wed May 14 22:31:25 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> RUN{
2> RESTORE DATABASE UNTIL TIME 'SYSDATE-1/24/60*30';
3> RECOVER DATABASE UNTIL TIME 'SYSDATE-1/24/60*30';
4> }
Starting restore at 14-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
.
.
media recovery complete, elapsed time: 00:00:27
Finished recover at 14-MAY-08
4)Open the database with RESETLOGS option.
RMAN> SQL'ALTER DATABASE OPEN RESETLOGS';
using target database control file instead of recovery catalog
sql statement: ALTER DATABASE OPEN RESETLOGS
5)Check the objects under Arju Schema.
SQL> conn arju/a
Connected.
SQL> select table_name from tabs;
TABLE_NAME
------------------------------
TesT
MY_TABLE
And see that BEFORE_PITR is lost.
In stead of giving 'SYSDATE-1/24/60*30' you can also use time expressions,SCN restore points,SCN or log sequence numbers with SET UNTIL clause.
Like,
RMAN>RUN{
#SET UNTIL TIME 'Nov 12 2007 06:00:00'; --Set NLS_DATE_FORMAT setting.
#SET UNTIL SEQUENCE 9923;
#SET UNTIL RESTORE POINT before_update; --The restore point you created early.
SET UNTIL SCN 123456;
RESTORE DATABASE;
RECOVER DATABASE;
}
----------------------------------------------
Database point-in-time recovery is helpful whenever we want to back the whole database to an earlier time. With RMAN you can give a specified target time and RMAN restores the database from backups prior to that time, and then applies archived redo log or incremental backups to perform media recovery to recreate all changes between the time of the datafile backups and the target time.
Disadvantages of Database Point in time Recovery
------------------------------------------------------
1)Unlike TSPITR you can't get back a set of objects to their past state instead you have to back to an earlier with of the entire database.
2)The entire database will be unavailable during the operation.
3)It is time-consuming, because all datafiles must be restored, and redo logs and incremental backups must be restored from backup and used to recover the datafiles.
Requirements of Database Point in time Recovery
--------------------------------------------------------------
1)Your database must be in archivelog mode.
2)You must have backups of all datafiles from before the target SCN for DBPITR and archived redo logs or incremental backups for the period between the SCN of the backups and the target SCN.
Database Point-in-Time Recovery Within the Current Incarnation
----------------------------------------------------------------------
If you want to perform database point in time recovery within current incarnation then you don't have to perform extra work as RMAN by default search for backups within current incarnation. Only you need SET UNTIL clause and then RESTORE and RECOVER. However you can get back your database to an ancestor incarnation .In that case before performing operation set incarnation. Like RESET DATABASE INCARNATION TO 1. In order to do so as well as to know about incarnation please have a look About Database Incarnations.
In the following steps I demonstrate an example of how we can perform DBPITR.
1)Create a Table. Just an an extra work. I just created it and want to perform DBPITR before the time of table creation in order to show that this table would not found after DBPITR.
SQL> CONN ARJU/A
Connected.
SQL> CREATE TABLE BEFORE_PITR TABLESPACE USERS AS SELECT LEVEL A FROM DUAL CONNECT BY LEVEL <100;
Table created.
2)Shutdown the database.
SQL> CONN / AS SYSDBA
Connected.
SQL> SHUTDOWN ABORT
ORACLE instance shut down.
3)Connect to rman and Perform DBPITR. Here I wanted to get back of database to 30 minutes ago from current date. So I used SYSDATE-1/24/60*30.
SQL> !rman TARGET /
Recovery Manager: Release 10.2.0.1.0 - Production on Wed May 14 22:31:25 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> RUN{
2> RESTORE DATABASE UNTIL TIME 'SYSDATE-1/24/60*30';
3> RECOVER DATABASE UNTIL TIME 'SYSDATE-1/24/60*30';
4> }
Starting restore at 14-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
.
.
media recovery complete, elapsed time: 00:00:27
Finished recover at 14-MAY-08
4)Open the database with RESETLOGS option.
RMAN> SQL'ALTER DATABASE OPEN RESETLOGS';
using target database control file instead of recovery catalog
sql statement: ALTER DATABASE OPEN RESETLOGS
5)Check the objects under Arju Schema.
SQL> conn arju/a
Connected.
SQL> select table_name from tabs;
TABLE_NAME
------------------------------
TesT
MY_TABLE
And see that BEFORE_PITR is lost.
In stead of giving 'SYSDATE-1/24/60*30' you can also use time expressions,SCN restore points,SCN or log sequence numbers with SET UNTIL clause.
Like,
RMAN>RUN{
#SET UNTIL TIME 'Nov 12 2007 06:00:00'; --Set NLS_DATE_FORMAT setting.
#SET UNTIL SEQUENCE 9923;
#SET UNTIL RESTORE POINT before_update; --The restore point you created early.
SET UNTIL SCN 123456;
RESTORE DATABASE;
RECOVER DATABASE;
}
ORA-16038,ORA-00354,ORA-00312 corrupt redo log block header
Error Description:
------------------------
Normal users could not connect to database. It messaged ORA-00257 :Connect Internal Only until freed. Whenever you try to archive the redo log it returns the message.
ORA-16038: log %s sequence# %s cannot be archived
ORA-00354: corrupt redo log block header
ORA-00312: "online log %s thread %s: '%s'"
Explanation of the Problem:
-------------------------------
Whenever normal user tried to connect the database the error returns as it is descriped in
ORA-00257. But you have noticed that there is enough space in V$RECOVERY_FILE_DEST. Whenever you look at alert log you will see the ORA-16038,ORA-00354,ORA-00312 error serial. The error produced as because it failed to archive online redolog due to a corruption in the online redo file.
Solution of The problem:
--------------------------------
Step1)While making your database running clear the unarchived redo log.
SQL>alter database clear unarchived logfile 'logilename';
This makes the corruption disappear which causes the contents of the cleared online redo file.
Step2)Make a complete backup of the database.
------------------------
Normal users could not connect to database. It messaged ORA-00257 :Connect Internal Only until freed. Whenever you try to archive the redo log it returns the message.
ORA-16038: log %s sequence# %s cannot be archived
ORA-00354: corrupt redo log block header
ORA-00312: "online log %s thread %s: '%s'"
Explanation of the Problem:
-------------------------------
Whenever normal user tried to connect the database the error returns as it is descriped in
ORA-00257. But you have noticed that there is enough space in V$RECOVERY_FILE_DEST. Whenever you look at alert log you will see the ORA-16038,ORA-00354,ORA-00312 error serial. The error produced as because it failed to archive online redolog due to a corruption in the online redo file.
Solution of The problem:
--------------------------------
Step1)While making your database running clear the unarchived redo log.
SQL>alter database clear unarchived logfile 'logilename';
This makes the corruption disappear which causes the contents of the cleared online redo file.
Step2)Make a complete backup of the database.
Database Startup Fails With Errors ORA-01078 And ORA-27046 Or ORA-01078
Error Description:
-----------------------
Database startup fails with error
SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '......'
or,
SQL> startup
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '.......'
ORA-27046: file size is not a multiple of logical block size
OSD-04012: file size mismatch (OS 2558)
Cause of The problem:
------------------------------
The error can be happened in many scenarios.
1)The ORACLE_SID environmental variable is set improperly.
2)The error occurred as database could not find the spfile and pfile on the default location or specified location.(if startup pfile= is specified)
3)The spfile exists in default location but it is corrupted and hence reported ORA-01078.
4)If spfile exists in non-default location and we started by STARTUP pfile= where inside pfile it holds the location of spfile=location then error reported ORA-1078 along with ORA-27046. This scenario is explained in How to start your database with non-default spfile.
Solution of The problem;
------------------------------
At first check whether your environment variable ORACLE_SID is set properly or not. On unix it is case sensitive. So dbase and Dbase is not same.
Is there is no way to repair or modify an spfile so try to solve the problem in following order.
1)If you have good backup of spfile then restore it. From RMAN you can easily do it if you have autobackup of controlfile. It is described in How to restore spfile by RMAN
2)You can get your pfile at the location $ORACLE_HOME/admin/$ORACLE_SID/pfile/. A typical file name is init.ora.418200821147. From that location first copy to another location like in /oradata2/pfile and then edit the pfile as necessary and try to make a spfile from that.
$SQL / as sysdba
$CREATE SPFILE FROM PFILE='/oradata2/pfile';
3)If the spfile backup does not exist then look at the database alert log file which is located in $ORACLE_HOME/admin/$ORACLE_SID/bdump. In the alert log file the list of non-default parameters listed. So create a pfile from spfile and later create spfile.
4)If you have corrupted spfile then from that, using binary utilities like strings available on unix open that and create pfile. And then create spfile from that. Like,
i)Follow either a or b part.
a)$strings /oracle/app/oracle/product/10.2.0/db_1/dbs/spfiledbase.ora >/export/home/pfile.ora
Now edit the /export/home/pfile.ora if any wrong character and then create spfile from that.
SQL>!vi /export/home/pfile.ora
SQL>create spfile from pfile='/export/home/pfile.ora';
SQL>startup
b)$strings /oracle/app/oracle/product/10.2.0/db_1/dbs/spfiledbase.ora
copy the usable contents inside it Open another file and paste contents into it.
SQL>!vi /export/home/oracle/test.txt
Create spfile from pfile.
SQL> create spfile from pfile='/export/home/oracle/test.txt';
File created.
Then start the database.
Related Documents:
http://arjudba.blogspot.com/2008/04/how-to-know-my-database-start-with.html
http://arjudba.blogspot.com/2008/04/pfile-and-spfile-in-oracle.html
http://arjudba.blogspot.com/2008/04/how-to-start-your-database-with-no.html
-----------------------
Database startup fails with error
SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '......'
or,
SQL> startup
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file '.......'
ORA-27046: file size is not a multiple of logical block size
OSD-04012: file size mismatch (OS 2558)
Cause of The problem:
------------------------------
The error can be happened in many scenarios.
1)The ORACLE_SID environmental variable is set improperly.
2)The error occurred as database could not find the spfile and pfile on the default location or specified location.(if startup pfile= is specified)
3)The spfile exists in default location but it is corrupted and hence reported ORA-01078.
4)If spfile exists in non-default location and we started by STARTUP pfile= where inside pfile it holds the location of spfile=location then error reported ORA-1078 along with ORA-27046. This scenario is explained in How to start your database with non-default spfile.
Solution of The problem;
------------------------------
At first check whether your environment variable ORACLE_SID is set properly or not. On unix it is case sensitive. So dbase and Dbase is not same.
Is there is no way to repair or modify an spfile so try to solve the problem in following order.
1)If you have good backup of spfile then restore it. From RMAN you can easily do it if you have autobackup of controlfile. It is described in How to restore spfile by RMAN
2)You can get your pfile at the location $ORACLE_HOME/admin/$ORACLE_SID/pfile/. A typical file name is init.ora.418200821147. From that location first copy to another location like in /oradata2/pfile and then edit the pfile as necessary and try to make a spfile from that.
$SQL / as sysdba
$CREATE SPFILE FROM PFILE='/oradata2/pfile';
3)If the spfile backup does not exist then look at the database alert log file which is located in $ORACLE_HOME/admin/$ORACLE_SID/bdump. In the alert log file the list of non-default parameters listed. So create a pfile from spfile and later create spfile.
4)If you have corrupted spfile then from that, using binary utilities like strings available on unix open that and create pfile. And then create spfile from that. Like,
i)Follow either a or b part.
a)$strings /oracle/app/oracle/product/10.2.0/db_1/dbs/spfiledbase.ora >/export/home/pfile.ora
Now edit the /export/home/pfile.ora if any wrong character and then create spfile from that.
SQL>!vi /export/home/pfile.ora
SQL>create spfile from pfile='/export/home/pfile.ora';
SQL>startup
b)$strings /oracle/app/oracle/product/10.2.0/db_1/dbs/spfiledbase.ora
copy the usable contents inside it Open another file and paste contents into it.
SQL>!vi /export/home/oracle/test.txt
Create spfile from pfile.
SQL> create spfile from pfile='/export/home/oracle/test.txt';
File created.
Then start the database.
Related Documents:
http://arjudba.blogspot.com/2008/04/how-to-know-my-database-start-with.html
http://arjudba.blogspot.com/2008/04/pfile-and-spfile-in-oracle.html
http://arjudba.blogspot.com/2008/04/how-to-start-your-database-with-no.html
File size is not a multiple of logical block size ORA-00202,ORA-27046
Error Description:
-------------------------
Whenever you tried to start the database the database failed to mount. It retuen the following error message.
ORA-00202: controlfile: '/oradata2/data1/dbase/control01.ctl'
ORA-27046: file size is not a multiple of logical block size
Cause of the Problem:
------------------------
All of the control files were corrupted.
Solution of the Problem:
----------------------------
A)If you have backup of control file then restore the control file. How to restore control file from backup is discussed on Restore Control File from backup
B)If you don't have control file then re-create it.
How to create control file is discussed on, How to create control file
-------------------------
Whenever you tried to start the database the database failed to mount. It retuen the following error message.
ORA-00202: controlfile: '/oradata2/data1/dbase/control01.ctl'
ORA-27046: file size is not a multiple of logical block size
Cause of the Problem:
------------------------
All of the control files were corrupted.
Solution of the Problem:
----------------------------
A)If you have backup of control file then restore the control file. How to restore control file from backup is discussed on Restore Control File from backup
B)If you don't have control file then re-create it.
How to create control file is discussed on, How to create control file
ORA-00313 ,ORA-00312 open failed for members of log group
Error Description:
---------------------------
Whenever you start your database you returned by the following message,
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/oradata2/data1/dbase/redo02.log'
Cause of The problem:
-----------------------------
Your database was in archive log file, you shutdown your database and whenever you start it up either your redo log file is deleted or it is corrupted (if you overwrite or truncate the file).In this case you had 1 redo log member on each group.
Solution of The problem:
--------------------------------
It is not possible to recover missing redo log file. In order to solve the problem do the following.
A)Mount the database.
SQL>STARTUP MOUNT
Database mounted.
B)Check the status of the logile to see whether it is current.
Here it is,
SQL> SELECT STATUS FROM V$LOG WHERE GROUP#=2;
STATUS
----------------
CURRENT
i)If the status did not CURRENT then simply drop the log file by,
SQL>ALTER DATABASE DROP LOGFILE GROUP 2;
If there are only 2 log groups then it will be necessary to add another group before dropping this one.
So, before dropping do,
SQL>ALTER DATABASE ADD LOGFILE GROUP 4 '/oradata2/redo3.log' SIZE 10M;
ii)If/As the status is CURRENT then simply perform fake recovery and then open resetlogs.
SQL>RECOVER DATABASE UNTIL CANCEL;
and print CANCEL.
SQL>ALTER DATABASE OPEN RESETLOGS;
Related Documents
---------------------------
Whenever you start your database you returned by the following message,
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/oradata2/data1/dbase/redo02.log'
Cause of The problem:
-----------------------------
Your database was in archive log file, you shutdown your database and whenever you start it up either your redo log file is deleted or it is corrupted (if you overwrite or truncate the file).In this case you had 1 redo log member on each group.
Solution of The problem:
--------------------------------
It is not possible to recover missing redo log file. In order to solve the problem do the following.
A)Mount the database.
SQL>STARTUP MOUNT
Database mounted.
B)Check the status of the logile to see whether it is current.
Here it is,
SQL> SELECT STATUS FROM V$LOG WHERE GROUP#=2;
STATUS
----------------
CURRENT
i)If the status did not CURRENT then simply drop the log file by,
SQL>ALTER DATABASE DROP LOGFILE GROUP 2;
If there are only 2 log groups then it will be necessary to add another group before dropping this one.
So, before dropping do,
SQL>ALTER DATABASE ADD LOGFILE GROUP 4 '/oradata2/redo3.log' SIZE 10M;
ii)If/As the status is CURRENT then simply perform fake recovery and then open resetlogs.
SQL>RECOVER DATABASE UNTIL CANCEL;
and print CANCEL.
SQL>ALTER DATABASE OPEN RESETLOGS;
Related Documents
Startup fails with oracle error ORA-00119, ORA-00132
User Managed, Consistent Backup in Noarchivelog Mode
-You must have to take consistent backup if your database is in noarchivelog mode.
-Remember that database backup taken while the database is open or after an instance failure or SHUTDOWN ABORT is inconsistent.
-So in noarchivelog mode you can't take your backup while the database is open.
-In case of noarchivelog mode you can take your backup after the database has been shut down with the NORMAL, IMMEDIATE, or TRANSACTIONAL options. Backup taken in this way is called consistent backup.
-Consistent backup can be taken whether you are in archive or noarchivelog mode.
-Consistent backup can also called cold backup.
In the following sections I will see an example of how we can taken consistent used Managed Backup.
1)Determine the files that you need to take backup.
Whenever you decide to take your database user managed consistent backup then take backup of data files, control files spfiles and network files.
In order to decide which files you need to backup issue the following query while the database is in open state.
SQL>SELECT NAME "File Need Backup" FROM V$DATAFILE
UNION ALL
SELECT NAME FROM V$CONTROLFILE
UNION ALL
SELECT VALUE FROM V$PARAMETER WHERE NAME='spfile';
File Need Backup
---------------------------------------------------------------------------------
/oradata2/data1/dbase/system01.dbf
/oradata2/data1/dbase/undotbs01.dbf
/oradata2/data1/dbase/sysaux01.dbf
/oradata2/data1/dbase/users01.dbf
/oradata2/data.dbf
/oradata2/data1/data02.dbf
/oradata2/6.dbf
/oradata2/DBASE/datafile/o1_mf_permanen_42l31vg0_.dbf
/oradata2/data_test.dbf
/oracle/app/oracle/product/10.2.0/db_1/dbs/cntrldupbase.dbf
/oracle/app/oracle/product/10.2.0/db_1/dbs/spfiledupbase.ora
11 rows selected.
2)Make clean shutdown of the database.
That is anyone of the following,
SQL>SHUTDOWN
SQL>SHUTDOWN IMMEDIATE
SQL>SHUTDOWN TRANSACTIONAL
3)Use an operating system utility to make backups of all datafiles as well as all control files as shown in section 1. Like,
$scp /oradata2/data1/dbase/system01.dbf /backup
.
.
scp /oracle/app/oracle/product/10.2.0/db_1/dbs/spfiledupbase.ora /backup
Also backup network files. Do a recursive search for *.ora starting in your Oracle home directory and under it.
4)Startup the database.
$sqlplus / as sysdba
SQL>STARTUP
-Remember that database backup taken while the database is open or after an instance failure or SHUTDOWN ABORT is inconsistent.
-So in noarchivelog mode you can't take your backup while the database is open.
-In case of noarchivelog mode you can take your backup after the database has been shut down with the NORMAL, IMMEDIATE, or TRANSACTIONAL options. Backup taken in this way is called consistent backup.
-Consistent backup can be taken whether you are in archive or noarchivelog mode.
-Consistent backup can also called cold backup.
In the following sections I will see an example of how we can taken consistent used Managed Backup.
1)Determine the files that you need to take backup.
Whenever you decide to take your database user managed consistent backup then take backup of data files, control files spfiles and network files.
In order to decide which files you need to backup issue the following query while the database is in open state.
SQL>SELECT NAME "File Need Backup" FROM V$DATAFILE
UNION ALL
SELECT NAME FROM V$CONTROLFILE
UNION ALL
SELECT VALUE FROM V$PARAMETER WHERE NAME='spfile';
File Need Backup
---------------------------------------------------------------------------------
/oradata2/data1/dbase/system01.dbf
/oradata2/data1/dbase/undotbs01.dbf
/oradata2/data1/dbase/sysaux01.dbf
/oradata2/data1/dbase/users01.dbf
/oradata2/data.dbf
/oradata2/data1/data02.dbf
/oradata2/6.dbf
/oradata2/DBASE/datafile/o1_mf_permanen_42l31vg0_.dbf
/oradata2/data_test.dbf
/oracle/app/oracle/product/10.2.0/db_1/dbs/cntrldupbase.dbf
/oracle/app/oracle/product/10.2.0/db_1/dbs/spfiledupbase.ora
11 rows selected.
2)Make clean shutdown of the database.
That is anyone of the following,
SQL>SHUTDOWN
SQL>SHUTDOWN IMMEDIATE
SQL>SHUTDOWN TRANSACTIONAL
3)Use an operating system utility to make backups of all datafiles as well as all control files as shown in section 1. Like,
$scp /oradata2/data1/dbase/system01.dbf /backup
.
.
scp /oracle/app/oracle/product/10.2.0/db_1/dbs/spfiledupbase.ora /backup
Also backup network files. Do a recursive search for *.ora starting in your Oracle home directory and under it.
4)Startup the database.
$sqlplus / as sysdba
SQL>STARTUP
Tuesday, May 13, 2008
What is enq: TX - row lock contention
Enqueues are locks that coordinate access to database resources. enq: wait event indicates that the session is waiting for a lock that is held by another session. The name of the enqueue is as part of the form enq: enqueue_type - related_details.
The V$EVENT_NAME view provides a complete list of all the enq: wait events.
TX enqueue are acquired exclusive when a transaction initiates its first change and held until the transaction does a COMMIT or ROLLBACK.
Several Situation of TX enqueue:
--------------------------------------
1) Waits for TX in mode 6 occurs when a session is waiting for a row level lock that is already held by another session. This occurs when one user is updating or deleting a row, which another session wishes to update or delete. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.
The solution is to have the first session already holding the lock perform a COMMIT or ROLLBACK.
2) Waits for TX in mode 4 can occur if a session is waiting due to potential duplicates in UNIQUE index. If two sessions try to insert the same key value the second session has to wait to see if an ORA-0001 should be raised or not. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.
The solution is to have the first session already holding the lock perform a COMMIT or ROLLBACK.
3)Waits for TX in mode 4 is also possible if the session is waiting due to shared bitmap index fragment. Bitmap indexes index key values and a range of ROWIDs. Each ‘entry’ in a bitmap index can cover many rows in the actual table. If two sessions want to update rows covered by the same bitmap index fragment, then the second session waits for the first transaction to either COMMIT or ROLLBACK by waiting for the TX lock in mode 4. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.
Troubleshooting:
for which SQL currently is waiting to,
select sid, sql_text
from v$session s, v$sql q
where sid in (select sid
from v$session where state in ('WAITING')
and wait_class != 'Idle' and event='enq: TX - row lock contention'
and (
q.sql_id = s.sql_id or
q.sql_id = s.prev_sql_id));
The blocking session is,
SQL> select blocking_session, sid, serial#, wait_class, seconds_in_wait from v$session
where blocking_session is not NULL order by blocking_session;
Realted Documents
Classes of Oracle Wait Events
ORA-00054: resource busy and acquire with NOWAIT specified
New features in Oracle database administration in 11g
DDL with the WAIT option in 11g -DDL_LOCK_TIMEOUT
The V$EVENT_NAME view provides a complete list of all the enq: wait events.
TX enqueue are acquired exclusive when a transaction initiates its first change and held until the transaction does a COMMIT or ROLLBACK.
Several Situation of TX enqueue:
--------------------------------------
1) Waits for TX in mode 6 occurs when a session is waiting for a row level lock that is already held by another session. This occurs when one user is updating or deleting a row, which another session wishes to update or delete. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.
The solution is to have the first session already holding the lock perform a COMMIT or ROLLBACK.
2) Waits for TX in mode 4 can occur if a session is waiting due to potential duplicates in UNIQUE index. If two sessions try to insert the same key value the second session has to wait to see if an ORA-0001 should be raised or not. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.
The solution is to have the first session already holding the lock perform a COMMIT or ROLLBACK.
3)Waits for TX in mode 4 is also possible if the session is waiting due to shared bitmap index fragment. Bitmap indexes index key values and a range of ROWIDs. Each ‘entry’ in a bitmap index can cover many rows in the actual table. If two sessions want to update rows covered by the same bitmap index fragment, then the second session waits for the first transaction to either COMMIT or ROLLBACK by waiting for the TX lock in mode 4. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.
Troubleshooting:
for which SQL currently is waiting to,
select sid, sql_text
from v$session s, v$sql q
where sid in (select sid
from v$session where state in ('WAITING')
and wait_class != 'Idle' and event='enq: TX - row lock contention'
and (
q.sql_id = s.sql_id or
q.sql_id = s.prev_sql_id));
The blocking session is,
SQL> select blocking_session, sid, serial#, wait_class, seconds_in_wait from v$session
where blocking_session is not NULL order by blocking_session;
Realted Documents
Classes of Oracle Wait Events
ORA-00054: resource busy and acquire with NOWAIT specified
New features in Oracle database administration in 11g
DDL with the WAIT option in 11g -DDL_LOCK_TIMEOUT
User Managed hot backup of oracle database
Used managed backup means you take backup without any oracle feature. Suppose if you take backup by Operating system then it is called user managed backup. And the term hot backup means taking your backup whenever your database is at open state.
To take full database backup follow the following steps.
1)Before proceed remember you can take online/hot backup whenever your database is in Archivelog mode. If your database run on noarchivelog mode then you must take consistent backup that is after cleanly shutdown. In order to determine the archival mode, issue the query,
SQL> SELECT LOG_MODE FROM V$DATABASE;
LOG_MODE
------------
ARCHIVELOG
If you see ARCHIVELOG then you can proceed further. In order to take backup while you are in noarhivelog mode follow other post on my blog.
2)Determine the files that you need to take backup.
Whenever you decide to take your database backup then take backup of data files , online redo log files ,control files, spfile.
In order to decide which files you need to backup issue the following query.
SQL>SELECT NAME "File Need Backup" FROM V$DATAFILE
UNION ALL
SELECT MEMBER FROM V$LOGFILE
UNION ALL
SELECT NAME FROM V$CONTROLFILE
UNION ALL
SELECT VALUE FROM V$PARAMETER WHERE NAME='spfile';
File Need Backup
--------------------------------------------------------------------------------
/oradata2/data1/dbase/system01.dbf
/oradata2/data1/dbase/undotbs01.dbf
/oradata2/data1/dbase/sysaux01.dbf
/oradata2/data1/dbase/users01.dbf
/oradata2/data.dbf
/oradata2/data1/data02.dbf
/oradata2/6.dbf
/oradata2/DBASE/datafile/o1_mf_permanen_42l31vg0_.dbf
/oradata2/data_test.dbf
/oradata2/data1/dbase/redo03.log
/oradata2/data1/dbase/redo02.log
/oradata2/data1/dbase/redo01.log
/oracle/app/oracle/product/10.2.0/db_1/dbs/cntrldupbase.dbf
/oracle/app/oracle/product/10.2.0/db_1/dbs/spfiledupbase.ora
13 rows selected.
So after running the above query I can say I need to backup 13 files.
3)Take the tablespace in backup mode rather than offline and read-only tablespace. In case of offline and read only tablespace you do not have to place the tablespace in backup mode because the database is not permitting changes to the datafiles.
You can check the status, tablespace_name and it's associated data file name with the following query,
SELECT t.STATUS,t.TABLESPACE_NAME "Tablespace", f.FILE_NAME "Datafile"
FROM DBA_TABLESPACES t, DBA_DATA_FILES f
WHERE t.TABLESPACE_NAME = f.TABLESPACE_NAME;
ORDER BY t.NAME;
Take the tablespace in backup mode rather than offline and read-only tablespace.
You can easily make a script of taking the online tablespace in backup mode by following query.
SQL>SELECT 'ALTER TABLESPACE ' ||TABLESPACE_NAME ||' BEGIN BACKUP;' "Script" FROM DBA_TABLESPACES WHERE STATUS NOT IN ('READ ONLY','OFFLINE');
Script
-------------------------------------------------------------
ALTER TABLESPACE SYSTEM BEGIN BACKUP;
ALTER TABLESPACE UNDOTBS1 BEGIN BACKUP;
ALTER TABLESPACE SYSAUX BEGIN BACKUP;
ALTER TABLESPACE TEMP BEGIN BACKUP;
ALTER TABLESPACE USERS BEGIN BACKUP;
ALTER TABLESPACE TEMP_T BEGIN BACKUP;
6 rows selected.
Alternatively, you can issue
SQL>ALTER DATABASE BEGIN BACKUP;
4)Copy the datafile to backup location.
After making a tablespace in backup mode take backup/copy of the associated datafiles. Here you can also make a script in order to copy datafiles to another location.
For online tablespace you must at first take it backup mode. You can check whether backup mode now active or not by issuing following query,
SQL>SELECT t.name AS "TB_NAME", d.file# as "DF#", d.name AS "DF_NAME", b.status
FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b
WHERE d.TS#=t.TS#
AND b.FILE#=d.FILE#
AND b.STATUS='ACTIVE';
SQL> SELECT 'host scp '|| FILE_NAME || ' &backup_location ' "Backup Command" FROM DBA_DATA_FILES;
Enter value for backup_location: /backup
old 1: SELECT 'host scp '|| FILE_NAME || ' &backup_location ' "Backup Command" FROM DBA_DATA_FILES
new 1: SELECT 'host scp '|| FILE_NAME || ' /backup ' "Backup Command" FROM DBA_DATA_FILES
Backup Command
------------------------------------------------------------------------------------------
host scp /oradata2/data1/dbase/system01.dbf /backup
host scp /oradata2/data1/dbase/undotbs01.dbf /backup
host scp /oradata2/data1/dbase/sysaux01.dbf /backup
host scp /oradata2/data1/dbase/users01.dbf /backup
host scp /oradata2/data.dbf /backup
host scp /oradata2/data1/data02.dbf /backup
host scp /oradata2/6.dbf /backup
host scp /oradata2/DBASE/datafile/o1_mf_permanen_42l31vg0_.dbf /backup
host scp /oradata2/data_test.dbf /backup
9 rows selected.
Also you can backup network files. Do a recursive search for *.ora starting in your Oracle home directory and under it.
In order to make script for to copy data files for those tablespace which are only in backup mode then issue,
SQL>SELECT 'host scp '|| d.name ||' &backup_location' FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b
WHERE d.TS#=t.TS#
AND b.FILE#=d.FILE#
AND b.STATUS='ACTIVE';
Run the script that you genereted.
On windows or other operating system you can use graphical browser to copy or other associated copy command.
5)Whenever you copy is finished make the tablespace out of backup mode. You can issue BEGIN BACKUP .. SCP serially (Take one tablespace in begin backup mode and then copy the associated datafiles and make the tablespace out of backup mode) or you can do it parallely(Take all tablespaces in begin backup mode and then copy the associated datafiles of all tabelspaces and then make the tablespace out of backup mode).
You here also make a script like,
SQL>SELECT 'ALTER TABLESPACE ' ||t.name ||' END BACKUP;' "End Backup Script"
FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b
WHERE d.TS#=t.TS#
AND b.FILE#=d.FILE#
AND b.STATUS='ACTIVE';
End Backup Script
----------------------------------------------------------
ALTER TABLESPACE SYSTEM END BACKUP;
You if you have taken Database in backup mode then issue
SQL>ALTER DATABASE END BACKUP;
Related Documents:
-----------------------
Types and Methods of Taking database backup
To take full database backup follow the following steps.
1)Before proceed remember you can take online/hot backup whenever your database is in Archivelog mode. If your database run on noarchivelog mode then you must take consistent backup that is after cleanly shutdown. In order to determine the archival mode, issue the query,
SQL> SELECT LOG_MODE FROM V$DATABASE;
LOG_MODE
------------
ARCHIVELOG
If you see ARCHIVELOG then you can proceed further. In order to take backup while you are in noarhivelog mode follow other post on my blog.
2)Determine the files that you need to take backup.
Whenever you decide to take your database backup then take backup of data files , online redo log files ,control files, spfile.
In order to decide which files you need to backup issue the following query.
SQL>SELECT NAME "File Need Backup" FROM V$DATAFILE
UNION ALL
SELECT MEMBER FROM V$LOGFILE
UNION ALL
SELECT NAME FROM V$CONTROLFILE
UNION ALL
SELECT VALUE FROM V$PARAMETER WHERE NAME='spfile';
File Need Backup
--------------------------------------------------------------------------------
/oradata2/data1/dbase/system01.dbf
/oradata2/data1/dbase/undotbs01.dbf
/oradata2/data1/dbase/sysaux01.dbf
/oradata2/data1/dbase/users01.dbf
/oradata2/data.dbf
/oradata2/data1/data02.dbf
/oradata2/6.dbf
/oradata2/DBASE/datafile/o1_mf_permanen_42l31vg0_.dbf
/oradata2/data_test.dbf
/oradata2/data1/dbase/redo03.log
/oradata2/data1/dbase/redo02.log
/oradata2/data1/dbase/redo01.log
/oracle/app/oracle/product/10.2.0/db_1/dbs/cntrldupbase.dbf
/oracle/app/oracle/product/10.2.0/db_1/dbs/spfiledupbase.ora
13 rows selected.
So after running the above query I can say I need to backup 13 files.
3)Take the tablespace in backup mode rather than offline and read-only tablespace. In case of offline and read only tablespace you do not have to place the tablespace in backup mode because the database is not permitting changes to the datafiles.
You can check the status, tablespace_name and it's associated data file name with the following query,
SELECT t.STATUS,t.TABLESPACE_NAME "Tablespace", f.FILE_NAME "Datafile"
FROM DBA_TABLESPACES t, DBA_DATA_FILES f
WHERE t.TABLESPACE_NAME = f.TABLESPACE_NAME;
ORDER BY t.NAME;
Take the tablespace in backup mode rather than offline and read-only tablespace.
You can easily make a script of taking the online tablespace in backup mode by following query.
SQL>SELECT 'ALTER TABLESPACE ' ||TABLESPACE_NAME ||' BEGIN BACKUP;' "Script" FROM DBA_TABLESPACES WHERE STATUS NOT IN ('READ ONLY','OFFLINE');
Script
-------------------------------------------------------------
ALTER TABLESPACE SYSTEM BEGIN BACKUP;
ALTER TABLESPACE UNDOTBS1 BEGIN BACKUP;
ALTER TABLESPACE SYSAUX BEGIN BACKUP;
ALTER TABLESPACE TEMP BEGIN BACKUP;
ALTER TABLESPACE USERS BEGIN BACKUP;
ALTER TABLESPACE TEMP_T BEGIN BACKUP;
6 rows selected.
Alternatively, you can issue
SQL>ALTER DATABASE BEGIN BACKUP;
4)Copy the datafile to backup location.
After making a tablespace in backup mode take backup/copy of the associated datafiles. Here you can also make a script in order to copy datafiles to another location.
For online tablespace you must at first take it backup mode. You can check whether backup mode now active or not by issuing following query,
SQL>SELECT t.name AS "TB_NAME", d.file# as "DF#", d.name AS "DF_NAME", b.status
FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b
WHERE d.TS#=t.TS#
AND b.FILE#=d.FILE#
AND b.STATUS='ACTIVE';
SQL> SELECT 'host scp '|| FILE_NAME || ' &backup_location ' "Backup Command" FROM DBA_DATA_FILES;
Enter value for backup_location: /backup
old 1: SELECT 'host scp '|| FILE_NAME || ' &backup_location ' "Backup Command" FROM DBA_DATA_FILES
new 1: SELECT 'host scp '|| FILE_NAME || ' /backup ' "Backup Command" FROM DBA_DATA_FILES
Backup Command
------------------------------------------------------------------------------------------
host scp /oradata2/data1/dbase/system01.dbf /backup
host scp /oradata2/data1/dbase/undotbs01.dbf /backup
host scp /oradata2/data1/dbase/sysaux01.dbf /backup
host scp /oradata2/data1/dbase/users01.dbf /backup
host scp /oradata2/data.dbf /backup
host scp /oradata2/data1/data02.dbf /backup
host scp /oradata2/6.dbf /backup
host scp /oradata2/DBASE/datafile/o1_mf_permanen_42l31vg0_.dbf /backup
host scp /oradata2/data_test.dbf /backup
9 rows selected.
Also you can backup network files. Do a recursive search for *.ora starting in your Oracle home directory and under it.
In order to make script for to copy data files for those tablespace which are only in backup mode then issue,
SQL>SELECT 'host scp '|| d.name ||' &backup_location' FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b
WHERE d.TS#=t.TS#
AND b.FILE#=d.FILE#
AND b.STATUS='ACTIVE';
Run the script that you genereted.
On windows or other operating system you can use graphical browser to copy or other associated copy command.
5)Whenever you copy is finished make the tablespace out of backup mode. You can issue BEGIN BACKUP .. SCP serially (Take one tablespace in begin backup mode and then copy the associated datafiles and make the tablespace out of backup mode) or you can do it parallely(Take all tablespaces in begin backup mode and then copy the associated datafiles of all tabelspaces and then make the tablespace out of backup mode).
You here also make a script like,
SQL>SELECT 'ALTER TABLESPACE ' ||t.name ||' END BACKUP;' "End Backup Script"
FROM V$DATAFILE d, V$TABLESPACE t, V$BACKUP b
WHERE d.TS#=t.TS#
AND b.FILE#=d.FILE#
AND b.STATUS='ACTIVE';
End Backup Script
----------------------------------------------------------
ALTER TABLESPACE SYSTEM END BACKUP;
You if you have taken Database in backup mode then issue
SQL>ALTER DATABASE END BACKUP;
Related Documents:
-----------------------
Types and Methods of Taking database backup
Subscribe to:
Posts (Atom)
Tag Cloud
10.2g
10g
11g
11gR2
Abasa
About Oracle
Administration
Adsense
Alerts
Archival
ASM
ASP.Net
Audit
Audit Vault
Backup
Bangladesh
Block Corruption
Blogger
Browser
Bug
Business
Clone
Clusterware
Comments
Concepts
Connection
Controlfiles
Crime
CSS
Data Block
Data Dictionary
Data Guard
Data Mining
Data Pump
Data Type
Database Administration
Database Vault
DBConsole
Developer
Economics
EM
Excel
Exercise
Explain plan
Export
External Table
Facebook
Firefox
Firmware
Flashback
Forum
Functions
Games
Globalization Support
Grid Control
Hardware
History
HTML
IE
Import
Indexes
initializaion parameter
initialization parameter
Installation
Internals
Internet
Interview
isql*plus
Java
JavaScript
Job
Joins
Joke
Limitation
Linux
Listener
Logminer
Magento
Mail
Materialized View
Medical
Memory
Mobile
Money
Multimedia
MySQL
Net Services
Network
OCP
Operators
Oracle
Oracle Concepts
Oracle Recovery
OS
Others
OUI
Package
Packages
Parameters
Partitioning
Patchset
Performance
Perl
Pfile
Photos
PHP
PL/SQL
Profile
Pseudocolumns
Puzzle
Quiz
Quota
RAC
RAC Installation
Recovery
Recovery Problems
Redo Log
Reports
RMAN
Scripts
Security
SEO
Server Administration
SGA
Shell Script
Smarty
Social Marketing
Solaris
Spfile
SQL
SQL Tuning
SQL*Loader
Sql*Plus
Startup Problem
Streams
SwingBench
System Analysis
Tablespaces
Technology
Temp
TNS Error
Tools
Troubleshooting
Tuning
Undo
UNIX
Upgradation
Utilities
Version
Views
Vmware
Windows
Wordpress
XML