Oracle Tutorials

In this Blog you can learn database, networking and cloud skills that will help you to understand Oracle Database and Non Oracle Database with extra key skill

Password Management in oracle ebs R12.X (FNDCPASS / AFPASSWD)

September 24, 2021 No comments :

 Password Management in Oracle EBS R12.X

There are two different utilities being use in oracle ebs R12.x 

1- FNDCPASS  - password passes on command line

2- AFPASSWD - Provide more security

Monitoring Concurrent Request

September 13, 2020 No comments :

 hi this the 

Practice SQL with Sample Tables

August 09, 2019 No comments :

Image result for SQL table
CREATE TABLE  "AGENTS"
   (
    "AGENT_CODE" CHAR(6) NOT NULL PRIMARY KEY,
        "AGENT_NAME" CHAR(40),
        "WORKING_AREA" CHAR(35),
        "COMMISSION" NUMBER(10,2),
        "PHONE_NO" CHAR(15),
        "COUNTRY" VARCHAR2(25)
         );

INSERT INTO AGENTS VALUES ('A007', 'Ramasundar', 'Bangalore', '0.15', '077-25814763', '');
INSERT INTO AGENTS VALUES ('A003', 'Alex ', 'London', '0.13', '075-12458969', '');
INSERT INTO AGENTS VALUES ('A008', 'Alford', 'New York', '0.12', '044-25874365', '');
INSERT INTO AGENTS VALUES ('A011', 'Ravi Kumar', 'Bangalore', '0.15', '077-45625874', '');
INSERT INTO AGENTS VALUES ('A010', 'Santakumar', 'Chennai', '0.14', '007-22388644', '');
INSERT INTO AGENTS VALUES ('A012', 'Lucida', 'San Jose', '0.12', '044-52981425', '');
INSERT INTO AGENTS VALUES ('A005', 'Anderson', 'Brisban', '0.13', '045-21447739', '');
INSERT INTO AGENTS VALUES ('A001', 'Subbarao', 'Bangalore', '0.14', '077-12346674', '');
INSERT INTO AGENTS VALUES ('A002', 'Mukesh', 'Mumbai', '0.11', '029-12358964', '');
INSERT INTO AGENTS VALUES ('A006', 'McDen', 'London', '0.15', '078-22255588', '');
INSERT INTO AGENTS VALUES ('A004', 'Ivan', 'Torento', '0.15', '008-22544166', '');
INSERT INTO AGENTS VALUES ('A009', 'Benjamin', 'Hampshair', '0.11', '008-22536178', '');


CREATE TABLE  "CUSTOMER"
   (    "CUST_CODE" VARCHAR2(6) NOT NULL PRIMARY KEY,
        "CUST_NAME" VARCHAR2(40) NOT NULL,
        "CUST_CITY" CHAR(35),
        "WORKING_AREA" VARCHAR2(35) NOT NULL,
        "CUST_COUNTRY" VARCHAR2(20) NOT NULL,
        "GRADE" NUMBER,
        "OPENING_AMT" NUMBER(12,2) NOT NULL,
        "RECEIVE_AMT" NUMBER(12,2) NOT NULL,
        "PAYMENT_AMT" NUMBER(12,2) NOT NULL,
        "OUTSTANDING_AMT" NUMBER(12,2) NOT NULL,
        "PHONE_NO" VARCHAR2(17) NOT NULL,
        "AGENT_CODE" CHAR(6) NOT NULL REFERENCES AGENTS
);

