ITPub博客

首页 > 数据库 > Oracle > oracle自测总结1

oracle自测总结1

Oracle 作者:jianghaifuyun1987 时间:2014-03-21 14:50:06 0 删除 编辑
今天,自己测试了一下对于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/14300593/viewspace-1126694/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-03-07

  • 博文量
    75
  • 访问量
    74637