How to Recreate a Controlfile

How to Recreate a Controlfile


This article describes how you can recreate your controlfile.
Solution

Warning

You should only recreate your control file under the following circumstances:

* All current copies of the control file have been lost or are corrupted.
* You are restoring a backup in which the control file is corrupted or missing.
* You need to change a hard limit database parameter in the controlfile.
* If you are moving your database to another server and files are located in a different location.
* Oracle Customer Support advises you to.

Creating a new Controlfile from an existing database that is mounted or open.

First you must generate an ascii dump of the controlfile.

Whilst the database is mounted or open issue:
SQL> alter database backup controlfile to trace;

A trace file will be generated in the user_dump_destination directory.
SQL> show parameter user_dump_dest

NAME TYPE VALUE
-------------- ------ ------------------------------------------------
user_dump_dest string /oracle/product/11.1.0/db_1/diag/rdbms/V11/trace

After navigating to the directory locate the latest trace file by date/time by issuing: ls -ltr.
% cd /oracle/product/11.1.0/db_1/diag/rdbms/V11/trace
% ls -ltr

Once you locate the file it will appear as an ordinary trace file:
Trace file /oracle/product/11.1.0/db_1/diag/rdbms/V11/trace/V11_ora_31225.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/product/11.1.0/db_1

You are interested in the section that contains the create controlfile script.

Modify the trace file and use it as a script to create the control.



CREATE CONTROLFILE REUSE DATABASE "V11" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oradata/V11/redo01.log' SIZE 50M,
GROUP 2 '/oradata/V11/redo02.log' SIZE 50M,
GROUP 3 '/oradata/V11/redo03.log' SIZE 50M
DATAFILE
'/oradata/V11/system01.dbf',
'/oradata/V11/sysaux01.dbf',
'/oradata/V11/undotbs01.dbf'
'/oradata/V11/user01.dbf'
CHARACTER SET WE8MSWIN1252
;




It is important to delete everything above the "CREATE CONTROLFILE" and
everything after the CHARACTER SET. Ensure you leave the semi colon. ";".
In the above example we are choosing the NORESETLOGS option and running the
database in archivelog mode. After successfully saving the script you are now
able to recreate the controlfile. When shutting down the database ensure that
you shutdown with the immediate option.




SQL> shutdown immediate;




NOTE: Oracle recommeds that a full database is taken prior to a creation of the
controlfile.




SQL> startup nomount;



SQL>@control.sql








Once the controlfile is successfully created the database is automatically
mounted. If you have opened the database with a resetlogs it is important to
take a backup asap.

Creating a new controlfile from a database that is not able to mount.

Under the rare occasion that you do not have a controlfile to either:

1. Restore

2. or have a script from a "backup controlfile to trace script"

you must create a script from the beginning.

CREATE CONTROLFILE REUSE DATABASE "DBNAME" NORESETLOGS ARCHIVELOG

Follow the format listing:

- Location of redo logs.

- Location of datafiles

- Specifying the characterset.

Once you have listed all files correctly you are ready to recreate your controlfile
SQL> startup nomount;

SQL>@control.sql

RMAN : Consistent Backup, Restore and Recovery using RMAN

RMAN : Consistent Backup, Restore and Recovery using RMAN [ID 162855.1]
Modified 19-OCT-2010 Type HOWTO Status PUBLISHED


"Checked for relevance on 02-JUL-2010"

Oracle9i Lab 1


Consistent Backup, Restore and Recovery using RMAN


In Oracle9i, Recovery Manager still works the same as in 8 and 8i with
enhancements for ease of use and manageability. Using the updateable
persistent configuration options, backing up the database is easier then
ever. To begin lets look where information is stored in the database about
RMAN backups. For testing we'll use the target database controlfile without
a recovery catalog. By setting up the autocontrolfile backup feature we can
use the controlfile backups for a recovery catalog.



1. V$ tables - What does the controlfile know?

What are the views and synonyms related to backup and rman?
Let's take a look before backups are done to see what information
they hold for us.

SQL> set pagesize 60
SQL> column object_name format a30
SQL> select object_name from dba_objects
2 where object_name like '%BACKUP%'
3 and object_type = 'SYNONYM';

select object_name from dba_objects
where object_name like '%RMAN%'
and object_type = 'SYNONYM';


OBJECT_NAME
--------------------------------
V$BACKUP_CORRUPTION
V$BACKUP_DATAFILE
V$BACKUP_DEVICE
V$BACKUP_PIECE
V$BACKUP_REDOLOG
V$BACKUP_SET
V$BACKUP_SYNC_IO
V$RMAN_CONFIGURATION
V$BACKUP
V$BACKUP_ASYNC_IO


Other controlfile views to reference are:

v$controlfile_record_section, v$copy_corruption, v$database, v$datafile,
v$datafile_copy, v$offline_range, v$proxy_archivelog, v$proxy_datafile,
v$session_longops, v$database_block_corruption


2. RMAN Configuration

Check the default configuration in RMAN and then check the V$ view to see what
is stored. Then update the configuration in RMAN and take a look at the view
again. This is accomplished in RMAN using the "show all" command.

% sqlplus "/ as sysdba"

set pagesize 60
spool rman_new_conf.lst
column name format a30
column VALUE format a31
set echo on;
set serveroutput on;
select * from v$rman_configuration;
spool off;

CONF# NAME VALUE
---------- ------------------------------ -------------------------------
1 RETENTION POLICY TO REDUNDANCY 3
2 BACKUP OPTIMIZATION OFF
3 DEFAULT DEVICE TYPE TO DISK
4 CONTROLFILE AUTOBACKUP ON
5 CONTROLFILE AUTOBACKUP FORMAT DISK TO '/proj/SME9i/backup/%F'
FOR DEVICE TYPE
6 DEVICE TYPE DISK PARALLELISM 2
7 DATAFILE BACKUP COPIES FOR DEV DISK TO 1
ICE TYPE
8 ARCHIVELOG BACKUP COPIES FOR D DISK TO 1
EVICE TYPE
9 MAXSETSIZE TO UNLIMITED

9 rows selected.


Since we are not using a recovery catalog let's look at the controlfile since
this is our catalog without a recovery catalog. In sqlplus run:

select TYPE, RECORDS_TOTAL, RECORDS_USED
from v$controlfile_record_section
where type like '%BACKUP%';

TYPE RECORDS_TOTAL RECORDS_USED
------------------ ------------- ------------
BACKUP SET 101 0
BACKUP PIECE 204 0
BACKUP DATAFILE 210 0
BACKUP REDOLOG 53 0
BACKUP CORRUPTION 185 0


The records used column is still zero since we have not taken any backups.
The records are inserted into these views after successful creation of a
backupset. When RMAN reads the files to backup, if any corruption is
encountered then the corruption views are populated with file#, block# and
contiguous blocks after the initial corrupt block.


3. Create a persistent configuration for reuse

Create a persistent backup configuration. Once these parameters are configured
RMAN will continue to reuse the configured options for subsequent backups unless
you override the option within your script or to clear or disable it.

In 9i RMAN you use the "show" command to see the currently configured options.


SHOW show_operand [,show_operand ...];

show_operand: RETENTION POLICY |
EXCLUDE |
BACKUP COPIES |
CHANNEL |
DEFAULT DEVICE TYPE |
DEVICE TYPE |
SNAPSHOT CONTROLFILE |
...
ALL




RMAN> show all;

using target database controlfile instead of recovery catalog
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 OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/beta/app/oracle/product/9.0.1/dbs/snapc
f_V901.f'; # default


You can script the configuration you want to use within a run block to change
more then one parameter or just change a single parameter at a time from the
RMAN prompt not using the run block.

run {
# Use the configure commands to create your backup policy.
# When complete these will be the new persistent
# configuration parameters for RMAN in the controlfile
# of the target datbase.
#
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO
'/proj/SME9i/backup/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/proj/SME9i/backup/snapf_prod9.f';
}



RMAN> show all;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
CONFIGURE BACKUP OPTIMIZATION OFF;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO
'/proj/SME9i/backup/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE MAXSETSIZE TO UNLIMITED;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/proj/SME9i/backup/snapf_prod9.f';


4. Create a consistent backup script

Create an RMAN script to generate closed database backups without a recovery
catalog. We set the autocontrolfile backup feature during configuration so
the backup controlfile will become our catalog to avoid the overhead of
maintaining a second database for recovery.

% vi backup_lvl0_cold.rcv

## Consistent backup
#
connect target;
run {
shutdown immedate;
startup mount pfile=/proj/SME9i/prod9/pfile/initprod9.ora;
backup
incremental level 0
database format '/proj/SME9i/backup/%d_closed_%U'
tag=prod9_closed_LVL0;
shutdown;
startup pfile=/proj/SME9i/prod9/pfile/initprod9.ora;
}
exit


