Sample Altering Datafile Availability NoArchivelog Mode

Primer promene dostupnosti datoteke NoArchivelog Mode

Sample Altering Datafile Availability NoArchivelog Mode. 1

Primer promene dostupnosti datoteke NoArchivelog Mode. 1

Rename datafile db down - Rename datoteke pre podizanja baze. 1

Try to start database – Pokušaj starta baze podataka. 2

DATAFILE OFFLINE FOR DROP; 2

Open the database – Baza podataka se otvara. 2

Datafile Status – Statusi datoteka. 3

Return the file back – Vraćanje datoteke na disk. 3

Put Datafile OnLine – Postaviti datoteku baze ONLINE. 3

Rename datafile db down - Rename datoteke pre podizanja baze

SQL> select FILE_NAME,TABLESPACE_NAME,BYTES,STATUS from DBA_DATA_FILES;

FILE_NAME TABLESPACE_NAME BYTES STATUS

-------------------------------------------------- ------------------------------ ---------- ---------

E:\ORACLE\PRODUCT\ORACLEXE\ORADATA\XE\USERS.DBF USERS 104857600 AVAILABLE

E:\ORACLE\PRODUCT\ORACLEXE\ORADATA\XE\SYSAUX.DBF SYSAUX 461373440 AVAILABLE

E:\ORACLE\PRODUCT\ORACLEXE\ORADATA\XE\UNDO.DBF UNDO 94371840 AVAILABLE

E:\ORACLE\PRODUCT\ORACLEXE\ORADATA\XE\SYSTEM.DBF SYSTEM 367001600 AVAILABLE

E:\ORACLE\PRODUCT\ORACLEXE\APP\ORACLE\PRODUCT\10.2 USERS01 20971520 AVAILABLE

.0\SERVER\DATABASE\USERS03.DBF

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> $

Microsoft Windows XP [Version 5.1.2600]

E:\oracle\product\oraclexe\app\oracle\product\10.2.0\server\BIN>cd E:\oracle\product\oraclexe\app\oracle\product\10.2.0\server\database

E:\oracle\product\oraclexe\app\oracle\product\10.2.0\server\database>rename USERS03.DBF USERS03a.DBF

E:\oracle\product\oraclexe\app\oracle\product\10.2.0\server\database>dir users03*

Volume in drive E has no label.

Volume Serial Number is 3CA6-D692

Directory of E:\oracle\product\oraclexe\app\oracle\product\10.2.0\server\database

03/18/2007 12:39 AM 20,979,712 USERS03a.DBF

Try to start database – Pokušaj starta baze podataka

E:\oracle\product\oraclexe\app\oracle\product\10.2.0\server\database>exit

SQL> startup

ORACLE instance started.

Total System Global Area 146800640 bytes

Fixed Size 1286220 bytes

Variable Size 92278708 bytes

Database Buffers 50331648 bytes

Redo Buffers 2904064 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 5 - see DBWR trace file

ORA-01110: data file 5: 'E:\ORACLE\PRODUCT\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\SERVER\DATABASE\USERS03.DBF'

DATAFILE OFFLINE FOR DROP;

SQL> ALTER DATABASE DATAFILE 'E:\ORACLE\PRODUCT\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\SERVER\DATABASE\USERS03.DBF' OFFLINE FOR DROP;

Database altered.

Open the database – Baza podataka se otvara

SQL> select OPEN_MODE from v$database;

OPEN_MODE

----------

MOUNTED

SQL> alter database open;

Database altered.

Datafile Status – Statusi datoteka

SQL> select FILE_NAME,TABLESPACE_NAME,BYTES,STATUS from DBA_DATA_FILES;

SQL> select FILE_NAME,TABLESPACE_NAME,BYTES,STATUS,ONLINE_STATUS from DBA_DATA_FILES;

FILE_NAME TABLESPACE_NAME BYTES STATUS ONLINE_

-------------------------------------------------- ------------------------------ ---------- --------- -------

E:\ORACLE\PRODUCT\ORACLEXE\ORADATA\XE\USERS.DBF USERS 104857600 AVAILABLE ONLINE

E:\ORACLE\PRODUCT\ORACLEXE\ORADATA\XE\SYSAUX.DBF SYSAUX 461373440 AVAILABLE ONLINE

