Create Data Guard standby using RMAN


Production server – serverProd
Standby server – serverStby
Production database – whseprod
Standby database – whsestby
Step 1: Using RMAN, Backup the database that includes backup of datafiles, archivelogs and controlfile for standby

[oracle@serverProd]$ rman target /
connected to target database: whseprod (DBID=123322)
RMAN> run {
allocate channel disk1 type disk format ‘/backup/whseprod_restore/%d%U’ maxpiecesize 5 G;
allocate channel disk2 type disk format ‘/backup/whseprod_restore/%d%U’ maxpiecesize 5 G;
allocate channel disk3 type disk format ‘/backup/whseprod_restore/%d%U’ maxpiecesize 5 G;
allocate channel disk4 type disk format ‘/backup/whseprod_restore/%d%U’ maxpiecesize 5 G;
allocate channel disk5 type disk format ‘/backup/whseprod_restore/%d%U’ maxpiecesize 5 G;
backup force  as BACKUPSET tag ‘%TAG’ database;
backup as BACKUPSET tag ‘%TAG’ archivelog;
release channel disk1;
release channel disk2;
release channel disk3;
release channel disk4;
release channel disk5;
}
RMAN> run {
allocate channel disk1 type disk format ‘/backup/whseprod_restore/%d%U’ maxpiecesize 5 G;
backup force  as BACKUPSET tag ‘%TAG’ current controlfile for standby;
release channel disk1;
}
Step 2: Move the backups to the standby server using ftp
               cd to directory that you want the files to go in on standby system
[oracle@serverStby ..]$ cd /backup/whseprod_restore
[oracle@serverStby /backup/whseprod_restore]$ ftp serverProd
ftp> bin
    ftp> prompt
change directory on the source system
                    ftp> cd /backup/whseprod_restore
                    ftp> mget *
                    ftp> quit

Step 3: Make proper changes in the parameter files of both primary and standby database
whseprod init file
DB_NAME=whseprod
DB_UNIQUE_NAME=whse
CONTROL_FILES=’/whseredo/oradata/control01.ctl’,’/whseredo/oradata/control02.ctl’,’/whseredo/oradata/control03.ctl’
LOG_ARCHIVE_DEST_1=’LOCATION=/whsearch/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES)’
alter system set FAL_CLIENT=whseprod scope=both;
alter system set FAL_SERVER=whsestby scope=both;
alter system set LOG_ARCHIVE_CONFIG=’DG_CONFIG=(whseprod, whsestby)’ scope=both;
alter system set log_archive_dest_2 =’SERVICE=whsestby LGWR ASYNC=40960 DB_UNIQUE_name=whsestby OPTIONAL REOPEN=120 MAX_FAILURE=120 NET_TIMEOUT=900′ scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;
alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both;
 whsestby init file
DB_NAME=whse
DB_UNIQUE_NAME=whsestby
CONTROL_FILES=’/whseredo/oradata/control01.ctl’,’/whseredo/oradata/control02.ctl’,’/whseredo/oradata/control03.ctl’
LOG_ARCHIVE_DEST_1=’LOCATION=/whsearch/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES)’
alter system set FAL_CLIENT=whsestby scope=both;
alter system set FAL_SERVER=whseprod scope=both;
alter system set LOG_ARCHIVE_CONFIG=’DG_CONFIG=(whseprod, whsestby)’ scope=both;
alter system set log_archive_dest_2 =’SERVICE=whseprod LGWR ASYNC=40960 DB_UNIQUE_name=whseprod OPTIONAL REOPEN=120 MAX_FAILURE=120 NET_TIMEOUT=900′ scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;
alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both;
Step 4: Do the restore and recover on standbydatabase
           serverStby> rman target /
          RMAN> startup nomount
          RMAN> restore standby controlfile from ‘/backup/whseprod_restore/c-1778-20171202-02’;
          serverStby>sqlplus as sysdba
          sql> alter database mount standby database;
          serverStby> rman target /
          RMAN> restore database
          Rman> list backup of archivelog all
***copied all of the archives that were created after the backup to the /whsearch/arch directory from the source
               cd to directory that you want the files to go in on standby system
[oracle@serverStby ..]$ cd /whsearch/arch
[oracle@serverStby /whsesarch/arch]$ ftp serverProd
ftp> bin
    ftp> prompt
change directory on the source system
                    ftp> cd /whsearch/arch
                    ftp> mget arch_111_2*
                    ftp> quit
          Rman> recover database
