ITPub博客

RMAN> list backup;RMAN> backup database plus archivelog format '/opt/oracle/app/oracle/data_bak/%d_full_backup';Starting backup at 25-JAN-11current log archivedusing channel ORA_DISK_1channel ORA_DISK_1: starting archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1 sequence=2 recid=1 stamp=741352074input archive log thread=1 sequence=3 recid=2 stamp=741353773input archive log thread=1 sequence=4 recid=3 stamp=741353829input archive log

1.如果oracle在参数文件中设置了db_domain，那么在客户端tnsnames.ora文件中应该加上db_domain,否则在连接数据库时，会出现tnsping通，但是sqlplus会报错,并且在listener中会有TNS_12564错误。例如：db_domain=egfit.com 客户端需做如下配置。ORCLST =(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.137.43.90)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = ORCLST.egfit.com))) 2.session会话过多ora-00018错误达到会话限制alter system set session=300;[@more@]

使用DBCA的模板迁移数据库的步骤及注意事项1.首先，利用DBCA创建源DB的DB模板包括data和structure.注意在此过程中,源DB会自动关闭并重启2.copy template到目标DB的$ORACLE_HOME/assistants/dbca/templates/下3.利用目标DB的DBCA创建clone DB4.利用ORAPWD工具创建SYS密码5.用SYS登录，解锁所有user并重新设置密码。因为迁移后，所有user都被锁，并且密码expired.6.如有DBLINK需要查看是否可用注：当数据库迁移后,一定要查看所有的DBLINK是否还有效。[@more@]

1.基于用户的备份与恢复冷备(noarchivelog mode)：shutdown immediate;cp datafile,control file,redo log to the new locationstartup;热备(archivelog mode)：alter database begin backup;cp datafile,control file,redo log to the new locationalter database end backup;alter tablespace <> begin backup;cp datafile to a new location;alter tablespace <> end backup;热备恢复：--完全恢复startup mount;recovery database;alter database open;--不完全恢复startup mount;recover database until time <>;al

主动维护数据字典中的信息和表中的信息，是不一样的，所以要统计信息和收集信息。statistics_level：basic手动收集统计信息type自动收集统计信息(默认）all自动收集统计信息AWR（automatic workload repository)SGA－＞MMON－＞AWR（SYS SCHEMA），默认收集时间是60分钟一次记录数据库度量快照，保留期为7天。MMON在SGA中收集统计信息放入AWR里。ADDM（automatic database diagnostic monitor)在记录每个AWR快照之后运行，监视实例：检测瓶颈，在AWR存储结果DBMS＿ADVISOR程序包DBA＿OUTSTANDING＿ALERTS DBA＿ALERT＿HISTORY自动维护任务

