RDBMS -- Restricted Mode,Append&Undo,Nologging Indexes --snip

In this post , I will write about 3 things those I have seen in my clients environment.
The things I will mention are related and observed during database migration operations.
They are also related with each other, as commonly in logical migration, we start our database in restricted mode , load our data in append mode and create our indexes in nologging mode ..

Suppose we have downtime for the migation operation.. During downtime, we unload our data and load our data to the target environment. The first  important thing to consider here is that , we need to ensure that the data will not change in our running database during our unload operation.
To supply this , we can stop our listeners, restart our database, stop our job queue processes and etc.. to have a clean database session environment, but even with these actions, we  does not guarantee the up-to-date reliablity of our data.. Because, some applications still can use beq connections and update our database even if our listeners are not working..
That 's why, to guarantee that,  we use restricted mode for our database. That is , we stop our database and startup in restricted mode.. In restricted mode we unload our data and import it to the target..

But, in restricted mode,  there is one important thing that needs to be considered ,which is the fact that you can not connect to a instance in restricted mode remotely using a normal tns ...
If this is the case, I mean if you need to connect remotely to an instance in restricted mode, you need use (UR) statement in your tns entry..

For example

ERMANDB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.55)(PORT = 1521))
)
(CONNECT_DATA =
(UR=A)
(SERVICE_NAME = ERMAN)
(SERVER = DEDICATED)
)
)  

The (UR=A) is enhancement, since 10g .. Using service_name is preferred..
Without UR=A , you can end up with; TNS-12526, TNS-12527 or TNS-12528 even if you use SYS account.. Because when the instance in restricted mode, PMON updates the listeners with that information in startup and that's why, listeners block new connections from being established..

So by specifiying UR=A in your tns entry, you can use sql developer or a similar tool to connect your database remotely and you can make your data unloading/loading remotely even if your instance is in restricted mode.

Loading data in append mode is also one of the irrevocable approaches in data migrations.. As known, when inserting in append mode,the operation will be direct path load and the redo generation will be decreased.

It is decreased but not eliminated..

insert /*+append*/

Create table as select

....

When we use direct path load, we bypass the buffer cache, we gain some time, we load our data above the high water mark, so in such a scenario, rollback for an append operation means just deallocating the areas above the High water mark.  

So in a manner, we bypass undo generation for our table.That is, we dont need to keep the undo data, as we already have our action plan if the transaction needs recovery.(just deallocate what is allocated by our transaction) 

On the other hand; this is not acceptable for dictionary modications and the indexes on the table we are loading in to.. The indexes and dictionary modifications, caused by our appending load operations, will still need to have undo, as they can only be rolled back with the traditional way. 

So keep in mind that, undo will be generated  in append mode, but the amount of it will be lesser.

Lastly, I will mention about the nologging indexes. Creating indexes in nologging mode is another considerable approach in migration projects with limited downtime.. 

That's why we can choose to create indexes in nologging mode.. By choosing nologging index operations, we gain time as these index creations does not generate accessive redo data, because they are considered as unrecoverable operations. On the other hand; leaving those indexes in nologging mode is unnecessary. Because, when we the subject is an index, nologging attribute plays part on only for creation, altering and rebuilding operations. That is , only create, alter and rebuild operations on an index can be done in  nologging mode.. So after these operations , you better make your indexes logging again, as this is more logical and logging objects are mandatory in Production environments.. 

alter index index_name logging;