ORA-16069: Archive Log standby database activation identifier mismatch
Last week, I was testing "Using a Physical Standby Database for Read/Write Testing and Reporting" documented at "http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/scenarios.htm#CIHIABDH".
I was able to activate standby database perform read-write operations, test my application, flashback the database back to standby mode and start the managed recovery once again.
However, my primary database started complaining while shipping the logs to standby.
ORA-16401: ARCHIVELOG REJECTED BY RFS
At the same time I could see the following error message in standby.
ORA-16069: Archive Log standby database activation identifier mismatch
I was running 10.2.0.2, so when I contacted oracle, they asked me apply the latest 10.2.0.4 patchset. So I will not talk about root case and I will talk on how I fixed ORA-16069 problem.
On Primary
----------
SQL> alter database create standby controlfile as 'C:\standby.ctl';
On Standby
-----------
SQL> shutdown immediate
Replace your standby controlfile with new standby controlfile created in previous controlfile.
SQL> startup mount
SQL> recover managed standby database disconnect;
The above steps will fix ORA-16069 and enable log shipping as well as GAP resolution from primary to standby.
Saturday, July 25, 2009
Friday, July 24, 2009
DBUA fails with LRM-00116 & ORA-01078
Recently I was upgrading the 10.2.0.2 database to 10.2.0.4 on Windows 2003 server. DBUA failed with "ORA-01078: failure in processing system parameters" error while doing the pre upgrade checks. I looked up in the C:\Oracle\product\10.2.4\cfgtoollogs\dbua\DBNAME\upgrade0\PreUpgrade.log and found the following.
LRM-00101: unknown parameter name 'e:\WEBCT64DATA\datafiledb_name'
LRM-00116: syntax error at 'E:\MYDBdb_uni' following '='
ORA-01078: failure in processing system parameters
It didn't make any sense to me, as I had no parameter with values as 'e:\MYDB\datafiledb_name' as this. However, when I closely looked at this I realised that I had parameter called db_file_name_convert which had value set to 'd:\dbname\datafile\', 'E:\MYDB\datafile\' and it was followed by the parameter db_name.
Now I knew something is wrong here which oracle DBUA doesn't like. As usual I checked up in metalink and found a note. 397802.1 "DBUA Reports ORA-01078 While Upgrading A Data Guard Database".
Yes, this note confirms that DBUA doesn't like this parameter value and as solution recommends it to set to null values. The metalink article doesn't talk about why DBUA doesn't like this and as per the note this can happen only with this data guard related parameters and hence issues associated with upgrading data giard database.
I did some further dig up on this issue and found it to be something else.
Its seems to me on windows DBUA treats any trailing "\" in pfile/spfile as an escape character and hence and trailing backspace which are part of values for any init params will be ignored and nect parameter name will be appended to the value.
For e.g.
In my case the param file looks like...
....
....
db_file_name_convert='D:\MYDB\DATAFILE\', 'E:\MYDB\DATAFILE\'
DB_NAME=MYDB
So as there is a "\" in end it escapes it and reads the file as "db_file_name_convert='D:\MYDB\DATAFILE\', 'E:\MYDB\DATAFILEDB_NAME=MYDB" which is invalid and it doesn't like and complains. So this error has nothing to do with data guard instances.
This error can be caused by any parameter in init file which has values with "/" at the end no matter you are run data guard or single instance.
For e.g.
user_dump_Dest="c:\oracle\admin\udump\"
background_dump_Dest="c:\oracle\admin\bdump\"
core_dump_Dest="c:\oracle\admin\cdump\"
It seems to be windows specific issue. So if DBUA fails with LRM-00116 & ORA-01078 on windows please check the init file for values which has a "\" at the end.
LRM-00101: unknown parameter name 'e:\WEBCT64DATA\datafiledb_name'
LRM-00116: syntax error at 'E:\MYDBdb_uni' following '='
ORA-01078: failure in processing system parameters
It didn't make any sense to me, as I had no parameter with values as 'e:\MYDB\datafiledb_name' as this. However, when I closely looked at this I realised that I had parameter called db_file_name_convert which had value set to 'd:\dbname\datafile\', 'E:\MYDB\datafile\' and it was followed by the parameter db_name.
Now I knew something is wrong here which oracle DBUA doesn't like. As usual I checked up in metalink and found a note. 397802.1 "DBUA Reports ORA-01078 While Upgrading A Data Guard Database".
Yes, this note confirms that DBUA doesn't like this parameter value and as solution recommends it to set to null values. The metalink article doesn't talk about why DBUA doesn't like this and as per the note this can happen only with this data guard related parameters and hence issues associated with upgrading data giard database.
I did some further dig up on this issue and found it to be something else.
Its seems to me on windows DBUA treats any trailing "\" in pfile/spfile as an escape character and hence and trailing backspace which are part of values for any init params will be ignored and nect parameter name will be appended to the value.
For e.g.
In my case the param file looks like...
....
....
db_file_name_convert='D:\MYDB\DATAFILE\', 'E:\MYDB\DATAFILE\'
DB_NAME=MYDB
So as there is a "\" in end it escapes it and reads the file as "db_file_name_convert='D:\MYDB\DATAFILE\', 'E:\MYDB\DATAFILEDB_NAME=MYDB" which is invalid and it doesn't like and complains. So this error has nothing to do with data guard instances.
This error can be caused by any parameter in init file which has values with "/" at the end no matter you are run data guard or single instance.
For e.g.
user_dump_Dest="c:\oracle\admin\udump\"
background_dump_Dest="c:\oracle\admin\bdump\"
core_dump_Dest="c:\oracle\admin\cdump\"
It seems to be windows specific issue. So if DBUA fails with LRM-00116 & ORA-01078 on windows please check the init file for values which has a "\" at the end.
Thursday, June 18, 2009
Frequently used commands in Data Guard using Broker
· How to upgrade/downgrade the protection mode?
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
· How to change the init parameter setting related to data guard?
DGMGRL> EDIT DATABASE 'MYDBdr' SET PROPERTY 'StandbyFileManagement'='AUTO';
· How to change the redo shipping process or mode?
DGMGRL> EDIT DATABASE 'MYDB’ PROPERTY 'LogXptMode'='SYNC';
· How to open the standby database in read-only mode?
DGMGRL> EDIT DATABASE 'MYDBdr' SET STATE='READ-ONLY';
· How to stop the recovery process on standby database?
DGMGRL> EDIT DATABASE 'MYDBdr' SET STATE='LOG-APPLY-OFF’;
· How to put the standby database back in recovery mode?
DGMGRL> EDIT DATABASE 'MYDBdr' SET STATE='ONLINE’;
· How to stop the redo shipping on primary database?
DGMGRL> EDIT DATABASE 'MYDB' SET STATE='LOG-TRANSPORT-OFF’;
· How to start the redo shipping on primary database?
DGMGRL> EDIT DATABASE 'MYDB' SET STATE='ONLINE’;
· How to check the status of broker configuration?
DGMGRL> SHOW CONFIGURATION;
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
· How to change the init parameter setting related to data guard?
DGMGRL> EDIT DATABASE 'MYDBdr' SET PROPERTY 'StandbyFileManagement'='AUTO';
· How to change the redo shipping process or mode?
DGMGRL> EDIT DATABASE 'MYDB’ PROPERTY 'LogXptMode'='SYNC';
· How to open the standby database in read-only mode?
DGMGRL> EDIT DATABASE 'MYDBdr' SET STATE='READ-ONLY';
· How to stop the recovery process on standby database?
DGMGRL> EDIT DATABASE 'MYDBdr' SET STATE='LOG-APPLY-OFF’;
· How to put the standby database back in recovery mode?
DGMGRL> EDIT DATABASE 'MYDBdr' SET STATE='ONLINE’;
· How to stop the redo shipping on primary database?
DGMGRL> EDIT DATABASE 'MYDB' SET STATE='LOG-TRANSPORT-OFF’;
· How to start the redo shipping on primary database?
DGMGRL> EDIT DATABASE 'MYDB' SET STATE='ONLINE’;
· How to check the status of broker configuration?
DGMGRL> SHOW CONFIGURATION;
Wednesday, March 11, 2009
Best Practices for Change Data Capture (CDC) Asynchronous Auto Log Archive
Currently I am working with a customer to implement CDC and as a part of this engagement, I was looking for resources on best practices / guidelines to consider while implementing CDC. Even though there is much on Google and Oracle documentation, I thought it will be good idea to consolidate them and publish it here.
Following are the guidelines / best practices to consider while implementing CDC in Asynchronous Auto Log Archive mode.
1. Supplemental Logging
· The asynchronous modes of Change Data Capture work best with appropriate supplemental logging on the source database. Oracle recommends that the source database supplementally log all source table columns that are part of a primary key or function to uniquely identify a row.
· Create an unconditional log group for all source table columns that are captured by any asynchronous change table. This should be done before any change tables are created on a source table. If an unconditional log group is not created for all source table columns to be captured, then when an update DML operation occurs, some unchanged user column values in change tables will be null instead of reflecting the actual source table value.
· If you intend to capture all the column values in a row whenever a column in that row is updated, you can use ALL COLUMN option instead of listing each column one-by-one in the ALTER TABLE statement for supplemental logging. However, do not use this form of the ALTER TABLE statement if all columns are not needed. Logging all columns incurs more overhead than logging selected columns.
· Use force-logging option only if you want to capture the changes out of direct load insert or inserts with nologging. Use this force logging with caution as it may introduce performance overhead on the source database.
· To minimize performance impact in case you want capture even the changes out of direct load inserts or inserts with nologging, consider moving the source tables which are part of such operation to a dedicated tablespace and turn on force-logging at tablespace level instead of database level.
2. Defining Tables and Columns
· Performance, storage & network overhead of the Change Data Capture configuration is proportional to the amount of table and columns, which are part of the configurations.
· Ensure that you include only required and relevant tables and their columns while creating change tables.
· Capture only selective and relevant control columns on the change tables.
· By default, Change Data Capture process, records both (old & new) values in change table. If old values are not required, ensure to capture only new values (parameter CAPTURE_VALUES=>’NEW’) to minimize the performance overhead.
· Oracle recommends that change tables not be created in system tablespaces. This can be accomplished if the publisher's default tablespace is not the system tablespace or if the publisher specifies a tablespace in the options_string parameter.
· Do not specify any constrains on change table as it adds performance overhead during the time of capture. Perform all your data validations in target database.
3. Change Source, Change Sets & Subscriptions
· Keep the number of change sources per source database to minimum. Ideally create one change source in your staging database for each source database. Bear in mind that a change set cannot span across the change sources.
· Keep the number of change sets within a given change source to minimum. Bear in mind that a change set is a set of change data that is guaranteed to be transactionally consistent and subscription cannot span across change sets. If your subscriber needs set of changed data, which spans across various tables in one subscription, then you should group those tables in one change set.
· Keep the number of subscription equal or more than number of subscribers. Bear in mind that given subscription cannot be shared by 2 subscribers. If you have a subscriber who is interested in change set but he is interested in few change tables on daily basis and few change tables in weekly basis, then consider creating two subscriptions for the give subscriber. If you have two subscribers interested in same set of change tables, the create two different subscriptions for a given change set instead of duplicating the source tables in two change sets.
4. Publisher & Subscriber
· It’s highly recommended to create a dedicated publisher user on staging database for publication purpose.
· Oracle recommends that when creating the publisher account on a staging database, the DBA specify a default tablespace for the publisher; the publisher should use this tablespace for any change tables he or she creates.
· Generally, one publisher is sufficient enough to manage multiple change sources/ source databases on given staging database. However, if you have centralized staging databases, which caters multiple source databases and you don’t want to share the access to change source for security reasons, then it makes more sense to have dedicated publisher for each source database.
· If you have multiple source databases and centralized staging databases and only one publisher, then make sure you use options_string while creating change tables and specify appropriate tablespace name to group the change tables from various source databases to their respective tablespaces.
· It’s highly recommended that end application don’t share the subscribers. Each application interested in changed data must have their own dedicated subscribers.
· The publisher must grant the SELECT privilege before a subscriber can subscribe to the change table. The publisher must not grant any DML access (use of INSERT, UPDATE, or DELETE statements) to the subscribers on the change tables because a subscriber might inadvertently change the data in the change table, making it inconsistent with its source. Furthermore, the publisher should avoid creating change tables in schemas to which subscribers have DML access.
5. ARCHIVE_LAG_TARGET
With the AutoLog archive option, Change sets are populated as new archived redo log files arrive on the staging database. The degree of latency depends on the frequency of redo log file switches on the source database. The AutoLog archive option has a higher degree of latency than any other option. However, ARCHIVE_LAG_TARGET parameter can be used to efficiently control this latency by forcing log switches at regular interval. A 0 value disables the time-based thread advance feature; otherwise, the value represents the number of seconds. The typical, or recommended value is 1800 (30 minutes). Extremely low values can result in frequent log switches, which could degrade performance; such values can also make the archiver process too busy to archive the continuously generated logs.
6. Archiving Strategy for Change tables
· When finished using change data, a subscriber must call the DBMS_CDC_SUBSCRIBE.PURGE_WINDOW procedure. This indicates to Change Data Capture and the publisher that the change data is no longer needed by this subscriber and allows auto scheduled DBMS_JOB to execute DBMS_CDC_PUBLISH.PURGE procedure to remove data that subscribers are no longer using from the change tables. This ensures that the size of the change tables does not grow without limit.
· If the subscriber fails to call PURGE_WINDOW after using the changed data, then DBMS_CDC_PUBLISH.PURGE procedure cannot delete unneeded rows from the change table. So, it’s important to ensure that subscriber runs the DBMS_CDC_SUBSCRIBE.PURGE_WINDOW procedure whenever finished using change data.
· In addition to DBMS_CDC_SUBSCRIBE.PURGE_WINDOW procedure, it’s highly recommended that the subscriber should call the DBMS_CDC_SUBSCRIBE.DROP_SUBSCRIPTION procedure to drop unneeded subscriptions.
· By default, auto scheduled DBMS_JOB executes DBMS_CDC_PUBLISH.PURGE every 24 hours. It’s recommended that the publisher who created the first change table should adjust this interval using the DBMS_JOB.CHANGE procedure based on the frequency of subscription (calls to extend_window procedure). The values for the JOB parameter for this procedure can be found by querying the USER_JOBS view for the job number that corresponds to the WHAT column containing the string 'SYS.DBMS_CDC_PUBLISH.PURGE ();'.
· It is possible that a subscriber could fail to call PURGE_WINDOW, with the result being that unneeded rows would not be deleted by the purge job. It’s highly recommended that the publisher should monitor the DBA_SUBSCRIPTIONS view at regular intervals to determine if this is happening. The publisher can use the DBMS_CDC_PUBLISH.DROP_SUBSCRIPTION procedure to drop active subscriptions where subscriber has failed to call PURGE_WINDOW.
· Publisher should consider performing purge operations at a finer granularity than the automatic purge operation performed by Change Data Capture as and when required. For e.g. after massive data load change capture, as a clean-up process etc. There are three purge operations available to the publisher:
§ – DBMS_CDC_PUBLISH.PURGE: Purges all change tables on the staging database. This is the same PURGE operation as is performed automatically by Change Data Capture.
§ – DBMS_CDC_PUBLISH.PURGE_CHANGE_SET: Purges all the change tables in a named change set.
§ – DBMS_CDC_PUBLISH.PURGE_CHANGE_TABLE: Purges a named changed table.
Following are the guidelines / best practices to consider while implementing CDC in Asynchronous Auto Log Archive mode.
1. Supplemental Logging
· The asynchronous modes of Change Data Capture work best with appropriate supplemental logging on the source database. Oracle recommends that the source database supplementally log all source table columns that are part of a primary key or function to uniquely identify a row.
· Create an unconditional log group for all source table columns that are captured by any asynchronous change table. This should be done before any change tables are created on a source table. If an unconditional log group is not created for all source table columns to be captured, then when an update DML operation occurs, some unchanged user column values in change tables will be null instead of reflecting the actual source table value.
· If you intend to capture all the column values in a row whenever a column in that row is updated, you can use ALL COLUMN option instead of listing each column one-by-one in the ALTER TABLE statement for supplemental logging. However, do not use this form of the ALTER TABLE statement if all columns are not needed. Logging all columns incurs more overhead than logging selected columns.
· Use force-logging option only if you want to capture the changes out of direct load insert or inserts with nologging. Use this force logging with caution as it may introduce performance overhead on the source database.
· To minimize performance impact in case you want capture even the changes out of direct load inserts or inserts with nologging, consider moving the source tables which are part of such operation to a dedicated tablespace and turn on force-logging at tablespace level instead of database level.
2. Defining Tables and Columns
· Performance, storage & network overhead of the Change Data Capture configuration is proportional to the amount of table and columns, which are part of the configurations.
· Ensure that you include only required and relevant tables and their columns while creating change tables.
· Capture only selective and relevant control columns on the change tables.
· By default, Change Data Capture process, records both (old & new) values in change table. If old values are not required, ensure to capture only new values (parameter CAPTURE_VALUES=>’NEW’) to minimize the performance overhead.
· Oracle recommends that change tables not be created in system tablespaces. This can be accomplished if the publisher's default tablespace is not the system tablespace or if the publisher specifies a tablespace in the options_string parameter.
· Do not specify any constrains on change table as it adds performance overhead during the time of capture. Perform all your data validations in target database.
3. Change Source, Change Sets & Subscriptions
· Keep the number of change sources per source database to minimum. Ideally create one change source in your staging database for each source database. Bear in mind that a change set cannot span across the change sources.
· Keep the number of change sets within a given change source to minimum. Bear in mind that a change set is a set of change data that is guaranteed to be transactionally consistent and subscription cannot span across change sets. If your subscriber needs set of changed data, which spans across various tables in one subscription, then you should group those tables in one change set.
· Keep the number of subscription equal or more than number of subscribers. Bear in mind that given subscription cannot be shared by 2 subscribers. If you have a subscriber who is interested in change set but he is interested in few change tables on daily basis and few change tables in weekly basis, then consider creating two subscriptions for the give subscriber. If you have two subscribers interested in same set of change tables, the create two different subscriptions for a given change set instead of duplicating the source tables in two change sets.
4. Publisher & Subscriber
· It’s highly recommended to create a dedicated publisher user on staging database for publication purpose.
· Oracle recommends that when creating the publisher account on a staging database, the DBA specify a default tablespace for the publisher; the publisher should use this tablespace for any change tables he or she creates.
· Generally, one publisher is sufficient enough to manage multiple change sources/ source databases on given staging database. However, if you have centralized staging databases, which caters multiple source databases and you don’t want to share the access to change source for security reasons, then it makes more sense to have dedicated publisher for each source database.
· If you have multiple source databases and centralized staging databases and only one publisher, then make sure you use options_string while creating change tables and specify appropriate tablespace name to group the change tables from various source databases to their respective tablespaces.
· It’s highly recommended that end application don’t share the subscribers. Each application interested in changed data must have their own dedicated subscribers.
· The publisher must grant the SELECT privilege before a subscriber can subscribe to the change table. The publisher must not grant any DML access (use of INSERT, UPDATE, or DELETE statements) to the subscribers on the change tables because a subscriber might inadvertently change the data in the change table, making it inconsistent with its source. Furthermore, the publisher should avoid creating change tables in schemas to which subscribers have DML access.
5. ARCHIVE_LAG_TARGET
With the AutoLog archive option, Change sets are populated as new archived redo log files arrive on the staging database. The degree of latency depends on the frequency of redo log file switches on the source database. The AutoLog archive option has a higher degree of latency than any other option. However, ARCHIVE_LAG_TARGET parameter can be used to efficiently control this latency by forcing log switches at regular interval. A 0 value disables the time-based thread advance feature; otherwise, the value represents the number of seconds. The typical, or recommended value is 1800 (30 minutes). Extremely low values can result in frequent log switches, which could degrade performance; such values can also make the archiver process too busy to archive the continuously generated logs.
6. Archiving Strategy for Change tables
· When finished using change data, a subscriber must call the DBMS_CDC_SUBSCRIBE.PURGE_WINDOW procedure. This indicates to Change Data Capture and the publisher that the change data is no longer needed by this subscriber and allows auto scheduled DBMS_JOB to execute DBMS_CDC_PUBLISH.PURGE procedure to remove data that subscribers are no longer using from the change tables. This ensures that the size of the change tables does not grow without limit.
· If the subscriber fails to call PURGE_WINDOW after using the changed data, then DBMS_CDC_PUBLISH.PURGE procedure cannot delete unneeded rows from the change table. So, it’s important to ensure that subscriber runs the DBMS_CDC_SUBSCRIBE.PURGE_WINDOW procedure whenever finished using change data.
· In addition to DBMS_CDC_SUBSCRIBE.PURGE_WINDOW procedure, it’s highly recommended that the subscriber should call the DBMS_CDC_SUBSCRIBE.DROP_SUBSCRIPTION procedure to drop unneeded subscriptions.
· By default, auto scheduled DBMS_JOB executes DBMS_CDC_PUBLISH.PURGE every 24 hours. It’s recommended that the publisher who created the first change table should adjust this interval using the DBMS_JOB.CHANGE procedure based on the frequency of subscription (calls to extend_window procedure). The values for the JOB parameter for this procedure can be found by querying the USER_JOBS view for the job number that corresponds to the WHAT column containing the string 'SYS.DBMS_CDC_PUBLISH.PURGE ();'.
· It is possible that a subscriber could fail to call PURGE_WINDOW, with the result being that unneeded rows would not be deleted by the purge job. It’s highly recommended that the publisher should monitor the DBA_SUBSCRIPTIONS view at regular intervals to determine if this is happening. The publisher can use the DBMS_CDC_PUBLISH.DROP_SUBSCRIPTION procedure to drop active subscriptions where subscriber has failed to call PURGE_WINDOW.
· Publisher should consider performing purge operations at a finer granularity than the automatic purge operation performed by Change Data Capture as and when required. For e.g. after massive data load change capture, as a clean-up process etc. There are three purge operations available to the publisher:
§ – DBMS_CDC_PUBLISH.PURGE: Purges all change tables on the staging database. This is the same PURGE operation as is performed automatically by Change Data Capture.
§ – DBMS_CDC_PUBLISH.PURGE_CHANGE_SET: Purges all the change tables in a named change set.
§ – DBMS_CDC_PUBLISH.PURGE_CHANGE_TABLE: Purges a named changed table.
Tuesday, January 29, 2008
Recovering 3 TB standby when the archive log is deleted/corrupted before applying
Yesterday, I had a challenging issue with one of my customer and its worth sharing.
They had 3 TB data warehouse system with physical standby in place. Its a critical banking reporting database and standby is mostly used in the evening to run the critical reports. They had a corruption in one of the archive log which was shipped to standby for applying. The same archive log on primary database was deleted without backup as the backups were taken on standby. Standby database was stuck for recovery requesting that particular archive log.
No other option but to take a fresh backup from primary and re-create the standby. But 3TB primary DB, 9 hours of backup time and around 14 hours of restore time. Not acceptable to customer.
Simple solution...
1. Check the SCN on standby
SQL>select current_scn from v$database;
CURRENT_SCN
-----------
47987632
2. Take incremental SCN backup on Primary
RMAN> BACKUP INCREMENTAL FROM SCN 47987632 DATABASE;
3. Recover the database
SQL>recover managed standby database cancel;
RMAN> RECOVER DATABASE NOREDO;
SQL> recover managed standby database nodelay disconnect;
Backup took 12 minutes and the size was 1.2 GB thats all. Recovery took 16 minutes.
They had 3 TB data warehouse system with physical standby in place. Its a critical banking reporting database and standby is mostly used in the evening to run the critical reports. They had a corruption in one of the archive log which was shipped to standby for applying. The same archive log on primary database was deleted without backup as the backups were taken on standby. Standby database was stuck for recovery requesting that particular archive log.
No other option but to take a fresh backup from primary and re-create the standby. But 3TB primary DB, 9 hours of backup time and around 14 hours of restore time. Not acceptable to customer.
Simple solution...
1. Check the SCN on standby
SQL>select current_scn from v$database;
CURRENT_SCN
-----------
47987632
2. Take incremental SCN backup on Primary
RMAN> BACKUP INCREMENTAL FROM SCN 47987632 DATABASE;
3. Recover the database
SQL>recover managed standby database cancel;
RMAN> RECOVER DATABASE NOREDO;
SQL> recover managed standby database nodelay disconnect;
Backup took 12 minutes and the size was 1.2 GB thats all. Recovery took 16 minutes.
Sunday, January 20, 2008
Exploiting 11g ASM features against 10g database
Exploiting 11g ASM features against 10g database
We all know that new Oracle 11g release has much more to offer. Its going to take some time for every one to adopt 11g in production. Lot of testing to be done. Management has to be convinced about its stability based on success stories of others customers. But somewhere, someone has to start.
For those who are using ASM as their storage can upgrade their ASM instance alone to 11g immediately and start exploiting 11g ASM features against their 10g database.
Following are the simple steps to follow and you are all set to go with ASM 11g features for your 1og database.
1. Install Oracle 11g RDBMS software for your new ASM 11g HOME.
2. Shutdown your 10g database & ASM instance
3. Reconfigure Oracle Cluster Synchronization Services to run from new 11g home
Set PATH and other environment variables(ORACLE_HOME, ORACLE_SID) to new ORACLE 11g executables.
Login as root and run following command.
[root@arul oracle]# localconfig reset
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Configuration for local CSS has been initialized
Stale CSS daemon is running... killing it now
Cleaning up Network socket directories
Setting up Network socket directories
Adding to inittab
Startup will be queued to init within 30 seconds.
Checking the status of new Oracle init process...
Expecting the CRS daemons to be up within 600 seconds.
Cluster Synchronization Services is active on these nodes.
arul
Cluster Synchronization Services is active on all the nodes.
Oracle CSS service is installed and running under init(1M)
[root@arul oracle]#
4. Copy password file & S/Pfile to new 11g ORACLE_HOME/dbs folder
$ cp spfile+ASM.ora /u02/app/oracle/product/11g/db_1/dbs
$ cp orapw+ASM /u02/app/oracle/product/11g/db_1/dbs
5. Remove the obsolete initialization parameters
Parameters like background/user/core dump dest are obsolete in 11g, so remove those parameters.
Change the remote login passwordfile to exclusive.
6. Login as sysdba and start the ASM instance from 11g home and grant sysasm privilege.
$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jan 21 09:11:50 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ASM instance started
Total System Global Area 284565504 bytes
Fixed Size 1299428 bytes
Variable Size 258100252 bytes
ASM Cache 25165824 bytes
ASM diskgroups mounted
SQL> grant sysasm to sys;
Grant succeeded.
SQL> exit
7. Start 10g RDBMS instance.
set PATH and other environment variables(ORACLE_HOME, ORACLE_SID) to ORACLE 10g RDBMS instance.
$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 21 09:13:46 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 184549376 bytes
Fixed Size 1218412 bytes
Variable Size 71305364 bytes
Database Buffers 109051904 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL>
8. All set to go.
Now you can use 11g ASM features for your 10g RDBMS instance.
ASMCMD has the few very handy new commands: cp, lsdsk, pwd...
Few examples..
ASMCMD [+data/mars] > cp spfilemars.ora +data/mars/test/spfilemars.ora
source +data/mars/spfilemars.ora
target +data/mars/test/spfilemars.ora
copying file(s)...
file, +DATA/mars/test/spfilemars.ora, copy committed.
ASMCMD [+data/mars] > cd test
ASMCMD [+data/mars/test] > ls
spfilemars.ora
$ asmcmd -v
asmcmd version 11.1.0.6.0
ASMCMD [+data/mars/test] > pwd
+data/mars/test
ASMCMD [+data/mars/test] > lsdsk
Path
/dev/raw/raw1
/dev/raw/raw2
ASMCMD [+data/mars/test] > lsct
DB_Name Status Software_Version Compatible_version Instance_Name Disk_Group
mars CONNECTED 10.2.0.1.0 10.2.0.1.0 mars DATA
mars CONNECTED 10.2.0.1.0 10.2.0.1.0 mars FRA
We all know that new Oracle 11g release has much more to offer. Its going to take some time for every one to adopt 11g in production. Lot of testing to be done. Management has to be convinced about its stability based on success stories of others customers. But somewhere, someone has to start.
For those who are using ASM as their storage can upgrade their ASM instance alone to 11g immediately and start exploiting 11g ASM features against their 10g database.
Following are the simple steps to follow and you are all set to go with ASM 11g features for your 1og database.
1. Install Oracle 11g RDBMS software for your new ASM 11g HOME.
2. Shutdown your 10g database & ASM instance
3. Reconfigure Oracle Cluster Synchronization Services to run from new 11g home
Set PATH and other environment variables(ORACLE_HOME, ORACLE_SID) to new ORACLE 11g executables.
Login as root and run following command.
[root@arul oracle]# localconfig reset
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Configuration for local CSS has been initialized
Stale CSS daemon is running... killing it now
Cleaning up Network socket directories
Setting up Network socket directories
Adding to inittab
Startup will be queued to init within 30 seconds.
Checking the status of new Oracle init process...
Expecting the CRS daemons to be up within 600 seconds.
Cluster Synchronization Services is active on these nodes.
arul
Cluster Synchronization Services is active on all the nodes.
Oracle CSS service is installed and running under init(1M)
[root@arul oracle]#
4. Copy password file & S/Pfile to new 11g ORACLE_HOME/dbs folder
$ cp spfile+ASM.ora /u02/app/oracle/product/11g/db_1/dbs
$ cp orapw+ASM /u02/app/oracle/product/11g/db_1/dbs
5. Remove the obsolete initialization parameters
Parameters like background/user/core dump dest are obsolete in 11g, so remove those parameters.
Change the remote login passwordfile to exclusive.
6. Login as sysdba and start the ASM instance from 11g home and grant sysasm privilege.
$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jan 21 09:11:50 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ASM instance started
Total System Global Area 284565504 bytes
Fixed Size 1299428 bytes
Variable Size 258100252 bytes
ASM Cache 25165824 bytes
ASM diskgroups mounted
SQL> grant sysasm to sys;
Grant succeeded.
SQL> exit
7. Start 10g RDBMS instance.
set PATH and other environment variables(ORACLE_HOME, ORACLE_SID) to ORACLE 10g RDBMS instance.
$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 21 09:13:46 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 184549376 bytes
Fixed Size 1218412 bytes
Variable Size 71305364 bytes
Database Buffers 109051904 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL>
8. All set to go.
Now you can use 11g ASM features for your 10g RDBMS instance.
ASMCMD has the few very handy new commands: cp, lsdsk, pwd...
Few examples..
ASMCMD [+data/mars] > cp spfilemars.ora +data/mars/test/spfilemars.ora
source +data/mars/spfilemars.ora
target +data/mars/test/spfilemars.ora
copying file(s)...
file, +DATA/mars/test/spfilemars.ora, copy committed.
ASMCMD [+data/mars] > cd test
ASMCMD [+data/mars/test] > ls
spfilemars.ora
$ asmcmd -v
asmcmd version 11.1.0.6.0
ASMCMD [+data/mars/test] > pwd
+data/mars/test
ASMCMD [+data/mars/test] > lsdsk
Path
/dev/raw/raw1
/dev/raw/raw2
ASMCMD [+data/mars/test] > lsct
DB_Name Status Software_Version Compatible_version Instance_Name Disk_Group
mars CONNECTED 10.2.0.1.0 10.2.0.1.0 mars DATA
mars CONNECTED 10.2.0.1.0 10.2.0.1.0 mars FRA
Wednesday, October 31, 2007
Oracle11g Active Data Guard
A physical standby database can be open for read-only access while Redo Apply is active if a license for the Oracle Active Data Guard option has been purchased. This capability is known as Real-time Query.
Let test this feature...
Oracle DATAGUARD Physical standby configuration steps remains same as 10g. So I have a physical standby database already running.
Make sure you have standby redo logs configured..
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
SQL> select count(*) from v$standby_log;
COUNT(*)
----------
3
Open the Physical standby database and start the recovery
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Database altered.
Now Lets perfrom some transaction on primary and redo will be shipped to standby and will be applied and we will be able to query them online when the apply is in progress.
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY
SQL> select count(*) from test;
COUNT(*)
----------
68289
SQL> truncate table test;
Table truncated.
SQL> select count(*) from test;
COUNT(*)
----------
0
Now lets check on standby
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
SQL> select count(*) from test;
COUNT(*)
----------
0
Let test this feature...
Oracle DATAGUARD Physical standby configuration steps remains same as 10g. So I have a physical standby database already running.
Make sure you have standby redo logs configured..
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
SQL> select count(*) from v$standby_log;
COUNT(*)
----------
3
Open the Physical standby database and start the recovery
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Database altered.
Now Lets perfrom some transaction on primary and redo will be shipped to standby and will be applied and we will be able to query them online when the apply is in progress.
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY
SQL> select count(*) from test;
COUNT(*)
----------
68289
SQL> truncate table test;
Table truncated.
SQL> select count(*) from test;
COUNT(*)
----------
0
Now lets check on standby
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
SQL> select count(*) from test;
COUNT(*)
----------
0
Subscribe to:
Posts (Atom)
