1. Identify Issue - Broker Configuration reports WARNINGS in standby database
DGMGRL> show configuration
Configuration - xxxprd_dgconfig
Protection Mode: MaxPerformance
Members:
dtxxxprof - Primary database
agxxxprof - Physical standby database
Warning: ORA-16809: multiple warnings detected for the database
Fast-Start Failover: DISABLED
Configuration Status:
WARNING (status updated 29 seconds ago)
2. Identify Issue - Drill down deeper for database reporting WARNINGS and we see issue with standby
redo logs being reported.
DGMGRL> show database agxxxprof
Database - agxxxprof
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 2.12 MByte/s
Real Time Query: OFF
Instance(s):
xxxprof1
Database Warning(s):
ORA-16789: standby redo logs configured incorrectly
Database Status:
WARNING
3. Check v$log to check the current redo logs, number of them and their sizes
select group#, thread#, status, bytes/1024/1024 from v$log ;
5 1 UNUSED 4096
2 1 UNUSED 4096
1 1 UNUSED 4096
4 2 UNUSED 4096
6 2 UNUSED 4096
3 2 UNUSED 4096
4. Check the v$standby_log for standby redo logs, number of them and their sizes
select group#, thread#, status, bytes/1024/1024 from v$standby_log ;
GROUP# THREAD# STATUS BYTES/1024/1024
------ ------- ---------- ---------------
20 1 UNASSIGNED 32768
21 1 ACTIVE 32768
22 1 UNASSIGNED 32768
23 1 UNASSIGNED 32768
25 2 ACTIVE 32768
26 2 UNASSIGNED 32768
27 2 UNASSIGNED 32768
28 2 UNASSIGNED 32768
5. Compare the outputs, Note size does not match redo log file sizes so these
are not correct also note the number of standby redo logs is not matching the redo logs
6. Now we can fix the issue, first Stop the managed recovery of standby database
alter database recover managed standby database cancel ;
7. create a new set of standby redo logs that match the redo logs, number of them
per thread and their size
alter database add standby logfile thread 1 group 31 size 4294967296 ;
alter database add standby logfile thread 1 group 32 size 4294967296 ;
alter database add standby logfile thread 2 group 33 size 4294967296 ;
alter database add standby logfile thread 2 group 34 size 4294967296 ;
alter database add standby logfile thread 1 group 35 size 4294967296 ;
alter database add standby logfile thread 2 group 36 size 4294967296 ;
8. Now we can Remove the old standby redo logs not active, this is so
we can e in a position to switch to the new logs and not stay in old logs
alter database drop standby logfile group 20 ;
alter database drop standby logfile group 22 ;
alter database drop standby logfile group 23 ;
alter database drop standby logfile group 26 ;
alter database drop standby logfile group 27 ;
alter database drop standby logfile group 28 ;
9. Restart the standby managed recovery again, so we can get out of the old standby
redo logs that are still used.
alter database recover managed standby database using current logfile disconnect ;
10. Check recovery catches up.
DGMGRL> show database agxxxprof
Database - agxxxprof
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 3.14 MByte/s
Real Time Query: OFF
Instance(s):
xxxprof1
Database Warning(s):
ORA-16789: standby redo logs configured incorrectly
Database Status:
WARNING
11. Switch logfile in primary database instances to switch into new standby redo for each thread,
In RAC must do in each instance so we cover all threads
alter system switch logfile ;
12. recheck standby logs on standby note that the old ones are switched out of
so we see the old standby redo logs are no longer ACTIVE.
select group#, thread#, status, bytes/1024/1024 from v$standby_log ;
GROUP# THREAD# STATUS BYTES/1024/1024
------- ------- ---------- ---------------
21 1 UNASSIGNED 32768
25 2 UNASSIGNED 32768
31 1 ACTIVE 4096
32 1 UNASSIGNED 4096
33 2 ACTIVE 4096
34 2 UNASSIGNED 4096
35 1 UNASSIGNED 4096
36 2 UNASSIGNED 4096
13. Now we Stop managed recovery of standby database so we can
finish removing the old standby redo logs.
alter database recover managed standby database cancel ;
14. Drop the remaining old standby redo logs
alter database drop standby logfile group 21 ;
alter database drop standby logfile group 25 ;
15. Restart managed recovery of standby database
alter database recover managed standby database using current logfile disconnect ;
16. Check standby redo logs in standby database, we can see only the new
standby redo logs we created.
select group#, thread#, status, bytes/1024/1024 from v$standby_log ;
GROUP# THREAD# STATUS BYTES/1024/1024
------- ------- ---------- ---------------
31 1 ACTIVE 4096
32 1 UNASSIGNED 4096
33 2 ACTIVE 4096
34 2 UNASSIGNED 4096
35 1 UNASSIGNED 4096
36 2 UNASSIGNED 4096
17. Recheck Broker Configuration and see that we have addressed the issue
reported
DGMGRL> show configuration
Configuration - xxxprd_dgconfig
Protection Mode: MaxPerformance
Members:
dtxxxprof - Primary database
agxxxprof - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 55 seconds ago)
DGMGRL> show database agxxxprof
Database - agxxxprof
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 0 seconds ago)
Apply Lag: 0 seconds (computed 0 seconds ago)
Average Apply Rate: 1.57 MByte/s
Real Time Query: OFF
Instance(s):
xxxprof1
Database Status:
SUCCESS
Thursday, July 7, 2016
Friday, March 4, 2016
MySQL Move tables to Innodb
1. Create script cr_move_tables_to_innodb.sql in
/opt/mysql/scripts
$ cd
/opt/mysql/scripts
$ vi
cr_move_tables_to_innodb.sql
use information_schema
select concat_ws ('', 'alter table ', table_schema, '.', table_name, ' engine=innodb;')
from tables
where engine <> 'innodb'
and table_schema not in ('information_schema','mysql','performance_schema')
into outfile "/opt/mysql/scripts/move_tables_to_innodb.sql" ;
2. Execute the script you created in step 1, the
execution will create a script call move_tables_to_innodb.sql in the
/opt/mysql/scripts location that will be a script that contains all the commands
to move tables not in innodb to innodb engine that are not in the internal
schemas.
Example:
$cd
/opt/mysql/scripts
$ mysql -u root -p
password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 494996
Server version: 5.6.27-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> source cr_move_tables_to_innodb.sql
Database changed
Query OK, 2 rows affected (0.46 sec)
mysql> exit
3. edit the script created by the process execution in step 2 and add a tee statement to the begging on the script so that when script is executed it will create a log that can be checked for errors.
$ mysql -u root -p
password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 494996
Server version: 5.6.27-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> source cr_move_tables_to_innodb.sql
Database changed
Query OK, 2 rows affected (0.46 sec)
mysql> exit
3. edit the script created by the process execution in step 2 and add a tee statement to the begging on the script so that when script is executed it will create a log that can be checked for errors.
$ cd
/opt/mysql/scripts
$ vi
move_tables_to_innodb.sql
tee /opt/mysql/scripts/logs/move_tables_to_innodb.out
4. Review script to make sure you are going to move the tables you expect to move
$ cd
/opt/mysql/scripts
$ vi
move_tables_to_innodb.sql
5. Execute the generated script move_tables_to_innodb.sql modified in step 3 and reviewed in step 4.
Example:
$mysql -u root -p
password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 494996
Server version: 5.6.27-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> source move_tables_to_innodb.sql
6. Once process is complete review the output file for any errors and issues
$ vi
/opt/mysql/scripts/logs/move_tables_to_innodb.out
7. Correct any errors or issues.
Golden Gate 12c Apply Patch 21913095
Stage Patch
1.
Download January Security Patch 2016
Path # 21913095
2.
Put downloaded patch into software staging
location
Example staging location on server: /u01/app/oracle/software/gg_121210
Example:
[2016-02-02 14:04.54] /drives/y/Database
Administration/Patches/GG_Jan2016
[mmessina.JXWPRDVDS20] → scp *
agodevorl03:/u01/app/oracle/software/gg_121210
### This
system is for authorized users only.
mmessina@agodevorl03's
password:
p21913095_1212110_Linux-x86-64.zip
100% 130MB 4.1MB/s
00:32
3.
Login to the server to apply Golden Gate Patch
on and get to the oracle account.
ssh servername
sudo su – oracle
4.
Goto the location where the Golden Gate Patch is
staged
cd /u01/app/oracle/software/gg_121210
5.
Unzip the Golden Gate Patch
unzip p21913095_1212110_Linux-x86-64.zip
6. Ensure
Golden Gate Home is in the /etc/oratab
Example:
vi /ec/oratab
ggs:/ggs:N
Update OPatch to Latest Version
1. Download
latest version of OPatch *** THIS MUST BE
THE LATEST 11.2.0.3 OPatch
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=224346.1
2.
Stage latest version of Opatch on server where
you staged the Golden Gate Patch
Example:
[2016-02-02 14:24.56] /drives/y/Database
Administration/Patches/GG_Jan2016
[mmessina.JXWPRDVDS20] → scp
p6880880_121010_Linux-x86-64.zip agodevorl03:/u01/app/oracle/software/gg_121210
### This
system is for authorized users only.
mmessina@agodevorl03's
password:
p6880880_112000_Linux-x86-64.zip 100% 50MB
4.2MB/s 00:12
3.
Login to the server and get to the Oracle
account where OPatch is staged
ssh servername
sudo su – oracle
4.
Go to the stage location and unsip OPatch
cd /u01/app/oracle/software/gg-121210
unzip p6880880_112000_Linux-x86-64.zip
5.
Update OPatch for the Golden Gate Home
Directory
sudo su – oracle
. oraenv
ggs
cd $ORACLE_HOME
cd OPatch
./OPatch version
rm –rf *
cp –r
/u01/app/oracle/software/gg-121210/OPatch/* ./
./opatch version
Apply Golden Gate Patch
1.
Login to the server to be patched and goto the
oracle account
ssh servername
sudo su – oracle
2.
Shutdown all running golden gate processes
. oraenv
defdb
cd /ggs
./ggsci
stop *
stop jagent
stop mgr
3.
Check the Golden Gate Home is set to ORACLE_HOME
. oraenv
ggs
echo $ORACLE_HOME
/ggs
4.
Ensure the OPatch location in the Golden Gate
Home is in the path
export PATH=$ORACLE_HOME/OPatch:$PATH
which opatch
5.
Goto the Golden Gate January 2016 unzipped
location
cd /u01/app/oracle/software/gg-121210/21913095
6.
Apply the patch
opatch apply
7.
Check patch applied ok
opatch lsinventory
8.
Restart all Golden Gate Processes
. oraenv
defdb -- local
database Golden Gate is running for
cd /ggs
./ggsci
start mgr
start jagent --
May not apply to all environments
start *
9.
Check that all Golden Gate Processes are back
running
. oraenv
defdb
./ggsci
info all
Oracle ADR
Summary
The goal for every database administrator (DBA) is to be
proactive with identifying any issues or potential issues. By being proactive a DBA can potentially
prevent damage to the database and its data, reduce problem/issue diagnosis
time leading to a reducing in resolution time.
With the introduction with Oracle Database 11g Oracle has
provided a file based repository that is automatically maintained by the
database called the Automatic Diagnostic Repository (ADR). The ADR captures data at the time of an event
and holds the data on the events allowing for review and packaging.
Setup / Location Details
The location of the Automatic Diagnostic Repository (ADR) is
set via the diagnostic_dest database instance initialization parameter. When the diagnostic_dest instance
initialization parameter is not set the location defaults to
ORACLE_HOME/log. The diagnostic_dest
database initialization parameter replaces the background_dump_dest, core_dump_dest
and user_dump_dest database initialization parameter.
The structure of the automatic diagnostic being a file based
repository the structure is made up of directories laid out in a common
structure based on the component.
ORACLE_BASE/diag or ORACLE_HOME/log/diag
asm
+asm
+
alert
cdump
hm
incident
incpkg
ir
lck
metadata
stage
sweep
trace
clients
crs
diagtool
lsnrctl
netcman
ofm
rdbms
incpkg
hm
metadata
ir
alert
sweep
stage
lck
incident
trace
cdump
tnslsnr
Viewing the ADR
The automatic diagnostic repository can be viewed via a
command line interface or Oracle Enterprise Manager.
adrci
The automatic diagnostic repository command line interface
(adrci) is a command line utility that will allow the viewing of the data
collected in the Automatic Diagnostic Repository.
Show Incidents
1. run the adrci utility
bash-3.00$ adrci
ADRCI: Release 11.2.0.1.0 - Production on Thu Feb 25 07:45:29 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
ADR base = "/u01/app/oracle"
2. Show ADR Homes
adrci> show homes
ADR Homes:
diag/rdbms/dbua0/DBUA0
diag/rdbms/rman/rman
diag/rdbms/oemgc/oemgc
diag/clients/user_oracle/host_2129719189_11
diag/clients/user_oracle/host_2129719189_76
diag/tnslsnr/grid/listener
3. Set ADR Home
adrci> set home diag/rdbms/rman/rman
4. Show Incidents in the ADR Home
adrci> show incidents
ADR Home = /u01/app/oracle/diag/rdbms/rman/rman:
*************************************************************************
INCIDENT_ID
PROBLEM_KEY
CREATE_TIME
--------------------
-----------------------------------------------------------
----------------------------------------
3889 ORA 7445 [strcpy()+28]
2010-02-04 09:27:39.861000 -06:00
3881
ORA 7445 [strcpy()+28]
2010-02-04 09:27:39.953000 -06:00
3897
ORA 7445 [strcpy()+28]
2010-02-04 09:34:21.672000 -06:00
3857
ORA 7445 [strcpy()+28]
2010-02-04 09:34:21.772000 -06:00
3882
ORA 7445 [strcpy()+60]
2010-02-04 09:46:05.110000 -06:00
…. …. ….
5109
ORA 7445 [strcpy()+60]
2010-02-10 00:30:48.458000 -06:00
6034
ORA 7445 [strcpy()+108] 2010-02-10
00:30:48.559000 -06:00
Show Specific Incident
adrci> show incident -mode detail -p
"incident_id=6183"
ADR Home = /u01/app/oracle/diag/rdbms/rman/rman:
*************************************************************************
**********************************************************
INCIDENT INFO
RECORD 1
**********************************************************
INCIDENT_ID 6183
STATUS ready
CREATE_TIME 2010-02-24 06:36:54.797000
-06:00
PROBLEM_ID 3
CLOSE_TIME
FLOOD_CONTROLLED none
ERROR_FACILITY ORA
ERROR_NUMBER 7445
ERROR_ARG 1 strcpy()+108
ERROR_ARG 2 SIGSEGV
ERROR_ARG 3 ADDR:0xC
ERROR_ARG 4 PC:0xFFFFFFFF7C53B52C
ERROR_ARG 5 Address not mapped to
object
ERROR_ARG 6
ERROR_ARG 7
ERROR_ARG 8
ERROR_ARG 9
ERROR_ARG 10
ERROR_ARG 11
ERROR_ARG 12
SIGNALLING_COMPONENT
SIGNALLING_SUBCOMPONENT
SUSPECT_COMPONENT
SUSPECT_SUBCOMPONENT
ECID
IMPACTS 0
PROBLEM_KEY ORA 7445 [strcpy()+108]
FIRST_INCIDENT 3885
FIRSTINC_TIME 2010-02-04 10:55:39.842000
-06:00
LAST_INCIDENT 6771
LASTINC_TIME 2010-02-25 07:36:37.179000
-06:00
IMPACT1 0
IMPACT2 0
IMPACT3 0
IMPACT4 0
OWNER_ID 1
INCIDENT_FILE
/u01/app/oracle/diag/rdbms/rman/rman/incident/incdir_6183/rman_ora_10992_i6183.trc
OWNER_ID 1
INCIDENT_FILE
/u01/app/oracle/diag/rdbms/rman/rman/trace/rman_ora_10992.trc
1 rows fetched\
Delete/Purge Incidents
adrci> purge -age 999
** Where 999 is number of days older then to purge
Show Problems
1. run the adrci utility
bash-3.00$ adrci
ADRCI: Release 11.2.0.1.0 - Production on Thu Feb 25 07:45:29 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
ADR base = "/u01/app/oracle"
2. Show ADR Homes
adrci> show homes
ADR Homes:
diag/rdbms/dbua0/DBUA0
diag/rdbms/rman/rman
diag/rdbms/oemgc/oemgc
diag/clients/user_oracle/host_2129719189_11
diag/clients/user_oracle/host_2129719189_76
diag/tnslsnr/grid/listener
3. Set ADR Home
adrci> set home diag/rdbms/rman/rman
4. Show Problems in ADR Home
adrci> show problem
ADR Home = /u01/app/oracle/diag/rdbms/rman/rman:
*************************************************************************
PROBLEM_ID
PROBLEM_KEY LAST_INCIDENT LASTINC_TIME
--------------------
-----------------------------------------------------------
-------------------- ----------------------------------------
2 ORA 7445 [strcpy()+60] 6774 2010-02-25 07:37:35.224000 -06:00
3
ORA 7445 [strcpy()+108] 6771 2010-02-25 07:36:37.179000 -06:00
4
ORA 445
6706 2010-02-25
06:36:34.685000 -06:00
1
ORA 7445 [strcpy()+28] 3857 2010-02-04 09:34:21.772000 -06:00
4 rows fetched
Show Specific Problem
adrci> show problem -p "problem_id=1"
ADR Home = /u01/app/oracle/diag/rdbms/rman/rman:
*************************************************************************
PROBLEM_ID
PROBLEM_KEY
LAST_INCIDENT
LASTINC_TIME
--------------------
-----------------------------------------------------------
-------------------- ----------------------------------------
1
ORA 7445 [strcpy()+28] 3857 2010-02-04 09:34:21.772000 -06:00
1 rows fetched
Delete a specific Problem
adci> delete from problem where problem_id=1
1
Row Deleted
Incident Packaging Service (IPS)
The incident packaging service provides a great way to
gather the information such as traces, alert log, etc needed to provide to
Oracle support when opening a Service Request on a particular problem.
Package Incident
To be able to send the information on the incident to Oracle
support we must first package the incident.
From the list of incidents from the show incident command we can package
the specific incident we want to send to Oracle Support.
Using the ips create package command we can package an
incident.
Example:
adrci>
ips create package incident 6183
Created
package 3 based on incident id 6183, correlation level typical
adrci>
ips generate package 3
Generated
package 3 in file /export/home/oracle/ORA7445st_20100225091554_COM_1.zip, mode
complete
Package Problem
To be able to send the information on the problem to Oracle
support we must first package the incident.
From the list of problems from the show problem command we can package
the specific problem we want to send to Oracle Support.
Using the ips create package command we can package a
problem.
Example:
adrci>
ips create package problem 1
Created
package 4 based on problem id 1, correlation level typical
adrci>
ips generate package 4
Generated
package 4 in file /export/home/oracle/ORA7445st_20100225091628_COM_1.zip, mode
complete
Subscribe to:
Posts (Atom)
