Srisha's blog: 2018

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;

Thursday, May 10, 2018

How to restart OAKD process in ODA X5(Oracle Database Appliance)

Existing configuration:
Server01,Server02 -ODA X5.

Server01-OAKD is in slave mode.
Server02- OAKD is in Master mode

New configuration:
Server02-OAKD is in slave mode.
Server01- OAKD is in Master mode

Login as root to server02.

[ root@server02 : Wed Apr 18, 05:43 PM : /root ]

$ oakcli show ismaster

OAKD is in Master Mode

[ root@server02 : Wed Apr 18, 05:53 PM : /root ]

$ oakcli restart oak

2018-04-18 18:03:51.467159031:[init.oak]:[Restarting the oakd..]

2018-04-18 18:03:51.638969666:[init.oak]:[Killing the running oakd with pid 18409]

2018-04-18 18:04:01.862710278:[init.oak]:[Successfully re-started the oakd..]

[ root@server02 : Wed Apr 18, 06:04 PM : /root ]

$ oakcli show ismaster

OAKD is in Slave Mode

[ root@server02 : Wed Apr 18, 06:04 PM : /root ]

$

Login as root to server01.

[ root@server01 : Wed Apr 18, 06:14 PM : /root ]

$ oakcli show ismaster

OAKD is in Master Mode

How to drop a database in ODA X5(Oracle Database appliance)

[ root@Server01 : Tue Aug 01, 10:27 AM : /root ]

$ oakcli delete database -db test99sb

INFO: 2017-08-01 10:37:41: Look at the log file '/opt/oracle/oak/log/Server01/tools/12.1.2.11.0/deletedatabase_test99sb_73509.log' for more details

Please enter the 'SYS' password :

Please re-enter the 'SYS' password:

All the storage volumes created for the database 'test99sb' will be deleted. Do you want to continue [Y|N]:Y

INFO: 2017-08-01 10:38:08: Deleting the database test99sb. It will take few minutes. Please wait...

INFO: 2017-08-01 10:38:12: Setting up SSH

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

.........

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

INFO: 2017-08-01 10:39:11: SSH has been successfully established

INFO: 2017-08-01 10:40:02: Successfully deleted the database 'test99sb'

How to create a standalone database in ODA X5 (Oracle Database Appliance)

Oracle Home Name Oracle Home version Home Location

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

OraDb11204_home1 11.2.0.4.170418(24732075,23054319) /u01/app/oracle/product/11.2.0.4/dbhome_1

OraDb12102_home1 12.1.0.2.170418(25171037,25942196) /u01/app/oracle/product/12.1.0.2/dbhome_1

[ root@server01 : Tue Aug 01, 10:09 AM : /root ]

$ oakcli create database -db test99sb -oh OraDb11204_home1

INFO: 2017-08-01 10:10:05: Please check the logfile '/opt/oracle/oak/log/server01/tools/12.1.2.11.0/createdb_test99sb_64379.log' for more details

INFO: 2017-08-01 10:10:06: Database parameter file is not provided. Will be using default parameters for DB creation

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

Please re-enter the 'SYSASM' password:

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

1 => OLTP

2 => DSS

1

The selected value is : OLTP

Please select one of the following for Database Deployment [1 .. 3] :

1 => EE : Enterprise Edition

2 => RACONE

3 => RAC

1

The selected value is : EE

Please select one of the following for Node Number [1 .. 2] :

1 => server01

2 => server02

2

The selected value is : server02

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

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)

1

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

Do you want to setup the EM DB Console for this database [ Y | N ] ? : N

INFO : Logging all actions in the file /opt/oracle/oak/log/server01/patch/12.1.2.11.0/server01-20170801101324.log and traces in the file /opt/oracle/oak/log/server01/patch/12.1.2.11.0/server01-20170801101324.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 : Background process 78589 (node: 192.168.16.24) gets done with the exit code 0

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

INFO : Background process 78614 (node: 190.130.10.18) 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@190.130.10.18:/opt/oracle/oak/onecmd --exclude=*zip --exclude=*gz --exclude=*log --exclude=*trc --exclude=*rpm to sync directory</opt/oracle/oak/onecmd> on node <190.130.10.18>

SUCCESS: Ran /usr/bin/rsync -tarqvz /opt/oracle/oak/onecmd/ root@190.130.10.18:/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 : server01

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

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

INFO : Background process 80186 (node: server01) gets done with the exit code 0

INFO : Background process 80209 (node: server02) gets done with the exit code 0

INFO : Did not do scp for node : server01

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

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

INFO : Background process 80257 (node: server01) gets done with the exit code 0

INFO : Background process 80280 (node: server02) 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 : server01

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

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

INFO : Background process 82483 (node: server01) 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 server02 /opt/oracle/oak/onecmd/tmp/DoAllcmds-20170801101439.sh

INFO : Background process 82506 (node: server02) gets done with the exit code 0

INFO : Setting up ACFS storage