用户认证方式 passwordexternal:域名服务器认证，第三方认证global: AD LDAPSYSOPER ：comlete recovery only.SYSOPER: can not perform 'CREATE DATABASE ' 'DROP DATABASE' SYSMAN、DBSNMP ：OEMprofile(概要文件） edit profile at $ORACLE_HOME/rdbms/admin/utlpwdmg.sql to modify default profilecreate profile pro_u1 limit;alter user u1 profile pro_u1;dba_profiles约束条件enable/disablevalidate/novalidate解决FK问题：enable novalidate(do experiments) --可以OK.启用constraints但是不验证已存在的记录FK_TAB中存在

drop table t_name;show recyclebin;flashback table table_name to before drop;drop table t_name purge;after we drop table a table we can flash it back from recyclebin ,except SYS schema of the table.viewuser_viewscreate or replace FORCE view vw_force as select * from aa; --force to create a view when aa table doesn't exist.sequenceuser_sequencesindexuser_ind_columnsuser_indexesset autotrace on;synonymuser_synonymsdba_synonyms --see the pulic synonymdictionarydict --contains all the dictionary tab

group functionsum() avg() max() min()the group function will not handle null value except count(*).count(*)　return all rows including null;count(expr) return the not null rows.SQL> select count(*) ,count(comm) from emp; COUNT(*) COUNT(COMM)---------- ----------- 14 4count(distinct expr) select column,group_function(expr_column) from table group by column order by expr;--group by column having group_condition order by不使用别名或数字作为order by 的排序条件，因为有可能会导致优

昨天晚上无缘无故失眠了，今天又感冒了，所以就成悲剧了。该锻炼身体了，估计是体质变差的原因。想想也是，好久没有锻炼了，身上都开始长坠肉了，从今天开始锻炼。好了，不写了去锻炼了。[@more@]

BFILENAME BFILENAME returns a BFILE locator that is associated with a physical LOB binary file on the server file system.FILEISOPEN FunctionThis function finds out whether a BFILE was opened with the given FILE locator.SyntaxDBMS_LOB.FILEISOPEN ( file_loc IN BFILE) RETURN INTEGER; LOADFROMFILE Procedure This procedure copies all, or a part of, a source external LOB (BFILE) to a destination internal LOB. Syntax DBMS_LOB.LOADFROMFILE ( dest_lob IN OUT NOCOPY BLOB, src_

[oracle@RAC1 ~]$ crs_stat -tName Type Target State Host------------------------------------------------------------ora....11.inst application ONLINE ONLINE rac1ora....12.inst application ONLINE OFFLINEora.RACDB1.db application ONLINE ONLINE rac1ora....SM1.asm application ONLINE ONLINE rac1ora....C1.lsnr application ONLINE ONLINE rac1ora.rac1.gsd application ONLINE ONLINE rac1ora.rac1.ons application ONLINE O

[root@RAC1 ~]# uname -aLinux RAC1 2.6.9-89.0.28.ELsmp #1 SMP Fri Aug 20 16:11:59 EDT 2010 i686 i686 i386 GNU/Linux[root@RAC1 ~]# service vsftpd startu4e3a vsftpd u542fu52a8 vsftpduff1a500 OOPS: could not bind listening IPv4 socket [ u786eu5b9a ][root@RAC1 ~]# service vsftpd stopu5173u95ed vsftpduff1a [ u786eu5b9a ][root@RAC1 ~]# service vsftpd startu4e3a vsftpd u542fu52a8 vsftpduff1a

有人说做IT的不懂生活。有时想想好像也是，除了工作要对着计算机，回家也要对着电脑，只是做的事不同。工作是写代码或是其他相关的，回家忙着写blog，看电影或是玩游戏。其实这何尝不是一种生活呢，难道都要到外面去活动才能叫生活，只是大家生活方式不同而已，况且现在宅男宅女多的是，只要大家过得充实、快乐,方式其实并不重要！[@more@]

OS platform: RHEL/etc/init.d/iptables status --check firewall statussystem-config-securitylevel --config firewall[@more@]

首先利用DBCA创建模板文件test_dbca_silent，然后利用下面的命令创建数据库 in silent mode.C:windowssystem32>dbca -createDatabase -templateName "D:optoracleproduct10.2.0db_1assistantsdbcatemplatestest_dbca_silent.dbc" -gdbName jerry.cn -silent正在复制数据库文件1% 已完成3% 已完成11% 已完成18% 已完成26% 已完成37% 已完成正在创建并启动 Oracle 实例40% 已完成45% 已完成50% 已完成55% 已完成56% 已完成60% 已完成62% 已完成正在进行数据库创建66% 已完成70% 已完成73% 已完成85% 已完成96% 已完成100% 已完

silent mode install oracle software./runInstaller -record -destinationFile /tmp/install.rsp./runInstaller -responseFile /tmp/install.rsp -silentsilent mode install oracle database./dbca ORACLE DBCA创建数据库DBCA template迁移ＤＢ（做实验) －＞ＯＫ　　数据库软件版本需要相同,新的数据库中用户需要重新启用,即首先解锁用户,再重新设置密码.并且要创建PASSWORD FILE，否则远程SYSDBA登录不了.在创建template过程中，数据库会自动关闭，然后重启。DBCA图形界面DBCA silent(做实验)手动创建（做实验)ORACLE INSTANCEemctl s

时区NLS_DATE_FORMATv$timezone_names,current_date,dbtimezone,sessiontimezone,localtimestamp,current_timestampselect dbtimezone from dual; --database zoneselect sessiontimezone from dual; --current zoneselect * from v$timezone_names;timestamptimestamp with time zonetimestamp with local time zoneyear -4712 to 9999timezone_hour -12 to 14timzone_minute 00 to 59SQL> create table tt1(d1 date,d2 timestamp,d3 timestamp with time zone,d4 timestamp with local time zone);Table created.SQL> insert i

This is a test for my first technology blog![@more@]