INSERT INTO CUSTOMER VALUES ('C00013', 'Holmes', 'London', 'London', 'UK', '2', '6000.00', '5000.00', '7000.00', '4000.00', 'BBBBBBB', 'A003');
INSERT INTO CUSTOMER VALUES ('C00001', 'Micheal', 'New York', 'New York', 'USA', '2', '3000.00', '5000.00', '2000.00', '6000.00', 'CCCCCCC', 'A008');
INSERT INTO CUSTOMER VALUES ('C00020', 'Albert', 'New York', 'New York', 'USA', '3', '5000.00', '7000.00', '6000.00', '6000.00', 'BBBBSBB', 'A008');
INSERT INTO CUSTOMER VALUES ('C00025', 'Ravindran', 'Bangalore', 'Bangalore', 'India', '2', '5000.00', '7000.00', '4000.00', '8000.00', 'AVAVAVA', 'A011');
INSERT INTO CUSTOMER VALUES ('C00024', 'Cook', 'London', 'London', 'UK', '2', '4000.00', '9000.00', '7000.00', '6000.00', 'FSDDSDF', 'A006');
INSERT INTO CUSTOMER VALUES ('C00015', 'Stuart', 'London', 'London', 'UK', '1', '6000.00', '8000.00', '3000.00', '11000.00', 'GFSGERS', 'A003');
INSERT INTO CUSTOMER VALUES ('C00002', 'Bolt', 'New York', 'New York', 'USA', '3', '5000.00', '7000.00', '9000.00', '3000.00', 'DDNRDRH', 'A008');
INSERT INTO CUSTOMER VALUES ('C00018', 'Fleming', 'Brisban', 'Brisban', 'Australia', '2', '7000.00', '7000.00', '9000.00', '5000.00', 'NHBGVFC', 'A005');
INSERT INTO CUSTOMER VALUES ('C00021', 'Jacks', 'Brisban', 'Brisban', 'Australia', '1', '7000.00', '7000.00', '7000.00', '7000.00', 'WERTGDF', 'A005');
INSERT INTO CUSTOMER VALUES ('C00019', 'Yearannaidu', 'Chennai', 'Chennai', 'India', '1', '8000.00', '7000.00', '7000.00', '8000.00', 'ZZZZBFV', 'A010');
INSERT INTO CUSTOMER VALUES ('C00005', 'Sasikant', 'Mumbai', 'Mumbai', 'India', '1', '7000.00', '11000.00', '7000.00', '11000.00', '147-25896312', 'A002');
INSERT INTO CUSTOMER VALUES ('C00007', 'Ramanathan', 'Chennai', 'Chennai', 'India', '1', '7000.00', '11000.00', '9000.00', '9000.00', 'GHRDWSD', 'A010');
INSERT INTO CUSTOMER VALUES ('C00022', 'Avinash', 'Mumbai', 'Mumbai', 'India', '2', '7000.00', '11000.00', '9000.00', '9000.00', '113-12345678','A002');
INSERT INTO CUSTOMER VALUES ('C00004', 'Winston', 'Brisban', 'Brisban', 'Australia', '1', '5000.00', '8000.00', '7000.00', '6000.00', 'AAAAAAA', 'A005');
INSERT INTO CUSTOMER VALUES ('C00023', 'Karl', 'London', 'London', 'UK', '0', '4000.00', '6000.00', '7000.00', '3000.00', 'AAAABAA', 'A006');