5. Create a backup using backup_lvl0_cold.rcv.

Make a backup of the target database using the new script. Rerun the loop
script in sqlplus to generate changes and run another backup of the database.
Do this until you have 4 backups of the database.


How many backup records were created for the backup we did?
Let's look at the controlfile and verify what's there.

select TYPE, RECORDS_TOTAL, RECORDS_USED
from v$controlfile_record_section
where type like '%BACKUP%';

TYPE RECORDS_TOTAL RECORDS_USED
------------------ ------------- ------------
BACKUP SET 101 3
BACKUP PIECE 204 3
BACKUP DATAFILE 210 9
BACKUP REDOLOG 53 0
BACKUP CORRUPTION 185 0


We have 2 backupsets from the parallelism 2 in the channel persistent
configuration set in step #3. The files to channel algorithm will split
the files across the allocated channels, since there are 2 channels there
will be 2 backupsets that consist of the datafile backups. The 3rd is the
controlfile backup created with "AUTOCONTROLFILE BACKUP" feature.


Use the "by summary" option to list your backups in RMAN.
This shows a short summary of your backupsets.

1 - channel datafile backupset
2 - channel datafile backupset
3 - autocontrolfile backup


RMAN> list backup summary;

List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Tag
------- -- -- - ----------- --------------- ------- ------- ---
1 B F A DISK 20-JUL-01 1 1
2 B F A DISK 20-JUL-01 1 1
3 B F A DISK 20-JUL-01 1 1

The "list backup" command will also show the files associated with the
backupset key. It uses verbose by default.


6. Sample procedure for change generation.

This is a test procedure to generate 5 1M archived logs or just to
generate changes to the database to create blocks for backup. Useful
for incremental backup testing.

-- loop.sql

-- set echo off

drop table team1;

create table team1
(col1 number, col2 date);

drop sequence team1_seq;

create sequence team1_seq start with 1;

truncate table team1;

begin
for i in 1..100 loop
for i in 1..200 loop
insert into team1
values(team1_seq.nextval, sysdate);
end loop;
commit;
end loop;
end;
/
-- end


7. Create a test schema.

Create a schema to generate changes to the database so there will changed
blocks for backup. Then connect as that user and run "loop.sql". This can
be used to make changes for incremental backups.


SQL> create user team1 identified by team1
2 default tablespace users
3 temporary tablespace temp;

User created.


SQL> grant connect, resource to team1;

Grant succeeded.


SQL> connect team1/team1
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.

SQL> @loop.sql
drop table team1
*
ERROR at line 1:
ORA-00942: table or view does not exist

Table created.


drop sequence team1_seq
*
ERROR at line 1:
ORA-02289: sequence does not exist

Sequence created.

Table truncated.

PL/SQL procedure successfully completed.

SQL> exit


8. Create a new closed database backup of the database.


% rman trace b_prod9_2_closed.log

RMAN> run {
shutdown immediate;
startup mount pfile=/proj/SME9i/prod9/pfile/initprod9.ora;
backup
incremental level 0
database format '/proj/SME9i/backup/%d_closed_%U';
shutdown;
startup pfile=/proj/SME9i/prod9/pfile/initprod9.ora;
}


9. Verify the new backup information in sqlplus and compare with RMAN.

sqlplus "/ as sysdba"

select TYPE, RECORDS_TOTAL, RECORDS_USED
from v$controlfile_record_section
where type like '%BACKUP%';

TYPE RECORDS_TOTAL RECORDS_USED
------------------ ------------- ------------
BACKUP SET 101 6
BACKUP PIECE 204 6
BACKUP DATAFILE 210 18
BACKUP REDOLOG 53 0
BACKUP CORRUPTION 185 0




RMAN> list backup summary;

using target database controlfile instead of recovery catalog

List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Tag
------- -- -- - ----------- --------------- ------- ------- ---
1 B F A DISK 20-JUL-01 1 1
2 B F A DISK 20-JUL-01 1 1
3 B F A DISK 20-JUL-01 1 1

4 B F A DISK 20-JUL-01 1 1
5 B F A DISK 20-JUL-01 1 1
6 B F A DISK 20-JUL-01 1 1




RMAN> list backup by file;


List of Datafile Backups
========================

File Key TY LV S Ckp SCN Ckp Time #Pieces #Copies Tag
---- ------- - -- - ---------- --------- ------- ------- ---
1 5 B F A 97763 20-JUL-01 1 1
2 B F A 96266 20-JUL-01 1 1
2 4 B F A 97763 20-JUL-01 1 1
1 B F A 96266 20-JUL-01 1 1
3 4 B F A 97763 20-JUL-01 1 1
1 B F A 96266 20-JUL-01 1 1
4 5 B F A 97763 20-JUL-01 1 1
2 B F A 96266 20-JUL-01 1 1
5 5 B F A 97763 20-JUL-01 1 1
2 B F A 96266 20-JUL-01 1 1
6 4 B F A 97763 20-JUL-01 1 1
1 B F A 96266 20-JUL-01 1 1
7 5 B F A 97763 20-JUL-01 1 1
2 B F A 96266 20-JUL-01 1 1
8 4 B F A 97763 20-JUL-01 1 1
1 B F A 96266 20-JUL-01 1 1

List of Controlfile Backups
===========================

CF Ckp SCN Ckp Time BS Key S #Pieces #Copies Tag
---------- --------- ------- - ------- ------- ---
97763 20-JUL-01 6 A 1 1
96266 20-JUL-01 3 A 1 1

RMAN> exit


The above shows the controlfile backups are in BS_KEY 3 and 6 after 2 backups.
List by file shows details for which file belongs to which backupset.


10. Accounting for all files in the target.

SQL> column name format a50
SQL> select file#, name from v$datafile;

FILE# NAME
---------- --------------------------------------------------
1 /proj/SME9i/prod9/data/system01.dbf
2 /proj/SME9i/prod9/data/undotbs01.dbf
3 /proj/SME9i/prod9/data/cwmlite01.dbf
4 /proj/SME9i/prod9/data/drsys01.dbf
5 /proj/SME9i/prod9/data/example01.dbf
6 /proj/SME9i/prod9/data/indx01.dbf
7 /proj/SME9i/prod9/data/tools01.dbf
8 /proj/SME9i/prod9/data/users01.dbf

8 rows selected.


What and where is file #9?

/proj/SME9i/prod9/data/control01.ctl

The controlfile autotbackup from the controlfile persistent configuration
does a backup of the controlfile when you issue a backup command.

BACKUP DATABASE
BACKUP TABLESPACE
BACKUP DATAFILE
BACKUP ARCHIVELOG

RMAN> report need backup;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 3
Report of files with less than 3 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
1 2 /proj/SME9i/prod9/data/system01.dbf
2 2 /proj/SME9i/prod9/data/undotbs01.dbf
3 2 /proj/SME9i/prod9/data/cwmlite01.dbf
4 2 /proj/SME9i/prod9/data/drsys01.dbf
5 2 /proj/SME9i/prod9/data/example01.dbf
6 2 /proj/SME9i/prod9/data/indx01.dbf
7 2 /proj/SME9i/prod9/data/tools01.dbf
8 2 /proj/SME9i/prod9/data/users01.dbf


In sqlplus run the team1 procedure again to generate more changes.

RMAN> @b_prod9_closed.rcv


Here is what I have in my backup directory.
Take a look at what's in yours.

[otcsol1]/proj/SME9i/backup> ls -g
ls -FC -g
total 1491252
-rw-r----- 1 udba 1511936 Jul 20 10:09 c-2094960375-20010720-00
-rw-r----- 1 udba 1511936 Jul 20 11:31 c-2094960375-20010720-01
-rw-r----- 1 udba 1511936 Jul 20 12:07 c-2094960375-20010720-02
-rw-r----- 1 udba 129495552 Jul 20 10:09 PROD9_closed_01cvcpvf_1_1
-rw-r----- 1 udba 122544640 Jul 20 10:09 PROD9_closed_02cvcpvf_1_1
-rw-r----- 1 udba 129909248 Jul 20 11:30 PROD9_closed_04cvcup9_1_1
-rw-r----- 1 udba 122544640 Jul 20 11:31 PROD9_closed_05cvcup9_1_1
-rw-r----- 1 udba 129909248 Jul 20 12:06 PROD9_closed_07cvd0ua_1_1
-rw-r----- 1 udba 122544640 Jul 20 12:07 PROD9_closed_08cvd0ua_1_1
-rw-r----- 1 udba 1503232 Jul 20 12:07 snapf_prod9.f


