Standby Database Creation Oracle 9ir1(9.0.1)

Standby Database Creation

Primary Database: 9ir1(9.0.1.0.1)

Standby Database: 9ir1(9.0.1.0.1)

Platform: windows XP 32bit

Standby & Primary Database Same System/Machine

Primary database name: ORA9I

Standby database name: ORA9ISTD

NOTE: Database Name should not be excced more than 8 character.

TNSENTRY Primary DB: ORA9I

TNSENTRY Standby DB: ora9istd

Location of datafile/controlfile/redofile

Primary site: $ORACLE_HOME/oradata/ORA9I

Standby site: c:\ora9istd

Perform following task at Primary database
1. Enable archivelog mode
2. take primary site database backup for standby
3. create standby controlfile
4. modify init.ora file at primary site for standby db
5. create init.ora file for standby site
6. startup standby database

1. How to enable archivelog mode

http://dbataj.blogspot.com/2007/09/how-to-enable-archivelog-mode.html

2. Backup

I am using HOT backup through USER MANAGED method

3. Create CONTROLFILE to standby database
SQL> alter database create standby controlfile as 'c:\ora9istd\control01.ctl';
Database altered.

4. COPY database backup and standby controlfile to STANDBY location

5. Modify init.ora file at primary site FOR PRIMARY DB.

standby_file_management=AUTO scope=spfile

STANDBY_ARCHIVE_DEST='LOCATION=c:\ora9i\archive'

log_archive_dest_1='LOCATION=c:\ora9ihome\rdbms'

log_archive_dest_2='SERVICE=ora9istd'

log_archive_dest_state_1=ENABLE

log_archive_dest_state_2=ENABLE

remote_archive_enable=TRUE

NOTE: Restare Oracle server ( shutdown + startup) to take effect of static parameter setting.

6. Modify init.ora file at primary site FOR STANDBY DB.

*.lock_name_space=ORA9I

*.FAL_SERVER=ora9istd

*.FAL_CLIENT=ora9i

7. Create standby init.ora file
use OS copy command to copy init.ora(primary site) and paste at standby site and modify below parameter.

*.STANDBY_ARCHIVE_DEST='LOCATION=c:\ora9istd\archive'
*.background_dump_dest='C:\ora9istd'
*.compatible='9.0.0'
*.control_files='C:\ora9istd\control01.ctl',

*.core_dump_dest='C:\ora9istd'
*.fal_client='ORA9ISTD'
*.fal_server='ORA9I'
*.instance_name='ora9iSTD'
*.lock_name_space='ORA9ISTD'
*.log_archive_dest_1='LOCATION=c:\ora9istd\archive'
*.log_archive_dest_state_1='ENABLE'
*.remote_archive_enable=TRUE
*.standby_archive_dest='LOCATION=c:\ora9istd\archive'
*.undo_tablespace='UNDOTBS'
*.user_dump_dest='C:\ora9istd'

8. Create standby services through ORADIM & create password file through ORAPWD and startup the standby database

C:\ora9ihome\BIN>oradim -NEW -SID ora9istd
C:\Ora9ihome\BIN>orapwd file=c:\ora9ihome\database\pwdora9istd.ora password=oracle entries=5

SQL> conn / as sysdba

Connected to an idle instance.

SQL> startup NOMOUNT pfile='c:\ora9istd\initora9istd.ora';

ORACLE instance started.
[output cut]

NOTE: STARTUP NOMOUNT is required.

SQL> alter database mount standby database;
Database altered.

10. RENAME DATAFILE OR REDOLOG FILE TO POING STANDBY LOCATION

SQL> alter database rename file

2 'C:\ORA9IHOME\ORADATA\ORA9I\INDX01.DBF',

3 'C:\ORA9IHOME\ORADATA\ORA9I\REDO01.LOG',

4 'C:\ORA9IHOME\ORADATA\ORA9I\REDO02.LOG',

5 'C:\ORA9IHOME\ORADATA\ORA9I\REDO03.LOG',

6 'C:\ORA9IHOME\ORADATA\ORA9I\SYSTEM01.DBF',

7 'C:\ORA9IHOME\ORADATA\ORA9I\TOOLS01.DBF',

8 'C:\ORA9IHOME\ORADATA\ORA9I\UNDOTBS01.DBF',

9 'C:\ORA9IHOME\ORADATA\ORA9I\USERS01.DBF'

