Srisha's blog

Thursday, February 18, 2021

Database upgrade from 12c to 19c(Manual method)

1. check the database compatibility.

2. Take full backup of database using RMAN.

3. Run pre-upgrade script

Example:

$/u01/app/oracle/product/12.1.0.2/dbhome2/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome1/rdbms/admin/preupgrade.jar DIR /tmp/preupgrade

-------log-----------------

==================

PREUPGRADE SUMMARY

==================

/tmp/preupgrade/preupgrade.log

/tmp/preupgrade_fixups.sql

/tmp/postupgrade_fixups.sql

Execute fixup scripts across the entire CDB:

Before upgrade:

1. Execute preupgrade fixups with the below command

$ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp/preupgrade/ -b preup_db01 /nfs/dpz/sb/preupgrade/preupgrade_fixups.sql

2. Review logs under /tmp/preupgrade/

After the upgrade:

1. Execute postupgrade fixups with the below command

$ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp/preupgrade/ -b postup_db01 /tmp/preupgrade/postupgrade_fixups.sql

2. Review logs under /tmp/preupgrade/

-----------------------------------------------------------------

4. Fix issues reported in preupgrade log.

5. purge recyclebin.

6. compile invalid objects usign utlrp.sql

.$ORACLE_HOME/rdbms/admin/utlrp.sql( in container, all pdbs)

--check any invalis objects in sys,system after that. apex upgarde to 18c

7. If any issues reported about APEX version in pregupgardefixup log, upgrade APEX version in the database.

check current APEX version:

Select Comp_name, status, Version

From Dba_Registry

where comp_id='APEX';

Download recommended version from oracle site.

--upgrade process for apex--

cd /nfs/dpz/sb/-- downlaoded place

cd /nfs/dpz/sb/apex

sqlplus /as sysdba

SQL> @apexins.sql SYSAUX SYSAUX TEMP /i/

After APEX version upgrade, check if APX version.

Example:

SQL> Select Comp_name, status, Version

From Dba_Registry

where comp_id='APEX'; 2 3

COMP_NAME

--------------------------------------------------------------------------------

STATUS

--------------------------------------------------------------------------------

VERSION

--------------------------------------------------------------------------------

Oracle Application Express

VALID

20.2.0.00.20

8. EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;

9. TEMP tablespace should be minimum 150MB for container.increase to 1GB

10. .Remove parallel_adaptive_multi_user parameter-deprecated-- need to be false

11. stop LISTENER

lsnrctl stop LISTENER

12. Check if flashback is on in database , if not turn it on,

13. create guaranteed restore point in 12c database.

14. shutdown the database

15. Copy parameter files to 19c home.

Example:

[user@erver01 ~]$ cp /u01/app/oracle/product/12.1.0.2/dbhome2/dbs/orpw /u01/app/oracle/product/19.3.0/dbhome1/dbs/

[user@erver01 ~]$ cp $ORACLE_HOME/dbs/spfileoradev.ora /u01/app/oracle/product/19.3.0/dbhome1/dbs/

[user@erver01 ~]$ cp $ORACLE_HOME/network/admin/listener.ora /u01/app/oracle/product/19.3.0/dbhome1/network/admin

16. start database in upgrade mode.

select name,open_mode,cdb,version,status from v$database,v$instance;

SQL> CONNECT / AS SYSDBA

Start the CDB in upgrade mode:

SQL> startup upgrade

Start the instance by issuing the following command in SQL*Plus:

SQL> alter pluggable database all open upgrade;

18.start the upgrade process

cd /u01/app/oracle/product/19.3.0/dbhome1/dbs/

nohup ./dbupgrade &

19. compile any invalid objects. execute utlrp.sql

cd /u01/app/oracle/product/19.3.0/dbhome1/rdbms/admin

SQL>utlrp.sql

check for any invalid objects after compilation

.select count(*) from dba_objects where status='INVALID';

20.Upgrade timezone if it is recommened in postfixup sccripts.

cd /u01/app/oracle/product/19.3.0/dbhome1/rdbms/admin