Since the retention policy is redundancy 3, let's see what needs backup or
if we've satisfied the policy.

RMAN> report need backup;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 3
Report of files with less than 3 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 3
no obsolete backups found

RMAN>

Nothing to obsolete yet. Only 3 backups.

Run team# loop.sql to generate more changes.


Run sqlplus and verify the control file v$ backup information.

select TYPE, RECORDS_TOTAL, RECORDS_USED
from v$controlfile_record_section
where type like '%BACKUP%';

TYPE RECORDS_TOTAL RECORDS_USED
------------------ ------------- ------------
BACKUP SET 101 12
BACKUP PIECE 204 12
BACKUP DATAFILE 210 36
BACKUP REDOLOG 53 0
BACKUP CORRUPTION 185 0

[otcsol1]/proj/SME9i/backup> ls -g
ls -FC -g
total 1987620
-rw-r----- 1 udba 1511936 Jul 20 10:09 c-2094960375-20010720-00
-rw-r----- 1 udba 1511936 Jul 20 11:31 c-2094960375-20010720-01
-rw-r----- 1 udba 1511936 Jul 20 12:07 c-2094960375-20010720-02
-rw-r----- 1 udba 1511936 Jul 20 12:22 c-2094960375-20010720-03
-rw-r----- 1 udba 129495552 Jul 20 10:09 PROD9_closed_01cvcpvf_1_1
-rw-r----- 1 udba 122544640 Jul 20 10:09 PROD9_closed_02cvcpvf_1_1
-rw-r----- 1 udba 129909248 Jul 20 11:30 PROD9_closed_04cvcup9_1_1
-rw-r----- 1 udba 122544640 Jul 20 11:31 PROD9_closed_05cvcup9_1_1
-rw-r----- 1 udba 129909248 Jul 20 12:06 PROD9_closed_07cvd0ua_1_1
-rw-r----- 1 udba 122544640 Jul 20 12:07 PROD9_closed_08cvd0ua_1_1
-rw-r----- 1 udba 129909248 Jul 20 12:22 PROD9_closed_0acvd1ps_1_1
-rw-r----- 1 udba 122544640 Jul 20 12:22 PROD9_closed_0bcvd1ps_1_1
-rw-r----- 1 udba 1503232 Jul 20 12:22 snapf_prod9.f


SQL> set pagesize 60
SQL> select RECID, STAMP, COMPLETION_TIME, INCREMENTAL_LEVEL from v$backup_set;

RECID STAMP COMPLETION_TIME INCREMENTAL_LEVEL
---------- ---------- --------------- -----------------
1 435578964 20-JUL-01 0
2 435578982 20-JUL-01 0
3 435578987 20-JUL-01
4 435583853 20-JUL-01 0
5 435583874 20-JUL-01 0
6 435583877 20-JUL-01
7 435586019 20-JUL-01 0
8 435586027 20-JUL-01 0
9 435586036 20-JUL-01
10 435586934 20-JUL-01 0
11 435586942 20-JUL-01 0
12 435586945 20-JUL-01

12 rows selected.


SQL> set pagesize 60
SQL> column handle format a32
SQL> column tag format a18
SQL> select RECID,SET_STAMP, TAG, STATUS, HANDLE from v$backup_piece
order by SET_STAMP;

RECID SET_STAMP TAG S HANDLE
---------- ---------- ------------------ - --------------------------------
1 435578863 A /proj/SME9i/backup/PROD9_closed_
01cvcpvf_1_1 <<= DB Backupset

2 435578863 A /proj/SME9i/backup/PROD9_closed_
02cvcpvf_1_1 <<= DB Backupset

3 435578986 A /proj/SME9i/backup/c-2094960375-
20010720-00 <<= Controlfile backup

4 435583785 A /proj/SME9i/backup/PROD9_closed_
04cvcup9_1_1 <<= DB Backupset

5 435583785 A /proj/SME9i/backup/PROD9_closed_
05cvcup9_1_1 <<= DB Backupset

6 435583876 A /proj/SME9i/backup/c-2094960375-
20010720-01 <<= Controlfile backup

7 435585994 PROD9_CLOSED_LVL0 A /proj/SME9i/backup/PROD9_closed_
07cvd0ua_1_1 <<= DB Backupset

8 435585994 PROD9_CLOSED_LVL0 A /proj/SME9i/backup/PROD9_closed_
08cvd0ua_1_1 <<= DB Backupset

9 435586035 A /proj/SME9i/backup/c-2094960375-
20010720-02 <<= Controlfile backup

10 435586876 PROD9_CLOSED_LVL0 A /proj/SME9i/backup/PROD9_closed_
0acvd1ps_1_1 <<= DB Backupset

11 435586876 PROD9_CLOSED_LVL0 A /proj/SME9i/backup/PROD9_closed_
0bcvd1ps_1_1 <<= DB Backupset

12 435586944 A /proj/SME9i/backup/c-2094960375-
20010720-03 <<= Controlfile backup


12 rows selected.


What do we have in the backup directory?

[otcsol1]/proj/SME9i/backup> ls -g
ls -FC -g
total 1987620
-rw-r----- 1 udba 1511936 Jul 20 10:09 c-2094960375-20010720-00
-rw-r----- 1 udba 1511936 Jul 20 11:31 c-2094960375-20010720-01
-rw-r----- 1 udba 1511936 Jul 20 12:07 c-2094960375-20010720-02
-rw-r----- 1 udba 1511936 Jul 20 12:22 c-2094960375-20010720-03
-rw-r----- 1 udba 129495552 Jul 20 10:09 PROD9_closed_01cvcpvf_1_1
-rw-r----- 1 udba 122544640 Jul 20 10:09 PROD9_closed_02cvcpvf_1_1
-rw-r----- 1 udba 129909248 Jul 20 11:30 PROD9_closed_04cvcup9_1_1
-rw-r----- 1 udba 122544640 Jul 20 11:31 PROD9_closed_05cvcup9_1_1
-rw-r----- 1 udba 129909248 Jul 20 12:06 PROD9_closed_07cvd0ua_1_1
-rw-r----- 1 udba 122544640 Jul 20 12:07 PROD9_closed_08cvd0ua_1_1
-rw-r----- 1 udba 129909248 Jul 20 12:22 PROD9_closed_0acvd1ps_1_1
-rw-r----- 1 udba 122544640 Jul 20 12:22 PROD9_closed_0bcvd1ps_1_1
-rw-r----- 1 udba 1503232 Jul 20 12:22 snapf_prod9.f
[otcsol1]/proj/SME9i/backup>


What backups obsolete now?

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 3
Report of obsolete backups and copies
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set 1 20-JUL-01
Backup Piece 1 20-JUL-01 /proj/SME9i/backup/PROD9_closed_0
1cvcpvf_1_1
Backup Set 2 20-JUL-01
Backup Piece 2 20-JUL-01 /proj/SME9i/backup/PROD9_closed_0
2cvcpvf_1_1
Backup Set 3 20-JUL-01
Backup Piece 3 20-JUL-01 /proj/SME9i/backup/c-2094960375-2
0010720-00

We now have an obsolete controlfile and 2 bakup sets. These are the 1st backups
we took. Backupsets 1, 2, and 3.

To remove the backupsets that exceed the retention policy set run "delete obsolete"
command and you will be prompted for Y/N to remove the physical backupsets and pieces.


RMAN> delete obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 3
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=9 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=10 devtype=DISK
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set 1 20-JUL-01
Backup Piece 1 20-JUL-01 /proj/SME9i/backup/PROD9_closed_0
1cvcpvf_1_1
Backup Set 2 20-JUL-01
Backup Piece 2 20-JUL-01 /proj/SME9i/backup/PROD9_closed_0
2cvcpvf_1_1
Backup Set 3 20-JUL-01
Backup Piece 3 20-JUL-01 /proj/SME9i/backup/c-2094960375-2
0010720-00

Do you really want to delete the above objects (enter YES or NO)? y
deleted backup piece
backup piece handle=/proj/SME9i/backup/PROD9_closed_01cvcpvf_1_1 recid=1 stamp=4
35578863
deleted backup piece
backup piece handle=/proj/SME9i/backup/PROD9_closed_02cvcpvf_1_1 recid=2 stamp=4
35578863
deleted backup piece
backup piece handle=/proj/SME9i/backup/c-2094960375-20010720-00 recid=3 stamp=43
5578987

The backups are removed from the backup directory and the controlfile.

