In this test, replication is made from Oracle Database version 11.1.0.7 to 11.1.0.6 database on a virtual machine. This is not a performance test but only a functional test of the streams basic abilities. This test is made on table replication basis maybe I can publish the schema replication based test in another article.
Some preparation needed first and i assume both the databases are in archivelog mode. Actually documentation says only the source database is enough for archiving redologs. That is reasonable for capturing the changes and not to miss one. But my databases are both in archivelog mode so I cover this necessity.
--check if there are unsupported column types.
select * from DBA_STREAMS_UNSUPPORTED;
--create a tablespace on the source database for test purposes
create tablespace TS_STREAMS_SRC DATAFILE 'D:\oracledb11g\oradata\orcl\TS_STREAMS_SRC_01.dbf'
SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 100M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
--create a tablespace on the destination database for test purposes
create tablespace TS_STREAMS_DEST DATAFILE 'D:\oracledb11g\oradata\orcl\TS_STREAMS_DEST_01.dbf'
SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 100M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
--creating the streams admin user on both of the databases.
create user U_STREAMS_ADM identified by password
DEFAULT TABLESPACE TS_STREAMS_SRC
QUOTA UNLIMITED ON TS_STREAMS_SRC;
--streams user should have dba privilege
grant connect,resource,dba to U_STREAMS_ADM;
exec DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE('U_STREAMS_ADM',TRUE);
--create database links on the databases to access both from SOURCE to DESTINATION and from DESTINATION to SOURCE
--here ORCL_SOURCE is the tns alias for the SOURCE database and ORCL_DESTINATION is the tns alias for the DESTINATION database
create database link ORCL.SOURCE.LOCAL connect to U_STREAMS_ADM_SRC identified by password using 'ORCL_SOURCE';
create database link ORCL.DESINATION.LOCAL connect to U_STREAMS_ADM_SRC identified by password using 'ORCL_DESTINATION';
--a second destination should be defined on the source database to send the redo logs to the destination database
alter system set LOG_ARCHIVE_DEST_2='SERVICE=ORCL ASYNC NOREGISTER VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL';
-- creating a table replication with MAINTAIN_TABLES
-- with this configuration CAPTURE and PROPAGATION processes are on the source database
-- APPLY process is on the DESTINATION database
DECLARE
tables DBMS_UTILITY.UNCL_ARRAY;
BEGIN
tables(1) := 'scott.dept';
DBMS_STREAMS_ADM.MAINTAIN_TABLES(
table_names => tables,
source_directory_object => NULL,
destination_directory_object => NULL,
source_database => 'ORCL.SOURCE.LOCAL',
destination_database => 'ORCL.DESTINATION.LOCAL',
capture_name => 'CAPTURE_TABLE_GRP1',
capture_queue_name => 'QUEUE_CAPT_TABLE_GRP1',
capture_queue_user => 'U_STREAMS_ADM',
propagation_name => 'PROPAGATE_TABLE_GRP1',
apply_name => 'APPLY_TABLE_GRP1',
apply_queue_name => 'QUEUE_APPLY_TABLE_GRP1',
apply_queue_user => 'U_STREAMS_ADM',
perform_actions => TRUE,
script_directory_object => NULL,
script_name => NULL,
bi_directional => FALSE,
include_ddl => TRUE,
instantiation => DBMS_STREAMS_ADM.INSTANTIATION_TABLE_NETWORK);
END;
The procedure worked smoothly while I was waiting for it to be end with an exception. But if there is a problem with the instantiation of the replication then the following set of commands helps you to clear the inadequate configuration and then you can run the MAINTAIN_TABLES again.
SELECT * FROM DBA_RECOVERABLE_SCRIPT_ERRORS;
execute DBMS_STREAMS_ADM.RECOVER_OPERATION('A6B753822B9C4C30A5CD87B9571ACF03','PURGE');
exec DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();
--The insert tests just worked fine as follows.
insert into SCOTT.DEPT values (60,'IT-DEVELOPMENT','IST');
insert into SCOTT.DEPT values (70,'IT-STORAGE','IST');
insert into SCOTT.DEPT values (80,'IT-BACKUP','IST');
insert into SCOTT.DEPT values (90,'IT-WINDOWS','IST');
insert into SCOTT.DEPT values (95,'IT-UNIX','IST');
insert into SCOTT.DEPT values (96,'IT-SECURITY','IST');
commit;
--Findout the configuration and status of the processes
select * from dba_capture;
select * from dba_propagation;
select * from dba_apply;
select * from v$streams_capture;
Lets see if the configuration is flexible enough to add a new table rule to the existing set easily. I followed the documentation with the following commands in order.
--on source database
exec DBMS_CAPTURE_ADM.STOP_CAPTURE('CAPTURE_TABLE_GRP1');
exec DBMS_PROPAGATION_ADM.STOP_PROPAGATION('PROPAGATE_TABLE_GRP1');
--on the destination database
exec DBMS_APPLY_ADM.STOP_APPLY('APPLY_TABLE_GRP1');
--add new rule to the CAPTURE process
exec DBMS_STREAMS_ADM.ADD_TABLE_RULES(
TABLE_NAME =>'SCOTT.EMP',
STREAMS_TYPE => 'CAPTURE',
STREAMS_NAME => 'CAPTURE_TABLE_GRP1',
SOURCE_DATABASE => 'ORCL.SOURCE.LOCAL',
QUEUE_NAME => 'QUEUE_CAPT_TABLE_GRP1',
INCLUDE_DML => TRUE,
INCLUDE_DDL => TRUE,
--DDL_RULE_NAME => NULL,
--DML_RULE_NAME => NULL,
INCLUSION_RULE => TRUE,
AND_CONDITION => NULL);
--add new rule to the PROPAGATION process
exec DBMS_STREAMS_ADM.ADD_TABLE_PROPAGATION_RULES(
TABLE_NAME => 'SCOTT.EMP',
STREAMS_NAME => '', --if propagation exists on source_queue_name then uses the existing propagation
SOURCE_QUEUE_NAME => 'U_STREAMS_ADM.QUEUE_CAPT_TABLE_GRP1',
DESTINATION_QUEUE_NAME => 'U_STREAMS_ADM.QUEUE_APPLY_TABLE_GRP1@ORCL.DESTINATION.LOCAL',
INCLUDE_DML => TRUE,
INCLUDE_DDL => TRUE,
SOURCE_DATABASE => 'ORCL.SOURCE.LOCAL',
INCLUSION_RULE => TRUE,
AND_CONDITION => NULL,
QUEUE_TO_QUEUE => TRUE --TRUE=queue_to_queue FALSE=queue_to_dblink
);
--now the last thing is to add a new rule to the APPLY process
exec DBMS_STREAMS_ADM.ADD_TABLE_RULES(
TABLE_NAME =>'SCOTT.EMP',
STREAMS_TYPE => 'APPLY',
STREAMS_NAME => 'APPLY_TABLE_GRP1',
SOURCE_DATABASE => 'ORCL.SOURCE.LOCAL',
QUEUE_NAME => 'QUEUE_APPLY_TABLE_GRP1',
INCLUDE_DML => TRUE,
INCLUDE_DDL => TRUE,
--DDL_RULE_NAME => NULL,
--DML_RULE_NAME => NULL,
INCLUSION_RULE => TRUE,
AND_CONDITION => NULL);
We can now check if the new rules are added and the conditions of the rules with the following views. We cannot see and new process configuration as i added the rules to the existing APPLY, PROPAGATION and CAPTURE processes.
DBA_RULES
DBA_RULE_SETS
DBA_STREAMS_RULES
DBA_RULE_SET_RULES
If you did something wrong wile adding the rules you can easily delete the rules by running the following procedures which are under the DBMS_RULE_ADM. Be sure what are you deleting by selecting DBA_STREAMS_RULES. You dont want to delete a rule associated with a working process.
--If you want to delete a rule you should first remove it
DBMS_RULE_ADM.REMOVE_RULE
DBMS_RULE_ADM.DELETE_RULE
I added the rules and now I should instantiate the table/tables manually. MAINTAIN_TABLES procedure makes this instantiation automatically for you but if you want to add the rules explicitly then you need to instantiate them.
--set the instantiation SCN of the SOURCE table/tables
DECLARE
iscn NUMBER; --Variable to hold instantiation SCN value
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
source_object_name => 'scott.emp',
source_database_name => 'ORCL.SOURCE.LOCAL',
instantiation_scn => iscn);
END;
--Create the table on the DESTINATION database.
create table scott.emp as select * from scott.emp@ORCL.SOURCE.LOCAL;
--The last thing is not to forget the SUPPLEMENTAL LOGGING for the replicated table to capture and apply the changes.
ALTER TABLE scott.emp ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
--After checking all the configuration again now I can start the processes i have stopped.
-- on source database.
exec DBMS_CAPTURE_ADM.START_CAPTURE('CAPTURE_TABLE_GRP1');
exec DBMS_PROPAGATION_ADM.START_PROPAGATION('PROPAGATE_TABLE_GRP1');
--On the destination database.
exec DBMS_APPLY_ADM.START_APPLY('APPLY_TABLE_GRP1');
It seems everything worked fine as the Oracle documentation stated. Adding rules to working processes of the streams are easy accept you have to manually instantiate the tables via create table .. as .. or export/import or expdp/impdp if you are adding the rules manually. Now i want to go furher and alter a replication rule of an existing configuration of the apply process to capture only a subset of the changes.
Rules can be seen by selecting DBA_RULES, DBA_RULESETS, DBA_RULE_SET_RULES or DBA_STREAMS_RULES which i prefer to use. One should be careful which rule he/she is going to alter. There are DML and DDL rules for every process. And the rule that will be altered should be related with the correct CAPTURE PROCESS (since there can be more than one capture process).
In my case the rule that i will going to alter is BONUS85 as follows. Stopping the processes before any change in the configuration is a good idea. Without stopping and starting the processes the new configuration cannot be applied to the existing streams processes.
--On source database
exec DBMS_CAPTURE_ADM.STOP_CAPTURE('CAPTURE_TABLE_GRP1');
exec DBMS_PROPAGATION_ADM.STOP_PROPAGATION('PROPAGATE_TABLE_GRP1');
--On the destination database
exec DBMS_APPLY_ADM.STOP_APPLY('APPLY_TABLE_GRP1');
--Altering a rule capturing a subset of the data in the table.
execute dbms_rule_adm.alter_rule('BONUS85',':dml.get_object_owner()=''SCOTT'' AND :dml.get_object_name()=''BONUS''
AND :dml.is_null_tag()=''Y'' AND :dml.get_source_database_name() = ''ORCL.DIGITURK.LOCAL''
AND (:dml.get_value(''NEW'',''ENAME'').AccessVarchar2()=''ERGEM'')');
--On source database
exec DBMS_CAPTURE_ADM.START_CAPTURE('CAPTURE_TABLE_GRP1');
exec DBMS_PROPAGATION_ADM.START_PROPAGATION('PROPAGATE_TABLE_GRP1');
--On the destination database
exec DBMS_APPLY_ADM.START_APPLY('APPLY_TABLE_GRP1');
--Related views for streams monitoring
select * from v$streams_apply_coordinator
select * from v$streams_apply_reader
select * from v$streams_apply_server
select * from v$streams_capture
select * from v$streams_pool_advice
select * from v$streams_transaction
select * from v$streams_monitor
As a conclusion Oracle Streams is easy to configure and use. It seems flexible to change the configuration easily and altering the existing rules to gather the subset of the changes. I did not test the performance but functional tests seems OK.
Of course there is a lot of options you can use; Downstream Capture Processes, Sync Captures, Parallel Capture/Apply Processes and so on. Oracle Documentation has sufficient information about the Streams.
Related:
Oracle Streams Replication Administrators Guide
Oracle Streams Concepts and Administration
PSOUG Streams Demo
Wednesday, October 14, 2009
Friday, May 15, 2009
SQL Server detected a logical consistency-based I/O error
After migrating the SQL Server 2005 SP2 32-bit database to SP3 64-bit environment some queries started to complain about the following error in the SQL Server Error Logs.
Message:
SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x0; actual signature: 0x5be5d620). It occurred during a read of page (1:718204) in database ID 7 at offset 0x0000015eaf8000 in file 'E:\MSSQL\DATA\SMPPGatewayBEP.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
It is obvious that there happened an IO inconsistency. Maybe while the backup or the restore operation. I decided to execute DBCC CHECKDB('SMPPGatewayBEP') But the following output generated by the server.
Msg 8967, Level 16, State 216, Line 1
An internal error occurred in DBCC that prevented further processing. Contact Customer Support Services.
DBCC results for 'SMPPGatewayBEP'.
Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'SMPPGatewayBEP'.
After a little search on msdn i decided to try to set the database into the single user mode by running:
alter database SMPPGatewayBEP SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CHECKDB('SMPPGatewayBEP', REPAIR_ALLOW_DATA_LOSS)
Now it started to repair the data pages by reading the log pages. There is thousands of lines of this output so i put the head and tail part of the output.
DBCC results for 'SMPPGatewayBEP'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:352523) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
The error has been repaired.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:352524) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
The error has been repaired.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:352525) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
The error has been repaired.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:352526) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
The error has been repaired.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:352527) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
The error has been repaired.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:719092) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
The error has been repaired.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:719093) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
The error has been repaired.
...
..
.
Could not repair this error.
Could not repair this error.
Could not repair this error.
Could not repair this error.
The error has been repaired.
The error has been repaired.
The error has been repaired.
Could not repair this error.
Could not repair this error.
Could not repair this error.
Could not repair this error.
Could not repair this error.
Could not repair this error.
The error has been repaired.
CHECKDB found 0 allocation errors and 1857 consistency errors not associated with any single object.
CHECKDB fixed 0 allocation errors and 973 consistency errors not associated with any single object.
DBCC results for 'sys.sysrowsetcolumns'.
There are 716 rows in 8 pages for object "sys.sysrowsetcolumns".
DBCC results for 'sys.sysrowsets'.
There are 101 rows in 1 pages for object "sys.sysrowsets".
DBCC results for 'sysallocunits'.
There are 115 rows in 2 pages for object "sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 2 rows in 1 pages for object "sys.sysfiles1".
DBCC results for 'sys.syshobtcolumns'.
There are 716 rows in 9 pages for object "sys.syshobtcolumns".
DBCC results for 'sys.syshobts'.
There are 101 rows in 1 pages for object "sys.syshobts".
DBCC results for 'sys.sysftinds'.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for 'sys.sysserefs'.
There are 115 rows in 1 pages for object "sys.sysserefs".
DBCC results for 'sys.sysowners'.
There are 15 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysprivs'.
There are 135 rows in 1 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 134 rows in 4 pages for object "sys.sysschobjs".
DBCC results for 'sys.syscolpars'.
There are 629 rows in 13 pages for object "sys.syscolpars".
DBCC results for 'sys.sysnsobjs'.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.syscerts'.
There are 0 rows in 0 pages for object "sys.syscerts".
...
..
.
Repair: The page (1:718716) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:718717) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:718718) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:718719) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:719048) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:719049) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:719050) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:719051) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:719052) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:719053) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:719054) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:719055) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
...
..
.
The error has been repaired.
The error has been repaired.
The error has been repaired.
There are 7270488 rows in 35601 pages for object "WSLog".
CHECKDB found 0 allocation errors and 8 consistency errors in table 'WSLog' (object ID 398624463).
CHECKDB fixed 0 allocation errors and 8 consistency errors in table 'WSLog' (object ID 398624463).
DBCC results for 'SettingsInbound'.
There are 1 rows in 1 pages for object "SettingsInbound".
DBCC results for 'SettingsDelivered'.
There are 1 rows in 1 pages for object "SettingsDelivered".
DBCC results for 'sysdiagrams'.
There are 0 rows in 0 pages for object "sysdiagrams".
DBCC results for 'ThreadMonitoring'.
There are 0 rows in 0 pages for object "ThreadMonitoring".
DBCC results for 'Settings'.
There are 1 rows in 1 pages for object "Settings".
DBCC results for 'SMPPIncoming'.
There are 95 rows in 4 pages for object "SMPPIncoming".
DBCC results for 'BlankMessages'.
There are 0 rows in 0 pages for object "BlankMessages".
DBCC results for 'TestTrace'.
There are 2134061 rows in 46701 pages for object "TestTrace".
DBCC results for 'ServiceParams'.
There are 18 rows in 1 pages for object "ServiceParams".
DBCC results for 'sys.queue_messages_1977058079'.
There are 0 rows in 0 pages for object "sys.queue_messages_1977058079".
DBCC results for 'sys.queue_messages_2009058193'.
There are 0 rows in 0 pages for object "sys.queue_messages_2009058193".
DBCC results for 'sys.queue_messages_2041058307'.
There are 0 rows in 0 pages for object "sys.queue_messages_2041058307".
CHECKDB found 0 allocation errors and 6622 consistency errors in database 'SMPPGatewayBEP'.
CHECKDB fixed 0 allocation errors and 5738 consistency errors in database 'SMPPGatewayBEP'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (SMPPGatewayBEP, repair_allow_data_loss).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
After one hour finally the summary shows that lots of the page errors are repaired. Maybe I should run the same DBCC CHECKDB second time, to be sure if the remaining errors would be gone but we didnt have to wait for that. After setting the database into multiuser mode again and backing it up users were able to run their reports again.
alter database SMPPGatewayBEP SET MULTI_USER;
Message:
SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x0; actual signature: 0x5be5d620). It occurred during a read of page (1:718204) in database ID 7 at offset 0x0000015eaf8000 in file 'E:\MSSQL\DATA\SMPPGatewayBEP.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
It is obvious that there happened an IO inconsistency. Maybe while the backup or the restore operation. I decided to execute DBCC CHECKDB('SMPPGatewayBEP') But the following output generated by the server.
Msg 8967, Level 16, State 216, Line 1
An internal error occurred in DBCC that prevented further processing. Contact Customer Support Services.
DBCC results for 'SMPPGatewayBEP'.
Msg 8921, Level 16, State 1, Line 1
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'SMPPGatewayBEP'.
After a little search on msdn i decided to try to set the database into the single user mode by running:
alter database SMPPGatewayBEP SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CHECKDB('SMPPGatewayBEP', REPAIR_ALLOW_DATA_LOSS)
Now it started to repair the data pages by reading the log pages. There is thousands of lines of this output so i put the head and tail part of the output.
DBCC results for 'SMPPGatewayBEP'.
Service Broker Msg 9675, State 1: Message Types analyzed: 14.
Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.
Service Broker Msg 9667, State 1: Services analyzed: 3.
Service Broker Msg 9668, State 1: Service Queues analyzed: 3.
Service Broker Msg 9669, State 1: Conversation Endpoints analyzed: 0.
Service Broker Msg 9674, State 1: Conversation Groups analyzed: 0.
Service Broker Msg 9670, State 1: Remote Service Bindings analyzed: 0.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:352523) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
The error has been repaired.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:352524) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
The error has been repaired.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:352525) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
The error has been repaired.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:352526) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
The error has been repaired.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:352527) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
The error has been repaired.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:719092) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
The error has been repaired.
Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 0 (type Unknown), page ID (1:719093) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
The error has been repaired.
...
..
.
Could not repair this error.
Could not repair this error.
Could not repair this error.
Could not repair this error.
The error has been repaired.
The error has been repaired.
The error has been repaired.
Could not repair this error.
Could not repair this error.
Could not repair this error.
Could not repair this error.
Could not repair this error.
Could not repair this error.
The error has been repaired.
CHECKDB found 0 allocation errors and 1857 consistency errors not associated with any single object.
CHECKDB fixed 0 allocation errors and 973 consistency errors not associated with any single object.
DBCC results for 'sys.sysrowsetcolumns'.
There are 716 rows in 8 pages for object "sys.sysrowsetcolumns".
DBCC results for 'sys.sysrowsets'.
There are 101 rows in 1 pages for object "sys.sysrowsets".
DBCC results for 'sysallocunits'.
There are 115 rows in 2 pages for object "sysallocunits".
DBCC results for 'sys.sysfiles1'.
There are 2 rows in 1 pages for object "sys.sysfiles1".
DBCC results for 'sys.syshobtcolumns'.
There are 716 rows in 9 pages for object "sys.syshobtcolumns".
DBCC results for 'sys.syshobts'.
There are 101 rows in 1 pages for object "sys.syshobts".
DBCC results for 'sys.sysftinds'.
There are 0 rows in 0 pages for object "sys.sysftinds".
DBCC results for 'sys.sysserefs'.
There are 115 rows in 1 pages for object "sys.sysserefs".
DBCC results for 'sys.sysowners'.
There are 15 rows in 1 pages for object "sys.sysowners".
DBCC results for 'sys.sysprivs'.
There are 135 rows in 1 pages for object "sys.sysprivs".
DBCC results for 'sys.sysschobjs'.
There are 134 rows in 4 pages for object "sys.sysschobjs".
DBCC results for 'sys.syscolpars'.
There are 629 rows in 13 pages for object "sys.syscolpars".
DBCC results for 'sys.sysnsobjs'.
There are 1 rows in 1 pages for object "sys.sysnsobjs".
DBCC results for 'sys.syscerts'.
There are 0 rows in 0 pages for object "sys.syscerts".
...
..
.
Repair: The page (1:718716) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:718717) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:718718) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:718719) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:719048) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:719049) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:719050) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:719051) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:719052) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:719053) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:719054) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
Repair: The page (1:719055) has been deallocated from object ID 174623665, index ID 7, partition ID 72057594160414720, alloc unit ID 72057594168016896 (type In-row data).
...
..
.
The error has been repaired.
The error has been repaired.
The error has been repaired.
There are 7270488 rows in 35601 pages for object "WSLog".
CHECKDB found 0 allocation errors and 8 consistency errors in table 'WSLog' (object ID 398624463).
CHECKDB fixed 0 allocation errors and 8 consistency errors in table 'WSLog' (object ID 398624463).
DBCC results for 'SettingsInbound'.
There are 1 rows in 1 pages for object "SettingsInbound".
DBCC results for 'SettingsDelivered'.
There are 1 rows in 1 pages for object "SettingsDelivered".
DBCC results for 'sysdiagrams'.
There are 0 rows in 0 pages for object "sysdiagrams".
DBCC results for 'ThreadMonitoring'.
There are 0 rows in 0 pages for object "ThreadMonitoring".
DBCC results for 'Settings'.
There are 1 rows in 1 pages for object "Settings".
DBCC results for 'SMPPIncoming'.
There are 95 rows in 4 pages for object "SMPPIncoming".
DBCC results for 'BlankMessages'.
There are 0 rows in 0 pages for object "BlankMessages".
DBCC results for 'TestTrace'.
There are 2134061 rows in 46701 pages for object "TestTrace".
DBCC results for 'ServiceParams'.
There are 18 rows in 1 pages for object "ServiceParams".
DBCC results for 'sys.queue_messages_1977058079'.
There are 0 rows in 0 pages for object "sys.queue_messages_1977058079".
DBCC results for 'sys.queue_messages_2009058193'.
There are 0 rows in 0 pages for object "sys.queue_messages_2009058193".
DBCC results for 'sys.queue_messages_2041058307'.
There are 0 rows in 0 pages for object "sys.queue_messages_2041058307".
CHECKDB found 0 allocation errors and 6622 consistency errors in database 'SMPPGatewayBEP'.
CHECKDB fixed 0 allocation errors and 5738 consistency errors in database 'SMPPGatewayBEP'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (SMPPGatewayBEP, repair_allow_data_loss).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
After one hour finally the summary shows that lots of the page errors are repaired. Maybe I should run the same DBCC CHECKDB second time, to be sure if the remaining errors would be gone but we didnt have to wait for that. After setting the database into multiuser mode again and backing it up users were able to run their reports again.
alter database SMPPGatewayBEP SET MULTI_USER;
Monday, February 23, 2009
Problem with adding a new node to RAC after ocr and voting disk change
After changing the ocr and voting disks with different raw devices, that used with installation, later on i faced a problem while adding a new node to my existing 2node cluster installation.
After the first step of adding a new node to a cluster (cloning my CRS_HOME (cluster home of oracle RAC) and ran all scripts), the last step in the documentation was running root.sh in the new node's CRS_HOME. But the unexpected error was;
I am pretty sure with the raw device definitions are OK in the new node. I decided to examine the "root.sh" to see what it is running in the background. "root.sh" was consisting of two other scripts "rootinstall" and "rootconfig".
I found the following part of the script in the very beginning of the rootconfig script.
Somehow (my second somehow in the 10gR2 RAC environment) crs records was showing the old ocr and voting disks. The new disks i have replaced in my existing RAC environment before was totally different. I decided to modify the rootconfig file and replace the old defined ocr and voting disks with the new ones as follows. An rerun the rootconfig file. I dont know if this action is recommended by oracle but i think this is the only change that i have in my hand.
My new node is part of my existing cluster now. Now time to add the ORACLE_HOME (database installation) and the instances with the services.
Resources:
Oracle® Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide 10g Release 2 (10.2)
After the first step of adding a new node to a cluster (cloning my CRS_HOME (cluster home of oracle RAC) and ran all scripts), the last step in the documentation was running root.sh in the new node's CRS_HOME. But the unexpected error was;
root@radbank04 # ./root.sh
"/dev/rdsk/c6t600601607D731F006596A6CD2CE9DD11d0s0" does not exist. Create it before proceeding.
Make sure that this file is shared across cluster nodes.
1
I am pretty sure with the raw device definitions are OK in the new node. I decided to examine the "root.sh" to see what it is running in the background. "root.sh" was consisting of two other scripts "rootinstall" and "rootconfig".
root@radbank04 # more root.sh
#!/bin/sh
/oracle/product/crs10g/install/rootinstall
/oracle/product/crs10g/install/rootconfig
I found the following part of the script in the very beginning of the rootconfig script.
SILENT=false
ORA_CRS_HOME=/oracle/product/crs10g
CRS_ORACLE_OWNER=oracle
CRS_DBA_GROUP=oinstall
CRS_VNDR_CLUSTER=false
CRS_OCR_LOCATIONS=/dev/rdsk/c6t600601607D731F00BAE716BB2CE9DD11d0s0,/dev/rdsk/c6t600601607D731F006496A6CD2CE9DD11d0s0
CRS_CLUSTER_NAME=crs
CRS_HOST_NAME_LIST=radbank02,1,radbank03,2
CRS_NODE_NAME_LIST=radbank02,1,radbank03,2
CRS_PRIVATE_NAME_LIST=radbank02-priv1,1,radbank03-priv1,2
CRS_LANGUAGE_ID='AMERICAN_AMERICA.WE8ISO8859P1'
CRS_VOTING_DISKS=/dev/rdsk/c6t600601607D731F006596A6CD2CE9DD11d0s0
CRS_NODELIST=radbank02,radbank03
CRS_NODEVIPS='radbank02/radbank02-vip/255.255.255.0/bge0,radbank03/radbank03-vip/255.255.255.0/bge0'
Somehow (my second somehow in the 10gR2 RAC environment) crs records was showing the old ocr and voting disks. The new disks i have replaced in my existing RAC environment before was totally different. I decided to modify the rootconfig file and replace the old defined ocr and voting disks with the new ones as follows. An rerun the rootconfig file. I dont know if this action is recommended by oracle but i think this is the only change that i have in my hand.
SILENT=false
ORA_CRS_HOME=/oracle/product/crs10g
CRS_ORACLE_OWNER=oracle
CRS_DBA_GROUP=oinstall
CRS_VNDR_CLUSTER=false
#CRS_OCR_LOCATIONS=/dev/rdsk/c6t600601607D731F00BAE716BB2CE9DD11d0s0,/dev/rdsk/c6t600601607D731F006496A6CD2CE9DD11d0s0
CRS_OCR_LOCATIONS=/dev/rdsk/c6t600601607D731F00AE6F711488FEDD11d0s0,/dev/rdsk/c6t600601607D731F00AF6F711488FEDD11d0s0
CRS_CLUSTER_NAME=crs
CRS_HOST_NAME_LIST=radbank02,1,radbank03,2
CRS_NODE_NAME_LIST=radbank02,1,radbank03,2
CRS_PRIVATE_NAME_LIST=radbank02-priv1,1,radbank03-priv1,2
CRS_LANGUAGE_ID='AMERICAN_AMERICA.WE8ISO8859P1'
#CRS_VOTING_DISKS=/dev/rdsk/c6t600601607D731F006596A6CD2CE9DD11d0s0
CRS_VOTING_DISKS=/dev/rdsk/c6t600601607D731F00A8833C0377FEDD11d0s0,/dev/rdsk/c6t600601607D731F00A6833C0377FEDD11d0s0,/dev/rdsk/c6t60
0601607D731F00A7833C0377FEDD11d0s0
CRS_NODELIST=radbank02,radbank03
CRS_NODEVIPS='radbank02/radbank02-vip/255.255.255.0/bge0,radbank03/radbank03-vip/255.255.255.0/bge0'
root@radbank04 # /oracle/product/crs10g/install/rootconfig
Checking to see if Oracle CRS stack is already configured
OCR LOCATIONS = /dev/rdsk/c6t600601607D731F00AE6F711488FEDD11d0s0,/dev/rdsk/c6t600601607D731F00AF6F711488FEDD11d0s0
OCR backup directory '/oracle/product/crs10g/cdata/crs' does not exist. Creating now
Setting the permissions on OCR backup directory
Setting up NS directories
Oracle Cluster Registry configuration upgraded successfully
WARNING: directory '/oracle/product' is not owned by root
WARNING: directory '/oracle' is not owned by root
clscfg: EXISTING configuration version 3 detected.
clscfg: version 3 is 10G Release 2.
Successfully accumulated necessary OCR keys.
Using ports: CSS=49895 CRS=49896 EVMC=49898 and EVMR=49897.
node:
node 1: radbank02 radbank02-priv1 radbank02
node 2: radbank03 radbank03-priv1 radbank03
clscfg: Arguments check out successfully.
NO KEYS WERE WRITTEN. Supply -force parameter to override.
-force is destructive and will destroy any previous cluster
configuration.
Oracle Cluster Registry for cluster has already been initialized
Startup will be queued to init within 30 seconds.
Adding daemons to inittab
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
radbank02
radbank03
radbank04
CSS is active on all nodes.
root@radbank04 # ./crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.monsc.db application ONLINE ONLINE radbank03
ora....c1.inst application ONLINE ONLINE radbank02
ora....c2.inst application ONLINE ONLINE radbank03
ora.montc.db application ONLINE ONLINE radbank02
ora....ntc1.cs application ONLINE ONLINE radbank02
ora....c1.inst application ONLINE ONLINE radbank02
ora....tc1.srv application ONLINE ONLINE radbank02
ora....ntc2.cs application ONLINE ONLINE radbank03
ora....c2.inst application ONLINE ONLINE radbank03
ora....tc2.srv application ONLINE ONLINE radbank03
ora....ntc3.cs application ONLINE ONLINE radbank03
ora....tc1.srv application ONLINE ONLINE radbank02
ora....tc2.srv application ONLINE ONLINE radbank03
ora....SM1.asm application ONLINE ONLINE radbank02
ora....02.lsnr application ONLINE ONLINE radbank02
ora....02.lsnr application ONLINE ONLINE radbank02
ora....k02.gsd application ONLINE ONLINE radbank02
ora....k02.ons application ONLINE ONLINE radbank02
ora....k02.vip application ONLINE ONLINE radbank02
ora....SM2.asm application ONLINE ONLINE radbank03
ora....03.lsnr application ONLINE ONLINE radbank03
ora....03.lsnr application ONLINE ONLINE radbank03
ora....k03.gsd application ONLINE ONLINE radbank03
ora....k03.ons application ONLINE ONLINE radbank03
ora....k03.vip application ONLINE ONLINE radbank03
ora....k04.gsd application ONLINE ONLINE radbank04
ora....k04.ons application ONLINE ONLINE radbank04
ora....k04.vip application ONLINE ONLINE radbank04
My new node is part of my existing cluster now. Now time to add the ORACLE_HOME (database installation) and the instances with the services.
Resources:
Oracle® Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide 10g Release 2 (10.2)
Subscribe to:
Posts (Atom)
