ORA-19504: failed to create file

SQL> startup
ORACLE instance started.

Total System Global Area 1620115456 bytes
Fixed Size 2144864 bytes
Variable Size 1207960992 bytes
Database Buffers 402653184 bytes
Redo Buffers 7356416 bytes
Database mounted.
ORA-16038: log 2 sequence# 302 cannot be archived
ORA-19504: failed to create file ""
ORA-00312: online log 2 thread 1:
'+DATA/testdb/onlinelog/group_2.1080.651074461'
ORA-00312: online log 2 thread 1:
'+DATA/testdb/onlinelog/group_2.1079.651074465'


=> Check Disks for Archivelog

This case used ASM (on +DATA):

$ export ORACLE_SID=+ASM
$ sqlplus / as sysdba

set lines 130
set pages 10000
select NAME, TOTAL_MB, FREE_MB from v$asm_diskgroup;

NAME TOTAL_MB FREE_MB
----------- ---------- ----------
DATA 7812 0

=> Clear Archivelog by using RMAN
(delete archivelog all, delete archivelog until time 'xxxx')

>>>

NAME TOTAL_MB FREE_MB
------------ ---------- ----------
DATA 7812 1627
>>>

After that, we can database instance.

SQL> alter database open

Database altered.

...

??? TSPITR [example]

Need to recover EXAMPLE tablespace (TSPITR)

Have TTTTT table:

SQL> create table TTTTT (id number) tablespace example;

Table created.

$ date
Thu May 1 01:31:13 ICT 2008

SQL> desc TTTTT
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER

$ date
Thu May 1 01:31:41 ICT 2008

DROP "PURGE" option:

SQL> drop table TTTTT purge;

Table dropped.

SQL> desc TTTTT
ERROR:
ORA-04043: object TTTTT does not exist

$ date
Thu May 1 01:34:06 ICT 2008

After drop table "PURGE" option:
Need recover "TTTTT" table on EXAMPLE tablespace ["Thu May 1 01:32:00 ICT 2008"]

>>>>>>>>>>>>>>>>>>>
SQL> select FILE_NAME, STATUS, ONLINE_STATUS from dba_data_files

FILE_NAME STATUS ONLINE_
------------------------------------------------------------ --------- -------
+DATA/testdb/datafile/users.1085.651074273 AVAILABLE ONLINE
+DATA/testdb/datafile/undotbs1.1086.651074273 AVAILABLE ONLINE
+DATA/testdb/datafile/sysaux.1087.651074273 AVAILABLE ONLINE
+DATA/testdb/datafile/system.270.651074273 AVAILABLE SYSTEM
+DATA/testdb/datafile/example.1075.651074487 AVAILABLE ONLINE

>>>>>>>>>>>>>>>>>>>

1. create inittspitr_testdb.ora file ($ORACLE_HOME/dbs) for auxiliary instance.

DB_NAME=testdb
DB_UNIQUE_NAME=tspitr_testdb
CONTROL_FILES='/oradata/testdb/tmp/controlfile.dbf'
DB_FILE_NAME_CONVERT=('+DATA/testdb', '/oradata/testdb/tmp')
LOG_FILE_NAME_CONVERT=('+DATA/testdb', '/oradata/testdb/tmp')
COMPATIBLE=11.1.0.0.0
remote_login_passwordfile='EXCLUSIVE'

2. Create service for target database (example: testdb).

3. use SQL*PLUS to start tspitr_testdb instance (auxiliary)

$ export ORACLE_SID=tspitr_testdb
$ sqlplus / as sysoper
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 217157632 bytes
Fixed Size 2142976 bytes
Variable Size 159386880 bytes
Database Buffers 50331648 bytes
Redo Buffers 5296128 bytes
.
.
.

4. use RMAN connect to the target and auxiliary database instances.

$export ORACLE_SID=tspitr_testdb
$rman target sys/password@testdb auxiliary / catalog rman/password@catalog
connected to target database: TESTDB (DBID=2434402578)
connected to recovery catalog database
connected to auxiliary database: TESTDB (not mounted)

5. execute TSPITR

