Surachart Opun
This page contains my experiences and my thoughts about Oracle and etc... about IT. Perhaps With another way in my life.
Updated: 8 hours 30 min ago
Invalid Objects on SYS and ORA-04063: package body "SYS.DBMS_REGISTRY_SYS" has errors
Some Objects invalid on SYS, then used catalog.sql, catproc.sql, that made error:
SELECT dbms_registry_sys.time_stamp('PATCH_BGN') AS timestamp FROM DUAL
*ERROR at line 1:
ORA-04063: package body "SYS.DBMS_REGISTRY_SYS" has errorsBEGIN dbms_registry.check_server_instance; END;*
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_REGISTRY" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_REGISTRY"
ORA-06512: at line 1 Some Objects invalid, we can recreate with scripts at ORACLE_HOME/rdbms/admin PATH
Example Object invalid:
DBMS_STATS_INTERNAL PACKAGE BODY ,DBMS_STATS PACKAGE BODY
We can recreate dbms_stats_internal & dbms_stats:
SQL> @?/rdbms/admin/prvtstas.plb
SQL> @?/rdbms/admin/prvtstat.plbBut Our Problem, we found many objects invalid on SYS, after used "catalog.sql", "catproc.sql"
SQL> startup migrate -- "backup database" before & if use RAC, should -- alter system set cluster_database=false scope=spfile -- before
SQL> spool /tmp/catalog.log
SQL> @?/rdbms/admin/catalog.sql
SQL> spool off
SQL> spool /tmp/catproc.log
SQL> @?/rdbms/admin/catproc.sql
SQL> spool offBelow Error after used catproc.sql:
ORA-04063: package body "SYS.DBMS_REGISTRY" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_REGISTRY" Checked in spool file.
Warning: Package Body created with compilation errors.
Errors for PACKAGE BODY DBMS_REGISTRY:
95/17 PL/SQL: Item ignored
95/21 PLS-00302: component 'REGISTRY$' must be declared
Checked objects invalid(Found many objects invalid):
select
decode( OBJECT_TYPE, 'PACKAGE BODY',
'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',
'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;' )
from
dba_objects a
where
STATUS = 'INVALID' and
OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE',
'TRIGGER', 'VIEW' )
and owner='SYS'
order by
OBJECT_TYPE,
OBJECT_NAME
/What should we do ?
- recover database until time (if have backup & if necessary)
- check inconsistencies in the data dictionary(metalink 136697.1) hcheck package, then verify the reported inconsistency data dictionary + fix(manual)... and then run "catalog.sql", "catproc.sql" and "utlrp.sql" again.
Instructions:
1. Connect as SYS schema in sqlplus
2. Create package hout as described in Note:101468.1
3. Create package hcheck in SYS schema.
4. execute hcheck.full
5. verify the reported inconsistencyThen:
SQL> startup migrate
SQL> set serverout on
SQL> set echo on
SQL> spool report.txt
SQL> exec hcheck.full
SQL> spool offVerifed(metalink 456468.1) the reported inconsistency data dictionary and fixed(manual), then:
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> @?/rdbms/admin/utlrp.sqlNo Error about DBMS_REGISTRY package.
Actually, we should solve a real problem, before use catalog/catproc.
The SYS.DBMS_REGISTRY problem, that made database couldn't do something (expdmp/exp, run utlrp.sql or ...)
By the way, Thank You Oracle Support for hcheck package.Written By: Surachart Opun http://surachartopun.com
SELECT dbms_registry_sys.time_stamp('PATCH_BGN') AS timestamp FROM DUAL
*ERROR at line 1:
ORA-04063: package body "SYS.DBMS_REGISTRY_SYS" has errorsBEGIN dbms_registry.check_server_instance; END;*
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_REGISTRY" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_REGISTRY"
ORA-06512: at line 1 Some Objects invalid, we can recreate with scripts at ORACLE_HOME/rdbms/admin PATH
Example Object invalid:
DBMS_STATS_INTERNAL PACKAGE BODY ,DBMS_STATS PACKAGE BODY
We can recreate dbms_stats_internal & dbms_stats:
SQL> @?/rdbms/admin/prvtstas.plb
SQL> @?/rdbms/admin/prvtstat.plbBut Our Problem, we found many objects invalid on SYS, after used "catalog.sql", "catproc.sql"
SQL> startup migrate -- "backup database" before & if use RAC, should -- alter system set cluster_database=false scope=spfile -- before
SQL> spool /tmp/catalog.log
SQL> @?/rdbms/admin/catalog.sql
SQL> spool off
SQL> spool /tmp/catproc.log
SQL> @?/rdbms/admin/catproc.sql
SQL> spool offBelow Error after used catproc.sql:
ORA-04063: package body "SYS.DBMS_REGISTRY" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_REGISTRY" Checked in spool file.
Warning: Package Body created with compilation errors.
Errors for PACKAGE BODY DBMS_REGISTRY:
95/17 PL/SQL: Item ignored
95/21 PLS-00302: component 'REGISTRY$' must be declared
Checked objects invalid(Found many objects invalid):
select
decode( OBJECT_TYPE, 'PACKAGE BODY',
'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',
'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;' )
from
dba_objects a
where
STATUS = 'INVALID' and
OBJECT_TYPE in ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE',
'TRIGGER', 'VIEW' )
and owner='SYS'
order by
OBJECT_TYPE,
OBJECT_NAME
/What should we do ?
- recover database until time (if have backup & if necessary)
- check inconsistencies in the data dictionary(metalink 136697.1) hcheck package, then verify the reported inconsistency data dictionary + fix(manual)... and then run "catalog.sql", "catproc.sql" and "utlrp.sql" again.
Instructions:
1. Connect as SYS schema in sqlplus
2. Create package hout as described in Note:101468.1
3. Create package hcheck in SYS schema.
4. execute hcheck.full
5. verify the reported inconsistencyThen:
SQL> startup migrate
SQL> set serverout on
SQL> set echo on
SQL> spool report.txt
SQL> exec hcheck.full
SQL> spool offVerifed(metalink 456468.1) the reported inconsistency data dictionary and fixed(manual), then:
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> @?/rdbms/admin/utlrp.sqlNo Error about DBMS_REGISTRY package.
Actually, we should solve a real problem, before use catalog/catproc.
The SYS.DBMS_REGISTRY problem, that made database couldn't do something (expdmp/exp, run utlrp.sql or ...)
By the way, Thank You Oracle Support for hcheck package.Written By: Surachart Opun http://surachartopun.com
Categories: DBA Blogs
DATAPUMP with SYS$SYS.* service_names
While we using DATAPUMP, we will find ALTER SYSTEM SET service_names='SYS$SYS.*...' in alert log file(RAC).
$ expdp directory=TMP logfile=full.log dumpfile=full.dmp full=yIn alert log file:
Mon Nov 30 01:14:22 2009
ALTER SYSTEM SET service_names='service2','service1','SYS$SYS.KUPC$C_1_20091130011411.ORCL' SCOPE=MEMORY SID='orcl1';
ALTER SYSTEM SET service_names='SYS$SYS.KUPC$C_1_20091130011411.ORCL','service2','service1','SYS$SYS.KUPC$S_1_20091130011411.ORCL' SCOPE=MEMORY SID='orcl1';
Mon Nov 30 01:14:29 2009
DM00 started with pid=56, OS id=26976, job SYS.SYS_EXPORT_SCHEMA_01
At "SERVICE_NAMES" parameter:
SQL> show parameter service_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string SYS$SYS.KUPC$C_1_20091130011411.ORCL, service2, service1, SYS$SYS.KUPC$S_1_20091130011411.ORCLAfter DATAPUMP finished. In alert log file:
ALTER SYSTEM SET service_names='SYS$SYS.KUPC$S_1_20091130011602.ORCL','service2','service1' SCOPE=MEMORY SID='orcl1';
ALTER SYSTEM SET service_names='service2','service1' SCOPE=MEMORY SID='orcl1';
At SERVICE_NAMES parameter:
SQL> show parameter service_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string service2, service1DATAPUMP doesn't change existing service name and no impact to existing services.
DATAPUMP just adds new services for its own queue operation.
read more metalink 363396.1Written By: Surachart Opun http://surachartopun.com
$ expdp directory=TMP logfile=full.log dumpfile=full.dmp full=yIn alert log file:
Mon Nov 30 01:14:22 2009
ALTER SYSTEM SET service_names='service2','service1','SYS$SYS.KUPC$C_1_20091130011411.ORCL' SCOPE=MEMORY SID='orcl1';
ALTER SYSTEM SET service_names='SYS$SYS.KUPC$C_1_20091130011411.ORCL','service2','service1','SYS$SYS.KUPC$S_1_20091130011411.ORCL' SCOPE=MEMORY SID='orcl1';
Mon Nov 30 01:14:29 2009
DM00 started with pid=56, OS id=26976, job SYS.SYS_EXPORT_SCHEMA_01
At "SERVICE_NAMES" parameter:
SQL> show parameter service_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string SYS$SYS.KUPC$C_1_20091130011411.ORCL, service2, service1, SYS$SYS.KUPC$S_1_20091130011411.ORCLAfter DATAPUMP finished. In alert log file:
ALTER SYSTEM SET service_names='SYS$SYS.KUPC$S_1_20091130011602.ORCL','service2','service1' SCOPE=MEMORY SID='orcl1';
ALTER SYSTEM SET service_names='service2','service1' SCOPE=MEMORY SID='orcl1';
At SERVICE_NAMES parameter:
SQL> show parameter service_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string service2, service1DATAPUMP doesn't change existing service name and no impact to existing services.
DATAPUMP just adds new services for its own queue operation.
read more metalink 363396.1Written By: Surachart Opun http://surachartopun.com
Categories: DBA Blogs
Disable/Enable Automatic startup Oracle HAS
On 11gR2, Oracle Clusterware consists of two separate stacks: an upper stack anchored by the Cluster Ready Services (CRS) daemon (crsd) and a lower stack anchored by the Oracle High Availability Services daemon (ohasd).
So.. How to disable/enable Oracle HAS.
Use the crsctl disable has command to disable automatic startup of the Oracle High Availability Services stack when the server boots up.
# crsctl config has
CRS-4622: Oracle High Availability Services autostart is enabled.How to know Oracle HAS is enabled(if doesn't use "crsctl config has")
# cat /etc/oracle/scls_scr/rhel5-test/root/ohasdstr
enable
# crsctl disable has
CRS-4621: Oracle High Availability Services autostart is disabled.
# crsctl config has
CRS-4621: Oracle High Availability Services autostart is disabled.
# cat /etc/oracle/scls_scr/rhel5-test/root/ohasdstr
disableUse the crsctl enable has command to enable automatic startup of the Oracle High Availability Services stack when the server boots up.
# crsctl enable has
CRS-4622: Oracle High Availability Services autostart is enabled.
# cat /etc/oracle/scls_scr/rhel5-test/root/ohasdstr
enableIf We just check HAS Disable/Enable status, that uses "crsctl config has" command, it's easier than "ohasdstr" file checking.
How about "crsctl disable/enable crs" on 11gR2?
They disable/enable automatic startup of Oracle HAS.
I posted "check enable/disable the startup of CRS".. that show Oracle Clusterware version <= 11gR1, we can check from "crsstart" file. On 11gR2, crsstart file is not used ???
Use the crsctl disable crs command to prevent the automatic startup of Oracle High Availability Services when the server boots.
Use the crsctl enable crs command to enable automatic startup of Oracle High Availability Services when the server boots.
# crsctl config has
CRS-4622: Oracle High Availability Services autostart is enabled.
# crsctl config crs
CRS-4622: Oracle High Availability Services autostart is enabled.
# ls -ltr /etc/oracle/scls_scr/rhel5-test/root/
-rw-r--r-- 1 root root 7 Sep 7 00:56 crsstart
-rw-r--r-- 1 root oinstall 5 Nov 22 17:04 ohasdrun
-rw-r--r-- 1 root oinstall 7 Nov 22 17:10 ohasdstr# cat /etc/oracle/scls_scr/rhel5-test/root/crsstart
enable
# cat /etc/oracle/scls_scr/rhel5-test/root/ohasdstr
enable
# crsctl disable crs
CRS-4621: Oracle High Availability Services autostart is disabled.
# crsctl config crs
CRS-4621: Oracle High Availability Services autostart is disabled.
# crsctl config has
CRS-4621: Oracle High Availability Services autostart is disabled.
# ls -ltr /etc/oracle/scls_scr/rhel5-test/root/
-rw-r--r-- 1 root root 7 Sep 7 00:56 crsstart
-rw-r--r-- 1 root oinstall 5 Nov 22 17:04 ohasdrun
-rw-r--r-- 1 root oinstall 8 Nov 22 17:12 ohasdstr
# cat /etc/oracle/scls_scr/rhel5-test/root/crsstart
enable
# cat /etc/oracle/scls_scr/rhel5-test/root/ohasdstr
disable
However, check CRSCTL Utility ReferenceWritten By: Surachart Opun http://surachartopun.com
Categories: DBA Blogs
Hiring 2 positions in Oracle Thailand
Oracle Functional Financials Lead
This candidate need to be a seasoned Oracle Applications Financials Lead with proven track of record of successful implementations. We (and client) will look for an accounting practitioner background, preferably CPA certified and working experience in multinational environment. In addition, candidate need to have 6+ years exposure in Oracle Financials implementations with 2-3 years in a Lead role. Automotive and/or Manufacturing will be a definite plus.
Sr. Project Manager
Candidate should have an ERP package project management background with proven success with large projects in multinational environments. Preferably including Oracle Financials implementations, team size 15+ with a budget of 2 M US$++, familiar with concepts of Program Management. Candidate must be fluent in English and excellent communication skills. Project Management experience minimum years of experience 10+
For more details contact sam.abraham@oracle.comWritten By: Surachart Opun http://surachartopun.com
This candidate need to be a seasoned Oracle Applications Financials Lead with proven track of record of successful implementations. We (and client) will look for an accounting practitioner background, preferably CPA certified and working experience in multinational environment. In addition, candidate need to have 6+ years exposure in Oracle Financials implementations with 2-3 years in a Lead role. Automotive and/or Manufacturing will be a definite plus.
Sr. Project Manager
Candidate should have an ERP package project management background with proven success with large projects in multinational environments. Preferably including Oracle Financials implementations, team size 15+ with a budget of 2 M US$++, familiar with concepts of Program Management. Candidate must be fluent in English and excellent communication skills. Project Management experience minimum years of experience 10+
For more details contact sam.abraham@oracle.comWritten By: Surachart Opun http://surachartopun.com
Categories: DBA Blogs
LOCK_SGA can not use with AMM or ASMM
LOCK_SGA locks the entire SGA into physical memory. It is usually advisable to lock the SGA into real (physical) memory, especially if the use of virtual memory would include storing some of the SGA using disk space.
On 11gR2:
SQL> show parameter lock_sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSESQL> show parameter memory_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_target big integer 776MSQL> ALTER SYSTEM SET LOCK_SGA=TRUE SCOPE=SPFILE;
System altered.SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.SQL> startup
ORA-00847: MEMORY_TARGET/MEMORY_MAX_TARGET and LOCK_SGA cannot be set togetherOn 10gR2:
SQL> show parameter lock_sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
SQL> show parameter sga_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 168M
SQL> ALTER SYSTEM SET LOCK_SGA=TRUE SCOPE=SPFILE;
System altered.SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.SQL> startup
ORA-27102: out of memory
Linux-x86_64 Error: 12: Cannot allocate memoryIf need to use LOCK_SGA -)
- To Disable the Use of Automatic Memory Management or Automatic Shared Memory Management and enable manual shared memory management
Or...
- Don't lock the SGA.Written By: Surachart Opun http://surachartopun.com
On 11gR2:
SQL> show parameter lock_sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSESQL> show parameter memory_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
memory_target big integer 776MSQL> ALTER SYSTEM SET LOCK_SGA=TRUE SCOPE=SPFILE;
System altered.SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.SQL> startup
ORA-00847: MEMORY_TARGET/MEMORY_MAX_TARGET and LOCK_SGA cannot be set togetherOn 10gR2:
SQL> show parameter lock_sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
SQL> show parameter sga_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 168M
SQL> ALTER SYSTEM SET LOCK_SGA=TRUE SCOPE=SPFILE;
System altered.SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.SQL> startup
ORA-27102: out of memory
Linux-x86_64 Error: 12: Cannot allocate memoryIf need to use LOCK_SGA -)
- To Disable the Use of Automatic Memory Management or Automatic Shared Memory Management and enable manual shared memory management
Or...
- Don't lock the SGA.Written By: Surachart Opun http://surachartopun.com
Categories: DBA Blogs
V$SGA_RESIZE_OPS.STATUS ERROR
In 10G version, the ASMM(Automatic Shared Memory Management) has been introduced to relieve DBAs from sizing some parts of the SGA by themselves.
ASMM can be configured by using the SGA_TARGET initialization parameter.when set > 0, the ASMM is enabledwhen set to 0, the ASMM is disabled
V$SGA_RESIZE_OPS displays information about the last 800 completed SGA resize operations.ALTER SESSION SET nls_date_format = 'DD/MM/YYYY:HH24:MI:SS';SET PAGESIZE 900SET LINESIZE 255COL COMPONENT FORMAT A25COL INITIAL_SIZE FORMAT A10COL TARGET_SIZE FORMAT A10COL FINAL_SIZE FORMAT A10COL OPER_TYPE FORMAT A10select START_TIME, component, oper_type, oper_mode,status, initial_size/1024/1024 "INITIAL", target_size/1024/1024 "TARGET", FINAL_SIZE/1024/1024 "FINAL", END_TIMEfrom v$sga_resize_opsorder by start_time, component;START_TIME COMPONENT OPER_TYPE OPER_MODE STATUS INITIAL TARGET FINAL END_TIME------------------- ------------------------- ---------- --------- ---------- ---------- ---------- ---------- -------------------16/11/2009:15:12:24 DEFAULT buffer cache SHRINK IMMEDIATE COMPLETE 5776 5760 5760 16/11/2009:15:12:2416/11/2009:15:12:24 shared pool GROW IMMEDIATE COMPLETE 4416 4432 4432 16/11/2009:15:12:2416/11/2009:19:22:34 DEFAULT buffer cache GROW DEFERRED ERROR 5760 5856 5792 16/11/2009:19:22:3516/11/2009:19:22:34 shared pool SHRINK DEFERRED ERROR 4432 4336 4400 16/11/2009:19:22:35My result found "ERROR" (sizing operation was unable to complete) status.Use this error checked in metalink(556928.1) and they told to increase the SGA_TARGET.
So, I checked v$sga_target_advice ...select * from v$sga_target_advice order by sga_size;SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR Estd Phys Reads---------- --------------- ------------ ------------------- ---------------- 2560 .25 68538927 2.6068 5,648,312,410 5120 .5 34072278 1.2959 1,863,613,370 7680 .75 28290588 1.076 1,204,264,183 10240 1 26292359 1 1,020,822,398 12800 1.25 25737600 .9789 973,149,992 15360 1.5 25416834 .9667 973,149,992 17920 1.75 25377404 .9652 847,180,508 20480 2 25377406 .9652 767,045,950Idea???...Investigate & solve anything reduce SGA size... (perhaps increase SGA_TARGET)... or it's just Bug!
Written By: Surachart Opun http://surachartopun.com
ASMM can be configured by using the SGA_TARGET initialization parameter.when set > 0, the ASMM is enabledwhen set to 0, the ASMM is disabled
V$SGA_RESIZE_OPS displays information about the last 800 completed SGA resize operations.ALTER SESSION SET nls_date_format = 'DD/MM/YYYY:HH24:MI:SS';SET PAGESIZE 900SET LINESIZE 255COL COMPONENT FORMAT A25COL INITIAL_SIZE FORMAT A10COL TARGET_SIZE FORMAT A10COL FINAL_SIZE FORMAT A10COL OPER_TYPE FORMAT A10select START_TIME, component, oper_type, oper_mode,status, initial_size/1024/1024 "INITIAL", target_size/1024/1024 "TARGET", FINAL_SIZE/1024/1024 "FINAL", END_TIMEfrom v$sga_resize_opsorder by start_time, component;START_TIME COMPONENT OPER_TYPE OPER_MODE STATUS INITIAL TARGET FINAL END_TIME------------------- ------------------------- ---------- --------- ---------- ---------- ---------- ---------- -------------------16/11/2009:15:12:24 DEFAULT buffer cache SHRINK IMMEDIATE COMPLETE 5776 5760 5760 16/11/2009:15:12:2416/11/2009:15:12:24 shared pool GROW IMMEDIATE COMPLETE 4416 4432 4432 16/11/2009:15:12:2416/11/2009:19:22:34 DEFAULT buffer cache GROW DEFERRED ERROR 5760 5856 5792 16/11/2009:19:22:3516/11/2009:19:22:34 shared pool SHRINK DEFERRED ERROR 4432 4336 4400 16/11/2009:19:22:35My result found "ERROR" (sizing operation was unable to complete) status.Use this error checked in metalink(556928.1) and they told to increase the SGA_TARGET.
So, I checked v$sga_target_advice ...select * from v$sga_target_advice order by sga_size;SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR Estd Phys Reads---------- --------------- ------------ ------------------- ---------------- 2560 .25 68538927 2.6068 5,648,312,410 5120 .5 34072278 1.2959 1,863,613,370 7680 .75 28290588 1.076 1,204,264,183 10240 1 26292359 1 1,020,822,398 12800 1.25 25737600 .9789 973,149,992 15360 1.5 25416834 .9667 973,149,992 17920 1.75 25377404 .9652 847,180,508 20480 2 25377406 .9652 767,045,950Idea???...Investigate & solve anything reduce SGA size... (perhaps increase SGA_TARGET)... or it's just Bug!
Written By: Surachart Opun http://surachartopun.com
Categories: DBA Blogs
Investigate ROW CACHE LOCK
My Oracle RAC had the problem, so I investigated... I found:Top 5 Timed Events Avg %Total~~~~~~~~~~~~~~~~~~ wait CallEvent Waits Time (s) (ms) Time Wait Class------------------------- ------------ ----------- ------ ------ ----------row cache lock 509,761 1,259,315 2470 71.8 ConcurrencROW CACHE LOCK (DDL statements require "row cache lock" and session will wait "row cache lock" to lock the data dictionary)
And then check V$SESSIONSQL> select p1text,p1,p2text,p2,p3text,p3 from v$session where event = 'row cache lock';
P1TEXT P1 P2TEXT P2 P3TEXT P3-------------- --------- -------------- --------- -------------- ----------cache id 13 mode 0 request 5cache id 13 mode 0 request 5cache id 13 mode 0 request 5cache id 13 mode 0 request 5cache id 13 mode 0 request 5cache id 13 mode 0 request 5cache id 13 mode 0 request 5What I see? I need "cache id" to find on V$ROWCACHE (to check enqueue type)Example: Enqueue TypeDC_TABLESPACES Probably the most likely is allocation of new extents. If extent sizes are set low then the application may constantly be requesting new extents and causing contention. Do you have objects with small extent sizes that are rapidly growing? (You may be able to spot these by looking for objects with large numbers of extents). Check the trace for insert/update activity, check the objects inserted into for number of extents.
DC_SEQUENCES Check for appropriate caching of sequences for the application requirements.
DC_USERS Deadlock and resulting "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!" can occur if a session issues a GRANT to a user, and that user is in the process of logging on to the database.
DC_OBJECTS Look for any object compilation activity which might require an exclusive lock and thus block online activity.
DC_SEGMENTS This is likely to be down to segment allocation. Identify what the session holding the enqueue is doing and use errorstacks to diagnose.To check on V$ROWCACHESQL> select PARAMETER ,COUNT ,GETS ,GETMISSES ,MODIFICATIONS from v$rowcache where cache#=13;
PARAMETER COUNT GETS GETMISSES MODIFICATIONS--------------------------- ---------- ---------- ---------- -------------dc_sequences 13 746449 210320 746449
SQL> column pct_succ_gets format 999.9SQL> column updates format 999,999,999SQL> SELECT parameter, sum(gets), sum(getmisses), 100*sum(gets - getmisses) / sum(gets) pct_succ_gets, sum(modifications) updates FROM V$ROWCACHE WHERE gets > 0 GROUP BY parameter;
PARAMETER SUM(GETS) SUM(GETMISSES) PCT_SUCC_GETS UPDATES---------------- ---------- -------------- ------------- ------------dc_constraints 4288 1455 66.1 4,288dc_object_ids 8161040 118929 98.5 5,482outstanding_alerts 2737095 2722712 .5 200dc_awr_control 31108 526 98.3 457dc_objects 21160173 145159 99.3 266,627dc_usernames 9387743 1349 100.0 0dc_table_scns 4658 4658 .0 0dc_users 94113064 2174 100.0 16dc_histogram_defs 7702201 783888 89.8 97,472kqlsubheap_object 153 36 76.5 0dc_profiles 1266752 13 100.0 0dc_object_grants 44530796 7547 100.0 0dc_histogram_data 2431665 377265 84.5 147,793dc_segments 1658801 347470 79.1 17,402dc_files 67249 6477 90.4 0dc_sequences 748386 210954 71.8 748,386dc_database_links 2552640 89 100.0 0dc_global_oids 850330 3091 99.6 0global database name 175908 174 99.9 0dc_tablespaces 42863352 137296 99.7 0dc_tablespace_quotas 14299 5277 63.1 576dc_rollback_segments 149604805 2673 100.0 949After check at v$rowcache, my point to SEQUENCE (DC_SEQUENCES). then I check ALL_SEQUENCES and then investigate how to solve...SQL> select * from all_sequences order by last_number;
SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER------------ ---------- ---------- -------- ------------ - - ------- ---------APPUSER SEQ_XYZ 1 1.0000E+27 1 N N 0 34015364This case, I solved it by "alter sequence ... cache ..."
Refer: RAC and SequenceWritten By: Surachart Opun http://surachartopun.com
And then check V$SESSIONSQL> select p1text,p1,p2text,p2,p3text,p3 from v$session where event = 'row cache lock';
P1TEXT P1 P2TEXT P2 P3TEXT P3-------------- --------- -------------- --------- -------------- ----------cache id 13 mode 0 request 5cache id 13 mode 0 request 5cache id 13 mode 0 request 5cache id 13 mode 0 request 5cache id 13 mode 0 request 5cache id 13 mode 0 request 5cache id 13 mode 0 request 5What I see? I need "cache id" to find on V$ROWCACHE (to check enqueue type)Example: Enqueue TypeDC_TABLESPACES Probably the most likely is allocation of new extents. If extent sizes are set low then the application may constantly be requesting new extents and causing contention. Do you have objects with small extent sizes that are rapidly growing? (You may be able to spot these by looking for objects with large numbers of extents). Check the trace for insert/update activity, check the objects inserted into for number of extents.
DC_SEQUENCES Check for appropriate caching of sequences for the application requirements.
DC_USERS Deadlock and resulting "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!" can occur if a session issues a GRANT to a user, and that user is in the process of logging on to the database.
DC_OBJECTS Look for any object compilation activity which might require an exclusive lock and thus block online activity.
DC_SEGMENTS This is likely to be down to segment allocation. Identify what the session holding the enqueue is doing and use errorstacks to diagnose.To check on V$ROWCACHESQL> select PARAMETER ,COUNT ,GETS ,GETMISSES ,MODIFICATIONS from v$rowcache where cache#=13;
PARAMETER COUNT GETS GETMISSES MODIFICATIONS--------------------------- ---------- ---------- ---------- -------------dc_sequences 13 746449 210320 746449
SQL> column pct_succ_gets format 999.9SQL> column updates format 999,999,999SQL> SELECT parameter, sum(gets), sum(getmisses), 100*sum(gets - getmisses) / sum(gets) pct_succ_gets, sum(modifications) updates FROM V$ROWCACHE WHERE gets > 0 GROUP BY parameter;
PARAMETER SUM(GETS) SUM(GETMISSES) PCT_SUCC_GETS UPDATES---------------- ---------- -------------- ------------- ------------dc_constraints 4288 1455 66.1 4,288dc_object_ids 8161040 118929 98.5 5,482outstanding_alerts 2737095 2722712 .5 200dc_awr_control 31108 526 98.3 457dc_objects 21160173 145159 99.3 266,627dc_usernames 9387743 1349 100.0 0dc_table_scns 4658 4658 .0 0dc_users 94113064 2174 100.0 16dc_histogram_defs 7702201 783888 89.8 97,472kqlsubheap_object 153 36 76.5 0dc_profiles 1266752 13 100.0 0dc_object_grants 44530796 7547 100.0 0dc_histogram_data 2431665 377265 84.5 147,793dc_segments 1658801 347470 79.1 17,402dc_files 67249 6477 90.4 0dc_sequences 748386 210954 71.8 748,386dc_database_links 2552640 89 100.0 0dc_global_oids 850330 3091 99.6 0global database name 175908 174 99.9 0dc_tablespaces 42863352 137296 99.7 0dc_tablespace_quotas 14299 5277 63.1 576dc_rollback_segments 149604805 2673 100.0 949After check at v$rowcache, my point to SEQUENCE (DC_SEQUENCES). then I check ALL_SEQUENCES and then investigate how to solve...SQL> select * from all_sequences order by last_number;
SEQUENCE_OWNER SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER------------ ---------- ---------- -------- ------------ - - ------- ---------APPUSER SEQ_XYZ 1 1.0000E+27 1 N N 0 34015364This case, I solved it by "alter sequence ... cache ..."
Refer: RAC and SequenceWritten By: Surachart Opun http://surachartopun.com
Categories: DBA Blogs
DUPLICATE Without Connection to Target Database
Backup-Based: Duplication Without a Target ConnectionOracle DUPLICATE Without Connection to Target Database, that's a 11gR2 Feature. DUPLICATE can be performed without connecting to a target database. This requires connecting to a catalog and auxiliary database.
Start...
- Create password file
$ orapwd file=/oracle/product/11.2.0/dbhome_1/dbs/orapwAUX entries=5
Enter password for SYS:- Create pfile (use ORACLE_SID=AUX)
SQL> create pfile='/oracle/product/11.2.0/dbhome_1/dbs/initAUX.ora' from spfile;
File created.Modify initAUX.ora file.
*.audit_file_dest='/oracle/product/admin/AUX/adump'
*.compatible='11.2.0.0.0'
*.control_files='+DISK01/AUX/controlfile/current.263.696964775','+DISK02/AUX/controlfile/current.492.696964779'
*.db_block_size=8192
*.db_create_file_dest='+DISK01'
*.db_domain=''
*.db_name='AUX'
*.db_recovery_file_dest='+DISK02'
*.db_recovery_file_dest_size=10737418240
*.diagnostic_dest='/oracle/product'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=AUXXDB)'
*.job_queue_processes=1000
*.log_archive_format='%t_%s_%r.dbf'
*.log_buffer=33554432
*.memory_target=813694976
*.open_cursors=300
*.processes=150
*.remote_listener='RHEL5-T-cluster-scan:1521'
*.remote_login_passwordfile='exclusive'
*.undo_tablespace='UNDOTBS1'- Check parameters and create some directories
$ mkdir -p /oracle/product/admin/AUX/adump- Check Target Database in Catalog
$ sqlplus rman/password@catalog
SQL> select * from rc_database ;
DB_KEY DBINC_KEY DBID NAME RESETLOGS_CHANGE# RESETLOGS
---------- ---------- ---------- -------- ----------------- ---------
1 501 1224745511 ORCL 3626383 03-NOV-09- STARTUP NOMOUNT (AUXILIARY) database
$ export ORACLE_SID=AUX
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 3 19:27:19 2009
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 810053632 bytes
Fixed Size 2217712 bytes
Variable Size 469764368 bytes
Database Buffers 301989888 bytes
Redo Buffers 36081664 bytes
- Duplicate Database by RMAN
$ rman
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Nov 4 12:38:14 2009
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
RMAN> CONNECT AUXILIARY /
connected to auxiliary database: AUX (not mounted)
RMAN> CONNECT CATALOG rman/password@catalog
connected to recovery catalog database
RMAN> DUPLICATE DATABASE orcl TO AUX ;
Starting Duplicate Db at 04-NOV-09
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=14 device type=DISK
contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 810053632 bytes
Fixed Size 2217712 bytes
Variable Size 469764368 bytes
Database Buffers 301989888 bytes
Redo Buffers 36081664 bytes
contents of Memory Script:
{
sql clone "alter system set control_files =
''+DISK01/aux/controlfile/current.282.702045937'', ''+DISK02/aux/controlfile/current.349.702045941'' comment=
''Set by RMAN'' scope=spfile";
sql clone "alter system set db_name =
''ORCL'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''AUX'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile;
alter clone database mount;
}
executing Memory Script
sql statement: alter system set control_files = ''+DISK01/aux/controlfile/current.282.702045937'', ''+DISK02/aux/controlfile/current.349.702045941'' comment= ''Set by RMAN'' scope=spfile
sql statement: alter system set db_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''AUX'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 810053632 bytes
Fixed Size 2217712 bytes
Variable Size 469764368 bytes
Database Buffers 301989888 bytes
Redo Buffers 36081664 bytes
Starting restore at 04-NOV-09
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=140 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece +DISK02/orcl/autobackup/2009_11_03/s_701989468.353.701989473
channel ORA_AUX_DISK_1: piece handle=+DISK02/orcl/autobackup/2009_11_03/s_701989468.353.701989473 tag=TAG20091103T210428
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:17
output file name=+DISK01/aux/controlfile/current.282.702045937
output file name=+DISK02/aux/controlfile/current.349.702045941
Finished restore at 04-NOV-09
database mounted
contents of Memory Script:
{
set until scn 3657467;
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 6 to new;
set newname for clone datafile 7 to new;
restore
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 04-NOV-09
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +DISK01
channel ORA_AUX_DISK_1: restoring datafile 00002 to +DISK01
channel ORA_AUX_DISK_1: restoring datafile 00003 to +DISK01
channel ORA_AUX_DISK_1: restoring datafile 00004 to +DISK01
channel ORA_AUX_DISK_1: restoring datafile 00005 to +DISK01
channel ORA_AUX_DISK_1: restoring datafile 00006 to +DISK01
channel ORA_AUX_DISK_1: restoring datafile 00007 to +DISK01
channel ORA_AUX_DISK_1: reading from backup piece /oracle/RMAN/12ktf0ar_1_1
channel ORA_AUX_DISK_1: piece handle=/oracle/RMAN/12ktf0ar_1_1 tag=TAG20091103T210011
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:04:25
Finished restore at 04-NOV-09
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=19 STAMP=702046260 file name=+DISK01/aux/datafile/system.283.702045995
datafile 2 switched to datafile copy
input datafile copy RECID=20 STAMP=702046260 file name=+DISK01/aux/datafile/sysaux.284.702045997
datafile 3 switched to datafile copy
input datafile copy RECID=21 STAMP=702046261 file name=+DISK01/aux/datafile/undotbs1.287.702045999
datafile 4 switched to datafile copy
input datafile copy RECID=22 STAMP=702046261 file name=+DISK01/aux/datafile/users.289.702046003
datafile 5 switched to datafile copy
input datafile copy RECID=23 STAMP=702046261 file name=+DISK01/aux/datafile/example.286.702045999
datafile 6 switched to datafile copy
input datafile copy RECID=24 STAMP=702046261 file name=+DISK01/aux/datafile/tbs1.285.702045999
datafile 7 switched to datafile copy
input datafile copy RECID=25 STAMP=702046261 file name=+DISK01/aux/datafile/tbs_fda.288.702046001
contents of Memory Script:
{
set until scn 3657467;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 04-NOV-09
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 1 is already on disk as file +DISK02/orcl/archivelog/2009_11_04/thread_1_seq_1.351.702037355
archived log for thread 1 with sequence 2 is already on disk as file +DISK02/orcl/archivelog/2009_11_04/thread_1_seq_2.350.702039149
archived log file name=+DISK02/orcl/archivelog/2009_11_04/thread_1_seq_1.351.702037355 thread=1 sequence=1
archived log file name=+DISK02/orcl/archivelog/2009_11_04/thread_1_seq_2.350.702039149 thread=1 sequence=2
media recovery complete, elapsed time: 00:00:33
Finished recover at 04-NOV-09
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set db_name =
''AUX'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 810053632 bytes
Fixed Size 2217712 bytes
Variable Size 469764368 bytes
Database Buffers 301989888 bytes
Redo Buffers 36081664 bytes
sql statement: alter system set db_name = ''AUX'' comment= ''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 810053632 bytes
Fixed Size 2217712 bytes
Variable Size 469764368 bytes
Database Buffers 301989888 bytes
Redo Buffers 36081664 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "AUX" RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 50 M ,
GROUP 2 SIZE 50 M ,
GROUP 3 SIZE 50 M
DATAFILE
'+DISK01/aux/datafile/system.283.702045995'
CHARACTER SET TH8TISASCII
contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
catalog clone datafilecopy "+DISK01/aux/datafile/sysaux.284.702045997",
"+DISK01/aux/datafile/undotbs1.287.702045999",
"+DISK01/aux/datafile/users.289.702046003",
"+DISK01/aux/datafile/example.286.702045999",
"+DISK01/aux/datafile/tbs1.285.702045999",
"+DISK01/aux/datafile/tbs_fda.288.702046001";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +DISK01 in control file
cataloged datafile copy
datafile copy file name=+DISK01/aux/datafile/sysaux.284.702045997 RECID=1 STAMP=702046345
cataloged datafile copy
datafile copy file name=+DISK01/aux/datafile/undotbs1.287.702045999 RECID=2 STAMP=702046345
cataloged datafile copy
datafile copy file name=+DISK01/aux/datafile/users.289.702046003 RECID=3 STAMP=702046345
cataloged datafile copy
datafile copy file name=+DISK01/aux/datafile/example.286.702045999 RECID=4 STAMP=702046345
cataloged datafile copy
datafile copy file name=+DISK01/aux/datafile/tbs1.285.702045999 RECID=5 STAMP=702046345
cataloged datafile copy
datafile copy file name=+DISK01/aux/datafile/tbs_fda.288.702046001 RECID=6 STAMP=702046345
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=702046345 file name=+DISK01/aux/datafile/sysaux.284.702045997
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=702046345 file name=+DISK01/aux/datafile/undotbs1.287.702045999
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=702046345 file name=+DISK01/aux/datafile/users.289.702046003
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=702046345 file name=+DISK01/aux/datafile/example.286.702045999
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=702046345 file name=+DISK01/aux/datafile/tbs1.285.702045999
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=702046345 file name=+DISK01/aux/datafile/tbs_fda.288.702046001
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 04-NOV-09
- Check
SQL> select dbid, name from v$database;
DBID NAME
---------- ---------
1555302662 AUXFun ;) to duplicate database without connection to target database.Written By: Surachart Opun http://surachartopun.com
Categories: DBA Blogs
Just Fun with RMAN Recovery...
My Test Database (ARCHIVELOG MODE) lost:
Tue Nov 03 20:12:13 2009
ALTER DATABASE OPEN
This instance was first to open
Tue Nov 03 20:12:13 2009
Errors in file /oracle/product/diag/rdbms/orcl/orcl/trace/orcl_dbw0_3372.trc:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '+DISK01/orcl/datafile/system.267.696964505'
ORA-17503: ksfdopn:2 Failed to open file +DISK01/orcl/datafile/system.267.696964505
ORA-15012: ASM file '+DISK01/orcl/datafile/system.267.696964505' does not exist
Errors in file /oracle/product/diag/rdbms/orcl/orcl/trace/orcl_dbw0_3372.trc:
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '+DISK01/orcl/datafile/sysaux.266.696964509'
ORA-17503: ksfdopn:2 Failed to open file +DISK01/orcl/datafile/sysaux.266.696964509
ORA-15012: ASM file '+DISK01/orcl/datafile/sysaux.266.696964509' does not exist
Errors in file /oracle/product/diag/rdbms/orcl/orcl/trace/orcl_dbw0_3372.trc:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '+DISK01/orcl/datafile/undotbs1.265.701210003'
ORA-17503: ksfdopn:2 Failed to open file +DISK01/orcl/datafile/undotbs1.265.701210003
ORA-15012: ASM file '+DISK01/orcl/datafile/undotbs1.265.701210003' does not exist
It's time to test(RMAN) database recovery (From BACKUPSET).
Workaround:
run{
startup nomount;
restore controlfile;
alter database mount;
restore database;
recover database;
alter database open resetlogs;
}But It could not "resetlogs"... and alert log showed:
alter database open resetlogs
Errors in file /oracle/product/diag/rdbms/orcl/orcl/trace/orcl_ora_5509.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '+DISK02/orcl/onlinelog/group_1.491.696964811'
ORA-17503: ksfdopn:2 Failed to open file +DISK02/orcl/onlinelog/group_1.491.696964811
ORA-15012: ASM file '+DISK02/orcl/onlinelog/group_1.491.696964811' does not exist
ORA-00312: online log 1 thread 1: '+DISK01/orcl/onlinelog/group_1.262.696964787'
ORA-17503: ksfdopn:2 Failed to open file +DISK01/orcl/onlinelog/group_1.262.696964787
ORA-15012: ASM file '+DISK01/orcl/onlinelog/group_1.262.696964787' does not exist Perhaps I made something wrong, so i check last SCN(backupset) in catalog and recovery "until scn" :
$ rman target / catalog rman/password@catalogRMAN> list backup
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
422 Incr 4 760.00K DISK 00:01:58 03-NOV-09
BP Key: 425 Status: AVAILABLE Compressed: NO Tag: TAG20091103T174313
Piece Name: /oracle/RMAN/0uktekpi_1_1
List of Datafiles in backup set 422
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/system.267.696964505
2 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/sysaux.266.696964509
3 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/undotbs1.265.701210003
4 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/users01.dbf
5 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/example.259.696964945
6 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/tbs1.256.697633229
7 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/tbs_fda.257.701208791
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
443 Full 17.95M DISK 00:00:06 03-NOV-09
BP Key: 451 Status: AVAILABLE Compressed: NO Tag: TAG20091103T174521
Piece Name: +DISK02/orcl/autobackup/2009_11_03/s_701977521.358.701977527
SPFILE Included: Modification time: 03-NOV-09
SPFILE db_unique_name: ORCL
Control File Included: Ckp SCN: 3626526 Ckp time: 03-NOV-09
RMAN> shutdown
database dismounted
Oracle instance shut down
RMAN> startup nomount;
connected to target database (not started)
Oracle instance started
Total System Global Area 810053632 bytes
Fixed Size 2217712 bytes
Variable Size 645925136 bytes
Database Buffers 125829120 bytes
Redo Buffers 36081664 bytesRMAN> restore controlfile from autobackup;
Starting restore at 03-NOV-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=14 device type=DISK
recovery area destination: +DISK02
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: AUTOBACKUP +disk02/ORCL/AUTOBACKUP/2009_11_03/s_701977521.358.701977527 found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20091103
channel ORA_DISK_1: restoring control file from AUTOBACKUP +disk02/ORCL/AUTOBACKUP/2009_11_03/s_701977521.358.701977527
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=+DISK01/orcl/controlfile/current.263.696964775
output file name=+DISK02/orcl/controlfile/current.492.696964779
Finished restore at 03-NOV-09
RMAN> alter database mount;
Database altered.
RMAN> run {
set until scn 3626382;
restore database;
recover database;
alter database open resetlogs;
}
executing command: SET until clause
Starting restore at 03-NOV-09
Starting implicit crosscheck backup at 03-NOV-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 device type=DISK
Crosschecked 22 objects
Finished implicit crosscheck backup at 03-NOV-09
Starting implicit crosscheck copy at 03-NOV-09
using channel ORA_DISK_1
Crosschecked 1 objects
Finished implicit crosscheck copy at 03-NOV-09
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: +disk02/ORCL/AUTOBACKUP/2009_11_03/s_701977521.358.701977527
using channel ORA_DISK_1
skipping datafile 4; already restored to file +DISK01/orcl/datafile/users01.dbf
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DISK01/orcl/datafile/system.267.696964505
channel ORA_DISK_1: restoring datafile 00002 to +DISK01/orcl/datafile/sysaux.266.696964509
channel ORA_DISK_1: restoring datafile 00003 to +DISK01/orcl/datafile/undotbs1.265.701210003
channel ORA_DISK_1: restoring datafile 00005 to +DISK01/orcl/datafile/example.259.696964945
channel ORA_DISK_1: restoring datafile 00006 to +DISK01/orcl/datafile/tbs1.256.697633229
channel ORA_DISK_1: restoring datafile 00007 to +DISK01/orcl/datafile/tbs_fda.257.701208791
channel ORA_DISK_1: reading from backup piece /oracle/RMAN/0sktekgc_1_1
channel ORA_DISK_1: piece handle=/oracle/RMAN/0sktekgc_1_1 tag=TAG20091103T173819
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:03:48
Finished restore at 03-NOV-09
Starting recover at 03-NOV-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DISK01/orcl/datafile/system.272.701988391
destination for restore of datafile 00002: +DISK01/orcl/datafile/sysaux.273.701988393
destination for restore of datafile 00003: +DISK01/orcl/datafile/undotbs1.276.701988395
destination for restore of datafile 00004: +DISK01/orcl/datafile/users01.dbf
destination for restore of datafile 00005: +DISK01/orcl/datafile/example.275.701988395
destination for restore of datafile 00006: +DISK01/orcl/datafile/tbs1.274.701988395
destination for restore of datafile 00007: +DISK01/orcl/datafile/tbs_fda.277.701988395
channel ORA_DISK_1: reading from backup piece /oracle/RMAN/0uktekpi_1_1
channel ORA_DISK_1: piece handle=/oracle/RMAN/0uktekpi_1_1 tag=TAG20091103T174313
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished recover at 03-NOV-09
database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync completeJust fun with RMAN (If A database has backup)... ;)Written By: Surachart Opun http://surachartopun.com
Tue Nov 03 20:12:13 2009
ALTER DATABASE OPEN
This instance was first to open
Tue Nov 03 20:12:13 2009
Errors in file /oracle/product/diag/rdbms/orcl/orcl/trace/orcl_dbw0_3372.trc:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '+DISK01/orcl/datafile/system.267.696964505'
ORA-17503: ksfdopn:2 Failed to open file +DISK01/orcl/datafile/system.267.696964505
ORA-15012: ASM file '+DISK01/orcl/datafile/system.267.696964505' does not exist
Errors in file /oracle/product/diag/rdbms/orcl/orcl/trace/orcl_dbw0_3372.trc:
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '+DISK01/orcl/datafile/sysaux.266.696964509'
ORA-17503: ksfdopn:2 Failed to open file +DISK01/orcl/datafile/sysaux.266.696964509
ORA-15012: ASM file '+DISK01/orcl/datafile/sysaux.266.696964509' does not exist
Errors in file /oracle/product/diag/rdbms/orcl/orcl/trace/orcl_dbw0_3372.trc:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '+DISK01/orcl/datafile/undotbs1.265.701210003'
ORA-17503: ksfdopn:2 Failed to open file +DISK01/orcl/datafile/undotbs1.265.701210003
ORA-15012: ASM file '+DISK01/orcl/datafile/undotbs1.265.701210003' does not exist
It's time to test(RMAN) database recovery (From BACKUPSET).
Workaround:
run{
startup nomount;
restore controlfile;
alter database mount;
restore database;
recover database;
alter database open resetlogs;
}But It could not "resetlogs"... and alert log showed:
alter database open resetlogs
Errors in file /oracle/product/diag/rdbms/orcl/orcl/trace/orcl_ora_5509.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '+DISK02/orcl/onlinelog/group_1.491.696964811'
ORA-17503: ksfdopn:2 Failed to open file +DISK02/orcl/onlinelog/group_1.491.696964811
ORA-15012: ASM file '+DISK02/orcl/onlinelog/group_1.491.696964811' does not exist
ORA-00312: online log 1 thread 1: '+DISK01/orcl/onlinelog/group_1.262.696964787'
ORA-17503: ksfdopn:2 Failed to open file +DISK01/orcl/onlinelog/group_1.262.696964787
ORA-15012: ASM file '+DISK01/orcl/onlinelog/group_1.262.696964787' does not exist Perhaps I made something wrong, so i check last SCN(backupset) in catalog and recovery "until scn" :
$ rman target / catalog rman/password@catalogRMAN> list backup
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
422 Incr 4 760.00K DISK 00:01:58 03-NOV-09
BP Key: 425 Status: AVAILABLE Compressed: NO Tag: TAG20091103T174313
Piece Name: /oracle/RMAN/0uktekpi_1_1
List of Datafiles in backup set 422
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/system.267.696964505
2 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/sysaux.266.696964509
3 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/undotbs1.265.701210003
4 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/users01.dbf
5 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/example.259.696964945
6 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/tbs1.256.697633229
7 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/tbs_fda.257.701208791
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
443 Full 17.95M DISK 00:00:06 03-NOV-09
BP Key: 451 Status: AVAILABLE Compressed: NO Tag: TAG20091103T174521
Piece Name: +DISK02/orcl/autobackup/2009_11_03/s_701977521.358.701977527
SPFILE Included: Modification time: 03-NOV-09
SPFILE db_unique_name: ORCL
Control File Included: Ckp SCN: 3626526 Ckp time: 03-NOV-09
RMAN> shutdown
database dismounted
Oracle instance shut down
RMAN> startup nomount;
connected to target database (not started)
Oracle instance started
Total System Global Area 810053632 bytes
Fixed Size 2217712 bytes
Variable Size 645925136 bytes
Database Buffers 125829120 bytes
Redo Buffers 36081664 bytesRMAN> restore controlfile from autobackup;
Starting restore at 03-NOV-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=14 device type=DISK
recovery area destination: +DISK02
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: AUTOBACKUP +disk02/ORCL/AUTOBACKUP/2009_11_03/s_701977521.358.701977527 found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20091103
channel ORA_DISK_1: restoring control file from AUTOBACKUP +disk02/ORCL/AUTOBACKUP/2009_11_03/s_701977521.358.701977527
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=+DISK01/orcl/controlfile/current.263.696964775
output file name=+DISK02/orcl/controlfile/current.492.696964779
Finished restore at 03-NOV-09
RMAN> alter database mount;
Database altered.
RMAN> run {
set until scn 3626382;
restore database;
recover database;
alter database open resetlogs;
}
executing command: SET until clause
Starting restore at 03-NOV-09
Starting implicit crosscheck backup at 03-NOV-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 device type=DISK
Crosschecked 22 objects
Finished implicit crosscheck backup at 03-NOV-09
Starting implicit crosscheck copy at 03-NOV-09
using channel ORA_DISK_1
Crosschecked 1 objects
Finished implicit crosscheck copy at 03-NOV-09
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: +disk02/ORCL/AUTOBACKUP/2009_11_03/s_701977521.358.701977527
using channel ORA_DISK_1
skipping datafile 4; already restored to file +DISK01/orcl/datafile/users01.dbf
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DISK01/orcl/datafile/system.267.696964505
channel ORA_DISK_1: restoring datafile 00002 to +DISK01/orcl/datafile/sysaux.266.696964509
channel ORA_DISK_1: restoring datafile 00003 to +DISK01/orcl/datafile/undotbs1.265.701210003
channel ORA_DISK_1: restoring datafile 00005 to +DISK01/orcl/datafile/example.259.696964945
channel ORA_DISK_1: restoring datafile 00006 to +DISK01/orcl/datafile/tbs1.256.697633229
channel ORA_DISK_1: restoring datafile 00007 to +DISK01/orcl/datafile/tbs_fda.257.701208791
channel ORA_DISK_1: reading from backup piece /oracle/RMAN/0sktekgc_1_1
channel ORA_DISK_1: piece handle=/oracle/RMAN/0sktekgc_1_1 tag=TAG20091103T173819
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:03:48
Finished restore at 03-NOV-09
Starting recover at 03-NOV-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DISK01/orcl/datafile/system.272.701988391
destination for restore of datafile 00002: +DISK01/orcl/datafile/sysaux.273.701988393
destination for restore of datafile 00003: +DISK01/orcl/datafile/undotbs1.276.701988395
destination for restore of datafile 00004: +DISK01/orcl/datafile/users01.dbf
destination for restore of datafile 00005: +DISK01/orcl/datafile/example.275.701988395
destination for restore of datafile 00006: +DISK01/orcl/datafile/tbs1.274.701988395
destination for restore of datafile 00007: +DISK01/orcl/datafile/tbs_fda.277.701988395
channel ORA_DISK_1: reading from backup piece /oracle/RMAN/0uktekpi_1_1
channel ORA_DISK_1: piece handle=/oracle/RMAN/0uktekpi_1_1 tag=TAG20091103T174313
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished recover at 03-NOV-09
database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync completeJust fun with RMAN (If A database has backup)... ;)Written By: Surachart Opun http://surachartopun.com
Categories: DBA Blogs
Learn "NOLOGGING" with Tablespace "force logging" mode
After I learned, with...
If the database, or that tablespace, is in "force logging" mode, the nologging will not work.Check Database is Archive Mode:
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 101
Next log sequence to archive 103
Current log sequence 103Check REDO Size with "create table" logging:
SQL> @redo-new
OLD_VALUE
----------
1860
SQL> create table T_LOGGING tablespace TBS1 as select * from dba_objects;
Table created.
SQL> @redo-diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 1860) OLD_VALUE
OLD_VALUE
----------
8622128Check REDO Size with "create table" nologging:
SQL> @redo-new
OLD_VALUE
----------
784
SQL> create table T_NOLOGGING nologging tablespace TBS1 as select * from dba_objects;
Table created.
SQL> @redo-diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 784) OLD_VALUE
OLD_VALUE
----------
105448Check REDO Size with "create table" nologging on Tablespace "force logging" mode:
SQL> select tablespace_name, force_logging from dba_tablespaces where tablespace_name='TBS1';
TABLESPACE_NAME FOR
------------------------------ ---
TBS1 NO
SQL> alter tablespace TBS1 force logging;
Tablespace altered.
SQL> select tablespace_name, force_logging from dba_tablespaces where tablespace_name='TBS1';
TABLESPACE_NAME FOR
------------------------------ ---
TBS1 YES
SQL> @redo-new
OLD_VALUE
----------
788
SQL> create table T_NOLOGGING_F nologging tablespace TBS1 as select * from dba_objects;
Table created.
SQL> @redo-diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 788) OLD_VALUE
OLD_VALUE
----------
8640936That's right, NOLOGGING isn't work on database or tablespace in "force logging" mode.
redo-new.sql:
column OLD_VALUE new_value OLD_VALUE
select value OLD_VALUE
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';redo-diff.sql:
select (value - &OLD_VALUE) OLD_VALUE
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';Written By: Surachart Opun http://surachartopun.com
If the database, or that tablespace, is in "force logging" mode, the nologging will not work.Check Database is Archive Mode:
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 101
Next log sequence to archive 103
Current log sequence 103Check REDO Size with "create table" logging:
SQL> @redo-new
OLD_VALUE
----------
1860
SQL> create table T_LOGGING tablespace TBS1 as select * from dba_objects;
Table created.
SQL> @redo-diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 1860) OLD_VALUE
OLD_VALUE
----------
8622128Check REDO Size with "create table" nologging:
SQL> @redo-new
OLD_VALUE
----------
784
SQL> create table T_NOLOGGING nologging tablespace TBS1 as select * from dba_objects;
Table created.
SQL> @redo-diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 784) OLD_VALUE
OLD_VALUE
----------
105448Check REDO Size with "create table" nologging on Tablespace "force logging" mode:
SQL> select tablespace_name, force_logging from dba_tablespaces where tablespace_name='TBS1';
TABLESPACE_NAME FOR
------------------------------ ---
TBS1 NO
SQL> alter tablespace TBS1 force logging;
Tablespace altered.
SQL> select tablespace_name, force_logging from dba_tablespaces where tablespace_name='TBS1';
TABLESPACE_NAME FOR
------------------------------ ---
TBS1 YES
SQL> @redo-new
OLD_VALUE
----------
788
SQL> create table T_NOLOGGING_F nologging tablespace TBS1 as select * from dba_objects;
Table created.
SQL> @redo-diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 788) OLD_VALUE
OLD_VALUE
----------
8640936That's right, NOLOGGING isn't work on database or tablespace in "force logging" mode.
redo-new.sql:
column OLD_VALUE new_value OLD_VALUE
select value OLD_VALUE
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';redo-diff.sql:
select (value - &OLD_VALUE) OLD_VALUE
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';Written By: Surachart Opun http://surachartopun.com
Categories: DBA Blogs
Learn "NOLOGGING" from forums
I often learn something(oracle) and find idea from OTN Forums.
And This Topic(Wait Events "log file parallel write" / "log file sync" during CREATE INDEX), It wrote about nologging option, “log file sync” and “log file parallel write” waits, and it's replied by Jonathan Lewis and Uwe Hesse.
Jonathan Lewis noted 2 points with "create index" nologging option:it's "only" an index, so you could always rebuild it in the event of media corruption, but if you had lots of indexes created nologging this might cause an unreasonable delay before the system was usable again - so you should decide on a fallback option, such as taking a new backup of the tablespace as soon as all the nologging operatons had completed.
If the database, or that tablespace, is in "force logging" mode, the nologging will not work.
Written By: Surachart Opun http://surachartopun.com
And This Topic(Wait Events "log file parallel write" / "log file sync" during CREATE INDEX), It wrote about nologging option, “log file sync” and “log file parallel write” waits, and it's replied by Jonathan Lewis and Uwe Hesse.
Jonathan Lewis noted 2 points with "create index" nologging option:it's "only" an index, so you could always rebuild it in the event of media corruption, but if you had lots of indexes created nologging this might cause an unreasonable delay before the system was usable again - so you should decide on a fallback option, such as taking a new backup of the tablespace as soon as all the nologging operatons had completed.
If the database, or that tablespace, is in "force logging" mode, the nologging will not work.
Written By: Surachart Opun http://surachartopun.com
Categories: DBA Blogs
MOVING DATAFILE IN ASM BY ASMCMD(cp)
After I wrote about Moving Datafile(system) from one ASM Diskgroup to Another, From example I used RMAN to move datafile. I read ocpdba's comment and curious Moving Datafile in ASM by asmcmd(cp). then start to test..
SQL> select name from v$asm_diskgroup;
NAME
------------------------------
DISK01
DISK02$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production
connected to target database: ORCL (DBID=1224745511)
RMAN> REPORT SCHEMA;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
4 5 USERS *** +DISK02/orcl/datafile/users.336.701196307This test, I want to move datafile from +DISK02 to +DISK01
.. Bring datafile offline
RMAN> SQL 'ALTER DATABASE DATAFILE 4 OFFLINE';
sql statement: ALTER DATABASE DATAFILE 4 OFFLINEAfter bring DATAFILE offline... then copy datafile by asmcmd
$ asmcmd -p
ASMCMD [+] > cp +DISK02/orcl/datafile/users.336.701196307 +DISK01/orcl/datafile/
copying +DISK02/orcl/datafile/users.336.701196307 -> +DISK01/orcl/datafile/users.336.701196307
ASMCMD-08016: copy source->'+DISK02/orcl/datafile/users.336.701196307' and target->'+DISK01/orcl/datafile/users.336.701196307' failed
ORA-19505: failed to identify file "+DISK01/orcl/datafile/users.336.701196307"
ORA-17502: ksfdcre:4 Failed to create file +DISK01/orcl/datafile/users.336.701196307
ORA-15046: ASM file name '+DISK01/orcl/datafile/users.336.701196307' is not in single-file creation form
ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 397
ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)Can't copy, so copy to new file name
ASMCMD [+] > cp +DISK02/orcl/datafile/users.336.701196307 +DISK01/orcl/datafile/users01.dbf
copying +DISK02/orcl/datafile/users.336.701196307 -> +DISK01/orcl/datafile/users01.dbf
ASMCMD [+] > ls -la +DISK02/orcl/datafile/users.336.701196307
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE OCT 25 17:00:00 Y none => users.336.701196307ASMCMD [+] > ls -la +DISK01/orcl/datafile/users01.dbf
Type Redund Striped Time Sys Name
N users01.dbf => +DISK01/ASM/DATAFILE/users01.dbf.264.701198411ASMCMD [+] > ls -la +DISK01/ASM/DATAFILE/users01.dbf.264.701198411
Type Redund Striped Time Sys Name
DATAFILE MIRROR COARSE OCT 25 17:00:00 Y +DISK01/ORCL/DATAFILE/users01.dbf => users01.dbf.264.701198411After copied datafile... then rename file in database
SQL> ALTER DATABASE RENAME FILE '+DISK02/orcl/datafile/users.336.701196307' TO '+DISK01/ORCL/DATAFILE/users01.dbf';
Database altered.Check in alert log...
Sun Oct 25 17:24:55 2009
ALTER DATABASE RENAME FILE '+DISK02/orcl/datafile/users.336.701196307' TO '+DISK01/ORCL/DATAFILE/users01.dbf'
Deleted Oracle managed file +DISK02/orcl/datafile/users.336.701196307
Completed: ALTER DATABASE RENAME FILE '+DISK02/orcl/datafile/users.336.701196307' TO '+DISK01/ORCL/DATAFILE/users01.dbf'
"ALTER DATABASE RENAME FILE" with ASM(OMF), that delete old datafile.
After renamed datafile in Database, then check + recover + bring datafile online.
RMAN> REPORT SCHEMA;
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
4 5 USERS *** +DISK01/orcl/datafile/users01.dbfRMAN> RECOVER DATAFILE 4;
Starting recover at 25-OCT-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=157 instance=orcl1 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:02
Finished recover at 25-OCT-09
RMAN> SQL 'ALTER DATABASE DATAFILE 4 ONLINE';
sql statement: ALTER DATABASE DATAFILE 4 ONLINERMAN> REPORT SCHEMA;
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
4 5 USERS *** +DISK01/orcl/datafile/users01.dbfThat's a good way... but I still interested about file (name, type) in ASM, after copied ;)Written By: Surachart Opun http://surachartopun.com
SQL> select name from v$asm_diskgroup;
NAME
------------------------------
DISK01
DISK02$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production
connected to target database: ORCL (DBID=1224745511)
RMAN> REPORT SCHEMA;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
4 5 USERS *** +DISK02/orcl/datafile/users.336.701196307This test, I want to move datafile from +DISK02 to +DISK01
.. Bring datafile offline
RMAN> SQL 'ALTER DATABASE DATAFILE 4 OFFLINE';
sql statement: ALTER DATABASE DATAFILE 4 OFFLINEAfter bring DATAFILE offline... then copy datafile by asmcmd
$ asmcmd -p
ASMCMD [+] > cp +DISK02/orcl/datafile/users.336.701196307 +DISK01/orcl/datafile/
copying +DISK02/orcl/datafile/users.336.701196307 -> +DISK01/orcl/datafile/users.336.701196307
ASMCMD-08016: copy source->'+DISK02/orcl/datafile/users.336.701196307' and target->'+DISK01/orcl/datafile/users.336.701196307' failed
ORA-19505: failed to identify file "+DISK01/orcl/datafile/users.336.701196307"
ORA-17502: ksfdcre:4 Failed to create file +DISK01/orcl/datafile/users.336.701196307
ORA-15046: ASM file name '+DISK01/orcl/datafile/users.336.701196307' is not in single-file creation form
ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 397
ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)Can't copy, so copy to new file name
ASMCMD [+] > cp +DISK02/orcl/datafile/users.336.701196307 +DISK01/orcl/datafile/users01.dbf
copying +DISK02/orcl/datafile/users.336.701196307 -> +DISK01/orcl/datafile/users01.dbf
ASMCMD [+] > ls -la +DISK02/orcl/datafile/users.336.701196307
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE OCT 25 17:00:00 Y none => users.336.701196307ASMCMD [+] > ls -la +DISK01/orcl/datafile/users01.dbf
Type Redund Striped Time Sys Name
N users01.dbf => +DISK01/ASM/DATAFILE/users01.dbf.264.701198411ASMCMD [+] > ls -la +DISK01/ASM/DATAFILE/users01.dbf.264.701198411
Type Redund Striped Time Sys Name
DATAFILE MIRROR COARSE OCT 25 17:00:00 Y +DISK01/ORCL/DATAFILE/users01.dbf => users01.dbf.264.701198411After copied datafile... then rename file in database
SQL> ALTER DATABASE RENAME FILE '+DISK02/orcl/datafile/users.336.701196307' TO '+DISK01/ORCL/DATAFILE/users01.dbf';
Database altered.Check in alert log...
Sun Oct 25 17:24:55 2009
ALTER DATABASE RENAME FILE '+DISK02/orcl/datafile/users.336.701196307' TO '+DISK01/ORCL/DATAFILE/users01.dbf'
Deleted Oracle managed file +DISK02/orcl/datafile/users.336.701196307
Completed: ALTER DATABASE RENAME FILE '+DISK02/orcl/datafile/users.336.701196307' TO '+DISK01/ORCL/DATAFILE/users01.dbf'
"ALTER DATABASE RENAME FILE" with ASM(OMF), that delete old datafile.
After renamed datafile in Database, then check + recover + bring datafile online.
RMAN> REPORT SCHEMA;
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
4 5 USERS *** +DISK01/orcl/datafile/users01.dbfRMAN> RECOVER DATAFILE 4;
Starting recover at 25-OCT-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=157 instance=orcl1 device type=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:02
Finished recover at 25-OCT-09
RMAN> SQL 'ALTER DATABASE DATAFILE 4 ONLINE';
sql statement: ALTER DATABASE DATAFILE 4 ONLINERMAN> REPORT SCHEMA;
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
4 5 USERS *** +DISK01/orcl/datafile/users01.dbfThat's a good way... but I still interested about file (name, type) in ASM, after copied ;)Written By: Surachart Opun http://surachartopun.com
Categories: DBA Blogs
Shared Memory Tuning: startup database - ORA-27102: out of memory Linux-X86_64 Error: 28: No space left on device
SQL> startupORA-27102: out of memoryLinux-x86_64 Error: 28: No space left on device
I have used RHEL X86_64 + memory (32GB) and setted sga_max_size=20G + sga_target=20G and ...$ cat /proc/sys/kernel/shmmax26843545600What wrong with my kernel tuning... So, I checked metalink (301830.1) and some recommend from RHELThey told; set SHMALL to the total amount of physical RAM divided by page size.SHMALL =>Total amount of shared memory available (bytes or pages)then:Check Page Size:$ getconf PAGE_SIZE4096Determine the system wide maximum number of shared memory pages:$ cat /proc/sys/kernel/shmall2097152My system 64bits with memory 32GB, then 1024 * 1024 * 1024 * 32 / 4096 = 8388608So, change kernel.shmall = 8388608$ su - root # echo 8388608 > /proc/sys/kernel/shmallOr modify /etc/sysctl.conf file:kernel.shmall=8388608and # sysctl -pAfter changed... check and startup database again:$ cat /proc/sys/kernel/shmall8388608SQL> startup...--- NO ERROR ---By the way, I read Tuning Red Hat Enterprise Linux: Oracle and Oracle RACFrom this idea with memory 32GBmem=$(free|grep Mem|awk '{print$2}')totmem=$(echo "$mem*1024"|bc)huge=$(grep Hugepagesize /proc/meminfo|awk '{print $2}')max=$(echo "$totmem*75/100"|bc)all=$(echo "$max/$huge"|bc)echo "kernel.shmmax = $max" echo "kernel.shmall = $all" Result:kernel.shmmax = 25213120512, kernel.shmall = 12311094
However, This case "ORA-27102: out of memory Linux-X86_64 Error: 28: No space left on device" , solved by set SHMALL = MemTotal(byte)/PAGE_SIZE and ...I hope to hear your idea about kernel tuning with Oracle Database.
Written By: Surachart Opun http://surachartopun.com
I have used RHEL X86_64 + memory (32GB) and setted sga_max_size=20G + sga_target=20G and ...$ cat /proc/sys/kernel/shmmax26843545600What wrong with my kernel tuning... So, I checked metalink (301830.1) and some recommend from RHELThey told; set SHMALL to the total amount of physical RAM divided by page size.SHMALL =>Total amount of shared memory available (bytes or pages)then:Check Page Size:$ getconf PAGE_SIZE4096Determine the system wide maximum number of shared memory pages:$ cat /proc/sys/kernel/shmall2097152My system 64bits with memory 32GB, then 1024 * 1024 * 1024 * 32 / 4096 = 8388608So, change kernel.shmall = 8388608$ su - root # echo 8388608 > /proc/sys/kernel/shmallOr modify /etc/sysctl.conf file:kernel.shmall=8388608and # sysctl -pAfter changed... check and startup database again:$ cat /proc/sys/kernel/shmall8388608SQL> startup...--- NO ERROR ---By the way, I read Tuning Red Hat Enterprise Linux: Oracle and Oracle RACFrom this idea with memory 32GBmem=$(free|grep Mem|awk '{print$2}')totmem=$(echo "$mem*1024"|bc)huge=$(grep Hugepagesize /proc/meminfo|awk '{print $2}')max=$(echo "$totmem*75/100"|bc)all=$(echo "$max/$huge"|bc)echo "kernel.shmmax = $max" echo "kernel.shmall = $all" Result:kernel.shmmax = 25213120512, kernel.shmall = 12311094
However, This case "ORA-27102: out of memory Linux-X86_64 Error: 28: No space left on device" , solved by set SHMALL = MemTotal(byte)/PAGE_SIZE and ...I hope to hear your idea about kernel tuning with Oracle Database.
Written By: Surachart Opun http://surachartopun.com
Categories: DBA Blogs
Moving datafile (system) from one ASM Diskgroup to Another
We have to move database file to NEW diskgroup... In case, we have to move system + sysaux database files as well. With Oracle ASM, when we need to move database file from one diskgroup to another. we have to use "rman" to help.
About moving system + sysaux database files, we need database (mount)SQL> select file_name, FILE_ID from dba_data_files where file_name like '%sys%';
FILE_NAME FILE_ID--------------------------------------------------- --------+OLD/db/datafile/sysaux.260.699468081 3+OLD/db/datafile/system.259.699468079 1SQL> SELECT name FROM v$asm_diskgroup;
NAME--------------------OLDNEWWe need to move database files to NEW diskgroupSQL> shutdown immediate;SQL> startup mount;Connect target database by "rman" command-line :$ rman target /connected to target database: DB (DBID=1043389676)RMAN> REPORT SCHEMA;
File Size(MB) Tablespace RB segs Datafile Name---- -------- -------------------- ------- ------------------------1 560 SYSTEM *** +OLD/db/datafile/system.259.6994680793 500 SYSAUX *** +OLD/db/datafile/sysaux.260.699468081Backup AS COPY "database files" to NEW diskgroup, then SWITCH DATAFILE to use NEW diskgroupRMAN> run {BACKUP AS COPY DATAFILE 1 FORMAT "+NEW";BACKUP AS COPY DATAFILE 3 FORMAT "+NEW";SWITCH DATAFILE "+OLD/db/datafile/sysaux.260.699468081" TO COPY;SWITCH DATAFILE "+OLD/db/datafile/system.259.699468079" TO COPY;}Open database...SQL> alter database open;SQL> select file_name, FILE_ID from dba_data_files where file_name like '%sys%';
FILE_NAME FILE_ID--------------------------------------------------- --------+NEW/db/datafile/sysaux.291.700906921 3+NEW/db/datafile/system.294.700906903 1RMAN> REPORT SCHEMA;
File Size(MB) Tablespace RB segs Datafile Name---- -------- -------------------- ------- ------------------------1 560 SYSTEM *** +NEW/db/datafile/system.294.7009069033 500 SYSAUX *** +NEW/db/datafile/sysaux.291.700906921After make sure...(no error and database files used in NEW diskgroup), then DELETE old datafilecopy(original)...RMAN> run {DELETE DATAFILECOPY "+OLD/db/datafile/sysaux.260.699468081";DELETE DATAFILECOPY "+OLD/db/datafile/system.259.699468079";}reference: http://download.oracle.com/docs/cd/B28359_01/backup.111/b28270/rcmasmmi.htm
If not "SYSTEM" database file. we can move database file while database open...SQL> select file_name, FILE_ID from dba_data_files where file_name like '%users%';
FILE_NAME FILE_ID------------------------------------------------------------------ --------+OLD/db/datafile/users.258.699468081 4SQL> SELECT name FROM v$asm_diskgroup;
NAME--------------------OLDNEWWe need to move database file (4) to NEW diskgroupConnect target database by "rman" command-line $ rman target /connected to target database: DB (DBID=1043389676)Check...RMAN> REPORT SCHEMA;
File Size(MB) Tablespace RB segs Datafile Name---- -------- -------------------- ------- ------------------------4 5 USERS *** +OLD/db/datafile/users.258.699468081Bring database file to offline RMAN> SQL "ALTER DATABASE DATAFILE ' ' +OLD/db/datafile/users.258.699468081 ' ' OFFLINE";orRMAN> SQL 'ALTER DATABASE DATAFILE 4 OFFLINE';Backup as copy database file to NEW diskgroupRMAN> BACKUP AS COPY DATAFILE "+OLD/db/datafile/users.258.699468081" FORMAT "+NEW";or RMAN> BACKUP AS COPY DATAFILE 4 FORMAT "+NEW";Switch database file to NEW diskgroupRMAN> SWITCH DATAFILE "+OLD/db/datafile/users.258.699468081" TO COPY;and we need "RECOVER"...RMAN> RECOVER DATAFILE 4;Bring database file to online:RMAN> SQL 'ALTER DATABASE DATAFILE 4 ONLINE';Check...RMAN> REPORT SCHEMA;File Size(MB) Tablespace RB segs Datafile Name---- -------- -------------------- ------- ------------------------4 5 USERS *** +NEW/db/datafile/users.257.700906939SQL> select file_name, FILE_ID from dba_data_files where file_name like '%users%';
FILE_NAME FILE_ID------------------------------------------------------------------ --------+NEW/db/datafile/users.257.700906939 4After Make sure... DELETE old datafilecopy(original):RMAN> DELETE DATAFILECOPY "+OLD/db/datafile/users.258.699468081";... Written By: Surachart Opun http://surachartopun.com
About moving system + sysaux database files, we need database (mount)SQL> select file_name, FILE_ID from dba_data_files where file_name like '%sys%';
FILE_NAME FILE_ID--------------------------------------------------- --------+OLD/db/datafile/sysaux.260.699468081 3+OLD/db/datafile/system.259.699468079 1SQL> SELECT name FROM v$asm_diskgroup;
NAME--------------------OLDNEWWe need to move database files to NEW diskgroupSQL> shutdown immediate;SQL> startup mount;Connect target database by "rman" command-line :$ rman target /connected to target database: DB (DBID=1043389676)RMAN> REPORT SCHEMA;
File Size(MB) Tablespace RB segs Datafile Name---- -------- -------------------- ------- ------------------------1 560 SYSTEM *** +OLD/db/datafile/system.259.6994680793 500 SYSAUX *** +OLD/db/datafile/sysaux.260.699468081Backup AS COPY "database files" to NEW diskgroup, then SWITCH DATAFILE to use NEW diskgroupRMAN> run {BACKUP AS COPY DATAFILE 1 FORMAT "+NEW";BACKUP AS COPY DATAFILE 3 FORMAT "+NEW";SWITCH DATAFILE "+OLD/db/datafile/sysaux.260.699468081" TO COPY;SWITCH DATAFILE "+OLD/db/datafile/system.259.699468079" TO COPY;}Open database...SQL> alter database open;SQL> select file_name, FILE_ID from dba_data_files where file_name like '%sys%';
FILE_NAME FILE_ID--------------------------------------------------- --------+NEW/db/datafile/sysaux.291.700906921 3+NEW/db/datafile/system.294.700906903 1RMAN> REPORT SCHEMA;
File Size(MB) Tablespace RB segs Datafile Name---- -------- -------------------- ------- ------------------------1 560 SYSTEM *** +NEW/db/datafile/system.294.7009069033 500 SYSAUX *** +NEW/db/datafile/sysaux.291.700906921After make sure...(no error and database files used in NEW diskgroup), then DELETE old datafilecopy(original)...RMAN> run {DELETE DATAFILECOPY "+OLD/db/datafile/sysaux.260.699468081";DELETE DATAFILECOPY "+OLD/db/datafile/system.259.699468079";}reference: http://download.oracle.com/docs/cd/B28359_01/backup.111/b28270/rcmasmmi.htm
If not "SYSTEM" database file. we can move database file while database open...SQL> select file_name, FILE_ID from dba_data_files where file_name like '%users%';
FILE_NAME FILE_ID------------------------------------------------------------------ --------+OLD/db/datafile/users.258.699468081 4SQL> SELECT name FROM v$asm_diskgroup;
NAME--------------------OLDNEWWe need to move database file (4) to NEW diskgroupConnect target database by "rman" command-line $ rman target /connected to target database: DB (DBID=1043389676)Check...RMAN> REPORT SCHEMA;
File Size(MB) Tablespace RB segs Datafile Name---- -------- -------------------- ------- ------------------------4 5 USERS *** +OLD/db/datafile/users.258.699468081Bring database file to offline RMAN> SQL "ALTER DATABASE DATAFILE ' ' +OLD/db/datafile/users.258.699468081 ' ' OFFLINE";orRMAN> SQL 'ALTER DATABASE DATAFILE 4 OFFLINE';Backup as copy database file to NEW diskgroupRMAN> BACKUP AS COPY DATAFILE "+OLD/db/datafile/users.258.699468081" FORMAT "+NEW";or RMAN> BACKUP AS COPY DATAFILE 4 FORMAT "+NEW";Switch database file to NEW diskgroupRMAN> SWITCH DATAFILE "+OLD/db/datafile/users.258.699468081" TO COPY;and we need "RECOVER"...RMAN> RECOVER DATAFILE 4;Bring database file to online:RMAN> SQL 'ALTER DATABASE DATAFILE 4 ONLINE';Check...RMAN> REPORT SCHEMA;File Size(MB) Tablespace RB segs Datafile Name---- -------- -------------------- ------- ------------------------4 5 USERS *** +NEW/db/datafile/users.257.700906939SQL> select file_name, FILE_ID from dba_data_files where file_name like '%users%';
FILE_NAME FILE_ID------------------------------------------------------------------ --------+NEW/db/datafile/users.257.700906939 4After Make sure... DELETE old datafilecopy(original):RMAN> DELETE DATAFILECOPY "+OLD/db/datafile/users.258.699468081";... Written By: Surachart Opun http://surachartopun.com
Categories: DBA Blogs
How to Setup 10gRAC with Solaris (IPMP)
On Solaris, IP network multipathing (IPMP) provides physical interface failure detection and transparent network access failover for a system with multiple interfaces on the same IP link.
On 10gRAC, It's a good question, How to Setup 10gRAC with Solaris (IPMP) ?
Setup Cluster Interconnect (metalink: 368464.1):rac01:- Physical IP : 10.10.10.1- Test IP for ce2 : 10.10.10.11- Test IP for ce3 : 10.10.10.12
rac02:- Physical IP : 10.10.10.2- Test IP for ce2 : 10.10.10.21- Test IP for ce3 : 10.10.10.22
Check & configure...oifcfg getif | grep -i cluster_interconnectIf already configuration... with "cluster_interconnect", then delete with 'oifcfg delif' oifcfg delif [-node <nodename> | -global] [ <if_name> [/ <subnet> ]]$ oifcfg getif | grep -i cluster_interconnect
ce2 10.10.10.0 global cluster_interconnect
ce3 10.10.10.0 global cluster_interconnect$ oifcfg delif -global ce2 $ oifcfg delif -global ce3 set the CLUSTER_INTERCONNECTS parameter in the spfile/init.ora (RDBMS/ASM) to the Physical IP (IPMP)... and check on GV$CLUSTER_INTERCONNECTS as well.rac database:rac1.CLUSTER_INTERCONNECTS=10.10.10.1rac2.CLUSTER_INTERCONNECTS=10.10.10.2
SQL> ALTER SYSTEM SET CLUSTER_INTERCONNECTS = '10.10.10.1' scope=spfile sid='rac1';
SQL>ALTER SYSTEM SET CLUSTER_INTERCONNECTS = '10.10.10.2' scope=spfile sid='rac2';
on ASM:+ASM1.CLUSTER_INTERCONNECTS=10.10.10.1+ASM2.CLUSTER_INTERCONNECTS=10.10.10.2..virtual IP (metalink:283107.1):rac01:- Physical IP : 192.168.10.10- Test IP for ce0 : 192.168.10.11- Test IP for ce1 : 192.168.10.12- Oracle VIP : 192.168.10.1
rac02:- Physical IP : 192.168.10.20- Test IP for ce0 : 192.168.10.21- Test IP for ce1 : 192.168.10.22- Oracle VIP : 192.168.10.2
=> New 10gRAC installation:In vipca (VIP Configuration Assistant, 1 of 2), select all NIC's within the same IPMP group where the VIP should run...
=> Existing 10gRAC installation, use "srvctl" to modify:http://download.oracle.com/docs/cd/B19306_01/rac.102/b14197/srvctladmin.htm#CHDBJBIG# srvctl config nodeapps -n rac01 -aVIP exists.: /rac01-vip/192.168.10.1/255.255.255.0/ce0
# srvctl stop nodeapps -n rac01# srvctl modify nodeapps -n rac01 -A 192.168.10.1/255.255.255.0/ce0\|ce1# srvctl config nodeapps -n rac01 -aVIP exists.: /rac01-vip/192.168.10.1/255.255.255.0/ce0:ce1# srvctl start nodeapps -n rac01# srvctl config nodeapps -n rac02 -aVIP exists.: /rac02-vip/192.168.10.2/255.255.255.0/ce0# srvctl stop nodeapps -n rac02# srvctl modify nodeapps -n rac02 -A 192.168.10.2/255.255.255.0/ce0\|ce1# srvctl config nodeapps -n rac02 -aVIP exists.: /rac02-vip/192.168.10.2/255.255.255.0/ce0:ce1# srvctl start nodeapps -n rac02After modifed... use "srvctl config nodeapps -n nodename -a" to check.Written By: Surachart Opun http://surachartopun.com
On 10gRAC, It's a good question, How to Setup 10gRAC with Solaris (IPMP) ?
Setup Cluster Interconnect (metalink: 368464.1):rac01:- Physical IP : 10.10.10.1- Test IP for ce2 : 10.10.10.11- Test IP for ce3 : 10.10.10.12
rac02:- Physical IP : 10.10.10.2- Test IP for ce2 : 10.10.10.21- Test IP for ce3 : 10.10.10.22
Check & configure...oifcfg getif | grep -i cluster_interconnectIf already configuration... with "cluster_interconnect", then delete with 'oifcfg delif' oifcfg delif [-node <nodename> | -global] [ <if_name> [/ <subnet> ]]$ oifcfg getif | grep -i cluster_interconnect
ce2 10.10.10.0 global cluster_interconnect
ce3 10.10.10.0 global cluster_interconnect$ oifcfg delif -global ce2 $ oifcfg delif -global ce3 set the CLUSTER_INTERCONNECTS parameter in the spfile/init.ora (RDBMS/ASM) to the Physical IP (IPMP)... and check on GV$CLUSTER_INTERCONNECTS as well.rac database:rac1.CLUSTER_INTERCONNECTS=10.10.10.1rac2.CLUSTER_INTERCONNECTS=10.10.10.2
SQL> ALTER SYSTEM SET CLUSTER_INTERCONNECTS = '10.10.10.1' scope=spfile sid='rac1';
SQL>ALTER SYSTEM SET CLUSTER_INTERCONNECTS = '10.10.10.2' scope=spfile sid='rac2';
on ASM:+ASM1.CLUSTER_INTERCONNECTS=10.10.10.1+ASM2.CLUSTER_INTERCONNECTS=10.10.10.2..virtual IP (metalink:283107.1):rac01:- Physical IP : 192.168.10.10- Test IP for ce0 : 192.168.10.11- Test IP for ce1 : 192.168.10.12- Oracle VIP : 192.168.10.1
rac02:- Physical IP : 192.168.10.20- Test IP for ce0 : 192.168.10.21- Test IP for ce1 : 192.168.10.22- Oracle VIP : 192.168.10.2
=> New 10gRAC installation:In vipca (VIP Configuration Assistant, 1 of 2), select all NIC's within the same IPMP group where the VIP should run...
=> Existing 10gRAC installation, use "srvctl" to modify:http://download.oracle.com/docs/cd/B19306_01/rac.102/b14197/srvctladmin.htm#CHDBJBIG# srvctl config nodeapps -n rac01 -aVIP exists.: /rac01-vip/192.168.10.1/255.255.255.0/ce0
# srvctl stop nodeapps -n rac01# srvctl modify nodeapps -n rac01 -A 192.168.10.1/255.255.255.0/ce0\|ce1# srvctl config nodeapps -n rac01 -aVIP exists.: /rac01-vip/192.168.10.1/255.255.255.0/ce0:ce1# srvctl start nodeapps -n rac01# srvctl config nodeapps -n rac02 -aVIP exists.: /rac02-vip/192.168.10.2/255.255.255.0/ce0# srvctl stop nodeapps -n rac02# srvctl modify nodeapps -n rac02 -A 192.168.10.2/255.255.255.0/ce0\|ce1# srvctl config nodeapps -n rac02 -aVIP exists.: /rac02-vip/192.168.10.2/255.255.255.0/ce0:ce1# srvctl start nodeapps -n rac02After modifed... use "srvctl config nodeapps -n nodename -a" to check.Written By: Surachart Opun http://surachartopun.com
Categories: DBA Blogs
Oracle SQL Developer 2.1 Book
Oracle SQL Developer is a graphical tool for database development and free. We can browse database objects, run SQL Statements, SQL scripts and edit/debug PL/SQL Statements.
Oracle SQL Developer 2.1 Early Adopter now available (min JDK 1.6 update11) - 24th September '09
We can check Oracle SQL Developer 2.1: New Features and Feature List.
I mention a book titled "Oracle SQL Developer 2.1" written by Sue Harper (Sue's Blog). Sue wrote an excellent Oracle SQL Developer's guide. "Oracle SQL Developer 2.1" covers SQL Developer fundamentals as well more intermediate and advanced topics, this book uses in-depth explanation and detailed examples to help you get the most out of Oracle SQL Developer.
What we will learn from this book: - Build complex queries based on a number of tables using visual Query Builder - Assess the health of your database, data structure of your application, and data in that application with built-in as well as user-defined reports - Create, compile, and debug PL/SQL code and explore available features to facilitate writing PL/SQL code - Integrate your SQL Developer with open source version control systems CVS and Subversion, which allow checking out of files from a repository, editing, and checking them back in - Enter and execute your SQL, PL/SQL, and SQL*Plus statements with the SQL Worksheet interface - Produce easily replicable scripts that copy and move data from one database instance to another, or from one schema to another - Create advanced database connections using a variety of connection and authentication types available for Oracle as well as non-Oracle databases - Create, review, and update database schema designs with SQL Developer Data Modeler - Augment your environment with features that are specific to your needs by extending your SQL Developer with XML structured user-defined extensions - Monitor and manage your Application Express applications by integrating with SQL Developer - Set up an easy and quick migration environment for your database schema by using the migration repository - Browse and review non-Oracle databases, before using the migration environment to migrate and consolidate databases on the Oracle platform
Reference:
- SQL Developer on OTN - Download
- Installation Guide
- User's Guide - Exchange - SQL Developer Forum- Sue's BlogWritten By: Surachart Opun http://surachartopun.com
Oracle SQL Developer 2.1 Early Adopter now available (min JDK 1.6 update11) - 24th September '09
We can check Oracle SQL Developer 2.1: New Features and Feature List.
I mention a book titled "Oracle SQL Developer 2.1" written by Sue Harper (Sue's Blog). Sue wrote an excellent Oracle SQL Developer's guide. "Oracle SQL Developer 2.1" covers SQL Developer fundamentals as well more intermediate and advanced topics, this book uses in-depth explanation and detailed examples to help you get the most out of Oracle SQL Developer.
What we will learn from this book: - Build complex queries based on a number of tables using visual Query Builder - Assess the health of your database, data structure of your application, and data in that application with built-in as well as user-defined reports - Create, compile, and debug PL/SQL code and explore available features to facilitate writing PL/SQL code - Integrate your SQL Developer with open source version control systems CVS and Subversion, which allow checking out of files from a repository, editing, and checking them back in - Enter and execute your SQL, PL/SQL, and SQL*Plus statements with the SQL Worksheet interface - Produce easily replicable scripts that copy and move data from one database instance to another, or from one schema to another - Create advanced database connections using a variety of connection and authentication types available for Oracle as well as non-Oracle databases - Create, review, and update database schema designs with SQL Developer Data Modeler - Augment your environment with features that are specific to your needs by extending your SQL Developer with XML structured user-defined extensions - Monitor and manage your Application Express applications by integrating with SQL Developer - Set up an easy and quick migration environment for your database schema by using the migration repository - Browse and review non-Oracle databases, before using the migration environment to migrate and consolidate databases on the Oracle platform
Reference:
- SQL Developer on OTN - Download
- Installation Guide
- User's Guide - Exchange - SQL Developer Forum- Sue's BlogWritten By: Surachart Opun http://surachartopun.com
Categories: DBA Blogs
How to create Oracle Service on 11gR2
On 11gR2 "dbca" can not manage oracle services after created database ;)
we can use the DBMS_SERVICE procedures and SRVCTL to create/manage Oracle services. Anyway Oracle recommends using srvctl to manage services, if we use Oracle Clusterware and Oracle Restart (DBMS_SERVICE procedures do not update the CRS attributes).
Example (create service by using srvctl):
$ srvctl add service -d orcl -s service1 -r orcl1
$ lsnrctl services | grep service1
--- Not Found ---
$ ./crsstat service1
HA Resource Target State (Host)
----------- ------ -----
ora.orcl.service1.svc OFFLINE OFFLINE
$ srvctl start service -d orcl -s service1
$ ./crsstat service1
HA Resource Target State (Host)
----------- ------ -----
ora.orcl.service1.svc ONLINE ONLINE on rhel5-test
$ lsnrctl services | grep service1
Service "service1" has 1 instance(s).
Example (create service by using DBMS_SERVICE):
SQL> exec DBMS_SERVICE.CREATE_SERVICE('service2','service2');
PL/SQL procedure successfully completed.
$ ./crsstat service2
HA Resource Target State (Host)
----------- ------ -----
$ lsnrctl services | grep service2
--- Not Found ---
SQL> show parameter service_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string service1
SQL> alter system set service_names='service1,service2';
System altered.
SQL> show parameter service_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string service1,service2
$ lsnrctl services | grep service2
Service "service2" has 1 instance(s).
$ ./crsstat service2
HA Resource Target State (Host)
----------- ------ -----
"DBMS_SERVICE procedures do not update the CRS attributes"... So,we don't see ;)
Reference:
--- begin crsstat begin ---
#!/bin/bash
if [ $# -le 1 ]
then
GREP_KEY=$1
else
echo "Please Check arguments."
echo
echo "./crsstat [Word]"
echo
echo " [Word] use to find word in result "
echo " ./crsstat listen"
exit 0
fi
if [ -z $ORA_CRS_HOME ]
then
ORA_CRS_HOME=$CRS_HOME
if [ ! -d $ORA_CRS_HOME ]
then
echo "Please Check ORA_CRS_HOME Environment ($ORA_CRS_HOME)"
exit 1
fi
fi
AWK=/usr/bin/awk
if [ ! -x $AWK ]
then
AWK=/bin/awk
fi
$AWK \
'BEGIN {printf "%-45s %-10s %-12s\n", "HA Resource", "Target", "State (Host)"; printf "%-45s %-10s %-12s\n", "-----------", "------", "-----";}'
$ORA_CRS_HOME/bin/crs_stat | $AWK \
'BEGIN { FS="="; state = 0; }
$1~/NAME/ && $2~/'$GREP_KEY'/ {appname = $2; state=1;}
state == 0 {next;}
$1~/TARGET/ && state == 1 {apptarget = $2; state=2;}
$1~/STATE/ && state == 2 {appstate = $2; state=3;}
state == 3 {printf "%-45s %-10s %-12s\n", appname, apptarget, appstate; state=0;}'
--- end crsstat --- Written By: Surachart Opun http://surachartopun.com
we can use the DBMS_SERVICE procedures and SRVCTL to create/manage Oracle services. Anyway Oracle recommends using srvctl to manage services, if we use Oracle Clusterware and Oracle Restart (DBMS_SERVICE procedures do not update the CRS attributes).Example (create service by using srvctl):
$ srvctl add service -d orcl -s service1 -r orcl1
$ lsnrctl services | grep service1
--- Not Found ---
$ ./crsstat service1
HA Resource Target State (Host)
----------- ------ -----
ora.orcl.service1.svc OFFLINE OFFLINE
$ srvctl start service -d orcl -s service1
$ ./crsstat service1
HA Resource Target State (Host)
----------- ------ -----
ora.orcl.service1.svc ONLINE ONLINE on rhel5-test
$ lsnrctl services | grep service1
Service "service1" has 1 instance(s).
Example (create service by using DBMS_SERVICE):
SQL> exec DBMS_SERVICE.CREATE_SERVICE('service2','service2');
PL/SQL procedure successfully completed.
$ ./crsstat service2
HA Resource Target State (Host)
----------- ------ -----
$ lsnrctl services | grep service2
--- Not Found ---
SQL> show parameter service_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string service1
SQL> alter system set service_names='service1,service2';
System altered.
SQL> show parameter service_names
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string service1,service2
$ lsnrctl services | grep service2
Service "service2" has 1 instance(s).
$ ./crsstat service2
HA Resource Target State (Host)
----------- ------ -----
"DBMS_SERVICE procedures do not update the CRS attributes"... So,we don't see ;)
Reference:
--- begin crsstat begin ---
#!/bin/bash
if [ $# -le 1 ]
then
GREP_KEY=$1
else
echo "Please Check arguments."
echo
echo "./crsstat [Word]"
echo
echo " [Word] use to find word in result "
echo " ./crsstat listen"
exit 0
fi
if [ -z $ORA_CRS_HOME ]
then
ORA_CRS_HOME=$CRS_HOME
if [ ! -d $ORA_CRS_HOME ]
then
echo "Please Check ORA_CRS_HOME Environment ($ORA_CRS_HOME)"
exit 1
fi
fi
AWK=/usr/bin/awk
if [ ! -x $AWK ]
then
AWK=/bin/awk
fi
$AWK \
'BEGIN {printf "%-45s %-10s %-12s\n", "HA Resource", "Target", "State (Host)"; printf "%-45s %-10s %-12s\n", "-----------", "------", "-----";}'
$ORA_CRS_HOME/bin/crs_stat | $AWK \
'BEGIN { FS="="; state = 0; }
$1~/NAME/ && $2~/'$GREP_KEY'/ {appname = $2; state=1;}
state == 0 {next;}
$1~/TARGET/ && state == 1 {apptarget = $2; state=2;}
$1~/STATE/ && state == 2 {appstate = $2; state=3;}
state == 3 {printf "%-45s %-10s %-12s\n", appname, apptarget, appstate; state=0;}'
--- end crsstat --- Written By: Surachart Opun http://surachartopun.com
Categories: DBA Blogs
Read Oracle SQL Developer Release 2.1
Oracle SQL Developer 2.1 Early Adopter is now available. (24th September '09 ).
Read about New Features.
By the way I'm so happy, when I used it with Timesten 11g. I saw packages/procedures/functions.
Nice ;)Written By: Surachart Opun
http://surachartopun.com
Read about New Features.
By the way I'm so happy, when I used it with Timesten 11g. I saw packages/procedures/functions.
Nice ;)Written By: Surachart Opun
http://surachartopun.com
Categories: DBA Blogs
Learn ORA-00600: internal error code, arguments: [17059]
My Database traced many files with ORA-00600
Errors in file $ORACLE_BASE/admin/db/udump/db1_ora_13255.trc:
ORA-00600: internal error code, arguments: [17059], [0x0D36AD8A8], [], [], [], [], [], []
Checked trace file, I found many sql statements and etc...
.
.
.
KGL recovered in-flux handle for lock 0xd4ee3178
----------------------------------------
SO: 0xd4ee3178, type: 53, owner: 0xdf985d88, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=d4ee3178 handle=0
call pin=(nil) session pin=(nil) hpc=0000 hlc=0000
htl=0xd4ee31f8[0xd4ee31f8,0xd4ee31f8] htb=(nil) ssga=0xaf9cf7e0
user=0 session=0 count=0 flags=[0000] savepoint=0x0
That's a bad thing with oracle bugs, when checked on metalink (138554.1). but still no idea to solve(10.2.0.4 linux x86_64 RAC).
One thing i can do it now... flush buffer_cache and shared_pool.
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
SQL> ALTER SYSTEM FLUSH SHARED_POOL;I know this's not good idea to do... but it helped(... but may error again, again, ...).
However I still check Memory misconfiguration and SQL statements. Anyway this case made me need Oracle Support as well ;)Written By: Surachart Opun http://surachartopun.com
Errors in file $ORACLE_BASE/admin/db/udump/db1_ora_13255.trc:
ORA-00600: internal error code, arguments: [17059], [0x0D36AD8A8], [], [], [], [], [], []
Checked trace file, I found many sql statements and etc...
.
.
.
KGL recovered in-flux handle for lock 0xd4ee3178
----------------------------------------
SO: 0xd4ee3178, type: 53, owner: 0xdf985d88, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=d4ee3178 handle=0
call pin=(nil) session pin=(nil) hpc=0000 hlc=0000
htl=0xd4ee31f8[0xd4ee31f8,0xd4ee31f8] htb=(nil) ssga=0xaf9cf7e0
user=0 session=0 count=0 flags=[0000] savepoint=0x0
That's a bad thing with oracle bugs, when checked on metalink (138554.1). but still no idea to solve(10.2.0.4 linux x86_64 RAC).
One thing i can do it now... flush buffer_cache and shared_pool.
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
SQL> ALTER SYSTEM FLUSH SHARED_POOL;I know this's not good idea to do... but it helped(... but may error again, again, ...).
However I still check Memory misconfiguration and SQL statements. Anyway this case made me need Oracle Support as well ;)Written By: Surachart Opun http://surachartopun.com
Categories: DBA Blogs
Test to use FreeRadius with TimesTen (OCI)
On TimesTen 11.2.1, TimesTen OCI depends on the Oracle client library and the TimesTen ODBC libraries. TimesTen OCI support enables you to run many existing OCI applications with TimesTen in direct mode or client/server mode.
TimesTen Release 11.2.1 OCI is based on Oracle Release 11.1.0.7 OCI and supports the contemporary OCI 8 style APIs.
tns_entry = (DESCRIPTION =(CONNECT_DATA =(SERVICE_NAME = dsn)(SERVER = timesten_direct | timesten_client)))
So, I want to compile freeradius to use OCI connect to TimesTen(testing):
- Setup TimesTen on radius Server by using "Client/Server and Data Manager" components.
# tar xvf timesten112120.linux8664.tar.gz
# cd linux8664
# ./setup.sh
[...]
Please choose an instance name for this installation? [ tt1121 ]
Instance name will be 'tt1121'.
Is this correct? [ yes ] y
Of the three components:
[1] Client/Server and Data Manager
[2] Data Manager Only
[3] Client Only
Which would you like to install? [ 1 ] 1
Where would you like to install the tt1121 instance of TimesTen? [ /opt ] /oracle
Installing into /oracle/TimesTen/tt1121 ...
Creating /oracle/TimesTen/tt1121 ...
Uncompressing ...
[...]I need library only, so stop TimesTen daemon:
# /oracle/TimesTen/tt1121/startup/tt_tt1121 stop
Stopping TimesTen Daemon : [ OK ]- Install freeradius
# cd SRC
# tar zxf freeradius-server-2.1.7.tar.gz
# cd freeradius-server-2.1.7
# ./configure && make && make installI installed freeradius, but no driver. So build driver by using "rlm_sql_oracle"
# cd src/modules/rlm_sql/drivers/rlm_sql_oracle/
# vi Makefile--- Begin Makefile ---
include ../../../../../Make.inc
TARGET = rlm_sql_oracle
SRCS = sql_oracle.c
INSTDIR = /oracle/TimesTen/tt1121
COMMDIR = $(INSTDIR)/quickstart/sample_code/common
TTORACLE_HOME = $(INSTDIR)/ttoracle_home/instantclient_11_1
OCIINCS = $(TTORACLE_HOME)/sdk/include
CC = gcc
PLATCFLAGS = -Os -finline-functions
LDFLAGS =
EXTRALIBS = -lpthread -lm -lrt
INCS = -I$(OCIINCS) -I$(INSTDIR)/include -I$(COMMDIR)
RLM_SQL_CFLAGS = $(INCLTDL) $(PLATCFLAGS) $(INCS)
TTLINK = -L$(INSTDIR)/lib -L$(TTORACLE_HOME) -Wl,-rpath,$(INSTDIR)/lib,-rpath,$(TTORACLE_HOME)
RLM_SQL_LIBS = $(TTLINK) -lclntsh $(EXTRALIBS)
include ../rules.mak
--- End Makefile ---
# make
SRC/freeradius-server-2.1.7/libtool --mode=compile gcc -g -O2 -D_REENTRANT -D_POSIX_PTHREAD_SEMANTICS -Wall -D_GNU_SOURCE -DNDEBUG -I../.. -ISRC/freeradius-server-2.1.7/src/ -ISRC/freeradius-server-2.1.7/libltdl -Os -finline-functions -I/oracle/TimesTen/tt1121/ttoracle_home/instantclient_11_1/sdk/include -I/oracle/TimesTen/tt1121/include -I/oracle/TimesTen/tt1121/quickstart/sample_code/common -c sql_oracle.c
mkdir .libs
gcc -g -O2 -D_REENTRANT -D_POSIX_PTHREAD_SEMANTICS -Wall -D_GNU_SOURCE -DNDEBUG -I../.. -ISRC/freeradius-server-2.1.7/src/ -ISRC/freeradius-server-2.1.7/libltdl -Os -finline-functions -I/oracle/TimesTen/tt1121/ttoracle_home/instantclient_11_1/sdk/include -I/oracle/TimesTen/tt1121/include -I/oracle/TimesTen/tt1121/quickstart/sample_code/common -c sql_oracle.c -fPIC -DPIC -o .libs/sql_oracle.o
[...]
gcc -g -O2 -D_REENTRANT -D_POSIX_PTHREAD_SEMANTICS -Wall -D_GNU_SOURCE -DNDEBUG -I../.. -ISRC/freeradius-server-2.1.7/src/ -ISRC/freeradius-server-2.1.7/libltdl -Os -finline-functions -I/oracle/TimesTen/tt1121/ttoracle_home/instantclient_11_1/sdk/include -I/oracle/TimesTen/tt1121/include -I/oracle/TimesTen/tt1121/quickstart/sample_code/common -c sql_oracle.c -o sql_oracle.o >/dev/null 2>&1
SRC/freeradius-server-2.1.7/libtool --mode=link gcc -release 2.1.7 \
-module -export-dynamic -o rlm_sql_oracle.la \
-rpath /usr/local/lib sql_oracle.lo -L/oracle/TimesTen/tt1121/lib -L/oracle/TimesTen/tt1121/ttoracle_home/instantclient_11_1 -Wl,-rpath,/oracle/TimesTen/tt1121/lib,-rpath,/oracle/TimesTen/tt1121/ttoracle_home/instantclient_11_1 -lclntsh -lpthread -lm -lrt
gcc -shared .libs/sql_oracle.o -L/oracle/TimesTen/tt1121/lib -L/oracle/TimesTen/tt1121/ttoracle_home/instantclient_11_1 -lclntsh -lpthread -lm -lrt -Wl,-rpath -Wl,/oracle/TimesTen/tt1121/lib -Wl,-rpath -Wl,/oracle/TimesTen/tt1121/ttoracle_home/instantclient_11_1 -Wl,-soname -Wl,rlm_sql_oracle-2.1.7.so -o .libs/rlm_sql_oracle-2.1.7.so
(cd .libs && rm -f rlm_sql_oracle.so && ln -s rlm_sql_oracle-2.1.7.so rlm_sql_oracle.so)
ar cru .libs/rlm_sql_oracle.a sql_oracle.o
ranlib .libs/rlm_sql_oracle.a
creating rlm_sql_oracle.la
(cd .libs && rm -f rlm_sql_oracle.la && ln -s ../rlm_sql_oracle.la rlm_sql_orac
# make install
# ls -l /usr/local/lib | grep oracle
lrwxrwxrwx 1 root root 17 Sep 17 15:57 rlm_sql_oracle-2.1.7.la -> rlm_sql_oracle.la
-rwxr-xr-x 1 root root 27880 Sep 17 15:57 rlm_sql_oracle-2.1.7.so
-rw-r--r-- 1 root root 32296 Sep 17 15:57 rlm_sql_oracle.a
-rwxr-xr-x 1 root root 967 Sep 17 15:57 rlm_sql_oracle.la
lrwxrwxrwx 1 root root 23 Sep 17 15:57 rlm_sql_oracle.so -> rlm_sql_oracle-2.1.7.so- Configure freeradius to use "rlm_sql_oracle"
# cd /usr/local/etc/raddb
# vi radiusd.confUncomment in radiusd.conf file:
[...]
$INCLUDE sql.conf
[...]Add "sql" in radiusd.conf file:[...]
instantiate {
sql
}
[...]
- Modify "sql.conf" to connect TimesTen database
# vi sql.conf
In sqlconf file:
database = "oracle"
login = "radius"
password = "password"
radius_db = "(DESCRIPTION=(CONNECT_DATA = (SERVICE_NAME = db01CS)(SERVER = timesten_client)))"
- Modify odbc in /etc/odbc.ini file.
# vi /etc/odbc.iniIn odbc.ini file:
[db01CS]
TTC_SERVER=timesten_server
TTC_SERVER_DSN=db01
- Test to start freeradius
# radiusd -X
[...]
rlm_sql (sql): Driver rlm_sql_oracle (module rlm_sql_oracle) loaded and linked
rlm_sql (sql): Attempting to connect to radius@localhost:/(DESCRIPTION=(CONNECT_DATA = (SERVICE_NAME = db01CS)(SERVER = timesten_client)))
rlm_sql (sql): starting 0
rlm_sql (sql): Attempting to connect rlm_sql_oracle #0
rlm_sql (sql): Connected new DB handle, #0
rlm_sql (sql): starting 1
rlm_sql (sql): Attempting to connect rlm_sql_oracle #1
rlm_sql (sql): Connected new DB handle, #1
rlm_sql (sql): starting 2
rlm_sql (sql): Attempting to connect rlm_sql_oracle #2
rlm_sql (sql): Connected new DB handle, #2
rlm_sql (sql): starting 3
rlm_sql (sql): Attempting to connect rlm_sql_oracle #3
rlm_sql (sql): Connected new DB handle, #3
rlm_sql (sql): starting 4
rlm_sql (sql): Attempting to connect rlm_sql_oracle #4
rlm_sql (sql): Connected new DB handle, #4
}
[...]A freeradius connected to TimesTen server.Written By: Surachart Opun http://surachartopun.com
TimesTen Release 11.2.1 OCI is based on Oracle Release 11.1.0.7 OCI and supports the contemporary OCI 8 style APIs.
tns_entry = (DESCRIPTION =(CONNECT_DATA =(SERVICE_NAME = dsn)(SERVER = timesten_direct | timesten_client)))
So, I want to compile freeradius to use OCI connect to TimesTen(testing):
- Setup TimesTen on radius Server by using "Client/Server and Data Manager" components.
# tar xvf timesten112120.linux8664.tar.gz
# cd linux8664
# ./setup.sh
[...]
Please choose an instance name for this installation? [ tt1121 ]
Instance name will be 'tt1121'.
Is this correct? [ yes ] y
Of the three components:
[1] Client/Server and Data Manager
[2] Data Manager Only
[3] Client Only
Which would you like to install? [ 1 ] 1
Where would you like to install the tt1121 instance of TimesTen? [ /opt ] /oracle
Installing into /oracle/TimesTen/tt1121 ...
Creating /oracle/TimesTen/tt1121 ...
Uncompressing ...
[...]I need library only, so stop TimesTen daemon:
# /oracle/TimesTen/tt1121/startup/tt_tt1121 stop
Stopping TimesTen Daemon : [ OK ]- Install freeradius
# cd SRC
# tar zxf freeradius-server-2.1.7.tar.gz
# cd freeradius-server-2.1.7
# ./configure && make && make installI installed freeradius, but no driver. So build driver by using "rlm_sql_oracle"
# cd src/modules/rlm_sql/drivers/rlm_sql_oracle/
# vi Makefile--- Begin Makefile ---
include ../../../../../Make.inc
TARGET = rlm_sql_oracle
SRCS = sql_oracle.c
INSTDIR = /oracle/TimesTen/tt1121
COMMDIR = $(INSTDIR)/quickstart/sample_code/common
TTORACLE_HOME = $(INSTDIR)/ttoracle_home/instantclient_11_1
OCIINCS = $(TTORACLE_HOME)/sdk/include
CC = gcc
PLATCFLAGS = -Os -finline-functions
LDFLAGS =
EXTRALIBS = -lpthread -lm -lrt
INCS = -I$(OCIINCS) -I$(INSTDIR)/include -I$(COMMDIR)
RLM_SQL_CFLAGS = $(INCLTDL) $(PLATCFLAGS) $(INCS)
TTLINK = -L$(INSTDIR)/lib -L$(TTORACLE_HOME) -Wl,-rpath,$(INSTDIR)/lib,-rpath,$(TTORACLE_HOME)
RLM_SQL_LIBS = $(TTLINK) -lclntsh $(EXTRALIBS)
include ../rules.mak
--- End Makefile ---
# make
SRC/freeradius-server-2.1.7/libtool --mode=compile gcc -g -O2 -D_REENTRANT -D_POSIX_PTHREAD_SEMANTICS -Wall -D_GNU_SOURCE -DNDEBUG -I../.. -ISRC/freeradius-server-2.1.7/src/ -ISRC/freeradius-server-2.1.7/libltdl -Os -finline-functions -I/oracle/TimesTen/tt1121/ttoracle_home/instantclient_11_1/sdk/include -I/oracle/TimesTen/tt1121/include -I/oracle/TimesTen/tt1121/quickstart/sample_code/common -c sql_oracle.c
mkdir .libs
gcc -g -O2 -D_REENTRANT -D_POSIX_PTHREAD_SEMANTICS -Wall -D_GNU_SOURCE -DNDEBUG -I../.. -ISRC/freeradius-server-2.1.7/src/ -ISRC/freeradius-server-2.1.7/libltdl -Os -finline-functions -I/oracle/TimesTen/tt1121/ttoracle_home/instantclient_11_1/sdk/include -I/oracle/TimesTen/tt1121/include -I/oracle/TimesTen/tt1121/quickstart/sample_code/common -c sql_oracle.c -fPIC -DPIC -o .libs/sql_oracle.o
[...]
gcc -g -O2 -D_REENTRANT -D_POSIX_PTHREAD_SEMANTICS -Wall -D_GNU_SOURCE -DNDEBUG -I../.. -ISRC/freeradius-server-2.1.7/src/ -ISRC/freeradius-server-2.1.7/libltdl -Os -finline-functions -I/oracle/TimesTen/tt1121/ttoracle_home/instantclient_11_1/sdk/include -I/oracle/TimesTen/tt1121/include -I/oracle/TimesTen/tt1121/quickstart/sample_code/common -c sql_oracle.c -o sql_oracle.o >/dev/null 2>&1
SRC/freeradius-server-2.1.7/libtool --mode=link gcc -release 2.1.7 \
-module -export-dynamic -o rlm_sql_oracle.la \
-rpath /usr/local/lib sql_oracle.lo -L/oracle/TimesTen/tt1121/lib -L/oracle/TimesTen/tt1121/ttoracle_home/instantclient_11_1 -Wl,-rpath,/oracle/TimesTen/tt1121/lib,-rpath,/oracle/TimesTen/tt1121/ttoracle_home/instantclient_11_1 -lclntsh -lpthread -lm -lrt
gcc -shared .libs/sql_oracle.o -L/oracle/TimesTen/tt1121/lib -L/oracle/TimesTen/tt1121/ttoracle_home/instantclient_11_1 -lclntsh -lpthread -lm -lrt -Wl,-rpath -Wl,/oracle/TimesTen/tt1121/lib -Wl,-rpath -Wl,/oracle/TimesTen/tt1121/ttoracle_home/instantclient_11_1 -Wl,-soname -Wl,rlm_sql_oracle-2.1.7.so -o .libs/rlm_sql_oracle-2.1.7.so
(cd .libs && rm -f rlm_sql_oracle.so && ln -s rlm_sql_oracle-2.1.7.so rlm_sql_oracle.so)
ar cru .libs/rlm_sql_oracle.a sql_oracle.o
ranlib .libs/rlm_sql_oracle.a
creating rlm_sql_oracle.la
(cd .libs && rm -f rlm_sql_oracle.la && ln -s ../rlm_sql_oracle.la rlm_sql_orac
# make install
# ls -l /usr/local/lib | grep oracle
lrwxrwxrwx 1 root root 17 Sep 17 15:57 rlm_sql_oracle-2.1.7.la -> rlm_sql_oracle.la
-rwxr-xr-x 1 root root 27880 Sep 17 15:57 rlm_sql_oracle-2.1.7.so
-rw-r--r-- 1 root root 32296 Sep 17 15:57 rlm_sql_oracle.a
-rwxr-xr-x 1 root root 967 Sep 17 15:57 rlm_sql_oracle.la
lrwxrwxrwx 1 root root 23 Sep 17 15:57 rlm_sql_oracle.so -> rlm_sql_oracle-2.1.7.so- Configure freeradius to use "rlm_sql_oracle"
# cd /usr/local/etc/raddb
# vi radiusd.confUncomment in radiusd.conf file:
[...]
$INCLUDE sql.conf
[...]Add "sql" in radiusd.conf file:[...]
instantiate {
sql
}
[...]
- Modify "sql.conf" to connect TimesTen database
# vi sql.conf
In sqlconf file:
database = "oracle"
login = "radius"
password = "password"
radius_db = "(DESCRIPTION=(CONNECT_DATA = (SERVICE_NAME = db01CS)(SERVER = timesten_client)))"
- Modify odbc in /etc/odbc.ini file.
# vi /etc/odbc.iniIn odbc.ini file:
[db01CS]
TTC_SERVER=timesten_server
TTC_SERVER_DSN=db01
- Test to start freeradius
# radiusd -X
[...]
rlm_sql (sql): Driver rlm_sql_oracle (module rlm_sql_oracle) loaded and linked
rlm_sql (sql): Attempting to connect to radius@localhost:/(DESCRIPTION=(CONNECT_DATA = (SERVICE_NAME = db01CS)(SERVER = timesten_client)))
rlm_sql (sql): starting 0
rlm_sql (sql): Attempting to connect rlm_sql_oracle #0
rlm_sql (sql): Connected new DB handle, #0
rlm_sql (sql): starting 1
rlm_sql (sql): Attempting to connect rlm_sql_oracle #1
rlm_sql (sql): Connected new DB handle, #1
rlm_sql (sql): starting 2
rlm_sql (sql): Attempting to connect rlm_sql_oracle #2
rlm_sql (sql): Connected new DB handle, #2
rlm_sql (sql): starting 3
rlm_sql (sql): Attempting to connect rlm_sql_oracle #3
rlm_sql (sql): Connected new DB handle, #3
rlm_sql (sql): starting 4
rlm_sql (sql): Attempting to connect rlm_sql_oracle #4
rlm_sql (sql): Connected new DB handle, #4
}
[...]A freeradius connected to TimesTen server.Written By: Surachart Opun http://surachartopun.com
Categories: DBA Blogs