今天，自己測試了一下對於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

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;

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

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

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的執行情況，執行次數，執行時間

oracle自測總結1

相關文章