pthon 文件

import getopt
import datetime
import time
import subprocess
import commands
import re
import logging
import os
import pwd
import sys
import grp

from ConfigParser import ConfigParser


#===============define and initialize the global variables==============
interactive_mode = False
param_list =[]
prog_param = {}
restore_config_file = ''
db_parser = ConfigParser()
report_para = {}

restore_operation_id = 0
log_to_mysql = True


if log_to_mysql:
    import pymysql.cursors

#    db_connection_string = {
#        'host':'10.197.205.201',
#        'port':16063,
#        'user':'root',
#        'password':'N3tB@ckup',
#        'database':'mysql',
#        'charset':'utf8',
#        'cursorclass':pymysql.cursors.Cursor,
#    }
    db_connection_string = {
        'host':'25.10.0.104',
        'port':3306,
        'user':'root',
        'password':'123456',
        'database':'mysql',
        'charset':'utf8',
        'cursorclass':pymysql.cursors.Cursor,
    }


class Logger:
    def __init__(self, cmd, filename, screenlog=False):
        self.logger = logging.getLogger(cmd)
        self.logger.setLevel(logging.DEBUG)
#       fmt = logging.Formatter('%(asctime)s %(levelname)s %(message)s', '%Y-%m-%d %H:%M:%S')
        fmt = logging.Formatter('%(asctime)s %(levelname)5s %(message)s', '%H:%M:%S')
        screen = logging.StreamHandler()
        screen.setFormatter(fmt)
        if screenlog:
            screen.setLevel(logging.DEBUG)
        else:
            screen.setLevel(logging.INFO)
        self.logger.addHandler(screen)

        logfile = logging.FileHandler(filename)
        logfile.setLevel(logging.DEBUG)
        logfile.setFormatter(fmt)
        self.logger.addHandler(logfile)

    def debug(self, message):
        self.logger.debug(message)

    def info(self, message):
        self.logger.info(message)

    def warn(self, message):
        self.logger.warn(message)

    def error(self, message):
        self.logger.error(message)

    def critical(self, message):
        self.logger.critical(message)

code_and_message ={
0: 'RESTORE SUCCESS',
1: 'phase_1:  create pfile and startup nomount',
2: 'phase_2:  restore controlfile',
3: 'phase_3:  restore datafile',
4: 'phase_4:  apply archivelog',
5: 'phase_5:  open database and verify',
101: 'This programe must run by root',
102: 'invalid arguments',
103: 'invalid arguments',
104: 'invalid arguments',
105: 'invalid arguments',
106: 'invalid arguments',
107: 'invalid arguments',
108: 'License expired',
110: 'invalid arguments',
111: 'invalid arguments',
112: 'Cannot find configuration using source client and sid',
113: 'Oracle user not found in the [GLOBAL] section',
114: 'Oracle group not found in the [GLOBAL] section',
115: 'restore path not set',
116: 'oracle version is missing ',
117: 'pdb only available in Oracle 12',
118: 'get oracle base failed',
119: 'get oracle home failed',
120: 'some necessary options are missing',
121: 'cannot connect to master server or find backup of archivelog',
122: 'cannot connect to master server or find backup of control file',
123: 'oracle base not exist',
124: 'oracle home not exist',
140: 'fail to connect mysql database',
161: 'oracle is running and shutdown options = 0',
162: 'oradata path not exist',
163: 'fail to get backup id of controlfile',
164: 'restore controlfile failed',
199: 'shutdown instance failed',
200: 'startup instance nomount failed',
201: 'mount database failed',
202: 'fail to parse db backup nodes',
203: 'restore database failed',
204: 'fail to rename datafile',
205: 'get tablespace list failed',
206: 'get datafile failed',
207: 'fail to get archivelog backup from multiplue nodes',
208: 'recover database failed',
209: 'get rename redolog list failed',
210: 'rename tempfile,redolog failed',
211: 'invalid value to open database',
212: 'open database failed',
213: 'cannot find the verify script file',
214: 'verify database failed',
215: 'get db size failed'
}

#==================End of  global variables=================



def Usage():
    print
    print
    print 'Usage: %s ' % (sys.argv[0])
    print '   or: %s  -s sid -c source_client [-v]' %(sys.argv[0])
    print '   or: %s  --sid sid --client source_client [--verbose]' %(sys.argv[0])
    print '   or: %s  --sid sid --client source_client --database pdb_name' %(sys.argv[0])
    print '   or: %s  --sid sid --client source_client --end phase_4  [--verbose]' %(sys.argv[0])
    print 
    print ' -h, --help: print help message.'
    print ' -s, --sid ORACLE_SID : ORACLE SID that you want to restore'
    print ' -u, --until "yyyy-mm-dd hh24:mi:ss" : Time the database recover to'
    print ' -c, --client : source client of backup'
    print ' -v, --verbose: detail output for debug'
    print ' -p, --preview: Preview mode, create profile/pfile/scripts only'
    print ' -l, --license: Show license information'
    print ' -d, --database: portable database name'
    print ' -f, --force: force restore '
    print ' -b,--begin; -e, --end: restore from or end at specific phase.'
    print '       phase_1:  create pfile and startup nomount'
    print '       phase_2:  restore controlfile and mount'
    print '       phase_3:  store datafile'
    print '       phase_4:  apply archivelog to until time'
    print '       phase_5:  open database and execute verify sql scripts'
    print
    print


def check_option():
    try:
        opts, args = getopt.getopt(sys.argv[1:], "vhplfc:s:u:b:e:d:",
                                   [
                                       "sid=",
                                       "until=",
                                       "client=",
                                       "help",
                                       "verbose",
                                       "preview",
                                       "license",
                                       "begin",
                                       "end",
                                       "database",
                                       "force"
                                   ])
    except getopt.GetoptError:
        print('invalid arguments.')
        Usage()
        exit_after_fail(102)
    for name, value in opts:
        if name in ('-h', '--help'):
            Usage()
            exit_after_fail(0)
        elif name in ('--license', '-l'):
            if prog_param['license date'] <> 'permanent':
                print('This prgram is a demo verison, expire date:' + prog_param['license date'])
            else:
                print('It is a permanent version with full features.')
            exit_after_fail(0)
        elif name in ('--verbose', '-v'):
            prog_param['verbose'] = True
        elif name in ('--preview', '-p'):
            prog_param['preview'] = True
        elif name in ('--sid', '-s'):
            prog_param['sid'] = value
        elif name in ('--client', '-c'):
            prog_param['source client'] = value
        elif name in ('--database', '-d'):
            prog_param['pdb'] = value
        elif name in ('--begin', '-b'):
            if value in ('phase_1','phase_2','phase_3','phase_4','phase_5','phase_6'):
                prog_param['begin'] = value
            else:
                print("Invalid arguments, acceptable begin value is: 'phase_1','phase_2','phase_3','phase_4','phase_5','phase_6'")
                exit_after_fail(103)
        elif name in ('--end', '-e'):
            if value in ('phase_2','phase_3','phase_4','phase_5','phase_6'):
                prog_param['end'] = value
            else:
                print("Invalid arguments, acceptable end value is:'phase_2','phase_3','phase_4','phase_5','phase_6'")
                exit_after_fail(104)
        elif name in ('--until', '-u'):
            if is_date(value):
                prog_param['until'] = value
            else:
                print('Invalid arguments, acceptable date format is: "yyyy-mm-dd hh24:mi:ss" NOTE: Time must enclosed in double quotes.')
                exit_after_fail(105)
        elif name in ('--force', '-f'):
            prog_param['force'] = True
        else:
            print('Invalid arguments,unknow option:' + name)
            Usage()
            exit_after_fail(106)

    #sid and source client must be set at the same time.
    if  ('sid' not in prog_param and 'source client' in prog_param) or ('sid' in prog_param and 'source client' not in prog_param):
        print ('Sid and client options must be specifed at the same time')
        exit_after_fail(107)


def check_license():
    if prog_param['license date'] <> 'permanent':
        print('This prgram is a demo verison, expire date:' + prog_param['license date'])
        expire_time = datetime.datetime.strptime(prog_param['license date'], '%Y-%m-%d')
        if expire_time < datetime.datetime.now():
            print ('License expired')
            exit_after_fail(108)
    return 0


def check_user():
    if os.getuid() != 0:
        print('This programe must run by root.')
        exit_after_fail(101)

script_sequence = 0
def get_script_seq():
    global script_sequence
    script_sequence += 1
    return str(script_sequence) + '-'


def write_file(fp, str):
    fp.write(str+'\n')


def write_oracle_log(fp, str):
    logger.debug(str.strip())
    fp.write(str.strip() + '\n')


#initialize the global variables
def define_global_variables():
    global param_list
    param_list = [ 'archivelog prefix',
                   'backup search period',
                   'cleanup after restore',
                   'cleanup before restore',
                   'compatible',
                   'controlfile prefix',
                   'db backup nodes',
                   'archivelog backup nodes',
                   'oradata path',
                   'tempfile path',
                   'script path',
                   'dbid',
                   'dbname',
                   'pdb',
                   'database open mode',
                   'license date',
                   'oracle base',
                   'oracle gid',
                   'oracle group',
                   'oracle home',
                   'oracle profile',
                   'oracle uid',
                   'oracle user',
                   'oracle version',
                   'restore version',
                   'preview',
                   'restore channel',
                   'sql logfile',
                   'restore logfile',
                   'restore path',
                   'rman connect string',
                   'shutdown after restore',
                   'shutdown exist instance',
                   'shutdown after failure',
                   'sid',
                   'source client',
                   'until',
                   'verbose',
                   'verify script',
                   'begin',
                   'end']
    prog_param['verbose'] = False
    prog_param['preview'] = False
    prog_param['license date'] = 'permanent'

    global restore_config_file
    restore_config_file = os.path.split(os.path.realpath(__file__))[0] + os.sep + 'oracle_restore.conf'
    if not os.path.isfile(restore_config_file):
        print ('Cannot find the configuration file: ' + restore_config_file)
        exit_after_fail(100)
    db_parser.read(restore_config_file)