Step 5: Put the standby database in recover managed mode
        
        sql> alter database recover managed standby database disconnect from session;
Database altered.
Verify the Physical Standby Database Is Performing Properly
On the standby database, query the V$ARCHIVED_LOG view to identify existing files in
the archived redo log.
          sql> select sequence#, applied, to_char(first_time,’mm/dd/yy hh24:mi:ss’) first from v$archived_log order by first_time;
Step 6: Add standby redo logs
          sql>alter database recover managed standby database cancel;
sql>alter database add standby logfile group 15 ‘/whseredo/oradata/whse_sr15.log’ size 1048576000;
sql>alter database add standby logfile group 16 ‘/whseredo/oradata/whse_sr16.log’ size 1048576000;
sql>alter database add standby logfile group 17 ‘/whseredo/oradata/whse_sr17.log’ size 1048576000;
sql>alter database recover managed standby database disconnect from session;
Step 7: Add Data Guard Broker through OEM
                –make sure the standby’s are in oem
    –set preferred credentials by trying to log in through oem

      Click on primary database in OEM

      On Home Page ->availability->add standby database
      Choose third option Manage an Existing standby database with data guard broker and follow the on-screen instructions

2018’s list of goals

Many items peak my interest and that can sometimes make it difficult to choose a few that I would like to gain a deeper learning of, but I think my current role will guide me in 2018.

In no particular order:

SQL Server on Linux: This is a no brainer. As long as I can remember, I have preferred to work with command line over gui based tools. I’ve always felt like I have more control over what I am doing. And now that SQL Server is on Linux, I want to dig in and learn whatever I can. I think I also like the idea, that there aren’t a lot of SQL Server DBA’s that are familiar with Linux.

Cloud: I’ve played around in the cloud for years, but with little reason. Between Azure options and the Oracle Cloud, it’s time to start looking at the differences and see where each can help me.

Power BI: With Power BI Premium, I think this is a game changer for Corporate adoption.

Make it to PASS Summit: I’ve been to Oracle Open World 3 times, but do to job responsibilities or other co-workers training needs, I have unfortunately never made it to PASS. I don’t mind Open World, but more and more I can see PASS being more beneficial.

Oracle OEM details in PowerBI


Instead of filling out excel files with all of our Oracle database details, I decided to use Power BI to organize that information.
Prerequisites

  •  Must have Oracle client installed on computer
  • Must have a tnsnames.ora file to connect to Oracle database
  • Connecting to Oracle Database


The server entry is actually the entry in your tnsnames. Example below

Also fill in the select statement for the view that you want



In the next window choose database and then supply database credentials

A few of the OEM tables that I find interesting
mgmt.$hw_nic
mgmt.$jobs
mgmt.$os_hw_summary
mgmt.$target
mgmt.$target_properties

Oracle Open World and PASS Summit

A little less than a month ago I attended Oracle Open World in San Francisco. It’s been a few years since I last attended the conference. Although San Francisco seems to have changed the conference has stayed the same. Numerous days of sessions that are very informative. As I felt the last time I attended, many of the sessions seem like marketing sessions, this isn’t a bad thing, just different. I did really enjoy some sessions dealing with Oracle EBS, Oracle Cloud and DBA topics. One session “Navigating your DBA Career in the Oracle Cloud” by Craig Shallahamer from orapub.com was very eye opening. Enough so, that I have started to look more closely at my future.

PASS Summit is going on right now and unfortunately, I am not in attendance. However, thanks to PASSTV, I have been able to watch numerous days worth of keynotes and sessions. This is definitely a conference that I would like to attend in the future. I know a good amount of people that attend every year and love this conference.

Combination of Both Worlds

I just recently came back to my blog and noticed that my last post stated that I was going back to Oracle. I did, but the fit with that employer was not the best and I moved on to another company at the beginning of 2016. I couldn’t be happier with that move in 2016. I get to work with SQL Server and Oracle along with other software packages. Some items that I am working on or researching and would like to blog about in the future, Automic Scheduling software, SQL Server on Linux, Azure, Azure Analysis Services, Business Intelligence, Power BI, Oracle Data Guard, Oracle Cloud and other various items.

Going back to Oracle

I am thrilled to say that after 4 years of working with Microsoft SQL Server and the BI Tool Stack, I am going back to working with Oracle. I enjoyed working with Microsoft and getting to add another tool in my tool belt, but I am very excited about this new opportunity.

Hopefully this opportunity will lend itself to more blog posts.

Stay tuned for more details.