Bo's Oracle Station

查看: 2318|回复: 0

活动第13/14次(2018-04-29星期日上下午)

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2018-4-29 09:26:45 | 显示全部楼层 |阅读模式

  1. [oracle@station90 dbhome_1]$ sqlplus /nolog

  2. SQL*Plus: Release 11.2.0.1.0 Production on Sun Apr 29 10:59:09 2018

  3. Copyright (c) 1982, 2009, Oracle.  All rights reserved.

  4. SQL> conn / as sysdba
  5. Connected.
  6. SQL> show parameter nls_date

  7. NAME                                     TYPE         VALUE
  8. ------------------------------------ ----------- ------------------------------
  9. nls_date_format                      string
  10. nls_date_language                     string
  11. SQL> select  sysdate from dual;

  12. SYSDATE
  13. -------------------
  14. 2018-04-29:10:59:33

  15. SQL> show parameter terri

  16. NAME                                     TYPE         VALUE
  17. ------------------------------------ ----------- ------------------------------
  18. nls_territory                             string         AMERICA
  19. SQL> ! echo $NLS_DATE_FORMAT
  20. YYYY-MM-DD:HH24:MI:SS

  21. SQL> alter system set nls_date_format='DD-MON-YYYY';
  22. alter system set nls_date_format='DD-MON-YYYY'
  23.                  *
  24. ERROR at line 1:
  25. ORA-02096: specified initialization parameter is not modifiable with this
  26. option


  27. SQL> alter session set nls_date_format='DD-MON-YYYY';

  28. Session altered.

  29. SQL> select  sysdate from dual;

  30. SYSDATE
  31. --------------------
  32. 29-APR-2018

  33. SQL> show parameter nls_lang

  34. NAME                                     TYPE         VALUE
  35. ------------------------------------ ----------- ------------------------------
  36. nls_language                             string         AMERICAN
  37. SQL> alter system set nls_language='korean';
  38. alter system set nls_language='korean'
  39.                  *
  40. ERROR at line 1:
  41. ORA-02096: specified initialization parameter is not modifiable with this
  42. option


  43. SQL> alter session set nls_language='korean';

  44. Session altered.

  45. SQL> select  sysdate from dual;

  46. SYSDATE
  47. ----------------
  48. 29-4월 -2018

  49. SQL>
复制代码
  1. SQL> show parameter open_cursor

  2. NAME                                     TYPE         VALUE
  3. ------------------------------------ ----------- ------------------------------
  4. open_cursors                             integer         300
  5. SQL> alter system set open_cursor=400;
  6. alter system set open_cursor=400
  7.                  *
  8. ERROR at line 1:
  9. ORA-02065: ALTER SYSTEM에 대한 부당한 옵션


  10. SQL> alter system set open_cursors=400;

  11. System altered.

  12. SQL> show parameter spfile

  13. NAME                                     TYPE         VALUE
  14. ------------------------------------ ----------- ------------------------------
  15. spfile                                     string         +DATA/orcl/spfileorcl.ora
复制代码

Screenshot.png


添加磁盘组:

  1. select  * from v$asm_disk;

  2. select  * from v$asm_diskgroup;

  3. alter diskgroup data add failgroup fg1 disk '/dev/raw/raw16' name DATA_0006
  4.                                          failgroup fg2 disk '/dev/raw/raw17' name DATA_0007
  5.                                          rebalance power 11;
复制代码
arbn进程:
Screenshot.png


归整故障组:
  1. select  * from v$asm_disk;

  2. select  * from v$asm_diskgroup;

  3. alter diskgroup data drop disk DATA_0005 disk DATA_0000 ;

  4. alter diskgroup data undrop disks;  

  5. select  * from v$asm_operation;

  6. alter diskgroup data add failgroup fg1 disk '/dev/raw/raw12' name DATA_0005
  7.                                              failgroup fg2 disk '/dev/raw/raw7' name DATA_0000
  8.                                              rebalance power 11;
  9.                                              
  10. alter diskgroup data drop disk DATA_0004 disk DATA_0001  rebalance power 11 ;

  11. alter diskgroup data add failgroup fg1 disk '/dev/raw/raw11' name DATA_0004
  12.                                              failgroup fg2 disk '/dev/raw/raw8' name DATA_0001
  13.                                              rebalance power 11;
  14.                                              
  15. alter diskgroup data drop disk DATA_0003 disk DATA_0002  rebalance power 11;

  16. alter diskgroup data add failgroup fg1 disk '/dev/raw/raw10' name DATA_0003
  17.                                              failgroup fg2 disk '/dev/raw/raw9' name DATA_0002
  18.                                              rebalance power 11;
  19.                                          
