Thursday 31 August 2017

Oracle Data Pump
This chapter gives the original Export and Import utilities a primer on how to move up to the faster, more powerful, and more flexible Data Pump Export and Import utilities.

 In this Chapter I going to export and import SALES schema

 Export Schema

 Step 1 : Login to Source Database server

 login as: orapreprod

 password:******

 $ cd /u03/CRP02/db/tech_st/12.1.0/

 $ . CRP2_dev.env

 $ sqlplus "/as sysdba"

 Step 2 : Connect as system user

 SQL> conn system/clonemanager
Connected.

 SQL> desc all_directories;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(128)
 DIRECTORY_NAME                            NOT NULL VARCHAR2(128)
 DIRECTORY_PATH                                     VARCHAR2(4000)
 ORIGIN_CON_ID                                      NUMBER

 Step 4 : Creating Directory Objects If doesn’t exist

If You Want to create New DATAPUMP_DIR DIRECTORY

 SQL> CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/u03/CRP02/DATA_PUMP_DIR';

 SQL> select OWNER,DIRECTORY_NAME,DIRECTORY_PATH from ALL_DIRECTORIES;

 SYS
DATA_PUMP_DIR
/u03/CRP02/DATA_PUMP_DIR

 $ cd /u03/CRP02/

 $ mkdir DATA_PUMP_DIR

 $ cd /u03/CRP02/DATA_PUMP_DIR/

 $ pwd
/u03/CRP02/DATA_PUMP_DIR

 $ cd /u03/CRP02/db/tech_st/12.1.0/

 $ . CRP2_dev.env

 Step 5 : Export shema from source database

 $ expdp system/clonemanager schemas=SALES directory=DATA_PUMP_DIR dumpfile=30AUG17_SALES_SCHEMA.dmp logfile=30AUG17_SALES_SCHEMA.log

 Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /u03/CRP02/DATA_PUMP_DIR/30AUG17_SALES_SCHEMA.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Aug 30 16:17:58 2017 elapsed 0 00:15:11

 $ cd /u03/CRP02/DATA_PUMP_DIR/

 $ ls -lrt
-rw-r--r-- 1 orapreprod dba     41016 Aug 30 16:17 30AUG17_SALES_SCHEMA.log
-rw-r----- 1 orapreprod dba 493174784 Aug 30 16:17 30AUG17_SALES_SCHEMA.dmp

 Step 6 : Copy Exported dumpfile to target database

 $ scp -pr 30AUG17_SALES_SCHEMA.dmp orauat@172.25.4.50:/u01/UAT/DATA_PUMP_DIR

 The authenticity of host '172.25.4.50 (172.25.4.50)' can't be established.
RSA key fingerprint is 49:81:30:ed:2c:17:5c:79:ee:30:79:2d:15:b0:f3:9e.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.25.4.50' (RSA) to the list of known hosts.
orauat@172.25.4.50's password:
30AUG17_SALES_SCHEMA.dmp                      100%  470MB 156.8MB/s   00:03

 Import Schema

 Step 1 : Login to Target database server

 login as : orauat

 password : ******

 $ cd /u01/UAT/db/tech_st/12.1.0

 $ . UAT_ebsuatdb.env

 $ sqlplus "/as sysdba"

 $ cd /u01/UAT

 Step 2 : Creating Directory Objects If doesnot exist

 $ mkdir DATA_PUMP_DIR

 SQL> CREATE OR REPLACE DIRECTORY DATA_PUMP_DIR AS '/u01/UAT/DATA_PUMP_DIR';

 $ cd DATA_PUMP_DIR/

 $ pwd
/u01/UAT/DATA_PUMP_DIR

 Step 3 : Backup SCHEMA if already exist target database 

 $ expdp system/clonemanager schemas=SALES directory=DATA_PUMP_DIR dumpfile=30AUG17_SALES_SCHEMA_BACKUP.dmp logfile=30AUG17_SALES_SCHEMA_BACKUP.log

 Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /u01/UAT/DATA_PUMP_DIR/30AUG17_SALES_SCHEMA_BACKUP.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Wed Aug 30 16:04:49 2017 elapsed 0 00:14:20

 $ ls -lrt
