正德厚生,臻于至善

expdp/impdp job基本管理

xuwanxin1987阅读(26)

一、Data Pump Job 交互命令管理
    在我们使用expdp 或 impdp 时, 当 expdp/impdp 指令发出时,如果不做任何操作,会在终端打印出 expdp/impdp 的过程,我们称之为交互模式。此时如果我们使用 Ctrl+C 组合键,仅仅是退出当前的交互模式,导出操作不会停止。

例如我们执行以下expdp 脚本
cd /backup/scripts
nohup expdp 'userid="/ as sysdba"' parfile=auexpdp.dat 2>&1 &

cat auexpdp.dat
#$Header: auexpdp.dat 120.4 2019/07/24 06:19:27 gong ship $
# 120.1 - Removed exclude, transform parameters
# 120.2 - Added metrics parameter
# 120.3 - Added QUERY parameter
# 120.4 - Added parallel parameter

directory=dmpdir
reuse_dumpfiles=yes
dumpfile=fullexp1_%U.dmp,fullexp2_%U.dmp,fullexp3_%U.dmp,fullexp4_%U.dmp,fullexp5_%U.dmp,fullexp6_%U.dmp,fullexp7_%U.dmp,fullexp8_%U.dmp
filesize=10485760000
full=y
logfile=fullexp_20210723.log
metrics=y
parallel=8
cluster=N
compression=NONE
estimate=STATISTICS
exclude=TABLE_STATISTICS,INDEX_STATISTICS
#exclude=TABLE:"like 'XLA_GLT%'"
#exclude=INDEX:"like 'XLA_GLT%'"
QUERY=applsys.wf_item_attribute_values:"where item_type!='WFERROR' and name != 'EVENT_MESSAGE'"

dumpfile=fullexp1_%U.dmp,fullexp2_%U.dmp,fullexp3_%U.dmp,fullexp4_%U.dmp,fullexp5_%U.dmp,fullexp6_%U.dmp,fullexp7_%U.dmp,fullexp8_%U.dmp,fullexp9_%U.dmp,fullexp10_%U.dmp,fullexp11_%U.dmp,fullexp12_%U.dmp,fullexp13_%U.dmp,fullexp14_%U.dmp,fullexp15_%U.dmp,fullexp16_%U.dmp

退出之后,要重新查看进程,执行如下命令可以附加job , 会再次进入交互状态 。
expdp 'userid="/ as sysdba"' attach=SYS_EXPORT_FULL_01
Export> STOP_JOB=IMMEDIATE
Are you sure you wish to stop this job ([yes]/no): yes

交互模式常用命令
HELP : 交互命令汇总
KILL_JOB : 删除作业。
PARALLEL :   更改当前作业的活动 的并行数
STATUS: 查看当前 JOB 的状态
STOP_JOB :   顺序关闭执行的作业并退出客户 端 。
STOP_JOB=IMMEDIATE :   将立即关闭数据泵作业。
START_JOB :  启动/恢复当前作业。

二、如何清理已经没用的job
1.  使用sqlplus 查看当前存在的 job
SQL> set lines 200 pages 50000
col operation for a30
col job_mode for a30
SELECT owner_name, job_name, rtrim(operation) "OPERATION", rtrim(job_mode)
"JOB_MODE", state, attached_sessions FROM dba_datapump_jobs
WHERE job_name NOT LIKE 'BIN$%' ORDER BY 1,2;

2.  确保db_datapump_jobs 列表中要删除的 jobs 状态为 ” NOT RUNNING ”
3.  查看控制表信息
SQL> COL owner.object FORMAT a50
SELECT o.status, o.object_id, o.object_type,
o.owner||'.'||object_name "OWNER.OBJECT"
FROM dba_objects o, dba_datapump_jobs j
WHERE o.owner=j.owner_name AND o.object_name=j.job_name
AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2;

4.  删除上面查询到的表
SQL> drop table SYS.SYS_EXPORT_FULL_01 purge;
SQL> drop dba_recyclebin;

