Oracle启用归档

1、单实例 Oracle 9i ALTER SYSTEM SET log_archive_start=TRUE SCOPE=spfile; ALTER SYSTEM SET log_archive_dest_1='location=/u01/oradata/MYSID/archive/' SCOPE=spfile; ALTER SYSTEM SET log_archive_format='arch_%t_%s.arc' SCOPE=spfile; SHUTDOWN IMMEDIATE; STARTUP MOUNT; ARCHIVE LOG START; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN; Oracle 10g ALTER SYSTEM SET log_archive_dest_1='location=/u01/oradata/MYSID/archive/' SCOPE=spfile; ALTER SYSTEM SET log_archive_format='arch_%t_%s_%r.arc' SCOPE=spfile; SHUTDOWN IMMEDIATE; STARTUP MOUNT; ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN; 2、Oracle RAC Oracle 9i ALTER SYSTEM SET log_archive_start=TRUE SCOPE=spfile; ALTER SYSTEM SET log_archive_dest_1='location=/u01/oradata/MYDB/archive/' SCOPE=spfile; ALTER SYSTEM SET log_archive_format='arch_%t_%s.arc' SCOPE=spfile; # 禁用集群模式 ALTER SYSTEM SET cluster_database=FALSE SCOPE=spfile; # 停止数据库 srvctl stop database -d MYDB # 配置归档 STARTUP MOUNT; ARCHIVE LOG START; ALTER DATABASE ARCHIVELOG; ALTER SYSTEM SET cluster_database=TRUE SCOPE=spfile; SHUTDOWN IMMEDIATE; # 启动数据库 srvctl start database -d MYDB Oracle 10gR1 ...

2015-02-11 · 1 min · 209 words · kevin

命令创建ACFS

创建挂载点 mkdir -p /u01/app/oracle/acfsmounts/data_acfsvol3 chown oracle:oinstall /u01/app/oracle/acfsmounts/data_acfsvol3 连接到ASM创建Volume su - oracle [oracle@rac1 ~]$ . oraenv ORACLE_SID = [RAC1] ? +ASM1 The Oracle base for ORACLE_HOME=/u01/app/11.2.0/grid is /u01/app/oracle [oracle@rac1 ~]$ dbhome /u01/app/11.2.0/grid [oracle@rac1 ~]$ sqlplus / as sysasm Issue to the following command to create a new volume. SQL> ALTER DISKGROUP DATA ADD VOLUME ACFSVOL3 SIZE 10G; Diskgroup altered. SQL> Exit the SQL*Plus session, then create a file system on the volume. 格式化Volume ...

2015-01-11 · 1 min · 141 words · kevin

在11gR2中使用RMAN复制数据库

RMAN 可以通过复制或者克隆,从一个运行的库中复制一个备份。可以用来在远程站点上复制数据库,下面示例中源库和复制库使用同样的SID:DB11G 在源库中创建备份。 $ rman target=/ RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON; RMAN> BACKUP DATABASE PLUS ARCHIVELOG 为目标库创建password文件 $ orapwd file=/u01/app/oracle/product/11.2.0/db_1/dbs/orapwDB11G password=password entries=10 在"$ORACLE_HOME/network/admin" 中增加"tnsnames.ora" 允许目标库访问源库。 # Added to the tnsnames.ora DB11G-SOURCE = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = ol5-112-dup1)(PORT = 1521)) ) (CONNECT_DATA = (ORACLE_SID = DB11G) ) ) 创建pfile文件 # Minimum Requirement. DB_NAME=DB11G # 修改目录位置. #DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/DB11G/','/u01/app/oracle/oradata/NEWSID/' #LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/DB11G/','/u02/app/oracle/oradata/NEWSID/' 创建数据库启动所需的目录 $ mkdir -p /u01/app/oracle/oradata/DB11G $ mkdir -p /u01/app/oracle/fast_recovery_area/DB11G $ mkdir -p /u01/app/oracle/admin/DB11G/adump 拷贝归档和备份文件到目标库 ...