RMAN> run{
SET NEWNAME FOR DATAFILE '+DATA/testdb/datafile/example.1075.651074487'
TO '/oradata/testdb/tmp/example01.dbf';
SET NEWNAME FOR DATAFILE '+DATA/testdb/datafile/system.270.651074273'
TO '/oradata/testdb/tmp/system01.dbf';
SET NEWNAME FOR DATAFILE '+DATA/testdb/datafile/sysaux.1087.651074273'
TO '/oradata/testdb/tmp/sysaux01.dbf';
SET NEWNAME FOR DATAFILE '+DATA/testdb/datafile/undotbs1.1086.651074273'
TO '/oradata/testdb/tmp/undo01.dbf';
ALLOCATE AUXILIARY CHANNEL t1 DEVICE TYPE DISK;
RECOVER TABLESPACE example until time "to_date('May:01:2008 01:32:00', 'Mon:DD:YYYY HH24:MI:SS')";
}

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

allocated channel: t1
channel t1: SID=97 device type=DISK

Starting recover at 01-MAY-08

contents of Memory Script:
{
# set the until clause
set until time "to_date('May:01:2008 01:32:00', 'Mon:DD:YYYY HH24:MI:SS')";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log for tspitr to a resent until time
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
# resync catalog after controlfile restore
resync catalog;
}
executing Memory Script

executing command: SET until clause

Starting restore at 01-MAY-08

channel t1: starting datafile backup set restore
channel t1: restoring control file
.
.
.
Starting recover at 01-MAY-08

starting media recovery
.
.
.
media recovery complete, elapsed time: 00:00:01
Finished recover at 01-MAY-08

released channel: t1
.
.
.

5. After auxiliary database instances "open" (resetlogs), that we check auxiliary database instances.

Connect...
SQL> desc TTTTT
Name Null? Type
----------------------------------------- -------- ----------------
ID NUMBER

We can recover "TTTTT" table:

>>>>>>>>>>>>>>>>>>>
SQL> select FILE_NAME, STATUS, ONLINE_STATUS from dba_data_files

FILE_NAME STATUS ONLINE_
------------------------------------------------------------ --------- -------
/oradata/testdb/tmp/users.1085.651074273 AVAILABLE OFFLINE
/oradata/testdb/tmp/undo01.dbf AVAILABLE ONLINE
/oradata/testdb/tmp/sysaux01.dbf AVAILABLE ONLINE
/oradata/testdb/tmp/system01.dbf AVAILABLE SYSTEM
/oradata/testdb/tmp/example01.dbf AVAILABLE ONLINE

>>>>>>>>>>>>>>>>>>>
This case we recover example tablespace, It'll recover example + system + sysaux + undo tablespaces except USERS tablespace or..... other, so we'll see online_status be offline.

RMAN Catalog

CONNECT catalog Database:

$sqlplus / as sysdba

SQL> create tablespace TBS_RMAN datafile '+DATA' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

SQL> create user rman identified by rman default tablespace TBS_RMAN ;

User created.

SQL> alter user rman quota unlimited on TBS_RMAN;

User altered.

SQL> grant connect, resource, recovery_catalog_owner to rman;

Grant succeeded.


Create catalog on TBS_RMAN tablespace:

$rman catalog rman/rman@catalog

RMAN> create catalog tablespace 'TBS_RMAN';

recovery catalog created


Register Database to Catalog:

$rman catalog rman/rman@catalog target /
RMAN> register database ;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete


...

??? TSPITR

Tablespace Point-in-Time Recovery
Rman automatic enable for "TSPITR", that make us to quickly recover one or more tablespaces in a database to an earlier time without affecting.

Situations:
- recover a logical database to point different from the rest of the physical database.
- recover data lost after DDL operations that change the structure of table. we can not use Flashback table to rewind a table to before the point of structural change such as a truncate table operation.
- recover a table after it dropped with the PURGE option.
- recover from the logical corruption of a table.


TSPITR using the RMAN RECOVER TABLESPACE command.

Example: use TSPITR with AUXILIARY database

RUN
{
SET NEWNAME FOR DATAFILE '+DATA/DB/example01.dbf'
TO '+DATA/AUX/example01.dbf';
SET NEWNAME FOR DATAFILE '+DATA/DB/system01.dbf'
TO '+DATA/AUX/system01.dbf';
SET NEWNAME FOR DATAFILE '+DATA/DB/sysaux01.dbf'
TO '+DATA/AUX/sysaux01.dbf';
SET NEWNAME FOR DATAFILE '+DATA/DB/undo01.dbf'
TO '+DATA/AUX/undo01.dbf';
ALLOCATE AUXILIARY CHANNEL t1 DEVICE TYPE DISK;
ALLOCATE AUXILIARY CHANNEL t2 DEVICE 'TYPE SBT_TAPE';
RECOVER TABLESPACE example UNTIL TIME "to_date('Apr:30:2008 14:00:00', 'Mon:DD:YYYY HH24:MI:SS')";
}