10

to

11 'c:\ora9istd\INDX01.DBF',

12 'c:\ora9istd\REDO01.DBF',

13 'c:\ora9istd\REDO02.DBF',

14 'c:\ora9istd\REDO03.DBF',

15 'c:\ora9istd\SYSTEM01.DBF',

16 'c:\ora9istd\TOOLS01.DBF',

17 'c:\ora9istd\UNDOTBS01.DBF',

18 'c:\ora9istd\USERS01.DBF';
Database altered.


After that If required then create STANDBY redolog file then configure TNSNAME.ORA file for primary db will connect to standby database and standby database will connect to primary db.

Source: http://download.oracle.com/docs/cd/A91202_01/901_doc/server.901/a88808/stdbyconfig.htm#46558

ORA-00600 [150]

SQL> alter system set log_file_name_convert= 2 'c:\ora9ihome\oradata\ora9i\','c:\ora9istdy' scope=spfile;

alter system set log_file_name_convert=

*

ERROR at line 1:ORA-00600: internal error code, arguments: [150], [], [], [], [], [], [], []

During configuration of standby database i am getting above error.

It is known bug

check metalink note: Note:138414.1

Real Time Exprience about DB Upgrade

Dear friends,

I just upgraded our production box (windows & linux) from 10gr1 (10.1.0.2.0) to 10gr2 (10.2.0.4.0).

I want to share upgrade procedure with you all.

Keep in mind there is two types of upgrade procedure

1. From lower oracle verion to higher oracle version

For eg:

Oracle 8i,9i,10g to oracle 11g

or

Oracle 8i,9i to oracle 10g

or

Oracle 8i to oracle 9i

or

so on.

2. patch set or cpu(critical patch update) upgrade.

it is always apply on same oracle version means if we are using oracle 10gr1 then we can apply only latest patchset for 10gr1

For eg:

If we are using oracle 8i (8.1.0) then we can apply only latest patch set for 8i is (8.1.7.4) patchset

If we are using oracle 9ir1(9.0.1) then we can apply only latest patch set for 9ir1 is(9.0.1.4) (not sure about latest patchset )

If we are using oracle 9ir2(9.2.0) then we can apply only latest patch set for 9ir2 is (9.2.0.7) same like10gr1 ---10.1.0.5.0, 10gr2 ---10.2.0.4.0 , 11gr1 ---N/A

according to me apply patchset is easy and safest task compare to upgrade from lower oracle version to higher oracle version.

Oracle enhanced with every new release so some feature in lower version are obsolete & desupported in higher version. Same like in higher version new features are introduced which are not exist in lower version. And upgrade/migrate is one of the important task for DBA. so we have to do it

I always recommended never ever upgrade directly PRODUCTION box without prior testing on TEST/DEV server.

ORA-01422 + ORA-06512 + "SYSMAN.MGMT_TIME_SYNC"

Platform: Windows2003

During Patchset applying process from 10gr2(10.2.0.1.0) to 10gr2(10.2.0.4.0) "catupgrd.sql" ended with below error.

ERROR at line 1:

ORA-01422: exact fetch returns more than requested number of rows

ORA-06512: at "SYSMAN.MGMT_TIME_SYNC", line 193

ORA-06512: at line 2

All oracle component was successfully upgraded except OEM becuase of above error.

Clause: After searching on metalink and google didn't find proper reason why it is happened.

Solution: Re-Run catupgrd.sql script

ORA-01991: invalid password file

Oracle Version: 10gr1

OS: Windows 2003 Server

In development db i was getting below error message during database startup in mount stage.

SQL> startup

ORACLE instance started.
Total System Global Area 171966464 bytes

Fixed Size 787988 bytes

Variable Size 145488364 bytes

Database Buffers 25165824 bytes

Redo Buffers 524288 bytes

ORA-01991: invalid password file

'D:\oracle\product\10.1.0\Db_1\DATABASE\PWDspipr.ORA'

above error occured after i refreshed development db from production db.

Clause: I was restore production db hot backup (user mananged) to development db and re-create oracle services and forget to create password file. so everything is new but the password file is old one. that is why i was getting ABOVE error during startup database.

Solution: Re-create the password file.

1. Stop oracle services

D:\oracle\product\10.1.0\Db_1\BIN>net stop oracleserviceSPIPR

