SQL> show spool
spool ON
SQL> archive log list
ORA-01034: ORACLE not available
SQL> startup mount
ORACLE instance started.
Total System Global Area 126950220 bytes
Fixed Size 453452 bytes
Variable Size 109051904 bytes
Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination C:\oracle9i\oradata\recvr_db\archive
Oldest online log sequence 25
Next log sequence to archive 26
Current log sequence 26
SQL> -- run workload generator
SQL> -- correction make whole database backup
SQL> -- make online backup of file to lose
SQL> select c.name, a.file#, a.name
2 from v$tablespace c, v$datafile a , v$recover_file b
3 where b.file#= a.file#
4 and c.ts# = a.ts#
5 /
no rows selected
SQL> ed
Wrote file afiedt.buf
1 select c.name, a.file#, a.name
2 from v$tablespace c, v$datafile a
3* where c.ts# = a.ts#
SQL> /
NAME FILE#
------------------------------ ----------
NAME
--------------------------------------------------------------------------------
SYSTEM 1
C:\ORACLE9I\ORADATA\RECVR_DB\SYSTEM01.DBF
UNDOTBS1 2
C:\ORACLE9I\ORADATA\RECVR_DB\UNDOTBS01.DBF
CWMLITE 3
C:\ORACLE9I\ORADATA\RECVR_DB\CWMLITE01.DBF
NAME FILE#
------------------------------ ----------
NAME
--------------------------------------------------------------------------------
DRSYS 4
C:\ORACLE9I\ORADATA\RECVR_DB\DRSYS01.DBF
EXAMPLE 5
C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF
INDX 6
C:\ORACLE9I\ORADATA\RECVR_DB\INDX01.DBF
NAME FILE#
------------------------------ ----------
NAME
--------------------------------------------------------------------------------
ODM 7
C:\ORACLE9I\ORADATA\RECVR_DB\ODM01.DBF
TOOLS 8
C:\ORACLE9I\ORADATA\RECVR_DB\TOOLS01.DBF
USERS 9
C:\ORACLE9I\ORADATA\RECVR_DB\USERS01.DBF
NAME FILE#
------------------------------ ----------
NAME
--------------------------------------------------------------------------------
XDB 10
C:\ORACLE9I\ORADATA\RECVR_DB\XDB01.DBF
10 rows selected.
SQL> ed
Wrote file afiedt.buf
1 select c.name, a.file#, a.name
2 from v$tablespace c, v$datafile a
3* where c.ts# = a.ts#
SQL> set pages 400
SQL> /
NAME FILE#
------------------------------ ----------
NAME
--------------------------------------------------------------------------------
SYSTEM 1
C:\ORACLE9I\ORADATA\RECVR_DB\SYSTEM01.DBF
UNDOTBS1 2
C:\ORACLE9I\ORADATA\RECVR_DB\UNDOTBS01.DBF
CWMLITE 3
C:\ORACLE9I\ORADATA\RECVR_DB\CWMLITE01.DBF
DRSYS 4
C:\ORACLE9I\ORADATA\RECVR_DB\DRSYS01.DBF
EXAMPLE 5
C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF
INDX 6
C:\ORACLE9I\ORADATA\RECVR_DB\INDX01.DBF
ODM 7
C:\ORACLE9I\ORADATA\RECVR_DB\ODM01.DBF
TOOLS 8
C:\ORACLE9I\ORADATA\RECVR_DB\TOOLS01.DBF
USERS 9
C:\ORACLE9I\ORADATA\RECVR_DB\USERS01.DBF
XDB 10
C:\ORACLE9I\ORADATA\RECVR_DB\XDB01.DBF
10 rows selected.
SQL> select * from dba_users
2 where username ='OE";
ERROR:
ORA-01756: quoted string not properly terminated
SQL> where username ='OE';
SP2-0734: unknown command beginning "where user..." - rest of line ignored.
SQL> select * from dba_users
2 where username ='OE';
select * from dba_users
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only
SQL> alter database open;
Database altered.
SQL> select * from dba_users
2 where username ='OE';
USERNAME USER_ID PASSWORD
------------------------------ ---------- ------------------------------
ACCOUNT_STATUS LOCK_DATE EXPIRY_DA
-------------------------------- --------- ---------
DEFAULT_TABLESPACE TEMPORARY_TABLESPACE CREATED
------------------------------ ------------------------------ ---------
PROFILE INITIAL_RSRC_CONSUMER_GROUP
------------------------------ ------------------------------
EXTERNAL_NAME
--------------------------------------------------------------------------------
OE 47 D1A2DFC623FDA40A
OPEN
EXAMPLE TEMP 12-MAY-02
DEFAULT DEFAULT_CONSUMER_GROUP
SQL> select c.name, a.file#, a.name
2 from v$tablespace c, v$datafile a
3 where c.ts# = a.ts#
4 /
NAME FILE#
------------------------------ ----------
NAME
--------------------------------------------------------------------------------
SYSTEM 1
C:\ORACLE9I\ORADATA\RECVR_DB\SYSTEM01.DBF
UNDOTBS1 2
C:\ORACLE9I\ORADATA\RECVR_DB\UNDOTBS01.DBF
CWMLITE 3
C:\ORACLE9I\ORADATA\RECVR_DB\CWMLITE01.DBF
DRSYS 4
C:\ORACLE9I\ORADATA\RECVR_DB\DRSYS01.DBF
EXAMPLE 5
C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF
INDX 6
C:\ORACLE9I\ORADATA\RECVR_DB\INDX01.DBF
ODM 7
C:\ORACLE9I\ORADATA\RECVR_DB\ODM01.DBF
TOOLS 8
C:\ORACLE9I\ORADATA\RECVR_DB\TOOLS01.DBF
USERS 9
C:\ORACLE9I\ORADATA\RECVR_DB\USERS01.DBF
XDB 10
C:\ORACLE9I\ORADATA\RECVR_DB\XDB01.DBF
10 rows selected.
SQL> -- will backup the Example tablespace
SQL> alter tablespace Example begin backup;
Tablespace altered.
SQL> SELECT STATUS FROM V$TABLESPACE
2 ;
SELECT STATUS FROM V$TABLESPACE
*
ERROR at line 1:
ORA-00904: "STATUS": invalid identifier
SQL> DESC V$TABLESPACE
Name Null? Type
----------------------------------------- -------- ----------------------------
TS# NUMBER
NAME VARCHAR2(30)
INCLUDED_IN_DATABASE_BACKUP VARCHAR2(3)
SQL> alter tablespace Example END backup;
Tablespace altered.
SQL> @C:\loci\TEMP\wkldgen\build_workload_generator.sql
Connected.
Synonym dropped.
Connected.
Package created.
No errors.
Package body created.
No errors.
Connected.
Synonym created.
Grant succeeded.
SQL> SHOW USER
USER is "SYSTEM"
SQL> CONN OE/OR@RECVR
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> CONN OE/OE@RECVR
Connected.
SQL> SELECT TABLE_NAME FROM USER_TABLES;
TABLE_NAME
------------------------------
CUSTOMERS
INVENTORIES
ORDERS
ORDERS_L
ORDER_ITEMS
ORDER_ITEMS_L
PRODUCT_DESCRIPTIONS
PRODUCT_INFORMATION
PRODUCT_REF_LIST_NESTEDTAB
SUBCATEGORY_REF_LIST_NESTEDTAB
WAREHOUSES
11 rows selected.
SQL> CREATE TABLE CUSTOMERS_BAK AS
2 SELECT * FROM CUSTOMERS;
Table created.
SQL> ARCHIVE LOG LIST
ORA-01031: insufficient privileges
SQL> show spool
spool ON
SQL> -- actual recovery of datafile without a backup
SQL> -- datafile examples will be deleted after shutdown
SQL> -- An online backup has been taken
SQL> shutdown immediate
ORA-01031: insufficient privileges
SQL> conn sys/manager@recvr as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 126950220 bytes
Fixed Size 453452 bytes
Variable Size 109051904 bytes
Database Buffers 16777216 bytes
Redo Buffers 667648 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF'
SQL> select a.file#, a.error from v$recover_file a;
FILE# ERROR
---------- -----------------------------------------------------------------
5 FILE NOT FOUND
SQL> select b.name, a.ts#, a.status, a.file#, a.name
2 from v$tablespace b, v$datafile a
3 where b.ts# = a.ts#
4 /
NAME TS# STATUS FILE#
------------------------------ ---------- ------- ----------
NAME
--------------------------------------------------------------------------------
SYSTEM 0 SYSTEM 1
C:\ORACLE9I\ORADATA\RECVR_DB\SYSTEM01.DBF
UNDOTBS1 1 ONLINE 2
C:\ORACLE9I\ORADATA\RECVR_DB\UNDOTBS01.DBF
CWMLITE 3 ONLINE 3
C:\ORACLE9I\ORADATA\RECVR_DB\CWMLITE01.DBF
DRSYS 4 ONLINE 4
C:\ORACLE9I\ORADATA\RECVR_DB\DRSYS01.DBF
EXAMPLE 5 ONLINE 5
C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF
INDX 6 ONLINE 6
C:\ORACLE9I\ORADATA\RECVR_DB\INDX01.DBF
ODM 7 ONLINE 7
C:\ORACLE9I\ORADATA\RECVR_DB\ODM01.DBF
TOOLS 8 ONLINE 8
C:\ORACLE9I\ORADATA\RECVR_DB\TOOLS01.DBF
USERS 9 ONLINE 9
C:\ORACLE9I\ORADATA\RECVR_DB\USERS01.DBF
XDB 10 ONLINE 10
C:\ORACLE9I\ORADATA\RECVR_DB\XDB01.DBF
10 rows selected.
SQL> -- EXAMPLE tablespace lost
SQL> alter database create datafile 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF' as ''C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01b.DBF';
alter database create datafile 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF' as ''C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01b.DBF'
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> ed
Wrote file afiedt.buf
1* alter database create datafile 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF' as 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01b.DBF'
SQL> /
alter database create datafile 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF' as 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01b.DBF'
*
ERROR at line 1:
ORA-01178: file 5 created before last CREATE CONTROLFILE, cannot recreate
ORA-01110: data file 5: 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF'
SQL> alter tablespace EXAMPLE offline immediate;
alter tablespace EXAMPLE offline immediate
*
ERROR at line 1:
ORA-01109: database not open
SQL> alter database datafile 5 offline;
Database altered.
SQL> alter database create datafile 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF' as ''C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01b.DBF';
alter database create datafile 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF' as ''C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01b.DBF'
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> ed
Wrote file afiedt.buf
1* alter database create datafile 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF' as 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01b.DBF'
SQL> /
alter database create datafile 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF' as 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01b.DBF'
*
ERROR at line 1:
ORA-01178: file 5 created before last CREATE CONTROLFILE, cannot recreate
ORA-01110: data file 5: 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF'
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> alter database open;
Database altered.
SQL> alter database create datafile 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF' as 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01b.DBF';
alter database create datafile 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF' as 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01b.DBF'
*
ERROR at line 1:
ORA-01178: file 5 created before last CREATE CONTROLFILE, cannot recreate
ORA-01110: data file 5: 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF'
SQL> alter tablespace example offline immediate;
Tablespace altered.
SQL> alter database create datafile 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF' as 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01b.DBF';
alter database create datafile 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF' as 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01b.DBF'
*
ERROR at line 1:
ORA-01178: file 5 created before last CREATE CONTROLFILE, cannot recreate
ORA-01110: data file 5: 'C:\ORACLE9I\ORADATA\RECVR_DB\EXAMPLE01.DBF'
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> spool off