control file autobackup to recover from the lost all control files -- oracle 10g

Environment: Oracle 10gR2

Summary of the testing steps

1. First, configure RMAN to perform a control file autobackup:
2. Next, perform a backup with the control file autobackup enabled:
3. Next, simulate the missing control files by deleting all the control files.
4. Next, start the database in NOMOUNT mode, which is required because there is no control file to mount.
5. Next, connect to RMAN and the target database. Specify the DBID to identify the database you are connecting to
6. Next, restore the control file from backup:
7. Next, mount the database and begin to recover the database:
8. Finally, open the database with RESETLOGS option for normal operations:
 

Detailed steps:

1. First, configure RMAN to perform a control file autobackup:

RMAN> connect target
connected to target database: TOY10G (DBID=3330944552)
RMAN> configure controlfile autobackup on;
using target database control file instead of recovery catalog

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters are successfully stored

RMAN>
RMAN> show all;
RMAN>

RMAN configuration parameters are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/ora01/orabkup/GENQ/.temp_toy10g/oracle/admin/TOY10G/backup/test_%t_%s_%p.dbf';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/ora01/orabkup/GENQ/.temp_toy10g/oracle/admin/TOY10G/backup/snapcf_TOY10G.f';


RMAN>

2. Next, perform a backup with the control file autobackup enabled:


RMAN> run
2> {
3> backup database;
4> backup (archivelog all);
5> }

Starting backup at 06-JUL-2007 14:19:13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=35 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/system01.dbf

input datafile fno=00003 name=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/sysaux01.dbf

input datafile fno=00004 name=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/users01.dbf

input datafile fno=00005 name=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/indx01.dbf

input datafile fno=00002 name=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/undotbs01.dbf

channel ORA_DISK_1: starting piece 1 at 06-JUL-2007 14:19:15

channel ORA_DISK_1: finished piece 1 at 06-JUL-2007 14:19:22

piece handle=/ora01/orabkup/GENQ/.temp_toy10g/oracle/admin/TOY10G/backup/test_627229154_17_1.dbf tag=TAG20070706T141914 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
Finished backup at 06-JUL-2007 14:19:22
Starting backup at 06-JUL-2007 14:19:25
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=478 recid=1 stamp=627229166
channel ORA_DISK_1: starting piece 1 at 06-JUL-2007 14:19:27
channel ORA_DISK_1: finished piece 1 at 06-JUL-2007 14:19:28
piece handle=/ora01/orabkup/GENQ/.temp_toy10g/oracle/admin/TOY10G/backup/test_627229166_18_1.dbf tag=TAG20070706T141926 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 06-JUL-2007 14:19:28
Starting Control File Autobackup at 06-JUL-2007 14:19:28
piece handle=/ora01/orabkup/GENQ/.temp_toy10g/ora01/orarcv/TOY10G/TOY10G/autobackup/2007_07_06/o1_mf_n_627229168_38x5g116_.bkp comment=NONE
Finished Control File Autobackup at 06-JUL-2007 14:19:30

RMAN>

3. Next, simulate the missing control files by deleting all the control files. (The database will need to be shut down to perform this simulated failure.)


sys@TOY10G> select name from v$controlfile;


NAME
----------------------------------------------------------------------------------------------------
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.001.dbf
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.002.dbf
/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.003.dbf


after shutdown, do:

rm /ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.001.dbf
rm /ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.002.dbf
rm /ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.003.dbf


4. Next, start the database in NOMOUNT mode, which is required because there is no control file to mount:


idle> conn / as sysdba

Connected to an idle instance.

idle> startup nomount pfile='/ora01/orabkup/GENQ/.temp_toy10g/oracle/admin/TOY10G/pfile/initTOY10G.ora';

ORACLE instance started.


5. Next, connect to RMAN and the target database. Specify the DBID to identify the database you are connecting to, because the control file contains this information and failure causes the control file to be unavailable.


RMAN> connect target

connected to target database: TOY10G (not mounted)

RMAN> set dbid   3330944552 

executing command: SET DBID

RMAN>

6. Next, restore the control file from backup:

RMAN> restore controlfile from autobackup;
Starting restore at 06-JUL-2007 14:35:11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=47 devtype=DISK


