What is the significance of a church being "consecrated"? Related to this question. I've split it into two questions because I didn't want to ask too much in one go.

My local parish church has a sign out the front which says "Australia's oldest consecrated church". That got me wondering, "what's consecration all about?"

If a church is consecrated, does that mean that it has been sanctified? ie has it become a holy site? Does it mean that God dwells inside the church in some special sense (Aside from the Eucharistic presence in the tabernacle)? Perhaps it is invalid or illicit to celebrate mass in a church that has not been consecrated, and therefore the consecration is essential? Perhaps the consecration is optional and is more like a "good luck blessing" which is just a nice-to-have. Does anything about the building actually change (on a spiritual level or otherwise)?

My main question: What is the purpose and significance of consecrating a Catholic church?
Consecration establishes a valid place of Catholic divine worship as confirmed by the local ordinary (Bishop)


  Can.  1214 By the term church is understood a sacred building
  designated for divine worship to which the faithful have the right of
  entry for the exercise, especially the public exercise, of divine
  worship.  
  
  Can.  1215 §1. No church is to be built without the express written
  consent of the diocesan bishop.  


From the Catholic Encyclopedia  


  Consecration, in general, is an act by which a thing is separated from
  a common and profane to a sacred use, or by which a person or thing is
  dedicated to the service and worship of God by prayers, rites, and
  ceremonies.  


Definitions  


  CONSECRATED GROUND Any place or space that has been liturgically
  blessed, or where some sacred object such as a church is built or
  has stood. {snipped burial grounds explanation}.   


Significance (truth in advertising)

The laws, rites, and processes that go into consecration1 of a place of divine worship ensure that when you arrive, for example, at a place called St Paul's Catholic Church, you are in a place that conforms to the standards for valid divine worship, to include the sacraments.   

The Code of Canon Law 1214 through 1222 addresses the requirements.  


  Can.  1217 §1. After construction has been completed properly, a new
  church is to be dedicated or at least blessed as soon as possible; the
  laws of the sacred liturgy are to be observed.
  
  §2. Churches, especially cathedrals and parish churches, are to be
  dedicated by the solemn rite.   


The Roman Ritual (Sancta Missa, 1964) describes in detail the rite.  (See section 2 at the link, there is a detailed liturgical rite in establishing a church).  See also the dedication of a church and altar.

Addressing further elements of your question.


If a church is consecrated, does that mean that it has been sanctified?  (i.e. has it become a holy site?)     

If you were using a generic definition for sanctification, yes.  


  The generic meaning of sanctification is "the state of proper functioning." To sanctify someone or something is to set that person or thing apart for the use intended by its designer.  Key Concepts.  God's usual modus operandi is to sanctify common things for his redemptive purposes, rather than to employ perfect heavenly things   


Using a Catholic definition, sanctification normally applies to people, so it applies to the Church in the sense that the faithful are the mystical body of Christ.   


  From the Catholic Encyclopedia entry on sanctity
   The object of the Passion was the redemption and sanctification of the
  Church: 
  
  
  "Christ also loved the church, and delivered Himself up for
  it: that he might sanctify it, cleansing it by the laver of water in
  the word of life" (Ephesians 5:25, 26).
  
  
  Again the Church is the body of Christ. He is the head of the mystical body: and supernatural life — the life of Christ Himself — is communicated through the
  sacraments to all His members.   


For another Catholic definition ...   


  SANCTIFICATION
  Being made holy. The first sanctification takes place at baptism, by
  which the love of God is infused by the Holy Spirit (Romans 5:5).
  Newly baptized persons are holy because the Holy Trinity begins to
  dwell in their souls and they are pleasing to God. The second
  sanctification is a lifelong process in which a person alredy in the
  state of grace grows in the possession of grace and in likeness to God
  by faithfully corresponding with divine inspirations. The third
  sanctification takes place when a person enters heaven and becomes
  totally and irrevocably united with God in the beatific vision. (Etym.
  Latin sanctificare, to make holy.)  


I'd recommend against using the term "sanctification" as a synonym for consecration in this case.  The setting aside for the special purpose of divine worship and administering the sacraments is the purpose of the blessings, etc.   It is worth noting that of the 38 times that the term sanctification comes up in the Catechism, in all 38 it applies to people not things.  
Does it mean that God dwells inside the church in some special sense
(Aside from the Eucharistic presence in the tabernacle)?  

Catholic churches are not a "put God in a box" places.  They are places where people (as the Catholic Church sees it, God's people, DE POPULO DEI) come for divine worship of God and to receive the sacraments.  The tabernacle in a Catholic church is a special place to store the consecrated host.          
Perhaps it is invalid or illicit to celebrate mass in a church that
has not been consecrated, and therefore the consecration is essential?

Per the Code of Canon Law cited above, it would be illicit to assert that one is at "St Paul's Catholic Church" unless the process described above had been properly completed.   


  Perhaps the consecration is optional and is more like a "good luck blessing" which is just a nice-to-have.  


No. See all of the above.  It is not "just nice to have," it's Canon Law, and a liturgical blessing is not "a good luck blessing."     
Does anything about the building actually change (on a spiritual
level or otherwise)?  

It becomes an officially sanctioned place where Catholic divine worship and liturgy are done, which includes administration of the sacraments.    




1 The current Code of Canon law uses the term dedication, but the process takes on the character of consecration -- equivalent terms.