Monday, 21 June 2021

OCI - Clone Autonomous database

 

  • Login OCI console and go to autonomous database location 




  • Select Full clone option
NOTE: We can select refreshable clone as well. It is paid option and we can  refresh periodically also manually. We can reduce time expdp and impdp activity.



  • Provide password for admin user for upcoming clone database 


  • Cloning in progress we can select work request option to check progress


  • Clone autonomous database created successfully


  • Clone database status 






  • Remember after download wallet.  Create separate folder for each cloud database inside tns_admin directory for making autonomous database connections.



  • Terminate clone database 






Posted by at No comments:
Labels:

Thursday, 17 June 2021

Patch - Applying patch for upgrading database time zone from 32 to 35

OCI autonomous database having time zone 35 and on premises database having 32timezone. We face error on impdp.

This blog is about applying patch and upgrading time zone.

Error: ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 35 into a target database with TSTZ version 32.

On Premises database name : PDBTEST (standalone DB)

OCI Autonomous database name: ORCLADB 

  •  Importing impdp dump  from autonomous database to on premesis database

 D:\test\WINDOWS.X64_193000_db_home\bin>impdp \"sys/system123@pdbtest as sysdba\" directory=STAGING_DIR schemas=soe remap_schema=soe:soe2 dumpfile=adsoe01.dmp,adsoe02.dmp transform=segment_attributes:n encryption_pwd_prompt=yes
Import: Release 19.0.0.0.0 - Production on Tue Jun 8 15:43:26 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Encryption Password:
ORA-39002: invalid operation
ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 35 into a target database with TSTZ version 32.


  • On premises database timezone 

 SQL> SELECT * FROM V$TIMEZONE_FILE;
FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_32.dat              32          0
SQL>
  • Autonomous database timezone

 SQL> SELECT * FROM V$TIMEZONE_FILE;
FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_35.dat              35          0
  • Check doc id for patch information 

NOTE: Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches (Doc ID 412160.1)

Note 2676922.1 Applying the DSTv35 update for the Oracle Database

  • Set pre requestors for pre patch Apply on database

 

C:\Windows\system32>SET ORACLE_HOME=D:\test\WINDOWS.X64_193000_db_home
C:\Windows\system32>SET PATH=D:\test\WINDOWS.X64_193000_db_home\OPatch;%PATH%
C:\Windows\system32>SET ORACLE_SID=PDBTEST
C:\Windows\system32>SET PATH=%ORACLE_HOME%\perl\bin;%PATH%
C:\Windows\system32>cd D:\test\WINDOWS.X64_193000_db_home\OPatch
C:\Windows\system32>D:
D:\test\WINDOWS.X64_193000_db_home\OPatch>opatch version
OPatch Version: 12.2.0.1.15
OPatch succeeded.

C:\Users\varun\OneDrive\Desktop\oci\p31335037_190000_MSWIN-x86-64>opatch prereq CheckConflictAgainstOHWithDetail -ph ./31335037
Oracle Interim Patch Installer version 12.2.0.1.15
Copyright (c) 2021, Oracle Corporation.  All rights reserved.
PREREQ session
Oracle Home       : D:\test\WINDOWS.X64_193000_db_home
Central Inventory : C:\Program Files\Oracle\Inventory
   from           :
OPatch version    : 12.2.0.1.15
OUI version       : 12.2.0.7.0
Log file location : D:\test\WINDOWS.X64_193000_db_home\cfgtoollogs\opatch\opatch2021-06-08_16-50-57PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

C:\Users\varun\OneDrive\Desktop\oci\p31335037_190000_MSWIN-x86-64>cd 31335037
C:\Users\varun\OneDrive\Desktop\oci\p31335037_190000_MSWIN-x86-64\31335037>opatch apply -analyze
Oracle Interim Patch Installer version 12.2.0.1.15
Copyright (c) 2021, Oracle Corporation.  All rights reserved.
Oracle Home       : D:\test\WINDOWS.X64_193000_db_home
Central Inventory : C:\Program Files\Oracle\Inventory
   from           :
