Guys,
I am trying to replicate a case where i switch off flashback logging on a tablespace and offline it at the time when i flashback the database to a time in the past. But i am still unable to get it.. Anyone have better idea where i am going wrong ?
SQL> startup mount
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 2029432 bytes
Variable Size 289409160 bytes
Database Buffers 121634816 bytes
Redo Buffers 6356992 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
SQL>
SQL> create tablespace my_test_ts
datafile '/ora02/oradata/TADDMD1/my_test_ts01.dbf' size 10M
autoextend on next 5M
maxsize 100M
extent management local
segment space management auto;
2 3 4 5 6
Tablespace created.
SQL> alter tablespace my_test_ts flashback off;
Tablespace altered.
SQL> create user str identified by str default tablespace users temporary tablespace temp;
User created.
SQL> grant connect,resource to temp;
grant connect,resource to temp
*
ERROR at line 1:
ORA-01917: user or role 'TEMP' does not exist
SQL> grant connect,resource to str;
Grant succeeded.
SQL> grant unlimited tablespace to str;
Grant succeeded.
SQL> connect str
Enter password:
Connected.
SQL> @/tmp/ddl.sql
Table created.
Table created.
Table created.
Table created.
Table created.
Table created.
Table created.
Table altered.
Table altered.
Table altered.
Table altered.
Table altered.
Table altered.
Sequence created.
Sequence created.
SQL> connect sys as sysdba
Enter password:
Connected.
SQL> create table str.my_table tablespace my_test_ts as select * from all_tables;
Table created.
SQL> create table str.my_table1 tablespace my_test_ts as select * from all_tables;
Table created.
SQL> select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'DD-MON-YY
--------------------------
24-JUL-2007 16:10:14
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 2029432 bytes
Variable Size 289409160 bytes
Database Buffers 121634816 bytes
Redo Buffers 6356992 bytes
Database mounted.
SQL> alter tablespace my_test_ts offline;
alter tablespace my_test_ts offline
*
ERROR at line 1:
ORA-01109: database not open
SQL> alter database open;
Database altered.
SQL> alter tablespace my_test_ts offline;
Tablespace altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 2029432 bytes
Variable Size 289409160 bytes
Database Buffers 121634816 bytes
Redo Buffers 6356992 bytes
Database mounted.
SQL> flashback database to timestamp to_timestamp('24-JUL-2007 16:10:14','DD-MON-YYYY HH24:MI:SS');
flashback database to timestamp to_timestamp('24-JUL-2007 16:10:14','DD-MON-YYYY HH24:MI:SS')
*
ERROR at line 1:
ORA-38795: warning: FLASHBACK succeeded but OPEN RESETLOGS would get error
below
ORA-01245: offline file 8 will be lost if RESETLOGS is done
ORA-01110: data file 8: '/ora02/oradata/TADDMD1/my_test_ts01.dbf'
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16004: backup database requires recovery
ORA-01245: offline file 8 will be lost if RESETLOGS is done
ORA-01110: data file 8: '/ora02/oradata/TADDMD1/my_test_ts01.dbf'
SQL> recovery database;
SP2-0734: unknown command beginning "recovery d..." - rest of line ignored.
SQL> recover database;
Media recovery complete.
SQL> alter database open read only;
Database altered.
SQL> select table_name,tablespace_name from dba_tables where owner='STR';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
STR_EXCEPTIONS USERS
STR_INBOUND_CDM_LOG USERS
STR_REPORTING_STATUS USERS
TRS_FILE_DETAIL USERS
TRS_FILE_RESULT USERS
TRS_REPORT_LINE USERS
STR_OUTBOUND_CDM_LOG USERS
MY_TABLE MY_TEST_TS
MY_TABLE1 MY_TEST_TS
9 rows selected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 419430400 bytes
Fixed Size 2029432 bytes
Variable Size 289409160 bytes
Database Buffers 121634816 bytes
Redo Buffers 6356992 bytes
Database mounted.
SQL> flashback database to timestamp to_timestamp('24-JUL-2007 16:06:14','DD-MON-YYYY HH24:MI:SS');
flashback database to timestamp to_timestamp('24-JUL-2007 16:06:14','DD-MON-YYYY HH24:MI:SS')
*
ERROR at line 1:
ORA-38795: warning: FLASHBACK succeeded but OPEN RESETLOGS would get error
below
ORA-01245: offline file 8 will be lost if RESETLOGS is done
ORA-01110: data file 8: '/ora02/oradata/TADDMD1/my_test_ts01.dbf'
SQL> recover database;
Media recovery complete.
SQL> alter database open read only;
Database altered.
SQL> select table_name,tablespace_name from dba_tables where owner='STR';
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
STR_EXCEPTIONS USERS
STR_INBOUND_CDM_LOG USERS
STR_REPORTING_STATUS USERS
TRS_FILE_DETAIL USERS
TRS_FILE_RESULT USERS
TRS_REPORT_LINE USERS
STR_OUTBOUND_CDM_LOG USERS
MY_TABLE MY_TEST_TS
MY_TABLE1 MY_TEST_TS
9 rows selected.
Thanks in advance