ORACLE DBA Knowledge Share: July 2007

Some Useful Links

http://www.oracle.com/technology/obe/demos/admin/demos.html

http://www.oracle.com/technology/obe/demos/10gr2/RAC_CSS_Diag/10gR2_Vote_Diag_viewlet_swf.html

http://www.oracle.com/technology/obe/demos/10gr2/10gR2_RAC_Obj_Affinity/10gR2_RAC_Obj_Affinity_viewlet_swf.html
http://sysdba.wordpress.com/category/rac/

http://www.oracle.com/technology/pub/articles/smiley_rac10g_install.html

http://oraclezone.wordpress.com/rac/

http://download-west.oracle.com/docs/cd/B19306_01/rac.102/b14197/admcon.htm#CJHFFBJJ

http://blogs.oracle.com/AlejandroVargas/stories/storyReader$15

http://oraclezone.wordpress.com/2007/11/18/weird-rac-issue-due-to-an-incorrect-setup/

http://www.cyberciti.biz/tips/linux-bond-or-team-multiple-network-interfaces-nic-into-single-interface.html

something about sqlldr

Oracle Installer check of prerequisite

this check is not done when used switch ignoreSysPrereqs with runInstaller.

further you can help to ignore warnings but not errors.

Ignoring these errors may later need fixing same warnings.

So follow the check of prerequisite and install all necessary OS pacthes/RPMs or oracle supplied rpms OS patch.

below is how 10gR1 prerequisite or checked on RHEL 3 :

1.Checking operating system certification
Expected result: One of redhat-2.1,redhat-3,UnitedLinux-1.0
Actual Result: redhat-3
Check complete. The overall result of this check is: Passed
=======================================================================

Checking kernel parameters
Checking for VERSION=2.4.9.25; found VERSION=2.4.21. Passed
Checking for shmall=2097152; found shmall=2097152. Passed
Checking for shmseg=10; found shmseg=4096. Passed
Checking for semmsl=250; found semmsl=250. Passed
Checking for semmni=128; found semmni=128. Passed
Checking for filemax=65536; found filemax=65536. Passed
Checking for shmmni=4096; found shmmni=4096. Passed
Checking for semmns=32000; found semmns=32000. Passed
Checking for semopm=100; found semopm=100. Passed
Checking for shmmin=1; found shmmin=1. Passed
Checking for shmmax=2147483648; found shmmax=2147483648. Passed
Check complete. The overall result of this check is: Passed
=======================================================================

Checking recommended operating system packages
Checking for make-3.79; found make-3.79.1-17. Passed
Checking for binutils-2.11.90.0.8-12; found binutils-2.14.90.0.4-26. Passed
Checking for gcc-2.96; found gcc-3.2.3-20. Passed
Checking for openmotif-2.1.30-11; found openmotif-2.2.2-16. Passed
Check complete. The overall result of this check is: Passed
=======================================================================

Checking recommended glibc version
Expected result: 2.2.4.31.7
Actual Result: 2.3.2.95.3
Check complete. The overall result of this check is: Passed
=======================================================================

Validating ORACLE_BASE location (if set)
Check complete. The overall result of this check is: Passed
=======================================================================

*****************************************8
************************
*************

9/10g install on Linux/Solaris

see other post http://orababy.blogspot.com/2007/08/installing-9i10g-on-rhel-3.html for clean steps for linux only

Install Oracle 9i Database on Linux RHEL AS 3

the following lines can be added to the /etc/sysctl.conf file:
kernel.shmmax = 2147483648
kernel.shmmni = 128
kernel.shmall = 2097152
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
In addition the following lines can be added to the /etc/security/limits.conf file:
oracle soft nofile 65536
oracle hard nofile 65536
oracle soft nproc 16384
oracle hard nproc 16384
Create the new groups and users:
groupadd oinstall
groupadd dba
useradd -g oinstall -G dba oracle
passwd oracle
Create the directories in which the Oracle software will be installed:
mkdir /u01
chown -R oracle.dba /u01
Login as the oracle user and add the following lines at the end of the .bash_profile file:
# Oracle 9i
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/9.2.0.1.0
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=orcl
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib
export LD_ASSUME_KERNEL=2.4.1
Install the 3006854 patch:
unzip p3006854_9204_LINUX.zip
cd 3006854
sh rhel3_pre_install.sh
/mnt/cdrom/runInstaller
When the ins_oemagent.mk error appears install the 3119415 patch:
unzip p3119415_9203_LINUX.zip
cd 3119415
sh patch.sh
Once this is complete click the retry button. Ignore the ins_ctx.mk error and all other .mk errors that come further in installation as this is fixed in the 9.2.0.4.0 patch. The installation is now complete.