SQL>utltz_upg_check.sql

SQL>utltz_upg_apply.sql

SELECT version FROM v$timezone_file;

20.Execute utlusts.sql

cd /u01/app/oracle/product/19.3.0/dbhome1/rdbms/admin

SQL>utlusts.sql

21.Execute Catuppst.sql

cd /u01/app/oracle/product/19.3.0/dbhome1/rdbms/admin

SQL>Catuppst.sql

22.Check postfixup_upgrade.sql for any recommendations which is executed in step3.

23. DROP guaranteed restore point which is created before upgrade.

24. change database compatibility parameter (this needs db bounce)

ALTER SYSTEM SET COMPATIBLE = '19.0.0' SCOPE=SPFILE;

25.check if /etc/oratab file is updated with new version.

26. Take full backup of database.

Tuesday, June 11, 2019

Create oracle home 12.2 in X5

Login to node1 (dbserver01) as root. change directory to '/opt/oracle/oak/bin', then issue

oakcli create dbhome -version 12.2.0.1.180417

[ root@dbserver01 : Mon Jun 10, 03:34 PM : /root ]

$ cd /opt/oracle/oak/bin

[ root@dbserver01 : Mon Jun 10, 03:34 PM : /opt/oracle/oak/bin ]

$ ./oakcli create dbhome -version 12.2.0.1.180417

INFO: 2019-06-10 15:34:44: Please check the logfile '/opt/oracle/oak/log/dbserver01/tools/12.2.1.4.0/createdbhome_74579.log' for more details

Please enter the 'SYSASM' password : (During deployment we set the SYSASM password to 'welcome1'):

Please re-enter the 'SYSASM' password:

INFO: 2019-06-10 15:34:59: Installing a new Home : OraDb12201_home1 at /u01/app/oracle/product/12.2.0.1/dbhome_1

Please select one of the following for Database edition [1 .. 2] :

1 => Enterprise Edition

2 => Standard Edition

The selected value is : Enterprise Edition

INFO : Logging all actions in the file /opt/oracle/oak/log/dbserver01/patch/12.2.1.4.0/dbserver01-20190610153512.log and traces in the file /opt/oracle/oak/log/dbserver01/patch/12.2.1.4.0/dbserver01-20190610153512.trc

INFO : Loading the configuration file /opt/oracle/oak/onecmd/create_database.params...

INFO : Creating the node list files...

INFO : Setting up ssh for root...

INFO : Setting up SSH across the Private Network...

...INFO : Running as root: /usr/bin/ssh -l root 192.168.16.24 /root/DoAllcmds.sh

INFO : Running as root: /usr/bin/ssh -l root 192.168.16.25 /root/DoAllcmds.sh

INFO : Background process 80803 (node: 192.168.16.24) gets done with the exit code 0

INFO : Background process 80879 (node: 192.168.16.25) gets done with the exit code 0

INFO : Setting up SSH completed successfully

INFO : Running the command /usr/bin/rsync -tarqvz /opt/oracle/oak/onecmd/ root@192.168.16.25:/opt/oracle/oak/onecmd --exclude=*zip --exclude=*gz --exclude=*log --exclude=*trc --exclude=*rpm to sync directory</opt/oracle/oak/onecmd> on node <192.168.16.25>

SUCCESS: Ran /usr/bin/rsync -tarqvz /opt/oracle/oak/onecmd/ root@192.168.16.25:/opt/oracle/oak/onecmd --exclude=*zip --exclude=*gz --exclude=*log --exclude=*trc --exclude=*rpm and it returned: RC=0

...INFO : Did not do scp for node : dbserver01

INFO : Running as root: /usr/bin/ssh -l root dbserver01 /root/DoAllcmds.sh

INFO : Running as root: /usr/bin/ssh -l root dbserver02 /root/DoAllcmds.sh

INFO : Background process 83580 (node: dbserver01) gets done with the exit code 0

INFO : Background process 83632 (node: dbserver02) gets done with the exit code 0

INFO : Did not do scp for node : dbserver01

