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