复制代码

创建磁盘组:
  1. create diskgroup highdata high redundancy
  2. failgroup fg1 disk 'ORCL:ASMDISK1' name highdata_0001
  3. failgroup fg2 disk 'ORCL:ASMDISK2' name highdata_0002
  4. failgroup fg3 disk 'ORCL:ASMDISK3' name highdata_0003;

  5. select  * from v$asm_disk;

  6. select  * from v$asm_diskgroup;
复制代码

ASM磁盘组里的所有东西都是别名,“文件夹”的别名的“文件号”和“文件化身号”都是4294967295,而“文件”的文件号是比较小的数字而且在磁盘组内唯一,“文件”的“文件化身号”越大代表越新。所以如果要对磁盘组目录下的文件按照创建/修改时间来升序排序的话,要根据文件别名的第三列按照数字来排序:

[oracle@station90 ~]$ asmcmd ls +FRA/ORCL/ARCHIVELOG/2018_04_29   | sort -t . -k 3 -n
thread_1_seq_28.269.974716305
thread_1_seq_29.270.974716347
thread_1_seq_30.271.974716365
thread_1_seq_31.272.974716393
thread_1_seq_32.273.974716417
thread_1_seq_33.274.974716877
thread_1_seq_34.275.974718007
thread_1_seq_35.276.974724811
thread_1_seq_36.277.974730669
thread_1_seq_37.278.974732405
thread_1_seq_38.279.974736817
thread_1_seq_39.280.974736857
thread_1_seq_40.281.974739181

创建带自定义模板的表空间:
  1. [oracle@station90 ~]$ sqlplus /nolog

  2. SQL*Plus: Release 11.2.0.1.0 Production on Sun Apr 29 18:09:44 2018

  3. Copyright (c) 1982, 2009, Oracle.  All rights reserved.

  4. SQL> conn / as sysdba
  5. Connected.
  6. SQL> create tablespace tbshigh2 datafile  '+highdata(temp1)' size 10M ;

  7. Tablespace created.

  8. SQL>
复制代码

一个normal冗余度的磁盘组具有最大的灵活性:既可以有(mirror coarse/fine)的模板,也可以有(high coarse/fine),更可以有(unprotected coarse/fine):

  1. select  * from v$asm_alias  where group_number=3;

  2. select  * from v$asm_file  where group_number=3 and file_number=258;

  3. select  * from v$asm_template where group_number=3;

  4. alter diskgroup highdata add template temp1 attributes ( mirror  fine  );

  5. alter diskgroup highdata add template temp2 attributes ( unprotected  coarse );

  6. select  * from v$asm_template where group_number=3;

  7. -------------------
  8. select  * from v$asm_template where group_number=1;

  9. alter diskgroup data add template temp1 attributes ( unprotected  fine  );
复制代码
关于别名:
  1. [oracle@station90 ~]$ asmcmd
  2. ASMCMD> ls
  3. DATA/
  4. FRA/
  5. HIGHDATA/
  6. ASMCMD> cd HIGHDATA/
  7. ASMCMD> ls
  8. ORCL/
  9. RCAT/
  10. ASMCMD> cd RCAT/     
  11. ASMCMD> ls
  12. DATAFILE/
  13. ASMCMD> cd DATAFILE/
  14. ASMCMD> ls
  15. TBSHIGH.256.974741575
  16. TBSHIGH2.258.974743821
  17. ASMCMD> pwd
  18. +HIGHDATA/RCAT/DATAFILE
  19. ASMCMD> exit
  20. [oracle@station90 ~]$ sqlplus /nolog

  21. SQL*Plus: Release 11.2.0.1.0 Production on Sun Apr 29 18:21:29 2018

  22. Copyright (c) 1982, 2009, Oracle.  All rights reserved.

  23. SQL> conn / as sysasm
  24. Connected.
  25. SQL> alter diskgroup highdata add alias '+HIGHDATA/RCAT/DATAFILE/TBSHIGH2.dbf' for '+HIGHDATA/RCAT/DATAFILE/TBSHIGH2.258.974743821';

  26. Diskgroup altered.