def get_last_arhivelog_backup_time():
    cmd = '/usr/openv/netbackup/bin/bplist -t 4 -l -Listseconds -R -C ' + prog_param['source client']
    
    #set start time of catalog searching
    start_time = datetime.datetime.now() - datetime.timedelta(days=int(prog_param['backup search period']))

    cmd = cmd + ' -s ' + start_time.strftime('%m/%d/%Y %H:%M:%S') + ' /' + prog_param['archivelog prefix'] + '*'
    logger.debug('Search archivelog backup: ')
    logger.debug(cmd)

    out_list = subprocess.Popen(cmd, shell=True, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    for line in out_list.stdout.readlines():
        if '/' + prog_param['archivelog prefix'] in line:
            row = line.split()
            if len(row) == 8 and row[7].startswith('/'+ prog_param['archivelog prefix']):
                logger.debug(line)
                str_time = row[4] + ' ' + row[5] + ' ' + datetime.datetime.now().strftime('%Y') + ' ' + row[6]
                l_time = datetime.datetime.strptime(str_time, '%b %d %Y %H:%M:%S')
                if l_time > datetime.datetime.now():
                    str_time = row[4] + ' ' + row[5] + ' ' + str(datetime.date.today().year - 1) + ' ' + row[6]
                    l_time = datetime.datetime.strptime(str_time, '%b %d %Y %H:%M:%S')
                logger.info('Found the last backup of archivelog: %s' %str_time)
                return l_time
    logger.error("Cannot connect to NetBackup Master Server or find archivelog by prefix '/%s'!" %(prog_param['archivelog prefix']))
    exit_after_fail(121)

def get_last_controlfile_backup_time():
    cmd = '/usr/openv/netbackup/bin/bplist -t 4 -l -Listseconds -R -C ' + prog_param['source client']

    #set start time of catalog searching
    start_time = datetime.datetime.now() - datetime.timedelta(days=int(prog_param['backup search period']))

    cmd = cmd + ' -s ' + start_time.strftime('%m/%d/%Y %H:%M:%S') + ' /' + prog_param['controlfile prefix'] + '*'
    logger.debug('Search controlfile backup: ')
    logger.debug(cmd)

    out_list = subprocess.Popen(cmd, shell=True, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    for line in out_list.stdout.readlines():
        if '/' + prog_param['controlfile prefix'] in line:
            row = line.split()
            if len(row) == 8 and row[7].startswith('/'+ prog_param['controlfile prefix']):
                logger.debug(line)
                str_time = row[4] + ' ' + row[5] + ' ' + datetime.datetime.now().strftime('%Y') + ' ' + row[6]
                l_time = datetime.datetime.strptime(str_time, '%b %d %Y %H:%M:%S')
                if l_time > datetime.datetime.now():
                    str_time = row[4] + ' ' + row[5] + ' ' + str(datetime.date.today().year - 1) + ' ' + row[6]
                    l_time = datetime.datetime.strptime(str_time, '%b %d %Y %H:%M:%S')
                logger.info('')
                logger.info('Found the last backup of controlfile at %s' %str_time)
                return l_time
    logger.error("Cannot connect to NetBackup Master Server or find contfilefile backup by prefix '/%s'!" %(prog_param['controlfile prefix']))
    exit_after_fail(122)

    
    
def get_until_time():
#    last_backup_time = min(get_last_controlfile_backup_time(), get_last_arhivelog_backup_time())
    last_backup_time = get_last_controlfile_backup_time()

    if interactive_mode == True:
        input_time = raw_input('Please Input oracle restore until time: (' + last_backup_time.strftime('%Y-%m-%d %H:%M:%S') + ') ')
        input_time = input_time.strip()
        if input_time == '':
            prog_param['until'] = last_backup_time.strftime('%Y-%m-%d %H:%M:%S')
        else:
            prog_param['until'] = input_time
            #validate until time 
            if not is_date(prog_param['until']):
                logger.error('Invalid time format! use yyyy-mm-dd hh24:mi:ss')
                exit_after_fail(121)
            elif datetime.datetime.strptime(prog_param['until'], '%Y-%m-%d %H:%M:%S') > last_backup_time:
                logger.error('Your specifed time is later than the last backup time ' + last_backup_time.strftime('%Y-%m-%d %H:%M:%S'))
                exit_after_fail(122)
        logger.info('Database will be restored to ' + prog_param['until'])    
    else:
        if 'until' not in prog_param or prog_param['until'] == '':
            prog_param['until'] = last_backup_time.strftime('%Y-%m-%d %H:%M:%S')


def initialize_env():
    define_global_variables()
    print BANNER
    check_user()
    check_option()
    check_license()

    if 'sid' not in prog_param  or 'source client' not in prog_param:
        # oralce sid and source client  are not specified in the command, jump into interactive mode.
        global interactive_mode
        interactive_mode = True
        prog_param['verbose'] = True
        print 'Available Database List:'
        for db_section_name in db_parser.sections():
            #exclude GLOBAL, ORACLE, PFILE section
            if  db_section_name in [ 'GLOBAL', 'ORACLE', 'PFILE' ]:
                continue

            if 'oracle sid' in db_parser.options(db_section_name):
                sid = db_parser.get(db_section_name, 'oracle sid')
            else:
                print('oracle sid is missing in [%s] of [%s]'  %(db_section_name, restore_config_file) )
                exit_after_fail(110)

            if 'source client' in db_parser.options(db_section_name):
                source_client = db_parser.get(db_section_name, 'source client')
            else:
                print('source client is missing in [%s] of [%s]'  %(db_section_name, restore_config_file) )
                exit_after_fail(111)

            print '\t' + db_section_name + ' (oracle sid=' + sid + '; source client=' + source_client + ')'

        db_section_name = raw_input('Select the database to be restored:[Q/q=exit] ')
        db_section_name = db_section_name.strip()
        while db_section_name not in db_parser.sections():
            if db_section_name in ('Q', 'q', 'quit'):
                exit_after_fail(0)
            print 'Error: Wrong input, Please input db listed above'
            db_section_name = raw_input('Select the database to be restored: ')

        prog_param['sid'] = db_parser.get(db_section_name, 'oracle sid')
        prog_param['source client'] = db_parser.get(db_section_name, 'source client')
    else:
        #check oracle sid and source client in config file
        database_found = False
        for db_section_name in db_parser.sections():
            if  db_section_name not in [ 'GLOBAL', 'ORACLE', 'PFILE' ]:
                if prog_param['sid'] == db_parser.get(db_section_name, 'oracle sid') and prog_param['source client'] == db_parser.get(db_section_name, 'source client'):
                    database_found = True
                    break

        if database_found == False:
            print('Cannot find configuration using source client:[%s] and sid:[%s] in [%s]' %(prog_param['source client'],prog_param['sid'],restore_config_file))
            exit_after_fail(112)

    for t_opt in db_parser.options('GLOBAL'):
        if t_opt not in  prog_param or prog_param[t_opt] == '':
            prog_param[t_opt] = db_parser.get('GLOBAL', t_opt)

    for t_opt in db_parser.options(db_section_name):
            prog_param[t_opt] = db_parser.get(db_section_name, t_opt)

    if 'oracle user' not in  prog_param or prog_param['oracle user'] == '':
        print 'Oracle user not found in the [GLOBAL] section of ' + restore_config_file
        exit_after_fail(113)
    if 'oracle group' not in  prog_param or prog_param['oracle group'] == '':
        print 'Oracle group not found in the [GLOBAL] section of ' + restore_config_file
        exit_after_fail(114)

    global uid
    global gid
    uid = pwd.getpwnam(prog_param['oracle user']).pw_uid
    gid = grp.getgrnam(prog_param['oracle group']).gr_gid
    prog_param['oracle uid'] = uid
    prog_param['oracle gid'] = gid

    if 'restore path' not in  prog_param or prog_param['restore path'] == '':
        print('restore path not set in '+ restore_config_file)
        exit_after_fail(115)

    restore_log_dir = prog_param['restore path'] + os.sep + 'logs'
    if not os.path.exists(restore_log_dir):
        os.makedirs(restore_log_dir)
        os.chown(restore_log_dir, uid, gid)

    #logfile for restore 
    prog_param['restore logfile'] = restore_log_dir + os.sep + 'restore_' + prog_param['source client']+ '_'+ prog_param['sid'] + '_' + datetime.datetime.now().strftime("%Y%m%d_%H%M%S") + '.log'

    #log file for sqlplus and rman
    #prog_param['sql logfile'] = restore_log_dir + os.sep + 'sql_' + datetime.datetime.now().strftime("%Y%m%d_%H%M%S") + '.log'
    prog_param['sql logfile'] = restore_log_dir + os.sep + 'sql_' + prog_param['source client']+ '_' + prog_param['sid'] +  '_' + datetime.datetime.now().strftime("%Y%m%d_%H%M%S") +'.log'
    global sql_logfile_fp
    sql_logfile_fp = open(prog_param['sql logfile'], 'w')

    global logger    
    logger = Logger('oracle_restore', prog_param['restore logfile'], prog_param['verbose'])
    
    
    prog_param['restore home'] = prog_param['restore path'] + os.sep + prog_param['source client'] + os.sep + prog_param['sid']
    prog_param['report path'] = prog_param['restore home'] + os.sep + 'report'
    prog_param['script path'] = prog_param['restore home'] + os.sep + 'scripts'
    prog_param['oradata path']  = prog_param['restore home'] + os.sep + 'oradata'
    prog_param['tempfile path'] = prog_param['oradata path'] + os.sep + 'tempfile'

    for path in ( prog_param['restore path']+ os.sep + prog_param['source client'],
                  prog_param['restore home'],
                  prog_param['report path'],
                  prog_param['script path'],
                  prog_param['oradata path'],
                  prog_param['tempfile path'],
                  prog_param['restore home'] + os.sep + 'diag',
                  prog_param['restore home'] + os.sep + 'fra',
                  prog_param['restore home'] + os.sep + 'adump'
                  ):
        if not os.path.exists(path):
            os.makedirs(path)
            os.chown(path,prog_param['oracle uid'],prog_param['oracle gid'])
        else: 
            os.chown(path,prog_param['oracle uid'],prog_param['oracle gid'])

    os.system( 'chown -R '+prog_param['oracle user'] + ' ' + prog_param['restore home'])
    os.system( 'chgrp -R '+prog_param['oracle group'] + ' ' + prog_param['restore home'])
    
    #cleanup oracle flash recovery area
    os.system('rm -rf ' +  prog_param['restore home'] + "/fra/*")        


    logger.debug('')
    logger.debug('')
    logger.debug('BEGIN TO RESTORE [%s]'  %(prog_param['sid']))
    logger.debug('Confguration [%s] wil be used for this restore'  %(db_section_name) )

    if 'dbname' not in  prog_param or prog_param['dbname'] == '':
        logger.debug('dbname is not set in [%s], set dbname same as oracle sid' %(db_section_name))
        prog_param['dbname'] = prog_param['sid']

    if 'restore channel' not in  prog_param or prog_param['restore channel'].isdigit()==False:
        logger.debug('Restore channel is not set or invalid, restore continue with one channel')
        prog_param['restore channel'] = int('1')
    else:
        prog_param['restore channel'] = int(prog_param['restore channel'])    
        #restore channel for archivelog should not more than 8.
        if prog_param['restore channel'] > 8:
           logger.warn('restore channel value is largger than 8')
           prog_param['restore channel'] = int('8')
    
    if 'backup search period' not in  prog_param or prog_param['backup search period'].isdigit()==False:
        logger.debug('backup search period is not set or invalid, restore continue with one channel')
        prog_param['backup search period'] = int('365')
    else:
        prog_param['backup search period'] = int(prog_param['backup search period'])    

    if 'oracle version' in db_parser.options(db_section_name):
        prog_param['compatible'] = prog_param['oracle version']
    else:
        logger.error('oracle version is missing in [%s] of [%s]'  %(db_section_name, restore_config_file) )
        exit_after_fail(116)
    
    if prog_param['oracle version'].startswith('12') == False and 'pdb' in prog_param  and prog_param['pdb'] != '':
        logger.error('pdb only available in Oracle 12' )
        exit_after_fail(117)
    
        
    if 'restore version' not in  prog_param or prog_param['restore version']:
        logger.debug('Restore version is not set, set dbname equal to oracle version')
        prog_param['restore version'] = prog_param['oracle version']

    if 'oracle base' not in  prog_param or prog_param['oracle base'] == '':
        prog_param['oracle base'] =  db_parser.get('GLOBAL', prog_param['restore version'] + '-base')
        if prog_param['oracle base'] == '':
            logger.error('get oracle base failed' )
            exit_after_fail(118)

    if not os.path.exists(prog_param['oracle base']):
            logger.error('oracle base not exist' )
            exit_after_fail(123)

    if 'oracle home' not in  prog_param or prog_param['oracle home'] == '':
        prog_param['oracle home'] =  db_parser.get('GLOBAL', prog_param['restore version'] + '-home')
        if prog_param['oracle home'] == '':
            logger.error('get oracle home failed' )
            exit_after_fail(119)

    if not os.path.exists(prog_param['oracle home']):
            logger.error('oracle home not exist' )
            exit_after_fail(124)

        
    # if "shutdown after failure" not set, database will be shutdown after failure in the restore
    if 'shutdown after failure' not in  prog_param or prog_param['shutdown after failure'] == '':
        prog_param['shutdown after failure'] = 0
            
    create_oracle_profile();

    for t_opt in param_list:
        #value of key is already set
        if t_opt in prog_param and prog_param[t_opt] != '':
            pass
        elif t_opt in ['until', 'oracle profile', 'dbid','verify script', 'begin', 'end', 'db backup nodes','archivelog backup nodes','pdb']:
            pass
        else:
            logger.error('the option [%s] is missing in section [%s] of %s' %(t_opt, db_section_name, restore_config_file))
            exit_after_fail(120)
          
    logger.debug('')
    logger.debug('    ============== Restore Parameters List ==============')

    list = prog_param.keys()
    list.sort()
    for t_opt in list:
        if t_opt in prog_param.keys():
            logger.debug('%25s = %s' %(t_opt, prog_param[t_opt]))
    logger.debug('    =====================================================')
    logger.debug('')
    logger.debug('')

    #get last backup time and set prog_param['until']
    get_until_time()

    if interactive_mode == True:
        print 'Restore Info:'
        print ' DBName: ' + prog_param['dbname']
        print ' Oracle SID: ' + prog_param['sid']
        if 'dbid' in prog_param and prog_param['dbid'] != '':
            print ' DBID: ' + prog_param['dbid']
        print ' restore Time: ' + prog_param['until']
        print ' restore Path: ' + prog_param['restore home']
        input_answer = raw_input('Do you want to continue?: [Y/N] ')
        if input_answer.strip() not in ('Y', 'y', 'YES','yes'):
            print 'restore operation is cancelled.'
            exit_after_fail(0)
    return 0

def create_oracle_profile():
    prog_param['oracle profile'] = prog_param['script path'] + os.sep + '.profile'
    fp = open(prog_param['oracle profile'], 'w')
    write_file(fp, 'LANG=C')
    write_file(fp, 'LC_ALL=C')
    write_file(fp, 'export LANG LC_ALL')
    write_file(fp, 'ORACLE_BASE=' + prog_param['oracle base'])
    write_file(fp, 'ORACLE_HOME=' + prog_param['oracle home'])
    write_file(fp, 'ORACLE_SID=' + prog_param['sid'])
    write_file(fp, 'PATH=$ORACLE_HOME/bin:$PATH')
    write_file(fp, 'export ORACLE_HOME ORACLE_BASE ORACLE_SID PATH')
    write_file(fp, 'export LD_LIBRARY_PATH=$ORACLE_HOME/lib32:$ORACLE_HOME/lib:$ORACLE_HOME/ctx/lib:$ORACLE_HOME/RDBMS/lib')
    write_file(fp, "export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'")
    

    fp.close()
    os.chown(prog_param['oracle profile'], uid, gid)
    return 0

def check_oracle_status(sid):
    cmd = "ps -ef |grep ora_smon_" + sid + "|grep -v grep"
    if commands.getoutput(cmd).strip() != '':
        return 'ONLINE'
    else:
        return 'OFFLINE'

def get_oracle_status():
    script_file = prog_param['script path'] + os.sep + 'get_oracle_status.sql'
    fp = open(script_file, 'w')
    write_file(fp, 'connect / as sysdba')
    write_file(fp, 'set heading off feedback off pagesize 0 verify off echo off')
    write_file(fp, 'select status from v$instance;')    
    write_file(fp, 'exit;')
    fp.close()
    
    script_cmd = 'su - ' + prog_param['oracle user'] + ' -c \'. ' + prog_param['oracle profile'] + '; sqlplus /nolog @' + script_file + '\''
    write_oracle_log(sql_logfile_fp, script_cmd)
    
    out_list = subprocess.Popen(script_cmd, shell=True, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    for line in out_list.stdout.readlines():
        write_oracle_log(sql_logfile_fp, line)
        if  'ORACLE not available' in line:
            return 'STOP'
        elif  'STARTED' in line:
            return 'NOMOUNT'            
        elif  'MOUNTED' in line:
            return 'MOUNT'
        elif  'OPEN' in line:    
            return 'OPEN'
    return 'UNKNOWN'

        
def shutdown_oracle(shutdown_opt='immediate'):
    if shutdown_opt not in ['immediate', 'abort']:
        shutdown_opt = 'immediate'

    logger.info('Shutdown Oracle [%s]' %(prog_param['sid']))

    if check_oracle_status(prog_param['sid']) == 'OFFLINE':
        logger.debug('Oracle instance ' + prog_param['sid'] + ' is not running')
        return 0

    create_oracle_profile()
    shutdown_file = prog_param['script path'] + os.sep + '0-shutdown-instance.sql'
    fp = open(shutdown_file, 'w')
    write_file(fp, 'connect / as sysdba')
    write_file(fp, 'shutdown ' + shutdown_opt + ';')
    write_file(fp, 'exit')
    fp.close()
    os.chown(shutdown_file, uid, gid)

    script_cmd = 'su - ' + prog_param['oracle user'] + ' -c \'. ' + prog_param['oracle profile'] + '; sqlplus /nolog @' + shutdown_file + '\''
    write_oracle_log(sql_logfile_fp, '')
    write_oracle_log(sql_logfile_fp, '')
    write_oracle_log(sql_logfile_fp, script_cmd)
    out_list = subprocess.Popen(script_cmd, shell=True, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    line = out_list.stdout.readline()

    is_err = True
    while line:
        write_oracle_log(sql_logfile_fp, line)
        if 'ORACLE instance shut down.' in line:
            is_err = False
            logger.info('Shutdown instance complete')
        line = out_list.stdout.readline()
    out_list.wait()

    if is_err:
        logger.error('shutdown instance failed')
        sys.exit(199)
    else:
        return 0

def create_pfile():
    prog_param['oracle pfile'] = prog_param['script path'] + os.sep + 'pfile.ora'
    fp = open(prog_param['oracle pfile'], 'w')
    write_file(fp, '*.db_name=' + prog_param['dbname'])
    write_file(fp, '*.compatible=' + prog_param['compatible'])
    write_file(fp, '*.audit_file_dest=' + prog_param['restore home'] + os.sep + 'adump')
    write_file(fp, '*.db_recovery_file_dest=' + prog_param['restore home'] + os.sep + 'fra')
    write_file(fp, '*.control_files=' + prog_param['oradata path'] + os.sep + 'control01.ctl')
    write_file(fp, '*.db_create_file_dest=' + prog_param['oradata path'])
    write_file(fp, '*.NLS_DATE_FORMAT=\'yyyy-mm-dd hh24:mi:ss\'')

    if prog_param['oracle version'].startswith('12') or prog_param['oracle version'].startswith('11'):
        write_file(fp, '*.diagnostic_dest=' + prog_param['restore home'] + os.sep + 'diag')
    
    for t_opt in db_parser.options('PFILE'):
        write_file(fp, t_opt + '=' + db_parser.get('PFILE', t_opt))

    if prog_param['oracle version'].startswith('12') and 'pdb' in prog_param and prog_param['pdb'] != '':
        write_file(fp, '*.enable_pluggable_database=TRUE')

    fp.close()
    os.chown(prog_param['oracle pfile'], uid, gid)
    logger.debug('Oracle Pfile: ' + prog_param['oracle pfile'] + ' created.')


def startup_nomount():
    create_oracle_profile()
    script_file = prog_param['script path']+os.sep +'1-startup-instance-nomount.sql'
    fp = open(script_file, 'w')
    write_file(fp, 'connect / as sysdba')
    write_file(fp, 'startup nomount pfile=\'' + prog_param['oracle pfile'] + '\'')
    write_file(fp, 'exit')
    fp.close()
    os.chown(script_file, uid, gid)

    if prog_param['preview']:
        return 0

    logger.debug('Start Oracle instance, pfile=' + prog_param['oracle pfile'])
    script_cmd = 'su - ' + prog_param['oracle user'] + ' -c \'. ' + prog_param['oracle profile'] + '; sqlplus /nolog @' + script_file + '\''
    logger.info('Startup instance [%s]' %(prog_param['sid']))

    write_oracle_log(sql_logfile_fp, '')
    write_oracle_log(sql_logfile_fp, '')
    write_oracle_log(sql_logfile_fp, script_cmd)

    out_list = subprocess.Popen(script_cmd, shell=True, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    out_list.wait()

    if get_oracle_status() != 'NOMOUNT':
        logger.error('startup instance nomount failed')
        exit_after_fail(200)
    else:
        logger.info('startup instance nomount complete')
        return 0


def mount_database():
    script_file = prog_param['script path'] + os.sep + '3-mount-database.sql'
    fp = open(script_file, 'w')
    write_file(fp, 'connect / as sysdba')
    write_file(fp, 'alter database mount;')
    write_file(fp, 'alter system set db_recovery_file_dest_size=1000G;')    
    write_file(fp, 'exit')
    fp.close()
    os.chown(script_file, uid, gid)

    if  prog_param['preview']:
        return 0

    create_oracle_profile()
    script_cmd = 'su - ' + prog_param['oracle user'] + ' -c \'. ' + prog_param['oracle profile'] + '; sqlplus /nolog @' + script_file + '\''

    logger.debug('')
    logger.info('Mounting database instance [%s]' %(prog_param['sid']))
    write_oracle_log(sql_logfile_fp, '')
    write_oracle_log(sql_logfile_fp, '')
    write_oracle_log(sql_logfile_fp, script_cmd)


    out_list = subprocess.Popen(script_cmd, shell=True, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    out_list.wait()

    if get_oracle_status() != 'MOUNT':
        logger.error('mount database failed')
        exit_after_fail(201)
    else:
        logger.info('Oracle database [%s] is mounted'  %(prog_param['sid']))
        db_size = get_db_size("v$datafile")
        if db_size :
            logger.info('The database size is %d MB' %db_size)
        return 0

def set_newname_for_datafile(restored_datafiles):
    script_file = prog_param['script path'] + os.sep + '5-set_newname_for_datafile.rvt'
    fp = open(script_file, 'w')
    if 'dbid' in prog_param and prog_param['dbid'] != '':
        write_file(fp, 'set dbid ' + prog_param['dbid'])
    write_file(fp, 'connect target /')
    write_file(fp, 'run {')
    
    if len(restored_datafiles) == 0 :
        write_file(fp, 'set newname for database to \'' + prog_param['oradata path'] + os.sep + '%f-%b\';')
    else:
        for file_id in restored_datafiles.keys():
            write_file(fp, "set NEWNAME for datafile %s to '%s';" %(file_id, prog_param['oradata path'] + os.sep + restored_datafiles[file_id]))
            
    write_file(fp, 'switch datafile all;')
    write_file(fp, '}')
    fp.close()
    os.chown(script_file, uid, gid)

    script_cmd = 'su - ' + prog_param['oracle user'] + ' -c \'. ' + prog_param['oracle profile'] + '; rman cmdfile=' + script_file + '\''
    write_oracle_log(sql_logfile_fp, '')
    write_oracle_log(sql_logfile_fp, '')
    write_oracle_log(sql_logfile_fp, script_cmd)

    out_list = subprocess.Popen(script_cmd, shell=True, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    line = out_list.stdout.readline()
    
    is_err = False
    i=1
    while line:
        write_oracle_log(sql_logfile_fp, line)
        if line.startswith('RMAN-'):
            is_err = True
        line = out_list.stdout.readline()
        
    out_list.wait()
    if  is_err == True:
        logger.error('Fail to rename datafile')
        exit_after_fail(204)
    else:
        logger.info('Rename datafile complete')
        return 0
        
        
def restore_db():
    logger.info('Restore all datafiles to %s'  %(prog_param['oradata path']))

    script_file = prog_param['script path'] + os.sep + '4-restore-database.rvt'
    fp = open(script_file, 'w')
    channel_env = 'PARMS \'ENV=(NB_ORA_CLIENT=' + prog_param['source client'] + ')\';'

    if 'dbid' in prog_param and prog_param['dbid'] != '':
        write_file(fp, 'set dbid ' + prog_param['dbid'])
    write_file(fp, 'connect target /')
    write_file(fp, 'run {')
    write_file(fp, 'set until time \"to_date(\'' + prog_param['until'] + '\',\'yyyy-mm-dd hh24:mi:ss\')\";')

    #prepare restore database scripts
    if 'db backup nodes' in prog_param and prog_param['db backup nodes'].strip() != '':
        #restore database from multiple nodes
        backup_nodes=[]
        node_list = prog_param['db backup nodes'].split(',')
        logger.debug('db backup nodes:')
        logger.debug(node_list)
        if len(node_list) < 1:
            logger.error("Fail to parse db backup nodes, please check the parameter 'db backup nodes'")
            fp.close()
            exit_after_fail(202)

        #check database backup record in NetBackup
        i=0
        while i <len(node_list):
            if check_datafile_backup(node_list[i]) == True:
                backup_nodes.append(node_list[i])
                logger.debug(node_list[i] + 'has db backup')
            i+=1
        if len(backup_nodes) < 1:
            fp.close()
            logger.error("Failed to get db backup from:")
            logger.error(backup_nodes)
            exit_after_fail()

        i=0
        channel_pre_node = prog_param['restore channel']/len(backup_nodes)
        if channel_pre_node < 1:
            fp.close()
            logger.error("The number of nodes is bigger than channel, please increase channel from config file")
            exit_after_fail()

        i=0
        while i < len(backup_nodes):
            channel_env = 'PARMS \'ENV=(NB_ORA_CLIENT=' + backup_nodes[i] + ')\';'
            j = 1
            while j <= channel_pre_node:
                write_file(fp, 'ALLOCATE CHANNEL CH' + str(i) + str(j) + ' TYPE \'SBT_TAPE\' ' + channel_env)
                j += 1
            i+=1

        write_file(fp, 'set newname for database to \'' + prog_param['oradata path'] + os.sep + '%f-%b\';')
        write_file(fp, 'restore database;')
        write_file(fp, '}')
        fp.close()
        os.chown(script_file, uid, gid)
    else:
        #restore database from one node
        for i in range(prog_param['restore channel']):
            write_file(fp, 'ALLOCATE CHANNEL CH' + str(i) + ' TYPE \'SBT_TAPE\' ' + channel_env)
        
        #change path of datafile
        if prog_param['oracle version'].startswith('12') or prog_param['oracle version'].startswith('11.2'):
            #oracle 11.2 and above, use set newname for database
            write_file(fp, 'set newname for database to \'' + prog_param['oradata path'] + os.sep + '%f-%b\';')
        else:
            #for oracle 10g and oracle 11.1, use set newname for datafile
            datafile_list = {}
            datafile_list = get_datafile_list()
            
            for datafile_id in datafile_list.keys():
                datafile_name = datafile_list[datafile_id]
                write_file(fp, 'set newname for datafile ' + datafile_id + " to '" + prog_param['oradata path'] + os.sep + datafile_id + '-' + datafile_name + "';")           
            #end of oracle 10g reanme
        
        #restore database
        if 'pdb' in prog_param  and prog_param['pdb'] != '':
            #restore pluggable databases
            write_file(fp, 'restore database root;')
            write_file(fp, 'restore pluggable database ' + prog_param['pdb'] + ';')
            write_file(fp, "restore pluggable database 'PDB$SEED';")
        else:    
            #restore normal databases
            write_file(fp, 'restore database;')  
            write_file(fp, 'switch datafile all;')
            
        for i in range(prog_param['restore channel']):
            write_file(fp, 'RELEASE CHANNEL CH' + str(i) + ';')
        write_file(fp, '}')
        fp.close()
        os.chown(script_file, uid, gid)

    if  prog_param['preview']:
        return 0

    script_cmd = 'su - ' + prog_param['oracle user'] + ' -c \'. ' + prog_param['oracle profile'] + '; rman cmdfile=' + script_file + '\''
    logger.debug('')
    logger.debug('Restore database [%s]' %(prog_param['dbname']))
    write_oracle_log(sql_logfile_fp, '')
    write_oracle_log(sql_logfile_fp, '')
    write_oracle_log(sql_logfile_fp, script_cmd)

    #report_para['restore start time'] = datetime.datetime.now()
    out_list = subprocess.Popen(script_cmd, shell=True, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    line = out_list.stdout.readline()

    is_err = False
    report_para['datafile'] = []
    restored_datafiles = {}
    while line:
        write_oracle_log(sql_logfile_fp, line)
        if line.startswith('RMAN-'):
            is_err = True
        if 'reading from backup piece' in line:
            report_para['datafile'].append(line.split()[6])
        
        #get data file name and id for rename
        if 'pdb' in prog_param  and prog_param['pdb'] != '':
            if line.startswith('input datafile copy')  or line.startswith('skipping datafile') or  (' restoring datafile ' in line):
                long_datafile_name=line.strip().split(' ')[-1]
                short_datafile_name=long_datafile_name.split('/')[-1]
                file_id=short_datafile_name.split('-')[0]
                if file_id.isdigit() == True:
                    restored_datafiles[file_id]=short_datafile_name.strip()
                else:
                    logger.info('Fail to get file id from: ' + long_datafile_name)
                    is_err = True

        #get next line
        line = out_list.stdout.readline()
 
    out_list.wait()
    report_para['restore end time'] = datetime.datetime.now()

    if is_err:
        logger.error('restore database failed')
        exit_after_fail(203)
    else:
        logger.info('Oracle database [%s] is restored'  %(prog_param['sid']))
    
    if 'pdb' in prog_param  and prog_param['pdb'] != '':
        set_newname_for_datafile(restored_datafiles)
    return 0

def get_datafile_list():
    get_datafile_sql = prog_param['script path'] + os.sep + '.get_dataifle_ora10g.sql'
   
    fp = open(get_datafile_sql, 'w')
    write_file(fp, 'connect / as sysdba')
    write_file(fp, 'set head off;')
    write_file(fp, 'set linesize 800;')
    write_file(fp, "select 'Datafile: '||file#, name from v$datafile;")
    write_file(fp, 'exit;')
    fp.close()
    os.chown(get_datafile_sql, uid, gid)
    
    if prog_param['preview']:
        return 0
    
    script_cmd = 'su - ' + prog_param['oracle user'] + ' -c \'. ' + prog_param['oracle profile'] + '; sqlplus /nolog @' + get_datafile_sql + '\''
    logger.debug('')
    logger.debug('get datafiles list')
    logger.debug(script_cmd)

    datafile_list = {}
    out_list = subprocess.Popen(script_cmd, shell=True, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    line = out_list.stdout.readline()
    is_err = False
    while line:
        write_oracle_log(sql_logfile_fp, line)
        if line.startswith('ORA-'):
            is_err = True
        
        if line.startswith('Datafile: '):
            datafile = line.strip()[10:]
            datafile_id = datafile.split(' ')[0].strip()
            datafile_name = datafile.split('/')[-1].strip()
            datafile_list[datafile_id] = datafile_name
        
        line = out_list.stdout.readline()
    out_list.wait()
    if is_err:
        logger.error('get datafile failed')
        exit_after_fail(206)
        
    #logger.debug(datafile_list)
    return datafile_list


def get_last_db_backup_time(tag='HOT_DB_BK'):
    script_file = prog_param['script path'] + os.sep + '.list_backup_of_db_summary'
    fp = open(script_file, 'w')
    write_file(fp, 'connect target /')
    write_file(fp, 'list backup of database summary;')
    write_file(fp, 'exit;')
    fp.close()
    os.chown(script_file, uid, gid)
    
    script_cmd = 'su - ' + prog_param['oracle user'] + ' -c \'. ' + prog_param['oracle profile'] + '; rman cmdfile=' + script_file + '\''

    logger.debug('List database backup records in controlfile : ')
    logger.debug(script_cmd)

    out_list = subprocess.Popen(script_cmd, shell=True, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    line = out_list.stdout.readline()
    
    while line:
        write_oracle_log(sql_logfile_fp, line)
        if line.find(tag) > -1:
            str_time = line.split()[5].strip() + ' ' + line.split()[6].strip()
        line = out_list.stdout.readline()    
    out_list.wait()
    
    logger.debug( 'The last database backup complete at ' + str_time)     
    if is_date(str_time):
        l_time = datetime.datetime.strptime(str_time, '%Y-%m-%d %H:%M:%S')
    else:
        logger.warn('fail to get the last database backup time')        
        l_time = datetime.datetime.now()
    return l_time
    

    
def recover_db():    
    if 'recover offset' not in prog_param:
        prog_param['recover offset'] = 0
        logger.debug("'recover offset' is not set")
        logger.info('Recover all archivelogs')
    elif prog_param['recover offset'].isdigit == False:
        logger.warn('Recover offset is not set or invalid')
        logger.info('Recover all archivelogs')
    elif prog_param['recover offset'] == '0':
        logger.info('Recover all archivelogs')
    else:
        db_backup_time = get_last_db_backup_time('HOT_DB_BK')
        logger.info('Recover %s hours archivelog at most' %(prog_param['recover offset']))        
        restore_target_time = db_backup_time + datetime.timedelta(hours=int(prog_param['recover offset']))
        restore_target_time = min(datetime.datetime.strptime(prog_param['until'], '%Y-%m-%d %H:%M:%S'),restore_target_time)
        prog_param['until'] = restore_target_time.strftime('%Y-%m-%d %H:%M:%S')
    
    logger.info('Recover database [%s] to [%s]' %(prog_param['dbname'],prog_param['until']))
   
    script_file = prog_param['script path'] + os.sep + '6-recover-database.rvt'
    fp = open(script_file, 'w')
    if 'dbid' in prog_param and prog_param['dbid'] != '':
        write_file(fp, 'set dbid ' + prog_param['dbid'])
    write_file(fp, 'connect target /')
    write_file(fp, 'run {')
    write_file(fp, 'set until time \"to_date(\'' + prog_param['until'] + '\',\'yyyy-mm-dd hh24:mi:ss\')\";')


    #prepare recover scripts
    if 'archivelog backup nodes' in prog_param and prog_param['archivelog backup nodes'].strip() != '':
        #restore archivelog from multiple nodes
        backup_nodes=[]
        node_list = prog_param['archivelog backup nodes'].split(',')
        logger.debug('archivelog backup nodes:')
        logger.debug(node_list)
        if len(node_list) < 1:
            logger.error("Fail to parse archivelog backup nodes, please check the parameter 'archivelog backup nodes'")
            exit_after_fail()

        #check archivelog backup record in NetBackup
        i=0
        while i <len(node_list):
            if check_archivelog_backup(node_list[i]) == True:
                backup_nodes.append(node_list[i])
                logger.debug(node_list[i] + 'has archivelog backup')
            i+=1
        if len(backup_nodes) < 1:
            logger.error("Failed to get archivelog backup from:")
            logger.error(backup_nodes)
            exit_after_fail(207)

        i=0
        channel_pre_node = prog_param['restore channel']/len(backup_nodes)
        if channel_pre_node < 1:
            logger.warn("The number of nodes is bigger than channel, please increase channel from config file")
            channel_pre_node = 1

        i=0
        while i < len(backup_nodes):
            channel_env = 'PARMS \'ENV=(NB_ORA_CLIENT=' + backup_nodes[i] + ')\';'
            j = 0
            while j < channel_pre_node:
                write_file(fp, 'ALLOCATE CHANNEL CH' + str(i) + str(j) + ' TYPE \'SBT_TAPE\' ' + channel_env)
                j += 1
            i+=1
        write_file(fp, 'recover database;')
        #write_file(fp, 'switch datafile all;')
        write_file(fp, '}')
        fp.close()
        os.chown(script_file, uid, gid)
    else:
        #restore archivelog from one node
        channel_env = 'PARMS \'ENV=(NB_ORA_CLIENT=' + prog_param['source client'] + ')\';'
        for i in range(prog_param['restore channel']):
            write_file(fp, 'ALLOCATE CHANNEL CH' + str(i) + ' TYPE \'SBT_TAPE\' ' + channel_env)

        if 'pdb' in prog_param  and prog_param['pdb'] != '' and  prog_param['preview'] == False:
            skip_tablespaces = get_skip_tablespaces(prog_param['pdb'])
            if skip_tablespaces:
                logger.debug('Tablespace ' + skip_tablespaces + ' will be skipped during recovery')
                write_file(fp, 'recover database skip forever tablespace ' + skip_tablespaces +' ;')
            else:
                write_file(fp, 'recover database;')
        else:
            write_file(fp, 'recover database;')
            
        for i in range(prog_param['restore channel']):
            write_file(fp, 'RELEASE CHANNEL CH' + str(i) + ';')
        write_file(fp, '}')
        fp.close()
        os.chown(script_file, uid, gid)


    if prog_param['preview']:
        return 0

    create_oracle_profile()
    script_cmd = 'su - ' + prog_param['oracle user'] + ' -c \'. ' + prog_param['oracle profile'] + '; rman cmdfile=' + script_file + '\''
    write_oracle_log(sql_logfile_fp, '')
    write_oracle_log(sql_logfile_fp, '')
    write_oracle_log(sql_logfile_fp, script_cmd)
    
    read_f = 0
    complete_f = 0
    report_para['archivelog'] = []
    report_para['recover start time'] = datetime.datetime.now()
    
    out_list = subprocess.Popen(script_cmd, shell=True, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    line = out_list.stdout.readline()
    
    is_err = False
    err_6054 = False
    while line:
        write_oracle_log(sql_logfile_fp, line)

        if line.startswith('RMAN-'):
            is_err = True

        if line.startswith('RMAN-06054'):
            err_6054 = True

        if 'reading from backup piece' in line:
            report_para['archivelog'].append(line.split()[6])
            read_f += 1
        if 'restore complete' in line:
            complete_f += 1
        line = out_list.stdout.readline()

    out_list.wait()
    report_para['recover end time'] = datetime.datetime.now()

    if ( is_err == True and err_6054 == False ) or ( complete_f != read_f ):
        logger.error('recover database failed')
        exit_after_fail(208)
    else:
        logger.info('Recover database complete')
    return 0


def get_skip_tablespaces(pdb):
    script_file = prog_param['script path'] + os.sep + '.get_skip_tablespaces.sql'

    fp = open(script_file, 'w')
    write_file(fp, 'connect / as sysdba')
    write_file(fp, 'set head off;')
    write_file(fp, 'set linesize 800')
    write_file(fp, "select 'Tablespace Name '||a.name||':'||b.name from  v$pdbs a, v$tablespace b  where a.con_id=b.con_id  and a.name!='PDB$SEED';" )                      
    write_file(fp, 'exit')
    fp.close()
    os.chown(script_file, uid, gid)

    script_cmd = 'su - ' + prog_param['oracle user'] + ' -c \'. ' + prog_param['oracle profile'] + '; sqlplus /nolog @' + script_file + '\''
    logger.debug('')
    write_oracle_log(sql_logfile_fp, '')
    write_oracle_log(sql_logfile_fp, '')
    write_oracle_log(sql_logfile_fp, script_cmd)

    out_list = subprocess.Popen(script_cmd, shell=True, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    line = out_list.stdout.readline()
    is_err = False
    tablespace_list = []
    while line:
        write_oracle_log(sql_logfile_fp, line)
        if line.startswith('ORA-'):
            is_err = True
            break
        if line.startswith('Tablespace Name') and len(line) > 16 :
            tablespace_list.append(line.strip()[16:])
        line = out_list.stdout.readline()
    out_list.wait()
    
    if is_err:
        logger.error('get tablespace list failed')
        exit_after_fail(205)
    else:
        #copy a list
        skip_tablespace_list = []
        for tablepace_name in tablespace_list:
            skip_tablespace_list.append(tablepace_name)
        
        pdb_list = pdb.split(',')
        for pdb_name in pdb_list:
            for tablepace_name in tablespace_list:
                if tablepace_name.startswith(pdb_name.upper()):
                    skip_tablespace_list.remove(tablepace_name)                   
    return ",".join(skip_tablespace_list)
            

def rename_redolog_tempfile():
    logger.info('Recreate redolog and tempfile' )

    #get file name from v$logfile and v$tempfile
    script_file = prog_param['script path'] + os.sep + '.get_rename_list.sql'
    sql_file = prog_param['script path'] + os.sep + '7-rename-redolog.sql'

    fp = open(script_file, 'w')
    write_file(fp, 'connect / as sysdba')
    write_file(fp, 'set head off;')
    write_file(fp, 'set linesize 800')
    write_file(fp, 'spool ' + sql_file)
    write_file(fp, "select 'FileName: '||member from v$logfile union select 'FileName: '||name from v$tempfile;")
    write_file(fp, 'spool off;')
    write_file(fp, 'exit')
    fp.close()
    os.chown(script_file, uid, gid)

    if prog_param['preview']:
        return 0

    script_cmd = 'su - ' + prog_param['oracle user'] + ' -c \'. ' + prog_param['oracle profile'] + '; sqlplus /nolog @' + script_file + '\''
    logger.debug('')
    write_oracle_log(sql_logfile_fp, '')
    write_oracle_log(sql_logfile_fp, '')
    write_oracle_log(sql_logfile_fp, script_cmd)

    out_list = subprocess.Popen(script_cmd, shell=True, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    line = out_list.stdout.readline()
    is_err = False
    while line:
        write_oracle_log(sql_logfile_fp, line)
        if line.startswith('ORA-'):
            is_err = True
        line = out_list.stdout.readline()
    out_list.wait()
    if is_err:
        logger.error('get rename redolog list failed')
        exit_after_fail(209)

    sql_fp = open(sql_file, 'r')
    lines = []
    lines.append('connect / as sysdba\n')
    for t_opt  in sql_fp:
        #skip the last blank and the last line "xxx" rows selected.
        if t_opt.startswith('FileName: '):
            temp_file_name = t_opt.strip()[10:]
            lines.append( "alter database rename file '" + temp_file_name + "' to " +  "'" + prog_param['tempfile path'] + os.sep + \
                temp_file_name.split('/')[-1]+ '.' + datetime.datetime.now().strftime('%Y%m%d%H%M%S') + "'" + ';' + '\n')
            time.sleep(2)

    sql_fp.close()
    lines.append('exit\n')

    sql_fp = open(sql_file, 'w')
    sql_fp.writelines(lines)
    sql_fp.close()
    os.chown(sql_file, uid, gid)

    sql_cmd = 'su - ' + prog_param['oracle user'] + ' -c \'. ' + prog_param['oracle profile'] + '; sqlplus /nolog @' + sql_file + '\''
    logger.debug('')
    logger.debug('Rename redo logfile and tempfile.')
    write_oracle_log(sql_logfile_fp, '')
    write_oracle_log(sql_logfile_fp, '')
    write_oracle_log(sql_logfile_fp, script_cmd)

    sql_list = subprocess.Popen(sql_cmd, shell=True, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    line = sql_list.stdout.readline()
    is_err = False
    while line:
        write_oracle_log(sql_logfile_fp, line)
        if line.startswith('ORA-'):
            is_err = True
        line = sql_list.stdout.readline()
    sql_list.wait()

    if is_err:
        logger.error('rename tempfile, redolog failed')
        exit_after_fail(210)
    else:
        logger.info('Redolog and temp file created. ')
        return 0

#get datafile size from v$datafile
def get_db_size(table_name):
    script_file = prog_param['script path'] + os.sep + '.get_db_size.sql'
    fp = open(script_file, 'w')
    write_file(fp, 'connect / as sysdba')
    write_file(fp, "SELECT '" + table_name + ": '||round(SUM(bytes / (1024*1024)), 0) FROM "+ table_name + ';')
    write_file(fp, 'exit')
    fp.close()
    os.chown(script_file, uid, gid)

    script_cmd = 'su - ' + prog_param['oracle user'] + ' -c \'. ' + prog_param['oracle profile'] + '; sqlplus /nolog @' + script_file + '\''
    write_oracle_log(sql_logfile_fp, '')
    write_oracle_log(sql_logfile_fp, '')
    write_oracle_log(sql_logfile_fp, script_cmd)
    write_oracle_log(sql_logfile_fp, "SELECT '" + table_name + ": '||round(SUM(bytes / (1024*1024)), 0) FROM "+ table_name + ';')


    out_list = subprocess.Popen(script_cmd, shell=True, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    is_err = True
    line = out_list.stdout.readline()
    while line:
        write_oracle_log(sql_logfile_fp, line)
        
        if line.strip().startswith(table_name):
            db_size  = line.split(':')[-1].strip()
            if db_size.isdigit() == True:
                return int(db_size) 
        line = out_list.stdout.readline()
    out_list.wait()
    return 0


def open_db(mode='resetlogs'):
    if mode not in ('resetlogs', 'read only'):
        logger.info('invalid mode for open_db')

    script_file = prog_param['script path'] + os.sep + '8-open-database.sql'
    fp = open(script_file, 'w')
    write_file(fp, 'connect / as sysdba')
    write_file(fp, 'alter database noarchivelog;')
    write_file(fp, 'alter database open ' + mode + ';')
    
    #open pluggable database
    if mode == 'resetlogs' and 'pdb' in prog_param  and prog_param['pdb'] != '':
            write_file(fp, 'alter pluggable database ' + prog_param['pdb'] + ' open;')
    #else:
    #    write_file(fp, 'alter pluggable database all open;')
   
    write_file(fp, 'exit')
    fp.close()
    os.chown(script_file, uid, gid)
    if prog_param['preview']:
        return 0

    script_cmd = 'su - ' + prog_param['oracle user'] + ' -c \'. ' + prog_param['oracle profile'] + '; sqlplus /nolog @' + script_file + '\''
    logger.info('Open database [%s]' %(prog_param['sid']))
    write_oracle_log(sql_logfile_fp, '')
    write_oracle_log(sql_logfile_fp, '')
    write_oracle_log(sql_logfile_fp, script_cmd)

    out_list = subprocess.Popen(script_cmd, shell=True, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    out_list.wait()
    report_para['restore end time'] = datetime.datetime.now()

    if get_oracle_status() != 'OPEN':
        logger.error('Open database failed')
        exit_after_fail(212)
    else:
        logger.info('Open database complete')
        if mode == 'read only':
            logger.info('Database is opened read only.')
    return 0

def verify_db():
    if 'verify script' not in prog_param or prog_param['verify script'] == '':
        logger.info('The script is not set, skip verification step.')
        return 0

    logger.info('Run verify script  ' + prog_param['verify script'])

    if not os.path.isfile(prog_param['verify script']):
        logger.error('Cannot find the verify script file: ' + prog_param['verify script'])
        exit_after_fail(213)
    verify_script_fp = open(prog_param['verify script'])

    verify_sql_file = prog_param['script path'] + os.sep + '9-verify_script.sql'

    fp = open(verify_sql_file, 'w')
    write_file(fp, 'connect / as sysdba')
    write_file(fp, verify_script_fp.read())
    write_file(fp, 'exit')
    fp.close()
    verify_script_fp.close()

    os.chown(verify_sql_file, uid, gid)
    if prog_param['preview']:
        return 0

    script_cmd = 'su - ' + prog_param['oracle user'] + ' -c \'. ' + prog_param['oracle profile'] + '; sqlplus /nolog @' + verify_sql_file + '\''
    write_oracle_log(sql_logfile_fp, '')
    write_oracle_log(sql_logfile_fp, '')
    write_oracle_log(sql_logfile_fp, script_cmd)

    out_list = subprocess.Popen(script_cmd, shell=True, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    is_err = False
    line = out_list.stdout.readline()
    while line:
        write_oracle_log(sql_logfile_fp, line)
        if line.startswith('ORA-'):
            is_err = True
        line = out_list.stdout.readline()
    out_list.wait()

    if is_err:
        logger.error('Verify database failed')
        exit_after_fail(214)
    else:
        logger.info('Verify database complete')
    return 0


def exit_after_fail( code=1):
    if 'shutdown after failure' in prog_param and prog_param['shutdown after failure'] == 1:
        shutdown_oracle('abort')
    
    if code > 160:
        update_restore_info(code)
     
    if 'sql_logfile_fp' in dir() and sql_logfile_fp:
        sql_logfile_fp.close()
    sys.exit(code)


def restore_report():
    if prog_param['preview']:
        return 0
    if 'begin 'in prog_param or 'end' in prog_param:
        return 0
        
    logger.debug('')
    logger.debug('Backup images used in the restore:')
    logger.debug(report_para['datafile'])
    logger.debug(report_para['archivelog'])
    logger.debug('')
    
    cmd = '/usr/openv/netbackup/bin/bplist -t 4 -l -Listseconds -R -C ' + prog_param['source client']
    s_time = datetime.datetime.now() - datetime.timedelta(days=int(prog_param['backup search period']))
    e_time = datetime.datetime.strptime(prog_param['until'], '%Y-%m-%d %H:%M:%S') + datetime.timedelta(days=1)
    cmd = cmd + ' -s ' + s_time.strftime('%m/%d/%Y %H:%M:%S') + ' -e ' + e_time.strftime('%m/%d/%Y %H:%M:%S') + ' /'
    logger.debug('Get backup size run command: ' + cmd)
    out_list = subprocess.Popen(cmd, shell=True, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    report_para['db size'] = 0
    report_para['log size'] = 0
    report_para['db backup time'] = datetime.datetime.now()
    report_para['log backup time'] = datetime.datetime.now()
    for line in out_list.stdout.readlines():
        row = line.split()
        if len(row) == 8:
            if row[7].strip('/\n\r\x00') in report_para['datafile']:
                if 'K' in row[3]:
                    report_para['db size'] += int(row[3].strip('K'))
                else:
                    report_para['db size'] += int(row[3].strip())/1024
                str_time = row[4] + ' ' + row[5] + ' ' + datetime.datetime.now().strftime('%Y') + ' ' + row[6]
                l_time = datetime.datetime.strptime(str_time, '%b %d %Y %H:%M:%S')
                if l_time > datetime.datetime.now():
                    str_time = row[4] + ' ' + row[5] + ' ' + str(datetime.date.today().year - 1) + ' ' + row[6]
                    l_time = datetime.datetime.strptime(str_time, '%b %d %Y %H:%M:%S')
                if l_time < report_para['db backup time']:
                    report_para['db backup time'] = l_time
            elif row[7].strip('/\n\r\x00') in report_para['archivelog']:
                if 'K' in row[3]:
                    report_para['log size'] += int(row[3].strip('K'))
                else:
                    report_para['log size'] += int(row[3].strip())/1024
                str_time = row[4] + ' ' + row[5] + ' ' + datetime.datetime.now().strftime('%Y') + ' ' + row[6]
                l_time = datetime.datetime.strptime(str_time, '%b %d %Y %H:%M:%S')
                if l_time > datetime.datetime.now():
                    str_time = row[4] + ' ' + row[5] + ' ' + str(datetime.date.today().year - 1) + ' ' + row[6]
                    l_time = datetime.datetime.strptime(str_time, '%b %d %Y %H:%M:%S')
                if l_time < report_para['log backup time']:
                    report_para['log backup time'] = l_time
                    
    report_file = prog_param['report path'] + os.sep + 'db-restore-report.csv'
    if not os.path.isfile(report_file):
        fp = open(report_file, 'w')
        write_file(fp, 'Oracle_SID,DBNAME,ClientName,RestoreStartTime,RestoreEndTime,RestoreDbSize,DbBackupTime')
        fp.close()
    if os.path.isfile(report_file):
        fp = open(report_file, 'a')
        row = prog_param['sid'] + ',' + prog_param['dbname'] + ',' + prog_param['source client'
        ]+ ',' + report_para['restore start time'].strftime('%Y-%m-%d %H:%M:%S') + ',' + report_para['restore end time'
        ].strftime('%Y-%m-%d %H:%M:%S') + ',' + str((report_para['db size'] + report_para['log size'])/1024) + ',' + report_para['db backup time'].strftime('%Y-%m-%d %H:%M:%S')
        write_file(fp, row)
        fp.close()
        logger.info('')
        logger.info('=================== RESTORE SUMMARY ===================')        
        logger.info('          DBNAME: '+ prog_param['dbname'])
        logger.info('      ORACLE SID: '+ prog_param['sid'])
        if 'dbid' in prog_param and prog_param['dbid'] != '':
            logger.info('            DBID: '+ prog_param['dbid'])
        logger.info('   Source Client: '+ prog_param['source client'])
        logger.info('  Backuped Taken: '+ report_para['db backup time'].strftime('%Y-%m-%d %H:%M:%S'))
        logger.info('   Database Size: '+ str(get_storage_size_datafile(prog_param['oradata path'])) + 'MB')        
        logger.info(' Actual Restored: '+ str((report_para['db size'] + report_para['log size'])/1024) + 'MB')
        logger.info('   Restore Start: ' + report_para['restore start time'].strftime('%Y-%m-%d %H:%M:%S'))
        logger.info('Restore Complete: '+ report_para['restore end time'].strftime('%Y-%m-%d %H:%M:%S'))
        logger.info(' Restore Logfile: ' + prog_param['restore logfile'])
        logger.info(' Restore SQL log: '+ prog_param['sql logfile'])
        logger.info('========================= END =========================')
        logger.info('')



def get_controlfile_backup():
    cmd = '/usr/openv/netbackup/bin/bplist -t 4 -l -R -C ' + prog_param['source client']

    s_time = datetime.datetime.now() - datetime.timedelta(days=int(prog_param['backup search period']))
#    e_time = datetime.datetime.strptime(prog_param['until'], '%Y-%m-%d %H:%M:%S') + datetime.timedelta(days=2)
    e_time = datetime.datetime.strptime(prog_param['until'], '%Y-%m-%d %H:%M:%S') + datetime.timedelta(days=1)

    cmd = cmd + ' -s ' + s_time.strftime('%m/%d/%Y %H:%M:%S') + ' -e ' + e_time.strftime('%m/%d/%Y %H:%M:%S') + ' /' + prog_param['controlfile prefix'] + '*'
    logger.info('Search backuped controlfile from ' + s_time.strftime('%m/%d/%Y %H:%M:%S') + " to " + e_time.strftime('%m/%d/%Y %H:%M:%S'))
    logger.debug(cmd)
    out_list = subprocess.Popen(cmd, shell=True, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    for line in out_list.stdout.readlines():
        if '/' + prog_param['controlfile prefix'] in line:
            row = line.split()
            if len(row) == 8:
                if '/' + prog_param['controlfile prefix'] in row[7]:
                    return row[7].strip('\n\r\x00')
    logger.error('fail to get backup id of controlfile')
    exit_after_fail(163)

def check_datafile_backup(client):
    if 'datafile prefix' not in prog_param  or prog_param['datafile prefix'] == '':
        logger.error('datafile prefix should be set in multiple node restore')
        exit_after_fail(202)

    cmd = '/usr/openv/netbackup/bin/bplist -t 4 -l -R -C ' + client

    s_time = datetime.datetime.now() - datetime.timedelta(days=int(prog_param['backup search period']))
#    e_time = datetime.datetime.strptime(prog_param['until'], '%Y-%m-%d %H:%M:%S') + datetime.timedelta(days=2)
    e_time = datetime.datetime.strptime(prog_param['until'], '%Y-%m-%d %H:%M:%S') + datetime.timedelta(days=1)
    cmd = cmd + ' -s ' + s_time.strftime('%m/%d/%Y %H:%M:%S') + ' -e ' + e_time.strftime('%m/%d/%Y %H:%M:%S') + ' /' + prog_param['datafile prefix'] + '*'
    logger.info('Search datafile from ' + s_time.strftime('%m/%d/%Y %H:%M:%S') + " to " + e_time.strftime('%m/%d/%Y %H:%M:%S'))
    logger.debug(cmd)
    out_list = subprocess.Popen(cmd, shell=True, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    for line in out_list.stdout.readlines():
        if '/' + prog_param['datafile prefix'] in line:
            row = line.split()
            if len(row) == 8:
                if '/' + prog_param['datafile prefix'] in row[7]:
                    logger.debug('Find datafile backup from ' + client)
                    return True
    logger.debug('Cannot find datafile backup from ' + client)
    return False

def check_archivelog_backup(client):
    cmd = '/usr/openv/netbackup/bin/bplist -t 4 -l -R -C ' + client

    s_time = datetime.datetime.now() - datetime.timedelta(days=int(prog_param['backup search period']))
    e_time = datetime.datetime.strptime(prog_param['until'], '%Y-%m-%d %H:%M:%S') + datetime.timedelta(days=2)

    cmd = cmd + ' -s ' + s_time.strftime('%m/%d/%Y %H:%M:%S') + ' -e ' + e_time.strftime('%m/%d/%Y %H:%M:%S') + ' /' + prog_param['archivelog prefix'] + '*'
    logger.info('Search datafile from ' + s_time.strftime('%m/%d/%Y %H:%M:%S') + " to " + e_time.strftime('%m/%d/%Y %H:%M:%S'))
    logger.debug(cmd)
    out_list = subprocess.Popen(cmd, shell=True, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    for line in out_list.stdout.readlines():
        if '/' + prog_param['archivelog prefix'] in line:
            row = line.split()
            if len(row) == 8:
                if '/' + prog_param['archivelog prefix'] in row[7]:
                    logger.debug('Find archivelog backup from ' + client)
                    return True
    logger.debug('Cannot find archivelog from ' + client)
    return False


def run_nbu_cmd(cmd):
    logger.debug('run nbu command: ' + cmd)
    result = os.system(cmd)
    result >>= 8
    if result != 0:
        logger.error('Failed run nbu command: ' + cmd)
        exit_after_fail(result)
    return 0

def is_date(date):
    try:
        time.strptime(date, "%Y-%m-%d %H:%M:%S")
        return True
    except:
        return False


def  get_restore_server():
    cmd = 'hostname'
    return commands.getoutput(cmd).strip() 
    
def  get_storage_size_datafile(path):
    cmd = 'du -sk  ' + path
    output = commands.getoutput(cmd)
    data_size = output.strip().split()[0]
    if  data_size.isdigit():
        return int(data_size)/1024
    else:
        logger.error('Get restored size failed')
        return 0


def create_restore_info():
    if log_to_mysql == False:
        return 0

    #prepare values
    date_type = 'oracle'
    source_client = prog_param['source client']
    dbname = prog_param['dbname']
    restore_server = get_restore_server()
    restore_until = prog_param['until']
    start_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    logfile = prog_param['restore logfile']
    sql_logfile = prog_param['sql logfile']
    status = 'prepare for restore'
    data = (date_type,source_client,dbname,restore_server,restore_until,start_time,logfile,sql_logfile, status)
    
    #connect database
    try:
        db_connection=pymysql.connect(**db_connection_string)
    except:
            logger.error('fail to connect mysql database')
            exit(140)
    
    cursor = db_connection.cursor()
    try:
        sql = """INSERT INTO restore_history (data_type, source_client, dbname, restore_server, restore_until, start_time, logfile, sql_logfile, status) 
                    VALUES ( '%s','%s','%s','%s','%s','%s','%s','%s','%s')"""
                    
        cursor.execute(sql % data)
    except Exception,e :
        logger.debug(sql % data)
        raise Exception('Insert record to database failed.')
    else:
        global restore_operation_id
        restore_operation_id = int(cursor.lastrowid)
        logger.info( 'The operation id of this restore is %d' %(restore_operation_id))
        db_connection.commit()
    finally:
        cursor.close()    
        db_connection.close()
        
    return restore_operation_id
    

def update_restore_info(code, data_size=0, datafile_size=0):
    if log_to_mysql == False:
        return 0

    #prepare  values
    complete_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    status = code_and_message[code]

    #connect database
    try:
        db_connection=pymysql.connect(**db_connection_string)
    except:
            logger.error('fail to connect mysql database')
            exit(140)
    
    cursor = db_connection.cursor()
    try:
        if datafile_size and data_size:
            sql = """UPDATE restore_history SET 
                            complete_time = '%s',
                            code  = %d,
                            status = '%s',
                            data_size = %d,
                            datafile_size = %d
                     WHERE id = %d"""
            data = (complete_time, code, status, data_size, datafile_size, restore_operation_id)
        else:
            sql = """UPDATE restore_history SET 
                            complete_time = '%s',
                            code  = %d,
                            status = '%s' 
                     WHERE id = %d"""
            data = (complete_time, code, status, restore_operation_id)
            
        #logger.debug(sql % data)
        cursor.execute(sql % data)
    except Exception,e :
        logger.debug(sql % data)
        raise Exception('Update resotre status in  database failed.')
    else:
        rs = cursor.rowcount
        if rs != 1:
            logger.error('update resotre status failed.')
            logger.debug(sql % data)
            logger.debug( 'cursor.rowcount: '+ str(rs))
        else:
            db_connection.commit()
    finally:
        cursor.close()    
        db_connection.close()


def get_last_restore_time( source_client, dbname):
    if log_to_mysql == False:
        return 0

    #prepare  values
    data = (source_client,dbname)
    
    #connect database
    try:
        db_connection=pymysql.connect(**db_connection_string)
    except:
            logger.error('fail to connect mysql database')
            exit(140)
    
    cursor = db_connection.cursor()
    try:
        sql = """SELECT  id 
                   FROM  restore_history
                  WHERE  code = 0
                    AND  source_client = '%s'
                    AND  dbname = '%s' """
        cursor.execute(sql % data)
        rs = cursor.fetchall()    
        
        if len(rs) == 0:
            logger.debug('This database has never been restored')
            retore_time = 0
        else: 
            sql = """SELECT   cast(max(complete_time) as char)
                       FROM  restore_history
                      WHERE  code = 0 
                        AND  source_client = '%s'
                        AND  dbname = '%s' """
            #logger.debug(sql % data)
            cursor.execute(sql % data)
            retore_time = cursor.fetchone()[0]

            if is_date(retore_time):
                logger.info('This database has been restored at ' + retore_time)
            else: 
                logger.debug('wrong time format fetched from mysql' + retore_time)
                retore_time = 0
    except Exception,e :
        raise Exception('Query mysql failed.')
    finally:
        cursor.close()    
        db_connection.close()
        
    if retore_time:
        return datetime.datetime.strptime(retore_time, '%Y-%m-%d %H:%M:%S')
    else:
        return 0

        
def cleanup_restored_datafile():
    if check_oracle_status( prog_param['sid']) == 'ONLINE':
        logger.info('Oracle instance ' + prog_param['sid'] + ' is  running. Shut it down before cleanup.')
        shutdown_oracle('abort')

    if not os.path.exists(prog_param['oradata path']):
        logger.debug('oradata path not exist')
        exit_after_fail(162)
    
    logger.debug('Delete previous restored datafiles in %s' %(prog_param['oradata path']))
    os.system('rm -rf ' + prog_param['oradata path'] + "/*")
    os.system('rm -rf ' +  prog_param['restore home'] + "/fra/*")    
    return 0

        
def phase_1_start_nomount():
    if 'begin' in prog_param and prog_param['begin'] > 'phase_1':
        return 0
   
    last_restore_time = get_last_restore_time(prog_param['source client'], prog_param['dbname'])
    if  last_restore_time != 0:
        if 'restore interval' not in prog_param or prog_param['restore interval'].isdigit() == False:
            prog_param['restore interval']=0
 
        if  (datetime.datetime.now() > last_restore_time  + datetime.timedelta(days=int(prog_param['restore interval']))):
            logger.info('Restore it again')
        elif 'force' in prog_param and prog_param['force'] == True:
            logger.debug('Restore with force option')
        else:
            logger.info('The restore operation is skipped')
            sys.exit(0)

    logger.info('')
    logger.info('')
    logger.info('PHASE #1: create pfile and startup nomount')
            
    create_restore_info()
    
    report_para['restore start time'] = datetime.datetime.now()
    

    if check_oracle_status( prog_param['sid']) == 'ONLINE':
        logger.info('Oracle instance [%s] is already running.' %(prog_param['sid']))
        if prog_param['shutdown exist instance'] == '1' and prog_param['preview'] == False:
            shutdown_oracle('abort')
        else:
            exit_after_fail(161)

    if prog_param['cleanup before restore'] == '1' and prog_param['preview'] == False:
        cleanup_restored_datafile()
    
    if not os.path.exists(prog_param['tempfile path']):
        os.makedirs(prog_param['tempfile path'])
        os.chown(prog_param['tempfile path'], prog_param['oracle uid'], prog_param['oracle gid'])
    else:
        os.system('rm -rf ' + prog_param['tempfile path'] + "/*")
        

    create_pfile()
    startup_nomount()
    return 0

def phase_2_restore_controlfile():
    if ('begin' in prog_param and prog_param['begin'] > 'phase_2') or ('end' in prog_param and prog_param['end'] < 'phase_2'):
        return 0

    logger.info('')
    logger.info('')
    update_restore_info(2)
    logger.info('PHASE #2: restore controlfile and mount')
    script_file = prog_param['script path'] + os.sep + '2-restore-control.rvt'
    fp = open(script_file, 'w')
    control_file = get_controlfile_backup()
    logger.info('Found controlfile: ' + control_file)

    channel_env = 'PARMS \'ENV=(NB_ORA_CLIENT=' + prog_param['source client'] + ')\';'
    write_file(fp, 'connect target /')
    write_file(fp, 'run {')
    write_file(fp, 'ALLOCATE CHANNEL CH1 TYPE \'SBT_TAPE\' ' + channel_env)
    write_file(fp, 'restore controlfile from \'' + control_file + '\';')
    write_file(fp, 'RELEASE CHANNEL CH1;')
    write_file(fp, '}')
    fp.close()
    os.chown(script_file, uid, gid)

    if prog_param['preview']:
        #generate mount database script file only
        mount_database()
        return 0

    create_oracle_profile()
    script_cmd = 'su - ' + prog_param['oracle user'] + ' -c \'. ' + prog_param['oracle profile'] + '; rman cmdfile=' + script_file + '\''
    logger.debug('')    
    logger.debug('Restore controlfile')
    write_oracle_log(sql_logfile_fp, '')
    write_oracle_log(sql_logfile_fp, script_cmd)

    out_list = subprocess.Popen(script_cmd, shell=True, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    line = out_list.stdout.readline()
    is_err = True
    while line:
        write_oracle_log(sql_logfile_fp, line)
        if 'restore complete' in line:
            is_err = False
        line = out_list.stdout.readline()
    out_list.wait()

    if is_err:
        logger.error('Restore controlfile failed')
        exit_after_fail(164)
    else:
        logger.info('Restore controlfile complete')
        mount_database()
        return 0

def phase_3_restore_db():
    if ('begin' in prog_param and prog_param['begin'] > 'phase_3') or ('end' in prog_param and prog_param['end'] < 'phase_3'):
        return 0

    
    logger.info('')
    logger.info('')
    logger.info('PHASE #3:  restore datafiles')
    update_restore_info(3)
    restore_db()

def phase_4_recover_db():
    if ('begin' in prog_param and prog_param['begin'] > 'phase_4') or ('end' in prog_param and prog_param['end'] < 'phase_4'):
        return 0
    logger.info('')
    logger.info('')
    logger.info('PHASE #4:  apply archivelog')
    update_restore_info(4)

    recover_db()

def phase_5_open_db():
    if ('begin' in prog_param and prog_param['begin'] > 'phase_5') or ('end' in prog_param and prog_param['end'] < 'phase_5'):
        return 0
    logger.info('')
    logger.info('')
    logger.info('PHASE #5: open database and execute verify sql scripts')
    rename_redolog_tempfile()
    if prog_param['database open mode'] in ('resetlogs', 'read only'):
        open_db(prog_param['database open mode'])
        logger.info('Restore complete')
    else:
        logger.info("invalid value to open database, Please check 'database open mode' in the config file")
        exit_after_fail(211)

def phase_6_verify_db():
    if('end' in prog_param and prog_param['end'] < 'phase_6'):
        return 0

        logger.info('')
    logger.info('')
    logger.info('PHASE #6: verify database restore')

    verify_db()
    
    data_size = get_db_size('dba_segments')
    if data_size == 0:
        logger.error('get db size failed')
        exit_after_fail(215)
        
    datafile_size = get_db_size('dba_data_files')
    if datafile_size == 0:
        logger.error('get db size failed')
        exit_after_fail(215)
    
    update_restore_info(0, data_size, datafile_size)
   
    if ('begin' not in prog_param):
        restore_report()

    if prog_param['shutdown after restore'] == '1':
        logger.info('shutdown instance after restore')
        shutdown_oracle()
        logger.info('Shutdown complete')
        logger.debug('')
             
    if prog_param['cleanup after restore'] == '1':
        cleanup_restored_datafile()
            
    if sql_logfile_fp:
        sql_logfile_fp.close()

if __name__ == '__main__':
    initialize_env()
    phase_1_start_nomount()
    phase_2_restore_controlfile()
    phase_3_restore_db()
    phase_4_recover_db()
    phase_5_open_db()
    phase_6_verify_db()