what? you didn't knew about it? and you thought i am lazy? lazy enough that i would never be able to write any IT certification exam after RHCE (7 years ago)? here is the proof! check and chill...
cheers!
Showing posts with label oracle. Show all posts
Showing posts with label oracle. Show all posts
Monday, April 15, 2013
Wednesday, February 23, 2011
step by step, oracle weblogic cluster on Linux
as a part of oracle taxation management software installation, i had to go through weblogic cluster installation, below are the step by step i used to perform a test installation, you can expect more posts on this topic in near future. the guide is taken from this site.
cheers
Friday, August 21, 2009
Concept behind IPMP confiugration for RAC's Public & Interconnect, Solaris
For our coming "going to born" baby Oracle 11g RAC on Solaris 10, i wrote a step by step pre installation task guide, that i am going to release pretty soon, though i mentioned on that guide about configuration of IPMP, i was expecting that our system admins will be able to do by themselves but unfortunately it was just new for them, so i have to make more research to discuss the exact IPMP concept and configuration with them. Here are my finding
Consider you have 4 Network cards in your machine i.e.
1. c0 ---> Public IP Interface
2. c1 ---> Public IP Interface (standby)
3. c2 ---> Private Interconnect IP Interface
4. c3 ---> Private Interconnect IP Interface (standby)
so consider if you have subnet (172.168.1.0) for Public Interface and (10.2.1.0) as private interconnect subnet, i am not considering netmask and other IP/Network configuration in my example, but you should keep in mind that all Public IP's for RAC network configuration should be in single/same Subnet, while all Interconnect IP's should also be in single/same subnet.
So now 1st to configure Public Interface you need three/3 IPs e.g.
172.168.1.1, 172.168.1.2 & 172.168.1.3
so you will assign them like:
1. c0 ---> will be assign 172.168.1.1 --- called Physical IP
2. c0:1 ---> will be assign 172.168.1.2 --- called Test IP
3. c1 ---> will be assign 172.168.1.3 --- called Test IP
all three should be in same/single IPMP group.
for private interconnect, you need also three IPs e.g.
10.2.1.1, 10.2.1.2 & 10.2.1.3
1. c2 ---> will be assign 10.2.1.1 --- called Physical IP
2. c2:1 ---> will be assign 10.2.1.2 --- called Test IP
3. c3 ---> will be assign 10.2.1.3 --- called Test IP
all three should be in same/single IPMP group other then Public IPMP group. You should NOT keep Public & Interconnect IPMP group names similar, otherwise it can cause serious network problems and even ORACLE discourage doing this.
References:
1. Metalink Note: 283107.1
2. Metalink Note: 368464.1
Cheers
Consider you have 4 Network cards in your machine i.e.
1. c0 ---> Public IP Interface
2. c1 ---> Public IP Interface (standby)
3. c2 ---> Private Interconnect IP Interface
4. c3 ---> Private Interconnect IP Interface (standby)
so consider if you have subnet (172.168.1.0) for Public Interface and (10.2.1.0) as private interconnect subnet, i am not considering netmask and other IP/Network configuration in my example, but you should keep in mind that all Public IP's for RAC network configuration should be in single/same Subnet, while all Interconnect IP's should also be in single/same subnet.
So now 1st to configure Public Interface you need three/3 IPs e.g.
172.168.1.1, 172.168.1.2 & 172.168.1.3
so you will assign them like:
1. c0 ---> will be assign 172.168.1.1 --- called Physical IP
2. c0:1 ---> will be assign 172.168.1.2 --- called Test IP
3. c1 ---> will be assign 172.168.1.3 --- called Test IP
all three should be in same/single IPMP group.
for private interconnect, you need also three IPs e.g.
10.2.1.1, 10.2.1.2 & 10.2.1.3
1. c2 ---> will be assign 10.2.1.1 --- called Physical IP
2. c2:1 ---> will be assign 10.2.1.2 --- called Test IP
3. c3 ---> will be assign 10.2.1.3 --- called Test IP
all three should be in same/single IPMP group other then Public IPMP group. You should NOT keep Public & Interconnect IPMP group names similar, otherwise it can cause serious network problems and even ORACLE discourage doing this.
References:
1. Metalink Note: 283107.1
2. Metalink Note: 368464.1
Cheers
Thursday, August 6, 2009
ORA-01157: cannot identify/lock data file
Yesterday when we were testing auto startup of development environment databases, i found that one of the database is getting following error:
ERROR at line 1:
ORA-01157: cannot identify/lock data file 29 - see DBWR trace file
ORA-01110: data file 29:
'/opt/oracle/product/oracle9i/dbs/C:oracleproduct10.2.0oradatapentahoptho_ts.dbf
'
from above datafile name you have realized that its a kinda jerk :s someone has made a datafile with no sense and then he/she have removed the file by O.S command, but he/she did'nt updated database about it !
So during the test when we were starting this database it came untill mount stage and then got stuck !!! i.e.
Problem:
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 29 - see DBWR trace file
ORA-01110: data file 29:
'/opt/oracle/product/oracle9i/dbs/C:oracleproduct10.2.0oradatapentahoptho_ts.dbf
'
so to fix it, i did following:
Solution:
SQL>alter database datafile 29 OFFLINE DROP;
SQL>alter database open;
cheers
ERROR at line 1:
ORA-01157: cannot identify/lock data file 29 - see DBWR trace file
ORA-01110: data file 29:
'/opt/oracle/product/oracle9i/dbs/C:oracleproduct10.2.0oradatapentahoptho_ts.dbf
'
from above datafile name you have realized that its a kinda jerk :s someone has made a datafile with no sense and then he/she have removed the file by O.S command, but he/she did'nt updated database about it !
So during the test when we were starting this database it came untill mount stage and then got stuck !!! i.e.
Problem:
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 29 - see DBWR trace file
ORA-01110: data file 29:
'/opt/oracle/product/oracle9i/dbs/C:oracleproduct10.2.0oradatapentahoptho_ts.dbf
'
so to fix it, i did following:
Solution:
SQL>alter database datafile 29 OFFLINE DROP;
SQL>alter database open;
cheers
Thursday, July 30, 2009
howto autostart database & listener under Solaris & Linux
whenever our office require to do some maintenance work, they ask us to take databases down, mostly it is on weekend and it turns to become a headache...we found that this thing is happening only or mostly with developer's environment database servers...so we decided to configure all developer's environment databases on autostart !
here is a small howto:
step 1: Modify oratab
For Solaris:
$vi /var/opt/oracle/oratab
For Linux:
$vi /etc/oratab
at the end of oratab file, you will see following lines :
SID1:/opt/oracle/product/oracle9i:N
SID2:/opt/oracle/product/oracle9i:N
replace above with:
SID1:/opt/oracle/product/oracle9i:Y
SID2:/opt/oracle/product/oracle9i:Y
step 2: Creating dbora.sh script
$vi /etc/init.d/dbora.sh
write following content in it:
#!/sbin/sh
ORACLE_HOME=/opt/oracle/product/oracle9i
ORACLE_OWNER=oracle
if [ ! -f $ORACLE_HOME/bin/dbstart ]
then
echo "Oracle startup: cannot start"
exit
fi
case "$1" in
'start') # Start the Oracle databases and listeners
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/dbstart"
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/lsnrctl start"
;;
'stop') # Stop the Oracle databases and listeners
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/lsnrctl stop"
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/dbshut"
;;
esac
step 3: configure O.S to run dbora.sh on bootup/shutdown
For Solaris:
Link the dbora.sh to /etc/rc directories:
ln -s /etc/init.d/dbora.sh /etc/rc0.d/K10dbora
ln -s /etc/init.d/dbora.sh /etc/rc2.d/S99dbora
or
cp /etc/init.d/dbora.sh /etc/rc0.d/K10dbora
cp /etc/init.d/dbora.sh /etc/rc2.d/S99dbora
Note:we did cp on solaris 9. not sure but ln was not working.
For Linux:
$chkconfig --level 345 dbora.sh on
step 4: create pfile under $ORACLE_HOME/dbs --- OPTIONAL
if your database do not have pfile under $ORACLE_HOME/dbs you need to create by following command.
connect as "sysdba"
SQL> create pfile='$ORACLE_HOME/dba/initSID.ora' from spfile;
cheers
here is a small howto:
step 1: Modify oratab
For Solaris:
$vi /var/opt/oracle/oratab
For Linux:
$vi /etc/oratab
at the end of oratab file, you will see following lines :
SID1:/opt/oracle/product/oracle9i:N
SID2:/opt/oracle/product/oracle9i:N
replace above with:
SID1:/opt/oracle/product/oracle9i:Y
SID2:/opt/oracle/product/oracle9i:Y
step 2: Creating dbora.sh script
$vi /etc/init.d/dbora.sh
write following content in it:
#!/sbin/sh
ORACLE_HOME=/opt/oracle/product/oracle9i
ORACLE_OWNER=oracle
if [ ! -f $ORACLE_HOME/bin/dbstart ]
then
echo "Oracle startup: cannot start"
exit
fi
case "$1" in
'start') # Start the Oracle databases and listeners
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/dbstart"
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/lsnrctl start"
;;
'stop') # Stop the Oracle databases and listeners
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/lsnrctl stop"
su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/dbshut"
;;
esac
step 3: configure O.S to run dbora.sh on bootup/shutdown
For Solaris:
Link the dbora.sh to /etc/rc directories:
ln -s /etc/init.d/dbora.sh /etc/rc0.d/K10dbora
ln -s /etc/init.d/dbora.sh /etc/rc2.d/S99dbora
or
cp /etc/init.d/dbora.sh /etc/rc0.d/K10dbora
cp /etc/init.d/dbora.sh /etc/rc2.d/S99dbora
Note:we did cp on solaris 9. not sure but ln was not working.
For Linux:
$chkconfig --level 345 dbora.sh on
step 4: create pfile under $ORACLE_HOME/dbs --- OPTIONAL
if your database do not have pfile under $ORACLE_HOME/dbs you need to create by following command.
connect as "sysdba"
SQL> create pfile='$ORACLE_HOME/dba/initSID.ora' from spfile;
cheers
Wednesday, July 15, 2009
ORA-00018 maximum number of sessions exceeded
We had ORA-00018 problem in one of our database in data center, in fact, the application users suddenly have been increased and we were not informed about it, so as soon as the default sessions limit went off, the database blocked all new sessions, and even if we were connecting to database with SYS, it was reporting somewhat, database is not available... though there was't any error reporting in alert-logs except few session waiting too long in queue. This could be because of session queue was over loaded and it was keeping all new database connections in queue for too long.
I followed this thread. to resolve it, though the terrible part of the story is, it can only be solved by bouncing the instance... So we had to wait until working hours :(
SPECIAL NOTE: if you are having this problem in RAC environment, then you should give a look to Riyaj Post
cheers
I followed this thread. to resolve it, though the terrible part of the story is, it can only be solved by bouncing the instance... So we had to wait until working hours :(
SPECIAL NOTE: if you are having this problem in RAC environment, then you should give a look to Riyaj Post
cheers
Tuesday, July 7, 2009
create standby when archive logs are lost/corrupt
I have been in such situation where somehow we lost or (most recently) got corrupt the archive logs from production site and we had to synchronize our standby/DataGaurd site. Anyhow the terrible fact about such situation is that we had to recreate standby database, here is the scenario with solution :)
Scenario: Your database is archiving itself to shared storage (of course in RAC) or it is using local disk (in NON-RAC) and somehow your disk get failed and you lost all/(some of) your archive files, or oracle wrote archive log on a corrupt block and a specific archive log file is lost. In either case you lost archives before transferring to standby/Dataguard database.
Consequences: You d'nt have any other way to regenerate that archive file and you are now to a point where you have to recreate that specific standby database.
Protection: In our office, we have a slight protection against such failure i.e. we have a shell cron script that is running after every 5 min. and coping archive log files to a NFS mounted disk. This is very handy in cases when we lost archive log file on disk but it is found in NFS mounted disk. BUT last week we even did'nt had those missing archive log files in NFS mounted disk.
Solution: You have to undergo with following procedure.
Step 1: Create a new rman based backup, EXCLUDING Archive Log Files, Including control file for standby.
Step 2: Copy all archives to standby site, those who are recent then your last backup. i.e. those archive log file that were generated after you made step 1 backup.
Step 3: recreate your standby database just as normal.
PS: you can take help from my post to accomplish step 3.
Request: if you know any other better way then this, d'nt forget to share with me :p
Cheers
Scenario: Your database is archiving itself to shared storage (of course in RAC) or it is using local disk (in NON-RAC) and somehow your disk get failed and you lost all/(some of) your archive files, or oracle wrote archive log on a corrupt block and a specific archive log file is lost. In either case you lost archives before transferring to standby/Dataguard database.
Consequences: You d'nt have any other way to regenerate that archive file and you are now to a point where you have to recreate that specific standby database.
Protection: In our office, we have a slight protection against such failure i.e. we have a shell cron script that is running after every 5 min. and coping archive log files to a NFS mounted disk. This is very handy in cases when we lost archive log file on disk but it is found in NFS mounted disk. BUT last week we even did'nt had those missing archive log files in NFS mounted disk.
Solution: You have to undergo with following procedure.
Step 1: Create a new rman based backup, EXCLUDING Archive Log Files, Including control file for standby.
Step 2: Copy all archives to standby site, those who are recent then your last backup. i.e. those archive log file that were generated after you made step 1 backup.
Step 3: recreate your standby database just as normal.
PS: you can take help from my post to accomplish step 3.
Request: if you know any other better way then this, d'nt forget to share with me :p
Cheers
Monday, June 29, 2009
Steps to shutdown Dataguard/Standby database for maintenance work
In our office there are certain situations where we have to shutdown Dataguard site, mostly for maintenance work, such as shared disk, server's hardware or any general maintenance at our DR (Disaster Recovery) site.
Point behind shutdown/startup of Dataguard is when Dataguard/Standby database come online it should get synchronized to Production/Primary site.
There may be many ways to achieve our goal, here is my recipe:
at primary/production database
SQL> alter system set log_archive_dest_state_2=defer scope = both sid = '*';
PS: in case of RAC: this step should be done at just ONE of the RAC node.
step 2: Disable auto recovery at standby site:
at secondary/DR database
SQL> alter database recover managed standby database cancel;
PS: In case of RAC: above command should run on just ONE of the standby RAC node.
SQL> shutdown immediate
PS: in case of RAC: shutdown should be done on ALL standby RAC nodes.
PS: in case of RAC: this step should be done at just ONE of the RAC node.
cheers
Point behind shutdown/startup of Dataguard is when Dataguard/Standby database come online it should get synchronized to Production/Primary site.
There may be many ways to achieve our goal, here is my recipe:
SHUTDOWN STANDBY DATABASE
step 1: Disable standby archive writing:at primary/production database
SQL> alter system set log_archive_dest_state_2=defer scope = both sid = '*';
PS: in case of RAC: this step should be done at just ONE of the RAC node.
step 2: Disable auto recovery at standby site:
at secondary/DR database
SQL> alter database recover managed standby database cancel;
PS: In case of RAC: above command should run on just ONE of the standby RAC node.
SQL> shutdown immediate
PS: in case of RAC: shutdown should be done on ALL standby RAC nodes.
STARTUP STANDBY DATABASE
Step 1: Coping all archive from primary to DR generated during shutdown.
in this step we will copy all archive those were generated after standby database was taken down.
$scp /opt/oracle/archive/*.arc oracle@DR-Site-IP:/opt/oracle/archive
Step 2: Startup standby database and configure automatic recovery:
SQL> startup nomount
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect from session;
PS: in case of RAC: repeat following commands on all standby nodes.
SQL> startup nomount
SQL> alter database mount standby database;
step 3: Enable standby archive writing:
at primary/production database
SQL> alter system set log_archive_dest_state_2=enable scope = both sid = '*';Step 1: Coping all archive from primary to DR generated during shutdown.
in this step we will copy all archive those were generated after standby database was taken down.
$scp /opt/oracle/archive/*.arc oracle@DR-Site-IP:/opt/oracle/archive
Step 2: Startup standby database and configure automatic recovery:
SQL> startup nomount
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect from session;
PS: in case of RAC: repeat following commands on all standby nodes.
SQL> startup nomount
SQL> alter database mount standby database;
step 3: Enable standby archive writing:
at primary/production database
PS: in case of RAC: this step should be done at just ONE of the RAC node.
cheers
Wednesday, June 10, 2009
Steps to configure Shared Server
Yesterday, Eric asked me to configure Shared Server on one of our DR site database, the idea was to test some application level connectivity issue. Though oracle strongly discourage you to run your database server as shared (MTS) still there are cases such as JAVA based application connection where shared server could be handy.
I found this post very helpful, short & focused.
cheers
I found this post very helpful, short & focused.
cheers
Tuesday, May 26, 2009
DataGuard - Step by Step
1. Intro
Oracle9i Data Guard provides an extensive set of data protection and disaster recovery features to help you to survive disasters, human errors, and corruptions that can incapacitate a database. Based on business requirements, these features can be advantageously combined with other Oracle9i high-availability and disaster recovery features for enhanced levels of high availability and disaster protection.
Oracle9i Data Guard is the management, monitoring, and automation software that works with a production database and one or more standby databases to protect your data against failures, errors, and corruptions that might otherwise destroy your database. It protects critical data by automating the creation, management, and monitoring of the databases and other components in a Data Guard configuration. It automates the otherwise manual process of maintaining a transactional consistent copy of an Oracle production database, called a standby database, that can be used if the production database is taken offline for routine maintenance or becomes damaged.
In a Data Guard configuration, a production database is referred to as a primary database. Using a backup copy of the primary database, you can create from one to nine physical and logical standby databases and incorporate them in a Data Guard configuration. Primary and standby databases can be running on a single node or in a Real Application Clusters environment. Every standby database is associated with only one primary database. However, a single primary database can support multiple physical standby databases, logical standby databases, or a mix of both in the same configuration:
Physical standby databases A physical standby database is physically identical to the primary database, with on-disk database structures that are identical to the primary database on a block-for-block basis. The physical standby database is updated by performing recovery. It can either be recovering data or open for read-only reporting.
Logical standby databases A logical standby database is logically identical to the primary database. The logical standby database is updated using SQL statements. The tables in a logical standby database can be used simultaneously for recovery and for other tasks such as reporting, summations, and queries.
2. Implementation:
2.1 Checking Force Logging:
This check need to be done on both databases i.e. (SID01 & SID02)
| SQL> select force_logging from v$database; |
If the result of above command shows “NO” then we need run following command.
2.2. Enable Force Logging:
| SQL> alter database force logging; |
2.3 Checking Archive log mode:
Both databases have to be in Archive Mode. If they are NOT we have to enable Archive mode.
Checking Archive Mode:
| SQL> archive log list Database log mode Archive Mode Automatic archival Enable Archive destination /opt/oracle/archive/SID01 Oldest online log sequence 2386 Current log sequence 2388 |
If you get “Database log mode NO ARCHIVE MODE” it means you have to do following steps to convert it in ARCHIVE MODE.
If your database is already in ARCHIVE MODE skip the following step.
Enabling Archive Mode:
| SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=’LOCATION= SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE SCOPE=BOTH; SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT =’ SQL> ALTER SYSTEM SET LOG_ARCHIVE_START= TRUE SCOPE=SPFILE; SQL> ALTER SYSTEM SET DG_BROKER_START= TRUE SCOPE=BOTH; SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP MOUNT; SQL> ALTER DATABASE ARCHIVELOG; SQL> ALTER DATABASE OPEN; SQL> ARCHIVE LOG LIST; |
2.4 Creating RMAN base backup:
SID01:
| { allocate channel canal01 type disk ; allocate channel canal02 type disk ; sql 'alter database backup controlfile to trace'; sql 'alter system archive log current'; backup filesperset 4 format “/backup/rman/dumps/SID01/SID01_db_%U.bkp" ( database ); backup format “/backup/rman/dumps/SID01/SID01_stdby_%U.cf" current controlfile for standby keep forever nologs; backup format "/backup/rman/dumps/SID01/SID01_arch_%U.bkp" archivelog all; release channel canal01; release channel canal02 } |
SID02:
| { allocate channel canal01 type disk ; allocate channel canal02 type disk ; sql 'alter database backup controlfile to trace'; sql 'alter system archive log current'; backup filesperset 4 format “/backup/rman/dumps/SID02/SID02_db_%U.bkp" ( database ); backup format “/backup/rman/dumps/SID02/SID02_stdby_%U.cf" current controlfile for standby keep forever nologs; backup format "/backup/rman/dumps/SID02/SID02_arch_%U.bkp" archivelog all; release channel canal01; release channel canal02 } |
2.5 Coping RMAN Backup to DR:
SID01:
| SID01:/backup/rman/dumps/SID01 >scp * oracle@10.2.24.52:/backup/rman/dumps/SID01 |
SID02:
| SID02:/backup/rman/dumps/SID02 >scp * oracle@10.2.24.52:/backup/rman/dumps/SID02 |
2.6 Coping Archive from primary to DR:
SID01:
| SID01:/opt/oracle/archive/SID01 >scp *.arc oracle@10.2.24.52: /opt/oracle/archive/SID01 |
SID02:
| SID02:/opt/oracle/archive/SID02 >scp *.arc oracle@10.2.24.52: /opt/oracle/archive/SID02 |
2.6 Checking tnsnames alias & listener:
You need to check tnsnames alias and listener status on both site from both direction.
SID01:
From Primay to DR:
| $tnsping SID01dr |
From DR to Primary:
| $tnsping SID01prod |
SID02:
From Primay to DR:
| $tnsping SID02dr |
From DR to Primary:
| $tnsping SID02prod |
2.7 Create pfile for standby on primary
You have to create pfile from spfile at primary database and later copy it to DR site.
SID01:
| SQL> create pfile='/tmp/initSID01.ora.standby' from spfile; |
SID02:
| SQL> create pfile='/tmp/initSID02.ora.standby' from spfile; |
2.8 Coping pfile from primary to standby – DR site
SID01:
| scp /tmp/initSID01.ora.standby oracle@10.2.24.52:/opt/oracle/admin/SID01/pfile/ |
SID02:
| scp /tmp/initSID02.ora.standby oracle@10.2.24.52:/opt/oracle/admin/SID02/pfile/ |
2.9 Edit standby parameter file
SID01:
| $vi initSID01.ora.standby output: *.background_dump_dest='/opt/oracle/admin/SID01/bdump' *.background_core_dest='/opt/oracle/admin/SID01/bdump' *.background_user_dest='/opt/oracle/admin/SID01/bdump' *.compatible='9.2.0.0.0' *.control_files='/opt/oracle/oradata/SID01/control01.ctl', '/opt/oracle/oradata/SID01/control02.ctl','/opt/oracle/oradata/SID01dr/control03.ctl' *.core_dump_dest='/opt/oracle/admin/SID01/cdump' *.db_block_size=8192 *.db_cache_size=50331648 *.db_domain='' *.db_file_multiblock_read_count=16 *.db_name='SID01' *.fast_start_mttr_target=300 *.hash_join_enabled=TRUE *.instance_name='SID01' *.java_pool_size=0 *.large_pool_size=33554432 *.log_archive_dest_1='LOCATION=/opt/oracle/archive/SID01' *.log_archive_format='arch_SID01_%t_%s.arc' *.log_archive_start=TRUE *.open_cursors=300 *.pga_aggregate_target=50331648 *.processes=150 *.query_rewrite_enabled='FALSE' *.remote_login_passwordfile='EXCLUSIVE' *.shared_pool_size=268435456 *.sort_area_size=524288 *.star_transformation_enabled='FALSE' *.timed_statistics=TRUE *.undo_management='AUTO' *.undo_retention=10800 *.undo_tablespace='UNDOTBS1' *.user_dump_dest='/opt/oracle/admin/SID01/udump' *.dg_broker_start='TRUE' *.standby_archive_dest='/backup/archive/SID01/standby' *.standby_file_management='AUTO' *.remote_archive_enable = 'TRUE' |
SID02:
| $vi initSID02.ora.standby output: *.background_dump_dest='/opt/oracle/admin/SID02/bdump' *.background_core_dest='/opt/oracle/admin/SID02/bdump' *.background_user_dest='/opt/oracle/admin/SID02/bdump' *.compatible='9.2.0.0.0' *.control_files='/opt/oracle/oradata/SID02/control01.ctl', '/opt/oracle/oradata/SID02/control02.ctl','/opt/oracle/oradata/SID02/control03.ctl' *.core_dump_dest='/opt/oracle/admin/SID02/cdump' *.db_block_size=8192 *.db_cache_size=50331648 *.db_domain='' *.db_file_multiblock_read_count=16 *.db_name='SID01' *.fast_start_mttr_target=300 *.hash_join_enabled=TRUE *.instance_name='SID01' *.java_pool_size=0 *.large_pool_size=33554432 *.log_archive_dest_1='LOCATION=/opt/oracle/archive/SID02' *.log_archive_format='arch_SID02_%t_%s.arc' *.log_archive_start=TRUE *.open_cursors=300 *.pga_aggregate_target=50331648 *.processes=150 *.query_rewrite_enabled='FALSE' *.remote_login_passwordfile='EXCLUSIVE' *.shared_pool_size=268435456 *.sort_area_size=524288 *.star_transformation_enabled='FALSE' *.timed_statistics=TRUE *.undo_management='AUTO' *.undo_retention=10800 *.undo_tablespace='UNDOTBS1' *.user_dump_dest='/opt/oracle/admin/SID02/udump' *.dg_broker_start='TRUE' *.standby_archive_dest='/backup/archive/SID02/standby' *.standby_file_management='AUTO' *.remote_archive_enable = 'TRUE' |
PS: In above text block the important parameters that needs to be changed are in biger fonts.
2.10 Copy initSID01.ora.standby to $ORACLE_HOME/dbs/:
SID01:
| cp /opt/oracle/admin/pfile/initSID01.ora.standby $ORACLE_HOME/dbs/initSID02.ora |
SID02:
| cp /opt/oracle/admin/pfile/initSID02.ora.standby $ORACLE_HOME/dbs/initSID02.ora |
2.11 Create executa_duplcate.sh:
SID01:
| Vi executa_duplcate.sh Output: export ORACLE_SID=SID01 sqlplus /nolog @/opt/oracle/admin/SID01/standby/inicia_SID01_standby.sql rman @/opt/oracle/admin/SID01/standby/executa_duplicate.rman sqlplus /nolog @/opt/oracle/admin/SID01/standby/gera_stb_redolog.sql |
SID02:
| Vi executa_duplcate.sh Output: export ORACLE_SID=SID02 sqlplus /nolog @/opt/oracle/admin/SID02/standby/inicia_SID02_standby.sql rman @/opt/oracle/admin/SID02/standby/executa_duplicate.rman sqlplus /nolog @/opt/oracle/admin/SID02/standby/gera_stb_redolog.sql |
PS: executa_duplcate.sh calls three sub scripts, that should be created in same directory as following:
2.11.1 create inicia_SID02_standby.sql
SID01:
| Vi inicia_SID01_standby.sql Output: connect / as sysdba; shutdown abort; startup nomount pfile='/opt/oracle/admin/SID01/pfile/initSID01.ora.standby'; create spfile='/opt/oracle/admin/SID01/pfile/spfileSID01' from pfile='/opt/oracle/admin/SID01/pfile/initSID01.ora.standby'; shutdown immediate; startup nomount; exit; |
SID02:
| Vi inicia_SID02_standby.sql Output: connect / as sysdba; shutdown abort; startup nomount pfile='/opt/oracle/admin/SID02/pfile/initSID02.ora.standby'; create spfile='/opt/oracle/admin/SID02/pfile/spfileSID02' from pfile='/opt/oracle/admin/SID02/pfile/initSID02.ora.standby'; shutdown immediate; startup nomount; exit; |
2.11.2 Create executa_duplicate.rman
SID01:
| Vi executa_duplicate.rman Output: connect auxiliary / ; connect target sys/******@SID01prod; run { allocate auxiliary channel canal01 device type disk format '/backup/rman/dumps/SID01/SID01_db_%U.bkp'; allocate auxiliary channel canal02 device type disk format '/backup/rman/dumps/SID01/SID01_db_%U.bkp'; duplicate target database for standby nofilenamecheck dorecover; release channel canal01; release channel canal02; } exit; |
SID02:
| Vi executa_duplicate.rman Output: connect auxiliary / ; connect target sys/******@SID02prod; run { allocate auxiliary channel canal01 device type disk format '/backup/rman/dumps/SID02/SID02_db_%U.bkp'; allocate auxiliary channel canal02 device type disk format '/backup/rman/dumps/SID02/SID02_db_%U.bkp'; duplicate target database for standby nofilenamecheck dorecover; release channel canal01; release channel canal02; } exit; |
2.11.3 create gera_stb_redolog.sql
SID01:
| Vi gera_stb_redolog.sql Output: alter database add standby logfile group 4 ( '/opt/oracle/oradata/SID01/SID01_sblog04_a.dbf' ) size 100M; alter database add standby logfile group 5 ( '/opt/oracle/oradata/SID01/SID01_sblog05_a.dbf' ) size 100M; alter database add standby logfile group 6 ( '/opt/oracle/oradata/SID01/SID01_sblog06_a.dbf' ) size 100M; alter database add standby logfile group 7 ( '/opt/oracle/oradata/SID01/SID01_sblog07_a.dbf' ) size 100M; |
SID02:
| Vi gera_stb_redolog.sql Output: alter database add standby logfile group 4 ( '/opt/oracle/oradata/SID02/SID02_sblog04_a.dbf' ) size 100M; alter database add standby logfile group 5 ( '/opt/oracle/oradata/SID02/SID02_sblog05_a.dbf' ) size 100M; alter database add standby logfile group 6 ( '/opt/oracle/oradata/SID02/SID02_sblog06_a.dbf' ) size 100M; alter database add standby logfile group 7 ( '/opt/oracle/oradata/SID02/SID02_sblog07_a.dbf' ) size 100M; |
2.12 run executa_duplcate.sh
SID01:
| $./executa_duplcate.sh |
SID02:
| $./executa_duplcate.sh |
2.13 Shutdown and Startup to mount:
SID01:
| SQL> shutdown abort SQL> startup nomount SQL> alter database mount standby database; |
SID02:
| SQL> shutdown abort SQL> startup nomount SQL> alter database mount standby database; |
2.14: Enable Archive Dest for standby at Primary:
At Primary:
SID01:
| SQL> alter system set log_archive_dest_2='SERVICE=SID01dr arch' scope=both; SQL> alter system set log_archive_dest_state_2=enable scope=both; |
SID02:
| SQL> alter system set log_archive_dest_2='SERVICE=SID02dr arch' scope=both; SQL> alter system set log_archive_dest_state_2=enable scope=both; |
2.15 Enable automatic recovery at standby:
SID01:
| SQL> alter database recover managed standby database disconnect from session; |
SID02:
| SQL> alter database recover managed standby database disconnect from session; |
2.16 Define FAL server & client at standby database
SID01:
| SQL> alter system set fal_server='SID01prod' scope=both; SQL> alter system set fal_client='SID01dr' scope=both; |
SID02:
| SQL> alter system set fal_server='SID02prod' scope=both; SQL> alter system set fal_client='SID02dr' scope=both; |
cheers
Subscribe to:
Posts (Atom)
