Aşağıdaki adımlarla 11g olan bir db ortamın (AIX 7.2) kopyasını başka yeni bir makinada (AIX 7.2) 19c olarak oluşturacağız. Öncelikle yeni makinaya 19c grid’i, 19c db ve 11g db software’i kuracağız ve son patch’leri geçeceğiz. Ardından prod ortamda Rman duplicate başlatıp yeni dev ortamında db’in kopyasını oluşturacağız. Preupgrade’deki required’ları tamamladıktan sonra da 19c’ye db upgrade’ini yapacağız.
Son olarak da postupgrade işlemlerini yaparak bitirmiş olacağız.
In the following steps, we will create a copy of a 11g db (AIX 7.2) as 19c in another new machine (AIX 7.2). First of all, we will install 19c grid, 19c db and 11g db software on the new machine and apply the last patches. Then we will launch Rman duplicate in the prod env. and create a copy of db in the new dev env. After completing the required requirements in preupgrade, we will upgrade to 19c db. Finally, we will be finished by doing postupgrade operations.
1- Setup
2- Install 19c Grid
3- Install 19c Db software only
4- 19c Grid ve DB için 19.7 (Nisan 2020) lates patch’in geçilmesi
5- Install 11.2.0.4 db home software only
6- 11.2.0.4’e lates db patch’in (Nisan 2020) geçilmesi
7- Rman Duplicate ile prod db’den kopya db’nin oluşturulması
8- Preupgrade Check – Db upgrade’i öncesi preupgrade.jar’daki required’larin yapılması
9- Upgrade Oracle Database 11.2.0.4 to 19c
10- Postupgrade
1- Setup:
— Reference:
— 19c Grid insallation and Upgrade for IBM AIX:
— 19c (19.3) for AIX download -> AIX.PPC64_193000_db_home
— GI & DB Patch: “Patch 30899722 – GI Release Update 19.7.0.0.200414”
Oracle Database 19c Proactive Patch Information (Doc ID 2521164.1)
— OPatch 12.2.0.1.21 for DB 19.x releases (Apr 2020) -> p6880880_190000_AIX64-5L
NOT: Upgrade öncesi minumum geçilmesi gerekli patch’leri burdan kontrol edebilirsiniz:
“Patches to apply before upgrading Oracle GI and DB to 19c or downgrading to previous release (Doc ID 2539751.1)”
— Nisan 2020 11.2.0.4 db patch’ini geçiyoruz:
“Patch 30670774 – Oracle Database Patch Set Update 11.2.0.4.200414” -> p30670774_112040_AIX64-5L.zip
–AIX 7.2 kernel settings, required packages (MOS)
Oracle Database (RDBMS) on Unix AIX,HP-UX,Linux,Solaris and MS Windows Operating Systems Installation and Configuration Requirements Quick Reference (12.1/12.2/18c/19c) (Doc ID 1587357.1)
–preupgrade.jar
How to Download and Run Oracle’s Database Pre-Upgrade Utility (Doc ID 884522.1)
For Unix: preupgrade_19_cbuild_6_lf.zip
2- Install 19c Grid:
cd /setup unzip AIX.PPC64_193000_grid_home.zip vi /etc/hosts 10.10.10.10 devdbserver root@devdbserver /dev # ls -la *rhdisk* crw------- 1 oracle dba 19, 2 Dec 06 2017 rhdiskASMd01 crw------- 1 oracle dba 19, 3 Dec 06 2017 rhdiskASMd02 crw------- 1 oracle dba 19, 4 Dec 06 2017 rhdiskASMd03 crw------- 1 oracle dba 19, 5 Dec 06 2017 rhdiskASMd04 crw------- 1 oracle dba 19, 6 Dec 06 2017 rhdiskASMd05 crw------- 1 oracle dba 19, 9 May 01 13:43 rhdiskASMd06 crw------- 1 oracle dba 19, 7 Dec 06 2017 rhdiskASMr01 crw------- 1 oracle dba 19, 10 May 01 13:43 rhdiskOCR01 root@devdbserver /dev # ---- hem oracle hem grid için ortak BASE --- ORACLE_BASE=/oracle/app/oracle export ORACLE_BASE ORACLE_HOME=/oracle/app/grid export ORACLE_HOME --- ORACLE_BASE/../oraInventory olmali: /oracle/app/oraInventory vi /etc/oraInst.loc inventory_loc=/oracle/app/oraInventory inst_group=oinstall ---- 19c db home -------- export ORACLE_BASE=/oracle/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/19.0.0/dbhome_1 --- 11.2.0.4 db home --------- export ORACLE_BASE=/oracle/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1 mkdir -p /oracle/app/grid mkdir -p /oracle/app/19.0.0/grid mkdir -p /oracle/app/oracle mkdir -p /oracle/app/oraInventory chown -R oracle:oinstall /oracle chmod -R 775 /oracle chown -R oracle:oinstall /setup chmod -R 775 /setup mkgroup -A id=54322 dba mkgroup -A id=54328 asmoper mkgroup -A id=54329 asmadmin mkgroup -A id=54327 asmdba mkgroup -A id=54323 oper mkgroup -A id=54325 dgdba mkgroup -A id=54326 kmdba usermod -G dba,oinstall,asmoper,asmadmin,asmdba,oper,dgdba,kmdba oracle root@devdbserver / # id oracle uid=206(oracle) gid=204(oinstall) groups=54322(dba),54328(asmoper), 54329(asmadmin),54327(asmdba),54323(oper),54325(dgdba),54326(kmdba)
------------------------- profile --------------------------------
cd /home/oracle
cat .profile
vi .profile
set_11g_DEVDB()
{
export ORACLE_UNQNAME=DEVDB
export ORACLE_BASE=/oracle/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_SID=DEVDB
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P9
export LANG=en_US
export GRID_HOME=/oracle/app/grid
export PATH=/usr/sbin:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$HOME/bin:$PATH
export EXINIT="set ll=80000000"
alias oh='cd $ORACLE_HOME'
alias gh='cd $GRID_HOME'
alias sql='sqlplus "/ as sysdba"'
alias tns='cd $ORACLE_HOME/network/admin'
alias log="tail -100f /oracle/app/oracle/diag/rdbms/DEVDB/DEVDB/trace/alert_DEVDB.log"
alias logasm="tail -100f /oracle/app/oracle/diag/asm/+asm/+ASM/trace/alert_+ASM.log"
alias logtns="tail -100f /oracle/app/oracle/diag/tnslsnr/devdbserver/listener/trace/listener.log"
alias logcrsd="tail -100f /oracle/app/grid/log/devdbserver/crsd/crsd.log"
alias logcssd="tail -100f /oracle/app/grid/log/devdbserver/cssd/ocssd.log"
alias logohasd="tail -100f /oracle/app/grid/log/devdbserver/ohasd/ohasd.log"
alias logps="ps -ef | grep pmon"
}
set_19c_DEVDB()
{
export ORACLE_UNQNAME=DEVDB
export ORACLE_BASE=/oracle/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/19.0.0/dbhome_1
export ORACLE_SID=DEVDB
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P9
export LANG=en_US
export GRID_HOME=/oracle/app/grid
export PATH=/usr/sbin:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$HOME/bin:$PATH
export EXINIT="set ll=80000000"
alias oh='cd $ORACLE_HOME'
alias gh='cd $GRID_HOME'
alias sql='sqlplus "/ as sysdba"'
alias tns='cd $ORACLE_HOME/network/admin'
alias log="tail -100f /oracle/app/oracle/diag/rdbms/DEVDB/DEVDB/trace/alert_DEVDB.log"
alias logasm="tail -100f /oracle/app/oracle/diag/asm/+asm/+ASM/trace/alert_+ASM.log"
alias logtns="tail -100f /oracle/app/oracle/diag/tnslsnr/devdbserver/listener/trace/listener.log"
alias logcrsd="tail -100f /oracle/app/grid/log/devdbserver/crsd/crsd.log"
alias logcssd="tail -100f /oracle/app/grid/log/devdbserver/cssd/ocssd.log"
alias logohasd="tail -100f /oracle/app/grid/log/devdbserver/ohasd/ohasd.log"
alias logps="ps -ef | grep pmon"
}
alias oh='cd $ORACLE_HOME'
alias tns='cd $ORACLE_HOME/network/admin'
echo set_11g_DEVDB set_19c_DEVDB
------------------------------------------------------------------
root@devdbserver / # chmod -R 755 /setup root@devdbserver / # chown -R oracle:oinstall /setup root@devdbserver /setup/clone # ./rootpre.sh ./rootpre.sh output will be logged in /tmp/rootpre.out_20-05-10.20:27:27 Checking if group services should be configured.... Nothing to configure. grep X11Forwarding /etc/ssh/sshd_config X11Forwarding yes # X11Forwarding no stopsrc -s sshd startsrc -s sshd /usr/bin/X11/xauth root@devdbserver / # xauth list devdbserver/unix:10 MIT-MAGIC-COOKIE-1 60859f5bc6312557d790861f9511d78d devdbserver/unix:11 MIT-MAGIC-COOKIE-1 c634a00a1fa46dc80b27c6f5c480cfa0 root@devdbserver / # oracle@devdbserver </oracle/app/grid> xauth add devdbserver/unix:11 MIT-MAGIC-COOKIE-1 c634a00a1fa46dc80b27c6f5c480cfa0 oracle@devdbserver </oracle/app/grid> export DISPLAY=localhost:11.0 oracle@devdbserver </oracle/app/grid> xclock oracle@devdbserver </oracle/app/grid> ./gridSetup.sh External +DATA +RECO +OCR oracle@devdbserver </home/oracle> cd /oracle/app/grid/bin oracle@devdbserver </oracle/app/grid/bin> sqlplus / as sysasm SQL*Plus: Release 19.0.0.0.0 - Production on Thu May 14 16:32:46 2020 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> SELECT name,total_mb / 1024 AS total_gb,TRUNC (cold_used_mb / 1024) AS used_gb,ROUND (free_mb / 1024) AS free_gb FROM v$asm_diskgroup; NAME TOTAL_GB USED_GB FREE_GB ------------------------------ ---------- ---------- ---------- OCR 5 0 5 DATA 3000 0 3000 RECO 400 0 400 SQL> exit
3- Install 19c Db software only:
oracle@devdbserver </oracle/app/oracle/product/19.0.0/dbhome_1> unzip -oq /backup/setup/19c_db/AIX.PPC64_193000_db_home.zip root@devdbserver / # xauth list devdbserver/unix:14 MIT-MAGIC-COOKIE-1 7dc8abb54702db68f36f4eeb1ffe72a6 devdbserver/unix:10 MIT-MAGIC-COOKIE-1 a78c2f0eb6dde76eca7733f1f979cbdc root@devdbserver / # sudo su - oracle set_11g_DEVDB set_19c_DEVDB oracle@devdbserver </home/oracle> xauth add devdbserver/unix:10 MIT-MAGIC-COOKIE-1 a78c2f0eb6dde76eca7733f1f979cbdc oracle@devdbserver </home/oracle> export DISPLAY=localhost:10.0 oracle@devdbserver </home/oracle> xclock oracle@devdbserver </home/oracle> set_19c_DEVDB oracle@devdbserver </home/oracle> echo $ORACLE_HOME /oracle/app/oracle/product/19.0.0/dbhome_1 oracle@devdbserver </home/oracle> echo $ORACLE_SID DEVDB oracle@devdbserver </home/oracle> cd $ORACLE_HOME oracle@devdbserver </oracle/app/oracle/product/19.0.0/dbhome_1> ./runInstaller root@devdbserver /oracle/app/oracle/product/19.0.0/dbhome_1/clone/rootpre.sh /oracle/app/oracle/product/19.0.0/dbhome_1/clone/rootpre.sh output will be logged in /tmp/rootpre.out_20-05-20.17:35:00 Checking if group services should be configured.... Nothing to configure. root@devdbserver /oracle/app/oracle/product/19.0.0/dbhome_1/root.sh Performing root user operation. The following environment variables are set as: ORACLE_OWNER= oracle ORACLE_HOME= /oracle/app/oracle/product/19.0.0/dbhome_1 Enter the full pathname of the local bin directory: [/usr/local/bin]: The contents of "dbhome" have not changed. No need to overwrite. The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y Copying oraenv to /usr/local/bin ... The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y Copying coraenv to /usr/local/bin ... Entries will be added to the /etc/oratab file as needed by Database Configuration Assistant when a database is created Finished running generic part of root script. Now product-specific root actions will be performed. Oracle Trace File Analyzer (TFA - Standalone Mode) is available at : /oracle/app/oracle/product/19.0.0/dbhome_1/bin/tfactl Note : 1. tfactl will use TFA Service if that service is running and user has been granted access 2. tfactl will configure TFA Standalone Mode only if user has no access to TFA Service or TFA is not installed
4- 19c Grid ve Db için 19.7 lates patch’in geçilmesi:
—- Grid patch:
oracle@devdbserver </oracle/app/oracle/product/19.0.0/dbhome_1/OPatch> opatch version OPatch Version: 12.2.0.1.21 OPatch succeeded. --For Grid Infrastructure Home, as home user: $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /backup/setup/psu/30899722/30869156 $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /backup/setup/psu/30899722/30894985 $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /backup/setup/psu/30899722/30869304 $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /backup/setup/psu/30899722/ $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /backup/setup/psu/30899722/30898856 vi /tmp/patch_list_gihome.txt /backup/setup/psu/30899722/30869156 /backup/setup/psu/30899722/30894985 /backup/setup/psu/30899722/30869304 /backup/setup/psu/30899722/30898856 $ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseFile /tmp/patch_list_gihome.txt --For Database home, as home user: $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /backup/setup/psu/30899722/30869156 $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /backup/setup/psu/30899722/30894985 vi /tmp/patch_list_dbhome.txt /backup/setup/psu/30899722/30869156 /backup/setup/psu/30899722/30894985 $ORACLE_HOME/OPatch/opatch prereq CheckSystemSpace -phBaseFile /tmp/patch_list_dbhome.txt export PATH=$PATH:/oracle/app/grid/OPatch opatchauto apply /backup/setup/psu/30899722 -oh /oracle/app/grid root@devdbserver / # . oraenv ORACLE_SID = [root] ? +ASM The Oracle base has been set to /oracle/app/oracle root@devdbserver / # echo $ORACLE_HOME /oracle/app/grid root@devdbserver / # echo $ORACLE_SID +ASM root@devdbserver / # export PATH=$PATH:/oracle/app/grid/OPatch root@devdbserver / # opatchauto apply /backup/setup/psu/30899722 -oh /oracle/app/grid OPatchauto session is initiated at Wed May 20 19:26:49 2020 System initialization log file is /oracle/app/grid/cfgtoollogs/opatchautodb/systemconfig2020-05-20_07-27-28PM.log. Session log file is /oracle/app/grid/cfgtoollogs/opatchauto/opatchauto2020-05-20_07-27-53PM.log The id for this session is R3A4 Executing OPatch prereq operations to verify patch applicability on home /oracle/app/grid Patch applicability verified successfully on home /oracle/app/grid Bringing down CRS service on home /oracle/app/grid Prepatch operation log file location: /oracle/app/oracle/crsdata/devdbserver/crsconfig/hapatch_2020-05-20_07-29-51PM.log CRS service brought down successfully on home /oracle/app/grid Start applying binary patch on home /oracle/app/grid Successfully executed command: /usr/sbin/slibclean Binary patch applied successfully on home /oracle/app/grid Starting CRS service on home /oracle/app/grid Postpatch operation log file location: /oracle/app/oracle/crsdata/devdbserver/crsconfig/hapatch_2020-05-20_08-00-05PM.log CRS service started successfully on home /oracle/app/grid OPatchAuto successful. --------------------------------Summary-------------------------------- Patching is completed successfully. Please find the summary as follows: Host:devdbserver SIHA Home:/oracle/app/grid Version:19.0.0.0.0 Summary: ==Following patches were SUCCESSFULLY applied: Patch: /backup/setup/psu/30899722/30869156 Log: /oracle/app/grid/cfgtoollogs/opatchauto/core/opatch/opatch2020-05-20_19-30-46PM_1.log Patch: /backup/setup/psu/30899722/30869304 Log: /oracle/app/grid/cfgtoollogs/opatchauto/core/opatch/opatch2020-05-20_19-30-46PM_1.log Patch: /backup/setup/psu/30899722/30894985 Log: /oracle/app/grid/cfgtoollogs/opatchauto/core/opatch/opatch2020-05-20_19-30-46PM_1.log Patch: /backup/setup/psu/30899722/30898856 Log: /oracle/app/grid/cfgtoollogs/opatchauto/core/opatch/opatch2020-05-20_19-30-46PM_1.log OPatchauto session completed at Wed May 20 20:02:29 2020 Time taken to complete the session 35 minutes, 45 seconds root@devdbserver / # oracle@devdbserver </home/oracle> . oraenv ORACLE_SID = [DEVDB] ? +ASM oracle@devdbserver </home/oracle> opatch lspatches 30898856;TOMCAT RELEASE UPDATE 19.0.0.0.0 (30898856) 30894985;OCW RELEASE UPDATE 19.7.0.0.0 (30894985) 30869304;ACFS RELEASE UPDATE 19.7.0.0.0 (30869304) 30869156;Database Release Update : 19.7.0.0.200414 (30869156) OPatch succeeded. oracle@devdbserver </home/oracle>
— DB Patch:
oracle@devdbserver </home/oracle> set_19c_DEVDB oracle@devdbserver </home/oracle> echo $ORACLE_HOME /oracle/app/oracle/product/19.0.0/dbhome_1 export PATH=$PATH:/oracle/app/oracle/product/19.0.0/dbhome_1/OPatch cd /backup/setup/psu/30899722/30869156 opatch apply Patch 30869156 successfully applied. Sub-set patch [29517242] has become inactive due to the application of a super-set patch [30869156]. Please refer to Doc ID 2161861.1 for any possible further required actions. OPatch Session completed. Log file location: /oracle/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/opatch/opatch2020-05-20_20-10-54PM_1.log oracle@devdbserver </home/oracle> opatch lspatches 30869156;Database Release Update : 19.7.0.0.200414 (30869156) 29585399;OCW RELEASE UPDATE 19.3.0.0.0 (29585399)
5- install 11.2.0.4 db home software only:
oracle@devdbserver </backup/setup/11204_db> ls -la total 5805752 drwxrwxrwx 3 root system 256 May 15 12:32 . drwxrwxrwx 3 root system 256 May 14 18:59 .. drwxr-xr-x 8 oracle oinstall 4096 Oct 09 2013 database -rwxr-xr-x 1 oracle oinstall 1801653734 May 15 12:25 p13390677_112040_AIX64-5L_1of7.zip -rwxr-xr-x 1 oracle oinstall 1170882875 May 15 12:27 p13390677_112040_AIX64-5L_2of7.zip oracle@devdbserver </backup/setup/11204_db> oracle@devdbserver </backup/setup/11204_db/database> export ORACLE_BASE=/oracle/app/oracle oracle@devdbserver </backup/setup/11204_db/database> export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1 oracle@devdbserver </backup/setup/11204_db/database> ./runInstaller root@devdbserver //oracle/app/oracle/product/11.2.0/dbhome_1/root.sh Performing root user operation for Oracle 11g The following environment variables are set as: ORACLE_OWNER= oracle ORACLE_HOME= /oracle/app/oracle/product/11.2.0/dbhome_1 Enter the full pathname of the local bin directory: [/usr/local/bin]: The file "dbhome" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y Copying dbhome to /usr/local/bin ... The file "oraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y Copying oraenv to /usr/local/bin ... The file "coraenv" already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y Copying coraenv to /usr/local/bin ... Entries will be added to the /etc/oratab file as needed by Database Configuration Assistant when a database is created Finished running generic part of root script. Now product-specific root actions will be performed. Finished product-specific root actions. oracle@devdbserver </home/oracle> . oraenv ksh: oraenv: not found. oracle@devdbserver </home/oracle> which oraenv which: 0652-141 There is no oraenv in /usr/sbin /oracle/app/oracle/product/19.0.0/dbhome_1/bin /oracle/app/oracle/product/19.0.0/dbhome_1/OPatch /home/oracle/bin /usr/bin /etc /usr/sbin /usr/ucb /home/oracle/bin /usr/bin/X11 /sbin .. oracle@devdbserver </home/oracle> cd /usr/local/bin oracle@devdbserver </usr/local/bin> ls -la total 40 drwxr-xr-x 2 root system 256 Dec 07 2017 . drwxr-xr-x 3 root system 256 Dec 07 2017 .. -rwxr-xr-x 1 oracle system 5778 May 15 12:49 coraenv -rwxr-xr-x 1 oracle system 2415 May 15 12:49 dbhome -rwxr-xr-x 1 oracle system 6183 May 15 12:49 oraenv oracle@devdbserver </usr/local/bin> cd oracle@devdbserver </home/oracle> cat .profile PATH=/usr/bin:/etc:/usr/sbin:/usr/ucb:$HOME/bin:/usr/bin/X11:/sbin:. --- /usr/local/bin eklendi oracle@devdbserver </home/oracle> vi .profile PATH=/usr/bin:/usr/local/bin:/etc:/usr/sbin:/usr/ucb:$HOME/bin:/usr/bin/X11:/sbin:. oracle@devdbserver </home/oracle> . oraenv ORACLE_SID = [DEVDB] ? +ASM The Oracle base remains unchanged with value /oracle/app/oracle oracle@devdbserver </home/oracle> sqlplus / as sysasm
6- 11.2.0.4’e lates db patch’in (Nisan 2020) geçilmesi:
oracle@devdbserver </home/oracle> opatch lspatches 30670774;Database Patch Set Update : 11.2.0.4.200414 (30670774) 24701840; OPatch succeeded. opatch lsinventory | grep 22646198 SQL> set lines 140 pages 100 SQL> col Comp_name Format a40 SQL> col Status Format a12 SQL> select Comp_name, status, Version From Dba_Registry order by Comp_name; COMP_NAME STATUS VERSION ---------------------------------------- ------------ ------------------------------ JServer JAVA Virtual Machine VALID 11.2.0.4.0 OLAP Analytic Workspace VALID 11.2.0.4.0 OLAP Catalog VALID 11.2.0.4.0 OWB VALID 11.2.0.4.0 Oracle Application Express VALID 3.2.1.00.12 Oracle Database Catalog Views VALID 11.2.0.4.0 Oracle Database Java Packages VALID 11.2.0.4.0 Oracle Database Packages and Types VALID 11.2.0.4.0 Oracle Enterprise Manager VALID 11.2.0.4.0 Oracle Expression Filter VALID 11.2.0.4.0 Oracle Multimedia VALID 11.2.0.4.0 Oracle OLAP API VALID 11.2.0.4.0 Oracle Real Application Clusters VALID 11.2.0.4.0 Oracle Rules Manager VALID 11.2.0.4.0 Oracle Text VALID 11.2.0.4.0 Oracle Workspace Manager VALID 11.2.0.4.0 Oracle XDK VALID 11.2.0.4.0 Oracle XML Database VALID 11.2.0.4.0 Spatial VALID 11.2.0.4.0 19 rows selected. SQL>
7- Rman duplicate ile prod db’den kopya db’in oluşturulması:
-- proddan
SQL> create pfile='/tmp/prod_pfile.txt' from spfile='+ASM_DG01/proddbserver/spfilePRODDB.ora';
cat /tmp/prod_pfile.txt
cd /oracle/app/oracle/product/11.2.0/dbhome_1/dbs
vi initDEVDB.ora
*.audit_file_dest='/oracle/app/oracle/admin/DEVDB/adump'
*.background_dump_dest='/oracle/app/diag/rdbms/DEVDB/DEVDB/trace'
*.cluster_database=FALSE
*.compatible='11.2.0.4.0'
*.cursor_sharing='FORCE'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_name='DEVDB'
*.db_unique_name='DEVDB'
*.db_recovery_file_dest_size=644245094400
*.db_recovery_file_dest='+RECO'
*.db_file_name_convert='+ASM_DG01','+DATA'
*.diagnostic_dest='/oracle/app/oracle'
*.log_archive_format='DEVDB%t_%s_%r.arc'
*.undo_retention=21600
*.undo_tablespace='UNDOTBS1'
SQL> startup nomount pfile='/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initDEVDB.ora';
ORA-32006: BACKGROUND_DUMP_DEST initialization parameter has been deprecated
ORACLE instance started.
Total System Global Area 242208768 bytes
Fixed Size 2245184 bytes
Variable Size 184552896 bytes
Database Buffers 50331648 bytes
Redo Buffers 5079040 bytes
SQL>
oracle@proddbserver </oracle/app/oracle/product/11.2.0/dbhome_1/dbs> scp orapwPRODDB oracle@10.10.10.10:/oracle/app/oracle/product/11.2.0/dbhome_1/dbs
The authenticity of host '10.10.10.10 (10.10.10.10)' can't be established.
ECDSA key fingerprint is ad:e2:a8:49:10:d4:fe:61:6a:31:a6:0d:2f:1d:cc:45.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.10.10.10' (ECDSA) to the list of known hosts.
oracle@10.10.10.10's password:
orapwPRODDB 100% 2560 2.5KB/s 00:00
oracle@proddbserver </oracle/app/oracle/product/11.2.0/dbhome_1/dbs>
oracle@devdbserver </oracle/app/oracle/product/11.2.0/dbhome_1/dbs> mv orapwPRODDB orapwDEVDB
/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin
vi tnsnames.ora
DEVDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.10)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DEVDB)
)
)
/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin
vi listener.ora
LISTENER_DEVDB =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.10)(PORT =1522))
)
)
SID_LIST_LISTENER_DEVDB =
(SID_LIST =
(SID_DESC =
(SID_NAME = DEVDB)
(ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)
)
)
lsnrctl start LISTENER_DEVDB
lsnrctl stat LISTENER_DEVDB
oracle@devdbserver </oracle/app/oracle/product/11.2.0/dbhome_1/network/admin> lsnrctl start LISTENER_DEVDB
LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 15-MAY-2020 19:49:22
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Starting /oracle/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
System parameter file is /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /oracle/app/oracle/diag/tnslsnr/devdbserver/listener_DEVDB/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.10)(PORT=1522)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.10)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias LISTENER_DEVDB
Version TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
Start Date 15-MAY-2020 19:49:23
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP ON
Listener Parameter File /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /oracle/app/oracle/diag/tnslsnr/devdbserver/listener_DEVDB/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.10.10.10)(PORT=1522)))
Services Summary...
Service "DEVDB" has 1 instance(s).
Instance "DEVDB", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
tnsping DEVDB
--- dest'te:
oracle@devdbserver </oracle/app/oracle/product/11.2.0/dbhome_1/network/admin> tnsping DEVDB
TNS Ping Utility for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 15-MAY-2020 19:50:04
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.10)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DEVDB)))
OK (0 msec)
--- source'da:
oracle@proddbserver </oracle/app/oracle/product/11.2.0/dbhome_1/network/admin> tnsping DEVDB
TNS Ping Utility for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 15-MAY-2020 20:08:29
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.10)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DEVDB)))
OK (90 msec)
-- $ORACLE_HOME/network/admin/sqlnet.ora’ya timeout almamasi için "SQLNET.EXPIRE_TIME = 10" parametresi eklendi.
--- source'da /home/oracle altında:
vi rman_DEVDB.scpt
export ORACLE_SID=DEVDB
export ORACLE_HOME=/oracle/app/oracle/product/11.2.0/dbhome_1
export PATH=$PATH1:$ORACLE_HOME/bin
rman target sys/***@PRODDB auxiliary sys/***@DEVDB msglog /home/oracle/rman_backup.log cmdfile=/home/oracle/backup_DEVDB.cmd
cat backup_DEVDB.cmd
run{
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
allocate channel c5 device type disk ;
allocate channel c6 device type disk ;
allocate channel c7 device type disk ;
allocate channel c8 device type disk ;
allocate channel c9 device type disk ;
allocate channel c10 device type disk ;
allocate channel c11 device type disk ;
allocate channel c12 device type disk ;
allocate auxiliary channel c13 device type disk ;
allocate auxiliary channel c14 device type disk ;
allocate auxiliary channel c15 device type disk ;
allocate auxiliary channel c16 device type disk ;
allocate auxiliary channel c17 device type disk ;
allocate auxiliary channel c18 device type disk ;
allocate auxiliary channel c19 device type disk ;
allocate auxiliary channel c20 device type disk ;
allocate auxiliary channel c21 device type disk ;
allocate auxiliary channel c22 device type disk ;
allocate auxiliary channel c23 device type disk ;
allocate auxiliary channel c24 device type disk ;
DUPLICATE TARGET DATABASE TO DEVDB FROM ACTIVE DATABASE NOFILENAMECHECK;
}
nohup /home/oracle/rman_DEVDB.scpt &
oracle@proddbserver </home/oracle> jobs
[1]+ Running nohup /home/oracle/rman_DEVDB.scpt &
oracle@proddbserver </home/oracle>
tail -100f /home/oracle/rman_backup.log
--init file'a controlfile'lar eklendi
oracle@devdbserver </oracle/app/oracle/product/11.2.0/dbhome_1/dbs> vi initDEVDB.ora
*.control_files='+DATA/DEVDB/controlfile/current.257.1040643749', '+RECO/DEVDB/controlfile/current.257.1040643749'
--alertlog'a düsen ora600 hatalardan dolayi DST patch'i geçildi, düzeldi.
ORA-00600: internal error code, arguments: [qcisSetPlsqlCtx:tzi init]
'catrelod.sql' Failed with ORA-00600: Internal Error Code, Arguments: [qcisSetPlsqlCtx:Tzi Init] and Alert Log Showing : "Failed to Find Timezone Data File # 14 (DST_4)" (Doc ID 1446710.1)
create spfile='+DATA' from pfile='/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initDEVDB.ora';
oracle@devdbserver </oracle/app/oracle/product/11.2.0/dbhome_1/dbs> cat initDEVDB.ora
spfile='+DATA/DEVDB/PARAMETERFILE/spfile.362.1040675463'
oracle@devdbserver </oracle/app/oracle/product/11.2.0/dbhome_1/dbs>
alter system reset db_file_name_convert scope=spfile;
8- Preupgrade Check – Db upgrade’i öncesi preupgrade.jar’daki required’ların yapılması:
— INSTALLATION & UPGRADE GUIDE ——
— Database Installation Guide for IBM AIX on POWER Systems (64-bit)
— Grid Infrastructure Installation and Upgrade Guide for IBM AIX on POWER Systems (64-Bit)
———————- UPGRADE PRECHECK ——————————————–
****** Oracle 19c – Complete Checklist for Manual Upgrades to Non-CDB Oracle Database 19c (Doc ID 2539778.1) ****
–Requirements and recommendations for source database:
•Take a cold or hot back up of the source database.
•Disable any custom triggers that would get executed before / after DDL. You can enable it once upgrade is completed.
•If your source version’s time zone is already higher than the destination’s home, then apply the matching or any higher
time zone patch to your destination home before upgrade. For latest DST patch refer to Note 412160.1
•No INVALID object / Components in Source
* Desupport of Oracle Multimedia
* Desupport of Oracle Streams
•IF APEX is installed then it is recommended to upgrade APEX in the source DB first before upgrading DB. Refer to Note 1088970.1
•Execute Preupgrade scripts before upgrade and review the preupgrade log for any issues.
•Execute dbupgdiag.sql (refer Note 556610.1 to download this script) and verify whether there are any INVALID components
or objects owned by SYS/SYSTEM. If any, fix them before proceeding to upgrade the database. You can execute utlrp.sql
multiple times to VALIDate them, if still objects are INVALID, create a service request with Oracle support.
–Requirements and recommendations for target ORACLE_HOME:
•Download and install latest RU if any
•Copy spfile or pfile from source ORACLE_HOME (under $ORACLE_HOME/dbs) to target home
* Remove any _ (underscore) parameter, obsolete and deprecated parameters in pfile
* Note min value of COMPATIBLE parameter to upgrade 19c is “11.2.0”, make sure you have COMPATIBLE
parameter is set to 11.2.0 or greater
•Review patch recommendations as given in the article “Patches to apply before upgrading Oracle GI and DB to 19c
(Doc ID 2539751.1)”
•Apply patch 29213893 on target ORACLE_HOME to avoid ORA-01422 error – refer: Database Upgrade to 12.2, 18c, 19c
fails with ORA-01422, ORA-06512 for SYS.DBMS_STATS (Doc ID 2525596.1)
–Pre-upgrade checks:
Clean up database:
•Empty the recycle bin (SQL> PURGE DBA_RECYCLEBIN)
•Check for INVALID objects in SYS and SYSTEM
•Check for duplicate objects in SYS and SYSTEM
•Check for INVALID, mandatory, obsolete components
Note: These will be taken care by preupgrade.jar.
Check materialized views:
•Check the status of all materialized views (MV), and refresh any materialized views that are not fresh.
•Check the size of your materialized view logs. If any materialized view logs have non-zero rows, then refresh
the base table materialized views.
•Check the size of direct loader logs and PMOP logs (partition maintenance operation logs). If any direct loader
logs or PMOP logs have non-zero rows, then refresh the MVs indicated by the logs
•Before upgrading Oracle Database, you must wait until all materialized views have completed refreshing.
Run the following SQL query:
SQL> SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;
Note: These will be taken care by preupgrade.jar. Review the preupgrade logs
——————————————————————————————————————-
set lines 140 pages 100
col Comp_name Format a40
col Status Format a12
select Comp_name, status, Version From Dba_Registry order by Comp_name;
$Earlier_release_Oracle_home/jdk/bin/java -jar $New_release_Oracle_home
/rdbms/admin/preupgrade.jar [FILE|TERMINAL] [TEXT|XML] [DIR output_dir]
---- 19c db home --------
export ORACLE_BASE=/oracle/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/19.0.0/dbhome_1
--- 11.2.0.4 db home ---------
export ORACLE_BASE=/oracle/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
$ORACLE_BASE/product/11.2.0/dbhome_1/jdk/bin/java -jar /backup/setup/preupgrade/preupgrade.jar TERMINAL TEXT DIR /backup/setup/preupgrade
-- Remove the EM repository:
oracle@devdbserver </backup/setup/19c_db> cp rdbms/admin/emremove.sql $ORACLE_HOME/rdbms/admin
PURGE DBA_RECYCLEBIN
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
-- all custom triggers disabled.
select 'alter trigger '||owner||'.'||trigger_name||' disable;' from dba_triggers where status='ENABLED'
AND owner NOT IN ('SYS', 'SYSTEM','SYSMAN','MDSYS','XDB','OLAPSYS','APEX_030200','FLOWS_FILES');
alter system set "_system_trig_enabled"=false scope=spfile;
-- dblink varsa tüm pass'leri ile kayit altina al:
SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)
||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING
'''||L.HOST||''''
||chr(10)||';' TEXT
FROM SYS.LINK$ L, SYS.USER$ U
WHERE L.OWNER# = U.USER#;
SELECT version FROM v$timezone_file;
-- 28
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
--DST_PRIMARY_TT_VERSION 28
--DST_SECONDARY_TT_VERSION 0
--DST_UPGRADE_STATE NONE
-- downgrade ihtimali olursa diye controlfile'in yedeği alındı:
alter database backup controlfile to trace as '/home/oracle/controlfile.ctl';
--parameters saklandı:
parameters_last.txt"
-- çıktıları saklandı:
SELECT * FROM dba_data_files;
SELECT * FROM v$logfile;
-- hidden parametre yok, fixup ile temizlendi:
SQL> SELECT name, value from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\' order by name;
no rows selected
oracle@devdbserver </> $ORACLE_BASE/product/11.2.0/dbhome_1/jdk/bin/java -jar /backup/setup/preupgrade/preupgrade.jar TERMINAL TEXT DIR /backup/setup/preupgrade <
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 6 on 2020-05-20T17:08:46
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: DEVDB
Container Name: Not Applicable in Pre-12.1 database
Container ID: Not Applicable in Pre-12.1 database
Version: 11.2.0.4.0
DB Patch Level: PSU 11.2.0.4.200414
Compatible: 11.2.0.4.0
Blocksize: 8192
Platform: AIX-Based Systems (64-bit)
Timezone File: 28
Database log mode: ARCHIVELOG
Readonly: FALSE
Edition: EE
==============
BEFORE UPGRADE
==============
REQUIRED ACTIONS
================
None
9- Upgrade Oracle Database 11.2.0.4 to 19c:
cp /oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/*.ora /oracle/app/oracle/product/19.0.0/dbhome_1/network/admin oracle@devdbserver </oracle/app/oracle/product/19.0.0/dbhome_1/network/admin> vi sqlnet.ora SQLNET.ALLOWED_LOGON_VERSION_SERVER=11 cp /oracle/app/oracle/product/11.2.0/dbhome_1/dbs/orapwDEVDB /oracle/app/oracle/product/19.0.0/dbhome_1/dbs/ cp /oracle/app/oracle/product/11.2.0/dbhome_1/dbs/initDEVDB.ora /oracle/app/oracle/product/19.0.0/dbhome_1/dbs/ oracle@devdbserver </oracle/app/oracle/product/19.0.0/dbhome_1/dbs> cat initDEVDB.ora spfile='+DATA/DEVDB/PARAMETERFILE/spfile.362.1040675463' cat /etc/oratab DEVDB:/oracle/app/oracle/product/11.2.0/dbhome_1:N oracle@devdbserver </home/oracle> lsnrctl stat LSNRCTL for IBM/AIX RISC System/6000: Version 19.0.0.0.0 - Production on 21-MAY-2020 10:10:53 Copyright (c) 1991, 2020, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for IBM/AIX RISC System/6000: Version 19.0.0.0.0 - Production Start Date 20-MAY-2020 22:06:41 Uptime 0 days 12 hr. 4 min. 12 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /oracle/app/grid/network/admin/listener.ora Listener Log File /oracle/app/oracle/diag/tnslsnr/devdbserver/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=devdbserver)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "+ASM" has 1 instance(s). Instance "+ASM", status READY, has 1 handler(s) for this service... Service "+ASM_DATA" has 1 instance(s). Instance "+ASM", status READY, has 1 handler(s) for this service... Service "+ASM_OCR" has 1 instance(s). Instance "+ASM", status READY, has 1 handler(s) for this service... Service "+ASM_RECO" has 1 instance(s). Instance "+ASM", status READY, has 1 handler(s) for this service... The command completed successfully oracle@devdbserver </home/oracle> oracle@devdbserver </oracle/app/oracle/product/19.0.0/dbhome_1/network/admin> vi tnsnames.ora LISTENER_DEVDB = (ADDRESS = (PROTOCOL = TCP)(HOST =devdbserver)(PORT = 1521)) DEVDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =devdbserver)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = DEVDB) ) ) oracle@devdbserver </oracle/app/oracle/product/19.0.0/dbhome_1/network/admin> alter system set local_listener='LISTENER_DEVDB' scope=both; ALTER SYSTEM SET DISPATCHERS="(PROTOCOL=tcp)(LISTENER=LISTENER_DEVDB)" scope=both; alter system register; show parameter local_listener
./dbua
10- POST UPGRADE:
---------postupgrade_fixups.sql: SQL> @/backup/setup/preupgrade/postupgrade_fixups.sql Session altered. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. Package created. No errors. Package body created. PL/SQL procedure successfully completed. No errors. Package created. No errors. Package body created. No errors. Executing Oracle POST-Upgrade Fixup Script Auto-Generated by: Oracle Preupgrade Script Version: 19.0.0.0.0 Build: 6 Generated on: 2020-05-20 20:59:29 For Source Database: DEVDB Source Database Version: 11.2.0.4.0 For Upgrade to Version: 19.0.0.0.0 Preup Preupgrade Action Issue Is Number Preupgrade Check Name Remedied Further DBA Action ------ ------------------------ ---------- -------------------------------- 9. depend_usr_tables YES None. 10. awr_dbids_present NO Manual fixup recommended. 11. old_time_zones_exist YES None. 12. dir_symlinks YES None. 13. post_dictionary YES None. 14. post_fixed_objects NO Informational only. Further action is optional. 15. upg_by_std_upgrd NO Informational only. Further action is optional. The fixup scripts have been run and resolved what they can. However, there are still issues originally identified by the preupgrade that have not been remedied and are still present in the database. Depending on the severity of the specific issue, and the nature of the issue itself, that could mean that your database upgrade is not fully complete. To resolve the outstanding issues, start by reviewing the postupgrade_fixups.sql and searching it for the name of the failed CHECK NAME or Preupgrade Action Number listed above. There you will find the original corresponding diagnostic message from the preupgrade which explains in more detail what still needs to be done. PL/SQL procedure successfully completed. Session altered.
— Post step of 19c PSU (grid & db):
oracle@devdbserver </oracle/app/oracle/product/19.0.0/dbhome_1/OPatch> ./datapatch -verbose SQL Patching tool version 19.7.0.0.0 Production on Thu May 21 10:52:44 2020 Copyright (c) 2012, 2020, Oracle. All rights reserved. Log file for this invocation: /oracle/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_12517880_2020_05_21_10_52_44/sqlpatch_invocation.log Connecting to database...OK Gathering database info...done Bootstrapping registry and package to current versions...done Determining current state...done Current state of interim SQL patches: No interim patches found Current state of release update SQL patches: Binary registry: 19.7.0.0.0 Release_Update 200320014235: Installed SQL registry: Applied 19.7.0.0.0 Release_Update 200320014235 successfully on 20-MAY-20 11.40.44.680187 PM Adding patches to installation queue and performing prereq checks...done Installation queue: No interim patches need to be rolled back No release update patches need to be installed No interim patches need to be applied SQL Patching tool complete on Thu May 21 10:53:37 2020 oracle@devdbserver
mv /oracle/app/oracle/product/11.2.0/dbhome_1 /oracle/app/oracle/product/11.2.0/dbhome_1.old
-- Remove 11g env from .profile
oracle@devdbserver </home/oracle> sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu May 21 10:59:56 2020
Version 19.7.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0
SQL> select version,name,open_mode from v$instance,v$database;
VERSION NAME OPEN_MODE
----------------- --------- --------------------
19.0.0.0.0 DEVDB READ WRITE
SQL> select TZ_VERSION from registry$database;
TZ_VERSION
----------
32
SQL>
select 'alter trigger '||owner||'.'||trigger_name||' enable;' from dba_triggers where status='DISABLED'
AND owner NOT IN ('SYS', 'SYSTEM','SYSMAN','MDSYS','XDB','OLAPSYS','APEX_030200','FLOWS_FILES','WMSYS');
alter system set "_system_trig_enabled"=true scope=spfile;
@?/rdbms/admin/utlrp.sql
SELECT scn, database_incarnation#, guarantee_flashback_database, storage_size, time, name FROM gv$restore_point;
-- 7882389475980 2 YES 7496892416 20/05/20 22:12:41,000000000 GRP_1590001961090
SQL> drop RESTORE POINT GRP_1590001961090;
Restore point dropped.
SELECT name,
total_mb / 1024 AS total_gb,
TRUNC (cold_used_mb / 1024) AS used_gb,
ROUND (free_mb / 1024) AS free_gb
FROM v$asm_diskgroup;
--DATA 3000 2314 686
--OCR 5 0 5
--RECO 400 25 374
Finally, don’t forget to take rman backup 🙂
Orhan Eripek's Oracle Blog 