Muhammad Nadeem Chaudhry's Blog


Oracle Enterprise Manager 13c Release 3 Cloud Control Installation (Without Using DB Repository Assistant)

Posted in Uncategorized by Muhammad Nadeem Chaudhry on January 10, 2019

Oracle 18c Data Guard

Posted in Uncategorized by Muhammad Nadeem Chaudhry on January 1, 2019

A) Installation
– DB name should be same.
– Instance name (oralce unique name) should be different.
– A failover renders the original primary database useless for converting to a standby database. If flashback database is not enabled, the original primary must be scrapped and recreated as a standby database.
https://oracle-base.com/articles/18c/oracle-db-18c-installation-on-oracle-linux-6-and-7

B) Application Connectivity

- Create database service using DBMS_SERVICE.CREATE_SERVICE package.
- Create entry in tnsnames.ora only for created service for addresses of hosts(s).

C) Concepts
https://www.youtube.com/watch?v=xgDALsnbl9k

Install Oracle 12c Flex Cluster

Posted in Uncategorized by Muhammad Nadeem Chaudhry on November 29, 2017

https://blog.dbi-services.com/install-oracle-12c-flex-cluster-easily/

Manual User Equivalence
https://gruffdba.wordpress.com/2012/10/30/oracle-11gr2-rac-sshusersetup-sh-script-to-swap-ssh-keys-between-hosts/
Error (Died at line 914)
http://oraclehub.com/convert-standard-to-flex-cluster/
Verification
https://www.red-gate.com/simple-talk/sql/oracle/oracle-flex-cluster-leaf-node-failover/
Database Creation Error (ORA-00445:background process "string" did not start after string seconds)
1: http://oracle.ninja/slow-system-ora-00445-background-process-diag-did-not-start-after-120-seconds/
2: Choose policy managed databases and server pools in advanced installation.

 

Installing APEX 4.2.5 in Oracle Database 11gR2

Posted in Uncategorized by Muhammad Nadeem Chaudhry on August 25, 2014

Time Dimension – SQL Query

Posted in Uncategorized by Muhammad Nadeem Chaudhry on January 30, 2014

This query generates a relational table storing the information needed to create an OLAP time dimension.

CREATE TABLE TIMES AS
SELECT
— DAY LEVEL
TIME_ID AS DAY_ID,
INITCAP(TO_CHAR(TIME_ID,’FMMONTH DD, YYYY’)) AS DAY_DESC,
INITCAP(TO_CHAR(TIME_ID, ‘FMDAY’)) AS DAY_NAME,
TO_NUMBER(TO_CHAR(TIME_ID – 1, ‘D’)) AS DAY_OF_WEEK,
TO_NUMBER(TO_CHAR(TIME_ID, ‘DD’)) AS DAY_OF_MONTH,
TO_NUMBER(TO_CHAR(TIME_ID, ‘DDD’)) AS DAY_OF_YEAR,
1 AS DAYS_IN_DAY,

— MONTH LEVEL
TO_CHAR(TIME_ID, ‘YYYY”-M”MM’) AS MONTH_ID,
TO_CHAR(TIME_ID, ‘FMMONTH YYYY’) AS MONTH_DESC,
DECODE(MOD(TO_NUMBER(TO_CHAR(TIME_ID, ‘MM’)), 4), 0, 4, MOD(TO_NUMBER(TO_CHAR(TIME_ID, ‘MM’)), 4)) AS MONTH_OF_QUARTER,
TO_NUMBER(TO_CHAR(TIME_ID, ‘MM’)) AS MONTH_OF_YEAR,
TO_CHAR(TIME_ID, ‘FMMONTH’) AS MONTH_NAME,
LAST_DAY(TIME_ID) AS END_OF_MONTH,
TO_CHAR(LAST_DAY(TIME_ID),’DD’) AS DAYS_IN_MONTH,

— QUARTER LEVEL
TO_CHAR(TIME_ID, ‘YYYY”-Q”Q’) AS QUARTER_ID,
INITCAP(TO_CHAR(TIME_ID, ‘FMQTH “QUARTER,” YYYY’)) AS QUARTER_DESC,
TO_NUMBER(TO_CHAR(TIME_ID, ‘Q’)) AS QUARTER_OF_YEAR,
TRUNC(ADD_MONTHS(TIME_ID,3), ‘Q’) – 1 AS END_OF_QUARTER,
(TRUNC(ADD_MONTHS(TIME_ID,3), ‘Q’) – 1) – (TRUNC(TIME_ID, ‘Q’) – 1) AS DAYS_IN_QUARTER,

— YEAR LEVEL
TO_NUMBER(TO_CHAR(TIME_ID, ‘YYYY’)) AS YEAR_ID,
(TRUNC(ADD_MONTHS(TIME_ID,12), ‘YYYY’) – 1) – (TRUNC(TIME_ID, ‘YYYY’) – 1) AS DAYS_IN_YEAR,
TRUNC(ADD_MONTHS(TIME_ID,12), ‘YYYY’) – 1 AS END_OF_YEAR,