INFO : Running as root: /usr/bin/ssh -l root dbserver01 /opt/oracle/oak/onecmd/tmp/DoAllcmds-20190610153554.sh

INFO : INFO : Running as root: /usr/bin/ssh -l root dbserver02 /opt/oracle/oak/onecmd/tmp/DoAllcmds-20190610153554.sh

Background process 83711 (node: dbserver01) gets done with the exit code 0

INFO : Background process 83762 (node: dbserver02) gets done with the exit code 0

INFO : Setting up SSH for user oracle...

...INFO : checking nodes in /opt/oracle/oak/onecmd/tmp/db_nodes...

...

SUCCESS: All nodes in /opt/oracle/oak/onecmd/tmp/db_nodes are pingable and alive.

INFO : Checking SSH setup for user (oracle) on nodes in /opt/oracle/oak/onecmd/tmp/db_nodes...

INFO : Did not do scp for node : dbserver01

INFO : This is root, will become oracle and run: /bin/su oracle -c /usr/bin/ssh -l oracle dbserver01 /opt/oracle/oak/onecmd/tmp/DoAllcmds-20190610153621.sh

INFO : Running on the local node: /bin/su oracle -c /opt/oracle/oak/onecmd/tmp/DoAllcmds-20190610153621.sh

INFO : Background process 86736 (node: dbserver01) gets done with the exit code 0

INFO : This is root, will become oracle and run: /bin/su oracle -c /usr/bin/ssh -l oracle dbserver02 /opt/oracle/oak/onecmd/tmp/DoAllcmds-20190610153621.sh

INFO : Background process 86791 (node: dbserver02) gets done with the exit code 0

INFO : Creating DB homes on the nodes dbserver01 dbserver02....

INFO : Did not do scp for node : dbserver01

INFO : Running as root: /usr/bin/ssh -l root dbserver01 /opt/oracle/oak/onecmd/tmp/DoAllcmds-20190610153622.sh

INFO : Background process 86864 (node: dbserver01) gets done with the exit code 0

INFO : Running as root: /usr/bin/ssh -l root dbserver02 /opt/oracle/oak/onecmd/tmp/DoAllcmds-20190610153622.sh

INFO : Background process 86918 (node: dbserver02) gets done with the exit code 0

INFO : Did not do scp for node : dbserver01

INFO : Running as root: /usr/bin/ssh -l root dbserver01 /opt/oracle/oak/onecmd/tmp/DoAllcmds-20190610153623.sh

INFO : Running as root: /usr/bin/ssh -l root dbserver02 /opt/oracle/oak/onecmd/tmp/DoAllcmds-20190610153623.sh

INFO : Background process 87020 (node: dbserver01) gets done with the exit code 0

INFO : Background process 87071 (node: dbserver02) gets done with the exit code 0

INFO : Running /usr/bin/scp /opt/oracle/oak/pkgrepos/orapkgs/DB/12.2.0.1.180417/Base/db122.tar.gz root@192.168.16.25:/opt/oracle/oak/pkgrepos/orapkgs/DB/12.2.0.1.180417/Base/db122.tar.gz to copy DB clone to the other node...

SUCCESS: Ran /usr/bin/scp /opt/oracle/oak/pkgrepos/orapkgs/DB/12.2.0.1.180417/Base/db122.tar.gz root@192.168.16.25:/opt/oracle/oak/pkgrepos/orapkgs/DB/12.2.0.1.180417/Base/db122.tar.gz and it returned: RC=0

INFO : Did not do scp for node : dbserver01

INFO : Running as root: /usr/bin/ssh -l root dbserver01 /opt/oracle/oak/onecmd/tmp/DoAllcmds-20190610153659.sh

INFO : Background process 90422 (node: dbserver01) gets done with the exit code 0

INFO : Running as root: /usr/bin/ssh -l root dbserver02 /opt/oracle/oak/onecmd/tmp/DoAllcmds-20190610153659.sh

INFO : Background process 90476 (node: dbserver02) gets done with the exit code 0

