FAQ
When creating a controlfile from a trace file, is this wording simply
removed or altered:

RECOVER DATABASE

ALTER SYSTEM ARCHIVE LOG ALL;

I have changed the other info to read
�CREATE CONTROLFILE REUSE DATABASE �SID_NAME� RESETLOGS NOARCHIVELOG�

At the very bottom of the script I have changed it to read
�ALTER DATABASE OPEN RESETLOGS�

I am copying a database to a new server and all of the 200+ files will have
a new directory structure (but will keep the same SID) so that is why I am
creating a new controlfile.

Don�t just search. Find. Check out the new MSN Search!
http://search.msn.click-url.com/go/onm00200636ave/direct/01/

Search Discussions

  • Goulet, Dick at Oct 4, 2005 at 8:47 am
    Depends. Was the database shutdown cleanly before you rebuilt the
    control file? If not then you need it.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of J. Dex
    Sent: Tuesday, October 04, 2005 9:39 AM
    To: oracle-l_at_freelists.org
    Subject: Creating a controlfile - language needed?

    When creating a controlfile from a trace file, is this wording simply
    removed or altered:

    RECOVER DATABASE

    ALTER SYSTEM ARCHIVE LOG ALL;

    I have changed the other info to read
    "CREATE CONTROLFILE REUSE DATABASE "SID_NAME" RESETLOGS NOARCHIVELOG"

    At the very bottom of the script I have changed it to read

    "ALTER DATABASE OPEN RESETLOGS"

    I am copying a database to a new server and all of the 200+ files will
    have
    a new directory structure (but will keep the same SID) so that is why I
    am
    creating a new controlfile.

    Don't just search. Find. Check out the new MSN Search!
    http://search.msn.click-url.com/go/onm00200636ave/direct/01/

    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Mark Bole at Oct 4, 2005 at 10:26 am

    Goulet, Dick wrote:

    Depends. Was the database shutdown cleanly before you rebuilt the
    control file? If not then you need it.
    Not exactly. If instance recovery is required, it is the online redo
    log files that you will need, not the original control file.
    I am copying a database to a new server and all of the 200+ files will
    have
    a new directory structure (but will keep the same SID) so that is why I
    am
    creating a new controlfile.
    Why create a new controlfile and open resetlogs when you don't need to?
    Shutdown the database, copy all the files to the new locations,
    startup nomount, then use a series of ALTER DATABASE RENAME FILE
    commands to update the location of datafiles and online log files. Then
    create/drop tempfiles as appropriate. You still have to do the same
    amount of text file editing in either case.

    In fact, you might even get away with simply using DB_FILE_NAME_CONVERT
    and LOG_FILE_NAME_CONVERT parameters.
  • Dennis Williams at Oct 4, 2005 at 11:21 am
    There are several alternatives for moving databases. The method chosen
    will depend on several factors:

    Amount of downtime allowed.
    Size of the database.
    Whether the two servers are compatible.
    Level of DBA experience. Stick to your comfort zone.
    Don't forget to perform the chosen method in test first.
    In my opinion, the simplest method is to create a controlfile to
    trace, edit it, etc. This is good if you are inexperienced at these
    methods and there is plenty of downtime available. There are several
    documents on the web that describe this process, for example:
    http://www.jlcomp.demon.co.uk/faq/db_move.html.

    Dennis Williams
    On 10/4/05, Mark Bole wrote:
    Goulet, Dick wrote:
    Depends. Was the database shutdown cleanly before you rebuilt the
    control file? If not then you need it.
    Not exactly. If instance recovery is required, it is the online redo
    log files that you will need, not the original control file.
    I am copying a database to a new server and all of the 200+ files will
    have
    a new directory structure (but will keep the same SID) so that is why I
    am
    creating a new controlfile.
    Why create a new controlfile and open resetlogs when you don't need to?
    Shutdown the database, copy all the files to the new locations,
    startup nomount, then use a series of ALTER DATABASE RENAME FILE
    commands to update the location of datafiles and online log files. Then
    create/drop tempfiles as appropriate. You still have to do the same
    amount of text file editing in either case.

    In fact, you might even get away with simply using DB_FILE_NAME_CONVERT
    and LOG_FILE_NAME_CONVERT parameters.

    --
    Mark Bole
    http://www.bincomputing.com



    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Goulet, Dick at Oct 4, 2005 at 10:31 am
    Your going to need the online redo logs one way or the other. But if
    the database is cleanly shutdown then you don't need to recover that
    database after building the new control file. The key is how the
    database gets shutdown. A shutdown immediate or normal does not require
    instance recovery. A shutdown abort does. And building a new control
    file of a hot backup copy of the database will require recovery period.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Mark Bole
    Sent: Tuesday, October 04, 2005 11:23 AM
    To: oracle-l_at_freelists.org
    Subject: Re: Creating a controlfile - language needed?

    Goulet, Dick wrote:
    Depends. Was the database shutdown cleanly before you rebuilt the
    control file? If not then you need it.
    Not exactly. If instance recovery is required, it is the online redo
    log files that you will need, not the original control file.
    I am copying a database to a new server and all of the 200+ files will
    have
    a new directory structure (but will keep the same SID) so that is why I
    am
    creating a new controlfile.
    Why create a new controlfile and open resetlogs when you don't need to?
    Shutdown the database, copy all the files to the new locations,
    startup nomount, then use a series of ALTER DATABASE RENAME FILE
    commands to update the location of datafiles and online log files. Then

    create/drop tempfiles as appropriate. You still have to do the same
    amount of text file editing in either case.

    In fact, you might even get away with simply using DB_FILE_NAME_CONVERT
    and LOG_FILE_NAME_CONVERT parameters.
  • Allen, Brandon at Oct 4, 2005 at 11:36 am
    From the referenced web page: "Do not shutdown abort or immediate as these will require instance recovery."
    Am I missing something, or is that statement incorrect? I've never heard of shutdown immediate requiring instance recovery.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Dennis Williams
    Sent: Tuesday, October 04, 2005 9:02 AM

    There are several documents on the web that describe this process, for example:
    http://www.jlcomp.demon.co.uk/faq/db_move.html.

    Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

    --
    http://www.freelists.org/webpage/oracle-l
  • Oracle-l-bounce_at_freelists.org at Oct 4, 2005 at 12:05 pm
    Brandon,

    Shutdown immediate is a controlled crash.

    Do a shutdown immediate. Then a startup. Review the alert.log file.
    Your will see a recovery takes place.

    Compare this with a shutdown and a shutdown abort.

    Simple test to see how it works.

    Tom

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Allen, Brandon
    Sent: Tuesday, October 04, 2005 12:35 PM
    To: oracledba.williams_at_gmail.com; makbo_at_pacbell.net
    Cc: oracle-l_at_freelists.org
    Subject: RE: Creating a controlfile - language needed?
    From the referenced web page: "Do not shutdown abort or immediate as
    these will require instance recovery."

    Am I missing something, or is that statement incorrect? I've never
    heard of shutdown immediate requiring instance recovery.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Dennis Williams
    Sent: Tuesday, October 04, 2005 9:02 AM

    There are several documents on the web that describe this process, for
    example:
    http://www.jlcomp.demon.co.uk/faq/db_move.html.

    Privileged/Confidential Information may be contained in this message or
    attachments hereto. Please advise immediately if you or your employer do
    not consent to Internet email for messages of this kind. Opinions,
    conclusions and other information in this message that do not relate to
    the official business of this company shall be understood as neither
    given nor endorsed by it.

    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Oracle-l-bounce_at_freelists.org at Oct 4, 2005 at 12:14 pm
    I beg your pardon. The following is from my alert log and is was a
    "shutdown immediate":

    Thu Sep 29 12:01:00 2005
    Completed: ALTER DATABASE CLOSE NORMAL
    Thu Sep 29 12:01:00 2005
    ALTER DATABASE DISMOUNT

    Completed: ALTER DATABASE DISMOUNT
    ARCH: Archiving is disabled
    Shutting down archive processes
    Archiving is disabled
    Archive process shutdown avoided: 0 active
    ARCH: Archiving is disabled
    Shutting down archive processes
    Archiving is disabled
    Archive process shutdown avoided: 0 active
    Thu Sep 29 12:15:41 2005
    Starting ORACLE instance (normal)
    LICENSE_MAX_SESSION = 0

    LICENSE_SESSIONS_WARNING = 0

    SCN scheme 1
    LICENSE_MAX_USERS = 0

    SYS auditing is disabled
    Starting up ORACLE RDBMS Version: 9.2.0.5.0.
    System parameters with non-default values:

    processes = 800
    timed_statistics = TRUE
    resource_limit = TRUE
    event = 10499 trace name context forever, level 10
    shared_pool_size = 335544320
    sga_max_size = 1058305200
    large_pool_size = 33554432
    java_pool_size = 67108864
    nls_date_format = DD-MON-RRRR
    dbwr_io_slaves = 6
    control_files = /ora1/92/dbs/ctrl12.ctl,
    /ora1/system/ctrl22.ctl, /ora1/archive/ctrl32.ctl
    db_block_size = 8192
    db_cache_size = 419430400
    compatible = 9.2.0.0
    log_archive_start = TRUE
    log_archive_dest =
    log_archive_dest_1 = LOCATION=/ora1/archive/oldarch/arch
    log_archive_dest_state_1 = ENABLE
    log_archive_max_processes= 2
    log_archive_format = log2_%s.arc
    log_buffer = 327680
    db_files = 500

    db_file_multiblock_read_count= 32

    fast_start_mttr_target = 300
    dml_locks = 500
    undo_management = AUTO
    undo_tablespace = UNDOTBS
    undo_retention = 21600
    remote_login_passwordfile= EXCLUSIVE
    db_domain = world
    global_names = FALSE
    instance_name = 02
    service_names = linus2.world
    dispatchers = (protocol=tcp)(disptachers=2)
    shared_servers = 5
    mts_servers = 5
    local_listener = linus2
    utl_file_dir = /ora1/local, /ora1/local/users
    job_queue_processes = 2
    cursor_sharing = SIMILAR
    background_dump_dest = /ora1/92/rdbms/log
    user_dump_dest = /ora1/92/rdbms/users
    max_dump_file_size = 10240
    core_dump_dest = /ora1/92/rdbms/core
    db_name = 02
    open_cursors = 500
    pga_aggregate_target = 67108864
    Deprecated system parameters with specified values:
    mts_servers

    End of deprecated system parameter listing

    PMON started with pid=2
    DBW0 started with pid=3
    LGWR started with pid=4
    CKPT started with pid=5
    SMON started with pid=6
    RECO started with pid=7
    CJQ0 started with pid=8

    Thu Sep 29 12:15:49 2005
    starting up 5 shared server(s) ...
    starting up 1 dispatcher(s) for network address
    '(ADDRESS=(PARTIAL=YES)(PROTOCOL=tcp))'...
    ARCH: STARTING ARCH PROCESSES

    ARC0 started with pid=15
    ARC0: Archival started
    ARC1 started with pid=16
    Thu Sep 29 12:15:53 2005
    ARCH: STARTING ARCH PROCESSES COMPLETE

    Thu Sep 29 12:15:53 2005
    ARC1: Archival started
    ARC1: Thread not mounted
    Thu Sep 29 12:15:53 2005
    ARC0: Thread not mounted
    Thu Sep 29 12:15:54 2005
    ALTER DATABASE MOUNT

    Thu Sep 29 12:16:00 2005
    Successful mount of redo thread 1, with mount id 5766140
    Thu Sep 29 12:16:00 2005
    Database mounted in Exclusive Mode.
    Completed: ALTER DATABASE MOUNT
    Thu Sep 29 12:16:01 2005
    ALTER DATABASE OPEN

    Thu Sep 29 12:16:03 2005
    LGWR: Primary database is in CLUSTER CONSISTENT mode
    Thread 1 opened at log sequence 12582
    Current log# 6 seq# 12582 mem# 0: /ora1/redo/log602.dbf
    Successful open of redo thread 1
    Thu Sep 29 12:16:03 2005
    SMON: enabling cache recovery
    Thu Sep 29 12:16:04 2005
    Undo Segment 1 Onlined
    Undo Segment 2 Onlined
    Undo Segment 3 Onlined
    Undo Segment 4 Onlined
    Undo Segment 5 Onlined
    Undo Segment 6 Onlined
    Undo Segment 7 Onlined
    Undo Segment 8 Onlined
    Undo Segment 9 Onlined
    Undo Segment 10 Onlined
    Successfully onlined Undo Tablespace 1.
    Thu Sep 29 12:16:04 2005
    SMON: enabling tx recovery
    Thu Sep 29 12:16:04 2005
    Database Characterset is WE8ISO8859P1
    replication_dependency_tracking turned off (no async multimaster
    replication found)
    Completed: ALTER DATABASE OPEN

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org

    Sent: Tuesday, October 04, 2005 1:02 PM
    To: Brandon.Allen_at_OneNeck.com; oracledba.williams_at_gmail.com;
    makbo_at_pacbell.net
    Cc: oracle-l_at_freelists.org
    Subject: RE: Creating a controlfile - language needed?

    Brandon,

    Shutdown immediate is a controlled crash.

    Do a shutdown immediate. Then a startup. Review the alert.log file.
    Your will see a recovery takes place.

    Compare this with a shutdown and a shutdown abort.

    Simple test to see how it works.

    Tom

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Allen, Brandon
    Sent: Tuesday, October 04, 2005 12:35 PM
    To: oracledba.williams_at_gmail.com; makbo_at_pacbell.net
    Cc: oracle-l_at_freelists.org
    Subject: RE: Creating a controlfile - language needed?
    From the referenced web page: "Do not shutdown abort or immediate as
    these will require instance recovery."

    Am I missing something, or is that statement incorrect? I've never
    heard of shutdown immediate requiring instance recovery.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Dennis Williams
    Sent: Tuesday, October 04, 2005 9:02 AM

    There are several documents on the web that describe this process, for
    example:
    http://www.jlcomp.demon.co.uk/faq/db_move.html.

    Privileged/Confidential Information may be contained in this message or
    attachments hereto. Please advise immediately if you or your employer do
    not consent to Internet email for messages of this kind. Opinions,
    conclusions and other information in this message that do not relate to
    the official business of this company shall be understood as neither
    given nor endorsed by it.

    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Dennis Williams at Oct 4, 2005 at 12:25 pm
    Dick,
    I beg your pardon. The following is from my alert log and is was a
    "shutdown immediate":
    Jolly nice of you to do that for our discussion. Did your users mind?

    I think we should report the web page author to the Web Accuracy
    Police (WAP). I think the main point is that there are lots of web
    pages that show this simple technique for moving databases. A novice
    DBA should carefully examine several web pages, and understand the
    points of each. A list like Oracle-L can clarify any lingering
    uncertainties. And TEST first.

    Dennis Williams
  • Allen, Brandon at Oct 4, 2005 at 12:18 pm
    Sorry, Tom, but I believe you are confused. It's clear in the documentation and I've looked at enough alert logs to know it is true:
    "Shutting Down with the IMMEDIATE Option
    . . .
    The next startup of the database will not require any instance recovery procedures."

    "Shutting Down with the ABORT Option
    . . .
    The next startup of the database will require instance recovery procedures."

    Regards,
    Brandon

    -----Original Message-----
    From: Mercadante, Thomas F (LABOR)

    Sent: Tuesday, October 04, 2005 10:02 AM

    Brandon,

    Shutdown immediate is a controlled crash.

    Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

    --
    http://www.freelists.org/webpage/oracle-l
  • Mercadante, Thomas F (LABOR) at Oct 4, 2005 at 1:26 pm
    Old memories coming back at me. I could have swore I saw it working
    differently "back in the day".

    Oh Well. Back to the mines.

    -----Original Message-----
    From: Allen, Brandon
    Sent: Tuesday, October 04, 2005 1:17 PM
    To: Mercadante, Thomas F (LABOR); oracledba.williams_at_gmail.com;
    makbo_at_pacbell.net
    Cc: oracle-l_at_freelists.org
    Subject: RE: Creating a controlfile - language needed?

    Sorry, Tom, but I believe you are confused. It's clear in the
    documentation and I've looked at enough alert logs to know it is true:

    From
    http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/star
    t.htm#6370:

    "Shutting Down with the IMMEDIATE Option
    . . .
    The next startup of the database will not require any instance recovery
    procedures."

    "Shutting Down with the ABORT Option
    . . .
    The next startup of the database will require instance recovery
    procedures."

    Regards,
    Brandon

    -----Original Message-----
    From: Mercadante, Thomas F (LABOR)

    Sent: Tuesday, October 04, 2005 10:02 AM

    Brandon,

    Shutdown immediate is a controlled crash.

    Privileged/Confidential Information may be contained in this message or
    attachments hereto. Please advise immediately if you or your employer do
    not consent to Internet email for messages of this kind. Opinions,
    conclusions and other information in this message that do not relate to
    the official business of this company shall be understood as neither
    given nor endorsed by it.

    --
    http://www.freelists.org/webpage/oracle-l
  • Goulet, Dick at Oct 4, 2005 at 3:46 pm
    If you noted that was done last Thursday, which is a scheduled event
    here for PeopleSoft code moves. Don't ask why as that takes up way to
    much bandwidth.

    -----Original Message-----
    From: Dennis Williams
    Sent: Tuesday, October 04, 2005 1:22 PM
    To: Goulet, Dick
    Cc: Thomas.Mercadante_at_labor.state.ny.us; Brandon.Allen_at_oneneck.com;
    makbo_at_pacbell.net; oracle-l_at_freelists.org
    Subject: Re: Creating a controlfile - language needed?

    Dick,
    I beg your pardon. The following is from my alert log and is was a
    "shutdown immediate":
    Jolly nice of you to do that for our discussion. Did your users mind?

    I think we should report the web page author to the Web Accuracy
    Police (WAP). I think the main point is that there are lots of web
    pages that show this simple technique for moving databases. A novice
    DBA should carefully examine several web pages, and understand the
    points of each. A list like Oracle-L can clarify any lingering
    uncertainties. And TEST first.

    Dennis Williams

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedOct 4, '05 at 8:41a
activeOct 4, '05 at 3:46p
posts12
users7
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase