database | ProgrammerAH

In general, the following file

is automatically created after we create the database instance on the Oracle database

pfile (.ora), which records the basic configuration of the database, plain text, not much content, usually beginning with init, startup command required file

spfile (.ora), binary file, similar to pfile, can be generated by pfile

table space container (.dbf), binary file, this should be familiar, often tens of hundreds of GB

control file and backup, generally under database instance default directory and fast_recovery directory, file names are generally control01.ctl and control02.ctl

The

redo file (.log) is usually installed in the database instance installation directory, along with the.dbf file above. The file name defaults to redo01.log redo02.log redo03.log

these files are the main ones in a database. In other words, if we copy these files to another server with Oracle, we can theoretically pull up the database and continue running.

Losing any of the files above

would be disastrous. This will cause the database to fail startup, and not just for those files. If you open archived logs, the loss of archived logs will cause the database to fail to start.

table space file is lost and data is lost and cannot be recovered unless there is a backup.

The

control file creates two instances by default when you create an instance, if you lose one you can copy the other, if you lose both then the backup with table space does not have

pfile and spfile lost will cause the database cannot startup, but the pfile content is less, plaintext, can be copied from other instances over reference. Spfile, on the other hand, can be generated from a pfile via SQLPlus, so the loss of these two files is not necessarily catastrophic.

After

pfile is lost or tampered, you will often encounter “ora-03113 communication channel end file “error or ora-01507 database not mounted error

let’s first look at the structure of the pfile

# 
# $Header: rdbms/admin/init.ora /main/23 2009/05/15 13:35:38 ysarig Exp $ 
# 
# Copyright (c) 1991, 1997, 1998 by Oracle Corporation
# NAME
#   init.ora
# FUNCTION
# NOTES
# MODIFIED
#     ysarig     05/14/09  - Updating compatible to 11.2
#     ysarig     08/13/07  - Fixing the sample for 11g
#     atsukerm   08/06/98 -  fix for 8.1.
#     hpiao      06/05/97 -  fix for 803
#     glavash    05/12/97 -  add oracle_trace_enable comment
#     hpiao      04/22/97 -  remove ifile=, events=, etc.
#     alingelb   09/19/94 -  remove vms-specific stuff
#     dpawson    07/07/93 -  add more comments regarded archive start
#     maporter   10/29/92 -  Add vms_sga_use_gblpagfile=TRUE 
#     jloaiza    03/07/92 -  change ALPHA to BETA 
#     danderso   02/26/92 -  change db_block_cache_protect to _db_block_cache_p
#     ghallmar   02/03/92 -  db_directory -> db_domain 
#     maporter   01/12/92 -  merge changes from branch 1.8.308.1 
#     maporter   12/21/91 -  bug 76493: Add control_files parameter 
#     wbridge    12/03/91 -  use of %c in archive format is discouraged 
#     ghallmar   12/02/91 -  add global_names=true, db_directory=us.acme.com 
#     thayes     11/27/91 -  Change default for cache_clone 
#     jloaiza    08/13/91 -         merge changes from branch 1.7.100.1 
#     jloaiza    07/31/91 -         add debug stuff 
#     rlim       04/29/91 -         removal of char_is_varchar2 
#   Bridge     03/12/91 - log_allocation no longer exists
#   Wijaya     02/05/91 - remove obsolete parameters
#
##############################################################################
# Example INIT.ORA file
#
# This file is provided by Oracle Corporation to help you start by providing
# a starting point to customize your RDBMS installation for your site. 
# 
# NOTE: The values that are used in this file are only intended to be used
# as a starting point. You may want to adjust/tune those values to your
# specific hardware and needs. You may also consider using Database
# Configuration Assistant tool (DBCA) to create INIT file and to size your
# initial set of tablespaces based on the user input.
###############################################################################

# Change '<ORACLE_BASE>' to point to the oracle base (the one you specify at
# install time)

db_name='mydb'
memory_target=1G
processes = 150
audit_file_dest='/u01/oracle/admin/mydb/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u01/oracle/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/db/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=mydbXDB)'
open_cursors=300 
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (control01.ctl，control02.ctl)
compatible ='11.2.0'

the field of the file is not much, most are fixed, such as the version of the database, the location of the database recovery file, size, connection protocol and so on. Parameters that vary between instances, except for the name and the corresponding storage path, are left with the block size.