2014-12-11 · 2 min · 332 words · kevin

关于VIP的奇怪故障一例

OS:HP-UX 11.31 DB:Oracle 10.0.2.5 RAC (2nodes) 问题描述:节点二在服务启动后,经过1分钟至3分钟会发生VIP导致的服务切换,切换后节点二VIP在节点一上启动,检查日志主要报错为: Invalid parameters, or failed to bring up VIP (host=essrzc2) 经过检查,系统网关配置正常,响应速度正常,系统日志无异常。开启服务debug后收获如下日志: 2013-12-16 14:57:08.192: [ RACG][1] [21721][1][ora.essrzc2.vip]: Mon Dec 16 14:57:03 EAT 2013 [ 21730 ] Checking interface existance Mon Dec 16 14:57:03 EAT 2013 [ 21730 ] Calling getifbyip Mon Dec 16 14:57:03 EAT 2013 [ 21730 ] getifbyip: started for 132.42.37.144 2013-12-16 14:57:08.192: [ RACG][1] [21721][1][ora.essrzc2.vip]: Mon Dec 16 14:57:03 EAT 2013 [ 21730 ] Completed getifbyip lan900:801 Mon Dec 16 14:57:03 EAT 2013 [ 21730 ] Completed with initial interface test Mon Dec 16 14:57:03 EAT 2013 [ 21730 ] Broadcast = 132.42.37.255 2013-12-16 14:57:08.192: [ RACG][1] [21721][1][ora.essrzc2.vip]: Mon Dec 16 14:57:03 EAT 2013 [ 21730 ] checkIf: start for if=lan900 Mon Dec 16 14:57:07 EAT 2013 [ 21730 ] checkIf: RX packets checked if=lan900 ok Mon Dec 16 14:57:07 EAT 2013 [ 21730 ] checkIf: end for if=lan900 2013-12-16 14:57:08.192: [ RACG][1] [21721][1][ora.essrzc2.vip]: Mon Dec 16 14:57:07 EAT 2013 [ 21730 ] main: interface is okay, exit success 2013-12-16 14:57:08.192: [ RACG][1] [21721][1][ora.essrzc2.vip]: clsrcexecut: env ORACLE_CONFIG_HOME=/oracle/product/10.2/crs 2013-12-16 14:57:08.192: [ RACG][1] [21721][1][ora.essrzc2.vip]: clsrcexecut: cmd = /oracle/product/10.2/crs/bin/racgeut -e _USR_ORA_DEBUG=5 54 /oracle/product/10.2/crs/bin/racgvip check essrzc2 2013-12-16 14:57:08.192: [ RACG][1] [21721][1][ora.essrzc2.vip]: clsrcexecut: rc = 0, time = 4.624s 2013-12-16 14:57:08.192: [ RACG][1] [21721][1][ora.essrzc2.vip]: clsrcaction: restyp=0 act_typ=2 stat=0 2013-12-16 14:57:08.192: [ RACG][1] [21721][1][ora.essrzc2.vip]: clsrcaction: return CLSR_CAA_SUCCESS 2013-12-16 14:57:08.192: [ RACG][1] [21721][1][ora.essrzc2.vip]: clsrcaction: init 0.000s 2013-12-16 14:57:08.192: [ RACG][1] [21721][1][ora.essrzc2.vip]: clsrcaction: action ok, 4.653s 2013-12-16 14:57:08.192: [ RACG][1] [21721][1][ora.essrzc2.vip]: clsrcaction: post 0.000s 2013-12-16 14:57:08.192: [ RACG][1] [21721][1][ora.essrzc2.vip]: clsrcaction: all 4.653s 2013-12-16 14:57:08.192: [ RACG][1] [21721][1][ora.essrzc2.vip]: end for resource = ora.essrzc2.vip, action = check, status = 0, time = 4.692s 2013-12-16 14:57:08.192: [ RACG][1] [21721][1][ora.essrzc2.vip]: clsrccln: exiting ora.essrzc2.vip refcount=1 2013-12-16 14:57:08.192: [ RACG][1] [21721][1][ora.essrzc2.vip]: clsrcprsrgter: gctx->prsrcfgref_clsrcgctx = 0 2013-12-16 14:57:38.420: [ RACG][1] [21974][1][ora.essrzc2.vip]: clsrcprsrgini: gctx->prsrcfgref_clsrcgctx = 0 2013-12-16 14:57:38.420: [ RACG][1] [21974][1][ora.essrzc2.vip]: clsrcini_ext: starting ora.essrzc2.vip refcount=1 global 2013-12-16 14:57:38.421: [ RACG][1] [21974][1][ora.essrzc2.vip]: begin for resource = ora.essrzc2.vip, action = check 2013-12-16 14:57:38.428: [ RACG][1] [21974][1][ora.essrzc2.vip]: clsrccssgetctx: all 0.007s 2013-12-16 14:57:38.436: [ RACG][1] [21974][1][ora.essrzc2.vip]: clsrcgetprsrctx: all 0.015s 2013-12-16 14:57:38.448: [ RACG][1] [21974][1][ora.essrzc2.vip]: clsrcnodeapp: prsr num_env = 0 2013-12-16 14:57:38.449: [ RACG][1] [21974][1][ora.essrzc2.vip]: clsrcnodeapp: setting ORACLE_CONFIG_HOME=/oracle/product/10.2/crs 2013-12-16 14:57:43.181: [ RACG][1] [21974][1][ora.essrzc2.vip]: Unable to get IPv6 interface information. Permission denied to 127.0.0.1 cmviewcl: Cannot view the cluster configuration: Permission denied. This user doesn't have access to view the cluster configuration. 2013-12-16 14:57:43.181: [ RACG][1] [21974][1][ora.essrzc2.vip]: Mon Dec 16 14:57:38 EAT 2013 [ 21979 ] Checking interface existance Mon Dec 16 14:57:38 EAT 2013 [ 21979 ] Calling getifbyip Mon Dec 16 14:57:38 EAT 2013 [ 21979 ] getifbyip: started for 132.42.37.144 2013-12-16 14:57:43.181: [ RACG][1] [21974][1][ora.essrzc2.vip]: Mon Dec 16 14:57:38 EAT 2013 [ 21979 ] Completed getifbyip lan900:801 Mon Dec 16 14:57:38 EAT 2013 [ 21979 ] Completed with initial interface test Mon Dec 16 14:57:38 EAT 2013 [ 21979 ] Broadcast = 132.42.37.255 2013-12-16 14:57:43.181: [ RACG][1] [21974][1][ora.essrzc2.vip]: Mon Dec 16 14:57:38 EAT 2013 [ 21979 ] checkIf: start for if=lan900 Mon Dec 16 14:57:42 EAT 2013 [ 21979 ] checkIf: RX packets checked if=lan900 failed Interface lan900 checked failed (host=essrzc2) 2013-12-16 14:57:43.181: [ RACG][1] [21974][1][ora.essrzc2.vip]: Mon Dec 16 14:57:42 EAT 2013 [ 21979 ] checkIf: end for if=lan900 Mon Dec 16 14:57:42 EAT 2013 [ 21979 ] Performing CRS_STAT testing Mon Dec 16 14:57:42 EAT 2013 [ 21979 ] Completed CRS_STAT testing 2013-12-16 14:57:43.181: [ RACG][1] [21974][1][ora.essrzc2.vip]: Mon Dec 16 14:57:43 EAT 2013 [ 21979 ] Completed second gateway test Mon Dec 16 14:57:43 EAT 2013 [ 21979 ] Interface tests Mon Dec 16 14:57:43 EAT 2013 [ 21979 ] DEBUG: FAIL_WHEN_ALL_LINK_DOWN = 1 and IF_USING = lan900 2013-12-16 14:57:43.181: [ RACG][1] [21974][1][ora.essrzc2.vip]: Invalid parameters, or failed to bring up VIP (host=essrzc2) 2013-12-16 14:57:43.181: [ RACG][1] [21974][1][ora.essrzc2.vip]: clsrcexecut: env ORACLE_CONFIG_HOME=/oracle/product/10.2/crs 2013-12-16 14:57:43.181: [ RACG][1] [21974][1][ora.essrzc2.vip]: clsrcexecut: cmd = /oracle/product/10.2/crs/bin/racgeut -e _USR_ORA_DEBUG=5 54 /oracle/product/10.2/crs/bin/racgvip check essrzc2 2013-12-16 14:57:43.181: [ RACG][1] [21974][1][ora.essrzc2.vip]: clsrcexecut: rc = 1, time = 4.731s 2013-12-16 14:57:43.182: [ RACG][1] [21974][1][ora.essrzc2.vip]: clsrcaction: restyp=0 act_typ=2 stat=1 2013-12-16 14:57:43.182: [ RACG][1] [21974][1][ora.essrzc2.vip]: clsrcaction: init 0.000s 2013-12-16 14:57:43.182: [ RACG][1] [21974][1][ora.essrzc2.vip]: clsrcaction: action failed, 4.761s 2013-12-16 14:57:43.182: [ RACG][1] [21974][1][ora.essrzc2.vip]: clsrcaction: post 0.000s 2013-12-16 14:57:43.182: [ RACG][1] [21974][1][ora.essrzc2.vip]: clsrcaction: all 4.761s 2013-12-16 14:57:43.182: [ RACG][1] [21974][1][ora.essrzc2.vip]: end for resource = ora.essrzc2.vip, action = check, status = 1, time = 4.801s 2013-12-16 14:57:43.182: [ RACG][1] [21974][1][ora.essrzc2.vip]: clsrccln: exiting ora.essrzc2.vip refcount=1 2013-12-16 14:57:43.182: [ RACG][1] [21974][1][ora.essrzc2.vip]: clsrcprsrgter: gctx->prsrcfgref_clsrcgctx = 0 2013-12-16 14:57:43.434: [ RACG][1] [22058][1][ora.essrzc2.vip]: clsrcprsrgini: gctx->prsrcfgref_clsrcgctx = 0 2013-12-16 14:57:43.434: [ RACG][1] [22058][1][ora.essrzc2.vip]: clsrcini_ext: starting ora.essrzc2.vip refcount=1 global 2013-12-16 14:57:43.435: [ RACG][1] [22058][1][ora.essrzc2.vip]: begin for resource = ora.essrzc2.vip, action = stop 2013-12-16 14:57:43.466: [ RACG][1] [22058][1][ora.essrzc2.vip]: clsrccssgetnodename: all 0.030s 2013-12-16 14:57:43.466: [ RACG][1] [22058][1][ora.essrzc2.vip]: clsrcnodeapps: calling FAILSRVSA 2013-12-16 14:57:43.483: [ RACG][1] [22058][1][ora.essrzc2.vip]: clsrcrundetach: cmd = /oracle/product/10.2/crs/bin/racgmain ora.essrzc2.vip rundetach 1 failsrvsa essrzc2, rc = 0, time = 0.016s 2013-12-16 14:57:43.483: [ RACG][1] [22058][1][ora.essrzc2.vip]: clsrcnodeapps: Posting PNWDOWN_EVENT 2013-12-16 14:57:43.484: [ RACG][1] [22058][1][ora.essrzc2.vip]: clsrccssgetnodename: all 0.000s 2013-12-16 14:57:43.484: [ RACG][1] [22058][1][ora.essrzc2.vip]: clsrccssgetnodename: all 0.000s 2013-12-16 14:57:43.484: [ RACG][1] [22058][1][ora.essrzc2.vip]: clsrcssgetrhost: using cached local host name 2013-12-16 14:57:43.486: [ RACG][1] [22058][1][ora.essrzc2.vip]: clsrccssgetlhost: all 0.002s 2013-12-16 14:57:43.493: [ RACG][1] [22058][1][ora.essrzc2.vip]: clsrcpostevt: EvmEventPost 1 0.001s 2013-12-16 14:57:43.493: [ RACG][1] [22058][1][ora.essrzc2.vip]: clsrcposthaevt: forward to EVM 从日志基本看出,出现问题是从“checkIf: RX packets checked if=lan900 failed”开始,从而导致资源切换,执行检测的命令为“racgvip”,此命令在完成2次“Completed second gateway test”后如果失败就进行切换,因此要解决问题,需要增加test次数,修改racgvip如下参数。 ...