The OracleServiceSPIPR service is stopping.......

The OracleServiceSPIPR service was stopped successfully.

2. Re-create password file through ORAPWD utility

D:\oracle\product\10.1.0\Db_1\BIN>orapwd file=D:\oracle\product\10.1.0\Db_1\database\pwdSPIPR.ORA password=oracle entries=5 force=Y

NOTE: FORCE=Y will re-create password file and replace any existing passwordfile.

3. start oracle database services

D:\oracle\product\10.1.0\Db_1\BIN>net stART oracleserviceSPIPR

The OracleServiceSPIPR service is starting......

The OracleServiceSPIPR service was started successfully.

D:\oracle\product\10.1.0\Db_1\BIN>sqlplus / as sysdba

SQL*Plus: Release 10.1.0.2.0 - Production on Mon Aug 18 19:11:19 2008
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:

Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.
[output cut]

Database mounted.

Database opened.

Remote DB connection without Oracle Client

Today I come across one interesting thread on OTN where user want to connect remotely to database through SQL* PLUS & TOAD without "Oracle Client" installation.

Don't know about TOAD but we can connect SQLPLUS remotely to database without Oracle Client installation through OraCmd Utility.

You can Download OraCmd utility from below link it is 30 days trail version.

http://www.withdata.com/oracmd.html

Some Screen Shots

http://www.withdata.com/oracmd_screenshots.html

ksdpec: called for event 13740 prior to event group initialization

After upgrade db from 10gr1 to 10gr2 I got the following message in alertlog file during db startup time only.

ksdpec: called for event 13740 prior to event group initialization

starting up ORACLE RDBMS Version: 10.2.0.1.0

After searching on metalink and google I found below metalink note.

Metalink Note: 342505.1

http://oracleappstechnology.blogspot.com/2008/03/ksdpec-called-for-event-13740-prior-to.html

'Lb_library_path Is Not Set' On Startup

After successfully upgrade db from 10gr1(10.1.0.3.0) to 10gr2 (10.2.0.1.0) I got below warning message during database startup

Sat Aug 9 17:52:09 2008

Starting ORACLE instance(normal)

Cannot determine all dependent dynamic libraries for /proc/self/exe

Unable to find dynamic library libocr10.so in search paths

after searching on metalink I found below metalink note.

Metalink Note: 371408.1

According to metalink note it is a BUG:4918834.

Soluction: Set LD_LIBRARY_PATH enviourment variable.

$export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH

Memory Notification: Library Cache Object Loaded Into SGA

After upgrade db from 10gr1 to 10gr2(10.2.0.1.0) on linux server I got below warning message in alert_sid.log file.

Tue Aug 12 11:34:37 2008Memory Notification: Library Cache Object loaded into SGAHeap size 2205K exceeds notification threshold (2048K)

After searching on metalink and google I found below metalink note and link.

Metalink Note: 330239.1

http://sabdarsyed.blogspot.com/2007/03/warning-memory-notification-library.html

Upgrade DB from 10gr1 to 10gr2

Yesterday I upgrade our production box from 10gr1 to 10gr2 on linux platform.

Old Version: 10.1.0.3.0

New Version: 10.2.0.1.0

Platform: Linux 4

I am going to share with you short notes for upgrade process which i follow.

Steps:

1. Take backup of Oracle DB / Ora Inventory / Ora_home of old version

2. Install New oracle version with Seperate ORACLE_HOME directory

3. Run Pre-Upgrade Utility on database

4. Run catupgrd.sql script on database Under New Oracle Home.

5. Run utlrp.sql script for compile all invalid objects

6. Bounce the database (shutdown normal + startup)

7. Create spfile or change location of redolog or datafile if these files exists in "old oracle home".

8. Remove the old oracle home folder.

9. shutdown the database startup with spfile in mount mode then change location of datafile/redolog file then open the database

10. Take the database full backup.

11. Create the new temporary tablespace if old temporary tablespace is exist on old oracle home and make default temporary tablespace to database , rename the temp tablespace name

check also below metalink note: http://dbataj.blogspot.com/2008/08/ora-00959-tablespace-deleted00-does-not.html

Above steps is only short details for upgrade.

Kindly visit Oracle official upgrade guide http://tahiti.oracle.com/

ORA-00959 tablespace "_$deleted$0$0' Does Not Exist