OPatch version    : 12.2.0.1.15
OUI version       : 12.2.0.7.0
Log file location : D:\test\WINDOWS.X64_193000_db_home\cfgtoollogs\opatch\opatch2021-06-08_16-52-14PM_1.log
Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   31335037

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
You are calling OPatch with -ocmrf option while this OPatch is generic, not being bundled with OCM. The -ocmrf option is being deprecated. Please remove it while calling OPatch.
Backing up files...
Applying interim patch '31335037' to OH 'D:\test\WINDOWS.X64_193000_db_home'
Users request no RAC file generation.  Do not create MP files.
Skip patching component oracle.oracore.rsf, 19.0.0.0.0 and its actions.
The actions are reported here, but are not performed.
ApplySession skipping inventory update.
Patch 31335037 successfully applied.
Log file location: D:\test\WINDOWS.X64_193000_db_home\cfgtoollogs\opatch\opatch2021-06-08_16-52-14PM_1.log
OPatch succeeded.
C:\Users\varun\OneDrive\Desktop\oci\p31335037_190000_MSWIN-x86-64\31335037>opatch apply
Oracle Interim Patch Installer version 12.2.0.1.15
Copyright (c) 2021, Oracle Corporation.  All rights reserved.
Oracle Home       : D:\test\WINDOWS.X64_193000_db_home
Central Inventory : C:\Program Files\Oracle\Inventory
   from           :
OPatch version    : 12.2.0.1.15
OUI version       : 12.2.0.7.0
Log file location : D:\test\WINDOWS.X64_193000_db_home\cfgtoollogs\opatch\opatch2021-06-08_16-56-04PM_1.log
Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   31335037
Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.
Backing up files...
Applying interim patch '31335037' to OH 'D:\test\WINDOWS.X64_193000_db_home'
Patching component oracle.oracore.rsf, 19.0.0.0.0...
Patch 31335037 successfully applied.
Log file location: D:\test\WINDOWS.X64_193000_db_home\cfgtoollogs\opatch\opatch2021-06-08_16-56-04PM_1.log
OPatch succeeded.
  • Shutdown database and upgrade time zone window.
 

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
Warning: You are no longer connected to ORACLE.
SQL> exit
C:\Users\varun\OneDrive\Desktop\oci\p31335037_190000_MSWIN-x86-64\31335037>sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 8 17:25:08 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> STARTUP UPGRADE;
ORACLE instance started.
Total System Global Area 5133824504 bytes
Fixed Size                  9277944 bytes
Variable Size             989855744 bytes
Database Buffers         4127195136 bytes
Redo Buffers                7495680 bytes
Database mounted.
Database opened.
SQL>
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2   l_tz_version PLS_INTEGER;
  3  BEGIN
  4   SELECT DBMS_DST.get_latest_timezone_version
  5   INTO l_tz_version
  6   FROM DUAL;
  7   DBMS_OUTPUT.PUT_LINE('l_tz_version=' || l_tz_version);
  8   DBMS_DST.BEGIN_UPGRADE(l_tz_version);
  9  END;
 10  /
l_tz_version=35
An upgrade window has been successfully started.
PL/SQL procedure successfully completed.
  • Shutdown database again startup normal mode , Upgrade  and check the timezone
 

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP;
ORACLE instance started.
Total System Global Area 5133824504 bytes
Fixed Size                  9277944 bytes
Variable Size             989855744 bytes
Database Buffers         4127195136 bytes
Redo Buffers                7495680 bytes
Database mounted.
Database opened.
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2   l_failures PLS_INTEGER;
  3  BEGIN
  4   DBMS_DST.UPGRADE_DATABASE(l_failures);
  5   DBMS_OUTPUT.PUT_LINE('DBMS_DST.upgrade_database : l_failures=' || l_failures);
  6   DBMS_DST.End_UPGRADE(l_failures);
  7   DBMS_OUTPUT.PUT_LINE('DBMS_DST.end_upgrade : l_failures=' || l_failures);
  8  END;
  9  /
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
Table list: "DVSYS"."AUDIT_TRAIL$"
Number of failures: 0
Table list: "DVSYS"."SIMULATION_LOG$"
Number of failures: 0
DBMS_DST.upgrade_database : l_failures=0
An upgrade window has been successfully ended.
DBMS_DST.end_upgrade : l_failures=0
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT * FROM v$timezone_file;
FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_35.dat              35          0
SQL>

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
PROPERTY_NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION
35

DST_SECONDARY_TT_VERSION
0
DST_UPGRADE_STATE
NONE

Posted by at No comments:
Labels:

Wednesday, 16 June 2021

OCI - Delete files from directory in autonomous database


 Delete files from directory in autonomous database


  • After performing expdp and impdp files remains in the directory.  Here we will clean files below example.

  • List existing files in directory , Here directory name is STAGING2
