If you want to objects statistics like (No. of object, collections, datasize etc. Available) follow below the commands.
use database
db.stats(); <—————- Get the database level statistics
db.technology.stats(); <—————- Get the collection level statistics
db.runCommand( { collStats : “restaurant”, scale: 1024 } )
For more Detail about Stats:
Database Stats: Click Here
Collection Stats: Click Here
Index Stats: Click Here
RDA captures data and provides Oracle support with a comprehensive picture of the customer's environment which aids in problem diagnosis. The goal of this article is to detail the steps on how to gather basic configuration, log and platform information from Oracle OpenSSO using the Remote Diagnostic Agent.
Instructions for UNIX/ZLinux Type Operating Systems
Choose or create a directory or area on your UNIX server. Make sure you have sufficient space for the RDA output (150MB). It does not matter where you create this directory or what it is named, but the same user that runs RDA must own it. Do not use a directory that contains an older version of RDA unless you have deleted the previous version of RDA first. If necessary, you can reuse prior setup files.
Note: The rda.zip creates a directory named "rda" containing all the required files when you extract it.
Installation Instruction
Do not extract the contents of the RDA archive on a Windows client first or you will have to remove the ^M characters from the end of each line in all of the shell scripts in order for them to run.
downloaded rda.zip file from oracle support as compitable your OS, I'm using UNIX server.
step 1.
Extract the .zip archive contents into a new directory, preserving the directory structure of the archive. Do not extract into a directory that contains an older RDA version. For example:
unzip rda.zip
step 2.
Change directory to the 'rda' directory created by the above command, for example:
cd rda
setp 3.
Make sure the RDA command 'rda.sh' is executable. To verify, enter the following command:
ls -l rda.sh
If the script is not executable, update it using the command:
chmod +x rda.sh
step 4.
The following command checks that you have a viable installation of RDA by checking that all file versions are as expected:
./rda.sh -cv
step 5.
Now run RDA here for Preinstallation checks and co-ordinate with SA for failed checks/fixes.
./rda.sh -T hcve
Below Query will show how much space is available to use in case of High or Normal Redundancy
TOTAL_MB:- Refers to Total Capacity of the Diskgroup
FREE_MB :- Refers to raw Free Space Available in Diskgroup in MB.
FREE_MB = (TOTAL_MB – (HOT_USED_MB + COLD_USED_MB))
REQUIRED_MIRROR_FREE_MB :- Indicates how much free space is required in an ASM disk group to restore redundancy after the failure of an ASM disk or ASM failure group.In exadata it is the disk capacity of one failure group.
USABLE_FILE_MB :- Indicates how much space is available in an ASM disk group considering the redundancy level of the disk group.
Its calculated as :-
USABLE_FILE_MB=(FREE_MB – REQUIRED_MIRROR_FREE_MB ) / 2 –> For Normal Redundancy
USABLE_FILE_MB=(FREE_MB – REQUIRED_MIRROR_FREE_MB ) / 3 –> For High Redundancy
Query to Run:
column total format 999,999 Heading "Total(G)"
column free format 999,999 Heading "Free (G)"
column Mirror_GB format 999,999 Heading "Space Used |for Mirroring(G)"
column Usable_GB format 999,999 Heading "Space Available |to Use(G)"
column pct format 999.0 Heading "% Free |in DG"
column pct2 format 999.0 Heading "Real % Free |in DG"
column type format a10
column name format a20
set linesize 200
set colsep '|'
prompt
Prompt "NOTE **** Incase of High or Normal Redundancy the Usable Space is lower than actual shown because of Mirroring *****"
prompt
select name,type, TOTAL_MB/1024 total, FREE_MB/1024 free, REQUIRED_MIRROR_FREE_MB/1024 Mirror_GB, USABLE_FILE_MB/1024 Usable_GB ,100-((total_MB-FREE_MB)/total_mb)*100 pct, 100-((total_MB-USABLE_FILE_MB)/total_mb)*100 pct2 from v$asm_diskgroup;
Sample Output :
"NOTE **** Incase of High or Normal Redundancy the Usable Space is lower than actual shown because of Mirroring *****"
| | | | Space Used |Space Available |% Free |Real % Free
NAME |TYPE |Total(G)|Free (G)|for Mirroring(G)| to Use(G)| in DG| in DG
--------------------|----------|--------|--------|----------------|----------------|-------|------------
DATA1 |HIGH | 260,496| 57,951| 14,472| 14,493| 22.2| 5.6
REDO1 |HIGH | 65,124| 33,322| 3,618| 9,901| 51.2| 15.2
I was asked to capture ASH reports every 5 minutes for an ongoing Database issue. Below is the process to schedule it through Crontab
Script to capture the ASH reports every 5 Minutes
$ cat ash.ksh
#!/bin/bash
export TZ=US/Central
dateString=`date +%d-%b-%Y_%H:%M:%S`
sqlplus -s / as sysdba << EOD1
define report_type = 'html'
define begin_time = '-5'
define duration = ''
define report_name = '/u01/user/local/reports/ashrpt.${dateString}.html'
@?/rdbms/admin/ashrpt
exit
EOF
To run it through Cron
0,5,10,15,20,22,25,30,32,35,40,45,50,55 * * * * /u01/user/local/ash/ash.ksh > /u01/user/local/reports/log/ash_collect.log 1>/dev/null 2>&1
After refreshing my QA database using RMAN DUPLICATE, my instance startup was failing with the below error
USER (ospid: 60897): terminating the instance due to error 304
Instance terminated by USER, pid = 60897
Wed Mar 06 02:14:34 2019
Starting ORACLE instance (normal)
Looking into the spfile, I noticed the database was pulling wrong instance_number and thread numbers even though the DB configuration was correct
$ srvctl config database -d oradb
Database unique name: oradb
Database name: oradb
Oracle home: /opt/app/oradb/oracle/product/11.2.0.4
Oracle user: oradb
Spfile: +oradb_DATA/oradb/spfileoradb.ora
Domain: db.abc.com
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: oradb
Database instances: oradb1,oradb2
Disk Groups: oradb_DATA,oradb_FRA,oradb_REDO1,oradb_REDO2
Mount point paths:
Services: oradb_1_2.db.abc.com,oradb_2_1.db.abc.com
Type: RAC
Database is administrator managed
From the pfile I created from the current spfile I could see
*.instance_number=2
*.thread=2
To resolve this, bring down the complete database and just start the failing instance, in our case instance 1
srvctl start instance -d oradb -i oradb1
Once the instance is started, login to SQL and run below
SQL> alter system set instance_number=1 scope=spfile sid='oradb1';
System altered.
SQL> alter system set thread=1 scope=spfile sid='oradb1';
System altered.
SQL> alter system set undo_tablespace='UNDO01' sid='oradb1';
System altered.
shutdown the instance and start the complete database
SQL> shutdown immediate
srvctl start database -d oradb
Hope this resolves your issue.
The client was getting Below error while connecting to the RAC database
sqlplus test@RACDB_1
SQL*Plus: Release 11.2.0.4.0 - Production on Tue Sep 22 11:34:07 2020
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
ERROR:
ORA-12537: TNS:connection closed
Issue :
The Oracle Binary permissions got changed and were not allowing the connections
Current Permissions under Oracle Home
[oracle@Node1 ~]$ ls -lrt $ORACLE_HOME/bin/oracle
-rwxr-sr-x 1 oracle asmadmin 243043788 Jun 2 01:12 oracle
It should be set to 6751 and should look like "-rwsr-s--x"
But doing chmod 6751 on oracle binary was not setting the correct permissions
[oracle@Node1 bin]$ chmod 6751 oracle
[oracle@Node1 bin]$ ls -lrt $ORACLE_HOME/bin/oracle
-rwsr-x--x 1 oracle asmadmin 243043788 Jun 2 01:12 oracle
Running below as RDBMS database Owner user helped, in this case, "oracle" user
1) Stop the database instance where the permissions got changed
srvctl stop instance -d RACDB -i RACDB1
2) Run as Oracle Database owner, in this case its Oracle OS user.
[oracle@Node1 ~]$ $GRID_HOME/bin/setasmgidwrap o=$ORACLE_HOME/bin/oracle
3) The permissions got changed and resolved the connection issues
[oracle@Node1 ~]$ ls -lrt $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 oracle asmadmin 243043788 Jun 2 01:12 oracle
Hope this resolves your issue..
Now a days i'm facing multiple issues on ExaCM boxes for patching on database nodes.
Sharing one scenario, Get request from customer apply APRIL_PSU2021 on ExaCM db boxes
ENV:
DB: 11204 version
GRID have 19c with APRIL2021 PSU.
*: Issue comes while running : exadbcpatchmulti -precheck_async 32537095 , failed with conflicts.
Get details from exadbcpatch log / conflict log to identify more details.
*: How to resolve conflicts ?
Share the all details and conflict with oracle, They provide me the solution below
Edit /var/opt/oracle/exapatch/exadbcpatch.cfg - #For this file mostly root ownership, need to take help from GRID owner team or any other script available to update cfg file.
add the Cloud patch which needs to be rolled back as in below.
rollback_needed_before_apply="32328629::normal"
Refer Doc:
Exadbcpatchmulti patch conflict on cloud MLR "ERROR: precheck failed in conflict check" (Doc ID 2492936.1)
*: Now Pre-checks successfully done without any issue and move forward to apply the patch on next step.
*: Now facing the issue again : Node_1 successfully applied the patch but another issue occur on Node_2 below the details here.
++++++Error getting when apply patch: On Node_1 successfully done but having issue on Node_2
INFO: running on the node Xxx3a02c02vm01-adm
INFO: check for this action apply_async
INFO: check for this action rollback_async
INFO: Successfully released ohome lock. Proceeding to release local provisioning lock
INFO: Successfully released local provisioning lock
ERROR: apply_async blocked on node Xxx3a02c02vm01-adm: as Exadbcpatch operation already running on requested home
ERROR: apply_async blocked on node Xxx3a02c02vm01-adm: as Exadbcpatch operation already running on requested home
++++++Solution
execute on all the nodes then run exadbcpatchmulti again.
mv /var/opt/oracle/cstate.xml /var/opt/oracle/cstate.xml_old
Refer Doc:
Exadbcpatchmulti fails with Apply_async Blocked On Node X As Another Instance Of Exadbcpatch Running (Doc ID 2530468.1)
*: Now run exadbcpatchmulti again and successfully applied the patch on ExaCM dbs.
Symptoms: As i faced during execute datapatch verbose 19c.
During execution of datapatch or DBMS_QOPATCH following error seen
ERROR:
ORA-20001: Latest xml inventory is not loaded into table
ORA-06512: at "SYS.DBMS_QOPATCH", line 1448
ORA-06512: at "SYS.DBMS_QOPATCH", line 122
Solution:
1. oraInventory issue
2. Recreate oraceinventory sometime works
3. 19c not using softlink so need to set correct entry into oratabs and run datapatch -verbose
-Because ORACLE_HOME using softlink that's why need to set this parameter for temporary purpose once datapatch done remove this parameter from pfile.
ORA-20001: Latest xml inventory is not loaded into table --->
1.
Startup upgrade
create or replace directory OPATCH_LOG_DIR as '$OH/QOpatch'; ## don't using ORACLE_HOME softlink
create or replace directory OPATCH_SCRIPT_DIR as '$OH/QOpatch';
ALTER SYSTEM SET "_disable_directory_link_check" = TRUE SCOPE=SPFILE;
shutdown immediate
startup upgrade
cd $ORACLE_HOME/OPatch
./datapatch -verbose
SELECT a.ksppinm "Parameter",b.ksppstvl "Session Value",c.ksppstvl "Instance Value" FROM x$ksppi a, x$ksppcv b, x$ksppsv c WHERE a.indx = b.indx AND a.indx = c.indx AND a.ksppinm LIKE '/_disable_direc%' escape '/';
Ref Doc:
datapatch fails with "ORA-20009:" “ORA-20013: DBMS_QOPATCH ran mostly in non install area” ORACLE_HOME is a SYMBOLIC LINK (Doc ID 2033620.1)
Queryable Patch Inventory - Issues/Solutions for ORA-20001: Latest xml inventory is not loaded into table (Doc ID 1602089.1)
Getting report of goldengate Replicate process which is shows after upgrade from 11g to 12c.
Even though checkpoint table also upgraded. But Replicate process unable to start.
------------------------------------------------------------------------------------------------------------------------
2021-02-11 04:13:31 INFO OGG-06451 Triggers will be suppressed by default.
[7m--More--(36%)[27m
2021-02-11 04:13:31 INFO OGG-01815 Virtual Memory Facilities for: COM
anon alloc: mmap(MAP_ANON) anon free: munmap
file alloc: mmap(MAP_SHARED) file free: munmap
target directories:
/opt/app/p0bfo3d1/ggs/12.1.2.1.0/dirtmp.
CACHEMGR virtual memory values (may have been adjusted)
CACHESIZE: 2G
CACHEPAGEOUTSIZE (default): 8M
PROCESS VM AVAIL FROM OS (min): 4G
CACHESIZEMAX (strict force to disk): 3.41G
Database Version:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 - Production
NLSRTL Version 12.1.0.2.0 - Production
Database Language and Character Set:
NLS_LANGUAGE = "AMERICAN"
NLS_TERRITORY = "AMERICA"
NLS_CHARACTERSET = "WE8ISO8859P1"
***********************************************************************
** Run Time Messages **
***********************************************************************
Opened trail file /opt/app/ORCL/ggs/11.2.1.0.3/trails/y1000331 at 2021-02-11 05:01:20
2021-02-11 05:01:20 INFO OGG-03506 The source database character set, as determined from the trail file, is we8iso8859p1.
2021-02-11 05:01:20 INFO OGG-06505 MAP resolved (entry ggsuser.ggs_heartbeat): MAP "GGSUSER"."GGS_HEARTBEAT", TARGET ggsuser.ggs_lagtime, KEY
COLS (ID), INSERTALLRECORDS, COLMAP (USEDEFAULTS, id = 0, committime = @GETENV ('GGHEADER', 'COMMITTIMESTAMP'), groupname = @GETENV ('GGENVIRONME
NT', 'GROUPNAME'), ).
Using the following default columns with matching names:
SOURCETIME=SOURCETIME, TARGETTIME=TARGETTIME, LAGTIME=LAGTIME
2021-02-11 05:01:21 INFO OGG-06510 Using the following key columns for target table GGSUSER.GGS_LAGTIME: ID.
2021-02-11 05:01:21 WARNING OGG-00869 OCI Error ORA-00001: unique constraint (GGSUSER.GGS_LAGTIME_PK) violated (status = 1), SQL <INSERT INTO "G
GSUSER"."GGS_LAGTIME" ("ID","COMMITTIME","GROUPNAME") VALUES (:a0,:a1,:a2)>.
2021-02-11 05:01:21 WARNING OGG-01004 Aborted grouped transaction on 'GGSUSER.GGS_LAGTIME', Database error 1 (OCI Error ORA-00001: unique constr
aint (GGSUSER.GGS_LAGTIME_PK) violated (status = 1), SQL <INSERT INTO "GGSUSER"."GGS_LAGTIME" ("ID","COMMITTIME","GROUPNAME") VALUES (:a0,:a1,:a2)
>).
2021-02-11 05:01:21 WARNING OGG-01003 Repositioning to rba 16083229 in seqno 331.
2021-02-11 05:01:21 WARNING OGG-01154 SQL error 1 mapping GGSUSER.GGS_HEARTBEAT to GGSUSER.GGS_LAGTIME OCI Error ORA-00001: unique constraint (G
GSUSER.GGS_LAGTIME_PK) violated (status = 1), SQL <INSERT INTO "GGSUSER"."GGS_LAGTIME" ("ID","COMMITTIME","GROUPNAME") VALUES (:a0,:a1,:a2)>.
2021-02-11 05:01:21 WARNING OGG-01003 Repositioning to rba 16083229 in seqno 331.
Source Context :
SourceModule : [er.errors]
SourceID : [/scratch/aime1/adestore/views/aime1_adc4150384/oggcore/OpenSys/src/app/er/errors.cpp]
SourceFunction : [take_rep_err_action]
SourceLine : [701]
ThreadBacktrace : [13] elements
: [/opt/app/ORCL/ggs/12.1.2.1.0/libgglog.so(CMessageContext::AddThreadContext()+0x1e) [0x7f69e005bdae]]
: [/opt/app/ORCL/ggs/12.1.2.1.0/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x340
) [0x7f69e0056ac0]]
: [/opt/app/ORCL/ggs/12.1.2.1.0/libgglog.so(_MSG_ERR_MAP_TO_TANDEM_FAILED(CSourceContext*, ggs::gglib::ggapp::CQualD
BObjName<(DBObjType)1> const&, ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1> const&, CMessageFactory::MessageDisposition)+0x53) [0x7f69e004fcff]
]
: [/opt/app/ORCL/ggs/12.1.2.1.0/replicat(take_rep_err_action(short, int, char const*, extr_ptr_def*, __std_rec_hdr*,
char*, file_def*, bool)+0xdda) [0x580b82]]
: [/opt/app/ORCL/ggs/12.1.2.1.0/replicat(process_record(file_def*&, short&, extr_ptr_def*&, extr_ptr_def*&, int&, bo
ol&, int&, int&, bool)+0xcaa) [0x5d0f5a]]
: [/opt/app/ORCL/ggs/12.1.2.1.0/replicat(process_replicat_loop()+0xe11) [0x5ad7f1]]
: [/opt/app/ORCL/ggs/12.1.2.1.0/replicat(replicat_main(int, char**)+0x5ca) [0x5c6b7a]]
: [/opt/app/ORCL/ggs/12.1.2.1.0/replicat(ggs::gglib::MultiThreading::MainThread::ExecMain()+0x4f) [0x699c0f]]
: [/opt/app/ORCL/ggs/12.1.2.1.0/replicat(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::MultiThreading::T
hread::ThreadArgs*)+0x104) [0x699f44]]
: [/opt/app/ORCL/ggs/12.1.2.1.0/replicat(ggs::gglib::MultiThreading::MainThread::Run(int, char**)+0x8b) [0x69a04b]]
: [/opt/app/ORCL/ggs/12.1.2.1.0/replicat(main+0x3f) [0x5c580f]]
: [/lib64/libc.so.6(__libc_start_main+0x100) [0x3b5481ed20]]
: [/opt/app/ORCL/ggs/12.1.2.1.0/replicat() [0x517099]]
2021-02-11 05:01:21 ERROR OGG-01296 Error mapping from GGSUSER.GGS_HEARTBEAT to GGSUSER.GGS_LAGTIME.
------------------------------------------------------------------------------------------------------------------------
Solution:
I had to troubleshoot a situation, after GoldenGate capture some DML and replicate that, Oracle database needs to run insert trigger making some business integration.
After to upgrade this enviroment from GG 11.1.1.1 to 12.1.2 and DB 11.2.0.3 to 12.1.0.2, was identified that GoldenGate wasn’t running this triggers
So, found interesting resolution on Oracle Docs:
SUPPRESSTRIGGERS | NOSUPPRESSTRIGGERS
Valid for nonintegrated Replicat for Oracle. Controls whether or not triggers are fired during the Replicat session. Provides an alternative to manually disabling triggers. (Integrated Replicat does not require disabling of triggers on the target system.)
SUPPRESSTRIGGERS is the default and prevents triggers from firing on target objects that are configured for replication with Oracle GoldenGate. SUPPRESSTRIGGERS is valid for Oracle 11.2.0.2 and later 11gR2 versions. SUPPRESSTRIGGERS is not valid for 11gR1.
So, added 'DBOPTIONS NOSUPPRESSTRIGGERS' in the replicat parameter file.
OR
However, altough you probably want most of the triggers not to be executed on the target, it can be possible to override this setting, for example when you implement a heartbeat table.
The way to exclude the trigger from the SUPRRESSTRIGGERS setting is to grant the trigger an exception. This can be done as follows on the target:
SQL> EXEC dbms_ddl.set_trigger_firing_property('HR','trg_tbl_heartbeat', FALSE);'
The result of the statement above is that trigger hr.trg_tbl_heartbeat will go off in the target database.
=================================================================================
2nd.
If above steps are not working and still Replicate process Abended then go for below the steps.
upgrade Goldengate from 11g to 12c ?
If yes, you have two options :
1) Change the double quotes to single quotes
2) Add NOUSEANSISQLQUOTES into your GLOBALS file and restart the processes
/
NOUSEANSISQLQUOTES is intended for backward compatibility with the
parameter files of Oracle GoldenGate versions that predate version 12c,
where strings in double quotes are intended to be literals and
case-sensitive column names are not supported (whether or not they are
within quotes).
Reference : https://docs.oracle.com/goldengate/1212/gg-winux/GWURF/gg_parameters183.htm#GWURF728
Operational Activities:
*.Diagnostics & SR Creation
CellCLI> list celldisk where name='<Cell_Disk>' details
Disk Status -> Online/Offline #Both Cases
Error Count -> more than 0.
SR Creation
1. ASR Configure
2. Manual SR - read Doc ID - 761868.1
Reference :
Sundiag & Other Diagnostics: Oracle Exadata Diagnostic Information required for Disk Failures and some other Hardware issues (Doc ID 761868.1)
*.Drop Faulted Disk
1.Normal -> Confined offline
*.Amber LED
*.Rebalance Initiated
2.Confined offline -> offline
*.Blue LED
*.Ready for Replacement
Reference:
Drop Online Disk: Things to Check in ASM When Replacing an ONLINE disk from Exadata Storage Cell (Doc ID 1326611.1)
*.Disk replacement
FSE visit and Replacement
Reference:
Replace Procedure: How to Replace a Hard Drive in an Exadata Storage Server (Predictive Failure) (Doc ID 1390836.1)
