Restoring Archive Logs to a Different Location

In situations where the archived redo logs need to  be restored to a different location, from an existing backup, following steps could be followed.

1.Load the profile of the the database whose archivelog needs to be restored.
2.Connect to rman client.
@:oracle:/home/oracle> rman

Recovery Manager: Release 11.2.0.1.0 - Production on Thu May 31 10:05:37 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

3.Connect to target database.

RMAN> connect target /

connected to target database: PROD (DBID=3162812738)

4.Connect to catalog database.
RMAN> connect catalog rman/rman@RMAN

connected to recovery catalog database

5.Execute the below scripts to restore archivelogs from sequence 100278 to 100298 at location  '/stage'
RMAN>run
>{
>set archivelog destination to '/stage';
>restore archivelog from logseq 100278 until logseq 100298;
>}

executing command: SET ARCHIVELOG DESTINATION

Starting restore at 31-MAY-12
configuration for SBT_TAPE channel 2 is ignored
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=1556 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 5.5.2.0
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=16 device type=DISK

channel ORA_SBT_TAPE_1: starting archived log restore to user-specified destination
archived log destination=/home/oracle
channel ORA_SBT_TAPE_1: restoring archived log
archived log thread=1 sequence=98529
channel ORA_SBT_TAPE_1: reading from backup piece arch_784714064_61569_1
channel ORA_SBT_TAPE_1: piece handle=arch_784714064_61569_1 tag=TAG20120531T080713
channel ORA_SBT_TAPE_1: restored backup piece 1
channel ORA_SBT_TAPE_1: restore complete, elapsed time: 00:02:15
Finished restore at 31-MAY-12

RMAN>

6.Restore activity completed.

FAL[client]: Failed to request gap sequence

During my routine early morning checks one day, I noticed that there there is a huge sequence mismatch between primary and standby database.Checking the alert logs revealed the below error message.

Fetching gap sequence in thread 1, gap sequence 2150-2150
Sun Mar 11 17:54:40 2012
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 2150-2150
DBID 31666668 branch 7122030
FAL[client]: All defined FAL servers have been attempted.
-------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that is sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.
-------------------------------------------------------------


Archived logs of a specific sequence was not available at the standby location.But was able to find this log at primary database(Thank God).


The solution was simple.Copy the specific logfile from primary to standby database.Let the standby database know about its availability.


alter database register logfile "dir+filename" ;


Recovery process would start automatically.Else stop the managed recovery and re-start it once again.

Restarting an IMPDP Job

There is a unique feature in impdp by which we could re-start the job, from the exact position where it stopped.

The reason for the job getting stopped(or forcefully stopped) could be anything from lack of space or if in case of any database parameter change.

The steps are as below :

1. In the import window - Ctrl -C(to come out of the running job)
2. Import> stop_job=immediate
3. Do the necessary changes.
4. Find the job name (SELECT * FROM DBA_DATAPUMP_JOBS;)
5. Attach to the job - impdp system/xxxxxxxxxxx attach=SYS_IMPORT_FULL_01
6. Restart the job
Import> start_job
7. Check the status
Import> status

Database Point In Time Recovery

@:oracle:/home/oracle> sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 9 18:09:45 2012

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, Oracle Label Security, OLAP,
Data Mining, Oracle Database Vault and Real Application Testing options

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2207208 bytes
Variable Size 1006633496 bytes
Database Buffers 587202560 bytes
Redo Buffers 7368704 bytes
Database mounted.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, Oracle Label Security, OLAP,
Data Mining, Oracle Database Vault and Real Application Testing options

@:oracle:/home/oracle> rman

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Jan 9 18:10:38 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target /

connected to target database: TEST (DBID=3150684, not open)

RMAN> connect catalog rman/rman@RMAN

connected to recovery catalog database

RMAN>RUN
{
SET UNTIL SEQUENCE 109; -- V$LOG_HISTORY
#Alternatives:
#SET UNTIL SCN 1000;
#SET UNTIL TIME 'Nov 15 2004 09:00:00';
RESTORE DATABASE;
RECOVER DATABASE;
}

executing command: SET until clause

Starting restore at 09-JAN-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=66 device type=DISK
flashing back control file to SCN 3267640

Finished restore at 09-JAN-12

Starting recover at 09-JAN-12
using channel ORA_DISK_1

starting media recovery


xxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxx

media recovery complete, elapsed time: 00:00:47
Finished recover at 09-JAN-12

RMAN> alter database open resetlogs;

database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN>

Creating RMAN Catalog Database

SQL> create tablespace rman
2 datafile '/datadir/rman01.dbf'
3 size 100M
4 autoextend on
5 maxsize 10G
6 extent management local;

Tablespace created.

oracle@rman:/home/oracle> sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 19 00:28:16 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create user rman
identified by rman
temporary tablespace temp
default tablespace rman
quota unlimited on users
quota unlimited on rman ;