-rw-r-----. 1 orauat dba 35319808 Aug 30 16:04 30AUG17_SALES_SCHEMA_BACKUP.dmp
-rw-r--r--. 1 orauat dba    33531 Aug 30 16:04 30AUG17_SALES_SCHEMA_BACKUP.log

 $ pwd
/u01/UAT/DATA_PUMP_DIR

 Step 4 : Drop Existing Schema

 Before drop user connect and check the DEFAULT TABLESPACE and TEMPORARY TABLESPACE of particular schema.

 SQL> select * from DBA_USERS where username='SALES';

 $ sqlplus "/as sysdba"

 SQL> drop user sales cascade;
drop user sales cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

 -----------------------------------------------------------------------------------------------------------------

 Currently SALES User connected
So disconnect the SALES user by below steps

 Kill connected user session
---------------------------------
SQL > select
       substr(a.spid,1,9) pid,
       substr(b.sid,1,5) sid,
       substr(b.serial#,1,5) ser#,
       substr(b.machine,1,6) box,
       substr(b.username,1,10) username,
--       b.server,
       substr(b.osuser,1,8) os_user,
       substr(b.program,1,30) program
from v$session b, v$process a
where
b.paddr = a.addr
and type='USER'
order by spid; 

 $ kill -9 26713

 $ sqlplus "/as sysdba"

 SQL> drop user sales cascade;

 User dropped.

 Step 5 : Create Schema

 SQL>CREATE USER SALES IDENTIFIED BY sales DEFAULT TABLESPACE APPS_POS_DATA TEMPORARY TABLESPACE TEMP1 PROFILE DEFAULT ACCOUNT UNLOCK;

 $ ls -lrt

 -rw-r-----. 1 orauat dba  35319808 Aug 30 16:04 30AUG17_SALES_SCHEMA_BACKUP.dmp
-rw-r--r--. 1 orauat dba     33531 Aug 30 16:04 30AUG17_SALES_SCHEMA_BACKUP.log
-rw-r-----. 1 orauat dba 493174784 Aug 30 16:17 30AUG17_SALES_SCHEMA.dmp

 Step 6: Now Import the schema from source dump file.

 Copy the source dump file to target database.
And run the below command

 $ impdp system/clonemanager schemas=SALES directory=DATA_PUMP_DIR dumpfile=30AUG17_SALES_SCHEMA.dmp logfile=30AUG17_SALES_SCHEMA.log remap_schema=SALES:SALES
at No comments:
Labels:

Error ORA-20100 .tmp Creation For FND_FILE Failed

APPLPTMP - this parameter directly update from $CONTEXT_FILE for usr tmp location issue

Difference between APPLPTMP and APPLTMP Directories in EBS (Doc ID 1355735.1)

Error ORA-20100 .tmp Creation For FND_FILE Failed In FA Concurrent Requests (Doc ID 392635.1)

Issue 


**Starts**18-AUG-2017 11:26:54
**SQL error and free**18-AUG-2017 11:27:14
ORACLE error 20100 in FDPSTP

Cause: FDPSTP failed due to ORA-20100: ORA-20100: Temporary file creation for FND_FILE failed.
Directory &FILE_DIR is invalid, contact your system administrator.

Action: Check that the directory is a valid directory with wri18-AUG-2017 11:27:14

While i checked cd $APPLPTMP parameter in $CONTEXT_FILE 
it shows some different location instead of my current server location

Solution

1.shut down application tier

2.So i have update $CONTEXT_FILE  with below /usr/tmp


grep APPLPTMP $CONTEXT_FILE

<APPLPTMP oa_var="s_applptmp" osd="UNIX">/usr/tmp</APPLPTMP>

3.run autoconfig application tier
at No comments:
Labels:

Saturday 19 August 2017

OERR: ORA-28040 "No matching authentication protocol" Reference Note (Doc ID 288402.1)

OERR: ORA-28040 "No matching authentication protocol" Reference Note (Doc ID 288402.1)

PURPOSE

This is a brief reference note to show the meaning of database error "ORA-28040". It includes the error text, "Cause" and "Action" from message files for each database version from 9.2 onwards, along with search links and details of any database bug issues related to the error.

SCOPE

This note is intended for general audience as a starting point to check the meaning of "ORA-28040". It does not give detailed information about the error and does not give error message text for versions below 9.2 .

DETAILS

Error Text, Cause and Action from Message File/s for ORA-28040

Versions 12.1

Error:  ORA-28040 No matching authentication protocol 
---------------------------------------------------------------------------
Cause:  There was no acceptable authentication protocol for either client or 
 server. 
Action: The administrator should set the values of the 
 SQLNET.ALLOWED_LOGON_VERSION_SERVER and 
 SQLNET.ALLOWED_LOGON_VERSION_CLIENT parameters, on both the client and 
 on the server, to values that match the minimum version software 
 supported in the system. This error is also raised when the client is 
 authenticating to a user account which was created without a verifier 
 suitable for the client software version. In this situation, that 
 account's password must be reset, in order for the required verifier to 
 be generated and allow authentication to proceed successfully. 

Versions 10.1, 10.2, 11.1, 11.2

Error:  ORA-28040 No matching authentication protocol 
---------------------------------------------------------------------------
Cause:  No acceptible authentication protocol for both client and server 
Action: Administrator should set SQLNET_ALLOWED_LOGON_VERSION parameter on 
 both client and servers to values that matches the minimum version 
 supported in the system. 









1.Go to Database server



2.source database environment file



3.cd $TNS_ADMIN



4.vi sqlnet.ora



5.add below line







SQLNET.ALLOWED_LOGON_VERSION_SERVER=8



SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8


















at





No comments:
  


















Labels:

























FDPSTP failed due to ORA-20100: ORA-20100: Temporary file creation for FND_FILE failed














APPLPTMP - this parameter directly update from $CONTEXT_FILE for usr tmp location issue







Difference between APPLPTMP and APPLTMP Directories in EBS (Doc ID 1355735.1)







Error ORA-20100 .tmp Creation For FND_FILE Failed In FA Concurrent Requests (Doc ID 392635.1)







Issue 











**Starts**18-AUG-2017 11:26:54



**SQL error and free**18-AUG-2017 11:27:14



ORACLE error 20100 in FDPSTP







Cause: FDPSTP failed due to ORA-20100: ORA-20100: Temporary file creation for FND_FILE failed.



Directory &FILE_DIR is invalid, contact your system administrator.







Action: Check that the directory is a valid directory with wri18-AUG-2017 11:27:14







While i checked cd $APPLPTMP parameter in $CONTEXT_FILE 



it shows some different location instead of my current server location







Solution







1.shut down application tier







2.update $CONTEXT_FILE  with "/usr/tmp"











grep APPLPTMP $CONTEXT_FILE











<APPLPTMP oa_var="s_applptmp" osd="UNIX">/usr/tmp</APPLPTMP>







3.run autoconfig in application tier














at





No comments:
  


















Labels:

















          

        

          

        

Wednesday 16 August 2017


          

        









Apps user account locked solution












R12: Clone fails with error "ORA-28000 The account is locked" (Doc ID 1114244.1)







SYMPTOMS



The clone of an E-Business Suite Instance of Release 12 fails with following error:



...

Exception occurred: java.sql.SQLException: ORA-28000: the account is locked
ORA-28000: the account is locked

...







CAUSE



The issue is caused by the APPS Database account, which is locked.



The following script allows you to check the number of failed logins, which can lock the apps user :





select profile , resource_name, limit 

from dba_profiles 

where resource_name ='FAILED_LOGIN_ATTEMPTS';






Example output :





DEFAULT FAILED_LOGIN_ATTEMPTS : 10

AD_PATCH_MONITOR_PROFILE FAILED_LOGIN_ATTEMPTS : 3







For additional information, please review Note 114930.1 - 'Oracle Password Management Policy'.











SOLUTION



To implement the solution, please test the following steps in a Development instance and migrate accordingly:


1. Ensure that you have taken a backup of your Environment.



2. Increase the number of failed login which locks apps user (optional):





alter profile AD_PATCH_MONITOR_PROFILE limit failed_login_attempts unlimited;

alter profile default limit failed_login_attempts unlimited password_lock_time 1/1440;





3. Unlock the locked APPS User account :





alter user apps account unlock;







4. Rerun the failed cloning commands.



5. Migrate the solution as appropriate to other Environments.














at





2 comments:
  


















Labels:

















        

      









Subscribe to:
Posts (Atom)