[otcsol1]/proj/SME9i/backup> ls -g
ls -FC -g
total 1492068
-rw-r----- 1 udba 1511936 Jul 20 11:31 c-2094960375-20010720-01
-rw-r----- 1 udba 1511936 Jul 20 12:07 c-2094960375-20010720-02
-rw-r----- 1 udba 1511936 Jul 20 12:22 c-2094960375-20010720-03
-rw-r----- 1 udba 129909248 Jul 20 11:30 PROD9_closed_04cvcup9_1_1
-rw-r----- 1 udba 122544640 Jul 20 11:31 PROD9_closed_05cvcup9_1_1
-rw-r----- 1 udba 129909248 Jul 20 12:06 PROD9_closed_07cvd0ua_1_1
-rw-r----- 1 udba 122544640 Jul 20 12:07 PROD9_closed_08cvd0ua_1_1
-rw-r----- 1 udba 129909248 Jul 20 12:22 PROD9_closed_0acvd1ps_1_1
-rw-r----- 1 udba 122544640 Jul 20 12:22 PROD9_closed_0bcvd1ps_1_1
-rw-r----- 1 udba 1503232 Jul 20 12:22 snapf_prod9.f



There were 13 backup objects before delete obsolete and there are now 10.
The 3 backup sets are removed.


Looking at it in RMAN now:

RMAN> list backup by file;


List of Datafile Backups
========================

File Key TY LV S Ckp SCN Ckp Time #Pieces #Copies Tag
---- ------- - -- - ---------- --------- ------- ------- ---
1 11 B F A 100734 20-JUL-01 1 1 PROD9_CLOSED_LVL0
8 B F A 99238 20-JUL-01 1 1 PROD9_CLOSED_LVL0
5 B F A 97763 20-JUL-01 1 1
2 10 B F A 100734 20-JUL-01 1 1 PROD9_CLOSED_LVL0
7 B F A 99238 20-JUL-01 1 1 PROD9_CLOSED_LVL0
4 B F A 97763 20-JUL-01 1 1
3 10 B F A 100734 20-JUL-01 1 1 PROD9_CLOSED_LVL0
7 B F A 99238 20-JUL-01 1 1 PROD9_CLOSED_LVL0
4 B F A 97763 20-JUL-01 1 1
4 11 B F A 100734 20-JUL-01 1 1 PROD9_CLOSED_LVL0
8 B F A 99238 20-JUL-01 1 1 PROD9_CLOSED_LVL0
5 B F A 97763 20-JUL-01 1 1
5 11 B F A 100734 20-JUL-01 1 1 PROD9_CLOSED_LVL0
8 B F A 99238 20-JUL-01 1 1 PROD9_CLOSED_LVL0
5 B F A 97763 20-JUL-01 1 1
6 10 B F A 100734 20-JUL-01 1 1 PROD9_CLOSED_LVL0
7 B F A 99238 20-JUL-01 1 1 PROD9_CLOSED_LVL0
4 B F A 97763 20-JUL-01 1 1
7 11 B F A 100734 20-JUL-01 1 1 PROD9_CLOSED_LVL0
8 B F A 99238 20-JUL-01 1 1 PROD9_CLOSED_LVL0
5 B F A 97763 20-JUL-01 1 1
8 10 B F A 100734 20-JUL-01 1 1 PROD9_CLOSED_LVL0
7 B F A 99238 20-JUL-01 1 1 PROD9_CLOSED_LVL0
4 B F A 97763 20-JUL-01 1 1

List of Controlfile Backups
===========================

CF Ckp SCN Ckp Time BS Key S #Pieces #Copies Tag
---------- --------- ------- - ------- ------- ---
100734 20-JUL-01 12 A 1 1
99238 20-JUL-01 9 A 1 1
97763 20-JUL-01 6 A 1 1


The records are no longer available (RECID 1, 2, and 3) in RMAN.

set pagesize 60
column handle format a32
column tag format a18
select RECID,SET_STAMP, TAG, STATUS, HANDLE from v$backup_piece
order by SET_STAMP;

RECID SET_STAMP TAG S HANDLE
---------- ---------- ------------------ - --------------------------------
1 435578863 D /proj/SME9i/backup/PROD9_closed_
01cvcpvf_1_1

2 435578863 D /proj/SME9i/backup/PROD9_closed_
02cvcpvf_1_1

3 435578986 D /proj/SME9i/backup/c-2094960375-
20010720-00

4 435583785 A /proj/SME9i/backup/PROD9_closed_
04cvcup9_1_1

5 435583785 A /proj/SME9i/backup/PROD9_closed_
05cvcup9_1_1

6 435583876 A /proj/SME9i/backup/c-2094960375-
20010720-01

7 435585994 PROD9_CLOSED_LVL0 A /proj/SME9i/backup/PROD9_closed_
07cvd0ua_1_1

8 435585994 PROD9_CLOSED_LVL0 A /proj/SME9i/backup/PROD9_closed_
08cvd0ua_1_1

9 435586035 A /proj/SME9i/backup/c-2094960375-
20010720-02

10 435586876 PROD9_CLOSED_LVL0 A /proj/SME9i/backup/PROD9_closed_
0acvd1ps_1_1

11 435586876 PROD9_CLOSED_LVL0 A /proj/SME9i/backup/PROD9_closed_
0bcvd1ps_1_1

12 435586944 A /proj/SME9i/backup/c-2094960375-
20010720-03


12 rows selected.


select TYPE, RECORDS_TOTAL, RECORDS_USED
from v$controlfile_record_section
where type like '%BACKUP%';

TYPE RECORDS_TOTAL RECORDS_USED
------------------ ------------- ------------
BACKUP SET 101 12
BACKUP PIECE 204 12
BACKUP DATAFILE 210 36
BACKUP REDOLOG 53 0
BACKUP CORRUPTION 185 0

Because we are maintaining the backups using a set backup policy the records_used
will increase/decrease as you manage your backups.

2. Restore of a database w/o a catalog and noachivelog mode.


Restoring using the backups you've taken. Now the fun begins. First we'll remove
the database to simulate our own disaster to recover from.


[otcsol1]/proj/SME9i/prod9/rmanlab> cd ../data
[otcsol1]/proj/SME9i/prod9/data> ls -ltr
total 1320832
-rw-r----- 1 usupport udba 41947136 Jul 12 14:49 temp01.dbf
-rw-r----- 1 usupport udba 1049088 Jul 20 13:59 redo03.log
-rw-r----- 1 usupport udba 1049088 Jul 20 13:59 redo02.log
-rw-r----- 1 usupport udba 26218496 Jul 20 14:00 users01.dbf
-rw-r----- 1 usupport udba 209719296 Jul 20 14:00 undotbs01.dbf
-rw-r----- 1 usupport udba 10489856 Jul 20 14:00 tools01.dbf
-rw-r----- 1 usupport udba 340791296 Jul 20 14:00 system01.dbf
-rw-r----- 1 usupport udba 1049088 Jul 20 14:00 redo01.log
-rw-r----- 1 usupport udba 26218496 Jul 20 14:00 indx01.dbf
-rw-r----- 1 usupport udba 10489856 Jul 20 14:00 example01.dbf
-rw-r----- 1 usupport udba 20975616 Jul 20 14:00 drsys01.dbf
-rw-r----- 1 usupport udba 20975616 Jul 20 14:00 cwmlite01.dbf
-rw-r----- 1 usupport udba 1503232 Jul 20 14:00 control03.ctl
-rw-r----- 1 usupport udba 1503232 Jul 20 14:00 control02.ctl
-rw-r----- 1 usupport udba 1503232 Jul 20 14:00 control01.ctl


[otcsol1]/proj/SME9i/prod9/data> rm *
rm: remove control01.ctl (yes/no)? y
rm: remove control02.ctl (yes/no)? y
rm: remove control03.ctl (yes/no)? y
rm: remove cwmlite01.dbf (yes/no)? y
rm: remove drsys01.dbf (yes/no)? y
rm: remove example01.dbf (yes/no)? y
rm: remove indx01.dbf (yes/no)? y
rm: remove redo01.log (yes/no)? y
rm: remove redo02.log (yes/no)? y
rm: remove redo03.log (yes/no)? y
rm: remove system01.dbf (yes/no)? y
rm: remove temp01.dbf (yes/no)? y
rm: remove tools01.dbf (yes/no)? y
rm: remove undotbs01.dbf (yes/no)? y
rm: remove users01.dbf (yes/no)? y
[otcsol1]/proj/SME9i/prod9/data> y

[otcsol1]/proj/SME9i/prod9/data> ls -la
total 4
drwxr-xr-x 2 usupport udba 512 Jul 20 14:20 ./
drwxr-xr-x 11 usupport udba 512 Jul 19 13:43 ../


OK it's all gone now and you have to bring it back with RMAN...