recovery area destination: /ora01/orabkup/GENQ/.temp_toy10g/ora01/orarcv/TOY10G

database name (or database unique name) used for search: TOY10G

channel ORA_DISK_1: autobackup found in the recovery area

channel ORA_DISK_1: autobackup found: /ora01/orabkup/GENQ/.temp_toy10g/ora01/orarcv/TOY10G/TOY10G/autobackup/2007_07_06/o1_mf_n_627229168_38x5g116_.bkp

channel ORA_DISK_1: control file restore from autobackup complete

output filename=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.001.dbf

output filename=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.002.dbf

output filename=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oradata/TOY10G/control.003.dbf

Finished restore at 06-JUL-2007 14:35:18



RMAN>


7. Next, mount the database and begin to recover the database:

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> recover database;


Starting recover at 06-JUL-2007 14:44:20
Starting implicit crosscheck backup at 06-JUL-2007 14:44:20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=47 devtype=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 06-JUL-2007 14:44:22
Starting implicit crosscheck copy at 06-JUL-2007 14:44:22
using channel ORA_DISK_1
Crosschecked 5 objects
Finished implicit crosscheck copy at 06-JUL-2007 14:44:22


searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
File Name: /ora01/orabkup/GENQ/.temp_toy10g/ora01/orarcv/TOY10G/TOY10G/autobackup/2007_07_06/o1_mf_n_627229168_38x5g116_.bkp
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 479 is already on disk as file /ora01/orabkup/GENQ/.temp_toy10g/ora01/oraredo/TOY10G/redo02.log
archive log filename=/ora01/orabkup/GENQ/.temp_toy10g/ora01/oraredo/TOY10G/redo02.log thread=1 sequence=479
media recovery complete, elapsed time: 00:00:03
Finished recover at 06-JUL-2007 14:44:28

RMAN>

8. Finally, open the database with RESETLOGS option for normal operations:

RMAN> alter database open resetlogs;
database opened
RMAN>

Rename and drop table columns

If I have a relatively big table and I want to modify a column from one type to another, i.e. number to varchar2(50), I may experience very long time. Note if I just change, for example, from varchar2(10) to varchar2(50), it will not be a problem, Oracle can finish in a split of second.This was what I faced last night during the code release. The column was acutally added in the previous release, but for some reason it has not been used and is containing no data. The problem was that I would run out of the maintenance window if I Ire just waiting for it to complete (obsevered it would take 4-5 hours from v$session_longops view).

In this situation, the capability of renaming a column name comes to help. I renamed the column with a suffix '_old', and added a new column with the desired data type and name. It completed in seconds.

The syntax looks like:

SQL> alter table myschema.mytab rename column MYCOL to MYCOL_old;
SQL> alter table myschema.mytab add MYCOL varchar2(50);


It is noted in the Oracle online doc that when you rename a column, Oracle updates associated data dictionary tables to ensure that function-based indexes and check constraints remain valid.

So now comes to the question what is the best way to drop those *_old columns? - There are actually 4 tables with 8 columns I addressed in this way.

Checking the online document again, there is a section in the Admin Guide (9i) that covers some options. I did not see I can do parallel drop of a column. I did notice there is an interesting option: checkpoint, which I may want to consider as our tables are very large. For example:

ALTER TABLE hr.admin_emp DROP UNUSED COLUMNS CHECKPOINT 250;


The doc describes:
 
" This option causes a checkpoint to be applied after processing the specified number of rows, in this case 250. Checkpointing cuts down on the amount of undo logs accumulated during the drop column operation to avoid a potential exhaustion of undo space."

Oracle PL SQL cursor For loops

The cursor FOR LOOP statement implicitly declares its loop index as a record variable of the row type that a specified cursor returns and opens a cursor. With each iteration the cursor FOR LOOP statement fetches a row from the result set into the record. When there are no more rows to fetch, the cursor FOR LOOP statement closes the cursor. The cursor also closes if a statement inside the loop transfers control outside the loop or if an exception is raised.

The following procedure is followed in most of the situations in PL/SQL:

1.    Open a cursor

2.    Start a loop

3.    Fetch the cursor

4.    Check whether rows are returned

5.    Process

6.    Close the loop

7.    Close the cursor

