ORACLE LISTENER

Protocol (B28317-02/4)

Protocol	Parameter	Description
IPC	PROTOCOL	Specify ipc as the value.	A protocol used by client applications that resides on the same node as the listener to communicate with the database. IPC can provide a faster local connection than TCP/IP.
	KEY	Specify a unique name for the service. Oracle recommends using the service name or the Oracle System Identifier (SID) of the service.
		Example: (PROTOCOL=ipc)(KEY=sales)
TCP/IP	HOST	Specify the host name or IP address of the computer.
	PORT	Specify the listening port number.
		Example:(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521)

lsnrctl

COMMAND	DESCRIPTION
show displaymode
set displaymode compat/normal/verbose/raw
services	information about the database services, instances, and service handlers (dispatchers and dedicated servers) to which the listener forwards client connection requests.
lsnrctl [command] [listener_name]
lsnrctl help
lsnrctl help [command]

Misc

• Allways verify service which status is READY and then at client side, put SERVICE_NAME value in tnsnames.ora

MsSQL (Database Gateway)

1. http://docs.oracle.com/cd/B28359\_01/gateways.111/b31043/conf\_sql.htm#CIHGDGFC
2. Install gateway from database package ../gateways/runInstaller
3. edit $ORACLE_HOME/dg4msql/admin/initdg4msql.ora
   • It is possible to create many files ex.: initdg4msql.ora, initdg4msql1.ora, initdg4msql2.ora for many connections
   • HS params: http://docs.oracle.com/cd/B28359\_01/gateways.111/e10311/apd.htm

     HS_FDS_CONNECT_INFO=[MsSQL_hostname]:1433//[db_name]
     HS_FDS_RECOVERY_ACCOUNT=RECOVER	MsSQL account
     HS_FDS_RECOVERY_PWD=RECOVER	MsSQL account password
     HS_FDS_TRANSACTION_LOG=	MsSQL table name for transaction logs
     HS_FDS_TRACE_LEVEL=OFF/ON/DEBUG

4. MsSQL - create RECOVER login
5. MsSQL - create RECOVER schema
6. MsSQL - create RECOVER user
   • Owned Schemas: RECOVER
   • Database role membership: db_datareader, db_datawriter, db_ddladmin
7. create MsSQL table HS_TRANSACTION_LOG at RECOVER schema, which comprise columns below: (if table name is different it must be set parameter HS_FDS_TRANSACTION_LOG at initdg4msql.ora)with columns:
   • GLOBAL_TRAN_ID char(64) not null
   • TRAN_COMMENT char(255)

   add ALL role for above table to PUBLIC

   For automatization above steps use script $ORACLE_HOME/dg4msql/admin/dg4msql_tx.sql as RECOVER user on MsSQL

   • isql -U[recover_account] -P[recover_account_pass] [-Sserver] -i[dg4msql_tx.sql]
   • osql -U[recover_account] -P[revocer_accout_pass] -d[dbname] -i[script]
   • MsSQL do not accept semicolon at the end of line, at last line put "go"
8. run script for superadmin tranlation views:
   • create view dbo.ALL_CONS_COLUMNS_vw and grant SELECT to PUBLIC
   • create view dbo.ALL_IND_COLUMNS_vw and grant SELECT to PUBLIC
   • create view dbo.USER_CONS_COLUMNS_vw and grant SELECT to PUBLIC
   • create view dbo.USER_IND_COLUMNS_vw and grant SELECT to PUBLIC

   $ORACLE_HOME/dg4msql/admin/dg4msql_cvw.sql isql -U[system_admin_user] -P[system_admin_pass] [-Sserver] [-ddatabase] -e -i dg4msql_cvw.sql