INSERT INTO CUSTOMER VALUES ('C00006', 'Shilton', 'Torento', 'Torento', 'Canada', '1', '10000.00', '7000.00', '6000.00', '11000.00', 'DDDDDDD', 'A004');
INSERT INTO CUSTOMER VALUES ('C00010', 'Charles', 'Hampshair', 'Hampshair', 'UK', '3', '6000.00', '4000.00', '5000.00', '5000.00', 'MMMMMMM', 'A009');
INSERT INTO CUSTOMER VALUES ('C00017', 'Srinivas', 'Bangalore', 'Bangalore', 'India', '2', '8000.00', '4000.00', '3000.00', '9000.00', 'AAAAAAB', 'A007');
INSERT INTO CUSTOMER VALUES ('C00012', 'Steven', 'San Jose', 'San Jose', 'USA', '1', '5000.00', '7000.00', '9000.00', '3000.00', 'KRFYGJK', 'A012');
INSERT INTO CUSTOMER VALUES ('C00008', 'Karolina', 'Torento', 'Torento', 'Canada', '1', '7000.00', '7000.00', '9000.00', '5000.00', 'HJKORED', 'A004');
INSERT INTO CUSTOMER VALUES ('C00003', 'Martin', 'Torento', 'Torento', 'Canada', '2', '8000.00', '7000.00', '7000.00', '8000.00', 'MJYURFD', 'A004');
INSERT INTO CUSTOMER VALUES ('C00009', 'Ramesh', 'Mumbai', 'Mumbai', 'India', '3', '8000.00', '7000.00', '3000.00', '12000.00', 'Phone No', 'A002');
INSERT INTO CUSTOMER VALUES ('C00014', 'Rangarappa', 'Bangalore', 'Bangalore', 'India', '2', '8000.00', '11000.00', '7000.00', '12000.00', 'AAAATGF', 'A001');
INSERT INTO CUSTOMER VALUES ('C00016', 'Venkatpati', 'Bangalore', 'Bangalore', 'India', '2', '8000.00', '11000.00', '7000.00', '12000.00', 'JRTVFDD', 'A007');
INSERT INTO CUSTOMER VALUES ('C00011', 'Sundariya', 'Chennai', 'Chennai', 'India', '3', '7000.00', '11000.00', '7000.00', '11000.00', 'PPHGRTS', 'A010');

CREATE TABLE  "ORDERS"
   (
        "ORD_NUM" NUMBER(6,0) NOT NULL PRIMARY KEY,
        "ORD_AMOUNT" NUMBER(12,2) NOT NULL,
        "ADVANCE_AMOUNT" NUMBER(12,2) NOT NULL,
        "ORD_DATE" DATE NOT NULL,
        "CUST_CODE" VARCHAR2(6) NOT NULL REFERENCES CUSTOMER,
        "AGENT_CODE" CHAR(6) NOT NULL REFERENCES AGENTS,
        "ORD_DESCRIPTION" VARCHAR2(60) NOT NULL
   );



