ORACLE_SID = [root] ? +ASM1
The Oracle base has been set to /u01/app/oracle
[root@oraclelab1 ~]# crsctl stop crs
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'oraclelab1'
CRS-2673: Attempting to stop 'ora.crsd' on 'oraclelab1'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on server 'oraclelab1'
CRS-2673: Attempting to stop 'ora.chad' on 'oraclelab1'
CRS-2673: Attempting to stop 'ora.qosmserver' on 'oraclelab1'
CRS-2673: Attempting to stop 'ora.devdb.db' on 'oraclelab1'
CRS-2677: Stop of 'ora.chad' on 'oraclelab1' succeeded
CRS-2677: Stop of 'ora.devdb.db' on 'oraclelab1' succeeded
CRS-33673: Attempting to stop resource group 'ora.asmgroup' on server 'oraclelab1'
CRS-2673: Attempting to stop 'ora.OCR.dg' on 'oraclelab1'
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'oraclelab1'
CRS-2673: Attempting to stop 'ora.RECO.dg' on 'oraclelab1'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'oraclelab1'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN2.lsnr' on 'oraclelab1'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN3.lsnr' on 'oraclelab1'
CRS-2673: Attempting to stop 'ora.cvu' on 'oraclelab1'
CRS-2677: Stop of 'ora.RECO.dg' on 'oraclelab1' succeeded
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'oraclelab1' succeeded
CRS-2677: Stop of 'ora.OCR.dg' on 'oraclelab1' succeeded
CRS-2673: Attempting to stop 'ora.oraclelab1.vip' on 'oraclelab1'
CRS-2677: Stop of 'ora.DATA.dg' on 'oraclelab1' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'oraclelab1'
CRS-2677: Stop of 'ora.LISTENER_SCAN3.lsnr' on 'oraclelab1' succeeded
CRS-2673: Attempting to stop 'ora.scan3.vip' on 'oraclelab1'
CRS-2677: Stop of 'ora.LISTENER_SCAN2.lsnr' on 'oraclelab1' succeeded
CRS-2673: Attempting to stop 'ora.scan2.vip' on 'oraclelab1'
CRS-2677: Stop of 'ora.oraclelab1.vip' on 'oraclelab1' succeeded
CRS-2677: Stop of 'ora.asm' on 'oraclelab1' succeeded
CRS-2673: Attempting to stop 'ora.ASMNET1LSNR_ASM.lsnr' on 'oraclelab1'
CRS-2677: Stop of 'ora.scan3.vip' on 'oraclelab1' succeeded
CRS-2677: Stop of 'ora.scan2.vip' on 'oraclelab1' succeeded
CRS-2677: Stop of 'ora.cvu' on 'oraclelab1' succeeded
CRS-2677: Stop of 'ora.qosmserver' on 'oraclelab1' succeeded
CRS-2677: Stop of 'ora.ASMNET1LSNR_ASM.lsnr' on 'oraclelab1' succeeded
CRS-2673: Attempting to stop 'ora.asmnet1.asmnetwork' on 'oraclelab1'
CRS-2677: Stop of 'ora.asmnet1.asmnetwork' on 'oraclelab1' succeeded
CRS-33677: Stop of resource group 'ora.asmgroup' on server 'oraclelab1' succeeded.
CRS-2672: Attempting to start 'ora.qosmserver' on 'oraclelab2'
CRS-2672: Attempting to start 'ora.scan2.vip' on 'oraclelab2'
CRS-2672: Attempting to start 'ora.scan3.vip' on 'oraclelab2'
CRS-2672: Attempting to start 'ora.cvu' on 'oraclelab2'
CRS-2672: Attempting to start 'ora.oraclelab1.vip' on 'oraclelab2'
CRS-2676: Start of 'ora.scan2.vip' on 'oraclelab2' succeeded
CRS-2676: Start of 'ora.scan3.vip' on 'oraclelab2' succeeded
CRS-2672: Attempting to start 'ora.LISTENER_SCAN2.lsnr' on 'oraclelab2'
CRS-2672: Attempting to start 'ora.LISTENER_SCAN3.lsnr' on 'oraclelab2'
CRS-2676: Start of 'ora.oraclelab1.vip' on 'oraclelab2' succeeded
CRS-2676: Start of 'ora.cvu' on 'oraclelab2' succeeded
CRS-2676: Start of 'ora.LISTENER_SCAN3.lsnr' on 'oraclelab2' succeeded
CRS-2676: Start of 'ora.LISTENER_SCAN2.lsnr' on 'oraclelab2' succeeded
CRS-2676: Start of 'ora.qosmserver' on 'oraclelab2' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'oraclelab1'
CRS-2677: Stop of 'ora.ons' on 'oraclelab1' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'oraclelab1'
CRS-2677: Stop of 'ora.net1.network' on 'oraclelab1' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'oraclelab1' has completed
CRS-2677: Stop of 'ora.crsd' on 'oraclelab1' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'oraclelab1'
CRS-2673: Attempting to stop 'ora.crf' on 'oraclelab1'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'oraclelab1'
CRS-2677: Stop of 'ora.mdnsd' on 'oraclelab1' succeeded
CRS-2677: Stop of 'ora.crf' on 'oraclelab1' succeeded
CRS-2677: Stop of 'ora.asm' on 'oraclelab1' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'oraclelab1'
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'oraclelab1' succeeded
CRS-2673: Attempting to stop 'ora.ctssd' on 'oraclelab1'
CRS-2673: Attempting to stop 'ora.evmd' on 'oraclelab1'
CRS-2677: Stop of 'ora.ctssd' on 'oraclelab1' succeeded
CRS-2677: Stop of 'ora.evmd' on 'oraclelab1' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'oraclelab1'
CRS-2677: Stop of 'ora.cssd' on 'oraclelab1' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'oraclelab1'
CRS-2673: Attempting to stop 'ora.gpnpd' on 'oraclelab1'
CRS-2677: Stop of 'ora.gpnpd' on 'oraclelab1' succeeded
CRS-2677: Stop of 'ora.gipcd' on 'oraclelab1' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'oraclelab1' has completed
CRS-4133: Oracle High Availability Services has been stopped.
[root@oraclelab1 ~]#
[root@oraclelab1 ~]#
[root@oraclelab1 ~]# crsctl stat res -t
CRS-4535: Cannot communicate with Cluster Ready Services
CRS-4000: Command Status failed, or completed with errors.
[root@oraclelab1 ~]#
[root@oraclelab1 ~]# ps -ef|grep pmon
root 28442 8086 0 00:18 pts/3 00:00:00 grep --color=auto pmon
[root@oraclelab1 ~]#
[root@oraclelab1 ~]#
Concurrent DML & DDL Tips
ReplyDeleteOracle Database Tips by Donald BurlesonDecember 9, 2015
Question: I need to know if I can do table DDL (alter table statements) while DML (inserts, updates deletes) are running? In some cases I believe that the DML must commit before the DDL happens, but in other cases, DML can be in progress when the DML occurs.
Are DDL statements blocked when they refer to an active object accessed from a DML or not?
Answer: Great question! It depends on the type of DDL. All drop table/index DDL operations must wait for DML to complete Delete DDL is not blocked, per se, but there must be no locks on the table at DDL drop table or drop user time.
Let's assume that a task in an a loop, deleting rows from a table:
for i = 1 to EOF loop
delete from mytab where var=:myvar(i)
end loop;
Until this delete DML task ends (or COMMIT's), a DDL drop table would have to wait.
However, Would an alter table DDL to dynamically add a column would have to wait for the DML to complete?
I once did an "alter table" DDL (I add a freelist) to a table while it was accepting DML. It worked at the time, but to the table became corrupted a few minutes later!
The point is, just because you CAN do DDL during DML, this does not mean that you should!
Of course, in the real-world this is a moot question, since dbms_redefinition would be used to make DDL changes when DML is running:
Please read: How to do concurrent DML/DDL with dbms_refdefinition.
As you see, with dbms_redefinition the DML in enqueued in a snapshot object until the DDL has completed.
Quoting from the SQL reference of the Oracle 11g documentation, we see that DDL and DCL are considered discrete transactions just like DML, and they will COMMIT upon completion::
"If a user issues a DDL or DCL, the transaction he has in progress (if any) will be committed. This is because the DDL and DCL commands are themselves transactions. The [DDL] adjusts the data structures by performing DML commands against the tables of the data dictionary, and these DML commands must be terminated with a COMMIT.
A DROP is therefore absolutely nonreversible. But there are some restrictions: if any session (even your own) has a transaction in progress that includes a row in the table, then the DROP will fail."