Note: Make sure you set the NLS_LANG on the target for the recovery session at the
Unix prompt if the database is not using the US7ASCII characterset. Ex.
whatever your database characterset is you are restoring.

setenv NLS_LANG AMERICAN_AMERICA.WE8ISO8859P1
or
setenv NLS_LANG AMERICAN_AMERICA.UTF8


To recover the database using an autobackup of the control file without a recovery
catalog:

1. Use SQL*Plus to start, but not mount, the database. For example, run:

SQL> STARTUP NOMOUNT


2. Start RMAN but do not connect to the target database:

% rman

RMAN>


3. Set the database identifier for the target database with SET DBID. RMAN
displays the DBID whenever you connect to the target. You can also get it
by running LIST or by querying the catalog (refer to "Restoring When
Multiple Databases Share the same Name: Example"). For example, run:

SET DBID 2094960375;

Note: You can use log files to determine the DBID also. Everytime RMAN connects
to the database the DBID is displayed if the database is open or mounted.

4. Connect to the target database. For example, run:

CONNECT TARGET

5. Restore the backup control file, then perform recovery. Do the following:

a. Optionally, specify the most recent backup time stamp that
RMAN can use when searching for a control file autobackup to restore.

b. If a non-default format was used to create the control file, then
specify a non-default format for the restore of the control file.

c. If the channel that created the control file autobackup was device
type sbt, then you must allocate one or more sbt channels. Because no
repository is available, you cannot use automatic channels. If the
autobackup was created on a disk channel, however, then you do not
need to manually allocate a channel.

d. Restore the autobackup of the control file, optionally setting the
maximum number of days backward that RMAN can search (up to 366) and
the initial sequence number that it should use in its search for the
first day.

e. Mount the database. Note that because the repository is now
available, any automatic channels that you configured are also
available.

f. If the online logs are inaccessible, then restore and recover the
database as described in "Performing Incomplete Restore and
Recovery". You must terminate recovery by setting the UNTIL clause to
a time, log sequence, or SCN before the online redo logs. If the
online logs are usable, then restore and recover the database as
described in "Performing Complete Restore and Recovery".


In this example, the online redo logs have been lost. This example limits
the restore of the control file autobackup, then performs recovery of the
database to log sequence 13243, which is the most recent archived log:

# manually allocate one or more channels

RUN
{
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE disk TO
'/proj/SME9i/backup/%F';
ALLOCATE CHANNEL d1 DEVICE TYPE disk;
RESTORE CONTROLFILE FROM AUTOBACKUP
MAXSEQ 5 # start at sequence 5 and count down
MAXDAYS 5; # start at UNTIL TIME and search back 5 days
MOUNT DATABASE;
}

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

allocated channel: d1
channel d1: sid=9 devtype=DISK

Starting restore at 20-JUL-01

channel d1, looking for controlfile autobackup on day: 20010720
channel d1, controlfile autobackup found: /proj/SME9i/backup/c-2094960375-200107
20-03
channel d1, controlfile autobackup restore complete
replicating controlfile
input filename=/proj/SME9i/prod9/data/control01.ctl
output filename=/proj/SME9i/prod9/data/control02.ctl
output filename=/proj/SME9i/prod9/data/control03.ctl
Finished restore at 20-JUL-01

database mounted
released channel: d1

r_prod9_db.log:

RMAN> run {
2> restore database;
3> recover database noredo;
4> alter database open resetlogs;
5> }


Backup the database after resetlogs.


A new 3rd autocontrolfile backup is created. We used the old -03 to recover with
so backup -03 never really existed since the current controlfile now would have
been backup -03 of the controlfile.

Starting Control File Autobackup at 20-JUL-01
piece handle=/proj/SME9i/backup/c-2094960375-20010720-03 comment=NONE
Finished Control File Autobackup at 20-JUL-01


[otcsol1]/proj/SME9i/backup> ls -g
ls -FC -g
total 1985460
-rw-r----- 1 udba 1511936 Jul 20 11:31 c-2094960375-20010720-01
-rw-r----- 1 udba 1511936 Jul 20 12:07 c-2094960375-20010720-02
-rw-r----- 1 udba 1511936 Jul 20 16:18 c-2094960375-20010720-03
-rw-r----- 1 udba 129909248 Jul 20 11:30 PROD9_closed_04cvcup9_1_1
-rw-r----- 1 udba 122544640 Jul 20 11:31 PROD9_closed_05cvcup9_1_1
-rw-r----- 1 udba 129909248 Jul 20 12:06 PROD9_closed_07cvd0ua_1_1
-rw-r----- 1 udba 122544640 Jul 20 12:07 PROD9_closed_08cvd0ua_1_1
-rw-r----- 1 udba 129909248 Jul 20 12:22 PROD9_closed_0acvd1ps_1_1
-rw-r----- 1 udba 122544640 Jul 20 12:22 PROD9_closed_0bcvd1ps_1_1
-rw-r----- 1 udba 129909248 Jul 20 16:18 PROD9_closed_0dcvdfjd_1_1
-rw-r----- 1 udba 122544640 Jul 20 16:18 PROD9_closed_0ecvdfjd_1_1
-rw-r----- 1 udba 1503232 Jul 20 12:22 snapf_prod9.f
[otcsol1]/proj/SME9i/backup> ls -g
ls -FC -g
total 992724
-rw-r----- 1 udba 1511936 Jul 20 16:18 c-2094960375-20010720-03
-rw-r----- 1 udba 129909248 Jul 20 12:22 PROD9_closed_0acvd1ps_1_1
-rw-r----- 1 udba 122544640 Jul 20 12:22 PROD9_closed_0bcvd1ps_1_1
-rw-r----- 1 udba 129909248 Jul 20 16:18 PROD9_closed_0dcvdfjd_1_1
-rw-r----- 1 udba 122544640 Jul 20 16:18 PROD9_closed_0ecvdfjd_1_1
-rw-r----- 1 udba 1503232 Jul 20 12:22 snapf_prod9.f


RMAN> report need backup;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 3
Report of files with less than 3 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
1 2 /proj/SME9i/prod9/data/system01.dbf
2 2 /proj/SME9i/prod9/data/undotbs01.dbf
3 2 /proj/SME9i/prod9/data/cwmlite01.dbf
4 2 /proj/SME9i/prod9/data/drsys01.dbf
5 2 /proj/SME9i/prod9/data/example01.dbf
6 2 /proj/SME9i/prod9/data/indx01.dbf
7 2 /proj/SME9i/prod9/data/tools01.dbf
8 2 /proj/SME9i/prod9/data/users01.dbf


select TYPE, RECORDS_TOTAL, RECORDS_USED
from v$controlfile_record_section
where type like '%BACKUP%';

TYPE RECORDS_TOTAL RECORDS_USED
------------------ ------------- ------------
BACKUP SET 101 14
BACKUP PIECE 204 14
BACKUP DATAFILE 210 44
BACKUP REDOLOG 53 0
BACKUP CORRUPTION 185 0


Summary:

In this lesson you should have learned how to configure RMAN to use the target
database controlfile as the recovery catalog using autocontrolfile backups. You
also learned how to created a persistent backup configuration for reuse. Then
you created level 0 and level 1 consistent backup using RMAN. After simulating
your own disaster you successfully restored and recovered the database using
the backups taken with RMAN.



RELATED DOCUMENTS
-----------------

Oracle9i Recovery Manager Users Guide and Reference Manuals.

Master Note for Data Guard

Master Note for Data Guard [ID 1101938.1]
Modified 12-AUG-2010 Type BULLETIN Status PUBLISHED

In this Document
Purpose
Scope and Application
Master Note for Data Guard
Setup and Configuration of Data Guard

Patching, Upgrade and Migration
Managing Data Guard - Physical Standby
Managing Data Guard - Logical Standby
Switchover and Failover
Performance
Troubleshooting
Dataguard and RAC
Miscellaneous

Applies to:
Oracle Database Products > Oracle Database > High Availability
Information in this document applies to any platform.
Purpose
This Master Note is intended to provide an index and references to the most frequently used My Oracle Support Notes with respect to Data Guard implementations. This Master Note is subdivided into categories to allow for easy access and reference to notes that are applicable to your area of interest, within the Data Guard.
Scope and Application

This is a master note for the data guard content that can be accessed from one page
Master Note for Data Guard

This note applies to the following versions of these products:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.x

Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.1.0.x

Oracle Server - Enterprise Edition - Version: 11.2.0.1 to 11.2.0.x
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 10.2.0.x

Setup and Configuration of Data Guard

Note.343424.1 Creating a 10gr2 Data Guard Physical Standby database with Real-Time apply

Note.380449.1 Creating a RAC Physical Standby for a RAC Primary

Note.387339.1 Creating a Single Instance Physical Standby for a RAC Primary

Creating a RAC Logical Standby Database for a RAC Primary Database

Client Failover setup in Data Guard Configurations

Data Guard Fast-Start Failover - Best Practices

Setup Multiple Standby Databases - Best Practices



Patching, Upgrade and Migration

Note.278641.1 Applying Patchset with a 10g Physical Standby in Place

Note.278521.1 Upgrading to 10g with a Physical Standby in Place

Note 437276.1 Upgrading Oracle Database with a Logical Standby Database In Place

Note 756671.1 Oracle Recommended Patches -- Oracle Database

Rolling Database Upgrades Using Data Guard SQL Apply

Minimal Downtime Migration to ASM

Oracle 10g Upgrade Companion - for upgrading from Oracle9i to Oracle10g

Managing Data Guard - Physical Standby


Adding a Datafile or Creating a Tablespace

Dropping Tablespaces and Deleting Datafiles

Renaming a Datafile in the Primary Database

Recovering Through the OPEN RESETLOGS Statement

Opening a Physical Standby Database for Read-Only Access

Note.382686.1 CAN DATAGUARD HAVE DIFFERENT SCHEMA ACCOUNT STATUS FOR PRIMARY AND STANDBY DB ?

Note. 394815.1 How to Rename the Datafiles When using OMF in Standby



Managing Data Guard - Logical

Using DBMS_LOGSTDBY.SKIP to Prevent Changes to Specific Schema Objects

Modifying a Logical Standby Database



Switchover and Failover

Note.414043.1 Transitions for Data Guard Configurations Using Mixed Oracle Binaries

Note.738642.1 Step by Step Guide on How To Reinstate Failed Primary Database into Physical Standby

Data Guard Switchover and Failover - Best Practice



Performance

Tuning a Logical Standby Database

Troubleshooting

Note 814417.1 Information to gather and upload for Dataguard related problems

Note.241374.1 Script to Collect Data Guard Primary Site Diagnostic Information

Note.241438.1 Script to Collect Data Guard Physical Standby Diagnostic Information

Note.241512.1 Script to Collect Data Guard Logical Standby Diagnostic Information



Dataguard and RAC

Note.370434.1 How to make CRS aware of the role change in Data Guard environment?



Miscelleanous

Note 386830.1 ALERT: Bug 5380055 Corrupts Index Blocks Especially in Data Guard Configurations




Common Dataguard errors

ORA-16057 DGID from server not in Data Guard configuration

ORA-12154 TNS No Listener





Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.1.0.x


Setup and Configuration of Data Guard



Creating a Physical Standby Database

Creating a Logical Standby Database

Patching, Upgrade and Migration



Rolling Database Upgrades using Data Guard SQL Apply

Rolling Database Upgrades for Physical Standby Databases using Transient Logical Standby 11g

Oracle 11g Upgrade Companion - for upgrading from Oracle9i to Oracle11g


Managing Data Guard - Physical Standby


Redo Apply Best Practices


Managing Data Guard - Logical Standby


SQL Apply Best Practices

Monitoring a Logical Standby Database


Switchover and Failover



Role Transitions Involving Physical Standby Databases

Role Transitions Involving Logical Standby Databases

Using Flashback Database After a Role Transition
Performance



Tuning a Logical Standby Database


Troubleshooting



Note 814417.1 Information to gather and upload for Dataguard related problems

Note.241374.1 Script to Collect Data Guard Primary Site Diagnostic Information

Note.241438.1 Script to Collect Data Guard Physical Standby Diagnostic Information

Note.241512.1 Script to Collect Data Guard Logical Standby Diagnostic Information

Setting Archive Tracing
Dataguard and RAC


Data Guard 11g Installation and Configuration Best Practices on Oracle RAC


Miscellaneous


Active Data Guard 11g Best Practices

Configuring Oracle Business Intelligence Enterprise Edition Server with Active Data Guard



Common Data Guard errors
Using My Oracle Support Effectively

* Note 374370.1 New Customers Start Here
* Note 736737.1 My Oracle Support - The Next Generation Support Platform
* Note 730283.1 Get the most out of My Oracle Support
* Note 747242.5 My Oracle Support Configuration Management FAQ
* Note 868955.1 My Oracle Support Health Checks Catalog
* Note 166650.1 Working Effectively With Global Customer Support
* Note 199389.1 Escalating Service Requests with Oracle Support Services

How to Kill Session Causing Concurrent Manager to Hang

Problem Description
-------------------

A session is causing the concurrent manager to hang. Jobs are pending since
this session is unprocessed by a user. This article describes how to identify
and kill the problem session.


Additional Search Words
-----------------------

select alter sql*plus


Solution Description
--------------------

Perform the following steps to kill the session:

1. Log into SQL*Plus as sys.

2. To identify the problem session, issue the SQL*Plus statement below:

SQL> select session_id, type, mode_held from dba_locks;
(the type should be that of )

3. To kill the problem session once identified, issue the following
SQL*Plus statement:

SQL> alter system kill session ;

The concurrent manager is now free; there is no need to change its status.

When you shutdown the managers with the abort option

Applies to:
Oracle Application Object Library - Version: 11.5.9 to 11.5.10.2
Information in this document applies to any platform.
Goal

When you shutdown the managers with the abort option does it submit a concurrent request that is run straight away with a high priority?

Similarly you shutdown with the normal option, does this job have to wait on an available manager or does it run straight away?
Solution
If you shutdown the concurrent managers with the ABORT option ( using CONCSUB), it will run a concurrent request with a priority of -75 to terminate all requests and shutdown the managers. This is the most important priority for a concurrent request and means that the ABORT request will be run before all other concurrent requests .

If you shutdown the concurrent managers normally ( using startmgr), it will run a concurrent manager with a priority of 0. This is very high priority for concurrent requests.
By default , all the concurrent request have a priority of 50 .
It means that the SHUTDOWN request will be run before all other concurrent requests except the ABORT request .

( -75 is a higher priority than 0 , 0 is a higher priority than 50 ) .

How to create a custom concurrent manager

Checked for relevance on 07-JAN-2010
Applications Install 11.5.7 to 12.1.1

* goal: How to create a custom concurrent manager
* fact: Oracle Application Object Library



fix:

1. Navigate to Concurrent / Manager / Define.
2. Manager Field: Custom Manager.
3. Short Name: CUSTOM.
4. Type: Concurrent Manager.
5. Program Library: FNDLIBR.
6. Enter desired cache.
7. Work Shifts: Standard.
8. Enter number of Processes.
9. Provide Specialization Rules (you can include or exclude program, id, user,
types or combination).
10. Save.
11. Navigate to Concurrent / Manager / Administer.
12. Activate the Custom Manager.

How does one bypass the approval step of 'Forgot Password' notifications?

How does one bypass the approval step of 'Forgot Password' notifications?


Solution

The password approval process cannot be by-passed in rel 11.5.10.2 as a seeded flow.

To by-pass this, the following public API can be used:

fnd_user_pkg.changepassword(username => rec_req_header.user_name
,newpassword=>l_pwd);

This functionality is available in 12.1.X where password changes do not need to be approved.

FSG Transfer Gives ORA-01000: Maximum Open Cursors Exceeded

Problem Description
-------------------
The FSG Transfer program is completing in error with the following message:

ORA-01000: Maximum Open Cursors Exceeded

Is there a script that can be run to determine how many open cursors
exist and what is causing the open cursors?

Solution Description
--------------------
In order to determine how many SQL statements have been parsed AND have an
open cursor use the following query:

SELECT s.osuser, s.username, s.sid, count(*)
FROM v$session s, v$open_cursor oc
WHERE s.saddr = oc.saddr AND
s.username IS NOT NULL
GROUP BY s.osuser, s.username, s.sid;

If there is one user with a large number of cursors open, the following query
can be used to determine the leading edge of the query in question:

SELECT s.username, SUBSTR(oc.sql_text,1,40)
FROM v$session s, v$open_cursor oc
WHERE s.saddr = oc.saddr AND
s.username = ;

If all of the sessions are connected with the same username, then change the
last line of the the above query to the following:

s.sid = ;


Search Words
------------
RGRXFR
ORA-00604
ORA-06512

How to Setup VNC Server with Clipboard Support on RHEL/OEL

How to Setup VNC Server with Clipboard Support on RHEL/OEL [ID 735767.1]
Modified 04-MAR-2009 Type HOWTO Status PUBLISHED

In this Document
Goal
Solution
1. Installation
2. System Configuration
3. Password
4. User Configuration
6. Test
7. Mouse buttons
References

Applies to:
Linux Kernel - Version: 4.4
Linux x86-64
Goal
This article describes how to setup a VNC (Virtual Network Computing ) server with clipboard support on Red Hat Enterprise Linux and Oracle Enterprise Linux.
Solution
1. Installation
Check if vnc-server rpm package is installed on your Linux OS:

rpm -q vnc-server

If it is not there, try to find vnc-server rpm package on Linux OS installation ISO/media and install it:

rpm -ivh

If you have Oracle Enterprise Linux support license and up2date configured, you can get vnc-server from ULN (Unbreakable Linux Network):

up2date -i vnc-server

Please consult ULN and ULN FAQ for how to update your Linux via up2date.

2. System Configuration
To automate the startup of vncserver after boot, the /etc/sysconfig/vncservers must be configured. The syntax of configuration file is:
# VNCSERVERS=":"
# VNCSERVERARGS[]="-geometry -nolisten tcp -nohttpd"

The parameter "VNCSERVERS" indicates a vnc server is started as user on display ":". should be an existing user in the system. You can specify multiple userids to start servers, for example:

VNCSERVERS="1:root 10:oracle"

In the above case, There will be 2 VNC servers on display ":1" and ":10".
By default, the following ports will be opened:

port 5801 for "root", 5810 for "oracle":
- for VNC client connection over HTTP protocol, using a java-enabled browser.

port 5901 for "root", 5910 for "oracle":
- for VNC client connection over RFB protocol (e.g. vncviewer).

port 6001 for "root" 6010 for "oracle":
- for X applications to connect to the VNC server

The parameter "VNCSERVERARGS" specifies the session properties, for full details see the online documentation

man Xvnc

With RHEL5/OEL5, the supplied /etc/sysconfig/vncservers documents how to restrict sessions:

# Use "-nolisten tcp" to prevent X connections to your VNC server via TCP.

# Use "-nohttpd" to prevent web-based VNC clients connecting.

# Use "-localhost" to prevent remote VNC clients connecting except when
# doing so through a secure tunnel. See the "-via" option in the
# `man vncviewer' manual page.

The vncserver is a Linux service, you manage it with "chkconfig" and "service" commands like other services. Refer to Note 551711.1 for enable/disable/start/stop operations. However, further configuration should be done prior to staring the service.
3. Password
Set the vnc access password:

# vncpasswd
Password:
Verify:

Please note the password must be set by each userid named on the "VNCSERVERS=" parameter. If this is not done, the service for the userid will not be started.

The vncpasswd command will also cause directory $HOME/.vnc to be created and populated with required files. Session log files will also be created here.
4. User Configuration
The vncserver service will start a VNC server "Xvnc", which runs script $HOME/.vnc/xstartup. This calls "vncconfig" which provides clipboard support. See the online manual for more information:

man vncconfig

Default xstartup script:
# cat xstartup
#!/bin/sh

# Uncomment the following two lines for normal desktop:
# unset SESSION_MANAGER
# exec /etc/X11/xinit/xinitrc

[ -x /etc/vnc/xstartup ] && exec /etc/vnc/xstartup
[ -r $HOME/.Xresources ] && xrdb $HOME/.Xresources
xsetroot -solid grey
vncconfig -iconic &
xterm -geometry 80x24+10+10 -ls -title "$VNCDESKTOP Desktop" &
twm &

If you follow the instructions "Uncomment the following two lines for normal desktop:" you will lose clipboard support. This is because "/etc/X11/xinit/xinitrc" will invoke “/etc/X11/xinit/Xclients” to setup Gnome or KDE, but command "exec" will terminate the current shell after executing, thus subsequent commands will not be run.

The "twm &" command will invoke a basic window manager. For a more advanced window manager, change the line to one of your choice, e.g.:

# cat xstartup
#!/bin/sh

# Uncomment the following two lines for normal desktop:
# unset SESSION_MANAGER
# exec /etc/X11/xinit/xinitrc

[ -x /etc/vnc/xstartup ] && exec /etc/vnc/xstartup
[ -r $HOME/.Xresources ] && xrdb $HOME/.Xresources
xsetroot -solid grey
vncconfig -iconic &
xterm -geometry 80x24+10+10 -ls -title "$VNCDESKTOP Desktop" &
#twm &
gnome-session &
# or "startkde &" for KDE desktop

6. Test
Start the service:

service vncserver start


The service "vncserver" reads /etc/sysconfig/vncservers to find all allowed users and their parameters and then calls /usr/lib/vncserver. This perl script program reads $HOME/.vnc/xstartup to allow you to change the applications run at Xvnc's startup.

Any user also can manually invoke "vncserver" command to start a vnc server on next available port. If vncpasswd has not been run, it will be called.

To access your vnc server, you can use the vncviewer utility

vncviewer :1

Or point your java-enabled browser at port 58xx on the vncserver.


7. Mouse buttons
If you only have a two-button mouse, you may find you are unable to use some Xwindow mouse buttons.
Xwindow provides the ability to change the mapping of the mouse buttons with the "xmodmap" command, for example:

xmodmap -e "pointer = 1 3 2 4 5"

This command will "reverse" buttons 2 and 3

Send mail configuration

To implement the solution, please execute the following steps:

1. Edit /etc/mail/sendmail.mc to have:

define(`SMART_HOST',`')

and

DAEMON_OPTIONS(`Port=smtp,Addr=127.0.0.1, Name=MTA')dnl


Do NOT edit /etc/mail/sendmail.cf as it may cause unexpected results.

(The "DAEMON_OPTIONS" line is a security measure - it allows sendmail to accept e-mail only from the local server. If you do not need otherwise, this is a good security practice. )

2. Regenerate sendmail.cf from sendmail.mc:

# m4 /etc/mail/sendmail.mc > /etc/mail/sendmail.cf

3. Restart the sendmail service:

# service sendmail restart

CCM.sql Diagnostic Script for Concurrent Manager

Modified 20-OCT-2010 Type SCRIPT Status PUBLISHED


Checked for relevance on 20-Oct-2010
Applications Install 10.7 to 12.0.6


Overview
--------

This script is made available to Diagnose Common Concurrent Manager issues.

Program Notes
-------------

o Ensure the Script is run within a Sourced Applications Environment

o Copy the body of the script within the 'Code begins here' and
'Code ends here' section

Save as: ccm.sql

o Run by entering the following: sqlplus apps/ @ccm.sql

= valid apps password


References
----------

Note 246584.1 Why do I get Found Dead Process in my Standard Manager Log?


Note 104452.1 Troubleshooting (Concurrent Manager Unix specific)

Note 134007.1 Clean up Concurrent Manager tables "cmclean.sql"

Note 157070.1 Pmon Method does not Change After Running "afimpmon.sql"
Script
Caution
-------

The sample program in this article is provided for educational purposes
only and is NOT supported by Oracle Support Services. It has been tested
internally, however, and works as documented. We do not guarantee that it
will work for you, so be sure to test it in your environment before
relying on it.

Program
-------

- - - - - - - - - - - - - - - - Code begins here - - - - - - - - - - - - - - -

REM #########################################################################
REM ## Purpose: Diagnostic Script for Concurrent Manager
REM ## Author: nabil albadin
REM ## Email: nabil.albadin@oracle.com
REM ## Filename: ccm.sql
REM ## Cert: 10.7, 11, 11.5, 12.0
REM ## Note:
REM ## Usage: sqlplus apps/ @ccm.sql
REM ## Output: reqcheck.lst
REM ## Notes:
REM ## Enter value for request ID WHEN PROMPT
REM ##
REM ## $Id: request.sql, v 1.0 4/17/2002 10:22 nalbadin Exp $
REM #########################################################################

spool ccm.lst
prompt Step 1 Checking how many rows in FND_CONCURRENT_REQUEST.

select count(*) from fnd_concurrent_requests;
prompt
-----------------------------------------

prompt Step 2 Checking how many rows in FND_CONCURRENT_PROCESSES table.

select count(*) from fnd_concurrent_processes;
prompt
-----------------------------------------------


prompt Step 3 Checking sys.dual table which should have one and only one row.
select count(*) from sys.dual;

prompt If you have more than one row in sys.dual, please delete it

prompt sql> delete rownum from SYS.DUAL;
Prompt rownum= the row number to delete
prompt
prompt
prompt
---------------------------------------------


prompt Step 4 Checking fnd_dual. There must be at lest one row:

select count(*) from fnd_dual;
prompt If there are no record selected,
prompt Update fnd_dual table to have at lest one record
prompt
----------------------------------------------

prompt Step 5 Checking the Internal Manager queue name "FNDICM" which should be=1

select concurrent_queue_id from fnd_concurrent_queues
where concurrent_queue_name='FNDICM';
prompt
----------------------------------------------

prompt Step 6 Checking for Active processes under the Internal Manager queue
prompt in fnd_concurrent_proceses table:
prompt
select a.concurrent_queue_name
, substr(b.os_process_id,0,10) "OS Proc"
, b.oracle_process_id "Oracle ID"
, b.process_status_code
from fnd_concurrent_queues a
, fnd_concurrent_processes b
where a.concurrent_queue_id=b.concurrent_queue_id
and a.concurrent_queue_name='FNDICM'
and b.process_status_code='A'
order by b.process_status_code;

prompt If any rows found with process_status_code with value = 'A' (= Active)
prompt The internal Manager will not start up ,so to avoide this issue
prompt update these rows to have process_status_code value ='K'(terminated)
prompt
prompt
-----------------------------------------
prompt Step 7 Checking for Active processes under the Standard Manager queue
prompt in fnd_concurrent_proceses table:
prompt
select a.concurrent_queue_name
, substr(b.os_process_id,0,10) "OS Proc"
, b.oracle_process_id "Oracle ID"
, b.process_status_code
from fnd_concurrent_queues a
, fnd_concurrent_processes b
where a.concurrent_queue_id=b.concurrent_queue_id
and a.concurrent_queue_name='STANDARD'
and b.process_status_code='A'
order by b.process_status_code;

prompt If any rows found with process_status_code with value = 'A' (= Active)
prompt The internal Manager will not start up ,so to avoide this issue
prompt update these rows to have process_status_code value ='K'(terminated)
prompt
prompt
------------------------------------------
prompt Step 8 Checking for Active processes under the Conflict Manager queue
prompt in fnd_concurrent_proceses table:
prompt
select a.concurrent_queue_name
, substr(b.os_process_id,0,10) "OS Proc"
, b.oracle_process_id "Oracle ID"
, b.process_status_code
from fnd_concurrent_queues a
, fnd_concurrent_processes b
where a.concurrent_queue_id=b.concurrent_queue_id
and a.concurrent_queue_name='FNDCRM'
and b.process_status_code='A'
order by b.process_status_code;

prompt If any rows found with process_status_code with value = 'A' (= Active)
prompt The internal Manager will not start up ,so to avoide this issue
prompt update these rows to have process_status_code value ='K'(terminated)
prompt
prompt
---------------------------------------------------
prompt Step 9 Checking Actual and Target Processes for Internal Manager:
select MAX_PROCESSES,RUNNING_PROCESSES
from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME='FNDICM';

prompt If the MAX_PROCESSES=RUNNING_PROCESSES that means the manager is UP.
prompt
prompt
--------------------------------------------------------

prompt Step 10 Checking Actual and Target Processes for the Standard Manager:
select MAX_PROCESSES,RUNNING_PROCESSES
from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME='STANDARD';

prompt If the MAX_PROCESSES=RUNNING_PROCESSES that means the manager is UP.
prompt
prompt
---------------------------------------------------------
prompt Step 11 Checking Actual and Target Processes for Conflict Resolution Manager:
select MAX_PROCESSES,RUNNING_PROCESSES
from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME='FNDCRM';

prompt If the MAX_PROCESSES=RUNNING_PROCESSES that means the manager is UP.
prompt
prompt
---------------------------------------------------------

Prompt Step 12 Checking if the control_code set to 'N':

select control_code from fnd_concurrent_queues
where control_code='N';
prompt
prompt If any rows selected, please update the table fnd_concurrent_queues:
prompt Update fnd_concurrent_queues set control_code = null
prompt where control_code ='N';
PROMPT Update fnd_concurrent_queues set target_node = null;
PROMPT commit;
prompt
prompt
--------------------------------

PROMPT Step 13 Checking terminated processes:
PROMPT
select count (*) from fnd_concurrent_requests
where status_code='T';
prompt
prompt If you have terminated processes run the following sql statement:
prompt
prompt SQL> Update fnd_concurrent_requests
prompt set status_code = 'E', phase_code = 'C'
prompt where status_code = 'T';
prompt
------------------------------------------


prompt Step 14 Checking pending requests:

select count(*) from fnd_concurrent_requests
where status_code='P';
prompt If any rows selected please run the following sql statement:

prompt SQL> Update fnd_concurrent_requests
prompt set status_code = 'E', phase_code = 'C'
prompt where status_code = 'P';
prompt
------------------------------------------------------
prompt Step 15 Checking Running processes:
prompt
select count (*) from fnd_concurrent_requests
where status_code='R';
prompt
prompt If you have Running processes run the following sql statement
prompt SQL> Update fnd_concurrent_requests
prompt set status_code = 'E', phase_code = 'C'
prompt where status_code = 'R';
prompt
------------------------------------------

prompt Step 16 Checking the PMON method, which should be set to LOCK:
prompt
select profile_option_id , profile_option_value
from FND_PROFILE_OPTION_VALUES
where profile_option_id= (select profile_option_id
from FND_PROFILE_OPTIONS
where profile_option_name='CONC_PMON_METHOD');
prompt
prompt If the PROFILE_OPTION_VALUE was't LOCK please
prompt Reset PMON to LOCK by running afimpmon.sql script(The manager should be down)

prompt 1-At UNIX command prompt:

prompt 2-cd $FND_TOP/sql

prompt 3-Log into SQLPLUS as apps/

prompt SQL> @afimpmon.sql
prompt prompt1:dual
prompt prompt2:LOCK (LOCK MUST BE ALL UPPERCASE)

prompt For Oracle Applications Release 11.5 and 12.0, when you check the PMON
prompt Method you may get no rows selected which is normal,
prompt because in apps 11.5 and 12.0 the PMON Method is hard coded to Lock at
prompt the Operating System level.
prompt
prompt
-------------------------------------------------------

prompt Step-17 Checking how many FNDLIBR processes are running:
prompt -For Unix :From unix command prompt $ ps -ef |grep -i fndlibr
prompt If you have any FNDLIBR processes running,please kill them before
prompt starting or shuting down the internal manager
prompt
prompt
prompt -For NT, through Task Manager, check the entries under the Processes tab
for FNDLIBR.exe processes.
prompt If there are any, Highlight and click [End Process] button to kill processes

prompt
----------------------------------------------------------

prompt Step-18 Checking how many "FND_%"invalid objects:

select substr(owner,1, 12) owner, substr(object_type,1,12) type,
substr(status,1,8) status, substr(object_name, 1, 25) name
from dba_objects
where object_name like 'FND_%'
and status='INVALID';

prompt If you have any invalied objects please see note#113947.1 via Metalink

prompt
--------------------------------------------------------------

prompt Step-19-How to find the PID in the O/S for request_id:
prompt If you do not like to check this enter any number then click Enter to Exit

select r.request_id, p.os_process_id
from FND_CONCURRENT_REQUESTS r,FND_CONCURRENT_PROCESSES p
where r.controlling_manager = p.concurrent_process_id
and request_id=&request_id;

prompt
prompt Please upload the "ccm.lst" output to Support, Thanks.
prompt
spool off
- - - - - - - - - - - - - - - - Code ends here - - - - - - - - - - - - - - - -


Sample Output
-------------

For Step 5 Checking the internal manager queue name"FNDICM":


CONCURRENT_QUEUE_ID
-------------------
1

For Step 6 Checking for Active processes under standard manager queue
in fnd_concurrent_proceses table



CONCURRENT_QUEUE_NAME OS Proc Oracle ID P
------------------------------ ---------- ---------- -
STANDARD 4271 12 A
STANDARD 4272 10 A


For Step 9 Checking the PMON method:

PROFILE_OPTION_ID
-----------------
PROFILE_OPTION_VALUE
-------------------
1044
LOCK


Additional Search Words
-----------------------

N/A

Scripts to Check the Setup of the General Ledger Calendar

nstructions for the Reader
A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.
Troubleshooting Details

Follow these steps to check the Calendar definitions and period statuses for Oracle Financial Applications that use the Calender defined by General Ledger.
This script will return the Calendar definitions, and the statuses of the periods for the different Oracle Financial Applications installed, for a specific Set of Books/Ledger.

1. Log into SQL*Plus as apps user:-
sql> /

2. At the SQL prompt type:

SQL> spool

Assuming you would like to save the output to the directory C:\TEMP and name the output file as
"calendar", type :

SQL> spool C:\TEMP\calendar.txt

3. Run the following queries to check the calendar:
(You can copy and paste the following query to Notepad, save it, then log into SQL*Plus from your
desktop by going to Start/Programs/Oracle for Windows/SQL Plus and log into your database and type
the following:

@\