User created.


SQL> GRANT RECOVERY_CATALOG_OWNER TO rman;

Grant succeeded.

oracle@rman:/home/oracle> rman CATALOG rman/rman@RMAN

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Dec 19 00:30:29 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to recovery catalog database

RMAN> CREATE CATALOG;

recovery catalog created

oracle@rman:/home/oracle> rman target system/oracle123@TARGETDB CATALOG rman/rman@RMAN

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Dec 19 00:44:23 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-01031: insufficient privileges



--------------------------------
Execute the below in target database for above error resolution

SQL> grant sysdba to system;

Grant succeeded.
--------------------------------

oracle@rman:/home/oracle> rman target system/oracle123@TARGETDB CATALOG rman/rman@RMAN

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Dec 19 00:47:07 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: TARGETDB (DBID=3477861)
connected to recovery catalog database
Recovery Manager: Release 11.2.0.1.0 - Production on Mon Dec 19 00:47:07 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

connected to target database: TARGETDB (DBID=3477861)
connected to recovery catalog database

RMAN> REGISTER DATABASE;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN>

Create a Restore Point

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=25G;

System altered.

SQL> alter system set db_recovery_file_dest='+DSKGRP';

System altered.

SQL> shu immediate
startup Database closed.
Database dismounted.

SQL> startup mount
ORACLE instance started.

Total System Global Area 6580703232 bytes
Fixed Size 2219168 bytes
Variable Size 3758097248 bytes
Database Buffers 2801795072 bytes
Redo Buffers 18591744 bytes
Database mounted.

SQL> ALTER DATABASE ARCHIVELOG ;

Database altered.

SQL> CREATE RESTORE POINT TEST_REC_PT GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL> shu immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL>startup
ORACLE instance started.
Total System Global Area 6580703232 bytes
Fixed Size 2219168 bytes
Variable Size 3758097248 bytes
Database Buffers 2801795072 bytes
Redo Buffers 18591744 bytes
Database mounted.
Database opened.

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 320
Next log sequence to archive 322
Current log sequence 322
SQL>

How to enable Automatic ArchiveLog in Oracle 11g Database

To check whether the database is in Archive log mode or not

SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination None
Oldest online log sequence 564
Current log sequence 566

Set the archival destination

SQL> alter system set log_archive_dest_1='LOCATION=PRMRYDSK_GRP' scope=both;

System altered.

Shutdown the database and start in Mount state

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area 8752304128 bytes
Fixed Size 2213792 bytes
Variable Size 1543506016 bytes
Database Buffers 7180648448 bytes
Redo Buffers 25935872 bytes
Database mounted.

Enable archive log

SQL>alter database archivelog;

Database altered.

Open the database
SQL> alter database open;

Database altered.

Shutdown and startup the DB.(This step is not mandatory)

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 8752304128 bytes
Fixed Size 2213792 bytes
Variable Size 1543506016 bytes
Database Buffers 7180648448 bytes
Redo Buffers 25935872 bytes
Database mounted.
Database opened.

Check whether the automatic archive log mode is enabled

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination PRMRYDSK_GRP
Oldest online log sequence 564
Next log sequence to archive 566
Current log sequence 566
SQL>

ORA-20600: The specified target is in the process of being deleted

Recived the following error while adding a database(which was removed earlier)through Oracle Grid Control 10.2.0.5.

Database Instance Configuration Result
TESTA:
java.sql.SQLException: ORA-20600: The specified target is in the process of being deleted.(target name = TESTA)(target type = oracle_database)(target guid = 48BA0AB2565D68E6437E6F5062A7426F) ORA-06512: at "SYSMAN.TARGETS_INSERT_TRIGGER", line 46 ORA-04088: error during execution of trigger 'SYSMAN.TARGETS_INSERT_TRIGGER' ORA-06512: at "SYSMAN.EM_TARGET", line 2117 ORA-06512: at "SYSMAN.MGMT_TARGET", line 2701 ORA-06512: at line 1


SQL> select TARGET_NAME from mgmt_targets_delete;

TARGET_NAME
-----------
TESTA


The above entry needs to be deleted.For the purose we tried executing the below script, which resulted in an error.

SQL> begin
2 mgmt_admin.delete_target('TESTA','oracle_database');
3 end;
4 /
begin
*
ERROR at line 1:
ORA-20206: Target does not exist:
ORA-06512: at "SYSMAN.MGMT_ADMIN", line 462
ORA-06512: at "SYSMAN.MGMT_ADMIN", line 624
ORA-06512: at line 2

After some google search we could find another proc to achieve the required.

SQL> begin
2 mgmt_admin.delete_target_internal('TESTA','oracle_database');
3 end;
4 /

PL/SQL procedure successfully completed.