复制代码
在数据库那边直接创建(还带模板):
  1. SQL> create tablespace tbshigh3 datafile '+highdata(temp1)/RCAT/DATAFILE/TBSHIGH3.dbf' size 10M;

  2. Tablespace created.

  3. SQL>
复制代码

暴力删除磁盘组:
  1. SQL*Plus: Release 11.2.0.1.0 Production on Sun Apr 29 18:21:29 2018

  2. Copyright (c) 1982, 2009, Oracle.  All rights reserved.

  3. SQL> conn / as sysasm
  4. Connected.
  5. SQL> alter diskgroup highdata add alias '+HIGHDATA/RCAT/DATAFILE/TBSHIGH2.dbf' for '+HIGHDATA/RCAT/DATAFILE/TBSHIGH2.258.974743821';

  6. Diskgroup altered.

  7. SQL> shutdown immediate
  8. ORA-15097: cannot SHUTDOWN ASM instance with connected client
  9. SQL> shutdown abort
  10. ASM instance shutdown
  11. SQL> startup
  12. ASM instance started

  13. Total System Global Area  283930624 bytes
  14. Fixed Size                    2212656 bytes
  15. Variable Size                  256552144 bytes
  16. ASM Cache                   25165824 bytes
  17. ASM diskgroups mounted
  18. SQL> drop diskgroup highdata;
  19. drop diskgroup highdata
  20. *
  21. ERROR at line 1:
  22. ORA-15039: diskgroup not dropped
  23. ORA-15053: diskgroup "HIGHDATA" contains existing files


  24. SQL> drop diskgroup highdata including contents;

  25. Diskgroup dropped.



复制代码
  1. Diskgroup dropped.

  2. create diskgroup highdata high redundancy
  3. failgroup fg1 disk 'ORCL:ASMDISK1' name highdata_0001
  4. failgroup fg2 disk 'ORCL:ASMDISK2' name highdata_0002
  5.   4  failgroup fg3 disk 'ORCL:ASMDISK3' name highdata_0003;

  6. Diskgroup created.