Cursor For Loop allows us to simplify this procedure by letting PL/SQL do most of the things for us.

 You can simplify coding by using a Cursor For Loop instead of the OPEN, FETCH, and CLOSE statements.

 A Cursor For Loop implicitly declares its loop index as a record that represents a row fetched from the database.

Next, it opens a cursor, repeatedly fetches rows of values from the result set into fields in the record, and then closes the cursor when all rows have been processed.

The cursor FOR LOOP statement lets you issue a SELECT statement and then immediately loop through the rows of the result set. This statement can use either an implicit or explicit cursor. If you use the SELECT statement only in the cursor FOR LOOP statement, then specify the SELECT statement inside the cursor FOR LOOP statement, as in. This form of the cursor FOR LOOP statement uses an implicit cursor, and is called an implicit cursor FOR LOOP statement. Because the implicit cursor is internal to the statement, you cannot reference it with the name SQL. If you use the SELECT statement more than once in the same PL/SQL unit, then define an explicit cursor for it and specify that cursor in the cursor FOR LOOP statement. This form of the cursor FOR LOOP statement is called an explicit cursor FOR LOOP statement. You can use the same explicit cursor elsewhere in the same PL/SQL unit. The cursor FOR LOOP statement implicitly declares its loop index as a %ROWTYPE record variable of the type that its cursor returns. This record is local to the loop and exists only during loop execution. Statements inside the loop can reference the record and its fields. They can reference calculated columns only by aliases. After declaring the loop index record variable, the FOR LOOP statement opens the specified cursor. With each iteration of the loop, the FOR LOOP statement fetches a row from the result set and stores it in the record. When there are no more rows to fetch, the cursor FOR LOOP statement closes the cursor. The cursor also closes if a statement inside the loop transfers control outside the loop or if an exception is raised.

Cursor For Loop Example

DECLARE CURSOR c1 IS

 SELECT ename, sal, deptno

 FROM emp;

 ...

 BEGIN

 FOR emp_rec IN c1 LOOP

 ...

 salary_total := salary_total +

 emp_rec.sal;

 END LOOP;

PL/SQL Ref Cursors

Implicit cursors.  Explicit cursors.  PL SQL cursors.


A REF CURSOR or cursor variable is just a reference or a handle to a static cursor. It allows a user to pass this "reference to the same cursor" among all the programs that need access to the cursor. Cursor variables give you easy access to centralized data retrieval.

There are two types of cursor variables:

Strong REF Cursor and

Weak REF Cursor

What is the difference between Cursor and REF Cursor, and when would you appropriately use each of these?

 Technically, under the covers, at the most "basic level", they are the same.

 A "normal" PL/SQL cursor is static in definition.

Ref cursors may be dynamically opened or opened based on logic.

 Declare

 type rc is ref cursor;

 cursor c is select * from dual;

 l_cursor rc;

 begin

 if (to_char(sysdate,'dd') = 30 ) then

 open l_cursor for 'select * from emp';

 elsif ( to_char(sysdate,'dd') = 29 ) then

 open l_cursor for select * from dept;

 else

 open l_cursor for select * from dual;

 end if;

 open c;

 end;

 /

Given that block of code - you see perhaps the most "salient" difference - no matter how many times you run that block - cursor C will always be select * from dual. The ref cursor can be anything.

Another difference is a ref cursor can be returned to a client. A PL/SQL cursor cannot be returned to a client.

Another difference is a cursor can be global - a ref cursor cannot (you cannot define them OUTSIDE of a procedure / function).

Another difference is a ref cursor can be passed from subroutine to subroutine - a cursor cannot be.

Another difference is that static SQL (not using a ref cursor) is much more efficient then using ref cursors and that use of ref cursors should be limited to.

returning result sets to clients