SQL>


Once this was done, we were able to add a new database Grid Control Target.

Convert Standby Database to independent operational DB

We had a requirement to convert the existing standby database to an independent one.

This is the steps that we followed to achieve this.

1.Shutdown the primary database consistently.

2.Do a failover on the standby database by issuing the following command.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;

Database altered.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WAIT WITH SESSION SHUTDOWN;

Database altered.

SQL> alter database open ;

Database altered.

In the primary database comment/remove the following parameters:

*.log_archive_config
*.log_archive_dest_2
*.log_archive_dest_state_2='ENABLE'
*.dg_broker_start
*.standby_file_management

The above changes would prevent the log shipping to old standby database and dataguard brocker wont start.

If the "*.dg_broker_start" parameter is set to true, expect the following error while opening the database.

ORA-16649: possible failover to another database prevents this database from
being opened

Once the above changes are made, the database could be started independently.

ORA - 17628

Was recieving the following error during the creation of the standby database on 11.2 Oracle Database.

RMAN-00571: ==================================================
RMAN-00569: ====== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ==================================================
RMAN-03002: failure of Duplicate Db command at 06/07/2011 14:07:03
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on prmy1 channel at 06/07/2011 14:07:03
ORA-17628: Oracle error 19505 returned by remote Oracle server

Check the alert log file at standby location.The software will be trying to access/write into an non-existent directory.

Resolution:
Create the directory and give the read/write permission.

RMAN Error 04014

I was recieving the below error while creating stand by database through RMAN.

RMAN-04014: startup failed: ORA-01261: Parameter db_create_file_dest destination string cannot be translated

The value of parameter db_create_file_dest locaiton was proper and was present at both the source and destination servers with proper files permissions.

But the issue later turned out to be because of an improper settting of another parameter -control_files, which had a extra character added to it(yes ..by mistake).Anyway the issue was with the improper location setting.

Oracle Password file creation

This is one of the silly mistakes that I have commited quite often.
Trying to assign sysdba previlage to user and getting struck up, only to find the password file is not as per specification.Oracle requires password file to be in the format 'orapwd$SID'.It checks for the presence of the file at $ORACLE_HOME/dbs location.The file should be provided permission 4640.

Run the follwing after loading Oracle Database profile:
orapwd file=$ORACLE_HOME/dbs/orapwd$ORACLE_SID password=oracle entries=5

Change the permission of th created file to 4640.
Parameter REMOTE_LOGIN_PASSWORDFILE should have value remote/shared.

Once the above requirements are satisfied, users could be granted sysdba previlages.

Places to Go Before I Die

1. Leh,India - From Manali by Road
2. Alaska
3. New Zealand
4. Switzerland
5. Gurudongmar Lake , Sikkim,India
6. Santorini, Greece
7. Greenland
8. Pamukkale,Cappadocia -  Turkey
9. The Storr, Scotland
10. Anzio, Italy
11. 
    

The list is growing every day.... :-)

Helpful travel URL's

Sikkim Home Stay

http://www.mayallyang.com/

Oracle Database Cloning using Cold Backup

Steps to clone an Oracle Database (File System Based) using cold backup.
1.Shutdown the source database

2.Backup the datafiles.

3.Copy the datafiles to destination location.

4.Execute the command 'alter database backup controlfile to trace' from the source database.

5.Rename this file to create_db.sql

6.Modify the file for the following:
6.1 Remove the contecnt under NoResetlog Section.
6.2 Rename the work reuse to set
6.3 Update the Database Name
6.4 Comment the commands - RECOVER DATABASE USING ....
ALTER DATABASE OPEN RESETLOGS;
ALTER TABLESPACE TEMP ADD TEMPFILE
6.5 Modify the datafile locations in this file so as to reflect the new location in destination database.
7.Copy the initSID.ora file from the source and to destination location and modify it contents for new database.
8.At the destination location, set the oracle home and oracle sid parameters
9.Connect as Sysdba and execute the create_db.sql script.
10.Once the control file is created ,execute the command 'alter database open resetlogs;'
11.Bounce the database once and it is ready for use.

Oracle 10g Dataguard Implementation

Oracle Database Upgradation

To be published soon....

Oracle 10g Database Cloning

Database cloning onto a different server using hot backups

Server-1 - Source
Server-2 - Destination

Step - 1 - Put the source database in backup mode.

Step - 2 - Copy all the datafiles,redologfiles,parameter files and
archived log files from server 1 to server 2.

Step - 3 - At the source datbase sql prompt execute the following
command sqlplus>backup controlfile to trace.
(Output will be generated at udump,this will be henceforth
called create_db.sql)

Step - 4 - Copy this file to server-2

Step - 5 - Edit the create_db.sql :
5.1 So as to represent new locations.
5.2 Replace "Reuse" with "set" (CREATE CONTROLFILE set DATABASE)
5.3 Update the SID of Database.

