Oracle control file addition, backup, recovery

The control file experiment has a maximum of eight control files, at least one of which is an image of each other. If the control file is lost, the database cannot be shut down normally and cannot be started. Add control file, required

The control file experiment has a maximum of eight control files, at least one of which is an image of each other. If the control file is lost, the database cannot be shut down normally and cannot be started. // Add control file, required

Control File Experiment
A maximum of 8 control files and at least 1 are mutual images. If the control file is lost, the database cannot be shut down normally and cannot be started.
// Add a control file. The original control file must be added. Note that the file names in the same path cannot be the same.
Alter system set control_files = '/lvm/app/Oracle/oradata/updb/control01.ctl', '/lvm/app/oracle/fast_recovery_area/updb/control02.ctl ', '/lvm/app/oracle/oradata/updb/control01a. ctl ','/lvm/app/oracle/fast_recovery_area/updb/control02.ctl 'scope = spfile;
Alter system set control_files = '/lvm/app/oracle/oradata/updb/control01.ctl', '/lvm/app/oracle/fast_recovery_area/updb/control02.ctl ', '/lvm/app/oracle/oradata/updb/control01a. ctl ','/lvm/app/oracle/fast_recovery_area/updb/control02a. ctl 'scope = spfile;

// Copy the original one to the newly created
SQL> ho cp/lvm/app/oracle/oradata/updb/control01.ctl/lvm/app/oracle/oradata/updb/control01a. ctl
SQL> ho cp/lvm/app/oracle/fast_recovery_area/updb/control02.ctl/lvm/app/oracle/fast_recovery_area/updb/control02a. ctl
Shutdown transactional
Startup

// Reduce the control file
Alter system set control_files = '/lvm/app/oracle/oradata/updb/control01.ctl', '/lvm/app/oracle/fast_recovery_area/updb/control02.ctl' scope = spfile;

Shutdown
Startup

// Control file Exception Handling

Set up the storage process according to the first two steps, and then create the following process
// Delete control file df = 8 delete all control files, df <8 delete Control Files
Create or replace procedure foway_test_1 (df number)
As
J number;
Begin
J: = 1;
If df> 8 or df <0 then
Dbms_output.put_line ('Number can not gt 8 or lt 0 ');
Else

For I in (select name from v $ controlfile) loop
If length (I. name)> 0 then
If df = 8 then
Del (I. name );
Else
If j = df then
Del (I. name );
Exit;
End if;

End if;
J: = j + 1;
End if;
End loop;
J: = 1;
End if;
End;
/

1. Report version errors at startup
Copy a higher version to a lower version
2. Some control files are lost.
// View the control file information first
SQL> select name from v $ controlfile;
Select name from v $ controlfile;

NAME
--------------------------------------------------------------------------------
/Lvm/app/oracle/oradata/updb/control01.ctl
/Lvm/app/oracle/fast_recovery_area/updb/control02.ctl
/Lvm/app/oracle/oradata/updb/control01a. ctl
/Lvm/app/oracle/fast_recovery_area/updb/control02a. ctl
Ls check one by one
SQL> exec foway_test_1 (1 );
Exec foway_test_1 (1 );

PL/SQL procedure successfully completed.
Check again
SQL> ho ls/lvm/app/oracle/oradata/updb/control01.ctl;
Ho ls/lvm/app/oracle/oradata/updb/control01.ctl;
Ls: cannot access/lvm/app/oracle/oradata/updb/control01.ctl: No such file or directory
Report file loss
Shutdown abort; // force shut down the database. Be sure to close the data before performing the operation. Otherwise, the database will crash.
Copy existing to lost
Ho cp/lvm/app/oracle/oradata/updb/control01a. ctl/lvm/app/oracle/oradata/updb/control01.ctl
Startup
Recovery completed
If a version error is reported, copy the previous version to the earlier version.

3. All control files are lost
Exec foway_test_1 (8 );

A. The database is not closed yet.
// Backup control file
Alter database backup controlfile to trace as '/tmp/c. SQL ';
Shutdown abort;
// Retrieve the control file creation script
Ho sed '1,/Set #2/p'-n/tmp/c. SQL>/tmp/c1. SQL;
Start/tmp/c1. SQL;
Start rman target/
Backup database;

B. The database has been closed but rman has a valid backup (there is no resetlogs after the backup, no re-creation control file, no clear logfile, the log files used by the current database are not lost and archived)
Rman target/
Startup nomount;
Restore controlfile from '* s * Backup file ';
Alter database mount;
Exit;

Sqlplus/as sysdba
Alter database backup controlfile to trace as '/tmp/c. SQL' reuse;
Shutdown abort;
// Retrieve the control file creation script
Ho sed '1,/Set #2/p'-n/tmp/c. SQL>/tmp/c1. SQL;
Start/tmp/c1. SQL;
Start rman target/
Backup database;

,