when there is NO other efficient/effective means of achieving the goal. That is, you want to use static SQL (with implicit cursors really) first and use a ref cursor only when you absolutely have to.

 A ref cursor is a variable, defined as a cursor type, which will point to, or reference a cursor result. The advantage that a ref cursor has over a plain cursor is that is can be passed as a variable to a procedure or a function. The ref cursor can be assigned to other ref cursor variables. This is a powerful capability in that the cursor can be opened, then passed to another block for processing, then returned to the original block to be closed. The cursor variable can also be returned by a function and assigned to another variable. The ref cursor variable is not a cursor, but a variable that points to a cursor. Before assigning a cursor variable, a cursor type must be defined.

 type author_cursor is ref cursor;

 This "REF CURSOR" is a weak typed cursor variable because it does not define the datatype the cursor will return. Below is the same cursor that is strongly typed.

 type author_cursor is ref cursor

 returning author%rowtype;

 By strongly typing the cursor variable, you define what the cursor can return. If a strongly typed cursor returns something other that its return type, a "ROWTYPE_MISMATCH" exception is raised. A strongly typed cursor type is less flexible but less prone to programming errors. The PL/SQL compiler will verify that the "FETCH clause" has the correct variable/record for the cursor return type at compile time.

 Once the cursor type is defined, the actual variable can be defined as the cursor type.

 c1 author_cursor;

 Now c1 is a variable of a cursor type. It is opened using a SQL statement.

 open c1 for select * from authors;

 Now c1 has all the attributes of the actual cursor. As with any cursor it is important to close the cursor as soon as you have completed processing.

 SQL> declare

 2 type auth_cursor is ref cursor;

 3 c1 auth_cursor;

 4 r_c1 author%rowtype;

 5 begin

 6 open c1 for select * from author;

 7 fetch c1 into r_c1;

 8 if c1%isopen then

 9 dbms_output.put_line('The Cursor is open.');

 10 end if;

 11 dbms_output.put_line('Row Count is '||c1%rowcount);

 12 close c1;

 13 if not c1%isopen then

 14 dbms_output.put_line('The Cursor is closed.');

 15 end if;

 16 end;

 17 /

The Cursor is open.

 Row Count is 1

 The Cursor is closed.

 Here some of the cursor attributes are used to process the cursor. Notice that the record used to hold a fetched cursor row is defined as an author table "%rowtype". Even though the example cursor variable is defined, the record can not use it because the cursor variable return type is not defined. The example below attempts to create a cursor %rowtype variable for processing.

 SQL> declare

 2 type auth_cursor is ref cursor;

 3 c1 auth_cursor;

 4 r_c1 c1%rowtype;

 5 begin

 6 null;

 7 end;

 8 /

r_c1 c1%rowtype;

 *

 ERROR at line 4:

 ORA-06550: line 4, column 8:

 PLS-00320: the declaration of the type of this

 expression is incomplete or malformed

 ORA-06550: line 4, column 8:

 PL/SQL: Item ignored

 However, a strongly typed cursor can use the cursor variable to define the cursor record.

 SQL> declare

 2 type auth_cursor is ref cursor return author%rowtype;

 3 c1 auth_cursor;

 4 r_c1 c1%rowtype;

 5 begin

 6 null;

 7 end;

 8 /

 In this example, the auth_cursor type returns an author%rowtype. Because this is defined in line 2, the record defined in line 4 can now use it.Cursor variables that are weakly typed can be defined to return any values. In the example below, the cursor variable c1 is defined as three different statements.

 SQL> declare

 2 type auth_cursor is ref cursor;

 3 c1 auth_cursor;

 4 r_c1 author%rowtype;

 5 r2_c1 book%rowtype;

 6 r3_c1 number;

 7 begin

 8 open c1 for select * from author;

 9 fetch c1 into r_c1;

 10 close c1;

 11 open c1 for select * from book;

 12 fetch c1 into r2_c1;

 13 close c1;

 14 open c1 for select sum(quantity)

 15 from store join sales using (store_key)

 16 group by store_name;

 17 fetch c1 into r3_c1;

 18 close c1;

 19 end;

 20 /

 Although the block does not do anything but open and close the cursor variable, it does demonstrate that weakly typed variables can be defined differently each time the variable is opened. But what happens when a cursor variable is defined with a SQL statement but returns unexpected values? Below is an example of defining a cursor variable of one type and the record as another. The cursor variable is returning all columns in the book table, but the receiving variable is defined as a record of columns from the author table.

 SQL> declare

 2 type auth_cursor is ref cursor;

 3 c1 auth_cursor;

 4 r_c1 author%rowtype;

 5 begin

 6 open c1 for select * from book;

 7 fetch c1 into r_c1;

 8 close c1;

 9 end;

 10 /

 declare

 *

 ERROR at line 1:

 ORA-06504: PL/SQL: Return types of Result Set

 variables or query do not match

 ORA-06512: at line 7

 Notice that the error message is pointing to the DECLARE clause. That is because the block successfully compiled and only threw the error when it was executed. The PL/SQL compiler can not catch the error because the cursor type did not define a return type. By changing the definition of the cursor type to a strongly typed definition, the compiler will catch this error when the code is compiled.

 SQL> declare

 2 type auth_cursor is ref cursor

 3 return book%rowtype;

 4 c1 auth_cursor;

 5 r_c1 author%rowtype;

 6 begin

 7 open c1 for select * from book;

 8 fetch c1 into r_c1;

 9 close c1;

 10 end;

 11 /

 fetch c1 into r_c1;

 *

 ERROR at line 8:

 ORA-06550: line 8, column 5:

 PLS-00394: wrong number of values in the INTO

 list of a FETCH statement

 ORA-06550: line 8, column 5:

 PL/SQL: SQL Statement ignored