db_block_size, which is the block size specified when we set up the database, determines the maximum size and efficiency of our database table space and buffer pool. Default is 8192. Under 8192, the maximum size of a single table space file is only 32gb, and expanding this value to 16384 would raise the single table space limit to 64GB, so this parameter will vary from database to database, and errors will cause startup failure.

control_files are control files for the database and can be written to multiple times, separated by commas. Written here is the actual control file name, path can go to find, to the startup log (startup fails will print the full path of this file), the name of the XML, you can use the locate the XML to search the file, or according to your oracle installation position, the path is about/oracle/diag/RDBMS/ora11g ora11g/alert log. The XML

watch the log, if the pfile was written incorrectly, then there will be the following error

ORA-00210: cannot open the specified control file
ORA-00202: control file: /opt/oracle/oradata/ora11g/control03.ctl;

you can refer to this article https://www.cnblogs.com/sparkbj/articles/6146247.html

spfile we can enter sqlplus to generate, the command is as follows:

sqlplus/as sysdba
create spfile from pfile='/u01/mydb/initmydb.ora';

Finally, after we have written the new pfile and spfile files, we can use the startup command to start

startup pfile='/u01/mydb/init.ora' mount

article directory

overview
python connection MySQL
python connection PostgreSQL

overview

in general, when using python to connect to a database, I like to define a Class to facilitate the subsequent use of
Class: a collection of objects that have the same properties and methods. It defines the properties and methods that are common to each object in the collection. An object is an instance of a class.
can be likened to a Word template that you can use every time you create a new Word file, which is handy for frequently used scenarios

connects to the database to execute commands and return results and then disconnects, as is often the case when writing scripts, so define a class

python connection MySQL

connects to MySQL using pymysql

class cnMySQL:
    def __init__(self):
        self._dbhost = '172..16.56.2'
        self._dbuser = 'dba'
        self._dbpassword = 'dba1'
        self._dbname = 'test'
        self._dbcharset = 'utf8'
        self._dbport = int(3306)
        self._conn = self.connectMySQL()

        if (self._conn):
            self._cursor = self._conn.cursor(cursor=pymysql.cursors.DictCursor)
    
    def connectMySQL(self):
        try:
            conn = pymysql.connect(host=self._dbhost,
                                   user=self._dbuser,
                                   passwd=self._dbpassword,
                                   db=self._dbname,
                                   port=self._dbport,
                                   cursorclass=pymysql.cursors.DictCursor,
                                   charset=self._dbcharset)
        except Exception as e:
            raise
            #print("数据库连接出错")
            conn = False
        return conn

    def close(self):
        if (self._conn):
            try:
                if (type(self._cursor) == 'object'):
                    self._conn.close()
                if (type(self._conn) == 'object'):
                    self._conn.close()
            except Exception:
                print("关闭数据库连接异常")

    def ExecQuery(self,sql,*args):
        """
        执行查询语句
        """
        res = ''
        if (self._conn):
            try:
                self._cursor.execute(sql,args)
                res = self._cursor.fetchall()
            except Exception:
                res = False
                print("查询异常")
            self.close()
        return res

use method :
first call: conn = cnMySQL()
execute SQL example: test_sql = conn.ExecQuery(” select * from test where id = %s; Canshu)
returns a list

containing dict when the data is returned

python connection PostgreSQL

connects to PG using the package psycopg2

class PGINFO:

    def __init__(self,host, user, pwd, db, port):
        self.host = host
        self.user = user
        self.pwd = pwd
        self.db = db
        self.port = port

    def __GetConnect(self):
        """
        得到连接信息
        返回: conn.cursor()
        """
        if not self.db:
            raise(NameError, "没有设置数据库信息")
        self.conn = psycopg2.connect(database=self.db, user=self.user, password=self.pwd, host=self.host, port=self.port)
        cur = self.conn.cursor()
        if not cur:
            raise (NameError, "连接数据库失败")
        else:
            return  cur

    def ExecQuery(self, sql):
        """
        执行查询语句
        """
        if sql == 'close':
            self.conn.close()
        else:
            cur = self.__GetConnect()
            cur.execute(sql)
            # resList = cur.fetchall()
            return cur

is called first, where

using the parameter file is called

 pg = PGINFO(host=host_cus, user=user_cus, pwd=pwd_cus, db=db_cus, port=port_cus)

executes the command, fetching the returned result

cur = pg.ExecQuery("show data_directory;")
    pgdata = cur.fetchone()

ProgrammerAH

Programmer Guide, Tips and Tutorial

Tag Archives: database

Oracle database missing init.ora Solutions

Python classes that connect to the database

article directory