SQL> select * from DBMS_CLOUD.LIST_FILES('STAGING2');

OBJECT_NAME                         BYTES CHECKSUM                                 CREATED                                  LAST_MODIFIED
------------------------------ ---------- ---------------------------------------- ---------------------------------------- ----------------------------------------
adsoe_export.log                      472                                          07-JUN-21 01.57.39.000000 PM +00:00      14-JUN-21 10.06.07.000000 AM +00:00
adsoe01.dmp                        520192                                          07-JUN-21 01.57.39.000000 PM +00:00      07-JUN-21 01.58.38.000000 PM +00:00
adsoe02.dmp                     338264064                                          07-JUN-21 01.58.01.000000 PM +00:00      07-JUN-21 01.58.38.000000 PM +00:00
soe_export.log                       3877                                          14-JUN-21 10.27.58.000000 AM +00:00      14-JUN-21 10.29.00.000000 AM +00:00
soe01.dmp                          520192                                          14-JUN-21 10.27.58.000000 AM +00:00      14-JUN-21 10.28.58.000000 AM +00:00
soe02.dmp                       338214912                                          14-JUN-21 10.28.19.000000 AM +00:00      14-JUN-21 10.28.58.000000 AM +00:00
expsoe.log                            615                                          14-JUN-21 11.24.16.000000 AM +00:00      14-JUN-21 11.43.32.000000 AM +00:00

7 rows selected.

  • Remove particular file from directory

 

SQL> BEGIN
  2  UTL_FILE.FREMOVE('STAGING2','adsoe_export.log');
  3  COMMIT;
  4  END;
  5  /

PL/SQL procedure successfully completed.




  • Check again after removing  files 
SQL> select * from DBMS_CLOUD.LIST_FILES('STAGING2');

OBJECT_NAME                         BYTES CHECKSUM                                 CREATED                                  LAST_MODIFIED
------------------------------ ---------- ---------------------------------------- ---------------------------------------- ----------------------------------------
adsoe01.dmp                        520192                                          07-JUN-21 01.57.39.000000 PM +00:00      07-JUN-21 01.58.38.000000 PM +00:00
adsoe02.dmp                     338264064                                          07-JUN-21 01.58.01.000000 PM +00:00      07-JUN-21 01.58.38.000000 PM +00:00
soe_export.log                       3877                                          14-JUN-21 10.27.58.000000 AM +00:00      14-JUN-21 10.29.00.000000 AM +00:00
soe01.dmp                          520192                                          14-JUN-21 10.27.58.000000 AM +00:00      14-JUN-21 10.28.58.000000 AM +00:00
soe02.dmp                       338214912                                          14-JUN-21 10.28.19.000000 AM +00:00      14-JUN-21 10.28.58.000000 AM +00:00
expsoe.log                            615                                          14-JUN-21 11.24.16.000000 AM +00:00      14-JUN-21 11.43.32.000000 AM +00:00

6 rows selected.




  • Similarly remove other files as well 

SQL> BEGIN
  2  UTL_FILE.FREMOVE('STAGING2','adsoe02.dmp');
  3  COMMIT;
  4  END;
  5  /

PL/SQL procedure successfully completed.

SQL> BEGIN
  2  UTL_FILE.FREMOVE('STAGING2','soe01.dmp');
  3  COMMIT;
  4  END;
  5  /

PL/SQL procedure successfully completed.

  • Check the status of files in directory


SQL> BEGIN
  2  UTL_FILE.FREMOVE('STAGING2','soe02.dmp');
  3  COMMIT;
  4  END;
  5  /

PL/SQL procedure successfully completed.
SQL> select * from DBMS_CLOUD.LIST_FILES('STAGING2');

OBJECT_NAME                         BYTES CHECKSUM                                 CREATED                                  LAST_MODIFIED
------------------------------ ---------- ---------------------------------------- ---------------------------------------- ----------------------------------------
soe_export.log                       3877                                          14-JUN-21 10.27.58.000000 AM +00:00      14-JUN-21 10.29.00.000000 AM +00:00
expsoe.log                            615                                          14-JUN-21 11.24.16.000000 AM +00:00      14-JUN-21 11.43.32.000000 AM +00:00

SQL>




Posted by at No comments:
Labels:
Subscribe to: Posts (Atom)