Install Oracle 10g Database on Linux 3 AS

Alternatively the following lines can be added to the /etc/sysctl.conf file:
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.shmall = 2097152
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
In addition the following lines can be added to the /etc/security/limits.conf file:
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft nproc 2047
oracle hard nproc 16384
vi /etc/pam.d/login
session required /lib/security/pam_limits.so
Create the new groups and users:
groupadd oinstall
groupadd dba
useradd -g oinstall -G dba oracle
passwd oracle
Create the directories in which the Oracle software will be installed:
mkdir /u01
chown oracle.dba /u01
chmod 777 /u01
Login as the oracle user and add the following lines at the end of the .bash_profile file:
# Oracle 9i
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/9.2.0.1.0
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=orcl
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:
export CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib;
export LD_ASSUME_KERNEL=2.4.1
Install the 3006854 patch:
unzip p3006854_9204_LINUX.zip
cd 3006854
sh rhel3_pre_install.sh
/mnt/cdrom/runInstaller
The installation is now complete.

On solaris +mix

Checking memory/swap etc.
1./usr/sbin/swap –l
2 . check df –h /tmp and if required create tmp and export it along
with tmpdir
3. /usr/sbin/prtconf grep “Memory Size”
4. df –g grep “block size”
5. ulimit –Sa
6. ulimit –Ha
7. id oracle
8. id nobody
9. tar xvf ora******.tar
9.check for availability of required executables
/usr/bin/which make
/usr/bin/which ar
/usr/bin/which ld
/usr/bin/which nm
10.$ export DISPLAY=hostname:0.0
$xclock
11.to determine system architecture /bin/isainfo –kv
12. find version of solaris uname –r
11. pkginfo –i SUNWarc SUNWbtool SUNWhea SUNWlibm SUNWlibms SUNWsprot
\ SUNWbtoo SUNWi1cs SUNWi10f SUNWsprox SUNWxefnt
SUNWi15cs
13. finding showrev –p
Adding patch /usr/sbin/patchadd –p grep patch_number (without version)
Sunsolve.sun.com
Verify hostname set by /etc/hosts only
$Cat etc/nsswitch.conf grep hosts
$hostname
$domainname
$cat /etc/hosts grep ‘eval hostname’
$/usr/sbin/patchadd –p grep 111713
14. Finding release $cat /etc/release
15. edit /etc/system file
1. set shmsys:shminfo_shmmax=4294967295
2. set shmsys:shminfo_shmmin=1
3. set shmsys:shminfo_shmmni=100
4. set shmsys:shminfo_shmseg=10
5. set semsys:seminfo_semmns=2000
6. set semsys:seminfo_semmsl=1000
7. set semsys:seminfo_semmni=100
16. id –a oracle
groupadd -g 115 dba
/usr/sbin/useradd -u 175 –c “oracle software owner.” –g
oinstall –G dba ,oper
-d /u01/app/oracle -m -s /bin/ksh oracle
id nobody
.profile
unzip xyz.zip
cpio –idmv

------

pkginfo -i SUNWarc SUNWbtool SUNWhea SUNWlibm SUNWlibms SUNWsprot \
SUNWsprox SUNWtoo SUNWi1of SUNWi1cs SUNWi15cs SUNWxwfnt

Profile :

$ pkginfo -i SUNWarc SUNWbtool SUNWhea SUNWlibm SUNWlibms SUNWsprot \
SUNWsprox SUNWtoo SUNWi1of SUNWi1cs SUNWi15cs SUNWxwfnt>
system SUNWarc Lint Libraries (usr)
system SUNWbtool CCS tools bundled with SunOS
system SUNWhea SunOS Header Files
system SUNWi15cs X11 ISO8859-15 Codeset Support
system SUNWi1cs X11 ISO8859-1 Codeset Support
system SUNWi1of ISO-8859-1 (Latin-1) Optional Fonts
system SUNWlibm Math & Microtasking Library Headers & Lint Files (Usr)
system SUNWlibms Math & Microtasking Libraries (Usr)
system SUNWsprot Solaris Bundled tools
system SUNWtoo Programming Tools
system SUNWxwfnt X Window System platform required fonts
ERROR: information for "SUNWsprox" was not found

$ cat .profile
ORACLE_BASE=/opt/oracle/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/10.2.4/db_1
LD_LIBRARY_PATH=$ORACLE_HOME/lib
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/lib/lwp
LD_LIBRARY_PATH_64=/usr/lib/lwp/64:$LD_LIBRARY_PATH_64
ORACLE_SID=mrxprod
#PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_BASE ORACLE_HOME LD_LIBRARY_PATH LD_LIBRARY_PATH_64 ORACLE_SID PATH
$

