Oracle Enterprise Manager 13c Release 3 Cloud Control Installation (Without Using DB Repository Assistant)
Download OEM13c R3 Installation on CentOS 7.6
Oracle 18c Data Guard
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
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.
Time Dimension – SQL Query
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_WEEKFROM (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
Read article:
http://www.idevelopment.info/data/Oracle/DBA_tips/Security/SEC_15.shtml
Moreover,
SQL> connect /@orcl
impdp /@orcl
Oracle Datapump Parameter REMAP_SCHEMA
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.
Send Report via EMail Attachment in Oracle Forms 10g
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;