Now the compiler catches the error. So far the examples have been using the cursor variables as regular cursors. The real advantage of using a cursor variable is the ability to pass it as a parameter. In the example below a local function is used to open a cursor called c1 and return it.

 The block body calls the function to assign the cursor to the cursor variable c2. The block body then processes the cursor and closes it.

SQL> declare

 2 type auth_cursor is ref cursor

 return author%rowtype;

 3 c2 auth_cursor;

 4 r_c2 c2%rowtype;

 5

 6 function get_auth return auth_cursor

 7 is

 8 c1 auth_cursor;

 9 begin

 10 open c1 for select * from author;

 11 return c1;

 12 end;

 13

 14 begin

 15 c2 := get_auth;

 16 loop

 17 fetch c2 into r_c2;

 18 exit when c2%notfound;

 19 dbms_output.put_line(initcap(r_c2.author_last_name));

 20 end loop;

 21 close c2;

 22 end;

 23 /

 Jones

 Hester

 Weaton

 Jackie

 Withers

 Petty

 Clark

 Mee

 Shagger

 Smith

 Line 2 defines the cursor type. Lines 3 and 4 define the cursor and return record used in the body. Line 6 declares a local function called get_auth that returns an auth_cursor type. Inside this local function, cursor c1 is defined as an auth_cursor type, opened and returned to the calling code. The function is actually executed on line 15 when c2 is assigned the return value of the get_auth function. The cursor c2 is processed and finally closed on line 21. Note that c1 opened the cursor and c2 closed it. This is an important point.

The example contains only ONE cursor. When c2 is assign the value of c1, both variables point to the same cursor. Remember that c1 and c2 are variables that point to or reference the actual cursor.

 The same basic example is shown below except, the output is generated by a local procedure. Note that the procedure print_name gets passed the cursor c2 and then processes it. It then passes the cursor backup to be closed by the body of the PL/SQL block.

 SQL> declare

 2 type auth_cursor is ref cursor

 return author%rowtype;

 3 c2 auth_cursor;

 4 r_c2 c2%rowtype;

 5

 6 function get_auth return auth_cursor

 7 is

 8 c1 auth_cursor;

 9 begin

 10 open c1 for select * from author;

 11 return c1;

 12 end;

 13

 14 procedure print_name (c3 in out auth_cursor)

 15 as

 16 r_c3 author%rowtype;

 17 begin

 18 loop

 19 fetch c3 into r_c3;

 20 exit when c3%notfound;

 21 dbms_output.put_line(initcap(r_c3.author_last_name));

 22 end loop;

 23 end;

 24

 25 begin

 26 c2 := get_auth;

 27 print_name(c2);

 28 close c2;

 29 end;

 30 /

 Jones

 Hester

 Weaton

 Jeckle

 Withers

 Petty

 Clark

 Mee

 Shagger

 Smith

 There are three items you should note about this PL/SQL block. First, the body is pretty simple to understand.

 Get the authors, print the names, and close the cursor. Second, you can pass cursor variables to procedures and functions, and functions can return the variables. Lastly, it can become confusing about when a cursor variable should be closed.

 Source:

Fast-Track
Oracle

see also: Implicit cursors.  Explicit cursors.  PL SQL cursors.