2013-11-17 · 5 min · 865 words · kevin

ORA-65086: cannot open/close the pluggable database

在Oracle 12C中执行卸载命令后,如果执行PDB的OPEN操作,将会提示错误,如下: SQL> alter pluggable database pdb01 unplug into '/opt/oracle/pdb01.xml'; Pluggable database altered. SQL> ALTER PLUGGABLE DATABASE pdb01 OPEN; * ERROR at line 1: ORA-65086: cannot open/close the pluggable database 处理办法是先drop掉,然后插入此PDB。 SQL> drop pluggable database pdb01; Pluggable database dropped. SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO SQL> create pluggable database pdb01 using '/opt/oracle/pdb01.xml' nocopy; Pluggable database created. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB01 MOUNTED SQL> alter pluggable database pdb01 open; Pluggable database altered.

2013-09-12 · 1 min · 96 words · kevin

crs_stop 错误一列

grid 与 oracle 版本为11.2.0.4,为两节点RAC,在通过crs_stop -all命令关闭oracle服务时出现下面问题: [oracle@rac01 ~]$ crs_stat -t Name Type Target State Host ------------------------------------------------------------ ora.DATA.dg ora....up.type OFFLINE OFFLINE ora.FRA.dg ora....up.type OFFLINE OFFLINE ora....ER.lsnr ora....er.type OFFLINE OFFLINE ora....N1.lsnr ora....er.type OFFLINE OFFLINE ora.OCR.dg ora....up.type OFFLINE OFFLINE ora.asm ora.asm.type OFFLINE ONLINE rac02 ora.cvu ora.cvu.type OFFLINE OFFLINE ora.gsd ora.gsd.type OFFLINE OFFLINE ora....network ora....rk.type OFFLINE OFFLINE ora.oc4j ora.oc4j.type OFFLINE OFFLINE ora.ons ora.ons.type OFFLINE OFFLINE ora.rac.db ora....se.type OFFLINE OFFLINE ora....SM1.asm application OFFLINE ONLINE rac01 ora....01.lsnr application OFFLINE OFFLINE ora.rac01.gsd application OFFLINE OFFLINE ora.rac01.ons application OFFLINE OFFLINE ora.rac01.vip ora....t1.type OFFLINE OFFLINE ora....SM2.asm application OFFLINE ONLINE rac02 ora....02.lsnr application OFFLINE OFFLINE ora.rac02.gsd application OFFLINE OFFLINE ora.rac02.ons application OFFLINE OFFLINE ora.rac02.vip ora....t1.type OFFLINE OFFLINE ora....ry.acfs ora....fs.type OFFLINE OFFLINE ora.scan1.vip ora....ip.type OFFLINE OFFLINE 从状态可以看出ASM进程无法关闭,通过srvctl或者crs_stop关闭提示如下内容: ...