INFO : Did not do scp for node : dbserver01

INFO : Running as root: /usr/bin/ssh -l root dbserver01 /opt/oracle/oak/onecmd/tmp/DoAllcmds-20190610153700.sh

INFO : Running as root: /usr/bin/ssh -l root dbserver02 /opt/oracle/oak/onecmd/tmp/DoAllcmds-20190610153700.sh

INFO : Background process 90558 (node: dbserver01) gets done with the exit code 0

INFO : Background process 90609 (node: dbserver02) gets done with the exit code 0

INFO : Restoring the copy of DB home on all nodes...

INFO : Did not do scp for node : dbserver01

INFO : This is root, will become oracle and run: /bin/su oracle -c /usr/bin/ssh -l oracle dbserver01 /opt/oracle/oak/onecmd/tmp/DoAllcmds-20190610153700.sh

INFO : Running on the local node: /bin/su oracle -c /opt/oracle/oak/onecmd/tmp/DoAllcmds-20190610153700.sh

INFO : This is root, will become oracle and run: /bin/su oracle -c /usr/bin/ssh -l oracle dbserver02 /opt/oracle/oak/onecmd/tmp/DoAllcmds-20190610153700.sh

INFO : Background process 90710 (node: dbserver01) gets done with the exit code 0

INFO : Background process 90912 (node: dbserver02) gets done with the exit code 0

INFO : Did not do scp for node : dbserver01

INFO : Running as root: /usr/bin/ssh -l root dbserver01 /opt/oracle/oak/onecmd/tmp/DoAllcmds-20190610154020.sh

INFO : Background process 11959 (node: dbserver01) gets done with the exit code 0

INFO : Running as root: /usr/bin/ssh -l root dbserver02 /opt/oracle/oak/onecmd/tmp/DoAllcmds-20190610154020.sh

INFO : Background process 12583 (node: dbserver02) gets done with the exit code 0

INFO : Cloning and relinking RAC home

INFO : Did not do scp for node : dbserver01

INFO : Running as root: /usr/bin/ssh -l root dbserver01 /opt/oracle/oak/onecmd/tmp/fuser_mv.sh

INFO : Running as root: /usr/bin/ssh -l root dbserver02 /opt/oracle/oak/onecmd/tmp/fuser_mv.sh

INFO : Background process 12673 (node: dbserver01) gets done with the exit code 0

INFO : Background process 12734 (node: dbserver02) gets done with the exit code 0

INFO : Running clone.pl on RAC home. Look at /opt/oracle/oak/onecmd/tmp/racclonepl.sh-20190610153512-log for more details

INFO : Did not do scp for node : dbserver01

INFO : Running as root: /usr/bin/ssh -l root dbserver01 /opt/oracle/oak/onecmd/tmp/racclonepl.sh

INFO : Running as root: /usr/bin/ssh -l root dbserver02 /opt/oracle/oak/onecmd/tmp/racclonepl.sh

INFO : Background process 12838 (node: dbserver01) gets done with the exit code 0

INFO : Background process 12891 (node: dbserver02) gets done with the exit code 0

INFO : Runing root.sh on all nodes...

INFO : Did not do scp for node : dbserver01

INFO : Running as root: /usr/bin/ssh -l root dbserver01 /opt/oracle/oak/onecmd/tmp/DoAllcmds-20190610154224.sh

INFO : Running as root: /usr/bin/ssh -l root dbserver02 /opt/oracle/oak/onecmd/tmp/DoAllcmds-20190610154224.sh

INFO : Background process 28932 (node: dbserver01) gets done with the exit code 0

INFO : Background process 28988 (node: dbserver02) gets done with the exit code 0

INFO : Removing temporary templates

INFO : Did not do scp for node : dbserver01

INFO : Running as root: /usr/bin/ssh -l root dbserver01 /opt/oracle/oak/onecmd/tmp/remove_temp.sh

INFO : Running as root: /usr/bin/ssh -l root dbserver02 /opt/oracle/oak/onecmd/tmp/remove_temp.sh