9. edit listener.ora

   SID_LIST_MSSQL=
     (SID_LIST=(SID_DESC=(SID_NAME=[gateway_sid])(ORACLE_HOME=[oracle_home_path])(PROGRAM=dg4msql)))
   MSSQL=
     (ADDRESS_LIST=(ADDRESS=(PROTOCOL=tcp)(HOST=[local_hostname])(PORT=[port#])))
   

10. edit tnsnames.ora
    • for CONNECT_DATA=(SID=[gateway_sid]) # gateway_sid must be the same as listener.ora SID
    • add parameter HS=OK (Oracle Heterogeneous Service)

    [ALIAS]=
     (DESCRIPTION=
       (ADDRESS= (PROTOCOL=TCP)(HOST=[local_hostname])(PORT=[port#]))
       (CONNECT_DATA=(SID=[gateway_sid]))
       (HS=OK))
    

ERROR

ORA-28545

[ symptom ]

ORA-28545: error diagnosed by Net8 when connectiong to an agent error
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from...

[ solution ]

• listener is not active

ORA-28513

[ symptom ]

ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from...

[ solution ]

• check file name $ORACLE_HOME/dg4msql/admin/initdg4…

ERROR

ORA-01150	check listener.ora
ORA-12154	check tnsnames.ora, client does not even try to connect to remote server
Linux Error: 110: Connection timed out dla lsnrctl status	compare /etc/hosts via listener.ora
ORA-12560	for winshit check service at services.msc
ORA-12514 TNS:listener does not currently know of service requested in connect	check lsnrctl status at server side, care about name of services
ORA-12545 client can't connect to server	for cluster check client side /etc/hosts, and put VIP addresses

ORACLE MANAGEMENT

compile invalid objects

exec dbms_utility.compile_schema('[USERNAME]')

desc table from dictionary:

SELECT * FROM dictionary;
SELECT * FROM dict_columns WHERE table name='[table_name]';

desc package:

desc [package_name];

check init parameters valid values:

SELECT * FROM v$parameter_valid_values;

prepare script to rebuild indexes:

SELECT 'ALTER INDEX [user].' || segment_name || ' REBUILD TABLESPACE [ts];' FROM dba_segments WHERE segment_type='INDEX' and owner='[user]';

check tables with tablespaces:

SELECT segment_name,tablespace_name,owner FROM dba_segments WHERE segment_type='TABLE' and owner='[user]' ORDER BY tablespace_name;

prepare script for roleback pending transactions:

SELECT 'EXEC DBMS_TRANSACTION.ROLLBACK_FORCE(''' || local_tran_id || ''');' FROM dba_2pc_pending WHERE upper(state)='PREPARED';

check chars:

SELECT '''''' FROM dual;

add message to alertlog:

EXEC sys.dbms_system.ksdwrt(2, 'My own message');

set date:

ALTER SESSION SET nls_date_format ='yyyy-mm-dd hh24:mi:ss';

restrict access:

ALTER SYSTEM [ENABLE | DISABLE] RESTRICTED SESSION
SELECT logins FROM v$instance;

check client NLS:

sql> @[%NLS_LANG%] - na w$
sql> @[$NLS_LANG] - na nixach

roll sequence:

SELECT [seq_name].nextval FROM dual;

check current session id :

SELECT sid FROM v$session WHERE audsid = sys_context('userenv','sessionid');

check user with process:

SELECT p.addr,s.machine,s.username,p.spid FROM v$process p,v$session s WHERE p.addr=s.paddr AND s.type != 'BACKGROUND' ORDER BY 3;

check current SQL:

SELECT u.sid, substr(u.username,1,12) user_name, s.sql_text FROM v$sql s,v$session u WHERE s.hash_value = u.sql_hash_value AND sql_text not like '%from v$sql s, v$session u%'ORDER BY u.sid;

check data from past:(for rows which were DELETE,INSERT,UPDATE):

SELECT * FROM [table] as of timestamp to timestamp('2008-01-01 10:00:00','YYYY-MM-DD HH24:MI:SS');
SELECT * FROM [table] AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '60' minute;
depend on param UNDO_RETENTION [sec] setting dynamicaly
SELECT tuned_undoretention as time[s] FROM v$undostat;

check timezone in database and OS:

SELECT dbtimezone FROM dual;
SELECT sessiontimezone FROM dual;
SELECT systimestamp FROM dual;
SELECT current_timestamp FROM dual;

check DDL source:

SET LONG 100000
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, ‘PRETTY’, false);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, ‘SEGMENT_ATTRIBUTES’, true);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, ‘STORAGE’, true);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, ‘TABLESPACE’, true);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, ‘CONSTRAINTS’, false);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, ‘REF_CONSTRAINTS’, false);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, ‘CONSTRAINTS_AS_ALTER’, false);
SELECT DBMS_METADATA.GET_DDL (OBJECT_TYPE, OBJECT_NAME, USER) from dual;

SELECT text FROM dba_source WHERE owner=[user] and name=[object_name] and type=[type] ORDER BY line;
SELECT name,text FROM dba_source WHERE owner=[user] and type=[type] and lower(text) like lower('%[text]%');

e.g. check source of tablespace UNDO_TS:

set long 2000
SQL> SELECT dbms_metadata.get_ddl('TABLESPACE','UNDO_TS') from dual;

e.g. check source of user SYSMAN:

SELECT dbms_metadata.get_ddl('USER','SYSMAN') FROM dual;

check long operation progress:

SELECT sofar,totalwork,time_remaining/60 MIN,message FROM v$session_longops where sid=[sid];

remove data:

- with tablespace UNDO:
DELETE FROM [table_name];
commit;
- without tablespace UNDO, reset HWM, without WHERE:
TRUNCATE TABLE [table_name]

check last 10 session from remote hosts:

SELECT * FROM (SELECT a.session_id,b.machine,b.logon_time FROM v$active_session_history a,v$session b WHERE a.session_id=b.sid and upper(b.machine) not like '%[SERVER_NAME]%' GROUP BY a.session_id,b.machine,b.logon_time ORDER BY b.logon_time) WHERE rownum <10;

run script to compile all objects:

[ORACLE_HOME]/rdbms/admin/utlrp.sql
which launch script
[ORACLE_HOME]/rdbms/admin/utlprp.sql with param=0
- param=0 - parallel run equal CPU_COUNT
- param=1 - single run
- parametr=N - N number of parallel run

run SQL from bash:

script.sh

sqlplus /nolog lsls <<EOF
connect / as sysdba
select username from dba_users;
EOF

set large page for winshit (Oracle9i 64-bit for Windows and Oracle10g 32-bit and 64-bit for Windows):

turn off lock_sga in init file
HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/KEY_[home] - ORA_LPENABLE = 1