课程第13/14次

admin

Notice: This blog is written by Bo Tang.

2019-06-10

1. create tablespace rcat datafile '/u01/app/oracle/oradata/rcat/rcat.dbf' size 100M autoextend on ;
   
2. 
   
3. create user u80 identified by oracle_4U default tablespace rcat quota unlimited on rcat;
   
4. 
   
5. grant recovery_catalog_owner to u90;

复制代码

1. . oraenv
   
2. orcl
   
3. ......
   
4. rman target / catalog u80/oracle_4U@rcat
   
5. rman>  create catalog;
   
6. rman> register database;

复制代码

1. select  * from v$asm_template where group_number=1;
   
2. 
   
3. select  * from v$asm_file
   
4. where group_number=1 and  file_number=268;
   
5. 
   
6. select  * from v$asm_alias
   
7.   where group_number=1 and file_number=268;
   
8.   where lower(name) ='tbs2.dbf';
   
9. 
   
10. --SYSTEM.256.1008007421
    
11. 
    
12. 
    
13. alter diskgroup data add template template1 attribute (unprotected fine);
    
14. -------------------------------------

复制代码

1. select  * from v$logfile;
   
2. 
   
3. select  * from v$datafile;
   
4. 
   
5. create tablespace tbs1 datafile '+data(template1)' size 10M ;
   
6. 
   
7. create tablespace tbs2 datafile '+data(template1)/orcl/datafile/tbs2.dbf' size 10M ;

复制代码

--------------------------------------------------
关于

1. [oracle@station80 ~]$ sqlplus /nolog
   
2. 
   
3. SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 10 20:22:08 2019
   
4. 
   
5. Copyright (c) 1982, 2013, Oracle.  All rights reserved.
   
6. 
   
7. SQL> conn / as sysasm
   
8. Connected.
   
9. SQL> ALTER DISKGROUP HIGHDATA ADD TEMPLATE TEMPLATE1 ATTRIBUTES (mirror FINE);
   
10. 
    
11. Diskgroup altered.
    
12. 
    
13. SQL>  ALTER DISKGROUP HIGHDATA ADD TEMPLATE TEMPLATE2 ATTRIBUTES ( unprotected  fine ) ;
    
14. ALTER DISKGROUP HIGHDATA ADD TEMPLATE TEMPLATE2 ATTRIBUTES ( unprotected  fine )
    
15. *
    
16. ERROR at line 1:
    
17. ORA-15067: command or option incompatible with diskgroup redundancy
    
18. 
    
19. 
    
20. SQL>  ALTER DISKGROUP fra ADD TEMPLATE TEMPLATE1  ATTRIBUTES ( unprotected  fine ) ;
    
21. 
    
22. Diskgroup altered.
    
23. 
    
24. SQL>  ALTER DISKGROUP fra ADD TEMPLATE TEMPLATE2  ATTRIBUTES (  mirror  fine ) ;
    
25. ALTER DISKGROUP fra ADD TEMPLATE TEMPLATE2  ATTRIBUTES (  mirror  fine )
    
26. *
    
27. ERROR at line 1:
    
28. ORA-15067: command or option incompatible with diskgroup redundancy
    
29. 
    
30. 
    
31. SQL>   ALTER DISKGROUP fra ADD TEMPLATE TEMPLATE2  ATTRIBUTES (  high fine ) ;
    
32.   ALTER DISKGROUP fra ADD TEMPLATE TEMPLATE2  ATTRIBUTES (  high fine )
    
33. *
    
34. ERROR at line 1:
    
35. ORA-15067: command or option incompatible with diskgroup redundancy
    
36. 
    
37. 
    
38. SQL>
    

复制代码

删除磁盘组，有client时候是删除不掉的：

1. SQL> drop diskgroup highdata including contents;
   
2. drop diskgroup highdata including contents
   
3. *
   
4. ERROR at line 1:
   
5. ORA-15039: diskgroup not dropped
   
6. ORA-15027: active use of diskgroup "HIGHDATA" precludes its dismount
   

复制代码

---------------------
非下线，非只读，alter database open时要检查：

1. ORACLE_SID = [orcl] ? +ASM
   
2. The Oracle base remains unchanged with value /u01/app/oracle
   
3. [oracle@station80 ~]$ sqlplus /nolog
   
4. 
   
5. SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 10 20:54:42 2019
   
6. 
   
7. Copyright (c) 1982, 2013, Oracle.  All rights reserved.
   
8. 
   
9. SQL> conn / as sysasm
   
10. Connected.
    
11. SQL> drop diskgroup highdata  ;
    
12. drop diskgroup highdata
    
13. *
    
14. ERROR at line 1:
    