Yesterday I upgrade the Production DB from 10gr1 (10.1.0.3.0) to 10gr2 (10.2.0.1.0) on Linux 4.

Upgrading process going fine and well. but after upgrading when I trying to export DB schema then I was getting below error

ORA-00959 tablespace '_$deleted$0$0' Does Not Exist

Then after I visit to metalink and found below metalink note: 604648.1

It is unpublish BUG.

Short Hint: Above Error occur after when we done below procedure after upgrade.

1. create new temp tablespace

2. make default temp tablespace to new one

3. drop old temp tablespace

4. rename new tablespace name to old tablespace name

After that we will get above error. for solution kindly visit mentioned metalink note.

Oracle 9i Restore Backup on different host

As a DBA this task we perform so many times.

In the following cases.

1. Create CLONE database

2. Create same copy as PRODUCTION database

3. TEST Production Database Backup

4. Update TEST & UAT database with Production Database

To complete above fours point we require Production Database Backup, In am using Oracle 9ir1 backup for this demostration.

How to Take Oracle 9i Online User Managed Backup

The following steps will required to complete above task.

1. Create Oracle Services

2. Create Password file

3. Edit following parameter location

1. bdump 2. udump 3. cdump 4. log_archive_dest 5. controlfile location

4. connect with SYSDBA user and create spfile from backup pfile

then startup database in nomount mode

5. Restore controlfile from backup and mount the database

6. If source and target database file directory structure is different then rename all datafile to new target location.

7. after rename all datafile & redolog files location restore all archivelog files from backup and open database with UNTIL CANCEL option.

(In this demo target or source db location is same)

8. open database with RESETLOGS.

C:\Ora9ihome\BIN>oradim -new -sid ORA9I

C:\Ora9ihome\BIN>orapwd file=C:\Ora9ihome\database\pwdORA9I.ora password=oracle entries=5

C:\Ora9ihome\BIN>set oracle_sid=ora9i

C:\Ora9ihome\BIN>sqlplus /nolog
SQL*Plus: Release 9.0.1.0.1 - Production on Sat Aug 2 17:21:38 2008
(c) Copyright 2001 Oracle Corporation.

All rights reserved.

SQL> conn sys as sysdba

Enter password:

Connected to an idle instance.

SQL> create spfile from pfile='e:\ora9i\backup\init.ora';
File created.

SQL> startup nomount

ORACLE instance started.
Total System Global Area 118255568 bytes

Fixed Size 282576 bytes

Variable Size 83886080 bytes

Database Buffers 33554432 bytes

Redo Buffers 532480 bytes

SQL> host copy e:\ora9i\backup\*.ctl c:\ora9ihome\oradata\ora9i

e:\ora9i\backup\CONTROL01.CTL

e:\ora9i\backup\CONTROL02.CTL

e:\ora9i\backup\CONTROL03.CTL

3 file(s) copied.

SQL> alter database mount;
Database altered.

SQL> host copy e:\ora9i\backup\*.dbf c:\ora9ihome\oradata\ora9i

e:\ora9i\backup\INDX01.DBF

e:\ora9i\backup\SYSTEM01.DBF

e:\ora9i\backup\TOOLS01.DBF

e:\ora9i\backup\UNDOTBS01.DBF

e:\ora9i\backup\USERS01.DBF

5 file(s) copied.

SQL> set autorecovery on

SQL> recover database using backup controlfile until cancel;

SQL> alter database open resetlogs;
Database altered.

Oracle 9i Online User Managed Backup

Online backup through User Managed.

Oracle Version: 9.0.1.1.1

OS: Windows (But you can apply below procedure for all platforms)

Step:1

Before going further understand what is important files for backup.

1. Controlfile

2. Datafiles

3. SPFILE/PFILE

4. Redolog files

5. Tempfiles

First three points and files are most important and part of the database. so in online hot backup we need to take only first tell files backup.

Forget about Point 4,5, why...? becuase redolog files copy we get as a "archivelog files" and tempfile is temparory files, this file doesn't store user data. and doesn't important & required for database. suppose if we lost tempfile then we create new tempfile for database.

So we need to consider only three points from 1 to 3 (above).

The procedure is follow

1. Put tablespace in backup mode.

2. copy the datafiles which tablespace in backup mode

3. once copy process is complete putout the tablespace from backup mode

4. switch the logfile.

For example:

