Server Administration :: ASM Rebalance Operation Never End
Sep 19, 2011
I had added a new disk into disk group DATA1 with rebalance power of 5, it ran as estimated for about 2 days long, I can see the estimated time down to zero but the process never end.
The command used:
SQL> alter diskgroup DATA1 add disk '<path>' rebalance power 5;
Below is the disk status:
SQL> select group_number, disk_number, total_mb, free_mb, mount_status, header_status, state
from v$asm_disk where group_number=1 order by disk_number;
GROUP_NUMBER DISK_NUMBER TOTAL_MB FREE_MB MOUNT_S HEADER_STATU STATE
------------ ----------- ---------- ---------- ------- ------------ --------
1 0 1191626 314236 CACHED MEMBER NORMAL
1 1 1191626 314230 CACHED MEMBER NORMAL
1 2 1191626 314232 CACHED MEMBER NORMAL
1 3 1191602 314229 CACHED MEMBER NORMAL
1 4 1191626 314210 CACHED MEMBER NORMAL
1 5 1191626 314218 CACHED MEMBER NORMAL
1 6 1191626 314218 CACHED MEMBER NORMAL
1 7 1191602 314223 CACHED MEMBER NORMAL
1 8 1191626 314216 CACHED MEMBER NORMAL
1 9 1191626 314202 CACHED MEMBER NORMAL
1 10 1191626 314230 CACHED MEMBER NORMAL
[code]....
I've checked and follow the status for a few days more, it still running with EST_MINUTES=0. I think it was hang and never end.During the next few weeks, I've manually restarted the rebalance process by modifying its power to 6, 8, 10
SQL> alter diskgroup DATA1 rebalance power 10;
After every restart, the diskgroup rebalance again but has the same issue, never end.Here is the content in the alert log
NOTE: starting rebalance of group 1/0x9d2529a0 (DATA1) at power 5
Starting background process ARB0
Fri Sep 16 12:01:11 2011
ARB0 started with pid=15, OS id=21066
Starting background process ARB1
Starting background process ARB2
Fri Sep 16 12:01:11 2011
[code]....
As you can see, the rebalance process started 3 days ago, hanging till now
SQL> select group_number, operation, state, power, est_minutes from v$asm_operation;
GROUP_NUMBER OPERA STAT POWER EST_MINUTES
------------ ----- ---- ---------- -----------
1 REBAL RUN 5 0
While doing scanning of character set, error occurred in between.
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
Current database character set is AL32UTF8. Enter new database character set name: > WE8MSWIN1252 Enter array fetch buffer size: 1024000 > Enter number of scan processes to utilize(1..64): 1 > Enumerating tables to scan...
. process 1 scanning REINS.RI_TD_EXECUTION_LOG[AAASwXAAAAABwkAAAA] . process 1 scanning INS.SERVICE_TRANSACTION_LOG[AAAS5hAAAAADWiAAAA] . process 1 scanning REINS.RI_TD_VOUCHER_DETAILS[AAASx+AAAAAC6KAAAA] . process 1 scanning REINS.RI_TT_VOUCHER_DETAILS[AAAS0OAAAAAAMiAAAA] . process 1 scanning CONFSYS.DML_AUDIT_LOG_FOR_DESIGNER[AAASnwAAAAAAsIAAAA] . process 1 scanning REINS.RI_TD_TRANSACTION_LOG[AAASx0AAAAACsoAAAA] [code]...
I have deinstalled Oracle database, ASM and oracle software. I installed the oracle software, ASM and database. I have seen the error in the ASM trace called
Errors in file /opt/oracle/admin/+ASM/bdump/+asm_gmon_10518.trc:
ORA-29702: error occurred in Cluster Group Service operation ORA-29702: error occurred in Cluster Group Service operation
UDI-22303: operation generated ORACLE error 22303 ORA-22303: type "SYS"."DBMSOUTPUT_LINESARRAY" not found ORA-00600: internal error code, arguments: [kokaocr], [], [], [], [], [], [], [], [], [], [], [] ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_OUTPUT" ORA-06512: at line
run down of the implications of MERGE by ROWID in such a fashion:
CODE MERGE INTO XXWT_AP_ACCRUALS_RECEIPT_F EXT USING ( SELECT PO_DISTRIBUTION_ID,
[code]...
Can this lead to an "Unstable Set of Rows?". Is it possible for the ROWID's to change during the execution of this statement - meaning certain ROWIDs identified in the SELECT will not actually be updated when it comes to the MERGE operation?
Basically, is it sound practice to use ROWID to merge on - in cases where you dont have a WHEN NOT MATCHED condition?
I am using the dblink to merge the data. I am using the following merge statement.
merge into APP_USER.USR_NEW_RIGHTS@NEW_RIGHTS t Using (select 'test' GRANTEE,'TESTxxx'ROLE from dual ) s on (t.GRANTEE = s.GRANTEE and t.ROLE = s.ROLE) when not matched then insert (ID,GRANTEE,ROLE,XRIGHT,COMPANY,OWNER,TABLENAME) values ('','test','TESTxxx',null, null, null, null);
I know that I have to set a commit and it's working when I insert information's with a normal insert statement via database link, but it seems that merging doesn't work.
then these values are present as substring in the particular column in the source view. So I need to flag those records. For every record, I need to check whether all the values present in the reference table matches or not. If it matches then it should be flagged.
I can use in operator as we are not checking for the exact match and we are checking whether that value is present anywhere in that column record.
Looping results in performance issue. We can use PL/SQL for this. As the source view is put into a ETL internal file.
Frequently getting below error in alert log file. to sort out this issue. Database: Active standby database OS: Windows Server Db Version: 11.2.0.2
Error: *********************************************************************** Fatal NI connect error 12170. VERSION INFORMATION: TNS for 64-bit Windows: Version 11.2.0.2.0 - Production Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 11.2.0.2.0 - Production
I have a car table with the following attribute sale_code,model_car etc. the format of sale_code is year/number example when a client buy a car(model_car=mercedes) her code is 2012/0125.
I want to calculate the number of cars that were sold in the year of 2011 grouped by the model_car attribute.
select count (sale_code),model_car from car where sale_code like '2011%'
when I tested this query i received an error.
My question is : how to resolve the issue when we use an attribute in where and we use the same attribute in count operation ?
i am trying to install Oracle 10.10.2.0 on Windows Server 2003 standard x64 Edition Service Pack, but when i try to run the installer or open DVD it gives me below error.
"The image file D: is Valid, but is for a machine type other than the current machine."
I was trying to delete the database in the test server. When i was deleting listener was already stopped, i continued deleting using dbca, it shown me some alert that datafiles cant be deleted because system could't find database, since listner was stopped so only service was deleted(the one showing in the windows administrator toolsservicesOracleServiceTEST).
All the datafile parameter files are still there. How can i delete the datafiles and parameter files belongs to that database or how to create the deleted service, so that i will start the listener and do the complete deleting of the database.
I have to migrate production database version 10gR2 on windows 2003 server to oracle 11gR2 on windows 2008 server . So far i have just installed the oracle software on 64bit windows 2008 server. How to move the data or migrate from old server to new server .
after a long time,(i never remember when was the last time i installed 9i)i was asked to install 9i.So, whether i install server or cilent i get this error <attachment>
after i ignore it and finished the installation,The server installation gets abruptly ended for the client i get TNS errors.
I am working on an SAP application migration project using Oracle 10.2.0.2 database. We are migrating the application from Windows to Solaris.
During the process we are facing problem with very slow insert operation on a particular table.The server's capacity is very good and so no resource bottleneck.
The table contains around 2,70,000 rows and inserting at around 100 rows per 10 seconds.
The table contains following data types.
SQL> desc SAPDATDB.CAF_GP_VALDEF; Name Null? Type ----------------------------------------- -------- ---------------------------- VAL_UUID NOT NULL NVARCHAR2(34) VAL_GUID NOT NULL NUMBER(10) VAL_CLOB NCLOB
How do you refresh a datablock after DML operation(s) - INSERT, UPDATE or DELETE? I know "EXECUTE_QUERY" retrieves records, but what trigger do I use to automatically refresh the datablock after these operations - my form only has 1 datablock.
When I try to call a database procedure written in Oracle 8.1.7.4.0 with OUT parameter and COMMIT statement from my Oracle 10g environment, I am getting error like "ORA-02064: distributed operation not supported".
I cannot omit OUT/COMMIT statement from the procedure because it is also updating another table from called procedure. I have tried some solutions from my end, but it is not working and same error generating. Like:
1) Moved the update statement with COMMIT statement to another procedure and calling that procedure from main called procedure 2) Creating a job to run the newly created procedure and submit the job from called procedureetc.
I have a type to table where only few columns are SCD 2. Like address, city, zip.I wrote a code using cursor. But now the requirement is to replace the cursor .how to perform type 2 using Merge in oracle 10g or any better way to perform it without using cursor. Look the code which i have attached with cursor.
CREATE OR REPLACE PROCEDURE Proc_cp_jci_contract_dim_hist IS l_fun_ret_value NUMBER; v_src_id NUMBER; rec_jci_contract_dim jci_contract_dim%ROWTYPE; rec_jci_contract_dim_hist jci_contract_dim_hist%ROWTYPE; err_row VARCHAR2(4000); ind NUMBER(1) := 1; [code]....
We are using the below procedure to generate the files on the DB server.
SQL> SQL> create or replace procedure write_to_file(p_dir varchar2, 2 p_file varchar2, 3 p_mode varchar2, 4 p_clob clob) as 5 l_output utl_file.file_type; 6 l_amt number default 32767; 7 l_offset number default 1; 8 l_length number default dbms_lob.getlength(p_clob); 9 new_clob clob; 10 BEGIN 11 12 l_output := utl_file.fopen(p_dir, p_file, p_mode, 32767); 13 while (l_offset < l_length) loop 14 new_clob:= SUBSTR(p_clob,l_offset,l_amt); 15 utl_file.put_line(l_output, new_clob,true); 16 l_offset := l_offset + dbms_lob.getlength(new_clob); 17 end loop; 18 utl_file.new_line(l_output); 19 utl_file.fclose(l_output); 20 end write_to_file; 21 /
Procedure created
SQL>
This works fine we call this from PL/SQL developer tool. However, when this procedure is called from Java JDBC, it is giving error
java.sql.SQLException: ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 536 ORA-29283: invalid file operation ORA-06512: at "SYS.OBP_UPGRADE_UTIL", line 2816 ORA-06512: at "SYS.OBP_UPGRADE_UTIL", line 3029 ORA-06512: at line 1
DB schema used for both are same (SYS) Verified the directory on the DB server This Procedure called from package with AUTHID CURRENT_USER option.
I am trying to use on trigger to get the code of an DDL operation, I use the code supplied on orafaq (orafaq.com/scripts/plsql/auditdll.txt) as base code and I added a collumn objcode and a call to DBMS_METADATA.GET_DDL to save the object code, however I am receiving an Oracle error ORA-00604.See code below.
This error is raised when I try to create a new view on the schema, on objects that I already have and I changed something usually the error did not happens, how to get the object code of one object when it is created, or changed.
I am using Oracle 11g
SQL> select banner 2 from sys.v_$version; Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production PL/SQL Release 11.2.0.1.0 - Production CORE11.2.0.1.0Production TNS for 32-bit Windows: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production
cl scr; DROP TRIGGER audit_ddl_changes / DROP TABLE dll_audit_log
[Code]....
CREATE FORCE VIEW V3 AS SELECT * FROM ALL_OBJECTS ORA-00604: error occurred at recursive SQL level 1 ORA-31603: object "V3" of type VIEW not found in schema "XXISV_V11R01" ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105 ORA-06512: at "SYS.DBMS_METADATA", line 3912 ORA-06512: at "SYS.DBMS_METADATA", line 5678 ORA-06512: at line 1 ORA-06512: at line 2
I am getting the below exception when i access the WebPage.This only happens sometimes, so if i try again to hit the URL, it works. Not getting why this is behaving unusual.
StandardWrapperValve[jsp]: Servlet.service() for servlet jsp threw exception (wt.fc.fcResource/0) wt.util.WTException: The operation: "getPrincipal" failed. Nested exception is: wt.util.WTRemoteException: Unable to invoke remote method; nested exception is: java.lang.OutOfMemoryError
I am trying to find the unix process for one of my application in the database but I am unable to view the same. To simulate, I did the following.
1. My database runs on different server. 2. I invoked "sqlplus" from another unix box to login to the database. 3. I found that the process id (ps -ef |grep sqlplus). 4. When I execute the below mentioned query it does not display the process id that I am looking for. But the osuser, username, program and machine details are correct. How can I know the process details from the database?
SELECT SYS.GV_$SESSION.OSUSER, SYS.GV_$SESSION.USERNAME, SYS.GV_$PROCESS.SPID, SYS.GV_$SESSION.MACHINE, SYS.GV_$SESSION.PROGRAM, SYS.GV_$PROCESS.PROGRAM ,SYS.GV_$SESSION.SQL_ID FROM SYS.GV_$PROCESS, SYS.GV_$SESSION WHERE SYS.GV_$PROCESS.ADDR=SYS.GV_$SESSION.PADDR and SYS.GV_$SESSION.USERNAME='TEST' and SYS.GV_$SESSION.MACHINE like '%hostname%'