15. ORA-15039: diskgroup not dropped
    
16. ORA-15053: diskgroup "HIGHDATA" contains existing files
    
17. 
    
18. 
    
19. SQL> drop diskgroup highdata including contents;
    
20. 
    
21. Diskgroup dropped.
    
22. 
    
23. SQL> exit
    
24. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    
25. With the Automatic Storage Management option
    
26. [oracle@station80 ~]$ . oraenv
    
27. ORACLE_SID = [+ASM] ? orcl
    
28. The Oracle base remains unchanged with value /u01/app/oracle
    
29. [oracle@station80 ~]$ sqlplus /nolog
    
30. 
    
31. SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 10 20:55:23 2019
    
32. 
    
33. Copyright (c) 1982, 2013, Oracle.  All rights reserved.
    
34. 
    
35. SQL> conn / as sysdba
    
36. Connected.
    
37. SQL> alter database open ;
    
38. alter database open
    
39. *
    
40. ERROR at line 1:
    
41. ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
    
42. ORA-01110: data file 8: '+HIGHDATA/orcl/datafile/tbs3.dbf'
    
43. 
    
44. 
    
45. SQL> alter database datafile 8 offline ;
    
46. 
    
47. Database altered.
    
48. 
    
49. SQL> alter database open ;
    
50. 
    
51. Database altered.
    
52. 
    
53. SQL> archive log list
    
54. Database log mode              Archive Mode
    
55. Automatic archival             Enabled
    
56. Archive destination            USE_DB_RECOVERY_FILE_DEST
    
57. Oldest online log sequence     8
    
58. Next log sequence to archive   10
    
59. Current log sequence           10
    
60. SQL> alter database create datafile 8 as '+data';
    
61. 
    
62. Database altered.
    
63. 
    
64. SQL> alter database datafile 8 online ;
    
65. alter database datafile 8 online
    
66. *
    
67. ERROR at line 1:
    
68. ORA-01113: file 8 needs media recovery
    
69. ORA-01110: data file 8: '+DATA/orcl/datafile/tbs3.269.1010610067'
    
70. 
    
71. 
    
72. SQL> recover datafile 8 ;
    
73. ORA-00279: change 1770013 generated at 06/10/2019 20:32:16 needed for thread 1
    
74. ORA-00289: suggestion :
    
75. +FRA/orcl/archivelog/2019_06_10/thread_1_seq_3.269.1010609115
    
76. ORA-00280: change 1770013 for thread 1 is in sequence #3
    
77. 
    
78. 
    
79. Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
    
80. 
    
81. ORA-00279: change 1771435 generated at 06/10/2019 20:45:14 needed for thread 1
    
82. ORA-00289: suggestion :
    
83. +FRA/orcl/archivelog/2019_06_10/thread_1_seq_4.270.1010609117
    
84. ORA-00280: change 1771435 for thread 1 is in sequence #4
    
85. 
    
86. 
    
87. Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
    
88. 
    
89. ORA-00279: change 1771445 generated at 06/10/2019 20:45:14 needed for thread 1
    
90. ORA-00289: suggestion :
    
91. +FRA/orcl/archivelog/2019_06_10/thread_1_seq_5.271.1010609117
    
92. ORA-00280: change 1771445 for thread 1 is in sequence #5
    
93. 
    
94. 
    
95. Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
    
96. auto
    
97. ORA-00279: change 1771459 generated at 06/10/2019 20:45:17 needed for thread 1
    
98. ORA-00289: suggestion :
    
99. +FRA/orcl/archivelog/2019_06_10/thread_1_seq_6.272.1010609119
    
100. ORA-00280: change 1771459 for thread 1 is in sequence #6
     
101. 
     
102. 
     
103. ORA-00279: change 1771462 generated at 06/10/2019 20:45:17 needed for thread 1
     
104. ORA-00289: suggestion :
     
105. +FRA/orcl/archivelog/2019_06_10/thread_1_seq_7.273.1010609121
     
106. ORA-00280: change 1771462 for thread 1 is in sequence #7
     
107. 
     
108. 
     
109. Log applied.
     
110. Media recovery complete.
     
111. SQL> alter database datafile 8 online;
     
112. 
     
113. Database altered.
     
114. 
     
115. SQL> alter tablespace tbs3 online;
     
116. 
     
117. Tablespace altered.
     
118. 
     
119. SQL>
     

复制代码

添加磁盘到磁盘组：

1. ALTER DISKGROUP DATA ADD FAILGROUP FG2 DISK 'ORCL:ASMLOOP2' SIZE 7000 M FAILGROUP FG1 DISK 'ORCL:ASMLOOP1' SIZE 7000 M REBALANCE POWER 11
   

复制代码