附加:清除stoped impdp/expdp job的方法
stoped impdp/expdp job会在dba_datapump_jobs中留下一条记录,显示为not running.
清除stopped job分两种情况:
1) job能够attach
如果job能够attach, 则可以attach后再kill job.
如:expdp system/**** attach=SYS_EXPORT_TABLE_01
    kill_job

2) job无法attach
如果job无法attach, 则需要删除连接DataPump的用户下的master table.
如:conn system/*****
        drop table SYS_EXPORT_TABLE_01 (master table名称一般与job name相同)

以上的用户名和job name都可以从dba_datapump_jobs中得到。

RMAN SET NEWNAME

xuwanxin1987阅读(18)

在做数据恢复时,偶尔会碰到需要对数据文件位置调整的案例,在这种情况下,可以在RMAN中使用SET NEWNAME命令。

在Oracle 11g之前,RMAN只支持SET NEWNAME FOR DATAFILE,在Oracle 11g中增加了SET NEWNAME FOR TEMPFILE/SET NEWNAME FOR TABLESPACE/SET NEWNAME FOR DATABASE的命令。

优先顺序如下:
1.SET NEWNAME FOR DATAFILE and SET NEWNAME FOR TEMPFILE
2.SET NEWNAME FOR TABLESPACE
3.SET NEWNAME FOR DATABASE

当使用SET NEWNAME FOR DATAFILE/TEMPFILE的时候,可以使用下面的SQL生成所有的SET NEWNAME命令:
set lines 200 pages 50000
select 'alter database rename file '''||member||''' to '''||replace(member,'/xxebs/origin/db/apps_st/data/','/yyebs/sit/db/apps_st/data/')||''';' from v$logfile;

alter database open resetlogs;

select 'set newname for datafile ' || file_id || ' to ''' || replace(file_name,'/xxebs/origin/db/apps_st/data/','/yyebs/sit/db/apps_st/data/') || ''';' from dba_data_files order by file_id;

select 'set newname for tempfile ' || file_id || ' to ''' || replace(file_name,'/xxebs/origin/db/apps_st/data/','/yyebs/sit/db/apps_st/data/') || ''';' from dba_temp_files order by file_id;

当使用FOR TABLESPACE/DATABASE命令的时候,可以指定下面的变量格式:
%b  Specifies the file name stripped of directory paths.对应的文件名称
%f  Specifies the absolute file number of the data file for which the new name is generated. 数据文件的绝对文件号
%U  Specifies the following format: data-D-%d_id-%I_TS-%N_FNO-%f
%I  Specifies the DBID 对应的DBID
%N  Specifies the tablespace name 对应的表空间名称

其中前面三个变量必须指定一个,后面2个是可选的。
常见的,我们需要保持数据文件一直,值使用%b即可。

run{
allocate channel d1 device type disk;
allocate channel d2 device type disk;
allocate channel d3 device type disk;
allocate channel d4 device type disk;
allocate channel d5 device type disk;
allocate channel d6 device type disk;
allocate channel d7 device type disk;
allocate channel d8 device type disk;
catalog start with '/backup/L0/';
set newname for database to '/yyebs/sit/db/apps_st/data/%b';
#set newname for datafile '/xxebs/origin/db/apps_st/data/%b' to '/yyebs/sit/db/apps_st/data/%b';
#set newname for tempfile '/xxebs/origin/db/apps_st/data/%b' to '/yyebs/sit/db/apps_st/data/%b';
sql'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';
set until time '2021-07-21 18:08:53';
restore database;
switch datafile all;
switch tempfile all; 
recover database;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
release channel d5;
release channel d6;
release channel d7;
release channel d8;
}

官方文档提供下面命令来判别具体原因.
RMAN> list incarnation of database;
 
RMAN> list backup of datafile 1;
 
RMAN> list copy of datafile 1;
 
RMAN> list backup summary;

set lines 160 pages 9999 
select name from v$datafile 
union 
select member from v$logfile 
union 
select name from v$controlfile 
union 
select name from v$tempfile; 

参考资料:
http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmdupad.htm
http://docs.oracle.com/cd/B19306_01/backup.102/b14194/rcmsynta055.htm#i1003832
http://docs.oracle.com/cd/B10501_01/server.920/a96565/rcmsynta50.htm#RCMRF153

How To Generate FND Debug Log File For Americas Localization?

xuwanxin1987阅读(48)

R12: How To Generate FND Debug Log File? (Doc ID 2225305.1)

1) Enable fnd logging by following the following steps.

Using the System Administration responsibility
Access the System Profile Values Form using the path Profile -> System

In the Find System Profile Values window click on the User checkbox
select the appropriate user
In the Profile field enter FND:%Debug%
Press the find Button

Please set the following profile options for the user in the System
Profile Values window
FND: Debug Log Enabled - Yes
FND: Debug Log Level - Statement
FND: Debug Log Module - %

Save the changes.

Log out of Applications and log in again.

2) Execute the following sql statement and note down the number

SELECT MAX(log_sequence) FROM fnd_log_messages;

Note down this max log sequence number on a notepad...for eg: 1001

3) Reproduce the issue reported.

4) Execute the following sql statement and note down the number

SELECT MAX(log_sequence) FROM fnd_log_messages;

Note down this max log sequence number on a notepad...for eg: 1022

5) Execute the following sql statement

SELECT * FROM fnd_log_messages
WHERE log_sequence between
'<replace this with the max log sequence number noted in step 2>'
and '<replace this with the max log sequence number noted in step 4>';

6) Please save the results of the sql statment executed in Step 5 in an excel spreadsheet.

Oracle EBS密码安全

xuwanxin1987阅读(46)

登录口令大小写 9829
SIGNON_PASSWORD_FAILURE_LIMIT 登录口令失败限制次数 5792
SIGNON_PASSWORD_HARD_TO_GUESS 登录口令应难以猜测 3844
SIGNON_PASSWORD_LENGTH 	登录口令长度 2027
SIGNON_PASSWORD_NO_REUSE 不得重新使用的登录口令 4524

Profile                      	Default	Recommendation
Signon Password Failure Limit	None   	3 (attempts)
Signon Password Hard to Guess	No     	Yes
Signon Password Length       	5      	7 (characters)
Signon Password No Reuse     	None   	180 (days)
Signon Password Custom       	None   	See Note Below
Password Case Option         	Null   	Sensitive

Signon Password Case
This profile option is not available from the beginning.
With 11i.ATG_PF.H RUP3  comes the system profile 'Password Case Option'
After 11i.ATG_PF.H.RUP4  this system profile option was renamed to  'Signon Password Case'.
There are two settings: 'Sensitive' and 'Insensitive'.
The default is 'Insensitive'.
Setting this profile option to 'Sensitive' will make the password case sensitive.
'Mixed' is no longer supported.

select u.user_name,v.* from applsys.fnd_profile_option_values v,fnd_user u
 where v.profile_option_id in (9829,5792,3844,2027,4524) and v.level_id=10004
 and u.user_id = v.level_value;

###update applsys.fnd_profile_option_values v,applsys.fnd_user u
###set v.profile_option_value=''
###where v.profile_option_id in (9829,5792,3844,2027,4524) and v.level_id=10004 and u.user_id = v.level_value;

delete from applsys.fnd_profile_option_values where profile_option_id in (9829,5792,3844,2027,4524) and level_id=10004;

1.Signon Password Failure Limit 登录口令失败限制次数
2.Signon Password Hard to Guess 登录口令应难以猜测 用户的密码中必须至少要有一个字母和一个数字,密码中不能包含用户名,且密码中的字符不能重复。 如某用户的用户名为ABC,则以下密码不被允许: 325763 密码中没有字母 ADFHTR 密码中没有数字 12ABC34 密码中包含用户名 DFGH11 密码中的1重复 
3.Signon Password Length 登录口令长度
4.Signon Password No Reuse 不得重新使用的登录口令 多少天内的密码不能重复使用 一般与 user 界面上的 Define user password expiration一起使用能保证用户的密码定时更换的需要
5.Signon Password Case 登录口令大小写。举例来说,某用户的密码AbsF1234,如果不区分大小写,则可以输入ABSF1234或者absf1234。但是如果区分大小写,则必须输入AbsF1234

Oracle-HTTP-Server Tuning

xuwanxin1987阅读(39)

  ServerLimit 128
  StartServers 32
  MinSpareThreads 1024
  MaxSpareThreads 2048
  ThreadLimit 72
  ThreadsPerChild 32
  MaxClients 4096
  MaxRequestsPerChild 10000
  AcceptMutex fcntl
  LockFile "${ORACLE_INSTANCE}/diagnostics/logs/${COMPONENT_TYPE}/${COMPONENT_NAME}/http_lock"
Oracle E-Business Suite Performance Best Practices (Doc ID 2528000.1)
https://www.oracle.com/webfolder/technetwork/tutorials/obe/cloud/compute-iaas/sharing_app_tier_file_system_ebs_fss/24fss.html

手工安装Oracle的JVM包

xuwanxin1987阅读(59)

检查是否创建了 dbms_java
SQL> select distinct owner,name from dba_source where lower(NAME)='dbms_java';
owner             name
---------------- ---------------
sys               dbms_java

某java开发人员反映 aaa用户没有javasyspriv和JAVA_DEPLOY权限,但在查询后没有这两个权限,原因是没有安装jvm包。
解决方案:安装以下两个jvm包后,就有了javasyspriv和JAVA_DEPLOY的角色。
SQL> @?/javavm/install/initjvm.sql
SQL> @?/rdbms/admin/catjava.sql

赋角色给给aaa用户
grant javasyspriv to aaa;
grant JAVA_DEPLOY to aaa;

附1:在安装initjvm.sql包时,由于中间断开了,再次安装时报错如下:
begin if initjvmaux.startstep('CREATE_JAVA_SYSTEM') then
*
ERROR at line 1:
ORA-29510: name, SYS.sun/util/resources/TimeZoneNamesBundle, already used by an
existing object
ORA-06512: at "SYS.INITJVMAUX", line 23
ORA-06512: at line 5

解决方案:
SQL> @?/javavm/install/rmjvm.sql
SQL> @?/javavm/install/initjvm.sql

UDEV SCSI Rules Configuration In Oracle Linux 5, 6 , 7 and 8

xuwanxin1987阅读(33)

原文链接https://oracle-base.com/articles/linux/udev-scsi-rules-configuration-in-oracle-linux

For Oracle Automatic Storage Manager (ASM) to use disks, it needs to be able to identify the devices consistently and for them to have the correct ownership and permissions. In Linux you can use ASMLib to manage these tasks, but it is seen as an additional layer of complexity and has never really gained any popularity. Instead, many people use the Linux device manager “udev” to perform these tasks. This article presents a brief overview of setting up udev rules with respect to disks for use with ASM in Oracle 11g. The examples are all done using Oracle Linux 5, 6, 7 and 8 so they will be consistent with RHEL and CentOS 5, 6, 7 and 8.

Background

Essentially, what udev does is apply rules defined in files in the “/etc/udev/rules.d” directory to the device nodes listed in the “/dev” directory. The rules can be defined in a variety of ways, but what we need to do is identify the device and say what we want udev to do with it.

In this case I know all my disk devices are named “/dev/sd?1”, where the “?” represents a letter from a-d, so I can identify the devices of interest using the following rule parameters.

KERNEL=="sd?1", BUS=="scsi"

I want to tie each specific device to an alias, so it is always identified the same way, regardless of the device name Linux assigns it. So I need to be able to test each device that matches the previous pattern to see if it is the disk I am interested in. Each disk has a unique SCSI ID, so I can place a test into the rule, telling it how to perform the test, and the result it should return for a positive match. The following rule parameters explain how to test the device and what result constitutes a match in Oracle Linux 5.

PROGRAM=="/sbin/scsi_id -g -u -s /block/$parent", RESULT=="SATA_VBOX_HARDDISK_VBd306dbe0-df3367e3_"

The scsi_id command works a little differently in Oracle Linux 6, so for that the following test works better.

PROGRAM=="/sbin/scsi_id -g -u -d /dev/$parent", RESULT=="SATA_VBOX_HARDDISK_VBd306dbe0-df3367e3_"

The scsi_id command is located in a different place in Oracle Linux 7 and 8, so for that the following test is correct.

PROGRAM=="/usr/lib/udev/scsi_id -g -u -d /dev/$parent", RESULT=="SATA_VBOX_HARDDISK_VBd306dbe0-df3367e3_"

Once we have identified the specific device of interest, we need to indicate what actions should be performed on it. The following parameters specify an alias, the ownership and the permissions for the device.

NAME="asm-disk1", OWNER="oracle", GROUP="dba", MODE="0660"

So the whole rule for each disk will look something like this in Oracle Linux 5.

KERNEL=="sd?1", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -s /block/$parent", RESULT=="SATA_VBOX_HARDDISK_VBd306dbe0-df3367e3_", NAME="asm-disk1", OWNER="oracle", GROUP="dba", MODE="0660"

Or this in Oracle Linux 6.

KERNEL=="sd?1", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -d /dev/$parent", RESULT=="SATA_VBOX_HARDDISK_VBd306dbe0-df3367e3_", NAME="asm-disk1", OWNER="oracle", GROUP="dba", MODE="0660"

Or this in Oracle Linux 7 and 8.

KERNEL=="sd?1", SUBSYSTEM=="block", PROGRAM=="/usr/lib/udev/scsi_id -g -u -d /dev/$parent", RESULT=="SATA_VBOX_HARDDISK_VBd306dbe0-df3367e3_", SYMLINK+="asm-disk1", OWNER="oracle", GROUP="dba", MODE="0660"

This means that the device pointing to the partition “sd*1” on the disk with the SCSI ID of “SATA_VBOX_HARDDISK_VBd306dbe0-df3367e3_” will always be called “/dev/asm-disk1”, regardless of the letter “?” Linux assigns when the device is discovered. In addition, the device will have the correct ownership and permissions for ASM.

There are a number of wildcards and matching patterns that can be used if you don’t want to write device-specific rules.

Now we know roughly what we are trying to achieve, we will look at each step necessary for setting up the disks for ASM to use.

Identify the Disks (/sbin/scsi_id)

We are going to write device-specific rules, so we need to be able to identify each device consistently, irrespective of the order in which Linux discovers it. To do this we are going to use the SCSI ID for each disk (not the partition), which we get using the scsi_id command. The “-s” option makes the paths relative to the “/sys” directory. For Oracle Linux 5, use the following command.

# /sbin/scsi_id -g -u -s /block/sdb
SATA_VBOX_HARDDISK_VBd306dbe0-df3367e3_
# /sbin/scsi_id -g -u -s /block/sdc
SATA_VBOX_HARDDISK_VB46dec7e0-192e8000_
# /sbin/scsi_id -g -u -s /block/sdd
SATA_VBOX_HARDDISK_VBce8c63bb-ac67a172_
# /sbin/scsi_id -g -u -s /block/sde
SATA_VBOX_HARDDISK_VB7437a3b7-95b199cd_
# 

The “-s” is not available in Oracle Linux 6, so you must use the following syntax.

# /sbin/scsi_id -g -u -d /dev/sdb
SATA_VBOX_HARDDISK_VBd306dbe0-df3367e3_
# /sbin/scsi_id -g -u -d /dev/sdc
SATA_VBOX_HARDDISK_VB46dec7e0-192e8000_
# /sbin/scsi_id -g -u -d /dev/sdd
SATA_VBOX_HARDDISK_VBce8c63bb-ac67a172_
# /sbin/scsi_id -g -u -d /dev/sde
SATA_VBOX_HARDDISK_VB7437a3b7-95b199cd_
# 

The location of the scsi_id command has changed in Oracle Linux 7 and 8, so you must use the following syntax.

# /usr/lib/udev/scsi_id -g -u -d /dev/sdb
SATA_VBOX_HARDDISK_VBd306dbe0-df3367e3_
# /usr/lib/udev/scsi_id -g -u -d /dev/sdc
SATA_VBOX_HARDDISK_VB46dec7e0-192e8000_
# /usr/lib/udev/scsi_id -g -u -d /dev/sdd
SATA_VBOX_HARDDISK_VBce8c63bb-ac67a172_
# /usr/lib/udev/scsi_id -g -u -d /dev/sde
SATA_VBOX_HARDDISK_VB7437a3b7-95b199cd_
# 

Make SCSI Devices Trusted

Add the following to the “/etc/scsi_id.config” file to configure SCSI devices as trusted. Create the file if it doesn’t already exist.

options=-g

Create UDEV Rules File

Create the “/etc/udev/rules.d/99-oracle-asmdevices.rules” file.

# vi /etc/udev/rules.d/99-oracle-asmdevices.rules

The file should contain the following lines for Oracle Linux 5. The PROGRAM parameter must match the command you used to retrieve the SCSI ID, and the RESULT parameter must match the value returned from your disks.

KERNEL=="sd?1", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -s /block/$parent", RESULT=="SATA_VBOX_HARDDISK_VBd306dbe0-df3367e3_", NAME="asm-disk1", OWNER="oracle", GROUP="dba", MODE="0660"
KERNEL=="sd?1", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -s /block/$parent", RESULT=="SATA_VBOX_HARDDISK_VB46dec7e0-192e8000_", NAME="asm-disk2", OWNER="oracle", GROUP="dba", MODE="0660"
KERNEL=="sd?1", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -s /block/$parent", RESULT=="SATA_VBOX_HARDDISK_VBce8c63bb-ac67a172_", NAME="asm-disk3", OWNER="oracle", GROUP="dba", MODE="0660"
KERNEL=="sd?1", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -s /block/$parent", RESULT=="SATA_VBOX_HARDDISK_VB7437a3b7-95b199cd_", NAME="asm-disk4", OWNER="oracle", GROUP="dba", MODE="0660"

The equivalent for Oracle Linux 6 is shown below.

KERNEL=="sd?1", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -d /dev/$parent", RESULT=="SATA_VBOX_HARDDISK_VBd306dbe0-df3367e3_", NAME="asm-disk1", OWNER="oracle", GROUP="dba", MODE="0660"
KERNEL=="sd?1", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -d /dev/$parent", RESULT=="SATA_VBOX_HARDDISK_VB46dec7e0-192e8000_", NAME="asm-disk2", OWNER="oracle", GROUP="dba", MODE="0660"
KERNEL=="sd?1", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -d /dev/$parent", RESULT=="SATA_VBOX_HARDDISK_VBce8c63bb-ac67a172_", NAME="asm-disk3", OWNER="oracle", GROUP="dba", MODE="0660"
KERNEL=="sd?1", BUS=="scsi", PROGRAM=="/sbin/scsi_id -g -u -d /dev/$parent", RESULT=="SATA_VBOX_HARDDISK_VB7437a3b7-95b199cd_", NAME="asm-disk4", OWNER="oracle", GROUP="dba", MODE="0660"

The equivalent for Oracle Linux 7 and 8 is shown below.

KERNEL=="sd?1", SUBSYSTEM=="block", PROGRAM=="/usr/lib/udev/scsi_id -g -u -d /dev/$parent", RESULT=="SATA_VBOX_HARDDISK_VBd306dbe0-df3367e3_", SYMLINK+="asm-disk1", OWNER="oracle", GROUP="dba", MODE="0660"
KERNEL=="sd?1", SUBSYSTEM=="block", PROGRAM=="/usr/lib/udev/scsi_id -g -u -d /dev/$parent", RESULT=="SATA_VBOX_HARDDISK_VB46dec7e0-192e8000_", SYMLINK+="asm-disk2", OWNER="oracle", GROUP="dba", MODE="0660"
KERNEL=="sd?1", SUBSYSTEM=="block", PROGRAM=="/usr/lib/udev/scsi_id -g -u -d /dev/$parent", RESULT=="SATA_VBOX_HARDDISK_VBce8c63bb-ac67a172", SYMLINK+="asm-disk3", OWNER="oracle", GROUP="dba", MODE="0660"
KERNEL=="sd?1", SUBSYSTEM=="block", PROGRAM=="/usr/lib/udev/scsi_id -g -u -d /dev/$parent", RESULT=="SATA_VBOX_HARDDISK_VB7437a3b7-95b199cd_", SYMLINK+="asm-disk4", OWNER="oracle", GROUP="dba", MODE="0660"

Load Updated Block Device Partitions (/sbin/partprobe)

Load updated block device partition tables.

# #OL5, OL6 and OL77
# /sbin/partprobe /dev/sdb1
# /sbin/partprobe /dev/sdc1
# /sbin/partprobe /dev/sdd1
# /sbin/partprobe /dev/sde1

# #OL8
/sbin/partx -u /dev/sbc1
/sbin/partx -u /dev/sdc1
/sbin/partx -u /dev/sdd1
/sbin/partx -u /dev/sde1

Test Rules (udevtest)

Test the rules are working as expected.

# #OL5
# udevtest /block/sdb/sdb1
# udevtest /block/sdc/sdc1
# udevtest /block/sdd/sdd1
# udevtest /block/sde/sde1

# #OL6 and OL7
# udevadm test /block/sdb/sdb1
# udevadm test /block/sdc/sdc1
# udevadm test /block/sdd/sdd1
# udevadm test /block/sde/sde1

The output from the first disk should look something like this.

# udevtest /block/sdb/sdb1
main: looking at device '/block/sdb/sdb1' from subsystem 'block'
udev_rules_get_name: add symlink 'disk/by-id/scsi-SATA_VBOX_HARDDISK_VBd306dbe0-df3367e3-part1'
udev_rules_get_name: add symlink 'disk/by-path/pci-0000:00:0d.0-scsi-1:0:0:0-part1'
run_program: '/lib/udev/vol_id --export /dev/.tmp-8-17'
run_program: '/lib/udev/vol_id' returned with status 4
run_program: '/sbin/scsi_id -g -u -s /block/sdb/sdb1'
run_program: '/sbin/scsi_id' (stdout) 'SATA_VBOX_HARDDISK_VBd306dbe0-df3367e3_'
run_program: '/sbin/scsi_id' returned with status 0
udev_rules_get_name: rule applied, 'sdb1' becomes 'asm-disk1'
udev_device_event: device '/block/sdb/sdb1' already in database, validate currently present symlinks
udev_node_add: creating device node '/dev/asm-disk1', major = '8', minor = '17', mode = '0660', uid = '1100', gid = '1200'
udev_node_add: creating symlink '/dev/disk/by-id/scsi-SATA_VBOX_HARDDISK_VBd306dbe0-df3367e3-part1' to '../../asm-disk1'
udev_node_add: creating symlink '/dev/disk/by-path/pci-0000:00:0d.0-scsi-1:0:0:0-part1' to '../../asm-disk1'
main: run: 'socket:/org/kernel/dm/multipath_event'
main: run: 'socket:/org/kernel/udev/monitor'
main: run: '/lib/udev/udev_run_devd'
main: run: 'socket:/org/freedesktop/hal/udev_event'
main: run: '/sbin/pam_console_apply /dev/asm-disk1 /dev/disk/by-id/scsi-SATA_VBOX_HARDDISK_VBd306dbe0-df3367e3-part1 /dev/disk/by-path/pci-0000:00:0d.0-scsi-1:0:0:0-part1'
#

Restart UDEV Service

Restart the UDEV service.

# #OL5
# /sbin/udevcontrol reload_rules

# #OL6, OL7 and OL8
# /sbinudevadm control --reload-rules

# #OL5 and OL6 : Not needed for OL7 and OL8
# /sbin/start_udev

Check Ownership and Permissions

Check the disks are now available with the “asm-disk*” alias and the correct ownership and permissions.

# cd /dev
# ls -al asm-disk*
brw-rw---- 1 oracle dba 8, 17 Apr  8 22:47 asm-disk1
brw-rw---- 1 oracle dba 8, 33 Apr  8 22:47 asm-disk2
brw-rw---- 1 oracle dba 8, 49 Apr  8 22:47 asm-disk3
brw-rw---- 1 oracle dba 8, 65 Apr  8 22:47 asm-disk4
#

So the ASM_DISKSTRING initialization parameter in the ASM instance can be set to ‘/dev/asm-disk*’ to identify the ASM disks.

For more information see:

Hope this helps. Regards Tim…

(转)Data guard Interview Question and Answers

xuwanxin1987阅读(39)

By  Nagulu Polagani

Data guard Interview Question and Answers:

Data guard Interview Question and Answers

1. Can Oracle’s Data Guard be used on Standard Edition, and if so how? How can you test that the standby database is in sync?

In Oracle Standard Edition, it is possible to be a standby database, and update it *manually*. Roughly, put your production database in archivelog mode. Create a hotbackup of the database and move it to the standby machine. Then create a standby controlfile on the production machine, and ship that file, along with all the archived redolog files to the standby server. Once you have all these files assembled, place them in their proper locations, recover the standby database, and you’re ready to roll. From this point on, you must manually ship, and manually apply those archived redologs to stay in sync with production.

To test your standby database, make a change to a table on the production server, and commit the change. Then manually switch a logfile so those changes are archived. Manually ship the newest archived redolog file, and manually apply it on the standby database. Then open your standby database in read-only mode, and select from your changed table to verify those changes are available. Once you’re done, shutdown your standby and startup again in standby mode.

2. What is the difference between Active Dataguard, and the Logical Standby implementation of 10g dataguard?

Active dataguard is almost like a physical standby. We can use physical standby for testing without compromising on data. We can open the physical standby on read/write mode, so that we can do some destructive things in it (drop tables, change data, whatever – run a test – perhaps with real application testing). While we are using it for reporting, redo will still stream from production.

3. What is a Dataguard?

Oracle Dataguard is a disaster recovery solution from Oracle Corporation that has been utilized in the industry extensively at times of Primary site failure, failover, switchover scenarios.

4. What are the uses of Oracle Data Guard?

a) Oracle Data Guard ensures high availability, data protection, and disaster recovery for enterprise data.

b) Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions.

c) With Data Guard, administrators can optionally improve production database performance by offloading resource-intensive backup and reporting operations to standby systems.

5. What is Redo Transport Services?

It control the automated transfer of redo data from the production database to one or more archival destinations. Redo transport services perform the following tasks:

a) Transmit redo data from the primary system to the standby systems in the configuration. b) Manage the process of resolving any gaps in the archived redo log files due to a network failure. c) Automatically detect missing or corrupted archived redo log files on a standby system and automatically retrieve replacement archived redo log files from the primary database or another standby database. 6. What is apply services?

Apply redo data on the standby database to maintain transactional synchronization with the primary database. Redo data can be applied either from archived redo log files, or, if real-time apply is enabled, directly from the standby redo log files as they are being filled, without requiring the redo data to be archived first at the standby database. It also allows read-only access to the data.

7. What is difference between physical and standby databases?

The main difference between physical and logical standby databases is the manner in which apply services apply the archived redo data:

a) For physical standby databases, Data Guard uses Redo Apply technology, which applies redo data on the standby database using standard recovery techniques of an Oracle database.

b) For logical standby databases, Data Guard uses SQL Apply technology, which first transforms the received redo data into SQL statements and then executes the generated SQL statements on the logical standby database. 8. What is Data Guard Broker?

Data guard Broker manage primary and standby databases using the SQL command-line interfaces or the Data Guard broker interfaces, including a command-line interface (DGMGRL) and a graphical user interface that is integrated in Oracle Enterprise Manager. It can be used to perform:

a) Create and enable Data Guard configurations, including setting up redo transport services and apply services b) Manage an entire Data Guard configuration from any system in the configuration c) Manage and monitor Data Guard configurations that contain Oracle RAC primary or standby databases d) Simplify switchovers and failovers by allowing you to invoke them using either a single key click in Oracle Enterprise Manager or a single command in the DGMGRL command-line interface. e) Enable fast-start failover to fail over automatically when the primary database becomes unavailable. When fast-start failover is enabled, the Data Guard broker determines if a failover is necessary and initiates the failover to the specified target standby database automatically, with no need for DBA intervention.

9. What are the Data guard Protection modes and summarize each?

Maximum availability :

This protection mode provides the highest level of data protection that is possible without compromising the availability of a primary database. Transactions do not commit until all redo data needed to recover those transactions has been written to the online redo log and to at least one standby database.

Maximum performance :

This is the default protection mode. It provides the highest level of data protection that is possible without affecting the performance of a primary database. This is accomplished by allowing transactions to commit as soon as all redo data generated by those transactions has been written to the online log.

Maximum protection :

This protection mode ensures that no data loss will occur if the primary database fails. To provide this level of protection, the redo data needed to recover a transaction must be written to both the online redo log and to at least one standby database before the transaction commits. To ensure that data loss cannot occur, the primary database will shut down, rather than continue processing transactions.

10. If you didn’t have access to the standby database and you wanted to find out what error has occurred in a data guard configuration, what view would you check in the primary database to check the error message?

You can check the v$dataguard_status view. Select message from v$dataguard_status;

11. In Oracle 11g, what command in RMAN can you use to create the standby database while the target database is active?

Oracle 11g has made it extremely simple to set up a standby database environment because Recovery Manager (RMAN) now supports the ability to clone the existing primary database directly to the intended standby database site over the network via the DUPLICATE DATABASE command set while the target database is active. RMAN automatically generates a conversion script in memory on the primary site and uses that script to manage the cloning operation on the standby site with virtually no DBA intervention required. You can execute this in a run block in RMAN:

duplicate target database for standby dorecover from active database;

12. What additional standby database mode does Oracle 11g offer?

Oracle 11g has introduced the Oracle Snapshot Standby Database. In Snapshot Standby Database a physical standby database can easily open in read-write mode and again you can convert it back to the physical standby database. This is suitable for test and development environments and also maintains protection by continuing to receive data from the production database and archiving it for later use.

13. In Oracle 11g how can speed up backups on the standby database?

In Oracle 11g, block change tracking is now supported in the standby database.

14. With the availability of Active Data Guard, what role does SQL Apply (logical standby) continue to play?

Use SQL Apply for the following requirements: (a) when you require read-write access to a synchronized standby database but do not modify primary data, (b) when you wish to add local tables to the standby database that can also be updated, or (c) when you wish to create additional indexes to optimize read performance. The ability to handle local writes makes SQL Apply better suited to packaged reporting applications that often require write access to local tables that exist only at the target database. SQL Apply also provides rolling upgrade capability for patchsets and major database releases. This rolling upgrade functionality can also be used by physical standby databases beginning with Oracle 11g using Transient Logical Standby.

15. Why would I use Active Data Guard and not simply use SQL Apply (logical standby) that is included with Data Guard 11g?

If read-only access satisfies the requirement – Active Data Guard is a closer fit for the requirement, and therefore is much easier to implement than any other approach. Active Data Guard supports all datatypes and is very simple to implement. An Active Data Guard replica can also easily support additional uses – offloading backups from the primary database, serve as an open read-write test system during off-peak hours (Snapshot Standby), and provide an exact copy of the production database for disaster recovery – fully utilizing standby servers, storage and software while in standby role.

16. Why do I need the Oracle 11g Active Data Guard Option?

Previous capabilities did not allow Redo Apply to be active while a physical standby database was open read-only, and did not enable RMAN block change tracking on the standby database. This resulted in (a) read-only access to data that was frozen as of the time that the standby database was opened read-only, (b) failover and switchover operations that could take longer to complete due to the backlog of redo data that would need to be applied, and (c) incremental backups that could take up to 20x longer to complete – even on a database with a moderate rate of change. Previous capabilities are still included with Oracle Data Guard 11g, no additional license is required to use previous capabilities.

17. If you wanted to upgrade your current 10g physical standby data guard configuration to 11g, can you upgrade the standby to 11g first then upgrade the primary ?

Yes, in Oracle 11g, you can temporarily convert the physical standby database to a logical standby database to perform a rolling upgrade. When you issue the convert command you need to keep the identity:

alter database recover logical standby keep identity;

18. If you have a low-bandwidth WAN network, what can you do to improve the Oracle 11g data guard configuration in a GAP detected situation?

Oracle 11g introduces the capability to compress redo log data as it transports over the network to the standby database. It can be enabled using the compression parameter. Compression becomes enabled only when a gap exists and the standby database needs to catch up to the primary database.

alter system set log_archive_dest_1=’SERVICE=DBA11GDR COMPRESSION=ENABLE’;

19. In an Oracle 11g Logical Standby Data Guard configuration, how can you tell the dbms_scheduler to only run jobs in primary database?

Oracle 11g, logical standby now provides support for DBMS_SCHEDULER. It is capable of running jobs in both primary and logical standby database. You can use the DBMS_SCHEDULER.SET_ATTRIBUTE procedure to set the database_role. You can specify that the jobs can run only when operating in that particular database role.

20. How can you control when an archive log can be deleted in the standby database in oracle 11g ?

In Oracle 11g, you can control it by using the log_auto_delete initialization parameter. The log_auto_delete parameter must be coupled with the log_auto_del_retention_target parameter to specify the number of minutes an archivelog is maintained until it is purged. Default is 24 hours. For archivelog retention to be effective, the log_auto_delete parameter must be set to true.

21. Can Oracle Data Guard be used with Standard Edition of Oracle ? Yes and No. The automated features of Data Guard are not available in the standard edition of Oracle. You can still however, perform log shipping manually and write scripts to manually perform the steps. If you are on unix platform, you can write shell scripts that identify the logs and then use the scp or sftp command to ship it to the standby server. Then on the standby server, identify which logs have not been applied and apply/recover them maually and remove them once applied.

(转)ORACLE DBA – UNIX Interview Questions

xuwanxin1987阅读(53)

By  Nagulu Polagani

ORACLE DBA – UNIX Interview Questions

 1.  How can you determine the space left in a file system?

Expected answer: There are several commands to do this: du, df, or bdf

2. How can you determine the number of SQLNET users logged in to the UNIX system?

Expected answer: SQLNET users will show up with a process unique name that begins with oracle, if you… do a ps -ef|grep oracle|wc -l you can get a count of the number of users.

3. What command is used to type files to the screen?

Expected answer: cat, more, pg

4. What command is used to remove a file?

Expected answer: rm

5. Can you remove an open file under UNIX?

Expected answer: yes

6. How do you create a decision tree in a shell script?

Expected answer: depending on shell, usually a case-esac or an if-endif or fi structure

7. What is the purpose of the grep command?

Expected answer: grep is a string search command that parses the specified string from the specified file or files

8. The system has a program that always includes the word nocomp in its name, how can you determine the number of processes that are using this program?

Expected answer: ps -ef|grep *nocomp*|wc -l

9. What is an inode?

Expected answer: an inode is a file status indicator. It is stored in both disk and memory and tracts file status. There is one inode for each file on the system.

 10. The system administrator tells you that the system hasn?t been rebooted in 6 months, should he be proud of this?

Expected answer: Maybe. Some UNIX systems don?t clean up well after themselves. Inode problems and dead user processes can accumulate causing possible performance and corruption problems. Most UNIX systems should have a scheduled periodic reboot so file systems can be checked and cleaned and dead or zombie processes cleared out.

11. What is redirection and how is it used?

Expected answer: redirection is the process by which input or output to or from a process is redirected to another process. This can be done using the pipe symbol “|”, the greater than symbol “>” or the “tee” command. This is one of the strengths of UNIX allowing the output from one command to be redirected directly into the input of another command.

 12. How can you find dead processes?

Expected answer: ps -ef|grep zombie — or — who -d depending on the system.

13. How can you find all the processes on your system?

Expected answer: Use the ps command

14. How can you find your id on a system?

Expected answer: Use the “who am i” command.

15. What is the finger command?

Expected answer: The finger command uses data in the passwd file to give information on system users.

16. What is the easiest method to create a file on UNIX?

Expected answer: Use the touch command

17. What does >> do?

Expected answer: The “>>” redirection symbol appends the output from the command specified into the file specified. The file must already have been created.

18. If you aren?t sure what command does a particular UNIX function what is the best way to determine the command?

Expected answer: The UNIX man -k command will search the man pages for the value specified. Review the results from the command to find the command of interest.

19.  How to know whether server is 32 or 64 bit?

Ans:  getconf KERNEL_BITS

(转)Oracle Apps DBA Interview Questions – VI

xuwanxin1987阅读(31)

By  Nagulu Polagani

Oracle Apps DBA Interview Questions – VI

Q: Why we need to put maintenance mode when we are applying a patch ?

A: Maintenance mode provides a clear separation between normal runtime operation of Oracle Applications and system downtime for

maintenance. Enabling the maintenance mode feature shuts down the Workflow Business Events System and sets up function security so

that no Oracle Applications functions are available to users. Used only during AutoPatch sessions, maintenance mode ensures

optimal performance and reduces downtime when applying a patch. For more information, refer to Preparing your System for Patching

in Oracle Applications Maintenance Utilities.

Q: After applying patch why we need to take snapshot and what is the use of it ??

A: Snapshot is a view of the system at specific time. In apps, Patch Wizard uses Global snapshot to determine which patches have

been applied to the system and Autopatch uses APPL_TOP snapshot to determine what patches have been applied to that APPL_TOP.

Snapshot actually records the list of files,file versions and bug fixes. Both snapshot are views, they are created once during

installation and then updated during patching. APPL_TOP snapshot information is stored in the AD_SNAPSHOTS, AD_SNAPSHOT_FILES, and

AD_SNAPSHOT_BUGFIXES tables

Q: Whether we can apply 2 patches at a time without merging them, why ??

A: We can’t apply 2 patches at a time because when patch started it will create 2 tables in db (AD_DEFERRED_JOBS AND

FND_INSTALL_PROCESSES). If you apply patches at a time both will try to create those 2 tables in db, so both will fail.

Q. After applying patch whether we can revert it back ?

A: No, as it is going to update some tables in db.

Q. Whether we need to run adpreclone everytime when we clone, why ?

A: We have to run adpreclone if there are changes in any customizations or any other tablespaces like if applied any AD patches,

Big Patches, Minipacks, ATG patches, Tech Stack, and AD Patches. If there are no changes, then i don’t think there is any need to

preclone it again.

Example: When you run adpreclone.pl, it essentially does an “alter database backup controlfile to trace” and stores the information from

that in a file within ${ORACLE_HOME}/appsutil. This represents all of the datafiles in the database at that particular time. The information

gathered at this time represents the state of the source at this time. If you does not run adpreclone.pl again when u do clone next time,

Your backup (your target) represents an earlier time (prior to the addition of that datafile), so the “create controlfile” step

that adcfgclone is doing is going to fail.

Q: Which files it will call when we run adcmctl.sh start apps/apps ?

A: It will call FNDLIBR executable, which will be located in $FND_TOP/bin.

Q: What are the main tables related to concurrent manager ?

A: FND_NODES

FND_CONCURRENT_PROCESSES (fcproc)

FND_CONCURRENT_REQUESTS (fcr)

FND_CONCURRENT_QUEUES (fcq)

FND_CONCURRENT_PROGRAMS (fcprog)

FND_EXECUTABLES

FND_CP_SERVICES

FND_CONCURRENT_QUEUE_SIZE

FND_CONCURRENT_QUEUE_CONTENT

FND_CONCURRENT_PROGRAM_SERIAL

FND_CONCURRENT_TIME_PERIODS

FND_CONCURRENT_PROCESSORS

Q: What is FNDFS ??

A: Report Review Agent(RRA) also referred by executable FNDFS is default text viewer in Oracle Applications 11i for viewing

output files and log files. As most of Applications DBA’s are not clear about Report Server and RRA.

Q: What is FNDSM ?? It has seperate name, what it is ?

A: FNDSM is executable & core component in GSM. GSM stands for Generic Service Management Framework. Oracle E-Business Suite

consist of various compoennts like Forms, Reports, Web Server, Workflow, Concurrent Manager .. Earlier each service used to start

at their own but managing these services (given that) they can be on various machines distributed across network. So Generic

Service Management is extension of Concurrent Processing which manages all your services , provide fault tolerance (If some

service is down ICM through FNDSM & other processes will try to start it even on remote server) With GSM all services are

centrally managed via this Framework.

Q: How to check whether ICM is up and running from backend ?

A: ps -ef | grep LIB

ps –ef | grep CPMGR

ps –ef | grep FNDCPMBR

$FND_TOP/sql/afcmstat.sql — Displays all the defined managers, their maximum capacity, pids, and their status.

afimchk.sql — Displays the status of ICM and PMON method in effect, the ICM’s log file, and determines if the concurrent manger

monitor is running.

afcmcreq.sql — Displays the concurrent manager and the name of its log file that processed a request.

afrqwait.sql — Displays the requests that are pending, held, and scheduled.

afrqstat.sql — Displays of summary of concurrent request execution time and status since a particular date.

afqpmrid.sql — Displays the operating system process id of the FNDLIBR process based on a concurrent request id. The process id

can then be used with the ORADEBUG utility.

afimlock.sql — Displays the process id, terminal, and process id that may be causing locks that the ICM and CRM are waiting to

get. You should run this script if there are long delays when submitting jobs, or if you suspect the ICM is in a gridlock with

another oracle process.

Q: Which component is responsible for PCP failover ?

A: When PCP is enabled, can you check that FNDSM is running on all your CCM nodes? That process is mainly reponsible to getting

the failovers to happen and also start up CCMs

Q: Where custom top information will be there ?

A: $APPL_TOP/APPLSYS.env file.

Q: What will happen when we run adpreclone ?

A: It will collect all info of the environment and store in seperate template files.

Q: Which two parameters are required when we do clone using RMAN ?

A: DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT

Q: How to clean FND_NODES table?

A: EXEC FND_CONC_CLONE.SETUP_CLEAN.

联系我们