Thursday, February 14, 2013
Temporary Tablespace Cleanup 11g
alter tablespace temporary shrink space keep 1G;
exec dbms_scheduler.drop_job('tempshrink');
begin
dbms_scheduler.create_job
(job_name => 'tempshrink',
job_type => 'PLSQL_BLOCK',
job_action => 'begin execute immediate ''alter tablespace temporary shrink space keep 1G''; end;',
start_date => '01-APR-12 01:00.00.00 AM ASIA/CALCUTTA',
repeat_interval => 'FREQ=WEEKLY; BYDAY=SUN;',
comments=>'Shrink Temporary Tablespace');
end;
/
exec dbms_scheduler.run_job('tempshrink');
========================
select distinct t1.tablespace_name TB,t2.file_name TempFile_name,t1.tablespace_size/1024/1024 Used_Allocated_size,t1.allocated_space/1024/1024 Allocated_size,t1.free_space/1024/1024 Free_space,t2.Autoextensible,t2.bytes/1024/1024 Used_space, t2.maxbytes/1024/1024 Max_TB_size from dba_temp_free_space t1,dba_temp_files t2 where t1.tablespace_name=t2.tablespace_name ;
select tablespace_size/1024/1024,allocated_space/1024/1024,free_space/1024/1024 from dba_temp_free_space;
select tablespace_name,Autoextensible,bytes/1024/1024,maxbytes/1024/1024 from dba_temp_files;
=================================
create or replace trigger tg_clear_tempTB
after startup
on database
declare
j integer;
pragma autonomous_transaction;
begin
dbms_job.submit (j, 'begin execute immediate ''alter tablespace temporary shrink space keep 5G''; end;');
commit;
end tg_clear_logindetails;
/
job OS audit files maintaince
For Oracle on unix .aud files are created whether you have auditing enabled or not. They record sys operations. If you've got a lot of shell scripts that connect / as sysdba you are going to get a lot of .aud files. (In windows they are written to the event viewer)
They will go to where you set audit_file_dest (aka adump) to be. If you don't set adump the first default value is ORACLE_BASE/admin/ORACLE_SID/adump. If this doesn't exist then they will go in ORACLE_HOME/rdbms/audit
If you didn't know about this they tend to raise their heads when $ORACLE_HOME fills up and you wonder why.
Please be clear, I am talking about the OS .aud files. This process will not touch $aud in your database.
Oracle at 11.2 provide a way to manage these .aud OS files using the audit management package DBMS_AUDIT_MGMT
Here's how:
Initialize DBMS_AUDIT_MGMT
Call just once the initialization procedure INIT_CLEANUP to set up the audit management infrastructure.
BEGIN
DBMS_AUDIT_MGMT.INIT_CLEANUP(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
DEFAULT_CLEANUP_INTERVAL => 24 );
END;
/
Create the Procedure to delete files (over a year old) for a single instance
CREATE OR REPLACE procedure SYS.delete_OSaud_files
is
ThisProc VARCHAR2(30) := 'delete_OSaud_files';
ThisAppErr NUMBER := -20000;
BEGIN
DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
last_archive_time => SYSTIMESTAMP-366);
dbms_audit_mgmt.clean_audit_trail(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
use_last_arch_timestamp => TRUE
);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(ThisProc||' - '||SQLERRM);
ROLLBACK;
RAISE_APPLICATION_ERROR(ThisAppErr, ThisProc);
END;
/
Create the Procedure to delete files (over a year old) for RAC
CREATE OR REPLACE procedure SYS.delete_OSaud_files
is
ThisProc VARCHAR2(30) := 'delete_OSaud_files';
ThisAppErr NUMBER := -20000;
BEGIN
DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
last_archive_time => SYSTIMESTAMP-366,
rac_instance_number => 1);
DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
last_archive_time => SYSTIMESTAMP-366,
rac_instance_number => 2);
dbms_audit_mgmt.clean_audit_trail(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
use_last_arch_timestamp => TRUE);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(ThisProc||' - '||SQLERRM);
ROLLBACK;
RAISE_APPLICATION_ERROR(ThisAppErr, ThisProc);
END;
/
If you don't need to keep a years worth, just change SYSTIMESTAMP-366
Feel free to moan at me about 'when others then' and post a better procedure in the comments - I'll readily admit my plsql is not what it should be and I'm happy to be corrected.
Create a Schedule
(I like this type of thing to run when I'm actually working so I don't get called out of hours if something goes wrong)
Begin
DBMS_SCHEDULER.CREATE_SCHEDULE (
schedule_name => 'DELETE_OSAUD_FILES_SCHED',
repeat_interval =>'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=10;',
comments => 'Delete adump files');
END;
/
Schedule the Job
BEGIN
dbms_scheduler.create_job (
job_name =>'DELETE_OSAUD_FILES_JOB',
job_type =>'STORED_PROCEDURE',
job_action => 'SYS.delete_OSaud_files',
enabled => TRUE,
auto_drop => false,
schedule_name => 'DELETE_OSAUD_FILES_SCHED',
comments => 'Remove aud files from adump');
END;
/
Set up mail notifications
(I like to know when my jobs error)
BEGIN
DBMS_SCHEDULER.set_scheduler_attribute('email_server', 'smtp.mycompany.com:25');
DBMS_SCHEDULER.set_scheduler_attribute('email_sender', 'do_not_reply@mydatabase');
END;
/
Create the events that I want mailing about
to test notifications work first set up events for everything
BEGIN
DBMS_SCHEDULER.add_job_email_notification (
job_name => 'DELETE_OSAUD_FILES_JOB',
recipients => 'me@mycompany.com',
events => 'job_all_events');
END;
/
run your job
BEGIN
dbms_scheduler.run_job (
job_name =>'DELETE_OSAUD_FILES_JOB');
END;
/
You should get an email saying it ran.
But you don't want an 'I ran' mail every day, I only want to know if it's failed so:
BEGIN
DBMS_SCHEDULER.remove_job_email_notification (
job_name => 'DELETE_OSAUD_DATA_JOB',
recipients => 'me@mycompany.com',
events => 'job_succeeded');
END;
/
BEGIN
DBMS_SCHEDULER.remove_job_email_notification (
job_name => 'DELETE_OSAUD_DATA_JOB',
recipients => 'me@mycompany.com',
events => 'job_started');
END;
/
BEGIN
DBMS_SCHEDULER.remove_job_email_notification (
job_name => 'DELETE_OSAUD_DATA_JOB',
recipients => 'me@mycompany.com',
events => 'job_completed');
END;
/
Which is the equivalent of :
BEGIN
DBMS_SCHEDULER.add_job_email_notification (
job_name => 'DELETE_OSAUD_DATA_JOB',
recipients => 'me@mycompany.com',
events => 'job_broken,job_chain_stalled,job_completed,job_disabled,job_failed,
job_over_max_dur,job_sch_lim_reached,job_stopped');
END;
/
So now your .aud files will be kept in check for you.
Create Database Structure / MetaData / scripts using EXPDP
Dont Use PLSQL Developer, it will corrupt code bodies
1) Take Export Full Dump of apx1213 DB using Data Pump
conn sys@apx1213srv as sysdba
CREATE DIRECTORY DPUMP_DIR1 AS 'D:\expdp1213';
GRANT read, write ON DIRECTORY DPUMP_DIR1 TO ldbo;
cmd
expdp ldbo/ldbo@apx1213srv FULL=y directory=dpump_dir1 dumpfile=apx1213.DMP LOGFILE=apx1213.LOG EXCLUDE=STATISTICS
2) Install Oracle 11g
3) Create apx1314 Database (Note: Don't Change oracle default parameters)
4) Create LDBO user using attached script (Note:only used attached, Don't use old createuserldbo.sql)
5) Login into LDBO user and create synonym
create synonym script from last year database apx1213 and run into current year apx1314
conn sys@apx1213srv as sysdba
set pagesize 1000
set linesize 150
set long 1000
col metadata for a145
SPOOL C:\YEP1314\SYNONYMNS1213.SQL
select dbms_metadata.get_ddl('SYNONYM',synonym_name, 'PUBLIC') || '/' metadata
from dba_synonyms
where owner = 'PUBLIC'
and table_owner = 'LDBO'
order by synonym_name;
SPOOL OFF
conn sys@apx1314srv as sysdba
@C:\YEP1314\SYNONYMNS1213.SQL
6) Login into LDBO user and create Database Link
Note: Please change the password and connect string as per client software
CREATE PUBLIC DATABASE LINK "LNK_RAKSHAK"
CONNECT TO "LDBO" IDENTIFIED BY LDBO USING 'APX1314SRV';
CREATE PUBLIC DATABASE LINK "LNK_CCM"
CONNECT TO "LDBO" IDENTIFIED BY LDBO USING 'APX1314SRV';
CREATE DATABASE LINK "LNK_DIGITAL"
CONNECT TO "LDBO" IDENTIFIED BY ldbo USING 'APX1314SRV';
CREATE DATABASE LINK "LNK_PREVIOUSYEARBALANCE"
CONNECT TO "LDBO" IDENTIFIED BY LDBO USING 'APX1213SRV';
CREATE PUBLIC DATABASE LINK "CMLDLINK"
CONNECT TO cmuser IDENTIFIED BY cmuser USING 'CMMASTERSRV';
7) Login into LDBO user and create Sequence
Already exist in Createuserldbo sql
please drop all sequences and recreate them
select 'DROP SEQUENCE ' || Sequence_Name || ';' from dba_Sequences where Sequence_Owner='LDBO';
Create Sequence LDBO.Dematuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Saudauniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Billuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Jobbrokuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Delbrokuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Turnbrokuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Fwdbrokuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Extbrokuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Dpuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Bankuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Payrequniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Riskuniquenumber minvalue 1 maxvalue 99999999999 ;
Create Sequence LDBO.Portuniquenumber minvalue 1 maxvalue 99999999999 ;
Create Sequence LDBO.Sq_Axiscms minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Pmsuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Debarreduniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Bkrecouniquenumber minvalue 50000000 maxvalue 9999999999 start with 50000000 increment by 1 nocache;
Create Sequence LDBO.Sq_Smsuniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Offlinenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Phytodemnumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Ipouniquenumber minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Trademodification minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_SpicePoa minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_Idbicms minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Scripuniquenumber minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_Clientuniquenumber minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_Brokeruniquenumber minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_Nsdlcdsldpuniquenumber minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_Orarupee minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_Rtgssequence minvalue 1 maxvalue 9999999 ;
Create Sequence LDBO.Sq_Bobbentopool minvalue 1 maxvalue 99999999999 ;
Create Sequence LDBO.Sq_Clientcode minvalue 1 maxvalue 99999999999 ;
Create Sequence LDBO.Sq_Familycode minvalue 1 maxvalue 99999999999 ;
Create Sequence LDBO.Sq_NigPortclose Minvalue 1 maxvalue 9999999999 Start With 1 Increment by 1 noCache;
Create Sequence LDBO.Seqclientsequence minvalue 1 maxvalue 99 start with 1 increment by 1;
Create Sequence LDBO.Sq_collateral minvalue 1 maxvalue 9999999999 ;
Create Sequence LDBO.Sq_Nsdlkra minvalue 1 maxvalue 99999999 ;
Create Sequence LDBO.LdOraclebatchid minvalue 1 maxvalue 9999999999 nocache;
Create Sequence LDBO.Sq_Limittokenno minvalue 1 maxvalue 99999999 ;
Create Sequence LDBO.SQ_CDSLXMLUNIQUENUMBER minvalue 10000 maxvalue 99999;
8) Create Data pump Directory expdp1314 and copy apx1213 dump (last year dump file) and Create optable
Note: Run from Database server only
conn sys@apx1314srv as sysdba
host mkdir D:\expdp1314
CREATE DIRECTORY DPUMP_DIR1 AS 'D:\expdp1314';
GRANT read, write ON DIRECTORY DPUMP_DIR1 TO ldbo;
cmd
impdp ldbo/ldbo@apx1314SRV directory=DPUMP_DIR1 dumpfile=apx1213.DMP LOGFILE=apx1314metadata.LOG schemas=LDBO content=metadata_only EXCLUDE=TABLESPACE,DB_LINK,SEQUENCE,STATISTICS,USER,GRANT sqlfile=optable.sql
9) Create splogin,tglogin manually
Already exist in Createuserldbo sql
SELECT owner,object_name FROM Dba_Objects WHERE OBJECT_NAME IN ('SPLOGININFORMATION','TGLOGININFORMATION');
10) Compile Invalid Objects
conn sys@apx1314srv as sysdba
EXEC sys.UTL_RECOMP.recomp_serial;
select object_name from dba_objects where owner='LDBO' and status='INVALID';
select * from SYS.USER_ERRORS ;
11) Match Last and Current Year Database Objects (Run the following command in last and current year database and match the records)
Select Object_Type,Count(*) From User_Objects Group By Object_Type Order By 1;
select COUNT(*) from user_objects where status='INVALID';
Select * From Dba_Synonyms Where Table_Owner='LDBO';
select * from dba_Constraints where owner='LDBO';
SELECT * FROM DBA_DB_LINKS;
SELECT * FROM DBA_DIRECTORIES;
12) Create Users
conn sys@apx1213srv as sysdba
set head off
set pages 0
set long 9999999
spool c:\yep1314\user_script.sql
SELECT DBMS_METADATA.GET_DDL('PROFILE',profile) || '/' DDL
FROM DBA_PROFILES WHERE profile != 'DEFAULT' GROUP BY PROFILE;
UNION ALL
SELECT DBMS_METADATA.GET_DDL('USER', USERNAME) || '/' DDL
FROM DBA_USERS
UNION ALL
SELECT DBMS_METADATA.GET_DDL('ROLE', role) || '/' DDL
FROM dba_roles
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA', USERNAME) || '/' DDL
FROM DBA_USERS where username in (select username from dba_ts_quotas)
UNION ALLs
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS where username in (select grantee from dba_role_privs)
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS where username in (select grantee from dba_sys_privs)
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS where username in (select grantee from dba_tab_privs);
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', GRANTEE) || '/' DDL
FROM DBA_TAB_PRIVS GROUP BY GRANTEE;
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', GRANTEE) || '/' DDL
FROM DBA_ROLE_PRIVS GROUP BY GRANTEE;
spool off;
conn sys@apx1314srv as sysdba
@c:\yep1314\user_script.sql
13) Create Roles using database link
Note: Please check database link (lnk_previousyearbalance) is working or not before running following script
conn sys@apx1314srv as sysdba
BEGIN
FOR cur_rec IN (SELECT role
FROM dba_roles@lnk_previousyearbalance) LOOP
BEGIN
EXECUTE IMMEDIATE 'CREATE ROLE ' || cur_rec.role ;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
/
BEGIN
FOR cur_rec IN (SELECT grantee,privilege
FROM DBA_SYS_PRIVS@lnk_previousyearbalance where grantee not in ('SYS','SYSTEM','SYSMAN','TSMSYS','WMSYS','RECOVERY_CATALOG_OWNER','RESOURCE','OUTLN','ORACLE_OCM','OEM_MONITOR','OEM_ADVISOR','MGMT_USER','IMP_FULL_DATABASE','EXP_FULL_DATABASE','DBA','CONNECT','AQ_ADMINISTRATOR_ROLE','DBSNMP','SCHEDULER_ADMIN')) LOOP
BEGIN
EXECUTE IMMEDIATE ('Grant ' || cur_rec.privilege || ' to ' || cur_rec.grantee );
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
/
BEGIN
FOR cur_rec IN (SELECT grantee,privilege,table_name
FROM dba_tab_privs@lnk_previousyearbalance Where Grantor='LDBO') LOOP
BEGIN
EXECUTE IMMEDIATE 'Grant ' || cur_rec.privilege || ' on ' || cur_rec.table_name || ' to ' || cur_rec.grantee ;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
/
BEGIN
FOR cur_rec IN (SELECT grantee,privilege,table_name,column_name
FROM dba_col_privs@lnk_previousyearbalance Where Grantor='LDBO') LOOP
BEGIN
EXECUTE IMMEDIATE 'Grant '|| cur_rec.PRIVILEGE || '('|| cur_rec.COLUMN_NAME ||') on '|| cur_rec.TABLE_NAME || ' to ' || cur_rec.GRANTEE ;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
/
BEGIN
FOR cur_rec IN (SELECT grantee,granted_role
FROM dba_role_privs@lnk_previousyearbalance Where Grantee!='SYSTEM' and Grantee!='SYS' and Grantee!='DBSNML' and Grantee!='REPADMIN') LOOP
BEGIN
EXECUTE IMMEDIATE 'Grant '|| cur_rec.granted_role || ' to ' || cur_rec.GRANTEE ;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
/
impdp ldbo/ldbo@apx1314SRV directory=DATA_PUMP_DIR dumpfile=apx1213.DMP LOGFILE=apx1314metadata.LOG schemas=LDBO content=metadata_only EXCLUDE=TABLESPACE,DB_LINK,SEQUENCE,STATISTICS,USER,GRANT sqlfile=optable.sql
D:\app\Administrator\product\11.2.0\dbhome_1\BIN\impdp.exe LDBO/LDBO@ALP1314SRV DIRECTORY=DPUMP_DIR1 DUMPFILE=ALP1213.DMP LOGFILE=ALP1314METADATA1.LOG CONTENT=METADATA_ONLY SCHEMAS=LDBO EXCLUDE=TABLESPACE,DB_LINK,SEQUENCE,STATISTICS,USER,GRANT,ROLE,ROLE_GRANT,DIRECTORY,PROCACT_SCHEMA,AUDIT_OBJ sqlfile=optable1.sql
How to create Database structure same as another Database using Export Dump
If
you want to create a Database Skeleton (All tables (without Data), indexes and
other objects) same as another database
Then
You just need the full export dump (apx1213.dmp) of source database
Source
database A: apx1213
Target
Database B: apx1314
1)
Create
database using Database
Configuration Assistant (DBCA)
2)
And import dump (without data) using following
command
imp
ldbo@apx1314srv FILE=D:\apx1213.dmp ROWS=N FULL=Y CONSTRAINTS=Y GRANTS=Y
INDEXES=Y STATISTICS=NONE
Tuesday, December 18, 2012
sqlserver to oracle10g / 11g connection
Accessing SQL Server from Oracle with Database Gateway for ODBC (DG4ODBC)
To connect Oracle to a non-Oracle system through DG4ODBC:
Install and configure the ODBC driver on the machine where DG4ODBC is installed.
D:\app\Administrator\product\11.2.0\dbhome_1\BIN\dg4odbc.exe
D:\oracle\product\10.2.0\db_1\bin\hsodbc.exe
D:\oracle\product\10.2.0\db_1\bin\dg4odbc.exe
for 10g 32 bit listener
(SID_DESC=
(SID_NAME=dg4odbc)
(ORACLE_HOME=D:\oracle\product\10.2.0\db_1)
(PROGRAM=hsodbc)
)
for 11g OR 10G 64 BIT listener
(SID_DESC=
(SID_NAME=dg4odbc)
(ORACLE_HOME=D:\oracle\product\10.2.0\db_1)
(PROGRAM=dg4odbc)
)
Configure Oracle:
Database gateway (init*.ora). initdg4odbc.ora
Database listener (listener.ora).
Network client (tnsnames.ora).
Create a database link with SQL*Plus.
----------odbcad32---------------------
datasource name (DSN) : dg4odbc
description: dg4odbc
sqlserver : 172.168.0.1
database name: mysqlserverdatabase
username: sa(sqlserver user to connect to oracle)
password: sa
test connection successfully
--------------------oracle------
Select * from v$parameter where name like 'global_names%';
alter system set global_names=false scope = both;
create public database link lnk_sqlserver connect to "sa" identified by "sa" using 'dg4odbc';
username password should be double quotes
The db link name is sqlserver. Username and password must be in double quotes, because the username and password are case sensitive in SQL Server. 'DG4ODBC' points to the alias in the tnsnames.ora file that calls the HS subsystem.
rename init<>.ora to initdg4odbc.ora
D:\app\Administrator\product\11.2.0\dbhome_1\hs\admin\initdg4odbc.ora
D:\oracle\product\10.2.0\db_1\hs\admin\initdg4odbc.ora
HS_FDS_CONNECT_INFO = dg4odbc
HS_FDS_TRACE_LEVEL = off
-----------listener-----D:\oracle\product\10.2.0\db_1------10g
(SID_DESC=
(SID_NAME=dg4odbc)
(ORACLE_HOME=D:\oracle\product\10.2.0\db_1)
(PROGRAM=hsodbc)
)
Please check hsodbc, dg4odbc at $oracle_home/bin
-----------listener-----D:\oracle\product\10.2.0\db_1------11g
(SID_DESC=
(SID_NAME=dg4odbc)
(ORACLE_HOME=D:\oracle\product\10.2.0\db_1)
(PROGRAM=dg4odbc)
)
-----------------tnsnames.ora--------D:\oracle\product\10.2.0\db_1-----
Host,SID should be same as listener entry and should be of Oracle (not sql server)
dg4odbc =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.0.1)(PORT=1521))
(CONNECT_DATA=(SID=dg4odbc))
(HS=OK)
)
Now Check the connection
select * from all_catalog@lnk_sqlserver
select * from "systables"@lnk_sqlserver;
INSERT INTO DBO.AMR@LNKASIANTRANSMIT("Header","ClientCode","BaseDeposit","SegmentAccount")
values('03','ksh-001',1,'a');
INSERT INTO DBO.AMR@LNKASIANTRANSMIT(Header,ClientCode,BaseDeposit,SegmentAccount)
values('03','ksh-001',1,'a');
INSERT INTO DBO.AMR@LNKASIANTRANSMIT(Header,ClientCode,BaseDeposit)
values('03','ksh-001','a');
Enclose the column names in double quotes due to case sensitivitiy of the columns
INSERT INTO DBO.AMR@LNKASIANTRANSMIT("Header","ClientCode","BaseDeposit","SegmentAccount")
values('03','ksh-001',1,'a');
SQL> alter package pk_asianauto compile body;
Warning: Package Body altered with compilation errors.
SQL> sho err
Errors for PACKAGE BODY PK_ASIANAUTO:
LINE/COL ERROR
-------- -----------------------------------------------------------------
28/13 PL/SQL: SQL Statement ignored
28/80 PL/SQL: ORA-00904: "SEGMENTACCOUNT": invalid identifier
60/13 PL/SQL: SQL Statement ignored
60/114 PL/SQL: ORA-00904: "ACTION": invalid identifier
92/13 PL/SQL: SQL Statement ignored
92/85 PL/SQL: ORA-00904: "ISPRIMARYDEALER": invalid identifier
126/13 PL/SQL: SQL Statement ignored
127/13 PL/SQL: ORA-00904: "ADHOCCAP": invalid identifier
169/13 PL/SQL: SQL Statement ignored
171/70 PL/SQL: ORA-00904: "FLAG": invalid identifier
204/13 PL/SQL: SQL Statement ignored
LINE/COL ERROR
-------- -----------------------------------------------------------------
204/57 PL/SQL: ORA-00904: "CLIENTCODE": invalid identifier
261/17 PL/SQL: SQL Statement ignored
262/48 PL/SQL: ORA-00904: "DAYSELLAMT": invalid identifier
266/17 PL/SQL: SQL Statement ignored
267/48 PL/SQL: ORA-00904: "DAYSELLAMT": invalid identifier
278/14 PL/SQL: SQL Statement ignored
278/90 PL/SQL: ORA-00904: "SEGMENTACCOUNT": invalid identifier
288/14 PL/SQL: SQL Statement ignored
288/90 PL/SQL: ORA-00904: "SEGMENTACCOUNT": invalid identifier
SQL> INSERT INTO DBO.AMR@LNKASIANTRANSMIT(Header,ClientCode,BaseDeposit,SegmentAccount)
2 values('03','ksh-001','a','a');
INSERT INTO DBO.AMR@LNKASIANTRANSMIT(Header,ClientCode,BaseDeposit,SegmentAccount)
*
ERROR at line 1:
ORA-00904: "SEGMENTACCOUNT": invalid identifier
SQL> INSERT INTO DBO.AMR@LNKASIANTRANSMIT(Header,ClientCode,BaseDeposit)
2 values('03','ksh-001','a');
INSERT INTO DBO.AMR@LNKASIANTRANSMIT(Header,ClientCode,BaseDeposit)
*
ERROR at line 1:
ORA-00904: "BASEDEPOSIT": invalid identifier
SQL> INSERT INTO DBO.AMR@LNKASIANTRANSMIT("Header","ClientCode","BaseDeposit","SegmentAccount")
2 values('03','ksh-001','a','a');
values('03','ksh-001','a','a')
*
ERROR at line 2:
ORA-28534: Heterogeneous Services preprocessing error
-------------
we are trying to insert char into number
--------------------
SQL> desc DBO.AMR@LNKASIANTRANSMIT
Name Null? Type
----------------------------------------- -------- ----------------------------
Header VARCHAR2(2)
ClientCode VARCHAR2(25)
BaseDeposit NUMBER(24,6)
SegmentAccount VARCHAR2(15)
SQL> INSERT INTO DBO.AMR@LNKASIANTRANSMIT("Header","ClientCode","BaseDeposit","SegmentAccount")
2 values('03','ksh-001',1,'a');
1 row created.
SQL> rollback;
Tuesday, November 6, 2012
11g File Watcher
http://docs.oracle.com/cd/E11882_01/server.112/e25494/scheduse005.htm
http://www.oracle-base.com/articles/11g/scheduler-enhancements-11gr2.php
http://awads.net/wp/2011/03/29/did-you-know-about-file-watchers/
A file watcher is a new scheduler object that enables a new type of even-based job that is triggered by the arrival of a file in a specified location. File watchers can be defined to monitor locations on the local server and remote servers, provided they have an agent installed on them.
We want oracle to import all the objects belonging to HR schema automatically whenever a .dmp file arrives in a specified folder on a system.
To implement it we will use filewatcher which is a new scheduler object in oracle 11g. It enables a new type of event-based job that is triggered by the arrival of a file in a specified location. File watchers can be defined to monitor locations on the local server and remote servers, provided they have an agent installed on them.
I am loading a file to a oracle table every day between 8 a.m and 9 a.m. The file may come at any time between 8 a.m to 9 a.m.
As of now, we are manually checking the file availability and then loading the file into oracle table.
My client is asking us to automate this process. My team is wondering if there is any way we can schedule based on file arrival in a specified location.
SELECT file_watcher_name, destination, directory_path, file_name, credential_name FROM user_scheduler_file_watchers;
SELECT file_watcher_name, destination, directory_path, file_name, credential_name FROM dba_scheduler_file_watchers;
SELECT * FROM DBA_scheduler_credentials;
/* Configuration */
-- by default, a destination is checked every 10 mins.
To change this:
CONN / AS SYSDBA
set serveroutput on
declare v varchar2(1000);
begin
DBMS_SCHEDULER.GET_ATTRIBUTE ( 'FILE_WATCHER_SCHEDULE','REPEAT_INTERVAL', v);
DBMS_OUTPUT.PUT_LINE(V);
end;
/
BEGIN
DBMS_SCHEDULER.set_attribute('file_watcher_schedule','repeat_interval','freq=minutely;interval=1');
END;
/
/* Creating File Watcher */
-- create OS credential:
BEGIN
DBMS_SCHEDULER.create_credential(credential_name => 'fw_credential',username => 'Administrator',password => 'adminadmin@123');
END;
/
-- Grant EXECUTE on the credential to the schema that owns the
-- event-based job that the file watcher will start:
GRANT EXECUTE ON fw_credential to LDBO;
-- create file watcher:
BEGIN
DBMS_SCHEDULER.create_file_watcher(
file_watcher_name => 'data_fw',
directory_path => 'd:\ldoutput', -- if '?' = ORACLE_HOME
file_name => '*.dat', -- wildcard supported
credential_name => 'fw_credential',
destination => NULL, -- NULL=local server
enabled => FALSE);
END;
/
-- Grant EXECUTE on the file watcher to any schema that owns an event-based job-- that references the file watcher.
GRANT EXECUTE ON data_fw to LDBO;
-- create a program raised by the file watcher
BEGIN
DBMS_SCHEDULER.create_program(
program_name => 'import_data_prog',
program_type => 'stored_procedure',
program_action => 'import_data_proc',
number_of_arguments => 1,
enabled => FALSE);
END;
/
-- define the metadata argument using the event_message attribute-- the metadata contains info about the file, such as its name:
BEGIN
DBMS_SCHEDULER.define_metadata_argument(
program_name => 'import_data_prog',
metadata_attribute => 'event_message',
argument_position => 1);
END;
/
-- create the defined procedure:-- It must accept an argument of the SCHEDULER_FILE WATCHER_RESULT type
DROP TABLE received_files ;
CREATE TABLE received_files ( fileinfo VARCHAR2(4000), rdate date );
CREATE OR REPLACE PROCEDURE import_data_proc
(p_sfwr SYS.SCHEDULER_FILEWATCHER_RESULT) AS
v_message received_files.fileinfo%type;
BEGIN
v_message := p_sfwr.directory_path || '\' || p_sfwr.actual_file_name || ' (' ||p_sfwr.file_size || ')';
INSERT INTO received_files VALUES (v_message, sysdate);
COMMIT;
END;
/
-- create the job:
BEGIN
DBMS_SCHEDULER.create_job(
job_name => 'import_data_job',
program_name => 'import_data_prog',
event_condition => NULL, -- 'tab.user_data.file_size < 1024'
queue_spec => 'data_fw', -- file watcher name
auto_drop => FALSE,
enabled => FALSE);
END;
/
-- By default, the arrival of new files will be ignored if the job is already running.-- If you need the job to fire for each new arrival, regardless of whether the job isalready-- running or not, set the PARALLEL_INSTANCES attribute for the job to true. The job-- will then be run as a lightweight job:
BEGIN
DBMS_SCHEDULER.set_attribute('import_data_job','parallel_instances',TRUE);
END;
/
-- Enable all the objects:
EXEC DBMS_SCHEDULER.enable('data_fw,import_data_prog,import_data_job');
-- to test:
echo "This is a test" > D:\LDOUTPUT\f1.dat
echo "This is a test too" > D:\LDOUTPUT\f2.dat
echo "Yes another test" > D:\LDOUTPUT\f3.dat
select * from received_files order by rdate desc;
/* Managing File Watchers */
-- enable/disable
EXEC DBMS_SCHEDULER.enable('data_fw') ;
EXEC DBMS_SCHEDULER.disable('data_fw') ;
-- change an attribute:
begin
DBMS_SCHEDULER.SET_ATTRIBUTE (name => 'data_fw',attribute =>'directory_path',value =>'/home/oracle/receivedfiles' ) ;
end;
/
beginDBMS_SCHEDULER.SET_ATTRIBUTE(
name => 'data_fw',
attribute =>'file_name',
value =>'*.txt' ) ;
end;
/
begin
DBMS_SCHEDULER.SET_ATTRIBUTE (name => 'data_fw',attribute =>'credential_name',value =>'fw_credential2' ) ;
end;
/
-- to drop a file watchers:
EXEC DBMS_SCHEDULER.DROP_job('import_data_job');
EXEC DBMS_SCHEDULER.DROP_program('import_data_prog');
EXEC DBMS_SCHEDULER.DROP_FILE_WATCHER('data_fw');
BEGIN
DBMS_SCHEDULER.drop_credential(credential_name => 'fw_credential');
END;
/
--------------Enable the file watcher, the program, and the job
BEGIN
DBMS_SCHEDULER.ENABLE('data_fw,import_data_prog,import_data_job');
END;
/
---------------------------------------------------
SELECT * FROM Dba_Scheduler_Job_Run_Details ORDER BY 1 DESC;
ORA-29538: Java not installed
ORA-06512: at "SYS.DBMS_ISCHED", line 3696
ORA-06512: at "SYS.DBMS_ISCHED", line 3907
ORA-29538 ORA-06512 FILE WATCHER
select * from product_component_version;
select comp_name,status from dba_registry;
select object_name from dba_objects where object_type like '%JAVA%' ;
set feedback on termout off
spool C:\install_java.log
@D:\app\Administrator\product\11.2.0\dbhome_1\javavm/install\initjvm.sql
@D:\app\Administrator\product\11.2.0\dbhome_1\xdk\admin\initxml.sql
@D:\app\Administrator\product\11.2.0\dbhome_1\xdk\admin\xmlja.sql
@D:\app\Administrator\product\11.2.0\dbhome_1\rdbms\admin\catjava.sql
@D:\app\Administrator\product\11.2.0\dbhome_1\rdbms\admin\catexf.sql
spool off
-------------Remove JVM:
SQL> @?/javavm/install/jvmrm.sql
-----------------------------Installing JVM on Oracle Database 11gR2
JVM was not installed on our Oracle 11gR2 database and we installed it manually by executing below steps from sqlplus login directly on the server [please follow all th steps]:
spool /home/oracle/ODMRINSTL/JVM/jvm_install.txt
set echo on
connect / as sysdba
shutdown immediate
startup mount
alter system set "_system_trig_enabled" = false scope=memory;
alter database open;
@?/javavm/install/initjvm.sql
@?/xdk/admin/initxml.sql
@?/xdk/admin/xmlja.sql
@?/rdbms/admin/catjava.sql
@?/rdbms/admin/catexf.sql
shutdown immediate
set echo off
spool off
exit
Verify with below queries & outputs:
SQL> select count(*), object_type from all_objects where object_type like '%JAVA%' group by object_type;
OWNER COUNT(*)
------ ----------
SYS 21313
EXFSYS 48
SQL> select comp_id,comp_name from dba_registry where comp_id in('CATJAVA','JAVAVM');
COMP_ID COMP_NAME
------- ------------------------------
JAVAVM JServer JAVA Virtual Machine
CATJAVA Oracle Database Java Packages
We have followed above for our case. For details and uninstallation please go through MOS Note:1112983.1 and prepare steps for your case.
-----------------------------------------------jssu.exe error on server
Problem signature:
Problem Event Name: APPCRASH
Application Name: jssu.exe
Application Version: 0.0.0.0
Application Timestamp: 4bb1e0d1
Fault Module Name: StackHash_cc43
Fault Module Version: 6.0.6001.18538
Fault Module Timestamp: 4cb73957
Exception Code: c0000374
Exception Offset: 00000000000a7857
OS Version: 6.0.6001.2.1.0.274.10
Locale ID: 1033
Additional Information 1: cc43
Additional Information 2: dcf9b9d4873b0ed461caccd3a40f2997
Additional Information 3: f0eb
Additional Information 4: 0e4fbfefd1abdaa97da3f3d79632520f
Read our privacy statement:
http://go.microsoft.com/fwlink/?linkid=50163&clcid=0x0409
ORA-29540: class schedFileWatcherJava does not exist
ORA-06512: at "SYS.DBMS_ISCHED", line 3744
ORA-06512: at "SYS.DBMS_ISCHED", line 3956
Monday, November 5, 2012
11g Migration using RMAN
The steps for 10g database:
1- Run the utlu112i.sql Pre-upgrade script. You can find this script under @$ORACLE_HOME/rdbms/admin/. It must be copied from the 11g database software.
SQL> @$ORACLE_HOME/rdbms/admin/utlu112i.sql
This script adds a column named tz_version to table named registry$database. Pre-upgrade script updates this column with the value of the following query.
SQL> select version from v$timezone_file;
So it performs following operation.
SQL> ALTER TABLE registry$database ADD (tz_version NUMBER);
SQL> UPDATE registry$database set tz_version =4;
SQL> ALTER PACKAGE “SYS”.”DBMS_REGISTRY” COMPILE BODY;
SQL> ALTER VIEW “SYS”.”DBA_REGISTRY_DATABASE” COMPILE;
2- Connect to 10g database and take RMAN full backup.
#rman target /
RMAN> backup as backupset database;
3- Copy 10g database backup files and archive files to 11g database server.
The steps for 11g database:
1- Create temporary pfile in $ORACLE_HOME/dbs
*.audit_file_dest=’/oracle/admin/TALIPDB/adump’
*.compatible=’11.2.0.0.0′
*.control_files=’+DATA/talipdb/controlfile/current.257.787742981′,’+DATA/talipdb/controlfile/current.258.787742983′
*.db_block_size=8192
*.db_create_file_dest=’+DATA’
*.db_create_online_log_dest_1=’+RECO’
*.db_domain=”
*.db_name=’TALIPDB’
*.diagnostic_dest=’/oracle’
*.job_queue_processes=0
*.open_cursors=300
*.pga_aggregate_target=1G
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=2G
*.undo_tablespace=’UNDOTBS1′
2- Open the database in NOMOUNT state.
# rman target /
RMAN> startup nomount;
3- Restore controlfile from backup.
RMAN> restore controlfile from ‘/oracle/ora11g/talipdb/backup/c-784951186-20120620-02′;
4- Open the database in MOUNT state.
RMAN> alter database mount;
5- Catalog RMAN backup files and archive log files.
RMAN> catalog start with ‘/oracle/ora11g/talipdb/backup’;
RMAN> catalog start with ‘/oracle/ora11g/talipdb/archive’;
6- Restore 10g database backup to +DATA diskgroup and perform incomplete recovery.
RMAN> run
{
allocate channel c1 device type disk;
SET NEWNAME FOR DATAFILE 1 TO ‘+DATA’;
SET NEWNAME FOR DATAFILE 2 TO ‘+DATA’;
SET NEWNAME FOR DATAFILE 3 TO ‘+DATA’;
SET NEWNAME FOR DATAFILE 4 TO ‘+DATA’;
restore database until sequence 4;
switch datafile all;
recover database until sequence 4;
}
7- Open the database with RESETLOGS UPGRADE.
# sqlplus / as sysdba
SQL> alter database open resetlogs upgrade;
8- Run the upgrade script.
SQL> SPOOL upgrade.log
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql
SQL> SPOOL off
9- If our 10g and 11g database os platforms are different then you must run utlmmig.sql script.
————–Changing 32 bit to 64 bit————–
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP UPGRADE
SQL> SPOOL migrate.log
SQL> @$ORACLE_HOME/rdbms/admin/utlmmig.sql
SQL> SPOOL off
——————————————-
10- Now, you can open the database.
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
11- Run the Post-Upgrade script to check problems.
SQL> @$ORACLE_HOME/rdbms/admin/utlu112s.sql
12- Compile invalid objects.
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
13- Drop the temporary file and create new one in +DATA diskgroup.
SQL> alter tablespace temp drop tempfile ‘/data_TALIPDB/temp01.dbf’;
SQL> alter tablespace temp add tempfile ‘+DATA’ size 1024M;
Listener ORA-00119 ORA-00132
Issues with setting Local Listener ORA-00119 ORA-00132
Today I was caught up in a issue with TNS while trying to setup SOA setup with OEM
Windows64bit machine
Database 11gr2
As per requirement we wanted to run the listener not on default port (1521 ) but some other port. Now as per Documentation if i want my non default port listener to register with pmon i need to set local_listener
“If you want PMON to register with a local listener that does not use TCP/IP, port 1521, then configure the LOCAL_LISTENER parameter in the initialization parameter file to locate the local listener.”
I though it will be a straight forward process just go ahead and run the alter system set local_listener=listener2 scope=both; but to surprise it didnt work as expected it returned an error
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER2' … so a straightforward process didnt work as expected.
alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=TAQVIA-IN.xx.xxx.com)(PORT=1522))))' sid='orcl64' scope=both
----------------------------------Startup fails with oracle error ORA-00119, ORA-00132-----------------------------------------------
Error Description:
---------------------------
Database Startup fails with oracle error ORA-00119, ORA-00132
SQL> startup mount
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_DBASE'
Cause of The problem:
-------------------------
If the tnsnames.ora entry was used for the value of the LOCAL_LISTENER and the LOCAL_LISTENER entry inside tnsnames.ora is changed or tnsnames.ora file is moved and hence the tns alias to which the LOCAL_LISTENER parameter points is no longer valid. So, the entry inside spfile or pfile to be unresolvable, and the database will not start.
PMON must be able to resolve whatever value the LOCAL_LISTENER or REMOTE_LISTENER parameter is set to. Here LISTENER_DBASE is the name of the local listener.
Remember the LISTENER_DBASE is NOT the listener name reflected in the listener.ora file but rather it is an alias stored in the tnsnames.ora file.
Solution of The problem
-----------------------------
A)Correct the tnsnames.ora
i)Determine if the tns alias is good by using tnsping.
SQL> !tnsping LISTENER_DBASE
TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 15-MAY-2008 11:57:06
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
TNS-03505: Failed to resolve name
So it could not find the name inside tnsnames.ora.
ii)
Add the LISTENER_DBASE entry in the tnsnames.ora file.
SQL> !vi /oracle/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
LISTENER_DBASE=(DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase')
and run tnsping utility,
SQL> !tnsping LISTENER_DBASE
TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 15-MAY-2008 12:11:40
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase')
TNS-12533: TNS:illegal ADDRESS parameters
So there is syntax error in the LISTENER_DBASE alias. Correct it as here add parenthesis.
SQL> !vi /oracle/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
LISTENER_DBASE=(DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase')))
iii)After correction run tnsping and start the database.
SQL> !tnsping LISTENER_DBASE
TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 15-MAY-2008 12:14:25
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/oracle/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION=(address=(protocol=tcp)(host=neptune)(port=1522)) (sid='dbase'))
OK (0 msec)
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 83886120 bytes
Database Buffers 75497472 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.
B)An alternative solution is to remove the LOCAL_LISTENER entry from the listener.ora file.
1)Create spfile from pfile if you don't have pfile upadted.
SQL>CREATE PFILE FROM SPFILE;
2)Open the pfile and remove the LOCAL_LISTENER entry.
$vi /oracle/app/oracle/product/10.2.0/db_1/dbs/initdbase.ora
3)Create spfile from pfile.
SQL>CREATE PFILE FROM SPFILE;
4)Start the database.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 83886120 bytes
Database Buffers 75497472 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.
Recommendation:
------------------
Though you can set the value of LOCAL_LISTENER (or REMOTE_LISTENER parameter if used) as alias in tnsnames.ora like in this example but it is not recommended setting. Instead use a full address descriptor inside pfile or set dynamically inside spfile.
In order to set dynamically inside spfile and in memory log in with dba privileges and issue:
ALTER SYSTEM set LOCAL_LISTENER="(address=(protocol=tcp)(host=yourhost)(port=yourport))" scope=both sid='instancename';
In my system,
SQL> ALTER SYSTEM set LOCAL_LISTENER="(address=(protocol=tcp)(host=neptune)(port=1522))" scope=both sid='dbase';
SQL> show parameter local
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
local_listener string (address=(protocol=tcp)(host=neptune)(port=1522))
ora 12560 tns protocol adapter error database DBCA fails
ora 12560 tns protocol adapter error database DBCA fails
-------------------Most common reasons for ORA-12560 in Windows are----------------------------
- firewall (port 1521)
- DHCP and not using loopback adapter
- antivirus
-Windows Domain Control should not be installed(Oracle recommends to NOT install it on a domain controller.)
-There was no ORA_DBA group and no ORADBA user on the server
while creating database in oracle 11.2.0.1 using DBCA i am getting following error.
ora 12560 tns protocol adapter error
i am using administrator userid . any soultion for this
1)
It seems you use windows OS so go %ORACLE_HOME%\network\admin directory and edit sqlnet.ora then change/add context below text
SQLNET.AUTHENTICATION_SERVICES = (NTS)
same error
2) Don't Install from mstsc
Did you try creating database through remote or directly server? if remote then try it from directly server.
i tried directly from server. oracle database software installed successfully. After that database creation i am getting this error. After getting ora 12560 tns protocol adapter error
i added tnsnames through NETCA. After that i started dbca i am getting same error. Then i checked tnsnames.ora file the entry i added NETCA is not there in the file.please provide any solution
3)
Are there only one ORACLE_HOME?
Post there sqlnet.ora file context.
Did you get other error messages than ora 12560?
Only ione oracle_home is there.Other than ora 12560 i am not getting any error. The sqlnet.ora file is given below
# sqlnet.ora Network Configuration File: C:\Oracle\product\11.2.0\dbhome_1\network\admin\sqlnet.ora
# Generated by Oracle configuration tools.
# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
4)
refer alert.log(C:\Oracle\product\11.2.0\diag\rdbms\<sid>\<db_unique_name>\trace directory)
In trace file i am getting following error in the OraVSSW file.
Could not execute [C:\Oracle\product\11.2.0\dbhome_1\bin\OraVSSW.exe ORAWH /d]; error 1
In this location no file is generated C:\Oracle\diag\rdbms
5)
ORA-12560 running DBCA on Windows 2008 AD Controller [ID 729774.1]
Another Solution:
Install the Oracle Software on a Non-Controller machine.
You can demote the Controller machine to a Member machine.
Perform the install, create the database.
Once everything is installed and setup, you can promote the machine to a Controller.
Be aware, you may have to go through these sames steps if you want to create another Database.
6) ORA-12560 - Database Creation Assistant (DBCA) Fails With TNS Error [ID 747243.1]
Cause
There was no ORA_DBA group and no ORADBA user on the server
System policies removed the group and/or the user used to install Oracl
Solution
After creating the group/user account (by matching the ones on the current production server), the DBCA ran to completion and created the database successfully.
Check with the sys admins to see if there is a policy or process in place to remove certain users and groups. The ORA_DBA group and the Oracle user used for the installation must remain intact.
If the problem remains, after creating the group/user account, you might need to remove Oracle and reinstall the software.
Create Database Structure using Datapump
You can use the INSERT statement to insert data into a table, partition, or view in two ways: conventional INSERT and direct-path INSERT. When you issue a conventional INSERT statement, Oracle Database reuses free space in the table into which you are inserting and maintains referential integrity constraints. With direct-path INSERT, the database appends the inserted data after existing data in the table. Data is written directly into datafiles, bypassing the buffer cache. Free space in the existing data is not reused. This alternative enhances performance during insert operations and is similar to the functionality of the Oracle direct-path loader utility, SQL*Loader.
Direct-path INSERT is subject to a number of restrictions. If any of these restrictions is violated, then Oracle Database executes conventional INSERT serially without returning any message, unless otherwise noted:
You can have multiple direct-path INSERT statements in a single transaction, with or without other DML statements. However, after one DML statement alters a particular table, partition, or index, no other DML statement in the transaction can access that table, partition, or index.
Queries that access the same table, partition, or index are allowed before the direct-path INSERT statement, but not after it.
If any serial or parallel statement attempts to access a table that has already been modified by a direct-path INSERT in the same transaction, then the database returns an error and rejects the statement.
The target table cannot be index organized or part of a cluster.
The target table cannot contain object type columns.
The target table cannot have any triggers or referential integrity constraints defined on it.
The target table cannot be replicated.
A transaction containing a direct-path INSERT statement cannot be or become distributed.
Select Dbms_Metadata.Get_Ddl(Object_Type, Object_Name) From User_Objects WHERE OBJECT_TYPE IN ('TABLE','INDEX','LOB','PACKAGE','PACKAGE BODY','PROCEDURE','FUNCTION','TRIGGER');
best way to import full=Y content=metadata_only
and drop sequeunce, link, directories and recreate
G:\app\Administrator\product\11.2.0\dbhome_1\BIN\sqlplus.exe sys/oracle@apx1213srv as sysdba
CREATE DIRECTORY EXPDP11G AS 'd:\exp1213';
GRANT read, write ON DIRECTORY EXPDP11G TO ldbo;
G:\app\Administrator\product\11.2.0\dbhome_1\BIN\impdp.exe ldbo/ldbo@nbs1213srv directory= EXPDP11G full=Y dumpfile=EXPDP11G.DMP logfile=expdp11meta.log content=metadata_only EXCLUDE=TABLESPACE,STATISTICS,DB_LINK,SEQUENCE
d:\app\Administrator\product\11.2.0\dbhome_1\bin\oradim.exe -delete -sid apx1314
select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints;
G:\app\Administrator\product\11.2.0\dbhome_1\BIN\impdp.exe ldbo/ldbo@NBS1213srv full=Y directory=EXPDP11G dumpfile=EXPDP11G.DMP LOGFILE=nbs1314_data.LOG content=data_only data_options=skip_constraint_errors
----------------Final-----------------------EXP
d:\app\Administrator\product\11.2.0\dbhome_1\bin\expdp.exe ldbo/ldbo@nbs1112srv Full=Y directory=EXPDP11G dumpfile=apx1213metadata.DMP LOGFILE=apx1213metadata.LOG content=metadata_only EXCLUDE=STATISTICS
----------------------
d:\app\Administrator\product\11.2.0\dbhome_1\bin\expdp.exe ldbo/ldbo@nbs1112srv directory=EXPDP11G dumpfile=apx1213metadata.DMP LOGFILE=apx1213metadata.LOG content=metadata_only schemas=LDBO EXCLUDE=TABLESPACE,DB_LINK,DIRECTORY,JOB,STATISTICS,USER,ROLE,SEQUENCE
d:\app\Administrator\product\11.2.0\dbhome_1\bin\expdp.exe ldbo/ldbo@nbs1112srv directory=EXPDP11G dumpfile=apx1213userROLE.DMP LOGFILE=apx1213userROLE.LOG content=metadata_only schemas=LDBO INCLUDE=USER,ROLE
d:\app\Administrator\product\11.2.0\dbhome_1\bin\expdp.exe ldbo/ldbo@nbs1112srv directory=EXPDP11G dumpfile=apx1213Synonyms.DMP LOGFILE=apx1213Synonyms.LOG content=metadata_only INCLUDE=SYNONYM
-------------------------------------------IMP-----------------------------------------------create optable-----------------------------
d:\app\Administrator\product\11.2.0\dbhome_1\bin\impdp.exe ldbo/ldbo@nbs1314SRV directory=DATA_PUMP_DIR dumpfile=apx1213metadata.DMP LOGFILE=apx1213metadata.LOG schemas=LDBO EXCLUDE=TABLESPACE,DB_LINK,JOB,USER,ROLE,SEQUENCE sqlfile=optable.sql
d:\app\Administrator\product\11.2.0\dbhome_1\bin\impdp.exe ldbo/ldbo@nbs1314SRV directory=DATA_PUMP_DIR dumpfile=apx1213metadata.DMP LOGFILE=apx1213Synonyms.LOG INCLUDE=SYNONYM sqlfile=synonyms.sql
d:\app\Administrator\product\11.2.0\dbhome_1\bin\impdp.exe ldbo/ldbo@nbs1314SRV directory=DATA_PUMP_DIR dumpfile=apx1213metadata.DMP LOGFILE=apx1213Synonyms.LOG INCLUDE=PUBLIC_SYNONYM/SYNONYM:"IN (SELECT synonym_name FROM dba_synonyms WHERE table_owner='LDBO')"
d:\app\Administrator\product\11.2.0\dbhome_1\bin\impdp.exe ldbo/ldbo@nbs1314SRV directory=DATA_PUMP_DIR dumpfile=apx1213metadata.DMP LOGFILE=apx1213userROLE.LOG IMPDPINCLUDE=USER,ROLE_GRANT,SYSTEM_GRANT,GRANT,OBJECT_GRANT sqlfile=users.sql
D:\app\Administrator\product\11.2.0\dbhome_1\BIN\impdp.exe ldbo/ldbo@NBS1314srv directory=DATA_PUMP_DIR dumpfile=EXPDP11GFull.DMP LOGFILE=EXPDP11GFull.DMP.LOG schemas=LDBO table_exists_action=append data_options=skip_constraint_errors
D:\app\Administrator\product\11.2.0\dbhome_1\BIN\impdp.exe ldbo/ldbo@NBS1314srv directory=DATA_PUMP_DIR dumpfile=EXPDP11GFull.DMP LOGFILE=EXPDP11GFull.DMP.LOG schemas=LDBO table_exists_action=replace data_options=skip_constraint_errors
REPLACE option drop the current table in the database and the import recreate the new table as in the dumpfile.
----------------------------------------------------------------------IMPDP----------------------------------------------------------------------------------------------------------
d:\app\Administrator\product\11.2.0\dbhome_1\bin\impdp.exe ldbo/ldbo@nbs1314SRV directory=DATA_PUMP_DIR dumpfile=apx1213metadata.DMP LOGFILE=apx1213metadata.LOG schemas=LDBO EXCLUDE=TABLESPACE,DB_LINK,JOB,USER,ROLE,SEQUENCE transform=pctspace:70
transform=pctspace:70 will reduce intial_extents, next_extents 70%.
select sum(initial_extent/1024/1024/1024) from user_tables;
select Sum(Initial_Extent/1024/1024/1024) from User_Indexes;
d:\app\Administrator\product\11.2.0\dbhome_1\bin\impdp.exe ldbo/ldbo@nbs1314SRV directory=DATA_PUMP_DIR dumpfile=apx1213metadata.DMP LOGFILE=apx1213Synonyms.LOG INCLUDE=SYNONYM
d:\app\Administrator\product\11.2.0\dbhome_1\bin\impdp.exe ldbo/ldbo@nbs1314SRV directory=DATA_PUMP_DIR dumpfile=apx1213metadata.DMP LOGFILE=apx1213userROLE.LOG INCLUDE=USER,ROLE
-------------------------------SEQUENCE CREATION--------------------------------------------
SELECT 'Create Sequence ' || Sequence_Name || ' minvalue ' || Min_Value || ' maxvalue ' || Max_Value || ' INCREMENT BY ' || Increment_By || ' CACHE ' || Cache_Size || ';' FROM USER_Sequences WHERE SEQUENCE_NAME NOT LIKE 'SQFIN%';
------------------------------------------------------------USER ROLE CREATION------------------------------------------------------------------------------------------------------------------------
SELECT 'CREATE ROLE ' ||ROLE || ';' FROM DBA_ROLES;
set head off
set pages 0
set long 9999999
spool c:\user_script.sql
SELECT DBMS_METADATA.GET_DDL('USER', USERNAME) || '/' DDL
FROM DBA_USERS
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA', USERNAME) || '/' DDL
FROM DBA_USERS where username in (select username from dba_ts_quotas)
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS where username in (select grantee from dba_role_privs)
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS where username in (select grantee from dba_sys_privs)
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS where username in (select grantee from dba_tab_privs);
spool off;
SELECT 'CREATE ROLE ' ||ROLE || ';' FROM DBA_ROLES;
Select 'GRANT ' || Privilege || ' ON ' ||Table_Name || ' TO "' || Grantee || '";' From Dba_Tab_Privs Where OWNER='LDBO' ORDER BY GRANTEE,TABLE_NAME;
Select 'GRANT ' || Privilege || ' TO "' || Grantee || '";' From Dba_sys_Privs ORDER BY GRANTEE;
select 'GRANT ' || Privilege || ' (' || COLUMN_NAME || ') ON ' ||Table_Name || ' TO "' || Grantee || '";' from Dba_Col_Privs;
Select 'GRANT ' || Granted_Role || ' TO "' || Grantee || '";' From Dba_role_Privs ORDER BY GRANTEE;
---------------------------------DATABASE LINK CREATION
SELECT 'CREATE ' || OWNER || ' DATABASE LINK ' || DB_LINK || ' CONNECT TO ' || USERNAME || ' IDENTIFIED BY ' || '<PLEASE PUT PASSWORD>' || ' USING ' || '<PLEASE PUT DATABASE STRING>' || ';' FROM DBA_DB_LINKS WHERE USERNAME !=' ';
SELECT 'DROP ' || OWNER || ' DATABASE LINK ' || DB_LINK || ';' FROM DBA_DB_LINKS WHERE USERNAME !=' ';
------------------------------------------DIRECTORY CREATION
select 'create directory '||OWNER||'.'||DIRECTORY_NAME||' as '||''''||DIRECTORY_PATH||''''||';' from dba_directories;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Select Object_Type,Count(*) From User_Objects Group By Object_Type Order By 1;
select COUNT(*) from user_objects where status='INVALID';
SELECT * FROM DBA_OBJECTS WHERE Owner='PUBLIC';
Select * From Dba_Synonyms Where Table_Owner='LDBO';
SELECT * FROM DBA_DB_LINKS;
SELECT * FROM DBA_DIRECTORIES;
EXEC sys.UTL_RECOMP.recomp_serial('LDBO');
Select * From Dba_Tablespace_Usage_Metrics;
Select * From Dba_Hist_Tbspc_Space_Usage;
Select Sum(Bytes)/1024/1024, Sum(Bytes)/1024/1024/1024 FROM dba_data_files;----------------DATABASE SIZE
------------------------------------------------------------------------------------
INCLUDE=SYNONYM:"IN (SELECT synonym_name FROM dba_synonyms WHERE table_owner='LDBO')" sqlfile=synonyms.sql
INCLUDE=SYNONYM:\"in \(select synonym_name FROM dba_synonyms where table_owner like \'LDBO%\'\)\"
-----------------------------------------------------
EXCLUDE=SEQUENCE:\"in \(select SEQUENCE_NAME from USER_Sequences where SEQUENCE_NAME like \'SQFIN%\'\)\"
SELECT * FROM USER_Sequences WHERE SEQUENCE_NAME NOT LIKE 'SQFIN%';
-------------------------------MANUALLY CREATION
RECREATE SEQUENCE BECAUSE LAST NUMBER IS START WILL PREVIOUS YR VALUE
Users and roles are imported BUT RECREATE AGAIN SOME USER,OBJECT ROLES CAN MISSINGS
Select COUNT(*) From Dba_Users;
Select Count(*) From Dba_Roles;
Select COUNT(*) From Dba_Users;
Select Count(*) From Dba_Roles;
Select Grantee,Count(*) From Dba_Role_Privs Group By Grantee;
Select Grantee,Count(*) From Dba_TAB_Privs Group By Grantee;
Select * From Dba_Directories;
Select * From Dba_Db_Links;
SELECT 'Create Sequence ' || Sequence_Name || ' minvalue ' || Min_Value || ' maxvalue ' || Max_Value || ' INCREMENT BY ' || Increment_By || ' CACHE ' || Cache_Size || ';' FROM USER_Sequences WHERE SEQUENCE_NAME NOT LIKE 'SQFIN%';
-----------------
expdp ldbo/ldbo@apx1213srv full=Y directory=dpump_dir1 dumpfile=apx1213_meta.DMP LOGFILE=apx1213_meta.LOG content=metadata_only
impdp ldbo/ldbo@apx1314srv full=Y directory=dpump_dir1 dumpfile=apx1213_meta.DMP LOGFILE=apx1314_meta.LOG EXCLUDE=DB_LINK,DIRECTORY,JOB,STATISTICS
impdp ldbo/ldbo@NBS1314srv full=Y directory=DATA_PUMP_DIR dumpfile=apx1213_meta.DMP LOGFILE=apx1314_meta.LOG EXCLUDE=DB_LINK,JOB,STATISTICS
IMPDP userid="'sys/oracle@NBS1314srv as sysdba'" full=Y directory=DATA_PUMP_DIR dumpfile=apx1213_meta.DMP LOGFILE=apx1314_meta.LOG
D:\app\Administrator\product\11.2.0\dbhome_1\BIN\impdp.exe
------------------DATA IMPORT--------------
D:\app\Administrator\product\11.2.0\dbhome_1\BIN\impdp.exe ldbo/ldbo@NBS1314srv full=Y directory=EXPDP11G dumpfile=EXPDP11G15102012.DMP LOGFILE=apx1314.LOG content=data_only
----------------------------------------------------------
DIrectory=EXPORT_AUTO dumpfile=apx1213_SYSmeta.DMP LOGFILE=apx1213_SYSmeta.LOG content=metadata_only schemas=LDBO,SYS INCLUDE=PROCEDURE,TRIGGER
EXCLUDE=DB_LINK,DIRECTORIES,JOB,STATISTICS
exec dbms_stats.delete_database_stats;
1) Users and roles are imported BUT RECREATE AGAIN SOME USER,OBJECT ROLES CAN MISSINGS
Select * From Dba_Users;
SELECT * FROM DBA_ROLES;
Select * From Dba_Role_Privs;
Select * From Dba_TAB_Privs;
Select * From Dba_SYS_Privs;
Select * From Dba_Directories;
Select * From Dba_Db_Links;
SELECT * FROM Dba_Scheduler_Jobs;
BEGIN sys.UTL_RECOMP.recomp_serial('LDBO'); END;
/
select object_name from user_objects where status='INVALID';
SELECT * FROM Dba_Scheduler_Jobs;
SELECT * FROM Dba_Objects WHERE OBJECT_NAME IN ('SPLOGININFORMATION','TGLOGININFORMATION');
CREATE SPLOGININFORMATION,TGLOGININFORMATION MANUALLY
DROP SEQUENCE
SELECT * FROM USER_Sequences WHERE SEQUENCE_NAME LIKE 'SQFIN%';
DATABASE LINK 2
FUNCTION 137
INDEX 730
JOB 21
LOB 255
PACKAGE 115
PACKAGE BODY 115
PROCEDURE 443
QUEUE 6
SEQUENCE 505
TABLE 981
TABLE PARTITION 368
TRIGGER 250
TYPE 13
VIEW 32
Select Tablespace_Name,SUM(bytes/1024/1024/1024) From DBA_Extents GROUP BY Tablespace_Name;
now let’s generate it again but using the transform parameter to reduce the size of it to 70% of original size.
impdp user/password transform=pctspace:70 content=metadata_only directory=datapump dumpfile=ex.dmp
before is to import it telling the Data Pump to reduce the size of extents to 70%
http://oraclenz.wordpress.com/2010/04/29/tip-of-the-month-how-to-have-super-powers-using-data-pump/
http://www.dba-oracle.com/t_data_pump_pctspace.htm
root> impdp transform=pctspace:25
This tells the impdp (import) to re-size the tablespace to 25% of its original size.
when the table actually gets imported than the extent sizes are same.
impdp scott1/tiger1 directory=dump_dir dumpfil
e=SCOTT1.DMP logfile=import1.log remap_schema=scott:scott1 SQLFILE=dump_dir:SQLf
ile2.log TRANSFORM=PCTSPACE:70
select
tablespace_name,
file_id,
owner,
segment_name,
block_id begin,
blocks,
block_id+blocks-1 end,
bytes/1024 KB,
'' free
from sys.dba_extents
where tablespace_name not in ('RBS','SYSTEM','TEMP','TOOLS','USER')
union
select
tablespace_name,
file_id,
'' owner,
'' segment_name,
block_id begin,
blocks,
block_id+blocks+1 end,
bytes/1023 KB,
'F' free
from sys.dba_free_space
where tablespace_name not in ('RBS','SYSTEM','TEMP','TOOLS','USER')
order by 1, 2, 5
;
For IMPORT:
$impdp parfile=import.par
import.par file contains :
full=y
directory=dump_files1
dumpfile=CR06172102_%Uexport.dmp
logfile=CR06172102_import.log
job_name=CR06172102_import
parallel=8
EXCLUDE=TABLESPACE
EXCLUDE=DIRECTORY
EXCLUDE=SCHEMA:"='TSMSYS'"
EXCLUDE=SCHEMA:"='SYS'"
EXCLUDE=SCHEMA:"='SYSTEM'"
EXCLUDE=SCHEMA:"='WMSYS'"
EXCLUDE=SCHEMA:"='DBSNMP'"
EXCLUDE=SCHEMA:"='OUTLN'"
EXCLUDE=SCHEMA:"='SYSMAN'"
EXCLUDE=SCHEMA:"='ANONYMOUS'"
EXCLUDE=SCHEMA:"='CTXSYS'"
EXCLUDE=SCHEMA:"='DIP'"
EXCLUDE=SCHEMA:"='ORAMAN'"
EXCLUDE=SCHEMA:"='XDB'"
expdp "'/ as sysdba'" DIRECTORY=DPDUMP_HRDB_DIR1 DUMPFILE=TRANS_HRDB%U.dmp LOGFILE=TRANS_HRDB.log FULL=Y "EXCLUDE=TABLESPACE,PROFILE,DIRECTORY,statistics,SCHEMA:\"IN ('ARCHIVE_USER','TRANS_HRDB','GLOBAL_HRDB','GLOBAL_INTERFACE_USER','TRANS_HRDD','PERF_DIRECT','GCM_DATA','GCM_APP','COMP','COMP_HIST','SYS','SYSTEM','SYSMAN','RMANUSER','RMAN','EXFSYS','WMSYS','DBSNMP')\"" PARALLEL=4
------------------------------Error
Oracle Bug ID 1434393.1
DataPump Import (IMPDP) With CONTENT=DATA_ONLY Fails With Error ORA-39126 Worker unexpected fatal error in KUPW$WORKER.UPATE_TD_ROW_IMP [ID 1434393.1]
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "LDBO"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "LDBO"."SYS_IMPORT_SCHEMA_01": ldbo/********@NBS1314srv directory=DAT
_PUMP_DIR dumpfile=EXPDP11GFull.DMP LOGFILE=EXPDP11GFull.DMP.LOG content=data_only schemas=LDBO
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UPATE_TD_ROW_IMP [112]
TABLE_DATA:"LDBO"."SCHEDULER$_JOB_ARG"
ORA-31603: object "SCHEDULER$_JOB_ARG" of type TABLE not found in schema "LDBO"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 8171
----- PL/SQL Call Stack -----
object line object
handle number name
000007FFB3973380 18990 package body SYS.KUPW$WORKER
000007FFB3973380 8192 package body SYS.KUPW$WORKER
000007FFB3973380 18552 package body SYS.KUPW$WORKER
000007FFB3973380 4105 package body SYS.KUPW$WORKER
000007FFB3973380 8875 package body SYS.KUPW$WORKER
000007FFB3973380 1649 package body SYS.KUPW$WORKER
000007FFB360AD30 2 anonymous block
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UPATE_TD_ROW_IMP [112]
TABLE_DATA:"LDBO"."SCHEDULER$_JOB_ARG"
ORA-31603: object "SCHEDULER$_JOB_ARG" of type TABLE not found in schema "LDBO"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 8171
----- PL/SQL Call Stack -----
object line object
handle number name
000007FFB3973380 18990 package body SYS.KUPW$WORKER
000007FFB3973380 8192 package body SYS.KUPW$WORKER
000007FFB3973380 18552 package body SYS.KUPW$WORKER
000007FFB3973380 4105 package body SYS.KUPW$WORKER
000007FFB3973380 8875 package body SYS.KUPW$WORKER
000007FFB3973380 1649 package body SYS.KUPW$WORKER
000007FFB360AD30 2 anonymous block
Job "LDBO"."SYS_IMPORT_SCHEMA_01" stopped due to fatal error at 15:12:28
. . imported "LDBO"."TBLAUDITEMAILACCOUNTDETAIL" 688.8 MB 389588 rows
ORA-31693: Table data object "LDBO"."JOURNDRCR" failed to load/unload and is being skipped due to error:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-02291: integrity constraint (LDBO.JOURNALCODE) violated - parent key not found
C:\Users\Administrator>G:\app\Administrator\product\11.2.0\dbhome_1\BIN\impdp.exe ldbo/ldbo@apx1213srv directory= EXPDP11G dumpfile=expdp11g.dmp logfile=expdp11meta.log SCHEMAS=LDBO content=DATA_ONLY data_options=skip_constraint_errors
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
ORA-39034: Table TABLE_DATA:"LDBO"."SCHEDULER$_JOB_ARG" does not exist.
C:\Users\Administrator>G:\app\Administrator\product\11.2.0\dbhome_1\BIN\impdp.exe ldbo/ldbo@apx1213srv directory= EXPDP11G dumpfile=expdp11g.dmp logfile=expdp11meta.log Tables=scheduler$_JOB_arg
C:\Users\Administrator>G:\app\Administrator\product\11.2.0\dbhome_1\BIN\impdp.exe ldbo/ldbo@apx1213srv directory= EXPDP11G dumpfile=expdp11g.dmp logfile=expdp11meta.log SCHEMAS=LDBO content=DATA_ONLY data_options=skip_constraint_errors
Saturday, November 3, 2012
Create Schema Objects
Note: This will not work from 10g to 11g version......................procedure will be corrupted
set pagesize 0
set linesize 30000
set long 500000
set longchunksize 500000
set trimspool on
set feed off
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY',true);
exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', 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);
SPOOL C:\OPTABLE.SQL
select dbms_metadata.get_ddl('SYNONYM',synonym_name,'PUBLIC') metadata from dba_synonyms where owner = 'PUBLIC' and table_owner = 'LDBO' ;
Select Dbms_Metadata.Get_Ddl('SEQUENCE',Sequence_Name) from User_Sequences Where Sequence_Name Not Like 'SQFIN_%';
Select Dbms_Metadata.Get_Ddl(Object_Type, Object_Name) From User_Objects WHERE OBJECT_TYPE = 'TABLE';
Select Dbms_Metadata.Get_Ddl(Object_Type, Object_Name) From User_Objects WHERE OBJECT_TYPE IN 'INDEX' ;
Select Dbms_Metadata.Get_Ddl('CONSTRAINT', Constraint_Name) Ddl FROM USER_CONSTRAINTS WHERE Constraint_Type='C' And Constraint_Name Not Like 'BIN%' order by table_name;
Select Dbms_Metadata.Get_Ddl(decode(constraint_type, 'R', 'REF_CONSTRAINT', 'CONSTRAINT'), constraint_name, owner) CONSDDL From Dba_Constraints WHERE owner='LDBO' AND CONSTRAINT_NAME NOT LIKE 'BIN$%' and constraint_type IN('P', 'R', 'U') Order By Case When Constraint_Type In('P','U') Then 0 Else 1 End;
Select Dbms_Metadata.Get_Ddl(Object_Type, Object_Name) From User_Objects WHERE OBJECT_TYPE IN ('PROCEDURE','FUNCTION','TYPE');
Select Dbms_Metadata.Get_Ddl('PACKAGE_SPEC', Object_Name) From User_Objects WHERE OBJECT_TYPE IN ('PACKAGE');
Select Dbms_Metadata.Get_Ddl('PACKAGE_BODY', Object_Name) From User_Objects WHERE OBJECT_TYPE IN ('PACKAGE BODY');
Select Dbms_Metadata.Get_Ddl(Object_Type, Object_Name) From User_Objects WHERE OBJECT_TYPE IN ('TRIGGER');
SELECT DBMS_METADATA.GET_DDL('USER', USERNAME) FROM DBA_USERS;
SELECT DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA', USERNAME) DDL FROM DBA_USERS where username in (select username from dba_ts_quotas);
SELECT DBMS_METADATA.GET_DDL('ROLE', role) FROM dba_roles;
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', GRANTEE) FROM DBA_SYS_PRIVS;
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', GRANTEE) FROM DBA_TAB_PRIVS WHERE OWNER='LDBO';
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', GRANTEE) FROM DBA_ROLE_PRIVS;
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', USERNAME) DDL FROM DBA_USERS where username in (select grantee from dba_role_privs);
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', USERNAME) DDL FROM DBA_USERS where username in (select grantee from dba_sys_privs)
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', USERNAME) DDL FROM DBA_USERS where username in (select grantee from dba_tab_privs);
spool off
--------------------------------------------------------------objects---------------------------------------------------------
db_link
sequence
directory
synonym
type
table
index
constraint
package
function
procedure
compile invalid
view
ref_constra
package body
type body
trigger
MATERIALIZED_VIEW
MATERIALIZED_VIEW_LOG
JOB
--------------------------------------------checking------------------------------------------------------------------
Select Object_Type,Count(*) From User_Objects Group By Object_Type Order By 1;
select COUNT(*) from user_objects where status='INVALID';
SELECT * FROM DBA_OBJECTS WHERE Owner='PUBLIC';
Select * From Dba_Synonyms Where Table_Owner='LDBO';
SELECT * FROM DBA_DB_LINKS;
SELECT * FROM DBA_DIRECTORIES;
select Constraint_Type,count(*) from User_Constraints group by Constraint_Type;
select table_name,count(*) from User_Constraints group by table_name order by 1;
select table_name,Search_Condition from User_Constraints where Constraint_Type='C' and table_name not like 'BIN%' ORDER BY 1;
---------------------------------------------------------------Directory Creation-------------------------------------------
select 'create directory '||OWNER||'.'||DIRECTORY_NAME||' as '||''''||DIRECTORY_PATH||''''||';' from dba_directories;
or
select dbms_metadata.get_ddl('DIRECTORY',directory_name) from dba_Directories;
get and change the path as you want
------------------------------------------------------------------Database link--------------------------------------------
SELECT 'CREATE ' || OWNER || ' DATABASE LINK ' || DB_LINK || ' CONNECT TO ' || USERNAME || ' IDENTIFIED BY ' || '<PLEASE PUT PASSWORD>' || ' USING ' || '<PLEASE PUT DATABASE STRING>' || ';' FROM DBA_DB_LINKS WHERE USERNAME !=' ';
Select 'DROP '||Decode(U.Name,'PUBLIC','public ')||'database link '||Chr(10)
||Decode(U.Name,'PUBLIC',Null, U.Name||'.')|| L.Name||Chr(10) ||';' TEXT
From Sys.Link$ L, Sys.User$ U
Where L.Owner# = U.User#;
SELECT 'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)
||Decode(U.Name,'PUBLIC',Null, U.Name||'.')|| L.Name||Chr(10)
||' connect to ' || L.Userid || ' identified by values '''
||L.PASSWORDX||''' using ''' || L.host || ''''
||chr(10)||';' TEXT
From Sys.Link$ L, Sys.User$ U
WHERE L.OWNER# = U.USER# AND L.USERID !=' ';
CREATE PUBLIC DATABASE LINK "LNK_RAKSHAK"
CONNECT TO "LDBO" IDENTIFIED BY LDBO USING 'APX1314SRV';
CREATE PUBLIC DATABASE LINK "LNK_CCM"
CONNECT TO "LDBO" IDENTIFIED BY LDBO USING 'APX1314SRV';
CREATE DATABASE LINK "LNK_DIGITAL"
CONNECT TO "LDBO" IDENTIFIED BY ldbo USING 'APX1314SRV';
create database link LNK_PREVIOUSYEARBALANCE connect to LDBO identified by ldbo using 'APX1314SRV';
CREATE PUBLIC DATABASE LINK "CMLDLINK"
CONNECT TO ldbo IDENTIFIED BY ldbo USING 'APX1314SRV';
--------------------------------------------------------------------------------------------------------------
set pagesize 0
set linesize 30000
set long 500000
set longchunksize 500000
set trimspool on
set feed off
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY',true);
exec dbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', 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);
SPOOL C:\SEQ.SQL
Select Dbms_Metadata.Get_Ddl('SEQUENCE',Sequence_Name) from User_Sequences Where Sequence_Name Not Like 'SQFIN_%';
SPOOL OFF
spool c:\tables.sql
Select Dbms_Metadata.Get_Ddl(Object_Type, Object_Name) From User_Objects WHERE OBJECT_TYPE = 'TABLE';
spool off
spool c:\index.sql
Select Dbms_Metadata.Get_Ddl(Object_Type, Object_Name) From User_Objects WHERE OBJECT_TYPE IN 'INDEX' ;
spool off
spool c:\syn.sql
select dbms_metadata.get_ddl('SYNONYM',synonym_name,'PUBLIC') metadata from dba_synonyms where owner = 'PUBLIC' and table_owner = 'LDBO' ;
spool off
spool c:\consc.sql
Select Dbms_Metadata.Get_Ddl('CONSTRAINT', Constraint_Name) Ddl FROM USER_CONSTRAINTS WHERE Constraint_Type='C' And Constraint_Name Not Like 'BIN%' order by table_name;
spool off
spool c:\consp.sql
Select Dbms_Metadata.Get_Ddl(
decode(constraint_type, 'R', 'REF_CONSTRAINT', 'CONSTRAINT'), constraint_name, owner) CONSDDL
From Dba_Constraints
WHERE owner='LDBO' AND CONSTRAINT_NAME NOT LIKE 'BIN$%'
and constraint_type IN('P', 'R', 'U')
Order By Case When Constraint_Type In('P','U') Then 0 Else 1 End;
spool off
spool c:\proc.sql
Select Dbms_Metadata.Get_Ddl(Object_Type, Object_Name) From User_Objects WHERE OBJECT_TYPE IN ('PROCEDURE','FUNCTION','TYPE');
spool off
spool c:\pack.sql
Select Dbms_Metadata.Get_Ddl('PACKAGE_SPEC', Object_Name) From User_Objects WHERE OBJECT_TYPE IN ('PACKAGE');
spool off
spool c:\packb.sql
Select Dbms_Metadata.Get_Ddl('PACKAGE_BODY', Object_Name) From User_Objects WHERE OBJECT_TYPE IN ('PACKAGE BODY');
spool off
spool c:\trigger.sql
Select Dbms_Metadata.Get_Ddl(Object_Type, Object_Name) From User_Objects WHERE OBJECT_TYPE IN ('TRIGGER');
spool off
------------------------------------------------------------------User Creation and its role--------------------------------------------
set head off
set pages 0
set long 9999999
spool c:\user_script.sql
SELECT DBMS_METADATA.GET_DDL('USER', USERNAME) || '/' DDL
FROM DBA_USERS
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('TABLESPACE_QUOTA', USERNAME) || '/' DDL
FROM DBA_USERS where username in (select username from dba_ts_quotas)
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS where username in (select grantee from dba_role_privs)
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS where username in (select grantee from dba_sys_privs)
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS where username in (select grantee from dba_tab_privs);
spool off;
--------------------------------------------------------------Role Creation------------------------------------------------------------------
SELECT 'CREATE ROLE ' ||ROLE || ';' FROM DBA_ROLES;
Select 'GRANT ' || Privilege || ' ON ' ||Table_Name || ' TO "' || Grantee || '";' From Dba_Tab_Privs Where OWNER='LDBO' ORDER BY GRANTEE,TABLE_NAME;
Select 'GRANT ' || Privilege || ' TO "' || Grantee || '";' From Dba_sys_Privs ORDER BY GRANTEE;
select 'GRANT ' || Privilege || ' (' || COLUMN_NAME || ') ON ' ||Table_Name || ' TO "' || Grantee || '";' from Dba_Col_Privs;
Select 'GRANT ' || Granted_Role || ' TO "' || Grantee || '";' From Dba_role_Privs ORDER BY GRANTEE;
-----------------------------------------------------------------------------------User Role Profile
select dbms_metadata.get_ddl('USER', u.username) AS ddl from dba_users u
union all
select dbms_metadata.get_ddl('ROLE', role) AS ddl FROM dba_roles
union all
select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', tq.username) AS ddl from dba_ts_quotas tq
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee) AS ddl from dba_role_privs rp
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee) AS ddl from dba_sys_privs sp
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee) AS ddl from dba_tab_privs tp
union all
select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', rp.grantee) AS ddl from dba_role_privs rp
union all
select to_clob('/* Start profile creation script in case they are missing') AS ddl from dba_users u where u.profile <> 'DEFAULT'
union all
select dbms_metadata.get_ddl('PROFILE', u.profile) AS ddl from dba_users u where u.profile <> 'DEFAULT'
union all
select to_clob('End profile creation script */') AS ddl from dba_users u where u.profile <> 'DEFAULT'
;
-----------------------------------------------------------------------------------
set define off
Turns off substitution variables.
---------------------------------------------------------compile invalid--------------------------------------------------------------------------------------------
EXEC sys.UTL_RECOMP.recomp_serial;
---------------------------------------------------------------Analyze-----------------------------------------------------------------------------------
EXEC DBMS_STATS.gather_database_stats(degree => DBMS_STATS.DEFAULT_DEGREE);
SET HEADING OFF
spool c:\temp\invalid.sql ;
select OBJECT_NAME from dba_objects where STATUS='INVALID';
select
'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '."' || OBJECT_NAME || '" COMPILE;' from dba_objects where status = 'INVALID'
and object_type in ('PACKAGE','FUNCTION','PROCEDURE','VIEW','TRIGGER');
Select decode( object_type, 'PACKAGE BODY', 'ALTER PACKAGE ' || OWNER || '.' || OBJECT_NAME || ' COMPILE BODY;')
from dba_objects
where status = 'INVALID' and object_type in ('PACKAGE BODY') order by object_type;
select 'ALTER ' || OWNER || ' ' || OBJECT_TYPE || ' "' || OBJECT_NAME || '" COMPILE;' from dba_objects where status = 'INVALID'
and object_type in ('SYNONYM');
Select decode( object_type, 'TYPE', 'ALTER TYPE ' || OWNER || '.' || OBJECT_NAME || ' COMPILE;')
from dba_objects where status = 'INVALID' and object_type in ('TYPE') order by object_type;
Select decode( object_type, 'TYPE BODY', 'ALTER TYPE ' || OWNER || '.' || OBJECT_NAME || ' COMPILE BODY;')
from dba_objects
where status = 'INVALID' and object_type in ('TYPE BODY') order by object_type;
spool out ;
@ c:\temp\invalid.sql ;
SELECT * FROM USER_ERRORS;
select 'SELECT INSTANCE_NAME,HOST_NAME FROM V$INSTANCE@' ||Db_Link || ';' from dba_db_links;
-------------------------------Drop objects
BEGIN
FOR cur_rec IN (SELECT object_name, object_type
FROM user_objects where object_type='TRIGGER') LOOP
BEGIN
EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' ' || cur_rec.object_name;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
FOR cur_rec IN (SELECT table_name, constraint_name
FROM user_constraints
WHERE constraint_type = 'R') LOOP
EXECUTE IMMEDIATE 'ALTER TABLE ' || cur_rec.table_name || ' DROP CONSTRAINT ' || cur_rec.constraint_name;
END LOOP;
FOR cur_rec IN (SELECT object_name, object_type
FROM user_objects) LOOP
BEGIN
EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' ' || cur_rec.object_name;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
FOR cur_rec IN (SELECT owner,synonym_name,table_owner FROM dba_synonyms where table_owner not in ('DBSNMP','ORACLE_OCM','OUTLN','SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','XDB')) LOOP
BEGIN
EXECUTE IMMEDIATE 'DROP ' || cur_rec.owner || ' SYNONYM ' || cur_rec.table_owner || '.' || cur_rec.synonym_name || ' FORCE';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
/
purge recyclebin ;
Subscribe to:
Posts (Atom)