$ cat /etc/system
*ident "@(#)system 1.18 97/06/27 SMI" /* SVR4 1.5 */
*
* SYSTEM SPECIFICATION FILE
*

* moddir:
*
* Set the search path for modules. This has a format similar to the
* csh path variable. If the module isn't found in the first directory
* it tries the second and so on. The default is /kernel /usr/kernel
*
* Example:
* moddir: /kernel /usr/kernel /other/modules

* root device and root filesystem configuration:
*
* The following may be used to override the defaults provided by
* the boot program:
*
* rootfs: Set the filesystem type of the root.
*
* rootdev: Set the root device. This should be a fully
* expanded physical pathname. The default is the
* physical pathname of the device where the boot
* program resides. The physical pathname is
* highly platform and configuration dependent.
*
* Example:
* rootfs:ufs
* rootdev:/sbus@1,f8000000/esp@0,800000/sd@3,0:a
*
* (Swap device configuration should be specified in /etc/vfstab.)

* exclude:
*
* Modules appearing in the moddir path which are NOT to be loaded,
* even if referenced. Note that `exclude' accepts either a module name,
* or a filename which includes the directory.
*
* Examples:
* exclude: win
* exclude: sys/shmsys

* forceload:
*
* Cause these modules to be loaded at boot time, (just before mounting
* the root filesystem) rather than at first reference. Note that
* forceload expects a filename which includes the directory. Also
* note that loading a module does not necessarily imply that it will
* be installed.
*
* Example:
* forceload: drv/foo

* set:
*
* Set an integer variable in the kernel or a module to a new value.
* This facility should be used with caution. See system(4).
*
* Examples:
*
* To set variables in 'unix':
*
* set nautopush=32
* set maxusers=40
*
* To set a variable named 'debug' in the module named 'test_module'
*
* set test_module:debug = 0x13
noexec_user_stack=1
set semsys:seminfo_semmni=100
set semsys:seminfo_semmns=1024
set semsys:seminfo_semmsl=256
set semsys:seminfo_semvmx=32767
set shmsys:shminfo_shmmax=17179869184
set shmsys:shminfo_shmmin=1
set shmsys:shminfo_shmmni=100
set shmsys:shminfo_shmseg=10
$

groupadd oinstall
groupadd dba

mkdir /opt/oracle
useradd -g oinstall -G dba -d /opt/oracle oracle

chown oracle:oinstall /opt/oracle -R

bash-3.00$ cd ~
bash-3.00$ cat .profile
ORACLE_BASE=/opt/oracle/app/oracle
ORACLE_HOME=$ORACLE_BASE/product/10.2.4/db_1
LD_LIBRARY_PATH=$ORACLE_HOME/lib
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/lib/lwp
LD_LIBRARY_PATH_64=/usr/lib/lwp/64:$LD_LIBRARY_PATH_64
ORACLE_SID=mrxprod
#PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_BASE ORACLE_HOME LD_LIBRARY_PATH LD_LIBRARY_PATH_64 ORACLE_SID PATH

Enabling command history in sqlplus on linux

command history in linux :tested in rhel3 , other
flavours esp. fedora may require slight modification

downlaod rpm file rlwrap_0.18_1.i386.rpm (24k)
Install the atatched package by going in the download
location of the attached rpm from root user.

rpm -ivh rlwrap-0.18-1.i386.rpm

and then
add following lines to the .bashrc

alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'
alias exp='rlwrap exp'

logout and login back ,now your oracle command line
tool behaves same as in windows command history.

illustration example - Migrating dictionary managed tablespace to locally managed

SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TEMPD')
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TEMPD'); END;
*
ERROR at line 1:
ORA-03245: Tablespace has to be dictionary managed, online and permanent to be able to migrate
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('USERSD')
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM')
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); END;
*
ERROR at line 1:
ORA-10643: Database should be mounted in restricted mode and Exclusive mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP RESTRICT
ORACLE instance started.
Total System Global Area 160504432 bytes
Fixed Size 453232 bytes
Variable Size 125829120 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL>
SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM')
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); END;
*
ERROR at line 1:
ORA-10647: Tablespace other than SYSTEM,UNDOTBS1, TEMPD not found in read only mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

SQL> SELECT * FROM V$TABLESPACE;
TS# NAME INC
---------- ------------------------------ ---
0 SYSTEM YES
1 UNDOTBS1 YES
2 TEMP YES
3 USERS YES
5 USERSD YES
6 TEMPD YES
7 SMALL YES
8 TEMPL YES
8 rows selected.
SQL> ALTER TABLESPACE USERS READ ONLY;
Tablespace altered.
SQL> ALTER TABLESPACE SMALL READ ONLY;
Tablespace altered.
SQL> ALTER TABLESPACE USERSD READ ONLY;
Tablespace altered.
SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM')
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); END;
*
ERROR at line 1:
ORA-10647: Tablespace other than SYSTEM,UNDOTBS1, TEMPD not found in read only mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

SQL> DROP TABLESPACE TEMPD;
DROP TABLESPACE TEMPD
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPL ;
Database altered.
SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM')
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); END;
*
ERROR at line 1:
ORA-10647: Tablespace other than SYSTEM,UNDOTBS1, TEMPL not found in read only mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

SQL> DROP TABLESPACE TEMPD;
Tablespace dropped.
SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM')
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); END;
*
ERROR at line 1:
ORA-10647: Tablespace other than SYSTEM,UNDOTBS1, TEMPL not found in read only mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

SQL> SELECT NAME FROM V$TABLESPACE;
NAME
------------------------------
SYSTEM
UNDOTBS1
TEMP
USERS
USERSD
SMALL
TEMPL
7 rows selected.
SQL> DROP TABLESPACE &1;
Enter value for 1: USERS
old 1: DROP TABLESPACE &1
new 1: DROP TABLESPACE USERS
Tablespace dropped.
SQL> /
Enter value for 1: USERSD
old 1: DROP TABLESPACE &1
new 1: DROP TABLESPACE USERSD
DROP TABLESPACE USERSD
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option

SQL> DROP TABLESPACE USERSD INCLUDING CONTENTS ;
Tablespace dropped.
SQL> DROP TABLESPACE &1;
Enter value for 1: SAMLL
old 1: DROP TABLESPACE &1
new 1: DROP TABLESPACE SAMLL
DROP TABLESPACE SAMLL
*
ERROR at line 1:
ORA-00959: tablespace 'SAMLL' does not exist

SQL> /
Enter value for 1: SMALL
old 1: DROP TABLESPACE &1
new 1: DROP TABLESPACE SMALL
Tablespace dropped.
SQL> /
Enter value for 1: TEMPL
old 1: DROP TABLESPACE &1
new 1: DROP TABLESPACE TEMPL
DROP TABLESPACE TEMPL
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace

SQL> SELECT NAME FROM V$TABLESPACE;
NAME
------------------------------
SYSTEM
UNDOTBS1
TEMP
TEMPL
SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM')
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); END;
*
ERROR at line 1:
ORA-10647: Tablespace other than SYSTEM,UNDOTBS1, TEMPL not found in read only mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

SQL> DROP TABLESPACE TEMP;
Tablespace dropped.
SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM')
PL/SQL procedure successfully completed.
SQL> CREATE TABLESPACE TESTD
2 DATAFILE 'TESTD'
3 EXTENT MANAGEMENT DICTIONARY ;
CREATE TABLESPACE TESTD
*
ERROR at line 1:
ORA-12913: Cannot create dictionary managed tablespace

SQL> SHOW PARAMETER READ
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 16
parallel_threads_per_cpu integer 2
read_only_open_delayed boolean FALSE
thread integer 0
SQL> CREATE TABLESPACE TESTD
2 DATAFILE 'TESTD' ;
CREATE TABLESPACE TESTD
*
ERROR at line 1:
ORA-01119: error in creating database file 'TESTD'
ORA-17610: file 'TESTD' does not exist and no size specified
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

SQL> ED
Wrote file afiedt.buf
1 CREATE TABLESPACE TESTD
2* DATAFILE 'TESTD' SIZE 1M
3
SQL>
SQL> /
Tablespace created.
SQL> ALTER TABLESPACE TESTD READ ONLY ;
Tablespace altered.

Out of deep Woods-Thanks Heaven ORA-26500: error on caching "SALES"."TRANSACT

Few days back I found a huge index on my main transaction table in primary production database(9i RAC environment), which I suspected was not used by any query. So I decided to monitor its usage using oracle 9i Monitoring usage command. And after monitoring it for a week I found it was not used by any query and I dropped it just before production hour began at monday after weekly backup and other maintenance activities were finished. This table was replicated to three other databases using updateable materialized view setup.So this production database was mater site for replication.

To meet my surprise I found that even though 45 minutes of production time elapsed no new records were append to the main transaction table whose unused function based index was dropped.
In my database alert log I got very weird error in both my RAC instances
Errors in file d:\oracle\admin\ctdp\udump\ctdp1_ora_424.trc
:ORA-26500: error on caching "SALES"."TRANSACT" "

search on metalink and Google suggested
DBMS_REPUTIL.MAKE_INTERNAL_PKG ('EDBMS','TEST');
DBMS_REPUTIL.SYNC_UP_REP('EDBMS','TEST');
but it was riskier too as said links.

Then I decided to regenerate replciation support.

took following steps on repadmin user at master site

begin
dbms_repcat.suspend_master_activity(gname=>'master_replication_group_name');
end;/
begin
dbms_repcat.generate_replication_support( sname => 'schemanameofmastertable',
oname => 'tablenamewhichisrepliciated',
type => 'TABLE',
min_communication => TRUE);
end;
/

begin
dbms_repcat.resume_master_activity(gname=>'master_replication_group_name');
end;
/

thanks God it worked.

Now It was clear the culprit was poor documentation of Oracle as oracle says only that you need to regenerate replication support when you change the object only. But I did not change the master table .I dropped its one index and that can not be said change to master table.Thats only a indirect change not a change to structure of DB .

setting up 10g physical standby database

STEPS for creating 10g dataguard

prerequisite : 9i dataguard setup knowledge

Step1 : Prepare initSID.ora file for primary and standby databases as follow.

** STANDBY setup parameters are given in bold and highlighted

part A)

**** Production database primary file ****

prod.__db_cache_size=125829120
prod.__java_pool_size=4194304
prod.__large_pool_size=4194304
prod.__shared_pool_size=79691776
prod.__streams_pool_size=0
*.audit_file_dest='C:\oracle\product\10.2.0\admin\prod\adump'
*.background_dump_dest='C:\oracle\product\10.2.0\admin\prod\bdump'
*.compatible='10.2.0.3.0'
*.control_files='Q:\oradata\prod\control01.ctl','Q:\oradata\prod\control02.ctl','Q:\oradata\prod\control03.ctl'
*.core_dump_dest='C:\oracle\product\10.2.0\admin\prod\cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='prod'
*.db_recovery_file_dest='C:\oracle\product\10.2.0\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648

*.instance_name='prod'
*.job_queue_processes=10

*.db_unique_name='prod'
*.fal_client='prod'
*.fal_server='prod_stdby'
*.log_archive_config='DG_CONFIG=(prod,stdby)'
*.log_archive_dest_1='LOCATION=Q:\oradata\prod\arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod'
*.log_archive_dest_2='SERVICE=prod_stdby VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdby LGWR ASYNC REOPEN=10'
*.remote_login_passwordfile='EXCLUSIVE'

*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t%s%r.arc'
*.open_cursors=300
*.pga_aggregate_target=72351744
*.processes=150
*.service_names='prod'
*.sga_target=218103808
*.standby_archive_dest='q:\arch'
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='C:\oracle\product\10.2.0\admin\prod\udump'

Part B)

**** standby database primary file ****

*.compatible='10.2.0.3.0'
*.control_files='C:\oradata\prod\stdby.ctl'
*.instance_name='stdby'
*.db_name='prod'
*.db_unique_name='stdby'
*.fal_client='prod_stdby'
*.fal_server='prod'
*.log_archive_config='DG_CONFIG=(prod,stdby)'
*.log_archive_dest_1='LOCATION=C:\oradata\prod\stdby\arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stdby'
*.log_archive_dest_2='SERVICE=prod VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod LGWR ASYNC REOPEN=10'
*.db_file_name_convert='Q:\','C:\'
*.log_file_name_convert='Q:\','C:\'
*.remote_login_passwordfile='EXCLUSIVE'

*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r'
*.service_names='stdby'
*.shared_pool_size=104857600
*.standby_file_management='AUTO'

STEP 2:

part A)

shutdown primary database cleanly and copy datafiles to standby location.

Part B) create standby controlfile by giving following command at production database at mount stage

alter databse create standby controlfiel as 'location\filename' ;

move this generated file to standby controlfile location as pointed by standby initSID.ora file

step 3)
create oracle service and password file with the same password at standby database location

step 4)
prepare the TNSNAMES.ORA and LISTENER.ora at both production and standy locations.

To check archive log gap

SELECT * FROM (
SELECT sequence#, archived, applied,
TO_CHAR(completion_time, 'RRRR/MM/DD HH24:MI') AS completed
FROM sys.v$archived_log
ORDER BY sequence# DESC)
WHERE ROWNUM <= 10

ORACLE DBA Knowledge Share

Translate

Search This Blog