今天，自己测试了一下对于oracle掌握的熟练程度，仅限于基本的操作，自己做了简单的总结。
1.修改global name的时候报了如下的错误
SQL> alter system set db_domain='oracle.com' ;
alter system set db_domain='oracle.com'
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
solution:
alter system set global_names=true;
alter system set db_domain='oracle.com' scope=spfile;
SQL> create bigfile tablesace tbs2 datafile '/u01/app/oracle/oradata/PROD/disk2/tbs2.dbf' size 20M autoextend on maxsize 500G;
create bigfile tablesace tbs2 datafile '/u01/app/oracle/oradata/PROD/disk2/tbs2.dbf' size 20M autoextend on maxsize 500G
*
ERROR at line 1:
ORA-00922: missing or invalid option
--创建Temporary tablespace报了如下的错误
SQL> create temporary tablespace tempts2 tempfile '/u01/app/oracle/oradata/PROD/disk2/tempts02.dbf' size 50M autoextend on group temp_grp;
create temporary tablespace tempts2 tempfile '/u01/app/oracle/oradata/PROD/disk2/tempts02.dbf' size 50M autoextend on group temp_grp
*
ERROR at line 1:
ORA-02180: invalid option for CREATE TABLESPACE
solution:
remove auotoextend on
所有账户密码都是oracle,如何设置
shared server登录后，为什么有的server显示是none
SQL> /
SID USERNAME SERVER
---------- ------------------------------ ---------
25 OUTLN NONE
27 SYSTEM SHARED
29 OUTLN NONE
31 DEDICATED
33 DEDICATED
36 DEDICATED
39 DEDICATED
40 DEDICATED
41 DEDICATED
[oracle@oel1 admin]$ rman target "system/oracle@prod" catalog rcuser/rcuser@emrep
XXXXXXXXXXXXXXXXXX
solution:
[oracle@oel1 bdump]$ rman target \'sys/oracle@prod as sysdba\' catalog rcuser/rcuser@emrep
Recovery Manager: Release 10.2.0.1.0 - Production on Sun May 26 22:03:17 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PROD (DBID=228221281)
connected to recovery catalog database
RMAN>
RMAN> register database;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of register command at 05/26/2013 08:52:40
RMAN-06428: recovery catalog is not installed
solution:
RMAN> create catalog;
recovery catalog created
RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN> backup incremental level=0 database plus archivelog skip inaccessible delete input;
RMAN> backup incremental level=0 database plus archivelog skip inaccessible delete input;
Starting backup at 26-MAY-13
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=23 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=22 devtype=DISK
channel ORA_DISK_1: starting compressed archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=5 recid=1 stamp=816426847
channel ORA_DISK_1: starting piece 1 at 26-MAY-13
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 05/26/2013 09:14:15
ORA-19715: invalid format F for generated name
ORA-27302: failure occurred at: slgpn
solution:
format as '%U' not ‘%F'
Database Instance Configuration Result
oracle.sysman.emSDK.emd.comm.CommException: Received unexpected response text : EMDAEMONEMDAEMON HTTP/1.1 404 , Not Found
- Received unexpected response text : EMDAEMONEMDAEMON HTTP/1.1 404 , Not Found
solutin:
after agent installation has done, need to check again.
SQL> alter system enable block change tracking as '/home/oracle/chg';
alter system enable block change tracking as '/home/oracle/chg'
*
ERROR at line 1:
ORA-00922: missing or invalid option
solution:
SQL> alter database enable block change tracking using file '/home/oracle/bak/block_tracking';
Database altered.
recovery window policy
rman> configure
[oracle@oel1 admin]$ rman target / catalog rcuser/rcuser@emrep
Recovery Manager: Release 10.2.0.1.0 - Production on Sun May 26 22:13:33 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PROD (DBID=228221281)
connected to recovery catalog database
RMAN> show all;
starting full resync of recovery catalog
full resync complete
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 5 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/bak/ctl%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '/home/oracle/bak/prod_%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE ON;
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/10.2.0/db_1/dbs/snapcf_PROD.f'; # default
RMAN> exit
如果使用tns来连接，就会有问题
[oracle@oel1 admin]$ rman target \'sys/oracle@PROD as sysdba\' catalog rcuser/rcuser@EMREP
Recovery Manager: Release 10.2.0.1.0 - Production on Sun May 26 22:14:07 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PROD (DBID=228221281)
connected to recovery catalog database
RMAN> show all;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 5 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/bak/ctl%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '/home/oracle/bak/prod_%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE ON;
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/10.2.0/db_1/dbs/snapcf_PROD.f'; # default
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-12005: error during channel cleanup
ORA-19550: cannot use backup/restore functions while using dispatcher
RMAN>
solution:
在tnsnames.ora中对PROD server type加入dedicated
flashback database 后
SQL> DESC FAL1
ERROR:
ORA-04043: object FAL1 does not exist
[oracle@oel1 ~]$ exp 'sys/oracle@prod as sysdba' file=fal1.dmp tables=fal1
LRM-00108: invalid positional parameter value 'as'
EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
EXP-00000: Export terminated unsuccessfully
[oracle@oel1 ~]$
solution:
exp \'sys/oracle@PROD as sysdba\' file=fal1.dmp tables=fal1
SQL Error
Failed to commit: ORA-27477: "HR.JOB1" already exists ORA-06512: at "SYS.DBMS_ISCHED", line 99 ORA-06512: at "SYS.DBMS_SCHEDULER", line 319 ORA-06512: at line 2
Failed to commit: ORA-27476: "SYS.JCLASS1" does not exist ORA-06512: at "SYS.DBMS_ISCHED", line 99 ORA-06512: at "SYS.DBMS_SCHEDULER", line 319 ORA-06512: at line 2
solution:赋予hr schedule_admin的权限
SQL> GRANT SCHEDULER_ADMIN TO HR;
Grant succeeded.
SQL> select * from employees where department_id=20;
select * from employees where department_di=20
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kzaSqlTxtLob1], [25153], [], [],
[], [], [], []
ORA-25153: Temporary Tablespace is Empty
solution:
添加一个tempoarary tablespace，指定hr的temporary tablespace 为新创建的tablespce
SQL> exec dbms_tts.transport_set_check('TBS4',TRUE,TRUE);
BEGIN dbms_tts.transport_set_check('TBS4',TRUE,TRUE); END;
*
ERROR at line 1:
ORA-25153: Temporary Tablespace is Empty
solution:
添加一个tempoarary tablespace，指定hr的temporary tablespace 为新创建的tablespce
然后可以指定database level
alter database default temporary tablespace xxxx;
for update materialized view
solution:
SQL> create materialized view mv1 refresh fast on commit enable query rewrite as select * from tt;
create materialized view mv1 refresh fast on commit enable query rewrite as select * from tt
*
ERROR at line 1:
ORA-12014: table 'TT' does not contain a primary key constraint
SQL> create materialized view mv1 refresh fast on commit with rowid enable query rewrite as select * from tt;
Materialized view created.
SQL> create materialized view mv1 for update as select object_id,object_name from tt group by object_id,object_name;
create materialized view mv1 for update as select object_id,object_name from tt group by object_id,object_name
*
ERROR at line 1:
ORA-12013: updatable materialized views must be simple enough to do fast refresh
SQL> create materialized view mv1 refresh fast with rowid for update enable query rewrite as select * from tt;
Materialized view created.
如果调整日志文件切换的频率
如何控制Job的运行情况，运行次数，运行时间
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23718752/viewspace-762046/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/23718752/viewspace-762046/
4196
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