INFO : Background process 29391 (node: dbserver01) gets done with the exit code 0

INFO : Background process 29447 (node: dbserver02) gets done with the exit code 0

INFO : Running asmgid

INFO : Did not do scp for node : dbserver01

INFO : This is root, will become oracle and run: /bin/su oracle -c /usr/bin/ssh -l oracle dbserver01 /opt/oracle/oak/onecmd/tmp/set_asm_gid.sh

INFO : Running on the local node: /bin/su oracle -c /opt/oracle/oak/onecmd/tmp/set_asm_gid.sh

INFO : This is root, will become oracle and run: /bin/su oracle -c /usr/bin/ssh -l oracle dbserver02 /opt/oracle/oak/onecmd/tmp/set_asm_gid.sh

INFO : Background process 29535 (node: dbserver01) gets done with the exit code 0

INFO : Background process 29593 (node: dbserver02) gets done with the exit code 0

INFO : Did not do scp for node : dbserver01

INFO : Running as root: /usr/bin/ssh -l root dbserver01 /opt/oracle/oak/onecmd/tmp/fuser_mv_bk.sh

INFO : Running as root: /usr/bin/ssh -l root dbserver02 /opt/oracle/oak/onecmd/tmp/fuser_mv_bk.sh

INFO : Background process 29752 (node: dbserver01) gets done with the exit code 0

INFO : Background process 29853 (node: dbserver02) gets done with the exit code 0

SUCCESS: 2019-06-10 15:42:34: Successfully created the Database Home : OraDb12201_home1

[ root@dbserver01 : Mon Jun 10, 03:42 PM : /opt/oracle/oak/bin ]

Tuesday, July 31, 2018

ODA X7- Delete database using ODACLI

List databases using ODACLI in ODA-X7

user90@S980p4 ~]$ sudo odacli list-databases

ID DB Name DB Type DB Version CDB Class Shape Storage Status DbHo
meID
---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ -----
-----------------------------------
b47dda6b-f967-4a4a-ba20-56baa3cfac5f db1 Rac 12.2.0.1 true OLTP Odb1 ASM Configured f9b63
5ef-1a82-4f43-8759-afe22a7f74f1
3e0856d0-5bbf-4e59-9c8f-84e399d532a8 db2 Rac 12.2.0.1 true OLTP Odb1 ASM Configured 894ef
5e6-de36-4eaf-a68f-603c09de1eb9
d62f4017-4085-44bf-b65b-cf6c8240c2f6 db3 Rac 12.1.0.2 false OLTP Odb1 ASM Configured a1ab0
ca9-546c-4b5f-8fd6-da7622c04127

Delete database using ODACLI in ODA X7
[user90@S980p4~]$ sudo odacli delete-database -i 894ef5e6-de36-4eaf-a68f-603c09de1eb9

ODA X7- create database using ODACLI

List databases in ODA X7-using ODACLI

[user02@S978a8 ~]$ sudo odacli list-databases

Create database in ODA X7-using ODACLI

Find all options available using odacli create-database with help.

[user02@S978a8 ~]$ sudo odacli create-database -h

Usage: create-database [options]

Options:

--adminpassword, -m

Password for SYS,SYSTEM and PDB Admin

--backupconfigid, -bi

Backup Config ID

--bkuppassword, -bp

Rman Backup Password

--cdb, -c

Create Container Database (Inverse option: --no-cdb/-no-c) (default: no cdb)

--characterset, -cs

Character Set (default:AL32UTF8)

--databaseUniqueName, -u

database unique name

--dbEdition, -de

Database Edition

--dbOnFlashStorage, -f

Enable data on FLASH Storage (Inverse option: --no-dbOnFlashStorage/-no-f)

--dbclass, -cl

Database Class EE: OLTP/DSS/IMDB, SE: OLTP (default:OLTP)

--dbconsole, -co

Enable Database Console (Inverse option: --no-dbconsole/-no-co) (default: no dbconsole)

--dbdomainname, -dn

Database Domain Name

--dbhomeid, -dh

Database Home ID (Use Existing DB Home)

--dblanguage, -l

Database Language (default:AMERICAN)

* --dbname, -n

Database Name

--dbshape, -s

Database Shape{odb1s,odb1,odb2,etc.} (default:odb1)

--dbstorage, -r

Database Storage {ACFS|ASM} (non case-sensitive) (default:ASM) 11.2.0.4 databases can be created only on ACFS storage

--dbterritory, -dt

Database Territory (default:AMERICA)

--dbtype, -y

Database Type: RAC/RACOne/SI (non case-sensitive)

Default: RAC

--enableFlashCache, -fc

Enable Flash Cache (Inverse option: --no-enableFlashCache/-no-fc)

--help, -h

get help

Default: true

--instanceonly, -io

Create Instance Only (For Standby)

--json, -j

json output

--nationalscharacterset, -ns

National Character Set (default:AL16UTF16)

--no-cdb, -no-c

Will not create Container Database (Inverse option: --cdb/-c)(default: no cdb)

--no-dbOnFlashStorage, -no-f

Disable data on FLASH Storage (Inverse option: --dbOnFlashStorage/-f)

--no-dbconsole, -no-co

Disable Database Console (Inverse option: --dbconsole/-co)(default: no dbconsole

--no-enableFlashCache, -no-fc

Disable Flash Cache(Inverse option: --enableFlashCache/-fc)

--pdbadmin, -d

Pluggable Database Admin User

--pdbname, -p

Pluggable Database Name

--targetnode, -g

Node Number (applicable only for SI and RACONE)

Default: 0

--version, -v

Database Version

Example: Below is the create database command to create database called db3 with version 12.1.0.2

[user02@S978a8 ~]$ sudo odacli create-database -m -n db3 -v 12.1.0.2

Friday, June 29, 2018

Oakcli commands to check disks status in ODA-X5

Oakcli show diskgroup can be used to diagnose the disks in ODA-X5

oakcli show diskgroup DATA
oakcli show diskgroup REDO
oakcli show diskgroup RECO

[ root@server01 : Fri Jun 29, 12:43 PM : /root ]

$ oakcli show diskgroup DATA

ASM_DISK PATH DISK STATE STATE_DETAILS

e0_data_00 /dev/mapper/HDD_E0_S00_1001222948p1 e0_pd_00 ONLINE Good

e0_data_01 /dev/mapper/HDD_E0_S01_1001143832p1 e0_pd_01 ONLINE Good

e0_data_02 /dev/mapper/HDD_E0_S02_1001176312p1 e0_pd_02 ONLINE Good

e0_data_03 /dev/mapper/HDD_E0_S03_1001244956p1 e0_pd_03 ONLINE Good

e0_data_04 /dev/mapper/HDD_E0_S04_1001145144p1 e0_pd_04 ONLINE Good

e0_data_05 /dev/mapper/HDD_E0_S05_1001109172p1 e0_pd_05 ONLINE Good

e0_data_06 /dev/mapper/HDD_E0_S06_1001181748p1 e0_pd_06 ONLINE Good

e0_data_07 /dev/mapper/HDD_E0_S07_1001138920p1 e0_pd_07 ONLINE Good

e0_data_08 /dev/mapper/HDD_E0_S08_1001114724p1 e0_pd_08 ONLINE Good

e0_data_09 /dev/mapper/HDD_E0_S09_1001143148p1 e0_pd_09 ONLINE Good

e0_data_10 /dev/mapper/HDD_E0_S10_1001136500p1 e0_pd_10 ONLINE Good

e0_data_11 /dev/mapper/HDD_E0_S11_1001139372p1 e0_pd_11 ONLINE Good

e0_data_12 /dev/mapper/HDD_E0_S12_1001143000p1 e0_pd_12 ONLINE Good

e0_data_13 /dev/mapper/HDD_E0_S13_1001142632p1 e0_pd_13 ONLINE Good

e0_data_14 /dev/mapper/HDD_E0_S14_1001111132p1 e0_pd_14 ONLINE Good

e0_data_15 /dev/mapper/HDD_E0_S15_1001140648p1 e0_pd_15 ONLINE Good

[ root@server01 : Fri Jun 29, 12:43 PM : /root ]

$ oakcli show diskgroup RECO

ASM_DISK PATH DISK STATE STATE_DETAILS

e0_reco_00 /dev/mapper/HDD_E0_S00_1001222948p2 e0_pd_00 ONLINE Good

e0_reco_01 /dev/mapper/HDD_E0_S01_1001143832p2 e0_pd_01 ONLINE Good

e0_reco_02 /dev/mapper/HDD_E0_S02_1001176312p2 e0_pd_02 ONLINE Good

e0_reco_03 /dev/mapper/HDD_E0_S03_1001244956p2 e0_pd_03 ONLINE Good

e0_reco_04 /dev/mapper/HDD_E0_S04_1001145144p2 e0_pd_04 ONLINE Good

e0_reco_05 /dev/mapper/HDD_E0_S05_1001109172p2 e0_pd_05 ONLINE Good

e0_reco_06 /dev/mapper/HDD_E0_S06_1001181748p2 e0_pd_06 ONLINE Good

e0_reco_07 /dev/mapper/HDD_E0_S07_1001138920p2 e0_pd_07 ONLINE Good

e0_reco_08 /dev/mapper/HDD_E0_S08_1001114724p2 e0_pd_08 ONLINE Good

e0_reco_09 /dev/mapper/HDD_E0_S09_1001143148p2 e0_pd_09 ONLINE Good

e0_reco_10 /dev/mapper/HDD_E0_S10_1001136500p2 e0_pd_10 ONLINE Good

e0_reco_11 /dev/mapper/HDD_E0_S11_1001139372p2 e0_pd_11 ONLINE Good

e0_reco_12 /dev/mapper/HDD_E0_S12_1001143000p2 e0_pd_12 ONLINE Good

e0_reco_13 /dev/mapper/HDD_E0_S13_1001142632p2 e0_pd_13 ONLINE Good

e0_reco_14 /dev/mapper/HDD_E0_S14_1001111132p2 e0_pd_14 ONLINE Good

e0_reco_15 /dev/mapper/HDD_E0_S15_1001140648p2 e0_pd_15 ONLINE Good

[ root@server01 : Fri Jun 29, 12:43 PM : /root ]

$ oakcli show diskgroup REDO

ASM_DISK PATH DISK STATE STATE_DETAILS

e0_redo_20 /dev/mapper/SSD_E0_S20_1319903892p1 e0_pd_20 ONLINE Good

e0_redo_21 /dev/mapper/SSD_E0_S21_1319903924p1 e0_pd_21 ONLINE Good

e0_redo_22 /dev/mapper/SSD_E0_S22_1319905524p1 e0_pd_22 ONLINE Good

e0_redo_23 /dev/mapper/SSD_E0_S23_1319904384p1 e0_pd_23 ONLINE Good

[ root@server01 : Fri Jun 29, 12:44 PM : /root ]

Sunday, June 24, 2018

Drop and create dbstorage in ODA( oracle database appliance)-X5

[ root@server01 : Mon Jun 04, 05:28 PM : /root ]

$ oakcli delete dbstorage -db dbtest01

INFO: 2018-06-05 13:37:39: Look at the log file '/opt/oracle/oak/log/server01/tools/12.2.1.2.0/deletedbstorage_s

INFO: 2018-06-05 13:37:50: Will be deleting the following if they exist:

ACFS snapshot: /u02/app/oracle/oradata/datastore/.ACFS/snaps/dbtest01

Directory: /u01/app/oracle/oradata/datastore/dbtest01

Directory: /u01/app/oracle/fast_recovery_area/datastore/dbtest01

Do you want to continue [Y|N]:Y

INFO: 2018-06-05 13:37:56: Deleting the storage set for the database : dbtest01

INFO: 2018-06-05 13:38:00: Successfully deleted the SNAP entries

[ root@server01 : Tue Jun 05, 01:38 PM : /root ]

$ oakcli create dbstorage -db dbtest01

INFO: 2018-06-05 13:38:21: Please check the logfile '/opt/oracle/oak/log/server01/tools/12.2.1.2.0/createdbstor

INFO: 2018-06-05 13:38:52: Storage for the Database with the name dbtest01 is possible

Please enter the 'SYSASM' password : (During deployment we set the SYSASM password to 'welcome1'):

Please re-enter the 'SYSASM' password:

Specify the Database Class (1. odb-01 '1 core, 8 GB memory' 2. Others) [1] : 2

Please select one of the following for Database Class [1 .. 7] :

1 => odb-01s ( 1 cores , 4 GB memory)

2 => odb-01 ( 1 cores , 8 GB memory)

3 => odb-02 ( 2 cores , 16 GB memory)

4 => odb-04 ( 4 cores , 32 GB memory)

5 => odb-06 ( 6 cores , 48 GB memory)

6 => odb-12 ( 12 cores , 96 GB memory)

7 => odb-16 ( 16 cores , 128 GB memory)

2

The selected value is : odb-01 ( 1 cores , 8 GB memory)

...SUCCESS: Ran /usr/bin/rsync -tarqvz /opt/oracle/oak/onecmd/ root@192.168.16.25:/opt/oracle/oak/onecmd --exclude=*RC=0

.........

SUCCESS: All nodes in /opt/oracle/oak/onecmd/tmp/db_nodes are pingable and alive.

INFO: 2018-06-05 13:50:04: Successfully setup the storage structure for the database 'dbtest01'

INFO: 2018-06-05 13:50:04: Set the following directory structure for the Database dbtest01

INFO: 2018-06-05 13:50:04: DATA: /u02/app/oracle/oradata/datastore/.ACFS/snaps/dbtest01

INFO: 2018-06-05 13:50:04: REDO: /u01/app/oracle/oradata/datastore/dbtest01

INFO: 2018-06-05 13:50:04: RECO: /u01/app/oracle/fast_recovery_area/datastore/dbtest01

SUCCESS: 2018-06-05 13:50:04: Successfully setup the Storage for the Database : dbtest01

[ root@server01 : Tue Jun 05, 01:50 PM : /root ]

Friday, May 18, 2018

SQL queries for database operations

How many times query executed in a day.

select s.begin_interval_time, sql.sql_id as sql_id, sql.executions_delta as exe_delta, sql.EXECUTIONS_TOTAL

from dba_hist_sqlstat sql, dba_hist_snapshot s

where sql_id='f3dwn5j209c7y'

and s.snap_id = SQL.snap_id

and s.begin_interval_time> TO_date('16-MAY-2018 00:00', 'dd-mon-yyyy hh24:mi')

and s.begin_interval_time< TO_date('17-MAY-2018 00:00', 'dd-mon-yyyy hh24:mi') order by s.begin_interval_time;

How to find the size of table partitions and sub partitions.

Find the size of Table(DEPARTMENT) with partitions

SELECT * FROM (

SELECT

owner, object_name, object_type, table_name, ROUND(bytes)/1024/1024 AS meg,

tablespace_name, extents, initial_extent,

ROUND(Sum(bytes/1024/1024) OVER (PARTITION BY table_name)) AS total_table_meg

FROM (

-- Tables

SELECT owner, segment_name AS object_name, 'TABLE' AS object_type,

segment_name AS table_name, bytes,

tablespace_name, extents, initial_extent

FROM dba_segments

WHERE

--segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION') and segment_name='TABLENAME'))

segment_type IN ('TABLE PARTITION') and segment_name='DEPARTMENT'))

Find the size of Table(DEPARTMENT) which is partitioned (has subpartitions)

select

s.owner,

s.segment_name,

sp.partition_name,

SUM(bytes/1024/1024/1024) "GB"

from dba_segments s

join dba_tab_subpartitions sp ON sp.subpartition_name = s.partition_name

where segment_name='DEPARTMENT'

group by s.owner,

s.segment_name,

sp.partition_name;