Monday, August 2, 2010
ASM
What is the ASM (Automatic Storage Management)
- Intrgrated file system and volume manager expressly build for oracle database files.
- ASM Provide performance of raw I/O with easy management of file system.
ASM – Extends the power of oracle managed files. ASM files created and managed automatically by YOU.
One additional feature Mirroring and Striping.
ASM Mirroring more flexible than operating system mirrored disks because ASM mirroring enables the redundancy level.
ASM Mirroring Options
Mirroring Option Description
2-way mirroring Each extent has 1 mirrored copy.
3-way mirroring Each extent has 2 mirrored copies.
Unprotected ASM provides no mirroring. Used when mirroring is provided by the disk subsystem itself.
Dynamic Storage Configuration
- ASM enable you have to change storage configuration without having to take the database offline.
- It’s Automatically rebalance
- If disk failure error occur; ASM automatically rebalance to restore full redundancy
Failure Groups
Failure Groups determine ASM disk that share common potential failure mechanism.
Errors in ASM
ORA-29701: Unable to connect to Cluster Manager
ORA-29701: Unable to connect to Cluster Manager
Every time I would try to start an ASM instance I would get this error:
Today In my ASM environment; When I try to start asm instance i got the below error message.
ORA-29701: Unable to connect to Cluster Manager
This is error releated to cluster manager goes down. We need to start manager using "localconfig add/delete"
1/ Log in as roor user
2/ Set your ORACLE_HOME
3/ execute the below command
$ORACLE_HOME/bin/localconfig delete
$ORACLE_HOME/bin/localconfig add
In Windows
C:\Documents and Settings\Baskar>SET ORACLE_SID=+ASM
C:\Documents and Settings\Baskar>set ORACLE_HOME=f:\oracle\product\10.2.0\db_1
C:\Documents and Settings\Baskar>%ORACLE_HOME%\bin\localconfig delete
Step 1: stopping local CSS stack
Step 2: deleting OCR repository
successfully deleted local CSS setup
C:\Documents and Settings\Baskar>
C:\Documents and Settings\Baskar>%ORACLE_HOME%\bin\localconfig add
Step 1: creating new OCR repository
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'baskar', privgrp ''..
Operation successful.
Step 2: creating new CSS service
successfully created local CSS service
successfully added CSS to home
Feel free post your comments.
ORA-12547: TNS:lost contact During ASM Instance Creation
Hi,
Using RHEL 4 & 10.2.0.4; I was trying to configure two node RAC Cluster. When i try configure ASM instance creation using DBCA I got the below error message.
ORA-12547: TNS:lost contact
Cluster Services & LISTENER working fine. But still not able to create ASM instance.
Work Around I:
1. Close your DBCA Session
2. Stop your cluster listener in your all the nodes
3. $ORACLE_HOME/bin
relink all
Once i relink my oracle home; it's should be work; but in my case continuously getting TNS Lost contact error message.
Work Around II:
When I try to type sqlplus in my terminal window i got some RPM package issues
-bash-3.00$ sqlplus
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Feb 25 15:34:47 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Enter user-name:
oracleoracle: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory
ERROR:
ORA-12547: TNS:lost contact
Current RPM Version:
The RPM package libaio-0.3.103-3.X86_64.RPM has not been installed.
$ rpm -qa --queryformat "%{NAME}-%{VERSION}.%{RELEASE} (%{ARCH})\n"
grep libaio
libaio-0.3.105.2 (ia64)
libaio-0.3.105.2 (i386)
The output should show:
libaio-0.3.105.2 (x86_64)
libaio-0.3.105.2 (i386)
As per 394297.1; We upgraded this RPM package after it's working fine.
After Upgrade RPM Package:
rpm -qa --queryformat "%{NAME}-%{VERSION}.%{RELEASE} (%{ARCH})\n"
grep libaio
libaio-devel-0.3.105.2 (x86_64)
libaio-0.3.105.2 (x86_64)
libaio-0.3.105.2 (i386)
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Feb 25 16:59:35 2010
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
Please share with your valuable comments & feedback. Thanks
"Marking disk "VOL1" as an ASM disk: [FAILED]"
Kernel : 2.6.9-42.EL-2.0.3
OS : Red Hat Enterprise Linux 4 AS
Database: 10.2.0.1
While doing ASM Configuraion, I got below error message
"Marking disk "VOL1" as an ASM disk: [FAILED]"
Not able to create ASM Disk's
[oracle@localhost ~]$ su - root
Password:
[root@localhost Desktop]# uname -r
2.6.9-42.0.0.0.1.ELhugemem
[root@localhost ~]#
[root@localhost ~]# cd Desktop/
[root@localhost Desktop]# ls -lrt
total 8
drwx------ 2 root root 4096 Mar 22 04:15 Trash
[root@localhost Desktop]# cd /home/oracle/Desktop/
[root@localhost Desktop]# ls -lrt
total 268
drwx------ 2 oracle oinstall 4096 Mar 22 14:20 Trash
-rw-r--r-- 1 oracle oinstall 4298 Mar 22 14:20 Home.desktop
-rw-r--r-- 1 oracle oinstall 82256 Mar 22 14:40 oracleasm-support-2.1.2-1.el4.i386.rpm
-rw-r--r-- 1 oracle oinstall 12948 Mar 22 14:40 oracleasmlib-2.0.2-1.i386.rpm
-rw-r--r-- 1 oracle oinstall 127412 Mar 22 14:41 oracleasm-2.6.9-42.EL-2.0.3-1.i686.rpm
[root@localhost Desktop]#
[root@localhost Desktop]# rpm -Uvh oracleasm-2.6.9-42.EL-2.0.3-1.i686.rpm \
> oracleasmlib-2.0.2-1.i386.rpm \
> oracleasm-support-2.1.2-1.el4.i386.rpm
Preparing... ########################################### [100%]
1:oracleasm-support ########################################### [ 33%]
2:oracleasm-2.6.9-42.EL ########################################### [ 67%]
3:oracleasmlib ########################################### [100%]
[root@localhost Desktop]#
[root@localhost Desktop]# /etc/init.d/oracleasm scandisks
Scanning the system for Oracle ASMLib disks: [ OK ]
[root@localhost Desktop]#
[root@localhost Desktop]# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.
This will configure the on-boot properties of the Oracle ASM library
driver. The following questions will determine whether the driver is
loaded on boot and what permissions it will have. The current values
will be shown in brackets ('[]'). Hitting without typing an
answer will keep that current value. Ctrl-C will abort.
Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver: [ OK ]
Scanning the system for Oracle ASMLib disks: [ OK ]
[root@localhost Desktop]#
[root@localhost Desktop]# /etc/init.d/oracleasm start
Initializing the Oracle ASMLib driver: [ OK ]
Scanning the system for Oracle ASMLib disks:
^[[A
[ OK ]
[root@localhost Desktop]#
[root@localhost Desktop]# /etc/init.d/oracleasm start
Initializing the Oracle ASMLib driver: [ OK ]
Scanning the system for Oracle ASMLib disks: [ OK ]
[root@localhost Desktop]#
[root@localhost Desktop]# /etc/init.d/oracleasm enable
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver: [ OK ]
Scanning the system for Oracle ASMLib disks: [ OK ]
[root@localhost Desktop]#
[root@localhost Desktop]# /etc/init.d/oracleasm createdisk /dev/sda6
Action "createdisk" requires two arguments
[root@localhost Desktop]#
[root@localhost Desktop]# /etc/init.d/oracleasm createdisk VOL1 /dev/sda6
Marking disk "VOL1" as an ASM disk: [FAILED]
Loading module "oracleasm": Unable to load module "oracleasm"
OS: Red Hat Enterprise Linux 4
Kernel : 2.6.9-22.EL
Database Version: 10.2.0.1
When I try to configure ASM disk I got below error message.
"Loading module "oracleasm": Unable to load module "oracleasm"
As per metalink "Doc ID: 338770.1". Need to upgrade kernel version
Uname –r
2.6.9-22.EL
[root@localhost Desktop]# rpm -Uvh
oracleasm-2.6.9-11.EL-2.0.3-1.i686.rpm \ >
oracleasmlib-2.0.2-1.i386.rpm \
> oracleasm-support-2.0.3-1.i386.rpm
Preparing... ########################################### [100%]
1:oracleasm-support ########################################### [ 33%]
2:oracleasm-2.6.9-11.EL ########################################### [ 67%]
3:oracleasmlib ########################################### [100%]
[root@localhost ~]# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.
This will configure the on-boot properties of the Oracle ASM library
driver. The following questions will determine whether the driver is
loaded on boot and what permissions it will have. The current values
will be shown in brackets ('[]'). Hitting without typing an
answer will keep that current value. Ctrl-C will abort.
Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [y]: y
Fix permissions of Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: [ OK ]
Loading module "oracleasm": Unable to load module "oracleasm"
[FAILED]
How to create ASM Instance
1. Before creating ASM instance you need to create asm disk using raw disk or using ASMLib disk.
1. Creating files using "dd" command
[oracle@localhost asmdisks]$ su - root
Password:
[root@localhost ~]# mkdir /dbbackup/asmdiks
[root@localhost ~]# chown oracle:oinstall /dbbackup/asmdiks/
[root@localhost ~]#
[root@localhost ~]# su - oracle
[oracle@localhost ~]$ dd if=/dev/zero of=/dbbackup/asmdiks/_file_disk1 bs=1k count=1000000
1000000+0 records in
1000000+0 records out
[oracle@localhost ~]$ dd if=/dev/zero of=/dbbackup/asmdiks/_file_disk2 bs=1k count=1000000
1000000+0 records in
1000000+0 records out
[oracle@localhost ~]$ dd if=/dev/zero of=/dbbackup/asmdiks/_file_disk3 bs=1k count=1000000
1000000+0 records in
1000000+0 records out
[oracle@localhost ~]$ cd /dbbackup/asmdiks/
[oracle@localhost asmdiks]$ ls -lrt
total 3002964
-rw-r--r-- 1 oracle oinstall 1024000000 Mar 27 04:45 _file_disk1
-rw-r--r-- 1 oracle oinstall 1024000000 Mar 27 04:48 _file_disk2
-rw-r--r-- 1 oracle oinstall 1024000000 Mar 27 04:52 _file_disk3
2. Makeing RAW disk using "losetup" command
[oracle@localhost asmdiks]$ su - root
Password:
[root@localhost ~]# losetup /dev/loop1 /dbbackup/asmdiks/_file_disk1
[root@localhost ~]# losetup /dev/loop2 /dbbackup/asmdiks/_file_disk2
[root@localhost ~]# losetup /dev/loop3 /dbbackup/asmdiks/_file_disk3
[root@localhost ~]## raw /dev/raw/raw1 /dev/loop1
/dev/raw/raw1: bound to major 7, minor 1
[root@localhost ~]## raw /dev/raw/raw2 /dev/loop2
/dev/raw/raw2: bound to major 7, minor 2
[root@localhost ~]## raw /dev/raw/raw3 /dev/loop3
/dev/raw/raw3: bound to major 7, minor 3
4. Change Ownership of RAW Devices
Finally, let's change the ownership of all four RAW devices:
# chown oracle:dba /dev/raw/raw1
# chown oracle:dba /dev/raw/raw2
# chown oracle:dba /dev/raw/raw3
# chown oracle:dba /dev/raw/raw4
# chmod 775 /dev/raw/raw1
# chmod 775 /dev/raw/raw2
# chmod 775 /dev/raw/raw3
# chmod 775 /dev/raw/raw4
5. Startup Script
Before restarting your OS you need to write small script for enable your raw disk in /etc/init.d/
/sbin/losetup /dev/loop1 /asmdisks/_file_disk1; sleep 2
/sbin/losetup /dev/loop2 /asmdisks/_file_disk2; sleep 2
/sbin/losetup /dev/loop3 /asmdisks/_file_disk3; sleep 2
/sbin/losetup /dev/loop4 /asmdisks/_file_disk4; sleep 2
/usr/bin/raw /dev/raw/raw1 /dev/loop1; sleep 2
/usr/bin/raw /dev/raw/raw2 /dev/loop2; sleep 2
/usr/bin/raw /dev/raw/raw3 /dev/loop3; sleep 2
/usr/bin/raw /dev/raw/raw4 /dev/loop4; sleep 2
/bin/chown oracle:dba /dev/raw/raw1
/bin/chown oracle:dba /dev/raw/raw2
/bin/chown oracle:dba /dev/raw/raw3
/bin/chown oracle:dba /dev/raw/raw4
/bin/chmod 660 /dev/raw/raw1
/bin/chmod 660 /dev/raw/raw2
/bin/chmod 660 /dev/raw/raw3
/bin/chmod 660 /dev/raw/raw4
sleep 120
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/dbstart"
su - $ORACLE_OWNER -c "lsnrctl start listener"
Database Creating
Linux DataBase Creation
Oracle Version : 10.2.0.1.0
Operating Sys : Suse Linux 9.3
Step 1: Here create oracle OFA folder.
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin> mkdir lamp
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin> cd lamp
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin/lamp> mkdir adump
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin/lamp> mkdir bdump
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin/lamp> mkdir cdump
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin/lamp> mkdir dpdump
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin/lamp> mkdir pfile
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin/lamp> mkdir udump
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin/lamp> cd ..
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin> cd ..
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/oradata> mkdir lamp
Step 2: Create new password file for your new instance.
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/bin> orapwd file=/u01/app/oracle/product/10.1.0/Db_1/dbs/pwdlamp.ora entries=5 password=oracle force=y
Step 3: To start a new instance, say newsid, you create an initnewsid
oracle@linuxDomain:~> export ORACLE_SID=lamp
oracle@linuxDomain:~> sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jul 4 16:09:40 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile=/u01/app/oracle/product/10.1.0/Db_1/dbs/initLAMP.ora
ORACLE instance started.
Total System Global Area 276824064 bytes
Fixed Size 1218920 bytes
Variable Size 75499160 bytes
Database Buffers 192937984 bytes
Redo Buffers 7168000 bytes
SQL>
Step 4: Execute bellow script.
1 CREATE DATABASE "LAMP"
2 MAXINSTANCES 8
3 MAXLOGFILES 16
4 MAXLOGMEMBERS 3
5 MAXDATAFILES 100
6 MAXLOGHISTORY 1
7 DATAFILE '/u01/app/oracle/product/10.1.0/Db_1/oradata/lamp/SYSTEM01.DBF' SIZE 500M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
8 EXTENT MANAGEMENT LOCAL
9 SYSAUX DATAFILE '/u01/app/oracle/product/10.1.0/Db_1/oradata/lamp/SYSAUX01.DBF' SIZE 500M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
10 DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/app/oracle/product/10.1.0/Db_1/oradata/lamp/TEMP01.DBF' SIZE 100M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
11 UNDO TABLESPACE "UNDOTBS1" DATAFILE '/u01/app/oracle/product/10.1.0/Db_1/oradata/lamp/UNDOTBS01.DBF' SIZE 500M REUSE AUTOEXTEND ON NEXT 5120K
12 MAXSIZE UNLIMITED
13 CHARACTER SET WE8MSWIN1252
14 NATIONAL CHARACTER SET AL16UTF16
15 LOGFILE GROUP 1 ('/u01/app/oracle/product/10.1.0/Db_1/oradata/lamp/REDO01.LOG') SIZE 10240K,
16 GROUP 2 ('/u01/app/oracle/product/10.1.0/Db_1/oradata/lamp/REDO02.LOG') SIZE 10240K,
17 GROUP 3 ('/u01/app/oracle/product/10.1.0/Db_1/oradata/lamp/REDO03.LOG') SIZE 10240K
18 USER SYS IDENTIFIED BY "&&syspwd"
19* USER SYSTEM IDENTIFIED BY "&&systempwd"
SQL> /
old 18: USER SYS IDENTIFIED BY "&&syspwd"
new 18: USER SYS IDENTIFIED BY "oracle"
old 19: USER SYSTEM IDENTIFIED BY "&&systempwd"
new 19: USER SYSTEM IDENTIFIED BY "oracle"
Database created.
Step 5 : Create users tablespace.
SQL> CREATE TABLESPACE USERS DATAFILE '/u01/app/oracle/product/10.1.0/Db_1/oradata/lamp/USERS01.DBF' SIZE 500M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
2 EXTENT MANAGEMENT LOCAL
3 SEGMENT SPACE MANAGEMENT AUTO;
Step 6: You need run the following script in your database.
SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql
SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql
SQL> @$ORACLE_HOME/rdbms/admin/catexp.sql
Step 7: Create Server parameter file using parameter file.
SQL> CREATE SPFILE FROM PFILE='/u01/app/oracle/product/10.1.0/Db_1/dbs/initLAMP.ora';
File created.
Step 8 : Start your database.
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.
Total System Global Area 276824064 bytes
Fixed Size 1218920 bytes
Variable Size 75499160 bytes
Database Buffers 192937984 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
-----------------------------------------------------------------------------------
Manually Create DataBase
DataBase creation there are two type's
1. Using Database configuration assistant. Start -> run -> type " Dbca"
2. manually crated database using Command based method.
Here i have used manually cteated.
1. Create folder in %ORACLE_BASE%/admin/
%ORACLE_BASE%\admin>mkdir iso
%ORACLE_BASE%\admin>cd iso
%ORACLE_BASE%\admin\iso>mkdir adump
%ORACLE_BASE%\admin\iso>mkdir bdump
%ORACLE_BASE%\admin\iso>mkdir cdump
%ORACLE_BASE%\admin\iso>mkdir dpdump
%ORACLE_BASE%\admin\iso>mkdir pfile
%ORACLE_BASE%\admin\iso>mkdir scripts
%ORACLE_BASE%\admin\iso>mkdir udump
Here "ISO" is a my database name.
2. set oracle enviorment variable
F:\>set oracle_sid=iso
3. create password file for iso database
F:\>orapwd file=F:\oracle\product\10.2.0\db_1\database\PWDiso.ora entries=5 password=oracle force=y
4. Create Oracle instance and start your new service
F:\>oradim -new -sid iso -startmode manual
Instance created.
F:\>oradim -edit -sid iso -startmode auto
F:\>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Apr 21 16:44:52 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> connect sys as sysdba
Enter password:
Connected to an idle instance.
5. create initialization parameter file
in %ORACLE_HOME%\database\initISO.ora
SQL> startup nomount
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 1248528 bytes
Variable Size 92275440 bytes
Database Buffers 180355072 bytes
Redo Buffers 7139328 bytes
6. Here run your Database script
SQL> CREATE DATABASE "ISO"
2 MAXINSTANCES 8
3 MAXLOGHISTORY 1
4 MAXLOGFILES 16
5 MAXLOGMEMBERS 3
6 MAXDATAFILES 50
7 DATAFILE 'F:\oracle\product\10.2.0\oradata\iso\SYSTEM01.DBF' SIZE 300M
REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
8 SYSAUX DATAFILE 'F:\oracle\product\10.2.0\oradata\iso\SYSAUX01.DBF'
SIZE 120M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE
UNLIMITED
9 DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE
'F:\oracle\product\10.2.0\oradata\iso\TEMP01.DBF' SIZE 50M REUSE
AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
10 UNDO TABLESPACE "UNDOTBS1" DATAFILE
'F:\oracle\product\10.2.0\oradata\iso\UNDOTBS01.DBF' SIZE 200M REUSE
AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
11 CHARACTER SET WE8MSWIN1252
12 NATIONAL CHARACTER SET AL16UTF16
13 LOGFILE GROUP 1 ('F:\oracle\product\10.2.0\oradata\iso\REDO01.LOG') SIZE
10240K,
14 GROUP 2 ('F:\oracle\product\10.2.0\oradata\iso\REDO02.LOG') SIZE
10240K,
15 GROUP 3 ('F:\oracle\product\10.2.0\oradata\iso\REDO03.LOG') SIZE
10240K
16 USER SYS IDENTIFIED BY "&SYSPWD"
17 USER SYSTEM IDENTIFIED BY "&SYSTEMPWD";
Enter value for syspwd: abbas
old 16: USER SYS IDENTIFIED BY "&SYSPWD"
new 16: USER SYS IDENTIFIED BY "abbas"
Enter value for systempwd: abbas
old 17: USER SYSTEM IDENTIFIED BY "&SYSTEMPWD"
new 17: USER SYSTEM IDENTIFIED BY "abbas"
Database created.
7. Create user tablespace
SQL> CREATE TABLESPACE "USER" LOGGING DATAFILE 'F:\oracle\product\10.2.0\oradata\iso\USERS01.DBF' SIZE 10m REUSE AUTOEXTEND ON NEXT 1280k MAXSIZE
2* UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
3 /
Tablespace created.
SQL> ALTER DATABASE DEFAULT TABLESPACE "USER";
Database altered.
8. Execute the following script
@%ORACLE_HOME%\RDBMS\ADMIN\catalog.SQL
@%ORACLE_HOME%\RDBMS\ADMIN\catproc.sql
@%ORACLE_HOME%\SQLPLUS\ADMIN\pupbld.sql
9. Finally create Spfile using your Pfile
SQL> CREATE SPFILE FROM PFILE='F:\oracle\product\10.2.0\db_1\database\initISO.ora';
File created.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 1248528 bytes
Variable Size 92275440 bytes
Database Buffers 180355072 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.
Oracle Version : 10.2.0.1.0
Operating Sys : Suse Linux 9.3
Step 1: Here create oracle OFA folder.
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin> mkdir lamp
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin> cd lamp
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin/lamp> mkdir adump
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin/lamp> mkdir bdump
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin/lamp> mkdir cdump
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin/lamp> mkdir dpdump
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin/lamp> mkdir pfile
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin/lamp> mkdir udump
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin/lamp> cd ..
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/admin> cd ..
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/oradata> mkdir lamp
Step 2: Create new password file for your new instance.
oracle@linuxDomain:/u01/app/oracle/product/10.1.0/Db_1/bin> orapwd file=/u01/app/oracle/product/10.1.0/Db_1/dbs/pwdlamp.ora entries=5 password=oracle force=y
Step 3: To start a new instance, say newsid, you create an initnewsid
oracle@linuxDomain:~> export ORACLE_SID=lamp
oracle@linuxDomain:~> sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jul 4 16:09:40 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile=/u01/app/oracle/product/10.1.0/Db_1/dbs/initLAMP.ora
ORACLE instance started.
Total System Global Area 276824064 bytes
Fixed Size 1218920 bytes
Variable Size 75499160 bytes
Database Buffers 192937984 bytes
Redo Buffers 7168000 bytes
SQL>
Step 4: Execute bellow script.
1 CREATE DATABASE "LAMP"
2 MAXINSTANCES 8
3 MAXLOGFILES 16
4 MAXLOGMEMBERS 3
5 MAXDATAFILES 100
6 MAXLOGHISTORY 1
7 DATAFILE '/u01/app/oracle/product/10.1.0/Db_1/oradata/lamp/SYSTEM01.DBF' SIZE 500M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
8 EXTENT MANAGEMENT LOCAL
9 SYSAUX DATAFILE '/u01/app/oracle/product/10.1.0/Db_1/oradata/lamp/SYSAUX01.DBF' SIZE 500M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
10 DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/app/oracle/product/10.1.0/Db_1/oradata/lamp/TEMP01.DBF' SIZE 100M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
11 UNDO TABLESPACE "UNDOTBS1" DATAFILE '/u01/app/oracle/product/10.1.0/Db_1/oradata/lamp/UNDOTBS01.DBF' SIZE 500M REUSE AUTOEXTEND ON NEXT 5120K
12 MAXSIZE UNLIMITED
13 CHARACTER SET WE8MSWIN1252
14 NATIONAL CHARACTER SET AL16UTF16
15 LOGFILE GROUP 1 ('/u01/app/oracle/product/10.1.0/Db_1/oradata/lamp/REDO01.LOG') SIZE 10240K,
16 GROUP 2 ('/u01/app/oracle/product/10.1.0/Db_1/oradata/lamp/REDO02.LOG') SIZE 10240K,
17 GROUP 3 ('/u01/app/oracle/product/10.1.0/Db_1/oradata/lamp/REDO03.LOG') SIZE 10240K
18 USER SYS IDENTIFIED BY "&&syspwd"
19* USER SYSTEM IDENTIFIED BY "&&systempwd"
SQL> /
old 18: USER SYS IDENTIFIED BY "&&syspwd"
new 18: USER SYS IDENTIFIED BY "oracle"
old 19: USER SYSTEM IDENTIFIED BY "&&systempwd"
new 19: USER SYSTEM IDENTIFIED BY "oracle"
Database created.
Step 5 : Create users tablespace.
SQL> CREATE TABLESPACE USERS DATAFILE '/u01/app/oracle/product/10.1.0/Db_1/oradata/lamp/USERS01.DBF' SIZE 500M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
2 EXTENT MANAGEMENT LOCAL
3 SEGMENT SPACE MANAGEMENT AUTO;
Step 6: You need run the following script in your database.
SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql
SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql
SQL> @$ORACLE_HOME/rdbms/admin/catexp.sql
Step 7: Create Server parameter file using parameter file.
SQL> CREATE SPFILE FROM PFILE='/u01/app/oracle/product/10.1.0/Db_1/dbs/initLAMP.ora';
File created.
Step 8 : Start your database.
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.
Total System Global Area 276824064 bytes
Fixed Size 1218920 bytes
Variable Size 75499160 bytes
Database Buffers 192937984 bytes
Redo Buffers 7168000 bytes
Database mounted.
Database opened.
-----------------------------------------------------------------------------------
Manually Create DataBase
DataBase creation there are two type's
1. Using Database configuration assistant. Start -> run -> type " Dbca"
2. manually crated database using Command based method.
Here i have used manually cteated.
1. Create folder in %ORACLE_BASE%/admin/
%ORACLE_BASE%\admin>mkdir iso
%ORACLE_BASE%\admin>cd iso
%ORACLE_BASE%\admin\iso>mkdir adump
%ORACLE_BASE%\admin\iso>mkdir bdump
%ORACLE_BASE%\admin\iso>mkdir cdump
%ORACLE_BASE%\admin\iso>mkdir dpdump
%ORACLE_BASE%\admin\iso>mkdir pfile
%ORACLE_BASE%\admin\iso>mkdir scripts
%ORACLE_BASE%\admin\iso>mkdir udump
Here "ISO" is a my database name.
2. set oracle enviorment variable
F:\>set oracle_sid=iso
3. create password file for iso database
F:\>orapwd file=F:\oracle\product\10.2.0\db_1\database\PWDiso.ora entries=5 password=oracle force=y
4. Create Oracle instance and start your new service
F:\>oradim -new -sid iso -startmode manual
Instance created.
F:\>oradim -edit -sid iso -startmode auto
F:\>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Sat Apr 21 16:44:52 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> connect sys as sysdba
Enter password:
Connected to an idle instance.
5. create initialization parameter file
in %ORACLE_HOME%\database\initISO.ora
SQL> startup nomount
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 1248528 bytes
Variable Size 92275440 bytes
Database Buffers 180355072 bytes
Redo Buffers 7139328 bytes
6. Here run your Database script
SQL> CREATE DATABASE "ISO"
2 MAXINSTANCES 8
3 MAXLOGHISTORY 1
4 MAXLOGFILES 16
5 MAXLOGMEMBERS 3
6 MAXDATAFILES 50
7 DATAFILE 'F:\oracle\product\10.2.0\oradata\iso\SYSTEM01.DBF' SIZE 300M
REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
8 SYSAUX DATAFILE 'F:\oracle\product\10.2.0\oradata\iso\SYSAUX01.DBF'
SIZE 120M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE
UNLIMITED
9 DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE
'F:\oracle\product\10.2.0\oradata\iso\TEMP01.DBF' SIZE 50M REUSE
AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
10 UNDO TABLESPACE "UNDOTBS1" DATAFILE
'F:\oracle\product\10.2.0\oradata\iso\UNDOTBS01.DBF' SIZE 200M REUSE
AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
11 CHARACTER SET WE8MSWIN1252
12 NATIONAL CHARACTER SET AL16UTF16
13 LOGFILE GROUP 1 ('F:\oracle\product\10.2.0\oradata\iso\REDO01.LOG') SIZE
10240K,
14 GROUP 2 ('F:\oracle\product\10.2.0\oradata\iso\REDO02.LOG') SIZE
10240K,
15 GROUP 3 ('F:\oracle\product\10.2.0\oradata\iso\REDO03.LOG') SIZE
10240K
16 USER SYS IDENTIFIED BY "&SYSPWD"
17 USER SYSTEM IDENTIFIED BY "&SYSTEMPWD";
Enter value for syspwd: abbas
old 16: USER SYS IDENTIFIED BY "&SYSPWD"
new 16: USER SYS IDENTIFIED BY "abbas"
Enter value for systempwd: abbas
old 17: USER SYSTEM IDENTIFIED BY "&SYSTEMPWD"
new 17: USER SYSTEM IDENTIFIED BY "abbas"
Database created.
7. Create user tablespace
SQL> CREATE TABLESPACE "USER" LOGGING DATAFILE 'F:\oracle\product\10.2.0\oradata\iso\USERS01.DBF' SIZE 10m REUSE AUTOEXTEND ON NEXT 1280k MAXSIZE
2* UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
3 /
Tablespace created.
SQL> ALTER DATABASE DEFAULT TABLESPACE "USER";
Database altered.
8. Execute the following script
@%ORACLE_HOME%\RDBMS\ADMIN\catalog.SQL
@%ORACLE_HOME%\RDBMS\ADMIN\catproc.sql
@%ORACLE_HOME%\SQLPLUS\ADMIN\pupbld.sql
9. Finally create Spfile using your Pfile
SQL> CREATE SPFILE FROM PFILE='F:\oracle\product\10.2.0\db_1\database\initISO.ora';
File created.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 1248528 bytes
Variable Size 92275440 bytes
Database Buffers 180355072 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.
Sunday, August 1, 2010
Imp / Exp
Oracle Data Pump is a feature of Oracle Database 10g that enables very fast bulk data and metadata movement between Oracle databases. Oracle Data Pump provides new high-speed, parallel Export and Import utilities (expdp and impdp) as well as a Web-based Oracle Enterprise Manager interface.
Data Pump Export and Import utilities are typically much faster than the original Export and Import Utilities. A single thread of Data Pump Export is about twice as fast as original Export, while Data Pump Import is 15-45 times fast than original Import.Data Pump jobs can be restarted without loss of data, whether or not the stoppage was voluntary or involuntary.Data Pump jobs support fine-grained object selection. Virtually any type of object can be included or excluded in a Data Pump job.Data Pump supports the ability to load one instance directly from another (network import) and unload a remote instance (network export).
Data Pump Export (expdp) :-
For this example, once your export your database before that you must be give privilege on this user. If you need to export you can give " EXP_FULL_DATABASE " and if you need import you can give " IMP_FULL_DATABASE "
SQL> CONNECT SYS/BABU@KEYSTONE AS SYSDBA
Connected.
SQL> GRANT CREATE ANY DIRECTORY TO ORTHONOVC16;
Grant succeeded.
SQL> CREATE OR REPLACE DIRECTORY OTHOC16 AS 'D:\ORTHOC16';
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY OTHOC16 TO ORTHONOVC16;
Grant succeeded.
SQL> GRANT EXP_FULL_DATABASE,IMP_FULL_DATABASE TO ORTHONOVC16;
Grant succeeded.
Table level Export :-
SQL> HOST expdp ORTHONOVC16/ORTHONOVC16@KEYSTONE tables=G_USER DIRECTORY=OTHOC16 DUMPFILE=ORTHO_G_USER.DMP LOGFILE=ORTHOLOG.LOG
The TABLE_EXISTS_ACTION=APPEND parameter allows data to be imported into existing tables.
Schema level export :-
SQL> HOST expdp ORTHONOVC16/ORTHONOVC16@KEYSTONE SCHEMAS=ORTHONOVC16 DIRECTORY=OTHOC16 DUMPFILE=ORTHONOVC16.DMP LOGFILE=ORTHONOVC16.LOG
DataBase level export :-
SQL> HOST expdp ORTHONOVC16/ORTHONOVC16@KEYSTONE FULL=Y DIRECTORY=OTHOC16 DUMPFILE=DBORTHO.DMP LOGFILE=DBORTHO.LOG
Other export's :
Exclude = View, Proceudre, Function , Constraint , Index
Include = Table: " in ( 'emp') "
Content = ALL (by default ) / data_only / metadata_only
Estimate_Only = Before export your dumpfile you can estimate your dumpfile size using the bellow
parameter " ESTIMATE_ONLY = Y "
Data Pump Export and Import utilities are typically much faster than the original Export and Import Utilities. A single thread of Data Pump Export is about twice as fast as original Export, while Data Pump Import is 15-45 times fast than original Import.Data Pump jobs can be restarted without loss of data, whether or not the stoppage was voluntary or involuntary.Data Pump jobs support fine-grained object selection. Virtually any type of object can be included or excluded in a Data Pump job.Data Pump supports the ability to load one instance directly from another (network import) and unload a remote instance (network export).
Data Pump Export (expdp) :-
For this example, once your export your database before that you must be give privilege on this user. If you need to export you can give " EXP_FULL_DATABASE " and if you need import you can give " IMP_FULL_DATABASE "
SQL> CONNECT SYS/BABU@KEYSTONE AS SYSDBA
Connected.
SQL> GRANT CREATE ANY DIRECTORY TO ORTHONOVC16;
Grant succeeded.
SQL> CREATE OR REPLACE DIRECTORY OTHOC16 AS 'D:\ORTHOC16';
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY OTHOC16 TO ORTHONOVC16;
Grant succeeded.
SQL> GRANT EXP_FULL_DATABASE,IMP_FULL_DATABASE TO ORTHONOVC16;
Grant succeeded.
Table level Export :-
SQL> HOST expdp ORTHONOVC16/ORTHONOVC16@KEYSTONE tables=G_USER DIRECTORY=OTHOC16 DUMPFILE=ORTHO_G_USER.DMP LOGFILE=ORTHOLOG.LOG
The TABLE_EXISTS_ACTION=APPEND parameter allows data to be imported into existing tables.
Schema level export :-
SQL> HOST expdp ORTHONOVC16/ORTHONOVC16@KEYSTONE SCHEMAS=ORTHONOVC16 DIRECTORY=OTHOC16 DUMPFILE=ORTHONOVC16.DMP LOGFILE=ORTHONOVC16.LOG
DataBase level export :-
SQL> HOST expdp ORTHONOVC16/ORTHONOVC16@KEYSTONE FULL=Y DIRECTORY=OTHOC16 DUMPFILE=DBORTHO.DMP LOGFILE=DBORTHO.LOG
Other export's :
Exclude = View, Proceudre, Function , Constraint , Index
Include = Table: " in ( 'emp') "
Content = ALL (by default ) / data_only / metadata_only
Estimate_Only = Before export your dumpfile you can estimate your dumpfile size using the bellow
parameter " ESTIMATE_ONLY = Y "
Standby Database
Physical Standby Database Creation
Primary DataBase : KEYSTONE
Standby DataBase : KEYSTONESTDY
Primary Database – ARHIVELOG Mode
Version : Oracle 10.2.0
Enable Force logging.
SQL> show user
USER is "SYS"
SQL> SELECT FORCE_LOGGING FROM V$DATABASE;
FOR
---
NO
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
SQL> SELECT FORCE_LOGGING FROM V$DATABASE;
FOR
---
YES
Configuring primary database initialization parameter’s
SQL> SHOW PARAMETER DB_NAME
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string KEYSTONE
SQL> SHOW PARAMETER DB_UNIQUE_NAME
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string KEYSTONE
SQL> SHOW PARAMETER SERVICE_NAMES
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string KEYSTONE
Note:
If not the above parameter in your primary init.ora file. Just alter the above three parameter in your database using alter command.
Example:
ALTER SYSTEM SET DB_UNIQUE_NAME=KEYSTONE SCOPE=SPFILE;
SQL> SHOW PARAMETER REMOTE_LOGIN_PASSWORDFILE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
SQL> ALTER SYSTEM SET log_archive_format='ARC%T_%R_%S.LOG' SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=D:\keystoneSTDY_Arh VALID_FOR=(ALL_ROLES,ALL_LOGFILES) DB_UNIQUE_NAME=KEYSTONESTDY';
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=D:\keystone_Arh VALID_FOR=(ALL_ROLES,ALL_LOGFILES) DB_UNIQUE_NAME=KEYSTONE';
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
System altered.
Standy database initialization parameter:
The below parameter only need to configure standby database using primary database.
SQL> ALTER SYSTEM SET FAL_SERVER=KEYSTONESTDY;
System altered.
SQL> ALTER SYSTEM SET FAL_CLIENT=KEYSTONE;
System altered.
SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT=
2 'c:\oracle\product\10.1.0\oradata\KEYSTONE',
3 'c:\oracle\product\10.1.0\oradata\KEYSTONEDY' scope=spfile;
System altered.
SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT=
2 'c:\oracle\product\10.1.0\oradata\KEYSTONE',
3 'c:\oracle\product\10.1.0\oradata\KEYSTONEDY' scope=spfile;
System altered.
SQL> ALTER SYSETM SET STANDBY_FILE_MANAGEMENT=AUTO;
System altered.
Primary database offline backup
Take Operating System level data file backup in primary database to standby database
SQL> HOST COPY -
> F:\oracle\product\10.2.0\oradata\KEYSTONE\*.DBF -
> F:\oracle\product\10.2.0\oradata\KEYSTONESTDY
F:\oracle\product\10.2.0\oradata\KEYSTONE\EXAMPLE01.DBF
F:\oracle\product\10.2.0\oradata\KEYSTONE\SYSAUX01.DBF
F:\oracle\product\10.2.0\oradata\KEYSTONE\SYSTEM01.DBF
F:\oracle\product\10.2.0\oradata\KEYSTONE\TEMP01.DBF
F:\oracle\product\10.2.0\oradata\KEYSTONE\UNDOTBS01.DBF
F:\oracle\product\10.2.0\oradata\KEYSTONE\USERS01.DBF
6 file(s) copied.
SQL> STARTUP MOUNT
ORACLE instance started.
Total System Global Area 289406976 bytes
Fixed Size 1248576 bytes
Variable Size 100664000 bytes
Database Buffers 180355072 bytes
Redo Buffers 7139328 bytes
Database mounted.
To Create Standby Control file
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'F:\oracle\product\10.2.0\oradata\KEYSTONESTDY\CONTROL01.CTL';
Database altered.
Create initialization file for standby database.
SQL> CREATE PFILE='F:\oracle\product\10.2.0\db_1\database\INITKEYSTONESTDY.ORA' FROM SPFILE;
File created.
Edit the following information in init.ora file (standby) : Edit BOLD line only.
<!--[if !supportLineBreakNewLine]-->
<!--[endif]-->
KEYSTONESTDY.__db_cache_size=180355072
KEYSTONESTDY.__java_pool_size=4194304
KEYSTONESTDY.__large_pool_size=4194304
KEYSTONESTDY.__shared_pool_size=92274688
KEYSTONESTDY.__streams_pool_size=0
*.audit_file_dest='F:\oracle\product\10.2.0/admin/KEYSTONESTDY/adump'
*.background_dump_dest='F:\oracle\product\10.2.0/admin/KEYSTONESTDY/bdump'
*.compatible='10.2.0.1.0'
*.control_files='F:\oracle\product\10.2.0/oradata/KEYSTONESTDY/\control01.ctl'
*.core_dump_dest='F:\oracle\product\10.2.0/admin/KEYSTONESTDY/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_file_name_convert='F:\oracle\product\10.2.0\oradata\KEYSTONE','F:\oracle\product\10.2.0\oradata\KEYSTONESTDY'
*.db_name='KEYSTONE'
db_unique_name='KEYSTONESTDY'
*.db_recovery_file_dest='F:\oracle\product\10.2.0/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=KEYSTONEXDB)'
*.fal_client='KEYSTONESTDY'
*.fal_server='KEYSTONE'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=D:\keystoneSTDY_Arh VALID_FOR=(ALL_ROLES,ALL_LOGFILES) DB_UNIQUE_NAME=KEYSTONESTDY'
*.log_archive_format='ARC%S_%R.%T'
*.log_file_name_convert='F:\oracle\product\10.2.0\oradata\KEYSTONE','F:\oracle\product\10.2.0\oradata\KEYSTONESTDY'
*.open_cursors=300
*.pga_aggregate_target=95420416
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
service_names='KEYSTONESTDY'
*.sga_target=287309824
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='F:\oracle\product\10.2.0/admin/KEYSTONESTDY/udump'
INSTANCE_NAME=KEYSTONESTDY
Create standby instance
C:\>ORADIM -NEW -SID KEYSTONESTDY -INTPWD oracle -STARTMODE manual
Instance created.
Configure Net Service's names
Add the following line in SQLNET.ORA file
sqlnet.expire_time=2
And configure tnsnames.ora file. like
KEYSTONESTDY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = adaptime75)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = KEYSTONESTDY)
)
)
C:\>SET ORACLE_SID=KEYSTONESTDY
C:\>SQLPLUS SYS AS SYSDBA
SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 16 19:48:34 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter password:
Connected to an idle instance.
Create server parameter file
SQL> CREATE SPFILE FROM PFILE;
File created.
SQL> STARTUP OPEN READ ONLY
ORACLE instance started.
Total System Global Area 289406976 bytes
Fixed Size 1248576 bytes
Variable Size 100664000 bytes
Database Buffers 180355072 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.
SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE CONTENTS='TEMPORARY';
TABLESPACE_NAME
------------------------------
TEMP
SQL> SELECT * FROM V$TEMPFILE;
FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED
---------- ---------------- --------- ---------- ---------- ------- ----------
BYTES BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------- ------------ ----------
NAME
--------------------------------------------------------------------------------
1 535157 16-MAY-07 3 1 ONLINE READ WRITE
20971520 2560 20971520 8192
F:\ORACLE\PRODUCT\10.2.0\ORADATA\KEYSTONESTDY\TEMP01.DBF
SQL> SHOW USER
USER is "SYS"
SQL>
Create Redo log file for standby database
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
Note:
Thanks for visit my blog. Please try physical database creation in testing env.If any problem please post your comments..
Primary DataBase : KEYSTONE
Standby DataBase : KEYSTONESTDY
Primary Database – ARHIVELOG Mode
Version : Oracle 10.2.0
Enable Force logging.
SQL> show user
USER is "SYS"
SQL> SELECT FORCE_LOGGING FROM V$DATABASE;
FOR
---
NO
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
SQL> SELECT FORCE_LOGGING FROM V$DATABASE;
FOR
---
YES
Configuring primary database initialization parameter’s
SQL> SHOW PARAMETER DB_NAME
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string KEYSTONE
SQL> SHOW PARAMETER DB_UNIQUE_NAME
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string KEYSTONE
SQL> SHOW PARAMETER SERVICE_NAMES
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string KEYSTONE
Note:
If not the above parameter in your primary init.ora file. Just alter the above three parameter in your database using alter command.
Example:
ALTER SYSTEM SET DB_UNIQUE_NAME=KEYSTONE SCOPE=SPFILE;
SQL> SHOW PARAMETER REMOTE_LOGIN_PASSWORDFILE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
SQL> ALTER SYSTEM SET log_archive_format='ARC%T_%R_%S.LOG' SCOPE=SPFILE;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=D:\keystoneSTDY_Arh VALID_FOR=(ALL_ROLES,ALL_LOGFILES) DB_UNIQUE_NAME=KEYSTONESTDY';
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=D:\keystone_Arh VALID_FOR=(ALL_ROLES,ALL_LOGFILES) DB_UNIQUE_NAME=KEYSTONE';
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE;
System altered.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;
System altered.
Standy database initialization parameter:
The below parameter only need to configure standby database using primary database.
SQL> ALTER SYSTEM SET FAL_SERVER=KEYSTONESTDY;
System altered.
SQL> ALTER SYSTEM SET FAL_CLIENT=KEYSTONE;
System altered.
SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT=
2 'c:\oracle\product\10.1.0\oradata\KEYSTONE',
3 'c:\oracle\product\10.1.0\oradata\KEYSTONEDY' scope=spfile;
System altered.
SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT=
2 'c:\oracle\product\10.1.0\oradata\KEYSTONE',
3 'c:\oracle\product\10.1.0\oradata\KEYSTONEDY' scope=spfile;
System altered.
SQL> ALTER SYSETM SET STANDBY_FILE_MANAGEMENT=AUTO;
System altered.
Primary database offline backup
Take Operating System level data file backup in primary database to standby database
SQL> HOST COPY -
> F:\oracle\product\10.2.0\oradata\KEYSTONE\*.DBF -
> F:\oracle\product\10.2.0\oradata\KEYSTONESTDY
F:\oracle\product\10.2.0\oradata\KEYSTONE\EXAMPLE01.DBF
F:\oracle\product\10.2.0\oradata\KEYSTONE\SYSAUX01.DBF
F:\oracle\product\10.2.0\oradata\KEYSTONE\SYSTEM01.DBF
F:\oracle\product\10.2.0\oradata\KEYSTONE\TEMP01.DBF
F:\oracle\product\10.2.0\oradata\KEYSTONE\UNDOTBS01.DBF
F:\oracle\product\10.2.0\oradata\KEYSTONE\USERS01.DBF
6 file(s) copied.
SQL> STARTUP MOUNT
ORACLE instance started.
Total System Global Area 289406976 bytes
Fixed Size 1248576 bytes
Variable Size 100664000 bytes
Database Buffers 180355072 bytes
Redo Buffers 7139328 bytes
Database mounted.
To Create Standby Control file
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS 'F:\oracle\product\10.2.0\oradata\KEYSTONESTDY\CONTROL01.CTL';
Database altered.
Create initialization file for standby database.
SQL> CREATE PFILE='F:\oracle\product\10.2.0\db_1\database\INITKEYSTONESTDY.ORA' FROM SPFILE;
File created.
Edit the following information in init.ora file (standby) : Edit BOLD line only.
<!--[if !supportLineBreakNewLine]-->
<!--[endif]-->
KEYSTONESTDY.__db_cache_size=180355072
KEYSTONESTDY.__java_pool_size=4194304
KEYSTONESTDY.__large_pool_size=4194304
KEYSTONESTDY.__shared_pool_size=92274688
KEYSTONESTDY.__streams_pool_size=0
*.audit_file_dest='F:\oracle\product\10.2.0/admin/KEYSTONESTDY/adump'
*.background_dump_dest='F:\oracle\product\10.2.0/admin/KEYSTONESTDY/bdump'
*.compatible='10.2.0.1.0'
*.control_files='F:\oracle\product\10.2.0/oradata/KEYSTONESTDY/\control01.ctl'
*.core_dump_dest='F:\oracle\product\10.2.0/admin/KEYSTONESTDY/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_file_name_convert='F:\oracle\product\10.2.0\oradata\KEYSTONE','F:\oracle\product\10.2.0\oradata\KEYSTONESTDY'
*.db_name='KEYSTONE'
db_unique_name='KEYSTONESTDY'
*.db_recovery_file_dest='F:\oracle\product\10.2.0/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=KEYSTONEXDB)'
*.fal_client='KEYSTONESTDY'
*.fal_server='KEYSTONE'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=D:\keystoneSTDY_Arh VALID_FOR=(ALL_ROLES,ALL_LOGFILES) DB_UNIQUE_NAME=KEYSTONESTDY'
*.log_archive_format='ARC%S_%R.%T'
*.log_file_name_convert='F:\oracle\product\10.2.0\oradata\KEYSTONE','F:\oracle\product\10.2.0\oradata\KEYSTONESTDY'
*.open_cursors=300
*.pga_aggregate_target=95420416
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
service_names='KEYSTONESTDY'
*.sga_target=287309824
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='F:\oracle\product\10.2.0/admin/KEYSTONESTDY/udump'
INSTANCE_NAME=KEYSTONESTDY
Create standby instance
C:\>ORADIM -NEW -SID KEYSTONESTDY -INTPWD oracle -STARTMODE manual
Instance created.
Configure Net Service's names
Add the following line in SQLNET.ORA file
sqlnet.expire_time=2
And configure tnsnames.ora file. like
KEYSTONESTDY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = adaptime75)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = KEYSTONESTDY)
)
)
C:\>SET ORACLE_SID=KEYSTONESTDY
C:\>SQLPLUS SYS AS SYSDBA
SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 16 19:48:34 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter password:
Connected to an idle instance.
Create server parameter file
SQL> CREATE SPFILE FROM PFILE;
File created.
SQL> STARTUP OPEN READ ONLY
ORACLE instance started.
Total System Global Area 289406976 bytes
Fixed Size 1248576 bytes
Variable Size 100664000 bytes
Database Buffers 180355072 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.
SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE CONTENTS='TEMPORARY';
TABLESPACE_NAME
------------------------------
TEMP
SQL> SELECT * FROM V$TEMPFILE;
FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED
---------- ---------------- --------- ---------- ---------- ------- ----------
BYTES BLOCKS CREATE_BYTES BLOCK_SIZE
---------- ---------- ------------ ----------
NAME
--------------------------------------------------------------------------------
1 535157 16-MAY-07 3 1 ONLINE READ WRITE
20971520 2560 20971520 8192
F:\ORACLE\PRODUCT\10.2.0\ORADATA\KEYSTONESTDY\TEMP01.DBF
SQL> SHOW USER
USER is "SYS"
SQL>
Create Redo log file for standby database
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
Note:
Thanks for visit my blog. Please try physical database creation in testing env.If any problem please post your comments..
Subscribe to:
Posts (Atom)