creating stand by database

Step-by-step instructions on how to create a Physical Standby Database on Windows and UNIX servers, and maintenance tips on the databases in a Data Guard Environment.

Oracle 10g Data Guard is a great tool to ensure high availability, data protection and disaster recovery for enterprise data. I have been working on Data Guard/Standby databases using both Grid control and SQL command line for a couple of years, and my latest experience with Data Guard was manually creating a Physical Standby Database for a Laboratory Information Management System (LIMS) half a year ago. I maintain it daily and it works well. I would like to share my experience with the other DBAs.

In this example the database version is 10.2.0.3.. The Primary database and Standby database are located on different machines at different sites. The Primary database is called PRIM and the Standby database is called STAN. I use Flash Recovery Area, and OMF.

I. Before you get started:

1. Make sure the operating system and platform architecture on the primary and standby systems are the same;

2. Install Oracle database software without the starter database on the standby server and patch it if necessary. Make sure the same Oracle software release is used on the Primary and Standby databases, and Oracle home paths are identical.

3. Test the Standby Database creation on a test environment first before working on the Production database.

II. On the Primary Database Side:

1. Enable forced logging on your primary database:
SQL> ALTER DATABASE FORCE LOGGING;

2. Create a password file if it doesn’t exist.
1) To check if a password file already exists, run the following command:
SQL> select * from v$pwfile_users;

2) If it doesn’t exist, use the following command to create one:
- On Windows:
$cd %ORACLE_HOME%\database
$orapwd file=pwdPRIM.ora password=xxxxxxxx force=y
(Note: Replace xxxxxxxxx with the password for the SYS user.)

- On UNIX:
$Cd $ORACLE_HOME/dbs
$Orapwd file=pwdPRIM.ora password=xxxxxxxx force=y
(Note: Replace xxxxxxxxx with your actual password for the SYS user.)

3. Configure a Standby Redo log.
1) The size of the standby redo log files should match the size of the current Primary database online redo log files. To find out the size of your online redo log files:
SQL> select bytes from v$log;

BYTES
----------
52428800
52428800
52428800

2) Use the following command to determine your current log file groups:
SQL> select group#, member from v$logfile;

3) Create standby Redo log groups.
My primary database had 3 log file groups originally and I created 3 standby redo log groups using the following commands:
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50M;

4) To verify the results of the standby redo log groups creation, run the following query:
SQL>select * from v$standby_log;

4. Enable Archiving on Primary.
If your primary database is not already in Archive Log mode, enable the archive log mode:
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database open;
SQL>archive log list;

5. Set Primary Database Initialization Parameters
Create a text initialization parameter file (PFILE) from the server parameter file (SPFILE), to add the new primary role parameters.

1) Create pfile from spfile for the primary database:
- On Windows:
SQL>create pfile=’\database\pfilePRIM.ora’ from spfile;
(Note- specify your Oracle home path to replace ‘’).

- On UNIX:
SQL>create pfile=’/dbs/pfilePRIM.ora’ from spfile;
(Note- specify your Oracle home path to replace ‘’).

2) Edit pfilePRIM.ora to add the new primary and standby role parameters: (Here the file paths are from a windows system. For UNIX system, specify the path accordingly)

db_name=PRIM
db_unique_name=PRIM
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIM,STAN)'
LOG_ARCHIVE_DEST_1=
'LOCATION=F:\Oracle\flash_recovery_area\PRIM\ARCHIVELOG
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=PRIM'
LOG_ARCHIVE_DEST_2=
'SERVICE=STAN LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=STAN'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
remote_login_passwordfile='EXCLUSIVE'
FAL_SERVER=STAN
FAL_CLIENT=PRIM
STANDBY_FILE_MANAGEMENT=AUTO
# Specify the location of the standby DB datafiles followed by the primary location;
DB_FILE_NAME_CONVERT='E:\oracle\product\10.2.0\oradata\STAN\DATAFILE','E:\oracle\product\10.2.0\oradata\PRIM\DATAFILE'
# Specify the location of the standby DB online redo log files followed by the primary location LOG_FILE_NAME_CONVERT=’E:\oracle\product\10.2.0\oradata\STAN\ONLINELOG’,’E:\oracle\product\10.2.0\oradata\PRIM\ONLINELOG’,’F:\Oracle\flash_recovery_area\STAN\ONLINELOG’,’F:\Oracle\flash_recovery_area\PRIM\ONLINELOG’