复制代码


  1. oracle@station90 ~]$ sqlplus /nolog

  2. SQL*Plus: Release 11.2.0.1.0 Production on Sun Apr 29 18:30:23 2018

  3. Copyright (c) 1982, 2009, Oracle.  All rights reserved.

  4. SQL> conn / as sysdba
  5. Connected to an idle instance.
  6. SQL> startup
  7. ORACLE instance started.

  8. Total System Global Area 1603411968 bytes
  9. Fixed Size                    2213776 bytes
  10. Variable Size                  620759152 bytes
  11. Database Buffers          973078528 bytes
  12. Redo Buffers                    7360512 bytes
  13. Database mounted.
  14. ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
  15. ORA-01110: data file 7: '+HIGHDATA/rcat/datafile/tbshigh.256.974741575'


  16. SQL> archive log list;
  17. Database log mode               Archive Mode
  18. Automatic archival               Enabled
  19. Archive destination               USE_DB_RECOVERY_FILE_DEST
  20. Oldest online log sequence     49
  21. Next log sequence to archive   51
  22. Current log sequence               51
  23. SQL> alter database create datafile 7 ;

  24. Database altered.

  25. SQL> recover datafile 7 ;
  26. ORA-00283: recovery session canceled due to errors
  27. ORA-01110: data file 7: '+HIGHDATA/rcat/datafile/tbshigh.256.974741575'
  28. ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
  29. ORA-01110: data file 7: '+HIGHDATA/rcat/datafile/tbshigh.256.974741575'


  30. SQL> alter database create datafile 7  as new;
  31. alter database create datafile 7  as new
  32.                                      *
  33. ERROR at line 1:
  34. ORA-02236: invalid file name


  35. SQL> alter database rename file '+HIGHDATA/rcat/datafile/tbshigh.256.974741575' to '+HIGHDATA/rcat/datafile/+HIGHDATA/rcat/datafile/TBSHIGH.256.974745093';
  36. alter database rename file '+HIGHDATA/rcat/datafile/tbshigh.256.974741575' to '+HIGHDATA/rcat/datafile/+HIGHDATA/rcat/datafile/TBSHIGH.256.974745093'
  37. *
  38. ERROR at line 1:
  39. ORA-01511: error in renaming log/data files
  40. ORA-15122: ASM file name
  41. '+HIGHDATA/rcat/datafile/+HIGHDATA/rcat/datafile/TBSHIGH.256.974745093'
  42. contains an invalid file number


  43. SQL> alter database rename file '+HIGHDATA/rcat/datafile/tbshigh.256.974741575' to '+HIGHDATA/rcat/datafile/TBSHIGH.256.974745093';
  44. Database altered.

  45. SQL> recover datafile 7 ;
  46. Media recovery complete.
  47. SQL> alter database open ;
  48. alter database open
  49. *
  50. ERROR at line 1:
  51. ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
  52. ORA-01110: data file 8: '+HIGHDATA/rcat/datafile/tbshigh2.258.974743821'


  53. SQL> alter database create datafile 8  as new;
  54. alter database create datafile 8  as new
  55.                                      *
  56. ERROR at line 1:
  57. ORA-02236: invalid file name


  58. SQL> alter database create datafile 8  ;

  59. Database altered.

  60. SQL> alter database rename file '+HIGHDATA/rcat/datafile/tbshigh2.258.974743821' to '+HIGHDATA/rcat/datafile/TBSHIGH2.257.974745279';

  61. Database altered.

  62. SQL> recover datafile 8 ;
  63. Media recovery complete.
  64. SQL> alter database open ;
  65. alter database open
  66. *
  67. ERROR at line 1:
  68. ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
  69. ORA-01110: data file 9: '+HIGHDATA/rcat/datafile/tbshigh3.dbf'


  70. SQL> alter database create datafile 9 ;

  71. Database altered.

  72. SQL> recover datafile 9 ;
  73. Media recovery complete.
  74. SQL> alter database open ;

  75. Database altered.

  76. SQL>
复制代码
另外一个用到它的库:

  1. [oracle@station90 ~]$ sqlplus /nolog

  2. SQL*Plus: Release 11.2.0.1.0 Production on Sun Apr 29 18:28:58 2018

  3. Copyright (c) 1982, 2009, Oracle.  All rights reserved.

  4. SQL> conn / as sysdba
  5. Connected to an idle instance.
  6. SQL> startup
  7. ORACLE instance started.

  8. Total System Global Area 6680915968 bytes
  9. Fixed Size                    2213936 bytes
  10. Variable Size                 3556771792 bytes
  11. Database Buffers         3087007744 bytes
  12. Redo Buffers                   34922496 bytes
  13. Database mounted.
  14. ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
  15. ORA-01110: data file 6: '+HIGHDATA/orcl/datafile/tbshighorcl.257.974741625'


  16. SQL> alter database create datafile 6 as new;

  17. Database altered.

  18. SQL> show parameter db_recovery_file

  19. NAME                                     TYPE         VALUE
  20. ------------------------------------ ----------- ------------------------------
  21. db_recovery_file_dest                     string         +FRA
  22. db_recovery_file_dest_size             big integer 3882M
  23. SQL> recover datafile 6;
  24. Media recovery complete.
  25. SQL> alter database open ;

  26. Database altered.

  27. SQL>
复制代码



回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-29 23:43 , Processed in 0.035403 second(s), 27 queries .

快速回复 返回顶部 返回列表