oracleoracle-11g-r2
Shutting down a database before doing an upgrade or a patch can be done several ways.
shutdown immediate;
or
shutdown abort;
startup restrict;
shutdown immediate;
or
shutdown abort;
startup restrict;
shutdown;
or
alter system checkpoint;
shutdown abort;
startup restrict;
shutdown immediate;
Of course there are other options as well. Which should be preferred and why?
You have to initiate the duplicate from the target database to be able to connect to the not mounted target database. I suggest you to downgrade the oracle version on the 64 bit machine. The easiest way to do this will be to install 11.1.0.6.0 version on the 64-bit server into a separate oracle home and perform the duplicate. You can upgrade the database after duplicate and switch it to the 11.1.0.7.0 home.
The error you are getting on the 32-bit database seems to be a connection problem to the database. The target instance is not mounted and you cannot connect to is via SQLnet.
When performing the duplicate form 64 bit system, please make sure that you are using username/password in the connection string to the target database and not the system authentication.
Best regards, Yuri
I use AQ for
By using a queue you can do these things:
Here is more information on how I use AQ between databases. I am not an expert and got most of the code from the Internet. Oracle documentation is lengthy but did not really help me.
First create the queue:
BEGIN
SYS.DBMS_AQADM.CREATE_QUEUE_TABLE
(
QUEUE_TABLE => 'QT_NEW_CASE'
,QUEUE_PAYLOAD_TYPE => 'FILE_ACTION'
,COMPATIBLE => '8.1'
,STORAGE_CLAUSE => '
TABLESPACE USERS
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)'
,SORT_LIST => 'ENQ_TIME'
,MULTIPLE_CONSUMERS => FALSE
,MESSAGE_GROUPING => 0
,SECURE => FALSE
);
End;
On the origin database:
CREATE OR REPLACE TYPE FILE_ACTION AS OBJECT
( ACTION VARCHAR2(20),
CASE_ID NUMBER(10),
OTHER VARCHAR2(20)
);
The Action is admittedly crude but versatile. The requirements were to funnel changes from multiple tables to another database where further processing was required without touching the application code. A typical call is to a package from a trigger, other procedure or job.
queue_util.add_file ('CLOSE', v_case_id,:NEW.ID);
Inside the package
PROCEDURE add_file_to_queue (action_in IN VARCHAR2,
d_case_id_in IN NUMBER,
d_other_in IN VARCHAR2:= NULL)
IS
/******************************************************************************
PURPOSE: when there is a change to a file (create, closed or reopen) add the change to the queue of changes
******************************************************************************/
queue_options SYS.DBMS_AQ.enqueue_options_t;
message_properties SYS.DBMS_AQ.message_properties_t;
message_id RAW (16);
my_message file_action;
err_text VARCHAR2 (2000);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
my_message := file_action (action_in, d_case_id_in, d_other_in);
DBMS_AQ.enqueue (queue_name => 'NEW_CASE_QUEUE',
enqueue_options => queue_options,
message_properties => message_properties,
payload => my_message,
msgid => message_id);
IF g_debugging
THEN
;
--insert debugging info if g_debugging is true
END IF;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
err_text := SQLERRM;
--logging error to another table
WHEN OTHERS
THEN
err_text := SQLERRM;
--more logging
END add_file_to_queue;
--and popping messages off the queue
PROCEDURE send_from queue (case_id_in IN NUMBER := NULL)
IS
/******************************************************************************
PURPOSE:get the list of file changes and send them out
*****************************************************************************/
queue_options DBMS_AQ.dequeue_options_t;
message_properties DBMS_AQ.message_properties_t;
message_id file_action;
v_file VARCHAR2 (20);
v_case_id NUMBER (10);
v_filename VARCHAR2 (500);
v_action VARCHAR2 (20);
v_other VARCHAR2 (20);
v_err_id INTEGER;
bad_data_ex EXCEPTION;
v_err_text VARCHAR2 (50);
TYPE cases_cur IS REF CURSOR;
new_cases cases_cur;
BEGIN
IF case_id_in IS NULL
THEN
OPEN new_cases FOR
SELECT qt.msgid
FROM cqt_new_case qt
ORDER BY qt.enq_time;
ELSE
OPEN new_cases FOR
SELECT qt.msgid
FROM qt_new_case qt
WHERE qt.user_data.case_id = case_id_in
ORDER BY qt.enq_time;
END IF;
--should have added a check here to make sure
--the other database is up and running
LOOP
BEGIN
FETCH new_cases INTO message_id;
--reinitialize values to null
v_case_id := NULL;
v_filename := NULL;
v_file := NULL;
v_action := NULL;
v_other := NULL;
--to try and clear all locks
COMMIT;
EXIT WHEN new_cases%NOTFOUND;
IF case_id_in IS NOT NULL
THEN
queue_options.deq_condition :=
'tab.user_data.case_id = ' || case_id_in;
END IF;
DBMS_AQ.dequeue (queue_name => 'NEW_CASE_QUEUE',
dequeue_options => queue_options,
message_properties => message_properties,
payload => my_message,
msgid => message_id);
v_case_id := my_message.case_id;
v_action := my_message.action;
v_other := my_message.other;
IF v_case_id IS NOT NULL AND v_case_id > 0
THEN
IF g_debugging
THEN
;
--insert your debugging information
END IF;
--continues on with lengthy data transforms
--for actions like NEW, CLOSE, REOPEN
END SEND_FROM_QUEUE;
I would certainly write this differently today but it works.... The key problem with AQ for me is that I have never gotten queue to queue messaging going between different databases. This was described as a primary value for it. Yet I still like having the certainty that the initial transaction can complete without regard for the state of the destination database. If the message fails to be inserted when into the destination database an error is logged and an email is sent notifying the developer.
Best Answer
The aim when shutting down for maintenance (or cold backup) is that the database is left in a consistent state with no need for rollback/recovery on startup.
There are 3 SQL*Plus
shutdowncommands that achieve this in theory, all of which immediately prevent new sessions connecting to the instance:shutdown normalor justshutdown: waits for all sessions to disconnect. This mode is rarely used in practice because it relies on well-behaved clients not leaving connections open. This used to be the onlyshutdownmode that did not cancel running transactions.shutdown transactional: disconnects sessions once currently running transactions complete, preventing new transactions from beginning.shutdown immediate: disconnects all sessions immedately and rolls back interrupted transactions before shutting down. Note that the disconnections are immediate, but the shutdown may not be as any interrupted transactions may take time to roll back.The fourth mode of
shutdownisshutdown abort. This is like pulling the power cord - the instance stops now without any cleanup. You usually want to bring the database up again afterwards and shut down cleanly immediately afterwards as in your example. The concepts guide says:All the examples you give perform a checkpoint as part of the
shutdown [normal]orshutdown immediateso explicit checkpointing is presumably to reduce the time required for recovery.general advice:
shutdown normal.shutdown transactionalfor attended shutdown only, when you want to minimise cancelled transactions (attended only because this kind of shutdown is not guaranteed to shut the database down at all if timeouts are breached).shutdown immediatefor unattended shutdown or when you do not care about currently running transactions.shutdown abort(plus startup/shutdown) unless you have to - this was more common in much earlier versions of Oracle that it is today. In other situations (not patch/upgrade), if you have a need to minimise downtime this mode may be appropriate.