6. Create spfile from pfile, and restart primary database using the new spfile.
Data Guard must use SPFILE. Create the SPFILE and restart database.
- On windows:
SQL> shutdown immediate;
SQL> startup nomount pfile=’\database\pfilePRIM.ora’;
SQL>create spfile from pfile=’\database\pfilePRIM.ora’;
-- Restart the Primary database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>Startup;
(Note- specify your Oracle home path to replace ‘’).

- On UNIX:
SQL> shutdown immediate;
SQL> startup nomount pfile=’/dbs/pfilePRIM.ora’;
SQL>create spfile from pfile=’/dbs/pfilePRIM.ora’;
-- Restart the Primary database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>Startup;
(Note- specify your Oracle home path to replace ‘’).

III. On the Standby Database Site:

1. Create a copy of Primary database data files on the Standby Server:
On Primary DB:
SQL>shutdown immediate;

On Standby Server (While the Primary database is shut down):
1) Create directory for data files, for example, on windows, E:\oracle\product\10.2.0\oradata\STAN\DATAFILE.
On UNIX, create the directory accordingly.

2) Copy the data files and temp files over.

3) Create directory (multiplexing) for online logs, for example, on Windows, E:\oracle\product\10.2.0\oradata\STAN\ONLINELOG and F:\Oracle\flash_recovery_area\STAN\ONLINELOG.
On UNIX, create the directories accordingly.

4) Copy the online logs over.

2. Create a Control File for the standby database:
On Primary DB, create a control file for the standby to use:
SQL>startup mount;
SQL>alter database create standby controlfile as ‘STAN.ctl;
SQL>ALTER DATABASE OPEN;

3. Copy the Primary DB pfile to Standby server and rename/edit the file.

1) Copy pfilePRIM.ora from Primary server to Standby server, to database folder on Windows or dbs folder on UNIX under the Oracle home path.

2) Rename it to pfileSTAN.ora, and modify the file as follows. : (Here the file paths are from a windows system. For UNIX system, specify the path accordingly)

*.audit_file_dest='E:\oracle\product\10.2.0\admin\STAN\adump'
*.background_dump_dest='E:\oracle\product\10.2.0\admin\STAN\bdump'
*.core_dump_dest='E:\oracle\product\10.2.0\admin\STAN\cdump'
*.user_dump_dest='E:\oracle\product\10.2.0\admin\STAN\udump'
*.compatible='10.2.0.3.0'
control_files='E:\ORACLE\PRODUCT\10.2.0\ORADATA\STAN\CONTROLFILE\STAN.CTL','F:\ORACLE\FLASH_RECOVERY_AREA\STAN\CONTROLFILE\STAN.CTL'
db_name='PRIM'
db_unique_name=STAN
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(PRIM,STAN)’
LOG_ARCHIVE_DEST_1=
‘LOCATION=F:\Oracle\flash_recovery_area\STAN\ARCHIVELOG
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=STAN’
LOG_ARCHIVE_DEST_2=
‘SERVICE=PRIM LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=PRIM’
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=PRIM
FAL_CLIENT=STAN
remote_login_passwordfile='EXCLUSIVE'
# Specify the location of the primary DB datafiles followed by the standby location
DB_FILE_NAME_CONVERT=’E:\oracle\product\10.2.0\oradata\PRIM\DATAFILE’,’E:\oracle\product\10.2.0\oradata\STAN\DATAFILE’
# Specify the location of the primary DB online redo log files followed by the standby location
LOG_FILE_NAME_CONVERT=’E:\oracle\product\10.2.0\oradata\PRIM\ONLINELOG’,’E:\oracle\product\10.2.0\oradata\STAN\ONLINELOG’,’F:\Oracle\flash_recovery_area\PRIM\ONLINELOG’,’F:\Oracle\flash_recovery_area\STAN\ONLINELOG’
STANDBY_FILE_MANAGEMENT=AUTO

(Note: Not all the parameter entries are listed here.)

4. On Standby server, create all required directories for dump and archived log destination:
Create directories adump, bdump, cdump, udump, and archived log destinations for the standby database.

5. Copy the standby control file ‘STAN.ctl’ from primary to standby destinations ;

6. Copy the Primary password file to standby and rename it to pwdSTAN.ora.
On Windows copy it to \database folder, and on UNIX copy it to /dbs directory. And then rename the password file.

7. For Windows, create a Windows-based services (optional):
$oradim –NEW –SID STAN –STARTMODE manual

8. Configure listeners for the primary and standby databases.

1) On Primary system: use Oracle Net Manager to configure a listener for PRIM and STAN. Then restart the listener.
$lsnrctl stop
$lsnrctl start

2) On Standby server: use Net Manager to configure a listener for PRIM and STAN. Then restart the listener.
$lsnrctl stop
$lsnrctl start

9. Create Oracle Net service names.
1) On Primary system: use Oracle Net Manager to create network service names for PRIM and STAN. Check tnsping to both services:
$tnsping PRIM
$tnsping STAN

2) On Standby system: use Oracle Net Manager to create network service names for PRIM and STAN. Check tnsping to both services:
$tnsping PRIM
$tnsping STAN

10. On Standby server, setup the environment variables to point to the Standby database.

Set up ORACLE_HOME and ORACLE_SID.

11. Start up nomount the standby database and generate a spfile.
- On Windows:
SQL>startup nomount pfile=’\database\pfileSTAN.ora’;
SQL>create spfile from pfile=’\database\pfileSTAN.ora’;
-- Restart the Standby database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>startup mount;

- On UNIX:
SQL>startup nomount pfile=’/dbs/pfileSTAN.ora’;
SQL>create spfile from pfile=’/dbs/pfileSTAN.ora’;
-- Restart the Standby database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>startup mount;
(Note- specify your Oracle home path to replace ‘’).

12. Start Redo apply
1) On the standby database, to start redo apply:
SQL>alter database recover managed standby database disconnect from session;

If you ever need to stop log apply services:
SQL> alter database recover managed standby database cancel;

13. Verify the standby database is performing properly:
1) On Standby perform a query:
SQL>select sequence#, first_time, next_time from v$archived_log;

2) On Primary, force a logfile switch:
SQL>alter system switch logfile;

3) On Standby, verify the archived redo log files were applied:
SQL>select sequence#, applied from v$archived_log order by sequence#;

14. If you want the redo data to be applied as it is received without waiting for the current standby redo log file to be archived, enable the real-time apply.

To start real-time apply:
SQL> alter database recover managed standby database using current logfile disconnect;

15. To create multiple standby databases, repeat this procedure.

IV. Maintenance:

1. Check the alert log files of Primary and Standby databases frequently to monitor the database operations in a Data Guard environment.

2. Cleanup the archive logs on Primary and Standby servers.

I scheduled weekly Hot Whole database backup against my primary database that also backs up and delete the archived logs on Primary.

For the standby database, I run RMAN to backup and delete the archive logs once per week.
$rman target /@STAN;
RMAN>backup archivelog all delete input;

To delete the archivelog backup files on the standby server, I run the following once a month:
RMAN>delete backupset;

3. Password management
The password for the SYS user must be identical on every system for the redo data transmission to succeed. If you change the password for SYS on Primary database, you will have to update the password file for Standby database accordingly, otherwise the logs won’t be shipped to the standby server.

Refer to section II.2, step 2 to update/recreate password file for the Standby database.

Rolling a standby forward using an RMAN incremental backup in 10g

to enable the automatic maintenance of archive logs on standby database :
to enable flash recovery area and to allow the archived logs to be shipped to flash recovery area

SQL> alter system set DB_RECOVERY_FILE_DEST='/disk1/FRA';
SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=80G;
SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';


RMAN>configure archivelog deletion policy to applied on standby database
'when there is space pressure on FRA then the archive logs applied on standby will be automatically deleted



Rolling a standby forward using an RMAN incremental backup in 10g
=================================================================
When the standby database has fallen significantly behind the primary database and there have not been nologging operations performed on the primary database
OR
When nologging operations have been performed on the primary database and the standby database has not yet applied past the point that the nologging operations occurred


Steps

1.stop managed recovery on the physical standby database
 
SQL> alter database recover managed standby database cancel;

2.connect to the recovery catalog and the standby database As the target and manually catalog the standby datafiles as datafile copies
 RMAN> connect catalog rman/rman@host1/rcvcat
 RMAN> connect target sys/manager@host2/stby
RMAN> catalog datafilecopy
     '/oracle/oradata/stdby/datafile/01_mf_system_0chdb5ab_.dbf,
                                    /01_mf_sys_undo_0chdb6in_.dbf,
                                    /01_mf_sysaux_0chdb5wm_.dbf,
  level 0 tag 'STBY';
3.connect to the recovery catalog and the primary database as the target, and create
   an incremantal level 1 backup using the datafile copies as the parent level 0
 RMAN>connect catalog rman/rman@host1/rcvcat
 RMAN>connect target sys/manager@host1/prod
 RMAN> backup incremental level 1 tag'STBY' for recover of copy with tag 'STBY' database format '/private/backup/%u';

4.copy the newly created backup piece to the same location on the standby system

  $ rcp /private/backup/<incr_backup> stby:/private/backup/

5.connect to the recovery catalog and the standby database as the target and roll the datafile copies forward
  RMAN>connect  catalog rman/rman@host1/rcvcat
  RMAN>connect target sys/manager@host2/stby
  RMAN>recover copy of database with tag 'STBY';

6.delete incremental backup and uncatalog the  standby datafiles as datafile copies
RMAN> delete backup tag 'STBY';
RMAN> change copy like '/oracle/oradata/STBY/datafile/%',uncatalog

The RECOVER COPY OF DATABASE command causes the 'STBY' tag associated with the datafile copies to change to '' (NULL) or back to a previous tag, if one had existed.  Use CHANGE COPY LIKE to uncatalog the standby datafiles as copies.

7.restart managed recovery
  SQL> alter database recover managed standby database disconnect;

Copy and Rename an Oracle Database - (without export/import)

1.) Copy production database files and init.ora

The first step is to locate and copy all database files to their new location. You can use the view V$DATAFILE in the PROD database to locate these files. Before running the query from V$DATAFILE, ensure that you are connected to the PROD database by selecting from V$DATABASE:

  SQL> select name from v$database;

  NAME

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

  PROD

  SQL> select name from v$datafile;

  NAME

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

  /u08/app/oradata/PROD/system01.dbf

  /u06/app/oradata/PROD/rbs01.dbf

  /u07/app/oradata/PROD/temp01.dbf

  /u10/app/oradata/PROD/userd01.dbf

  /u09/app/oradata/PROD/userx01.dbf

After recording these files, shutdown the PROD database and perform an operating system copy of all database files to another location and/or machine. In my example, I will copy all datafiles to a new location as shown in the following table:

Old Location	New Location
/u08/app/oradata/PROD/system01.dbf	/u08/app/oradata/TEST/system01.dbf
/u06/app/oradata/PROD/rbs01.dbf	/u06/app/oradata/TEST/rbs01.dbf
/u07/app/oradata/PROD/temp01.dbf	/u07/app/oradata/TEST/temp01.dbf
/u10/app/oradata/PROD/userd01.dbf	/u10/app/oradata/TEST/userd01.dbf
/u09/app/oradata/PROD/userx01.dbf	/u09/app/oradata/TEST/userx01.dbf

After copying all files to their new location, startup the PROD database.

From the production database, get a copy of the initPROD.ora file and copy it to initTEST.ora. In the initTEST.ora file, change the value of "db_name" from PROD to TEST. Keep in mind that you may also need to change:

·         audit_file_dest

·         background_dump_dest

·         control_files

·         core_dump_dest

·         log_archive_dest

·         user_dump_dest

If the TEST database is going to be on a different machine, copy the initTEST.ora file to that machine in the proper directory.

2.) Create the script that will re-create the controlfile

Using SVRMGR on the PROD database, create a script that will be able to re-create the controlfile for the database.

  PROD on testdb: svrmgrl

  SVRMGR> connect internal

  Connected.

  SVRMGR> alter database backup controlfile to trace;

  Statement processed.

The above statement will put a text copy of the controlfile in the USER_DUMP_DEST directory. You will need to search for the newest trace file in this directory. In UNIX you can use the "ls -lt" command. Once you find the correct trace file, rename it to cr_control.sql and edit it as follows:

·         Remove everything up to the "START NOMOUNT" statement and everything after the semicolon at the end of the "CREATE CONTROLFILE" statement.

·         Edit the line starting with "CREATE CONTROLFILE" and replace the word "REUSE" with the word "SET" right before the keyword DATABASE.

·         On the same line, modify the database name changing it from PROD to TEST.

·         On the same line, change the keyword NORESETLOGS to RESETLOGS.

Your script should now read:

Edited file cr_control.sql

STARTUP NOMOUNT CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS NOARCHIVELOG     MAXLOGFILES 32     MAXLOGMEMBERS 5     MAXDATAFILES 600     MAXINSTANCES 10     MAXLOGHISTORY 1000 LOGFILE   GROUP 1 (     '/u03/app/oradata/TEST/redo_g01a.log',     '/u04/app/oradata/TEST/redo_g01b.log',     '/u05/app/oradata/TEST/redo_g01c.log'   ) SIZE 200K,   GROUP 2 (     '/u03/app/oradata/TEST/redo_g02a.log',     '/u04/app/oradata/TEST/redo_g02b.log',     '/u05/app/oradata/TEST/redo_g02c.log'   ) SIZE 200K,   GROUP 3 (     '/u03/app/oradata/TEST/redo_g03a.log',     '/u04/app/oradata/TEST/redo_g03b.log',     '/u05/app/oradata/TEST/redo_g03c.log'   ) SIZE 200K DATAFILE   '/u08/app/oradata/TEST/system01.dbf',   '/u06/app/oradata/TEST/rbs01.dbf',   '/u07/app/oradata/TEST/temp01.dbf',   '/u10/app/oradata/TEST/userd01.dbf',   '/u09/app/oradata/TEST/userx01.dbf' ;

If the TEST database is on a different machine move this file to that machine.

3.) Create the new controlfile for TEST

Make sure that your Oracle environment variable "ORACLE_SID" is set to TEST. (i.e. export ORACLE_SID=TEST).

Now use SVRMGR and the CREATE CONTROLFILE script (cr_control.sql) to create your controlfile for TEST:

  TEST on testdb: svrmgrl

  SVRMGR> connect internal

  Connected to an idle instance.

  SVRMGR> @cr_control

  ORACLE instance started.

  Total System Global Area      32798752 bytes

  Fixed Size                       39816 bytes

  Variable Size                 22600856 bytes

  Database Buffers               9994240 bytes

  Redo Buffers                    163840 bytes

  Statement processed.

  SVRMGR>

NOTE: Stay logged into SVRMGR and proceed to the next step.

4.) Open the TEST database

Before opening the TEST database, you will need to perform incomplete recovery. After recovery you can open the database using the RESETLOGS option as show below:

  SVRMGR> alter database recover database until cancel using backup controlfile;

  SVRMGR> alter database recover cancel;

  Statement processed.

  SVRMGR> alter database open resetlogs;

  Statement processed.

  SVRMGR>

You can verify that the database was renamed to TEST by querying from V$DATABASE:

    SVRMGR> select name from v$database;

    NAME

    ---------

    TEST

    1 row selected.

    SVRMGR>

resizeing the log file

http://dbataj.blogspot.com/2009/07/how-to-resize-redolog-file-size.html

How to resize Redolog File Size


Today I resized one of our production database redolog size from 10m to 25m.

I want to share with all.

Procedure (Tested on PROD)

1. Check the logfile member , group and current size

SQL> column member format a50

SQL> select a.group#,a.member,b.bytes/1024/1024 "size"

2 from v$logfile a, v$log b

3 where a.group# = b.group#;
GROUP# MEMBER size--------- -------------------------------------------------- --------- 1 /db/app/oracle/Db_1/oradata/orcl/redo01.log 10 2 /db/app/oracle/Db_1/oradata/orcl/redo02.log 10 3 /db/app/oracle/Db_1/oradata/orcl/redo03.log 10 4 /db/app/oracle/Db_1/oradata/orcl/redo04.log 10 5 /db/app/oracle/Db_1/oradata/orcl/redo05.log 10

2. Check the logfile status of the existing redolog file

SQL> select group#,status from v$log; GROUP# STATUS
--------- ----------------
1 INACTIVE

2 CURRENT

3 INACTIVE

4 INACTIVE

5 INACTIVE

3. Switch the logfile upto last group

SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.

Again check the logfile status

SQL> select group#,status from v$log;
GROUP# STATUS

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

1 INACTIVE

2 ACTIVE

3 ACTIVE

4 ACTIVE

5 CURRENT

4. Issue the below statement to make the Inactive status of active status and avoid the 'ORA-01624'.

SQL> alter system checkpoint global;

5. Once logfile status show "INACTIVE" then issue the following statement.

SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.

6. At least minimum 2 group must be exist in the database at a time Or if we trying to drop group 4 then we receive the following error.

SQL> alter database drop logfile group 4;alter database drop logfile group 4

*

ERROR at line 1:ORA-01567: dropping log 4 would leave less than 2 log files for instance orcl (thread 1)

ORA-00312: online log 4 thread 1: '/db/app/oracle/Db_1/oradata/orcl/redo04.log'

To avoid above error please add the group first then drop group 4,5 respectivily.

SQL> alter database add logfile group 1 2 ('/db/app/oracle/Db_1/oradata/orcl/redo01.log') size 25m reuse;
Database altered.
SQL> alter database add logfile group 2 2 ('/db/app/oracle/Db_1/oradata/orcl/redo02.log') size 25m reuse;
Database altered.
SQL> alter database add logfile group 3 2 ('/db/app/oracle/Db_1/oradata/orcl/redo03.log') size 25m reuse;
Database altered.

Note drop the group 4,5 and add the group 4,5 with above statement.

Check the group status and size...

SQL> select group#,status from v$log;
GROUP# STATUS

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

1 CURRENT

2 UNUSED

3 UNUSED

4 UNUSED

5 UNUSED

SQL> select a.group#,a.member,b.bytes/1024/1024 "size"

2 from v$logfile a, v$log b

3 where a.group# = b.group#;
GROUP# MEMBER size