INSERT INTO ORDERS VALUES('200100', '1000.00', '600.00', '08-JAN-08', 'C00013', 'A003', 'SOD');
INSERT INTO ORDERS VALUES('200110', '3000.00', '500.00', '04-FEB-08', 'C00019', 'A010', 'SOD');
INSERT INTO ORDERS VALUES('200107', '4500.00', '900.00', '08-MAR-08', 'C00007', 'A010', 'SOD');
INSERT INTO ORDERS VALUES('200112', '2000.00', '400.00', '05-APR-08', 'C00016', 'A007', 'SOD');
INSERT INTO ORDERS VALUES('200113', '4000.00', '600.00', '06-MAY-08', 'C00022', 'A002', 'SOD');
INSERT INTO ORDERS VALUES('200102', '2000.00', '300.00', '05-JUN-08', 'C00012', 'A012', 'SOD');
INSERT INTO ORDERS VALUES('200114', '3500.00', '2000.00', '08-JUL-08', 'C00002', 'A008', 'SOD');
INSERT INTO ORDERS VALUES('200122', '2500.00', '400.00', '09-AUG-08', 'C00003', 'A004', 'SOD');
INSERT INTO ORDERS VALUES('200118', '500.00', '100.00', '07-SEP-08', 'C00023', 'A006', 'SOD');
INSERT INTO ORDERS VALUES('200119', '4000.00', '700.00', '09-OCT-08', 'C00007', 'A010', 'SOD');
INSERT INTO ORDERS VALUES('200121', '1500.00', '600.00', '09-NOV-08', 'C00008', 'A004', 'SOD');
INSERT INTO ORDERS VALUES('200130', '2500.00', '400.00', '07-DEC-08', 'C00025', 'A011', 'SOD');
INSERT INTO ORDERS VALUES('200134', '4200.00', '1800.00', '09-JAN-08', 'C00004', 'A005', 'SOD');
INSERT INTO ORDERS VALUES('200108', '4000.00', '600.00', '02-FEB-08', 'C00008', 'A004', 'SOD');
INSERT INTO ORDERS VALUES('200103', '1500.00', '700.00', '05-MAR-08', 'C00021', 'A005', 'SOD');
INSERT INTO ORDERS VALUES('200105', '2500.00', '500.00', '07-APR-08', 'C00025', 'A011', 'SOD');
INSERT INTO ORDERS VALUES('200109', '3500.00', '800.00', '07-MAY-08', 'C00011', 'A010', 'SOD');
INSERT INTO ORDERS VALUES('200101', '3000.00', '1000.00', '07-JUN-08', 'C00001', 'A008', 'SOD');
INSERT INTO ORDERS VALUES('200111', '1000.00', '300.00', '07-JUL-08', 'C00020', 'A008', 'SOD');
INSERT INTO ORDERS VALUES('200104', '1500.00', '500.00', '03-AUG-08', 'C00006', 'A004', 'SOD');
INSERT INTO ORDERS VALUES('200106', '2500.00', '700.00', '04-SEP-08', 'C00005', 'A002', 'SOD');
INSERT INTO ORDERS VALUES('200125', '2000.00', '600.00', '10-OCT-08', 'C00018', 'A005', 'SOD');
INSERT INTO ORDERS VALUES('200117', '800.00', '200.00', '10-NOV-08', 'C00014', 'A001', 'SOD');
INSERT INTO ORDERS VALUES('200123', '500.00', '100.00', '09-DEC-08', 'C00022', 'A002', 'SOD');
INSERT INTO ORDERS VALUES('200120', '500.00', '100.00', '07-JAN-08', 'C00009', 'A002', 'SOD');
INSERT INTO ORDERS VALUES('200116', '500.00', '100.00', '07-FEB-08', 'C00010', 'A009', 'SOD');
INSERT INTO ORDERS VALUES('200124', '500.00', '100.00', '06-MAR-08', 'C00017', 'A007', 'SOD');
INSERT INTO ORDERS VALUES('200126', '500.00', '100.00', '06-APR-08', 'C00022', 'A002', 'SOD');
INSERT INTO ORDERS VALUES('200129', '2500.00', '500.00', '07-MAY-08', 'C00024', 'A006', 'SOD');
INSERT INTO ORDERS VALUES('200127', '2500.00', '400.00', '07-JUN-08', 'C00015', 'A003', 'SOD');
INSERT INTO ORDERS VALUES('200128', '3500.00', '1500.00', '07-JUL-08', 'C00009', 'A002', 'SOD');
INSERT INTO ORDERS VALUES('200135', '2000.00', '800.00', '09-AUG-08', 'C00007', 'A010', 'SOD');
INSERT INTO ORDERS VALUES('200131', '900.00', '150.00', '08-SEP-08', 'C00012', 'A012', 'SOD');
INSERT INTO ORDERS VALUES('200133', '1200.00', '400.00', '06-OCT-08', 'C00009', 'A002', 'SOD');

Sample Table File_2 

watcher

April 22, 2019 No comments :

Set FRA in Oracle RAC Database

April 17, 2019 No comments :
Setting Fast Recovery Area (FRA) and Enabling archive log in Oracle RAC Database

aSS
1- Check the database status: srvctl status database -d ORCL
Instance ORCL1 is running on node rac-node1

Instance ORCL2 is running on node rac-node2
2- Create arch directory in ASM disk location (+DATA)
$ asmcmd
asmcmd> lsdg
asmcmd> cd +DATA
asmcmd> mkdir arch
3- If database is up then connect to any instance of database and run the following query to set the parameter:
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 3G SCOPE=BOTH SID='*';
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+Data/arch' SCOPE=BOTH SID='*';
4- Stop the database : srvctl stop database -d ORCL
5- Start database in mount state : srvctl start database -d ORCL -o mount
6- Connect to datbase and enable archive log:
sqlplus / as sysdba
alter database archivelog;
7- Stop the database :srvctl stop database -d ORCL
8- Start database and check archive log is enabled or not
$ srvctl start database -d ORCL
$ sqlplus / as sysdba
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     24
Next log sequence to archive   25

