Funda 2 > Closed_backup - 10th May

sql> -------- back up sql> ------- closed backup sql> --- we want back up the datbase in closed type the archive file is in no archive mode sql> -------- closed backup for the datafile sql> archive log list database log mode archive mode automatic archival disabled archive destination d:\oracle oldest online log sequence 4 next log sequence to archive 0 current log sequence 6 sql> alter system set log_archive_list_start=false scope=spfle; alter system set log_archive_list_start=false scope=spfle * error at line 1: ora-02065: illegal option for alter system sql> ---- it will change only in no mount stage sql> shutdown immediate; ora-01109: database not open database dismounted. oracle instance shut down. sql> start nomount; sp2-0310: unable to open file "nomount.sql" sql> startup nomount; oracle instance started. total system global area 135338868 bytes fixed size 453492 bytes variable size 109051904 bytes database buffers 25165824 bytes redo buffers 667648 bytes sql> alter system set log_archive_list_start=false scope=spfile; alter system set log_archive_list_start=false scope=spfile * error at line 1: ora-02065: illegal option for alter system sql>

alter system set log_archive_start=false scope=spfile;

system altered. sql> alter database open; alter database open * error at line 1: ora-01507: database not mounted sql> alter databse mount; alter databse mount * error at line 1:

ora-00940: invalid alter command sql> alter database mount; database altered. sql> alter database open; database altered. sql> ---------- backup the control file sql> sql> sql> ------------ backup the datafile not control file sql> sql> sql> select name from v$datafile where ts#=9; name -------------------------------------------------------------------------------d:\oracle\oradata\iimdba\users01.dbf sql> alter database rename file 'd:\oracle\oradata\iimdba\users01.dbf' to 'd:\bkp\users01.dbf' 2 ; alter database rename file 'd:\oracle\oradata\iimdba\users01.dbf' to 'd:\bkp\users01.dbf' * error at line 1: ora-01511: error in renaming log/data files ora-01121: cannot rename database file 9 - file is in use or recovery ora-01110: data file 9: 'd:\oracle\oradata\iimdba\users01.dbf' sql> alter database rename datafile 'd:\oracle\oradata\iimdba\users01.dbf' to 'd:\bkp\users01.dbf'; alter database rename datafile 'd:\oracle\oradata\iimdba\users01.dbf' to 'd:\bkp\users01.dbf' * error at line 1: ora-02231: missing or invalid option to alter database sql> alter tablespace rename datafile 'd:\oracle\oradata\iimdba\users01.dbf' to 'd:\bkp\users01.dbf'; alter tablespace rename datafile 'd:\oracle\oradata\iimdba\users01.dbf' to 'd:\bkp\users01.dbf' * error at line 1: ora-02140: invalid tablespace name sql> ed wrote file afiedt.buf 1* alter tablespace users rename datafile 'd:\oracle\oradata\iimdba\users01.dbf' to 'd:\bkp\users01.dbf'

sql> / alter tablespace users rename datafile 'd:\oracle\oradata\iimdba\users01.dbf' to 'd:\bkp\users01.dbf' * error at line 1: ora-01525: error in renaming data files ora-01121: cannot rename database file 9 - file is in use or recovery ora-01110: data file 9: 'd:\oracle\oradata\iimdba\users01.dbf' sql> alter tablespace users rename datafile 'd:\oracle\oradata\iimdba\users01.dbf' to 'd:\bkp\users01.dbf; alter tablespace users rename datafile 'd:\oracle\oradata\iimdba\users01.dbf' to 'd:\bkp\users01.dbf * error at line 1: ora-01756: quoted string not properly terminated sql> alter tablespace users rename datafile 'd:\oracle\oradata\iimdba\users01.dbf' to 'd:\bkp\users01.dbf'; alter tablespace users rename datafile 'd:\oracle\oradata\iimdba\users01.dbf' to 'd:\bkp\users01.dbf' * error at line 1: ora-01525: error in renaming data files ora-01121: cannot rename database file 9 - file is in use or recovery ora-01110: data file 9: 'd:\oracle\oradata\iimdba\users01.dbf' sql> alter tablespace users begin backup; tablespace altered. sql> host sql> alter tablespace users end backup; tablespace altered. sql> alter tablespace users offline; tablespace altered. sql> ---------- we want to phsyically remove the file users01.dbf from oradata folder sql> sql> shutdown immediate; database closed. database dismounted. oracle instance shut down. sql> startup oracle instance started. total system global area fixed size variable size database buffers

135338868 453492 109051904 25165824

bytes bytes bytes bytes

redo buffers 667648 bytes database mounted. database opened. sql> alter tablespace users rename datafile 'd:\oracle\oradata\iimdba\users01.dbf' to 'd:\bkp\users01.dbf'; tablespace altered. sql> sql> alter tablespace users online; alter tablespace users online * error at line 1: ora-01157: cannot identify/lock data file 9 - see dbwr trace file ora-01110: data file 9: 'd:\bkp\users01.dbf' sql> alter tablespace users online; alter tablespace users online * error at line 1: ora-01113: file 9 needs media recovery ora-01110: data file 9: 'd:\bkp\users01.dbf' sql> --------- why media recovery came means if remove the tablespace from oradata folder and then we made any changes in tables or transcation we want to rollback the data if it is not rollbacked than the media recobery error comes sql> sql> ------ to avoid this error sql> sql> recover tablespace users; media recovery complete. sql> alter tablespace online; alter tablespace online * error at line 1: ora-02140: invalid tablespace name sql> alter tablespace users online; tablespace altered. sql> sql> shutdoen immediate; sp2-0734: unknown command beginning "shutdoen i..." - rest of line ignored. sql> shutdown immediate; database closed. database dismounted. oracle instance shut down. sql> startup; oracle instance started. total system global area fixed size variable size database buffers

135338868 453492 109051904 25165824

bytes bytes bytes bytes

redo buffers database mounted. database opened. sql> spool off

667648 bytes