The following is the tablespace which we need to take backup.

SQL> select tablespace_name from dba_data_files;
TABLESPACE_NAME

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

SYSTEM

UNDOTBS

INDX

TOOLS

USERS

SQL> alter tablespace SYSTEM begin backup;
Tablespace altered.

SQL> host copy c:\ora9ihome\oradata\ora9i\system01.dbf e:\ora9i\backup

1 file(s) copied.

SQL> --we can check which tablespace/datafile is currently in backup mode

SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME

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

1 ACTIVE 120148 02-AUG-08

2 NOT ACTIVE 0

3 NOT ACTIVE 0

4 NOT ACTIVE 0

5 NOT ACTIVE 0

SQL> alter tablespace SYSTEM end backup;
Tablespace altered.

SQL> select * from v$backup;
FILE# STATUS CHANGE# TIME

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

1 NOT ACTIVE 120148 02-AUG-08

2 NOT ACTIVE 0

3 NOT ACTIVE 0

4 NOT ACTIVE 0

5 NOT ACTIVE 0

Repeat above steps for all tablespace which we want to take backup. always check v$backup view for any datafile is still in backup mode, if you found any which datafile already copied to backup location and still showing in backup mode then putout that tablespace in backup mode through "alter tablespace TABLESPACE NAME end backup;"

Once you complete all datafile backup then force log switch.

SQL> ALTER SYSTEM SWITCH LOGFILE;
System altered.

After taking all datafiles backup now turn is controlfile backup.

SQL> alter database backup controlfile to

'e:\ora9i\backup\control01.ctl';

Database altered.

Above statement create BINARY controlfile backup copy.

After datafiles and controlfile backup now turn is init.ora (spfile) backup.

SQL> create pfile='e:\ora9i\backup\init.ora' from spfile;
File created.

---here Oracle 9i backup procedure is complete---

Oracle 9i Can't allocate log, archival required

When your getting below error message means you enable the archivelog mode for Oracle 9i database but still didn't "start" archive.

For example:

In oracle 9i we need to enable archivelog mode as well as we need to start.

1. LOG_ARCHIVE_START parameter we need to also set for start archivelog mode.

see below:

SQL> archive log list

Database log mode Archive Mode

Automatic archival Disabled

Archive destination C:\Ora9ihome\RDBMS

Oldest online log sequence 4

Next log sequence to archive 4

Current log sequence 6

In above Database is archive log mode but archiving is not start means following parameter is not TRUE.

SQL> show parameter log_archive_start
NAME TYPE VALUE

------------------------------------ ----------- -----------log_archive_start boolean FALSE

LOG_ARCHIVE_START is static parameter so we need to restart Oracle database, ( startup followed by shutdown)

SQL> alter system set log_archive_start=TRUE scope=spfile;
System altered.

SQL> shutdown immediate;

SQL> startup

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination C:\Ora9ihome\RDBMS
Oldest online log sequence 5
Next log sequence to archive 7
Current log sequence 7

What is HOT / COLD backup?

There is two types of backup we can take for Oracle Database.

1. COLD/OFFLINE/CONSISTENT backup

What is cold backup and why we say "cold" backup?

When database is DOWN, no activity running on database, no one accessing the database. that time taken database backup called "COLD BACKUP". We can also say "OFFLINE" database backup.

In short:

COLD backup equal to OFFINE backup

COLD backup equal to CONSISTENT backup

For COLD/OFFLINE/COSISTENT database backup we must need to SHUTDOWN Oracle Database with the following option.

1. SQL>shutdown normal

2. SQL>shutdown immediate

3. SQL>shutdown transactional;

4. SQL>shutdown abort;

5. CMD>net stop OracleService

For Example of cold backup on any OS platforms.

1. conn with sysdba user.

2. shutdown oracle database

3. copy init.ora, all datafiles, all redologs files, all controlfile to backup location

4. startup oracle database

NOTE: Database doesn't require ARCHIVELOG mode for COLD backup.

2. What is HOT/ONLINE/INCONSISTENT backup?

When database is open, user accessing the database that time we taken backup is called "HOT,ONLINE, inconsistent" backup.

NOTE: Database must require ARCHIVELOG mode for HOT backup.

For HOT backup we have two options

1. RMAN Recovery Manager (Server Managed Backup)

2. User Managed Backup (User Managed backup)