— THIS IS THE SAME FOR ALL WEEKS
7 AS DAYS_IN_WEEK,

— CALENDAR WEEK LEVEL
TO_CHAR(TIME_ID, ‘IYYY’) || ‘-CW’ || TO_CHAR(TIME_ID, ‘IW’) AS CAL_WEEK_ID,
INITCAP(TO_CHAR(TIME_ID, ‘FMIWTH “WEEK OF” IYYY’)) || ‘, ENDING ‘ || TO_CHAR(TRUNC(TIME_ID + 7, ‘IW’) – 1, ‘FMMONTH DD, YYYY’) AS CAL_WEEK_DESC,
TO_NUMBER(TO_CHAR(TIME_ID, ‘IW’)) AS CAL_WEEK_OF_YEAR,
TRUNC(TIME_ID + 7, ‘IW’) – 1 AS END_OF_CAL_WEEK

FROM (SELECT TO_DATE(’01/01/2008′,’MM/DD/YYYY’) + ROWNUM – 1 AS TIME_ID
FROM ALL_OBJECTS
WHERE ROWNUM <= 1492)     — ADD ONE MORE DAY BY SUBTRACTING MIN-MAX DATES
ORDER BY TIME_ID;

Secure Database Passwords in an Oracle Wallet

Posted in Uncategorized by Muhammad Nadeem Chaudhry on September 10, 2013

Read article:

http://www.idevelopment.info/data/Oracle/DBA_tips/Security/SEC_15.shtml

Moreover,

SQL> connect /@orcl

impdp /@orcl

Difference between Oracle Database 10g & 11g

Posted in Uncategorized by Muhammad Nadeem Chaudhry on August 28, 2013

Please read:

Difference between Oracle 10g and 11g

Oracle Datapump Parameter REMAP_SCHEMA

Posted in Uncategorized by Muhammad Nadeem Chaudhry on July 3, 2013

Loads all objects from the source schema into a target schema.

Syntax
REMAP_SCHEMA=source_schema:target_schema

Suppose that you execute the following Export and Import commands to remap the hr schema into the scott schema:

expdp SYSTEM/password SCHEMAS=hr DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp

impdp SYSTEM/password DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp REMAP_SCHEMA=hr:scott

In this example, if user scott already exists before the import, then the Import REMAP_SCHEMA command will add objects from the hr schema into the existing scott schema. You can connect to the scott schema after the import by using the existing password (without resetting it).If user scott does not exist before you execute the import operation, import automatically creates it with an unusable password. This is possible because the dump file, hr.dmp, was created by SYSTEM, which has the privileges necessary to create a dump file that contains the metadata needed to create a schema. However, you cannot connect to scott on completion of the import, unless you reset the password forscott on the target database after the import completes.

Configuring Oracle Workflow for OWB

Posted in Uncategorized by Muhammad Nadeem Chaudhry on July 1, 2013

Send Report via EMail Attachment in Oracle Forms 10g

Posted in Uncategorized by Muhammad Nadeem Chaudhry on May 31, 2013

DECLARE
report_id REPORT_OBJECT := find_report_object(‘MYREPORT’);
report_message VARCHAR2(100) :=”;
rep_status VARCHAR2(100) :=”;
v_error Exception;
thelist paramlist;
BEGIN
— Create parameters list
thelist:= Get_Parameter_List(‘rep_paramlist’);
IF NOT Id_Null (thelist) THEN
DESTROY_PARAMETER_LIST(thelist);
END IF;
thelist := Create_Parameter_List (‘rep_paramlist’);

— Add report paremeters
ADD_PARAMETER(thelist, ‘FROM’,TEXT_PARAMETER, ‘abc@abc.com’);
ADD_PARAMETER(thelist, ‘SUBJECT’,TEXT_PARAMETER, ‘MRF NO:’||:GLOBAL.SIFNO);
ADD_PARAMETER(thelist, ‘PROJECT_NBR’,TEXT_PARAMETER, 968404);

— Set report object properties
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_COMM_MODE, SYNCHRONOUS);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_FILENAME, ‘C:\workingDirectory\MRF.RDF’);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_SERVER, ‘rep_XP_oracleasFRM’);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_DESTYPE, MAIL );
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_DESFORMAT, ‘PDF’);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_OTHER,’paramform=no mrfno=’||:GLOBAL.SIFNO);
SET_REPORT_OBJECT_PROPERTY(report_id,REPORT_DESNAME,’abc@abc.com’ );

— Check report status
report_message := run_report_object (report_id, thelist);
rep_status := report_object_status (report_message);
BEGIN
WHILE
rep_status in (‘RUNNING’,’OPENING_REPORT’,’ENQUEUED’)
LOOP
rep_status := report_object_status(report_message);
END LOOP;
EXCEPTION
WHEN v_error then
message(‘Error in sending Action Checklist email.’|| rep_status);
END;
END;

Next Page »