Step - 6 - Restore the initialisation parameter file to
$ORACLE_HOME/dbs
Step - 7 - Edit the file with new db name and file locations.

Step - 8 - Set ORACLE_SID with new database name

Step - 9 - Connect as "sysdba"
[oracle]/ > sqlplus
SQL*Plus: Release 10.2.0.3.0 - Production on Mon Jul 27 20:47:17 2009
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Enter user-name: / as sysdba
Connected to an idle instance.

Step - 10

SQL> @create_db.sql
ORACLE instance started.

Total System Global Area 813694976 bytes
Fixed Size 2033504 bytes
Variable Size 528488608 bytes
Database Buffers 268435456 bytes
Redo Buffers 14737408 bytes

Control file created.



It is important to specify CANCEL when you are asked for the last log.

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 794271 generated at 12/22/2006 12:09:07 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/flash_recovery_area/DDB/archivelog/1_4_609804799.dbf
ORA-00280: change 794271 for thread 1 is in sequence #4

Specify log: {=suggested filename AUTO CANCEL}

{Specify the location of the log file here...if the log required is present in archivelog files,else press type cancel)

CANCEL
Media recovery cancelled.
SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL> Shu immediate
SQL>Startup

Add temp file and Database is ready to use.


Useful Links:
http://www.blogger.com/www.dba-oracle.com/oracle_tips_db_copy.htm
http://www.blogger.com/www.dba-oracle.com/topica/super_fast_database_cloning.htm
=================================================================================================

[oracle] > vi create_db.sql

STARTUP NOMOUNT
CREATE CONTROLFILE set DATABASE "TEST" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 1000
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 (
'/pre/RESTORE_ACTIVITY/redo1a.log',
'/pre/RESTORE_ACTIVITY/redo1b.log'
) SIZE 1000M,
GROUP 2 (
'/pre/RESTORE_ACTIVITY/redo2a.log',
'/pre/RESTORE_ACTIVITY/redo2b.log'
) SIZE 1000M,
GROUP 3 (
'/pre/RESTORE_ACTIVITY/redo3a.log',
'/pre/RESTORE_ACTIVITY/redo3b.log'
) SIZE 1000M
-- STANDBY LOGFILE
DATAFILE
'/pre/RESTORE_ACTIVITY/system01.dbf',
'/pre/RESTORE_ACTIVITY/undo_ts01.dbf',
'/pre/RESTORE_ACTIVITY/sysaux01.dbf',
'/pre/RESTORE_ACTIVITY/index01.dbf',
'/pre/RESTORE_ACTIVITY/data01.dbf',
CHARACTER SET UTF8;

Oracle 9i SQL Query Tuning Tips

The following are not de-facto rules.But quite helpful when tuning SQL query

Rule - 1
Avoid data type mismatch for index columns.

Rule - 2
Avoid functions on indexed columns

Rule - 3
Move conditions from having clause to where clause wherever possible.

Rule - 4
Use joins instead of nested selects. (Very Very Imp)

Rule - 5
When joining multiple tables the smallest table should be specified last.

Rule - 6
Replace NOT IN with NOT EXISTS.

Rule - 7
Replace != by UNION of “<“and “>” .

Rule - 9
Use UNION ALL instead of UNION wherever possible.

Rule - 10
Do not use columns on both sides of operator
When an indexed column appears on both sides of an operator, the index for that column is disabled.

Rule-11

Use Count(1) instead of count(*)

The COUNT function does not need to retrieve all fields from the employees table as it had to when you used the COUNT(*) syntax. It will merely retrieve the numeric value of 1 for each record that meets your criteria.

Useful Links

============
http://people.aapt.net.au/roxsco/tuning/
http://download.oracle.com/docs/html/A86647_01/vmqtune.htm
http://www.dba-oracle.com/art_sql_tune.htm

Oracle 9i Modifying settings in favour of Index scans

In a normal setting CBO in Oracle 9i gives equal preference to index scans and full table scans.But it is possible for the administrator to tweak the settings so as to make the optimiser favour index scan to full table scan.

optimizer_index_cost_adj is the parameter used for the purpose.By default its value is 100.Here it gives equal weightage to index scan as well as full table scan.By bringing that value down the CBO prefers for a index scan to full table scan.The value can range between 1-10000.

Error - ora-01034 - 8i STANDARD EDITION

CONNECTION PROBLEMS- 8i STANDARD EDITION (ora-01034 )
----------------------------------------------------------------------

GO TO THE "svrmgrl" file (in a 'BIN' file - do a search for files if not sure where it is). Double-click on it and a dos_style prompt will open. Type "connect internal" (without the quotes!) and press enter. Then type "startup open". Then wait a short while and the database will be opened. You will then be able to connect to sql, etc without getting this error.