Contents
- 金碟 南方报业
- multiple controlfile
- add OCR disk group
-
Troubleshooting
- Export/Import DataPump Parameter VERSION - Compatibility of Data Pump Between Different Oracle Versions [Video] (Doc ID 553337.1)
- 999
- Table AUD$[FGA_LOG$] should use Automatic Segment Space Management for fxwl
- impdp errors
- ORA-39151 Error whil importing DB
- grid install failed
- CRS-4402
- hotplug no
- Oracle Linux: Shell Script to Calculate Values Recommended Linux HugePages / HugeTLB Configuration (Doc ID 401749.1)
- Verify transparent hugepages are disabled
- impdp&expdp dump_dir
- REDO log group
- RAC test
- preferences
金碟 南方报业
ID: 744043 PW: 289
multiple controlfile
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
+SSD_DATA/fxwl/controlfile/current.264.927034859
alter system set control_files='+SSD_DATA/fxwl/controlfile/current.264.927034859', '+FRA' scope=spfile;
[grid@rac01 ~]$ srvctl stop database -d fxwl
[grid@rac01 ~]$ srvctl start database -d fxwl -o nomount
SQL> show parameter control_files;
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
control_files string +SSD_DATA/fxwl/controlfile/cur
rent.264.927034859, +FRA
[oracle@rac01 orachk]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on 星期五 11月 4 21:17:59 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: FXWL (not mounted)
RMAN> restore controlfile from '+SSD_DATA/fxwl/controlfile/current.264.927034859';
Starting restore at 04-11月-2016 21:22:12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1701 instance=fxwl1 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+SSD_DATA/fxwl/controlfile/current.264.927034859
output file name=+FRA/fxwl/controlfile/current.256.927062533
Finished restore at 04-11月-2016 21:22:14
RMAN> exit
alter system set control_files='+SSD_DATA/fxwl/controlfile/current.264.927034859', '+FRA/fxwl/controlfile/current.256.927062533' scope=spfile;
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
+SSD_DATA/fxwl/controlfile/current.264.927034859
+FRA/fxwl/controlfile/current.256.927062533
add OCR disk group
[root@rac01 ~]# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 3156
Available space (kbytes) : 258964
ID : 1241825630
Device/File Name : +VOTE
Device/File integrity check succeeded
Device/File not configured
Device/File not configured
Device/File not configured
Device/File not configured
Cluster registry integrity check succeeded
Logical corruption check succeeded
[root@rac01 ~]# ocrconfig -add +CRS
[root@rac01 ~]# ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 3156
Available space (kbytes) : 258964
ID : 1241825630
Device/File Name : +VOTE
Device/File integrity check succeeded
Device/File Name : +CRS
Device/File integrity check succeeded
Device/File not configured
Device/File not configured
Device/File not configured
Cluster registry integrity check succeeded
Logical corruption check succeeded
Troubleshooting
Export/Import DataPump Parameter VERSION - Compatibility of Data Pump Between Different Oracle Versions [Video] (Doc ID 553337.1)
999
ALTER SYSTEM SET _memory_broker_stat_interval=999 SCOPE=BOTH;
Table AUD$[FGA_LOG$] should use Automatic Segment Space Management for fxwl
SQL> select t.table_name,ts.segment_space_management from dba_tables t, dba_tablespaces ts where ts.tablespace_name = t.tablespace_name and t.table_name in ('AUD$','FGA_LOG$');
TABLE_NAME SEGMEN
------------------------------ ------
AUD$ MANUAL
FGA_LOG$ MANUAL
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,--this moves table AUD$
audit_trail_location_value => 'SYSAUX');
END;
/
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,--this moves table FGA_LOG$
audit_trail_location_value => 'SYSAUX');
END;
/
PL/SQL procedure successfully completed.
SQL> SQL> 2 3 4 5
PL/SQL procedure successfully completed.
SQL> SQL> SQL>
SQL>
SQL> select t.table_name,ts.segment_space_management from dba_tables t, dba_tablespaces ts where ts.tablespace_name = t.tablespace_name and t.table_name in ('AUD$','FGA_LOG$');
TABLE_NAME SEGMEN
------------------------------ ------
AUD$ AUTO
FGA_LOG$ AUTO
SQL> select t.table_name,ts.segment_space_management from dba_tables t, dba_tablespaces ts where ts.tablespace_name = t.tablespace_name and t.table_name in ('AUD$','FGA_LOG$');
TABLE_NAME SEGMEN
------------------------------ ------
AUD$ AUTO
FGA_LOG$ AUTO
impdp errors
下面是我昨天导入的全备的记录：
[oracle@rac02 dump_dir]$ grep ORA- impdpDB11G.log | awk -F":" '{ print $1 }' | sort | uniq -c
2 ORA-00001 is OK. Doc ID 783299.1
2 ORA-22812 is not OK??? Doc ID 8856467.8
1 ORA-29357 is OK. Doc ID 1499141.1
3557 ORA-31684 is OK. Doc ID 465019.1
1 ORA-31685 is not OK??? Doc ID 1568333.1 => Grant the DBA role back to user SYSTEM, Re-run the import job.
2 ORA-39082 is not OK??? Doc ID 1969347.1
5 ORA-39083 is OK.
2996 ORA-39111 is OK.
1335 ORA-39151 is OK.
# 20161104 13:00分导入fxwl schema只有以下四个报错。
ORA-00001
ORA-31684
ORA-39081
ORA-39083
________________________________________
发件人: 陈美林
发送时间: 2016年11月4日 10:17
收件人: 刘跃鹏
抄送: 刘智峰
主题: lfby impdp full errors
[root@rac02 dump_dir2]# grep ORA- impdpFrom11.2.0.1_full.log | awk -F":" '{ print $1 }' | sort | uniq -c
3 ORA-00001
1 ORA-00054
4 ORA-00942
20 ORA-01403
2 ORA-01452
1 ORA-02298
14 ORA-02354
1 ORA-02437
8 ORA-20000
2 ORA-22812
6 ORA-23327
1 ORA-29357
1 ORA-29400
1 ORA-29913
3546 ORA-31684
1 ORA-31685
15 ORA-31693
2 ORA-39082
39 ORA-39083
2996 ORA-39111
2 ORA-39112
14 ORA-39779
ORA-39151 Error whil importing DB
https://community.oracle.com/thread/763945
TABLE_EXISTS_ACTION={SKIP | APPEND | TRUNCATE | REPLACE} - Provides Data Pump Import with instructions to perform when it finds a table in the target database with the same name as a table contained in the input file. TABLE_EXISTS_ACTION=SKIP - Existing tables are bypassed if they already exist. No action is taken.
grid install failed
Adding daemon to inittab CRS-4124: Oracle High Availability Services startup failed. CRS-4000: Command Start failed, or completed with errors. ohasd failed to start: Inappropriate ioctl for device ohasd failed to start at /u01/app/11.2.0/grid/crs/install/rootcrs.pl line 443.
CRS-4402
[root@rac02 ~]# oracleasm-discover
Using ASMLib from /opt/oracle/extapi/64/asm/orcl/1/libasm.so
[root@rac02 ~]# /u01/app/11.2.0/grid/root.sh
Performing root user operation for Oracle 11g
The following environment variables are set as:
ORACLE_OWNER= grid
ORACLE_HOME= /u01/app/11.2.0/grid
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 ...
Creating /etc/oratab file...
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.
Using configuration parameter file: /u01/app/11.2.0/grid/crs/install/crsconfig_params
Creating trace directory
User ignored Prerequisites during installation
Installing Trace File Analyzer
OLR initialization - successful
Adding Clusterware entries to upstart
CRS-4402: The CSS daemon was started in exclusive mode but found an active CSS daemon on node rac01, number 1, and is terminating
An active cluster was found during exclusive startup, restarting to join the cluster
Configure Oracle Grid Infrastructure for a Cluster ... succeeded
hotplug no
Network interface going down when dynamically adding disks to storage using udev in RHEL 6 (Doc ID 1569028.1)
Oracle Linux: Shell Script to Calculate Values Recommended Linux HugePages / HugeTLB Configuration (Doc ID 401749.1)
#
# hugepages_settings.sh
#
# Linux bash script to compute values for the
# recommended HugePages/HugeTLB configuration
# on Oracle Linux
#
# Note: This script does calculation for all shared memory
# segments available when the script is run, no matter it
# is an Oracle RDBMS shared memory segment or not.
#
# This script is provided by Doc ID 401749.1 from My Oracle Support
# http://support.oracle.com
# Welcome text
echo "
This script is provided by Doc ID 401749.1 from My Oracle Support
(http://support.oracle.com) where it is intended to compute values for
the recommended HugePages/HugeTLB configuration for the current shared
memory segments on Oracle Linux. Before proceeding with the execution please note following:
* For ASM instance, it needs to configure ASMM instead of AMM.
* The 'pga_aggregate_target' is outside the SGA and
you should accommodate this while calculating SGA size.
* In case you changes the DB SGA size,
as the new SGA will not fit in the previous HugePages configuration,
it had better disable the whole HugePages,
start the DB with new SGA size and run the script again.
And make sure that:
* Oracle Database instance(s) are up and running
* Oracle Database 11g Automatic Memory Management (AMM) is not setup
(See Doc ID 749851.1)
* The shared memory segments can be listed by command:
# ipcs -m
Press Enter to proceed..."
read
# Check for the kernel version
KERN=`uname -r | awk -F. '{ printf("%d.%d\n",$1,$2); }'`
# Find out the HugePage size
HPG_SZ=`grep Hugepagesize /proc/meminfo | awk '{print $2}'`
if [ -z "$HPG_SZ" ];then
echo "The hugepages may not be supported in the system where the script is being executed."
exit 1
fi
# Initialize the counter
NUM_PG=0
# Cumulative number of pages required to handle the running shared memory segments
for SEG_BYTES in `ipcs -m | cut -c44-300 | awk '{print $1}' | grep "[0-9][0-9]*"`
do
MIN_PG=`echo "$SEG_BYTES/($HPG_SZ*1024)" | bc -q`
if [ $MIN_PG -gt 0 ]; then
NUM_PG=`echo "$NUM_PG+$MIN_PG+1" | bc -q`
fi
done
RES_BYTES=`echo "$NUM_PG * $HPG_SZ * 1024" | bc -q`
# An SGA less than 100MB does not make sense
# Bail out if that is the case
if [ $RES_BYTES -lt 100000000 ]; then
echo "***********"
echo "** ERROR **"
echo "***********"
echo "Sorry! There are not enough total of shared memory segments allocated for
HugePages configuration. HugePages can only be used for shared memory segments
that you can list by command:
# ipcs -m
of a size that can match an Oracle Database SGA. Please make sure that:
* Oracle Database instance is up and running
* Oracle Database 11g Automatic Memory Management (AMM) is not configured"
exit 1
fi
# Finish with results
case $KERN in
'2.2') echo "Kernel version $KERN is not supported. Exiting." ;;
'2.4') HUGETLB_POOL=`echo "$NUM_PG*$HPG_SZ/1024" | bc -q`;
echo "Recommended setting: vm.hugetlb_pool = $HUGETLB_POOL" ;;
'2.6') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
'3.8') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
'3.10') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
'4.1') echo "Recommended setting: vm.nr_hugepages = $NUM_PG" ;;
esac
# End$ ./hugepages_settings.sh ... Recommended setting: vm.nr_hugepages = 67
Verify transparent hugepages are disabled
Benefit / Impact:
Linux transparent huge pages are enabled by default in OEL 6 and SuSE 11 which might cause soft lockup of CPUs and make system unresponsive which can cause node eviction.
if AnonHugePages are found and value is greater than 0 in /proc/meminfo, means transparent huge pages are enabled.
- Risk:
Because Transparent HugePages are known to cause unexpected node reboots and performance problems with RAC, Oracle strongly advises to disable the use of Transparent HugePages. In addition, Transparent Hugepages may cause problems even in a single-instance database environment with unexpected performance problems or delays. As such, Oracle recommends disabling Transparent HugePages on all Database servers running Oracle Action / Repair: To turn this feature off,put this in /etc/rc.local:-
# Verify transparent hugepages are disabled echo never > /sys/kernel/mm/transparent_hugepage/enabled echo never > /sys/kernel/mm/transparent_hugepage/defrag
impdp&expdp dump_dir
[oracle@rac02 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on 星期四 11月 3 14:08:08 2016 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> ALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK; SQL> CREATE OR REPLACE DIRECTORY dump_dir AS '/u01/dump_dir/'; SQL> GRANT READ, WRITE ON DIRECTORY dump_dir TO scott; Directory created.
impdp \" system as sysdba \" full=Y directory=dump_dir dumpfile=FXWL_FULL_DATA_BACKUP.DMP logfile=impdpDB11G.log
REDO log group
spool log.lst set echo off set feedback off set linesize 120 set pagesize 35 set trim on set trims on set lines 120 col group# format 999 col thread# format 999 col member format a70 wrap col status format a10 col archived format a10 col fsize format 999 heading "Size (MB)" select l.group#, l.thread#, f.member, l.archived, l.status, (bytes/1024/1024) fsize from v$log l, v$logfile f where f.group# = l.group# order by 1,2 / spool off
ALTER DATABASE ADD LOGFILE thread 1 GROUP 5 ('+REDO') SIZE 512M;
ALTER DATABASE ADD LOGFILE thread 1 GROUP 6 ('+REDO') SIZE 512M;
ALTER DATABASE ADD LOGFILE thread 1 GROUP 7 ('+REDO') SIZE 512M;
ALTER DATABASE ADD LOGFILE thread 1 GROUP 8 ('+REDO') SIZE 512M;
ALTER DATABASE ADD LOGFILE thread 2 GROUP 9 ('+REDO') SIZE 512M;
ALTER DATABASE ADD LOGFILE thread 2 GROUP 10 ('+REDO') SIZE 512M;
ALTER DATABASE ADD LOGFILE thread 2 GROUP 11 ('+REDO') SIZE 512M;
ALTER DATABASE ADD LOGFILE thread 2 GROUP 12 ('+REDO') SIZE 512M;SQL> ALTER DATABASE DROP LOGFILE GROUP 2; ALTER DATABASE DROP LOGFILE GROUP 2 * ERROR at line 1: ORA-01623: 日志 2 是实例 fxwl1 (线程 1) 的当前日志 - 无法删除 ORA-00312: 联机日志 2 线程 1: '+SSD_DATA/fxwl/onlinelog/group_2.258.927034863'
ALTER SYSTEM SWITCH LOGFILE; ALTER DATABASE DROP LOGFILE GROUP 2; alter system checkpoint;
RAC test
ifconfig em2 down
停止RAC02心跳em2口，RAC02被移出群集。
2016-11-04 16:05:26.125: [cssd(13682)]CRS-1612:Network communication with node rac02 (2) missing for 50% of timeout interval. Removal of this node from cluster in 14.230 seconds 2016-11-04 16:05:33.127: [cssd(13682)]CRS-1611:Network communication with node rac02 (2) missing for 75% of timeout interval. Removal of this node from cluster in 7.230 seconds 2016-11-04 16:05:38.128: [cssd(13682)]CRS-1610:Network communication with node rac02 (2) missing for 90% of timeout interval. Removal of this node from cluster in 2.230 seconds 2016-11-04 16:05:40.360: [cssd(13682)]CRS-1607:Node rac02 is being evicted in cluster incarnation 373666531; details at (:CSSNM00007:) in /u01/app/11.2.0/grid/log/rac01/cssd/ocssd.log. 2016-11-04 16:05:43.364: [cssd(13682)]CRS-1625:Node rac02, number 2, was manually shut down 2016-11-04 16:05:43.372: [cssd(13682)]CRS-1601:CSSD Reconfiguration complete. Active nodes are rac01 . 2016-11-04 16:05:43.386: [ctssd(13788)]CRS-2407:The new Cluster Time Synchronization Service reference node is host rac01. 2016-11-04 16:05:44.907: [crsd(14141)]CRS-5504:Node down event reported for node 'rac02'. 2016-11-04 16:05:45.552: [client(146549)]CRS-4743:File /u01/app/11.2.0/grid/oc4j/j2ee/home/OC4J_DBWLM_config/system-jazn-data.xml was updated from OCR(Size: 13365(New), 13378(Old) bytes) 2016-11-04 16:06:02.345: [crsd(14141)]CRS-2773:Server 'rac02' has been removed from pool 'Generic'. 2016-11-04 16:06:02.345: [crsd(14141)]CRS-2773:Server 'rac02' has been removed from pool 'ora.fxwl'.