REAL TIME AI ORACLE DATABASE PROBLEM AND SOLUTIONS

Friday, May 29, 2020

Importing Data into Oracle on Amazon RDS using Oracle Data Pump and a Database Link

Import schema in AWS Oracle RDS
==============================================

Environment Details:
-------------------------------

Schema Name : TEST

Source (Normal)
-----------------

Database Name : india
Database Version : 19c
Platform : oracle linux

Target (RDS)
--------------

Database Name : orcl
Dabase version : 19c
Platform : AWS Oracle RDS

Step 1 : perform schema export

expdp '/ as sysdba' directory=DATA_PUMP_DIR dumpfile=test_schema.dmp LOGFILE=test_schema.log schemas=test

example

[oracle@anurag ~]$ expdp directory=DATA_PUMP_DIR dumpfile=test_schema.dmp LOGFILE=test_schema.log schemas=test

Export: Release 19.0.0.0.0 - Production on Thu May 28 17:21:14 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.

Username: / as sysdba
Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYS"."SYS_EXPORT_SCHEMA_01": /******** AS SYSDBA directory=DATA_PUMP_DIR dumpfile=test_schema.dmp LOGFILE=test_schema.log schemas=test
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "TEST"."TEST" 5.539 KB 2 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/u01/oracle/admin/india/dpdump/test_schema.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu May 28 17:21:53 2020 elapsed 0 00:00:29

[oracle@anurag ~]$

we can perform export using dataump API as well

login as sysdba

DECLARE
hdnl NUMBER;
BEGIN
hdnl := DBMS_DATAPUMP.OPEN( operation => 'EXPORT', job_mode => 'SCHEMA', job_name=>null);
DBMS_DATAPUMP.ADD_FILE(handle => hdnl, filename => 'sample.dmp', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_dump_file);
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => 'exp.log', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_log_file); DBMS_DATAPUMP.METADATA_FILTER(hdnl,'SCHEMA_EXPR','IN (''TEST'')');
DBMS_DATAPUMP.START_JOB(hdnl);
END;
/

example :

SQL>
SQL> DECLARE
2 hdnl NUMBER;
BEGIN
hdnl := DBMS_DATAPUMP.OPEN( operation => 'EXPORT', job_mode => 'SCHEMA', job_name=>null);
DBMS_DATAPUMP.ADD_FILE(handle => hdnl, filename => 'sample.dmp', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_dump_file); 3
4 5 6 DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => 'exp.log', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_log_file); DBMS_DATAPUMP.METADATA_FILTER(hdnl,'SCHEMA_EXPR','IN (''TEST'')');
DBMS_DATAPUMP.START_JOB(hdnl);
END;
/ 7 8 9

PL/SQL procedure successfully completed.

SQL>
SQL>

Step 2: ADD RDS database tns entry in TNSNAMES.ora file also validate using tnsping

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = aws_rds_db_orcl_endpoint_name)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
(UR = A)
)
)

Step 3: Create public database link to using AWS rds database endpoint (create as per your requirement like public or private)

create public database link to_rds19c connect to "username" identified by "password"
USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = aws_rds_db_orcl_endpoint_name)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))';

SQL> create public database link to_rds19c connect to "username" identified by "password"
USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = aws_rds_db_orcl_endpoint_name)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))';
2
Database link created.

Step 4: validate created database link is working or not

SQL>
SQL>
SQL> select * from dual@to_rds19c;

D
-
X

SQL>
SQL>

Step 4: Transfer dumpfile to AWS RDS orcl database using database link using below API

BEGIN
DBMS_FILE_TRANSFER.PUT_FILE(
source_directory_object => 'DATA_PUMP_DIR',
source_file_name => 'sample.dmp',
destination_directory_object => 'DATA_PUMP_DIR',
destination_file_name => 'sample_copied.dmp',
destination_database => 'to_rds19c'
);
END;
/

SQL>
SQL> BEGIN
2 DBMS_FILE_TRANSFER.PUT_FILE(
source_directory_object => 'DATA_PUMP_DIR',
source_file_name => 'sample.dmp',
destination_directory_object => 'DATA_PUMP_DIR',
destination_file_name => 'sample_copied.dmp',
destination_database 3 => 'to_rds19c'
);
END;
4 5 6 7 8 9 10 /

PL/SQL procedure successfully completed.

SQL>
SQL>

Step 5: On AWS RDS database validate dumpfile which you transfer using below command

select * from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR')) order by mtime;

Step 6: create schema in target AWS database (orcl) with required privilege after that Perform import usig datapump API

DECLARE
hdnl NUMBER;
BEGIN
hdnl := DBMS_DATAPUMP.OPEN( operation => 'IMPORT', job_mode => 'SCHEMA', job_name=>null);
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => 'sample_copied.dmp', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_dump_file);
DBMS_DATAPUMP.ADD_FILE( handle => hdnl, filename => 'sample_imp.log', directory => 'DATA_PUMP_DIR', filetype => dbms_datapump.ku$_file_type_log_file);
DBMS_DATAPUMP.METADATA_FILTER(hdnl,'SCHEMA_EXPR','IN (''TEST'')');
DBMS_DATAPUMP.START_JOB(hdnl);
END;
/

Step 7: To monitor import log using below command

SELECT text FROM table(rdsadmin.rds_file_util.read_text_file('DATA_PUMP_DIR','sample_imp.log'))

* You have to enable the port for access in AWS security group

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/rds-ug.pdf

Tuesday, May 26, 2020

Simulating ORA-XXXX errors manually in database alert log

Generate ORA-XXX error manually in DB alert log

login as sysdba

SQL> exec dbms_system.ksdwrt(2,'ORA-00600: This is a test error message for monitoring and can be ignored.');

PL/SQL procedure successfully completed.

SQL>

In Alert_india.log

*************************************************************************
2020-05-26 13:45:21.335000 +00:00
ORA-00600: This is a test error message for monitoring and can be ignored.
2020-05-26 13:45:25.125000 +00:00
ORA-00600: This is a test error message for monitoring and can be ignored.

Tuesday, May 12, 2020

Create ASM diskgroup using ASMCA

Create ASM diskgroup using ASMCA

It will open the GUI window that helps to create diskgroup

after click on create give diskgroup name , redundancy and many more things as per requirement in my case I am creating FRA diskgroup with external redundancy

FRA disk group has been created