2013-08-31 · 1 min · 154 words · kevin

Oracle Database 12C 体验

oracle版本:12.1.0.1.0 redhat版本:RedHat EL5.6 x64 安装过程分以下四个步骤 一,安装准备 二,下载安装 三,过程详解 四,常见错误 一,安装准备 1,创建oracle用户 以root用户登录,并创建oracle用户以及所属的dba组 su - groupadd dba useradd -g dba oracle mkdir /home/oracle chown oracle:dba /home/oracle 2,配置系统参数 编辑 /etc/sysctl.conf 文件并添加下列参数: fs.file-max = 6815744 kernel.sem = 250 32000 100 128 kernel.shmmni = 4096 kernel.shmall = 1073741824 kernel.shmmax = 4398046511104 net.core.rmem_default = 262144 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 1048576 fs.aio-max-nr = 1048576 net.ipv4.ip_local_port_range = 9000 65500 编辑后可以运行“sysctl -p" 生效,或者重启系统 编辑 /etc/security/limits.conf 并添加下行参数: ...

2013-06-27 · 2 min · 348 words · kevin

ORA-00445错误一则

数据库在运行中突然出现如下错误,导致服务不可用。 ORA-00445: background process “m000” did not start after 120 seconds ORA-32701: Possible hangs up to hang ID=0 detected DIA0 terminating blocker (ospid: 14288486 sid: 4370 ser#: 7549) of hang with ID = 129 requested by master DIA0 process on instance 1 Hang Resolution Reason: Automatic hang resolution was performed to free a critical database process. by terminating session sid: 4370 ospid: 14288486 DIA0 successfully terminated session sid:4370 ospid:14288486 with status 31. ORA-00445: background process “J000” did not start after 120 seconds ORA-00445: background process “W005” did not start after 120 seconds ORA-00445: background process “J000” did not start after 120 seconds ERROR: Unable to normalize symbol name for the following short stack (at offset 311): 初步判断由于相应资源分配不足,导致进程无法启动,进一步分析日志,share_pool内存资源不足造成。 ...

2013-04-18 · 1 min · 134 words · kevin

GoldenGate 11g 简要配置步骤

描述:源端和目标端为Oracle 10gR2数据库,将scott数据库导出,在源端与目标端新增test用户,并导入scott的逻辑备份,来实现两个数据库之间test下数据同步。两个数据库的实例均为db01。 -- scott 资源导入导出命令 expdp scott/tiger schemas=scott dumpfile=scott.dmp directory=backup; impdp scott/tiger schemas=scott dumpfile=scott.dmp directory=backup; -- 创建GoldenGate用户并授权(S&T) CREATE USER ogg IDENTIFIED BY ogg DEFAULT TABLESPACE “USERS” TEMPORARY TABLESPACE “TEMP”; GRANT “DBA” TO ogg ; -- 创建数据库用户并授权(S&T) CREATE USER test IDENTIFIED BY welcome DEFAULT TABLESPACE “EXAMPLE” TEMPORARY TABLESPACE “TEMP”; GRANT “DBA” TO test ; -- 导入scott到test中(S&T) impdp test/welcome remap_schema=scott:test dumpfile=scott.dmp directory=backup; -- 检查附加日志并打开(S&T) SQL> archive log list; SQL> alter database add supplemental log data; SQL> alter system archive log current; SQL> alter database force logging; SQL> select log_mode,supplemental_log_data_min,force_logging from v$database; ...

2013-03-19 · 2 min · 301 words · kevin

Oracle 11gR2 RAC On AIX 6.1故障一例

系统环境:AIX 6.1(6100-04-11-1140) 数据库:Oracle 11.2.0.3 RAC with ASM 问题描述:数据库安装完毕后,节点而系统重启后数据库集群服务无法自动启动,同时SSH服务也无法自动启动。 由此想到是不是系统的自动服务有问题,首先检查了/etc/inittab下的启动级别,都是默认设置,为2,之后到/etc/rc.d/rc2.d目录检查,相关文件和权限正确。 之后比较两台机器的/etc/inittab,发现节点二多了一行配置: install_assist:2:wait:/usr/sbin/install_assist /dev/console 2>&1 Google下发现这是系统的安装助手,而且是交互式的,即如果没有响应,则会一直等待,那么在该行后面的命令将不会被执行。也就是说rc2.d下的服务将不会被启动,于是手动注视(在行开头添加冒号)了下,保存后重启系统,一切正常了。

2013-02-14 · 1 min · 14 words · kevin