Current log sequence           25
9- Check archive is generating or not
SQL> alter system switch logfile;
Go to the location '+Data/arch' in asmcmd and type lsdg 

Setting Up FRA in Oracle Database

April 17, 2019 No comments :
To enable the flash recovery area(FRA), you must set the two initialization parameters DB_RECOVERY_FILE_DEST_SIZE (which specifies the disk quota, or maximum space to use for flash recovery area files for this database) and 
DB_RECOVERY_FILE_DEST (which specifies the location of the flash recovery area).

 Note:

  • DB_RECOVERY_FILE_DEST_SIZE must be set before DB_RECOVERY_FILE_DEST in parameter file.
  • In a RAC database, all instances must have the same values for these parameters

SELECT dest_name, status, destination FROM v$archive_dest;
 ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G;
 ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u02/oracle/fra';
 
 
 ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 10G SCOPE=BOTH SID='*';
 ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/disk1/flash_recovery_area' SCOPE=BOTH SID='*';

 If FRA is ASM Disk Group
 --------------------------
 ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '+disk1' SCOPE=BOTH SID='*';
 
 Startup mount 
 alter database close;
 Alter database archivelog;
 alter database open;
 archive log list;
 
 
 10.10.10.73
 ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 3G SCOPE=BOTH ;
 ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u06/FRA/DCLUATarch' SCOPE=BOTH; 
 
 ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 3G SCOPE=BOTH ;
 ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u06/FRA/dg731122arch' SCOPE=BOTH ;
 
 ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 3G SCOPE=BOTH ;
 ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u06/FRA/dg731211arch' SCOPE=BOTH;
 dg731211
 
 
 10.10.10.89
 ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 3G SCOPE=BOTH;
 ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u01/install/EBSUAT/EBSarch' SCOPE=BOTH; 
 
 
 41-RAC
 ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 3G SCOPE=BOTH SID='*';
 ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+OCRVFDG' SCOPE=BOTH SID='*';
 srvctl stop database -d ORCL
 srvctl start database -d ORCL -o mount
 alter database archivelog;
 srvctl stop database -d ORCL
 srvctl start database -d ORCL
 
 
 10.10.10.74
 ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 3G SCOPE=BOTH;
 ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u04/ARCH/DG731124' SCOPE=BOTH; 
 
 ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 3G SCOPE=BOTH;
 ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/u04/ARCH/DG731212' SCOPE=BOTH;
 
 
 
 
----- 10.10.10.73 
 Instance        FRA_LOCATION SIZE ArchiveLog
 --------------------------------------------------------------
 DCLUAT /u06/FRA/DCLUAT 3G ENABLED
 dg731122 /u06/FRA/dg731122arch 3G ENABLED
 dg731211 /u06/FRA/dg731211arch 3G ENABLED
 
----- 10.10.10.41
 Instance        FRA_LOCATION     SIZE ArchiveLog
 -----------------------------------------------------------------
 ORCL +OCRVFDG 3G ENABLED
 
----- 10.10.10.74 
 Instance        FRA_LOCATION SIZE ArchiveLog
 --------------------------------------------------------------
 DG731124 /u04/ARCH/DG731124 3G ENABLED
 DG731212 /u04/ARCH/DG731212 3G ENABLED
 
 

 
 
 Dataguard -1 
 10.10.10.73 primary db: 
 10.10.10.74 standby  
 
 parallel from 41 have to take backup of EBSUAT in /mnt
 shift db in archive log and take rman backup in mount state in location of /mnt
 
 
 dataguard -2
 primary 10.10.10.41
 standby 10.10.10.89  db has been dropped. 
 
 
 EBSUAT database env file location : . /u01/app/oracle/product/11.2.0/EBSUAT_rac-node1.env
 
 FILE_NAME
--------------------------------------------------------------------------------
+OCRVFDG/ebsuat/data/system17.dbf.

AutoVacuum in PostgreSQL

April 02, 2019 No comments :
Updates are on the way.....
Subscribe to: Posts ( Atom )

Recent Posts

Contact Form

Name

Email *

Message *