E:\ORACLE\PRODUCT\ORACLEXE\ORADATA\XE\UNDO.DBF UNDO 94371840 AVAILABLE ONLINE

E:\ORACLE\PRODUCT\ORACLEXE\ORADATA\XE\SYSTEM.DBF SYSTEM 367001600 AVAILABLE SYSTEM

E:\ORACLE\PRODUCT\ORACLEXE\APP\ORACLE\PRODUCT\10.2 USERS01 AVAILABLE OFFLINE

.0\SERVER\DATABASE\USERS03.DBF

SQL> select NAME,STATUS,ENABLED from v$datafile;

NAME STATUS ENABLED

-------------------------------------------------- ------- ----------

E:\ORACLE\PRODUCT\ORACLEXE\ORADATA\XE\SYSTEM.DBF SYSTEM READ WRITE

E:\ORACLE\PRODUCT\ORACLEXE\ORADATA\XE\UNDO.DBF ONLINE READ WRITE

E:\ORACLE\PRODUCT\ORACLEXE\ORADATA\XE\SYSAUX.DBF ONLINE READ WRITE

E:\ORACLE\PRODUCT\ORACLEXE\ORADATA\XE\USERS.DBF ONLINE READ WRITE

E:\ORACLE\PRODUCT\ORACLEXE\APP\ORACLE\PRODUCT\10.2 OFFLINE READ WRITE

.0\SERVER\DATABASE\USERS03.DBF

Return the file back – Vraćanje datoteke na disk

SQL> $

Microsoft Windows XP [Version 5.1.2600]

E:\oracle\product\oraclexe\app\oracle\product\10.2.0\server\BIN>copy E:\ORACLE\PRODUCT\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\SERVER\DATABASE\USERS03a.dbf E:\ORACLE\PRODUCT\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\SERVER\DATABASE\USERS03.dbf

1 file(s) copied.

E:\oracle\product\oraclexe\app\oracle\product\10.2.0\server\BIN>exit

Put Datafile OnLine – Postaviti datoteku baze ONLINE

SQL> ALTER DATABASE DATAFILE 'E:\ORACLE\PRODUCT\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\SERVER\DATABASE\USERS03.dbf' online;

Database altered.

SQL> select NAME,STATUS,ENABLED from v$datafile;

NAME STATUS ENABLED

-------------------------------------------------- ------- ----------

E:\ORACLE\PRODUCT\ORACLEXE\ORADATA\XE\SYSTEM.DBF SYSTEM READ WRITE

E:\ORACLE\PRODUCT\ORACLEXE\ORADATA\XE\UNDO.DBF ONLINE READ WRITE

E:\ORACLE\PRODUCT\ORACLEXE\ORADATA\XE\SYSAUX.DBF ONLINE READ WRITE

E:\ORACLE\PRODUCT\ORACLEXE\ORADATA\XE\USERS.DBF ONLINE READ WRITE

E:\ORACLE\PRODUCT\ORACLEXE\APP\ORACLE\PRODUCT\10.2 ONLINE READ WRITE

.0\SERVER\DATABASE\USERS03.DBF

SQL> select FILE_NAME,TABLESPACE_NAME,BYTES,STATUS,ONLINE_STATUS from DBA_DATA_FILES;

FILE_NAME TABLESPACE_NAME BYTES STATUS ONLINE_

-------------------------------------------------- ------------------------------ ---------- --------- -------

E:\ORACLE\PRODUCT\ORACLEXE\ORADATA\XE\USERS.DBF USERS 104857600 AVAILABLE ONLINE

E:\ORACLE\PRODUCT\ORACLEXE\ORADATA\XE\SYSAUX.DBF SYSAUX 461373440 AVAILABLE ONLINE

E:\ORACLE\PRODUCT\ORACLEXE\ORADATA\XE\UNDO.DBF UNDO 94371840 AVAILABLE ONLINE

E:\ORACLE\PRODUCT\ORACLEXE\ORADATA\XE\SYSTEM.DBF SYSTEM 367001600 AVAILABLE SYSTEM

E:\ORACLE\PRODUCT\ORACLEXE\APP\ORACLE\PRODUCT\10.2 USERS01 20971520 AVAILABLE ONLINE

.0\SERVER\DATABASE\USERS03.DBF