--------- -------------------------------------------------- --------- 1 /db/app/oracle/Db_1/oradata/orcl/redo01.log 25 2 /db/app/oracle/Db_1/oradata/orcl/redo02.log 25 3 /db/app/oracle/Db_1/oradata/orcl/redo03.log 25 4 /db/app/oracle/Db_1/oradata/orcl/redo04.log 25 5 /db/app/oracle/Db_1/oradata/orcl/redo05.log 25

You can receive the following error message during the above procedure ...

1. ORA-01624

To avoid this error issue the "alter system checkpoint global;"

2. ORA-01567

To avoid this error we need to keep at least two redolog group in the database.

3. ORA-01623

To avoid this error issue the "alter system switch logfile;"

I use the below references:

http://www.idevelopment.info/data/Oracle/DBA_tips/Database_Administration/DBA_34.shtml

RMAN error

RMAN-06429 TARGET Database is not Compatible with this Version of RMAN [ID 245698.1]
  Modified 20-OCT-2010     Type PROBLEM     Status PUBLISHED

The information in this article applies to:

  RMAN : Target 8.1.7.4.0 - Schema and database catalog 9i.    


Symptom(s)
~~~~~~~~~~

  When connecting a target 8.1.7.4 and a catalog 9i  You receive
  the errors:

    RMAN-06438: error executing package DBMS_RCVMAN in TARGET database

    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============
    RMAN-00571: ===========================================================
    RMAN-00554: initialization of internal recovery manager package failed
    RMAN-06429: TARGET database is not compatible with this version of RMAN


Cause
~~~~~~~

  The relevant message in this case is RMAN-06438 because package DBMS_RCVMAN
  in target database is invalid.


Fix
~~~~

  Recompile package DBMS_RCVMAN as states in the error message :

  Error: RMAN 6438
  Text: error executing package DBMS_RCVMAN in target database
  ---------------------------------------------------------------------------
  Cause: Recovery Manager requires the DBMS_RCVMAN package in the
         SYS schema of the indicated database.
         Normally this package is installed during database
         creation. To re-create the package, run the files dbmsrman.sql
         and prvtrmns.plb.

   $ sqlplus sys/<pw>
   SQL> @?/rdbms/admin/dbmsrman.sql
        @?/rdbms/admin/prvtrmns.plb

rman duplicate data base script

hi

i managed to get a small backup script from another colleague and would like to clear a few things up with you.

here is the script:

CONNECT TARGET /
-----(this just connects to the DB :-) )

configure channel device type disk format 'C:\oracle\oracle_backup';
-----(i have created this to point the backup in the correct place. Do i need to register this every time i run the backup? will it ----------affect an incremental backup if i register it everytime?)

-----(what I have noticed as well is that it creates a file called 'SNC<SID_NAME>.ORA' of about 10MB in -----'C:\oracle\product\10.2.0\db_1\database\' folder still.... what is this file? )

BACKUP CHECK LOGICAL VALIDATE DATABASE;
-----(what does this do?)

BACKUP INCREMENTAL LEVEL 0 TAG weekly_backup DATABASE;
-----(will this backup all data? in the event of a drive failure, could it restore everything? this will be weekly and a level 1 backup -----tagged as monday_backup, tuesady_backup, etc. etc.)

SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
-----(what does this do?)

CROSSCHECK ARCHIVELOG ALL;
-----(what does this do?)

CROSSCHECK BACKUP;
-----(what does this do?)

DELETE OBSOLETE;
-----(what does this do? i noticed when I ran a level 1 backup for monday, tuesday to test and then ran the level 0 again, it ---------------deleted all the level 1 backups.. is this basically what this command does?)


ANS::::::::::::::::::::::::::::::;;;
 First, a configure command will sore the configuration in the controlfile (and catalog if it's being used) so there is no need to execute it again.

However, your configuration:

configure channel device type disk format 'C:\oracle\oracle_backup';

Is not correct.  You need to add more to the file name so RMAN creates a unique name each time.  Look at the 'formatSpec' options in Oracle® Database Backup and Recovery Reference 10 g Release 2 (10.2).  Assuming  oracle_backup is a directory, your configuration would be something like:

configure channel device type disk format 'C:\oracle\oracle_backup\%U';

Next, the 'SNC<SID_NAME>.ORA' file.  By default Oracle will always create files in the $ORACLE_HOME/dbs unless another location is specified.

Hard to say what this file is without looking at the backup output.  Post the full 'show all' output and we will be able to tell if this is a controlfile (either autobackup or snapshot).

>will it ----------affect an incremental backup if i register it everytime?)
I suppose you could without impact; but unless you're changing the setup, there's no need.


>BACKUP CHECK LOGICAL VALIDATE DATABASE; -----(what does this do?)
This checks for logical (and physical) corruption.


>BACKUP INCREMENTAL LEVEL 0 TAG weekly_backup DATABASE; -----(will this backup all data? in the event of a drive failure, could it restore everything? this will be weekly and a level 1 backup -----tagged as monday_backup, tuesady_backup, etc. etc.)

Assuming you have all of the archive logs in between, then yes.
Level 0...arch logs...level 1....arch logs....level 1....arch logs...point of restore


>SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';-----(what does this do?)
This logs into sqlplus and performs a log switch. (Writing the current redo logs onto an archive log).


>CROSSCHECK ARCHIVELOG ALL;-----(what does this do?)
>CROSSCHECK BACKUP;-----(what does this do?)
Crosschecks go through RMANs records of backups according to the policies you set. If for instance you said you only want to retain 1 week's worth, it will mark all backups no longer needed according to that criteria as obsolete. Additionally, it will mark all backups it can no longer find as expired.


>DELETE OBSOLETE;-----(what does this do? i noticed when I ran a level 1 backup for monday, tuesday to test and then ran the >level 0 again, it ---------------deleted all the level 1 backups.. is this basically what this command does?)
This fits in to the above question. What it comes up with as obsolete is exclusively based on the retention policy you set. Based on this setting, it will mark the files as obsolete (during the crosscheck) and then delete them (during this step).

linux commands for DBA

Linux Command

Below are the few linux command which frequently use by DBA in day to day work.

1. Copy command

$cp -irf source directory target directory

2. size

$du -sh direcotry name

3. Remove the diretory & subdirectory

$rm -irf directoryname

4. Move the files from one directory to another directory

mv -irf sourcedirectory targetdirectory

5. Change ownership on directory or subdirectory

$chown -R user:group directory

6. Find file in the linux system

$find -name eg: find /data1/PROD/ -name apichecb.pls

1. directory name where we want to search the file.

2. file name is which we want to search

7. How to check "File" type in linux

$file filename

eg:

[appluat@FAKORAAPPS forms]$ file collect_30324

collect_30324: ASCII text

8. how to check linux version
I.$uname -a (with kernal version)

[root@FAKORAAPPS ~]# uname -aLinux FAKORAAPPS.xxxxxxxx.local 2.6.18-53.el5PAE #1 SMP Wed Oct 10 16:48:18 EDT 2007 i686 i686 i386 GNU/Linux

II.[root@FAKORAAPPS /]# cat /proc/versionLinux version 2.6.18-53.el5PAE (brewbuilder@hs20-bc2-3.build.redhat.com) (gcc version 4.1.2 20070626 (Red Hat 4.1.2-14)) #1 SMP Wed Oct 10 16:48:18 EDT 2007

III.[root@FAKORAAPPS /]# cat /etc/redhat-releaseRed Hat Enterprise Linux Server release 5.1 (Tikanga)

8. how to check group information in the linux

$cat /etc/group

9. how to check user information like group and etc..
$id

10. how to install RPM package

#rpm -ivch packagename

i stand for install

11. change to usb filesystem type from ntfs to ext3

#mke2fs -j /dev/sdb1
then mount the usbfirst check the usb filsesystem type

#fdisk -l then use the below command to mount

#mount /dev/sdb1 /mnt/usb