DB funds used to be available from many large organisations as well as the various public sector organisations. Once DC (defined contribution - which most funds now are) funds started to be available, a lot of DB funds were closed to new members, because they were costing much more as (if you stayed until you retired) they tend to give people more money. The public sector organisations seem to be about the last places that still have any DB funds available, but a lot of them have also been closed.
Again, it depends on the fund. I have been in three DB funds during my working life. The first two didn’t allow you to add anything to them, so you would need to have a different fund for any extra contributions. If you left these before retirement age, you would be removed from the fund and given a superannuation cash out, to be transferred into another fund. This was about the same amount as I’d put in, with very little interest. I would have been much better off in a DC fund.
The third fund allowed extra contributions of two sorts - either as pre tax money that allowed you to multiply your pension benefits, or as post tax money that effectively went into a separate DC fund. The pretax contributions were very worthwhile but the post tax DC fund wasn’t worth it, so I put that money into a different fund. As I was closer to retirement age and I was retrenched (the fund had higher benefits for people who were retrenched), this was an excellent fund to be in.
Inheritance is definitely better for DC funds. However, the government gives the tax breaks to superannuation on the understanding that it’s going to be used during your lifetime and not inherited. The transfer balance cap is one way of them clamping down on superannuation being used for inheritance. I suspect that there will be future regulations to reduce inheritance even more.
All the DB funds I know of that still exist can be opted out of - and I think it might be contrary to the legislation if you couldn’t, since you are supposed to be able to chose your super fund. Whether a DB fund is worthwhile really depends on how old you are, and how long you expect to work there.