Joining DB to AOAG

Steps:


1. Disable backup jobs
2. Go to Primary node. Take a Full and TLog backup. Copy it over to the secondary.
3. Restore the full with Recovery option in secondary. Then change the owner to 'SA'. Restore the DB again. It will not change the owner any more. Another way to do it - login as 'SA' in the secondary node and then restore it.
4. Restore the TLog after that in No Recovery Mode.
5. Go to the Primary again. Right click on the AOAG name and select add databases in the AOAG.
6. In the AOAG config GUI select join only and proceed.
7. The new databases should be joined now.


Things to remember:


1. Check the owner in both nodes. it should be 'SA'. If not it needs to be changed. Another approach is to restore with command

Execute as login = 'sa';
GO
USE master;
GO -- First determine the number and names of the files in the backup. -- AdventureWorks2012_Backup is the name of the backup device. RESTORE FILELISTONLY FROM AdventureWorks2012_Backup; -- Restore the files for MyAdvWorks. RESTORE DATABASE MyAdvWorks FROM AdventureWorks2012_Backup WITH NORECOVERY, MOVE 'AdventureWorks2012_Data' TO 'D:\MyData\MyAdvWorks_Data.mdf', MOVE 'AdventureWorks2012_Log' TO 'F:\MyLog\MyAdvWorks_Log.ldf'; GO

Comments

Post a Comment

Popular posts from this blog

Log is not getting shipped in SQL Server Logshipping

Image
Well Problem could be of different types. But common issues are few. Cause Inside the LSRestore job history, you can find out two kind of messages  :  – Restore job skipping the logs on secondary server  Skipped log backup file. Secondary DB: ‘logshippingSecondary’, File: ‘\\myDB\logshipping\logshippingPrimary_20090808173803.trn’ – Backup log older is missing *** Error 4305: The file ‘\\myDB\logshipping\logshippingPrimary_20090808174201.trn’ is too recent to apply to the secondary database ‘logshippingSecondary’. **** Error : The log in this backup set begins at LSN 18000000005000001, which is too recent to apply to the database. An earlier log backup that includes LSN 18000000004900001 can be restored. Transaction Log backups can only be restored if they are in a sequence. If the LastLSN field and the FirstLSN field do not display the same number on consecutive transaction log backups, they are not restorable in that sequence. There may be several reasons for tran
Read more

SQL Loader with dynamic control file generation

Hi All, Now what i am writing, I believe many of new bees will require it while using sqlldr for ETL or any other data loading issue. The thing is while loading using sqlldr there is no option to enter the source file name in that table. So what I have done I just create a dynamic control file which generates before loading of each source file. Here I have used two scripts 1) proc1: for filtering files and running a loop. Every source file will be processed in this loop. 2) another procedure proc2 which is called by proc1 to perform load operation proc1 ------- while (( $i < $filecounter )); do echo "file number $i" ### Take the first File FILENAME=`ls -l *.txt|head -1|awk -F' ' '{print $9}'` dos2unix $FILENAME ### if filename exists already continue the loop ### FINDSQLSCRIPT="select 'COUNT:'||count(*) from TBLBRACSUMMARY where file_name='$FILENAME';" OUTPUT_FILECOUNT=`/app/u01/oracle/prod
Read more

Oracle Job Operations

As a DBA, often I need to deal with Client's Jobs. Sometimes the jobs got hault, needs to break etc etc. Here are some useful commands for this sort of operations. ## Job Owner Schema can do this execute dbms_job.remove(444); execute dbms_job.broken(444, true); ## If Sys User needs to remove the job exec SYS.DBMS_IJOB.REMOVE(444); ## to find which jobs are running SELECT a.sid, c.serial#, a.job, a.failures, to_char(a.this_date, 'mm/dd/yyyy hh:mi pm') startdatetime, b.what FROM dba_jobs_running a, dba_jobs b, v$session c WHERE a.job = b.job AND a.sid = c.sid order by a.this_date ##let say we have only sid and serial and we need to kill the process from both OS and database. So first we need to find out the SPID. this query for any RAC database select P.SPID ,S.SID ,S.SERIAL#, S.SCHEMANAME, S.PROGRAM, S.INST_ID, S.OSUSER, S.STATUS from gv$process p, gv$session s where P.ADDR=S.PADDR and S.SID=888 and s.serial#=9293; ## kill the job al
Read more