INFO : Did not do scp for node : server01

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

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

INFO : Background process 82813 (node: server01) gets done with the exit code 0

INFO : Background process 82837 (node: server02) gets done with the exit code 0

INFO: 2017-08-01 10:15:05: Successfully setup the storage structure for the database 'test99sb'

SUCCESS: Successfully setup ACFS storage for the database test99sb

INFO : Creating Database using DBCA...

INFO : Did not do scp for node : server01

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

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

INFO : Background process 87128 (node: server01) gets done with the exit code 0

INFO : Background process 87152 (node: server02) gets done with the exit code 0

INFO : Running DBCA using /opt/oracle/oak/onecmd/tmp/dbca-test99sb.sh on server02 as oracle...

INFO : Check output in /opt/oracle/oak/onecmd/tmp/dbca-test99sb-20170801101324.log on server02

...

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

Instance test99sb is running on node server02

INFO : One or more Instances running on the cluster nodes.

INFO : Running export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1;/u01/app/oracle/product/11.2.0.4/dbhome_1/bin/srvctl setenv database -d test99sb -t 'TZ=America/New_York' to set DB timezone

INFO : Running the command /u01/app/12.1.0.2/grid/bin/crsctl stat resource ora.test99sb.db -p

...

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

INFO : Running export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1;/u01/app/oracle/product/11.2.0.4/dbhome_1/bin/srvctl stop database -d test99sb

INFO : Running export ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1;/u01/app/oracle/product/11.2.0.4/dbhome_1/bin/srvctl start database -d test99sb

INFO: 2017-08-01 10:23:09: Successfully set the RMAN SNAPSHOT control file

SUCCESS: 2017-08-01 10:23:14: Successfully created the Database : test99sb

[ root@server01 : Tue Aug 01, 10:23 AM : /root ]

Thursday, May 3, 2018

Create db storage in ODA X5(oracle database appliance)

Log in as root in matser node,issue below command
$ oakcli create dbstorage -db testqa

Log in as root,issue below command
$ oakcli create dbstorage -db testqa
INFO: 2018-05-03 17:31:33: Please check the logfile '/opt/oracle/oak/log/dbserver01/tools/12.2.1.2.0/createdbstorage_testqa_51560.log' for more details
INFO: 2018-05-03 17:31:41: Storage for the Database with the name testqa is possible

Please enter the 'SYSASM' password : (During deployment we set the SYSASM password to 'welcome1'):
Please enter the 'SYSASM' password :
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)
3
The selected value is : odb-02 ( 2 cores , 16 GB memory)
...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

.........
SUCCESS: All nodes in /opt/oracle/oak/onecmd/tmp/db_nodes are pingable and alive.
INFO: 2018-05-03 17:34:14: Successfully setup the storage structure for the database 'testqa'
INFO: 2018-05-03 17:34:14: Set the following directory structure for the Database testqa
INFO: 2018-05-03 17:34:14: DATA: /u02/app/oracle/oradata/datastore/.ACFS/snaps/testqa
INFO: 2018-05-03 17:34:14: REDO: /u01/app/oracle/oradata/datastore/testqa
INFO: 2018-05-03 17:34:14: RECO: /u01/app/oracle/fast_recovery_area/datastore/testqa
SUCCESS: 2018-05-03 17:34:14: Successfully setup the Storage for the Database : testqa

Change UNDO tablespace from smallfile to bigfile in RAC environment

With below steps UNDO tablespace in RAC can be converted from small file to bigfile.

RAC Database: TESTQA
Instances: TESTQA1,TESTQA2

1.NODE1-(Login to TESTQA1 instance)

create bigfile undo tablespace UNDOTBS3 datafile

size 200m autoextend on next 5m maxsize 32727m

online

retention noguarantee

blocksize 8k

flashback on;

alter system set undo_tablespace= UNDOTBS3 scope=both sid='testqa1';

drop tablespace UNDOTBS1 including contents and datafiles;

create bigfile undo tablespace UNDOTBS1 datafile

size 200m autoextend on next 5m maxsize 32727m

online

retention noguarantee

blocksize 8k

flashback on;

Alter system set undo_tablespace= UNDOTBS1 scope=both sid='testqa1';

Drop tablespace UNDOTBS3 including contents and datafiles;

2.NODE2-(Login to TESTQA2 instance)

Create bigfile undo tablespace UNDOTBS4 datafile

size 200m autoextend on next 5m maxsize 32727m

online

retention noguarantee

blocksize 8k

flashback on;

Alter system set undo_tablespace= UNDOTBS4 scope=both sid='testqa2';

Drop tablespace UNDOTBS2 including contents and datafiles;

Create bigfile undo tablespace UNDOTBS2 datafile

size 200m autoextend on next 5m maxsize 32727m

online

retention noguarantee

blocksize 8k

flashback on;

Alter system set undo_tablespace= UNDOTBS2 scope=both sid='testqa2';

Drop tablespace UNDOTBS4 including contents;